2

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.

0
1

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.

2
  • I see now that I probably shouldn't have mentioned that the server is remote, since I first log into the server using ssh, so the connection to the database is technically local (will update the question). But good advice about the scram-sha-256, I will definitely rather use that then a password, and the select pg_reload_conf() is the answer I needed. Thanks!
    – Nanna
    Aug 21 '20 at 12:39
  • If you use scram-sha-256 you must first change the Postgres server to also store passwords with scram-sha-256. If you change that on the server, then you must set the password again for that user, in order to overwrite the current md5 hash. Aug 21 '20 at 12:41

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .