GitHub

SSL certificate authentication

Andrey Aksenov

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:

  1. 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 the CN value differs from the Greengage DB user name, you need to configure the mapping between these values.

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

  1. Open pg_hba.conf for editing:

    $ vi $MASTER_DATA_DIRECTORY/pg_hba.conf
  2. Add the following line to the file:

    # connection-type  database  user   address       auth-method
    hostssl            postgres  alice  .example.com  cert

    auth-method is set to cert and enables SSL certificate authentication for alice.

  3. Save and close the file.

  4. 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
NOTE

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.