The PostgreSQL sslmode parameter plays a crucial role in establishing the SSL connections as it defines the level of security that is required when connecting to the PostgreSQL server. In addition, it allows the clients to specify the type of SSL connection as needed.
This tutorial explores how to configure and work with PostgreSQL using SSL and the sslmode parameter.
PostgreSQL SSLMode Parameter
In PostgreSQL, the sslmode parameter specifies the SSL encryption mode for client-server communication.
The parameter accepts the following values:
- Disable – It disables the SSL security.
- Allow – It tries to connect with SSL first but falls back to non-SSL if the server does not support it.
- Prefer – It tries to connect with SSL at first but falls back to non-SSL if the connection to the server cannot be established. This is similar to the “allow” value and is the default functionality.
- Require – This requires an SSL connection. If it is set to require, the connection is not established unless it’s under SSL.
- Verify-ca – It requires an SSL connection, and the server certificate is verified for the correct CA. This helps to prevent the attacks such as MITM attacks.
- Verify-full – It requires an SSL connection while the server certificate is verified for the correct CA, and the server hostname must match the one that is defined in the certificate. This is the most secure option.
The provided values are the accepted values for the sslmode parameter in PostgreSQL auth.
How to Use the PostgreSQL SSLMODE Parameter
The following steps describe how you can use the sslmode parameter in PostgreSQL. We assume that you have a PostgreSQL server installed on your machine.
Step 1: Generate the SSL Certificates
Start by generating the SSL server key, server certificates, and root certificate using the openssl tool. You can run the commands as follows:
openssl genpkey -algorithm RSA -out server.key -pkeyopt rsa_keygen_bits:2048
Secure the server key:
chmod 400 server.key
Create the server certificate:
openssl req -new -key server.key -out server.csr
Self-sign the server certificate:
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
Step 2: Configure the PostgreSQL Server to Use SSL
The next step is configuring the PostgreSQL server to support SSL and using the provided certificates and keys. We can do this by editing the PostgreSQL server configuration file.
Start by moving the generated certificates into the data directory of PostgreSQL which is typically located at /var/lib/postgresql/<version>/main.
For example:
Next, edit the postgresql.conf file and enable the server configuration file. Locate the following entries:
#ssl_cert_file = 'server.crt'
#ssl_key_file = 'server.key'
Uncomment the previous entries by removing the preceding “#” characters.
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
Once satisfied, save the changes to the configuration file and restart the PostgreSQL server.
Step 3: Connect to the PostgreSQL Server with a Specific SSL Mode
Once we configured the server to accept the SSL connections, the clients can specify the type of SSL connection that they want.
We do this by appending the sslmode parameter to the connection string or settings. The following are examples on how to specify the SSL modes in PSQL.
psql "dbname=postgres user=postgres host=localhost sslmode=disable"
Connect using the sslmode=require:
psql "dbname=postgres user=postgres host=localhost sslmode=require"
Connect using the sslmode=verify-ca:
psql "dbname=postgres user=postgres host=localhost sslmode=verify-ca sslrootcert=root.crt"
Connect using the sslmode=verify-full:
psql "dbname=postgres user=postgres host=localhost sslmode=verify-full sslrootcert=root.crt"
Replace the dbname, user, host, and sslrootcert with your target database name, username, host, and root certificate path, respectively.
Conclusion
This tutorial walked you through the steps of configuring the SSL authentication in PostgreSQL and defining the various SSL levels using the sslmode parameter.
