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?

If you store the password in the .pgpass file on the server where you create the user mapping, then you can omit the password in the user mapping — user330315
– user330315, Commented Sep 8, 2021 at 9:26
The 'obvious security reasons' are not obvious. Are you worried about someone capturing the hard drive or a backup? Shoulder surfing your 'create user mapping' command, are spotting it in some log file? Running \deu? — jjanes
– jjanes, Commented Sep 8, 2021 at 14:07
Never store or write a password in clear anywhere, that seems to me a such fundamental security rule that it's 'obvious'. — GeoGyro
– GeoGyro, Commented Sep 8, 2021 at 16:17

Laurenz Albe · Accepted Answer · 2021-09-08 12:09:29Z

2

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.

edited Sep 8, 2021 at 12:09

answered Sep 8, 2021 at 9:29

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GeoGyro Over a year ago

Ok thanks, I need to read documentation with more attention.

autra · Accepted Answer · 2023-05-30 09:04:40Z

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.

Collectives™ on Stack Overflow

PostgreSQL - Foreign Data Wrapper user mapping password

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related