Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

Encryption of database connections

Andrey Aksenov

Greengage DB (based on Greenplum) allows you to encrypt connections between clients and a coordinator host using SSL. It is recommended to enable SSL for all clients connecting to a coordinator host through insecure links.

For more information about securing connections with SSL, see the corresponding topic in the PostgreSQL documentation: Secure TCP/IP connections with SSL.

NOTE

If you need to encrypt connections between Greengage DB cluster hosts, use a third-party solution, such as IPsec.

Prerequisites

The following hosts are used to illustrate how to enable SSL encryption in Greengage DB:

  • Coordinator host: FQDN — cdw.example.com, IP — 192.168.1.10.

  • Client host: FQDN — alice.example.com, operating system — Windows, Linux, or macOS.

OpenSSL must be installed on both the coordinator and client hosts.

Generate certificates

The coordinator host should use a certificate signed by a certificate authority (CA) to let clients verify the server’s identity. This might be a global CA (such as GlobalSign or Let’s Encrypt) or a local CA used to issue certificates within your organization. In this topic, a self-signed CA certificate is used to generate SSL certificates for demonstration purposes.

Log in to the coordinator host

  1. Log in to the coordinator host as gpadmin and go to the home directory.

  2. Create the certs directory and enter it:

    $ mkdir certs && cd certs

Generate a root certificate authority

  1. Generate a private key:

    $ openssl genrsa -out root.key 2048

  2. Create a self-signed CA certificate:

    $ openssl req \
  -x509 \
  -subj "/CN=ExampleCorp Root CA" \
  -key root.key \
  -days 365 \
  -out root.crt

Generate a server certificate

  1. Generate a server private key:

    $ openssl genrsa -out server.key 2048

  2. Generate a certificate signing request (CSR):

    $ openssl req \
  -new \
  -key server.key \
  -subj "/CN=cdw.example.com" \
  -out server.csr

    The CN (Common Name) certificate identifier is a fully qualified domain name (FQDN) of the coordinator host.

  3. Create and sign a server certificate using the private key and the CA certificate:

    $ openssl x509 \
  -req \
  -in server.csr \
  -extfile <(printf "subjectAltName=DNS:cdw.example.com,IP:192.168.1.10") \
  -days 365 \
  -CA root.crt \
  -CAkey root.key \
  -CAcreateserial \
  -out server.crt

Generate a client certificate

If you plan to use the cert authentication method, generate a client certificate as described in Generate a client certificate.

Update permissions

Change the permissions for the generated files as follows:

$ chmod 600 root.key server.key
$ chmod 644 root.crt server.crt

Configure SSL

To enable SSL, specify the paths to SSL server files in the postgresql.conf configuration file. The default location for such files is the $COORDINATOR_DATA_DIRECTORY directory. If coordinator mirroring is enabled, SSL server files should not be placed in the default directory. When gpinitstandby is executed, the content of $COORDINATOR_DATA_DIRECTORY is copied from the coordinator to the standby coordinator. Since SSL files are specific to each host, this might cause issues when a standby coordinator starts.

Edit postgresql.conf

  1. Open the postgresql.conf file for editing:

    $ vi $COORDINATOR_DATA_DIRECTORY/postgresql.conf

  2. Set the ssl option value to on and provide the paths to SSL files as follows:

    ssl=on
ssl_ca_file='/home/gpadmin/certs/root.crt'
ssl_cert_file='/home/gpadmin/certs/server.crt'
ssl_key_file='/home/gpadmin/certs/server.key'

    The following options are specified:

    • ssl_ca_file — the path to a file containing the SSL server certificate authority (CA).

    • ssl_cert_file — the path to a file containing the SSL server certificate.

    • ssl_key_file — the path to a file containing the SSL server private key.

  3. Save and close the file.

Reload the configuration

  1. Reload the configuration using gpstop to apply the changes:

    $ gpstop -u

  2. When the cluster is restarted, check if SSL is enabled using gpconfig:

    $ gpconfig -s ssl

    The result should look like this:

    Values on all segments are consistent
GUC              : ssl
Coordinator value: on
Segment     value: off

Allow SSL connections

Create a user

Create the alice Greengage DB role with a password:

$ createuser --pwprompt alice

When prompted, enter a password for the user:

Enter password for new role:
Enter it again:

Edit pg_hba.conf

The pg_hba.conf file specifies the connection types that are allowed for different users using the connection-type field. You can use SSL encryption for the host and hostssl connection types:

  • host allows both unencrypted and encrypted connections. The chosen connection type depends on the sslmode value set on the client side.

  • hostssl allows encrypted connections only.

Follow the steps below to allow a user to establish an SSL connection:

  1. Open pg_hba.conf for editing:

    $ vi $COORDINATOR_DATA_DIRECTORY/pg_hba.conf

  2. Add the following line to the file:

    # connection-type  database  user   address       auth-method
host               postgres  alice  .example.com  password

    Note the following:

    • connection-type is set to host and allows both unencrypted and encrypted connections for the alice user.

    • auth-method is set to password and enables password authentication for alice.

  3. Save and close the file.

  4. Reload the configuration using gpstop to apply the changes:

    $ gpstop -u

Connect to a database

Log in to the client host.

Copy a root certificate to the client host

If you want the client to verify the server’s certificate, copy the root.crt file to the following directory:

  • ~/.postgresql/ for Linux or macOS;

  • %APPDATA%\postgresql\ for Windows.

For more information about configuring SSL on the client side, see the corresponding topic in the PostgreSQL documentation: SSL Support.

Connect to a database using psql

To connect to a database, execute the psql command with sslmode set to require:

$ psql "sslmode=require host=cdw.example.com dbname=postgres user=alice"

Enter the alice user’s password and press Enter. The output shows that the SSL connection is established and includes the SSL parameters in use:

psql (13.23, server 12.22)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

Verify the server’s certificate

To verify the server’s certificate, set sslmode to verify-ca when connecting to the database:

$ psql "sslmode=verify-ca host=cdw.example.com dbname=postgres user=alice"

If sslmode is set to verify-full, the client also verifies that the server host name matches the name stored in the server certificate:

$ psql "sslmode=verify-full host=cdw.example.com dbname=postgres user=alice"
Previous
PAM
Next
Roles and privileges