SSL certificate authentication
SSL certificate authentication lets a server verify that a client’s certificate is signed by one of the trusted certificate authorities (CA). You can also enable verification of a client’s certificate when using any other authentication method. To use SSL certificate authentication, you need to enable encryption of database connections.
For more information about configuring SSL on the client side, see the corresponding topic in the PostgreSQL documentation: SSL Support.
Prerequisites
The following hosts are used to illustrate how to enable SSL certificate authentication in Greengage DB:
-
Master host: FQDN —
mdw.example.com
. -
Client host: FQDN —
alice.example.com
, operating system — Windows, Linux, or macOS.
OpenSSL must be installed on both the master and client hosts.
Generate a client certificate
To learn how to generate a root certificate that can be used to sign server and client certificates, see Generate certificates. Then, you can create a client certificate, as shown below:
-
Generate a certificate signing request (CSR):
$ openssl req \ -newkey rsa:2048 \ -subj "/CN=alice" \ -nodes \ -keyout postgresql.key \ -out postgresql.csr
The
CN
(Common Name) value is set to the Greengage DB user name. If theCN
value differs from the Greengage DB user name, you need to configure the mapping between these values. -
Create a signed certificate for the client using the root certificate:
$ openssl x509 \ -req \ -in postgresql.csr \ -out postgresql.crt \ -CA root.crt \ -CAkey root.key \ -CAcreateserial \ -days 365
Configure SSL certificate authentication
Create a user
Create the alice
Greengage DB role:
$ createuser alice
Edit pg_hba.conf
The pg_hba.conf file specifies the connection types that are allowed for different users using the connection-type field.
SSL certificate authentication can be used for the hostssl
connection type only.
-
Open pg_hba.conf for editing:
$ vi $MASTER_DATA_DIRECTORY/pg_hba.conf
-
Add the following line to the file:
# connection-type database user address auth-method hostssl postgres alice .example.com cert
auth-method
is set tocert
and enables SSL certificate authentication foralice
. -
Save and close the file.
-
To apply the changes, restart the Greengage DB cluster:
$ gpstop -ra
You can now connect to a database as described in Connect to a database.
Map CN (Common Name) and user name
A server does not only verify the certificate chain but also checks whether a Greengage DB user name matches CN
of the provided certificate.
If a CN
value differs from the Greengage DB user name, configure the mapping between these values in the pg_ident.conf file.
For example, the pg_hba.conf configuration below specifies that user_map
maps a CN
value and a Greengage DB user name:
# connection-type database user address auth-method
hostssl postgres alice .example.com cert map=user_map
If the alice@example.com
value is used as CN
when generating a client certificate, map this CN
to the alice
Greengage DB user name in the pg_ident.conf file as follows:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
user_map alice@example.com alice
Check a client certificate when using other authentication methods
You can enable verification of a client’s certificate for any authentication method using the clientcert
authentication option.
The example below shows how to verify a client’s certificate when using password authentication:
# connection-type database user address auth-method
hostssl postgres alice .example.com password clientcert=1
Mapping a CN
value to a Greengage DB user name is not supported when using clientcert
for a client’s certificate verification.
Connect to a database
Log in to the client host.
Copy certificates to the client host
Copy client certificates (postgresql.key and postgresql.crt) to the following directory:
-
~/.postgresql/ for Linux or macOS;
-
%APPDATA%\postgresql\ for Windows.
For Linux and macOS you also need to change the permissions for the private key file:
$ chmod 600 ~/.postgresql/postgresql.key
Connect to a database using psql
To connect to a database, execute the psql
command with sslmode
set to require
:
$ psql "sslmode=require host=mdw.example.com dbname=postgres user=alice"
The output shows that the SSL connection is established and includes the SSL parameters in use:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=>
Additional actions might be required if you use client certificate verification with another authentication method. For example, provide a password if password authentication is used, create a ticket-granting ticket (TGT) if GSSAPI authentication is used, and so on.