Cannot set password of user postgres despite running successful ALTER USER WITH PASSWORD query

Question

I have a brew-installed version of Postgres 12.3 running on my mac.

\du shows two users:

 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 facthna   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres  | Superuser, Create role, Create DB                          | {}

This command is all it takes to log me in:

psql -U postgres

Note it doesn't require a password.

Once logged in, I have tried this:

ALTER USER postgres WITH PASSWORD 'blah';

Which responds with:

ALTER ROLE

However logging out and logging back in still doesn't require a password.

I have also tried restarting the Postgres service but this makes no difference.

Please help.

Zegarek · Accepted Answer · 2020-06-30 12:41:27Z

You might want to check your pg_hba.conf which specifies what origins of connection to which database require what types of authorisation for which user. To locate pg_hba.conf file you can issue this command from within psql:

SHOW hba_file;

If upon inspection of the file you find something along the lines of

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

It means all local connections are trusted and therefore you're just skipping the password prompt when connecting locally.

To force the database to require the password, replace trust with md5 for the databases, users and origins of connection where you want this behaviour, like so:

host    all             all             127.0.0.1/32            md5

And then ask your database to apply the changes:

The pg_hba.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload, calling the SQL function pg_reload_conf(), or using kill -HUP) to make it re-read the file.

Collectives™ on Stack Overflow

Cannot set password of user postgres despite running successful ALTER USER WITH PASSWORD query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related