4.5. Configuring TLS encryption on a PostgreSQL server

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
```
```
# dnf install openssl
```
Copy to Clipboard Toggle word wrap

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"

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

Copy to Clipboard

Toggle word wrap

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/.
```
```
# cp server.{key,crt} /var/lib/pgsql/data/.
```
Copy to Clipboard Toggle word wrap
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}
```
```
# chown postgres:postgres /var/lib/pgsql/data/server.{key,crt}
```
Copy to Clipboard Toggle word wrap
Restrict the permissions for your private key so that it is readable only by the owner:
```
chmod 0400 /var/lib/pgsql/data/server.key
```
```
# chmod 0400 /var/lib/pgsql/data/server.key
```
Copy to Clipboard Toggle word wrap
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 = md5              # md5 or scram-sha-256
```
Copy to Clipboard Toggle word wrap
à :
```
password_encryption = scram-sha-256
```
```
password_encryption = scram-sha-256
```
Copy to Clipboard Toggle word wrap
Configure PostgreSQL to use SSL/TLS by changing the following line in the /var/lib/pgsql/data/postgresql.conf file:
```
#ssl = off
```
```
#ssl = off
```
Copy to Clipboard Toggle word wrap
à :
```
ssl=on
```
```
ssl=on
```
Copy to Clipboard Toggle word wrap
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
```
```
host		all		all		127.0.0.1/32		ident
```
Copy to Clipboard Toggle word wrap
à :
```
hostssl 	all		all		127.0.0.1/32		scram-sha-256
```
```
hostssl 	all		all		127.0.0.1/32		scram-sha-256
```
Copy to Clipboard Toggle word wrap
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
```
```
hostssl	mydatabase	mydbuser	127.0.0.1/32		scram-sha-256
```
Copy to Clipboard Toggle word wrap
Replace mydatabase with the database name and mydbuser with the username.
Make the changes effective by restarting the postgresql service:
```
systemctl restart postgresql.service
```
```
# systemctl restart postgresql.service
```
Copy to Clipboard Toggle word wrap

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
```
```
$ psql -U mydbuser -h 127.0.0.1 -d mydatabase
Password for user mydbuser:
```
Copy to Clipboard Toggle word wrap
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)

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)

Copy to Clipboard

Toggle word wrap

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;
}

#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;
}

Copy to Clipboard

Toggle word wrap

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

$ gcc source_file.c -lpq -o myapplication

Copy to Clipboard

Toggle word wrap

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
```
```
# dnf install postgresql-server
```
Copy to Clipboard Toggle word wrap

Initialize the database cluster:

postgresql-setup --initdb

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

Copy to Clipboard

Toggle word wrap

Install the OpenSSL library:
```
dnf install openssl
```
```
# dnf install openssl
```
Copy to Clipboard Toggle word wrap

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"

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

Copy to Clipboard

Toggle word wrap

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/.
```
```
# cp server.{key,crt} /var/lib/pgsql/data/.
```
Copy to Clipboard Toggle word wrap
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}
```
```
# chown postgres:postgres /var/lib/pgsql/data/server.{key,crt}
```
Copy to Clipboard Toggle word wrap
Restrict the permissions for your private key so that it is readable only by the owner:
```
chmod 0400 /var/lib/pgsql/data/server.key
```
```
# chmod 0400 /var/lib/pgsql/data/server.key
```
Copy to Clipboard Toggle word wrap
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 = md5              # md5 or scram-sha-256
```
Copy to Clipboard Toggle word wrap
à :
```
password_encryption = scram-sha-256
```
```
password_encryption = scram-sha-256
```
Copy to Clipboard Toggle word wrap
Configure PostgreSQL to use SSL/TLS. In the /var/lib/pgsql/data/postgresql.conf file, change the following line:
```
#ssl = off
```
```
#ssl = off
```
Copy to Clipboard Toggle word wrap
à :
```
ssl=on
```
```
ssl=on
```
Copy to Clipboard Toggle word wrap
Start the postgresql service:
```
systemctl start postgresql.service
```
```
# systemctl start postgresql.service
```
Copy to Clipboard Toggle word wrap
Log in as the system user named postgres:
```
su - postgres
```
```
# su - postgres
```
Copy to Clipboard Toggle word wrap
Start the PostgreSQL interactive terminal as the postgres user:
```
psql -U postgres
```
```
$ psql -U postgres
psql (13.7)
Type "help" for help.

postgres=#
```
Copy to Clipboard Toggle word wrap

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

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

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

Copy to Clipboard

Toggle word wrap

Create a database named mydatabase:

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

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

Copy to Clipboard

Toggle word wrap

Grant all permissions to the mydbuser user:

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

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

Copy to Clipboard

Toggle word wrap

Log out of the interactive terminal:
```
postgres=# \q
```
```
postgres=# \q
```
Copy to Clipboard Toggle word wrap
Log out of the postgres user session:
```
logout
```
```
$ logout
```
Copy to Clipboard Toggle word wrap
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
```
```
host		all		all		127.0.0.1/32		ident
```
Copy to Clipboard Toggle word wrap
à :
```
hostssl 	all		all		127.0.0.1/32		scram-sha-256
```
```
hostssl 	all		all		127.0.0.1/32		scram-sha-256
```
Copy to Clipboard Toggle word wrap
Make the changes effective by restarting the postgresql service:
```
systemctl restart postgresql.service
```
```
# systemctl restart postgresql.service
```
Copy to Clipboard Toggle word wrap

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

$ 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=>

Copy to Clipboard

Toggle word wrap

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

Theme

Red Hat legal and privacy links

Red Hat legal and privacy links