0

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: enter image description here

Aymen Rahal
  • 115
  • 1
  • 10

1 Answers1

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • 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 Feb 18 '23 at 15:11
  • I created the .pgpass file under /var/lib/pgsql/14/.pgpass and I am still having the same issue. – Aymen Rahal Feb 18 '23 at 15:28
  • 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 – Aymen Rahal Feb 18 '23 at 15:33
  • 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 Feb 18 '23 at 20:42
  • @AymenRahal U don't know how you installed PostgreSQL (what package manager) but I wouldn't expect the correct location to have a version number in it. When you do `sudo su - postgres`, what directory do you land in? That is probably the place to put the file. – jjanes Feb 18 '23 at 20:58
  • pwd gives this :/var/lib/pgsql I created a .pgpass file under this directory with chmod 600 with owner postgres. but stil having the same error. – Aymen Rahal Feb 19 '23 at 08:56
  • 1
    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)? – jjanes Feb 19 '23 at 14:16
  • nope, once trying to do psql will ask for password of the user postgres, even to connect to the local machine, but if I connect from the outside means "psql -U postgres" working fine it will check the /root/.pgpass file – Aymen Rahal Feb 19 '23 at 17:07
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/251988/discussion-between-aymen-rahal-and-jjanes). – Aymen Rahal Feb 19 '23 at 19:25