PostgreSQL — SSL Configuration to Connect Database

What is SSL Connection in PostgreSQL

SSL (Secure Sockets Layer) is a protocol that provides secure communication over a network by establishing an encrypted connection between a client and a server. In the context of PostgreSQL, SSL can be used to secure the communication channel between a client application and a PostgreSQL database server.

When SSL is enabled for a PostgreSQL connection, the data transmitted between the client and the server is encrypted, making it difficult for unauthorized parties to intercept and understand the information. This helps protect sensitive data, such as usernames, passwords, and the actual content of the database queries, from being exposed.

Advantages of using SSL connections in PostgreSQL include:

1. Data confidentiality: SSL encryption ensures that the data exchanged between the client and the server remains confidential and cannot be easily deciphered by unauthorized individuals who may attempt to intercept the communication.
2. Data integrity: SSL also provides data integrity by using digital certificates to verify the authenticity of the server and ensure that the data transmitted has not been tampered with during transit.
3. Authentication: SSL allows the server to authenticate itself to the client using digital certificates. This helps ensure that the client is connecting to the intended server and not an impostor or a malicious entity.
4. Secure remote access: SSL enables secure communication between a client and a PostgreSQL database server over an untrusted network, such as the internet. This is particularly important when accessing the database remotely or when connecting from a public Wi-Fi network, where the risk of eavesdropping or data interception is higher.
5. Compliance requirements: Many regulatory standards and industry-specific compliance frameworks require the use of encryption for protecting sensitive data. By using SSL connections in PostgreSQL, organizations can meet these requirements and maintain compliance with data security standards.

It’s important to note that setting up and configuring SSL for PostgreSQL requires generating and managing digital certificates, which involves additional administrative tasks. However, the benefits of securing the communication channel make it worthwhile, especially when dealing with sensitive or confidential data.

Configure SSL Connection

• Generate private key files under PostgreSQL data directory.

su - postgres
cd /pgsql-data-directory
openssl genrsa -aes128 2048 > server.key
openssl rsa -in server.key -out server.key

• Configure file permission for private key.

chmod 400 server.key
chown postgres:postgres server.key

• Generate trusted root certificate

su - postgres
cd /pgsql-data-directory
openssl req -new -key server.key -days 365 -out server.crt -x509
cp server.crt root.crt

• Change PostgreSQL configurations to use SSL. Example configurations as follows;

ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
ssl_crl_file = ''
ssl_key_file = 'server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
ssl_prefer_server_ciphers = on

Note: If you’re not sure where the configuration file is located in instance you can use the following query to find configuration file’s location.

postgres=# show config_file ;
               config_file                
------------------------------------------
 /var/lib/postgresql/data/postgresql.conf
(1 row)

postgres=#

• Add PostgreSQL hba rule to accept SSL connections. Example hba rule as follows;

hostssl  all         all          0.0.0.0/0      md5

After all changes and configurations you can restart the PostgreSQL service to activate SSL connection.

systemctl restart postgresql

Note: If you are using additional tools to manage PostgreSQL you have to use the tool while configuring and restarting.

After these operations, connection to PostgreSQL will be possible with the SSL option. The important points to know and pay attention to are as follows.

• SSL can be used as needed when establishing a connection to PostgreSQL. So not all links are changed. Therefore, after completing the change at the database level, connection tests with SSL must be performed.
• The validity period of the certificates produced for the SSL connection should be checked. In this study, a 1-year certificate was produced. Since this certificate will be invalid after 1 year, all clients using it may get an error.

To make sure SSL is working you can check catalog tables in PostgreSQL. For example use pg_stat_ssl table.

test=# select * from pg_stat_ssl ;
 pid  | ssl | version |         cipher         | bits | client_dn | client_serial | issuer_dn 
------+-----+---------+------------------------+------+-----------+---------------+-----------
   54 | f   |         |                        |      |           |               | 
   55 | f   |         |                        |      |           |               | 
   63 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 |           |               | 
 1310 | f   |         |                        |      |           |               |

Finally, you can prefer SSL while connecting PostgreSQL. For example, you can use sslmode in your connection string. There is a sample Python example connection.

# Connection details
host = '172.17.0.2'
port = '5432'
database = 'test'
user = 'demo'
password = 'test123'
sslmode = 'require'  # Set SSL mode to require
conn = psycopg2.connect(host=host, port=port, database=database,
                        user=user, password=password, sslmode=sslmode)

If you ever have questions or need advice on database-related matters, feel free to reach out to me on LinkedIn, connect on Twitter or Superpeer.

Regards.

Demir.