Create user mapping without password how to configure authentication

Question

I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says.

local_db=> select * from employee;
ERROR:  could not connect to server "testmachine02"
DETAIL:  connection to server at "192.168.56.10", port 5432 failed: fe_sendauth: no password supplied

Here is the command that I used to create the user mapping:

CREATE USER MAPPING for app_user SERVER testmachine02 OPTIONS (password_required 'false');

I also created a pgpass file with the following entries:

localhost:5432:local_db:app_user:app_user123
192.168.56.10:5432:admin:admin:admin123
192.168.56.10:5432:remote_db:test:test123

Despite these steps, I am still unable to access the table without a password. How can I create a user mapping without a password and access the table?

here is what I am getting after creating a .pgpass file under /pgsql:

jjanes · Accepted Answer · 2023-02-18 12:33:57Z

1

The password file does not get proxied through your client to be used by server. The .pgpass file you need the password in is the one which would be used for the OS user who runs the database server. So often that would be something like "/var/lib/postgresql/.pgpass", not something like "/home/arahal/.pgpass". If you do this and set (password_required 'false'), it will work.

But why not just stick the password in the user mapping and be done with it? The purpose of password_required is to give you the option of using non-password-based authentication methods.

answered Feb 18, 2023 at 12:33

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

9 Comments

Aymen Rahal Over a year ago

thanks for you reponse, I am trying to save the password in the .pgpass file so no one can see the password like in here: local_db=# select * from pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+-------------------+--------+----------+--------------------------------- 26440 | 26439 | testmachine02_fdw | 26421 | app_user | {user=fdw_user,password=secret} (1 row)

Aymen Rahal Over a year ago

I created the .pgpass file under /var/lib/pgsql/14/.pgpass and I am still having the same issue.

Aymen Rahal Over a year ago

the local_db with user mapping are from local machine, the remote_db with the user "test" and "password" is another machine 192.168.56.10. the same data in the remote_db will appear in the local_db. the .pgpass file is in the local machine

jjanes Over a year ago

Unless you are a privileged user, pg_user_mappings.umoptions returns NULL. (And if you are a privileged user, you can probably read the .pgpass file directly anyway.)

jjanes Over a year ago

Once you su/sudo into the service account, can you then connect to the foreign machine using psql without manually specifying a password (using the just-created .pgpass to automatically supply it)?

|

Collectives™ on Stack Overflow

Create user mapping without password how to configure authentication

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related