In PostgreSQL , can certificate authentication and basic authentication(username/password authentication) co-exist at one time?

Question

Can we configure both Basic authentication and Certificate authentication for PostgreSQL simultaneously for the same IP address of client. I think no , because they are 2 different authentication mechanisms and cannot co-exist at same time... because if we configure for certificate authentication , then it cannot use username password for authentication , but have to use only certificates for authentication.

Please help.

Answer 1

I tried myself. In pg_hba.conf file, we can set MD5 authentication for 1 set of IP address and cert for another IP address range. So 1 Postgres server can address 2 different authentication for 2 different IP address

Answer 2

Yes, you can specify clientcert in conjunction with any of the authentication methods. so md5 clientcert=verify-full would require both a cert and a password.

verify-full was only introduced in v12. Prior to that, you could only make it check that the cert was validly signed, not that the CN on the cert matched the username being claimed.

Answer 3

Basic Auth and SSL can be achieved with postgres groups and roles. Follow below steps-

Create new group

psql -U postgres -d postgres -c "CREATE GROUP testcertificateusers";

Alter group to add new users/CN/roles-

psql -U postgres -d postgres -c "ALTER GROUP testcertificateusers ADD USER user";

To see members of group

psql -U postgres -d postgres -c "SELECT * FROM pg_roles";

psql -U postgres -d postgres -c "SELECT * FROM pg_auth_members";
#(oid column from above query will be shown as a members of the group)

Update pg_hba.conf file

Add hostssl line before ipv4 host connection

# TYPE  DATABASE        USER            ADDRESS                 METHOD

hostssl all +testcertificateusers 0.0.0.0/0  cert clientcert=1

host    all             all             0.0.0.0/0               md5