I want to add password protection to my psql db that I have set up on an Amazon Linux ec2 server. I only want the database to be accessible through the server instance(I am connecting to server via putty), and only with password authentication.
Previously, my pg_hba.conf (located at /var/lib/pgsql/data/) looked like this (USER: all, METHOD: trust):
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres trust # IPv4 local connections: host all postgres 127.0.0.1/32 trust # IPv6 local connections: host all postgres ::1/128 trust # replication privilege. local replication postgres trust host replication postgres 127.0.0.1/32 trust host replication postgres ::1/128 trust
To secure it, I’ve changed it to this (USER: postgres, METHOD: scram-sha-256):
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all scram-sha-256 # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all scram-sha-256 host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256
To set password, I have used (to get into postgres terminal):
[ec2-user@AWS]: sudo -u postgres psql
Then i run:
postgres=# ALTER ROLE postgres PASSWORD 'new_password';
And i receive:
ALTER ROLE
Then when i exit the postgres terminal and change to postgres user with:
[ec2-user@AWS]: su - postgres
I am prompted to enter a password. I enter the previously set:
Password: 'new_password'
And i get:
su: Authentification failure
What am I missing..?
Advertisement
Answer
You habe to set password_encryption
to scram-sha-256
and reload the server before changing your password. scram-sha-256
authentication only works with a scram-sha-256
-hashed password.