0

I would like to perform queries over different postgresql11-databases. Therefore I created a data wrapper and a user mapping as following:

CREATE server 'test' foreign data wrapper postgres_fdw
OPTIONS (dbname 'name_db', host 'ip_adress');

CREATE USER MAPPING FOR "Userame"
SERVER 'test' OPTIONS (user 'Username', password '******');

Afterwards it is possible to query with db_link left join etc.. so far no problem. My question is now, how can I hide the passwords for the different users that are visible in foreign data wrappers-postgres_fdw-'test'-user mapping-options ???

3
  • The password should only be visible to select users, I think that would be superusers, and the "Userame" himself. Is that not good enough?
    – jjanes
    Mar 23, 2021 at 1:02
  • no, not good enough
    – oppia04
    Mar 24, 2021 at 11:29
  • Actually it's not even visible by the "Username" himself, only superuser. Good enough this time? ;-)
    – autra
    May 30, 2023 at 8:55

2 Answers 2

0

Create a .pgpass file on the postgres server in the $HOME directory of the postgres Linux user. You can then store the password of that database user in that file.

Then you can remove the password option from the user mapping, as Postgres (or more precisely: libpq) will pick up the password from the password file.

Don't forget to set password_required to 'false' on the user mapping unless the user mapping is for a superuser (which is an altogether awful thing to do).

11
  • The pgpass file with the different user passwords exists already on my pc (appdat\roaming\postgresql\pgpass.conf) from which I create the query. But when I create the user mapping without password and send the query I get the message: fe_sendauth: no password supplied
    – oppia04
    Mar 22, 2021 at 17:12
  • 1
    The password file needs to be on the server where Postgres is running, not on your local computer
    – user1822
    Mar 22, 2021 at 17:24
  • password_required doesn't need to be false here, because the password was required and used. It doesn't care where that password came from, as long as it was required.
    – jjanes
    Mar 23, 2021 at 1:19
  • @jjanes No, password_required needs to be set. A user mapping without password can only be used with blessing by the superuser. Mar 23, 2021 at 4:40
  • 1
    @oppia04 Local superuser don't need password_required, that is implicit behavior for them. But you do have to be a superuser in order to set it for other users, they can't set it for themselves. You would add it with ALTER USER MAPPING FOR...OPTIONS ( password_required 'false')
    – jjanes
    Mar 24, 2021 at 15:28
0

Passwords in foreign user mappings are already hidden by default, you don't need to do anything. Only superuser can see it, according to my tests.

Not even a user with a user mapping can see it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.