PostgreSQL – Adding Password Authenticated User to Peer Authenticated Setup

postgresql

I would like to add a read-only user to a postgres setup on a remote server with default peer authentication, that uses a password as an authentication method.

I create my user like this:

CREATE USER my_user WITH PASSWORD 'some_pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO my_user;

and I have added a rule to the pg_hba.conf file, stating that this particular user should be able to access the database using a password.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   my_db           my_user                                 password <- my new line
local   all             all                                     peer

And then I start psql from the command line like this:

psql -d my_db -U my_user -W

and provide 'some_pass' when prompted. This works fine locally, but on the server I get this error:

psql: FATAL:  Peer authentication failed for user "my_user"

Do I need to restart postgresql so that the changes in pg_hba.conf will take effect, or is there something else missing? I am reluctant to restart postgresql since it is being used on a production environment.

EDIT: I mention that the server is remote, but I log into it first using ssh before accessing the database, meaning that the connection to the database is in fact local.

Best Answer

Your user connects from a remote client host, so the authentication type, must be host, not local which is used for local connections using named pipes.

The "password" authenticaion method should not be used either, as it sends the password in cleartext. Use md5 instead (or if your Postgres server is configured for it, scram-sha-256)

To allow remote connections you also need to put in an IP address. If you know the IP address you could add that. Using 0.0.0.0/0 would allow a connection from any IP address.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host   my_db           my_user          0.0.0.0/0               md5

If the client computer is located in the same network as the Postgres server, samenet would be a safer choice than 0.0.0.0/0

To apply changes to pg_hba.conf you need to reload the configuration. A restart is not needed. Reloading can e.g. be done through select pg_reload_conf(); while being connected as the superuser.

Related Question