Friday, February 4, 2022

[SOLVED] can't change postgres 10.4 user password on AWS ec2

Issue

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..?


Solution

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.



Answered By - Laurenz Albe
Answer Checked By - Dawn Plyler (WPSolving Volunteer)
at