Adding a password authenticated user to a peer authenticated postgres setup not working 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 &lt;- 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 &quot;my_user&quot;

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.
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 &quot;password&quot; 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.