CODEWITHSUNDEEP
postgresqlsslopensslamazon-rdspostgresql-9.6
YogeshR
YogeshR

Reputation: 1736

Requirement of Client certificates in PostgreSQL SSL

I am new to DBA role and setting up SSL for the first time. I have created a key and generated a certificate for testing using openssl with the help of this article. This article says to generate key and certificate for client also. i have generated that and tested for an on premises server.

we have a test aws rds instance and i have downloaded a certificate for RDS provided at this link. and i am able to connect to rds instance without using any client key/certificate using below command.

psql -h somehost.rds.amazonaws.com -p 5432     "dbname=test user=testuser sslrootcert=rds-ca-2019-root.pem sslmode=verify-full"

however, if i dont specify sslrootcert then i get below error 

psql: root certificate file "/root/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

i want to know why i didn't need a client key and certificate in case of AWS?

If we use certificate signed by a Certificate authority, we wont be needing client key and secret?

what i have to do extra to enable SSL in production if we want a single certificate for all clients?

I want to use sslmode=verify-full

Upvotes: 2

Views: 14550

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246103

Don't trust random articles, consult the official documentation.

As you have found, you need to use the connection string option sslmode=verify-full (or set the environment variable PGSSLMODE to verify-full) to verify the server certificate.

For that, you do not need client certificates. All you need is the certification authority (CA) certificate on the client, so that the client can verify that the server certificate was signed with that certificate.

Client certificates are only needed if you want the server to authenticate the client using certificates.

If you don't specify the CA certificate's path with either the sslrootcert connection string option or the PGSSLROOTCERT environment variable, the client will search the CA certificate in ~/.postgresql/root.crt, as detailed in the documentation.

Upvotes: 9

jjanes
jjanes

Reputation: 44137

Client certs are optional, and as far as I know are much rarer than server certs. You can demand a client cert by setting the authentication method to cert in pg_hba.conf, or by adding the clientcert option to some other authentication method. If you don't do either of those, then client certs are not used.

Upvotes: 1

Related Questions