postgresql

PostgreSQL

Setting up a testing environment

Create user: sudo -u postgres createuser -s -P ryan

Edit /etc/postgresql/.../postgresql.conf:

• add listen_addresses = '*'

Edit /etc/postgresql/.../pg_hba.conf:

• add host all all 255.255.255.255/0 md5 as last line

Set up SCRAM authentication:

Reference: http://hacksoclock.blogspot.com/2018/10/how-to-set-up-scram-sha-256.html

Edit /etc/postgresql/.../pg_hba.conf:

• edit last line to host all all 255.255.255.255/0 scram-sha-256

Update user password:

set password_encryption = 'scram-sha-256';
alter role ryan with password 'XXX';

Set up multiple users for multiple auth methods:

Edit /etc/postgresql/.../pg_hba.conf:

host all +password_users 0.0.0.0/0 password
host all +md5_users 0.0.0.0/0 md5
host all +scram_users 0.0.0.0/0 scram-sha-256 
host all +pam_users 0.0.0.0/0 pam

Log in as a superuser:

create role password_users nologin;
create role md5_users nologin;
create role scram_users nologin;
create role pam_users nologin;

create user ryan_password in role password_users password 'XXX';
create user ryan_md5 in role md5_users password 'XXX';
set password_encryption = 'scram-sha-256';
create user ryan_scram in role scram_users password 'XXX';
create user ryan_pam in role pam_users;

Note: PAM authentication doesn't work without more setup, because postgresql doesn't run as root, so it can't check the passwords of arbitrary users. But this is enough to check that pam leads to a cleartext password request.