CODEWITHSUNDEEP
postgresql
GeoGyro
GeoGyro

Reputation: 559

PostgreSQL - Foreign Data Wrapper user mapping password

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?

Upvotes: 4

Views: 6734

Answers (2)

autra
autra

Reputation: 962

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?

"A good level of security" depends on your constraints of course, but for what it's worth passwords in foreign user mappings are already hidden by default, you don't need to do anything. Only superuser can see it (a user cannot even see the password for their own user mapping).

On-disk I don't think it's encrypted. If you have read access to the server, you can always copy the data, start a cluster pointing on this data, connect as superuser and read it.

Apart from securing the server, if you want to encrypt the storage itself, then you may want to have a look at the official doc, even though I'm not sure their recommendation will work for a system table.

There is also pgsodium, but same, I'm not sure you can encrypt system column with it.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247235

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.

Upvotes: 2

Related Questions