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.