4.5. Configuring TLS encryption on a PostgreSQL server

PDF

By default, PostgreSQL uses unencrypted connections. For more secure connections, you can enable Transport Layer Security (TLS) support on the PostgreSQL server and configure your clients to establish encrypted connections.

Conditions préalables

The PostgreSQL server is installed.
The database cluster is initialized.

Procédure

Install the OpenSSL library:
```
# dnf install openssl
```

Generate a TLS certificate and a key:

# openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"

Replace dbhost.yourdomain.com with your database host and domain name.

Copy your signed certificate and your private key to the required locations on the database server:
```
# cp server.{key,crt} /var/lib/pgsql/data/.
```
Change the owner and group ownership of the signed certificate and your private key to the postgres user:
```
# chown postgres:postgres /var/lib/pgsql/data/server.{key,crt}
```
Restrict the permissions for your private key so that it is readable only by the owner:
```
# chmod 0400 /var/lib/pgsql/data/server.key
```
Set the password hashing algorithm to scram-sha-256 by changing the following line in the /var/lib/pgsql/data/postgresql.conf file:
```
#password_encryption = md5              # md5 or scram-sha-256
```
à :
```
password_encryption = scram-sha-256
```
Configure PostgreSQL to use SSL/TLS by changing the following line in the /var/lib/pgsql/data/postgresql.conf file:
```
#ssl = off
```
à :
```
ssl=on
```
Restrict access to all databases to accept only connections from clients using TLS by changing the following line for the IPv4 local connections in the /var/lib/pgsql/data/pg_hba.conf file:
```
host		all		all		127.0.0.1/32		ident
```
à :
```
hostssl 	all		all		127.0.0.1/32		scram-sha-256
```
Alternatively, you can restrict access for a single database and a user by adding the following new line:
```
hostssl	mydatabase	mydbuser	127.0.0.1/32		scram-sha-256
```
Replace mydatabase with the database name and mydbuser with the username.
Make the changes effective by restarting the postgresql service:
```
# systemctl restart postgresql.service
```

Vérification

To manually verify that the connection is encrypted:

Connect to the PostgreSQL database as the mydbuser user, specify the host name and the database name:
```
$ psql -U mydbuser -h 127.0.0.1 -d mydatabase
Password for user mydbuser:
```
Replace mydatabase with the database name and mydbuser with the username.

Obtain information about the current database connection:

mydbuser=> \conninfo
You are connected to database "mydatabase" as user "mydbuser" on host "127.0.0.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

You can write a simple application that verifies whether a connection to PostgreSQL is encrypted. This example demonstrates such an application written in C that uses the libpq client library, which is provided by the libpq-devel package:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main(int argc, char* argv[])
{
//Create connection
PGconn* connection = PQconnectdb("hostaddr=127.0.0.1 password=mypassword port=5432 dbname=mydatabase user=mydbuser");

if (PQstatus(connection) ==CONNECTION_BAD)
    {
    printf("Connection error\n");
    PQfinish(connection);
    return -1; //Execution of the program will stop here
    }
    printf("Connection ok\n");
    //Verify TLS
    if (PQsslInUse(connection)){
     printf("TLS in use\n");
     printf("%s\n", PQsslAttribute(connection,"protocol"));
    }
    //End connection
    PQfinish(connection);
    printf("Disconnected\n");
    return 0;
}

Replace mypassword with the password, mydatabase with the database name, and mydbuser with the username.

Note

You must load the pq libraries for compilation by using the -lpq option. For example, to compile the application by using the GCC compiler:

$ gcc source_file.c -lpq -o myapplication

where the source_file.c contains the example code above, and myapplication is the name of your application for verifying secured PostgreSQL connection.

Exemple 4.4. Initializing, creating, and connecting to a PostgreSQL database using TLS encryption

This example demonstrates how to initialize a PostgreSQL database, create a database user and a database, and how to connect to the database using a secured connection.

Install the PosgreSQL server:
```
# dnf install postgresql-server
```

Initialize the database cluster:

# postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Install the OpenSSL library:
```
# dnf install openssl
```

Generate a TLS certificate and a key:

# openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"

Replace dbhost.yourdomain.com with your database host and domain name.

Copy your signed certificate and your private key to the required locations on the database server:
```
# cp server.{key,crt} /var/lib/pgsql/data/.
```
Change the owner and group ownership of the signed certificate and your private key to the postgres user:
```
# chown postgres:postgres /var/lib/pgsql/data/server.{key,crt}
```
Restrict the permissions for your private key so that it is readable only by the owner:
```
# chmod 0400 /var/lib/pgsql/data/server.key
```
Set the password hashing algorithm to scram-sha-256. In the /var/lib/pgsql/data/postgresql.conf file, change the following line:
```
#password_encryption = md5              # md5 or scram-sha-256
```
à :
```
password_encryption = scram-sha-256
```
Configure PostgreSQL to use SSL/TLS. In the /var/lib/pgsql/data/postgresql.conf file, change the following line:
```
#ssl = off
```
à :
```
ssl=on
```
Start the postgresql service:
```
# systemctl start postgresql.service
```
Log in as the system user named postgres:
```
# su - postgres
```

Start the PostgreSQL interactive terminal as the postgres user:

$ psql -U postgres
psql (13.7)
Type "help" for help.

postgres=#

Create a user named mydbuser and set a password for mydbuser:

postgres=# CREATE USER mydbuser WITH PASSWORD 'mypasswd';
CREATE ROLE
postgres=#

Create a database named mydatabase:

postgres=# CREATE DATABASE mydatabase;
CREATE DATABASE
postgres=#

Grant all permissions to the mydbuser user:

postgres=# GRANT ALL PRIVILEGES ON DATABASE mydatabase TO mydbuser;
GRANT
postgres=#

Log out of the interactive terminal:
```
postgres=# \q
```
Log out of the postgres user session:
```
$ logout
```
Restrict access to all databases to accept only connections from clients using TLS by changing the following line for the IPv4 local connections in the /var/lib/pgsql/data/pg_hba.conf file:
```
host		all		all		127.0.0.1/32		ident
```
à :
```
hostssl 	all		all		127.0.0.1/32		scram-sha-256
```
Make the changes effective by restarting the postgresql service:
```
# systemctl restart postgresql.service
```

Connect to the PostgreSQL database as the mydbuser user, specify the host name and the database name:

$ psql -U mydbuser -h 127.0.0.1 -d mydatabase
Password for user mydbuser:
psql (13.7)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

mydatabase=>

4.5. Configuring TLS encryption on a PostgreSQL server

Apprendre

Essayez, achetez et vendez

Communautés

À propos de la documentation Red Hat

Rendre l’open source plus inclusif

À propos de Red Hat

Red Hat legal and privacy links

Red Hat legal and privacy links