PostgreSQL - Foreign Data Wrapper user mapping password

Question

I'm using a PostgreSQL database and postgres_fdw extension to query external data.

CREATE EXTENSION postgres_fdw;

CREATE SERVER foreign_fake_database
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '....', port '5432', dbname 'fake_database');

When I create the user mapping to query this external database, I must write in clear, username and password:

CREATE USER MAPPING FOR fake_user SERVER foreign_fake_database
    OPTIONS ("user" 'fake_user', password 'fake_password');

This method seems fragile to me for obvious security reasons so I'm looking for users feedbacks.

What are the best practices to maintain a good level of security and not have the password stored in clear ? Can I encrypt this password? With multiple users, is it okay to use the same user to connect? Doesn't it overload the system or create conflict?

Answer 1

From a performance viewpoint, it doesn't matter if different users are mapped to the same or to different users on the remote server, this is purely a security consideration.

There is no way to hide or encrypt the password, but you can either use a password file to store the password on the server or use an authentication method that does not require a password at all, like certificate authentication (then you could use sslkey and sslcert in the user mapping).

Note that you must set password_required to false on the user mapping to allow a non-superuser to connect without an explicit password in the user mapping. This option was introduced in PostgreSQL v13.