Enable SSL encryption and SCRAM-SHA-256 password verification in PostgreSQL

You can download this article in PDF format via the link below to support us.
Download the guide in PDF formatshut down

Due to more and more security breaches worldwide, current business needs continue to grow. As a result, every organization pulls up the socks while tightening the socks and minimizes the attack surface. Whether it’s an application, network, server, or database, security standards are key requirements that should not be evaded at all. Therefore, we will take a safe journey to protect your powerful, open source object-relational PostgreSQL database.

PostgreSQL is a powerful open source object-relational database system. It uses and extends the SQL language and combines many features that can safely store and expand the most complex data workloads. source: PostgreSQL page

We will study how to enable SSL/TLS connection with the PostgreSQL database by first enabling SSL on the database and then adding the certificate to the client for a secure connection. In addition, we will enable SCRAM-SHA-256 password authentication in the PostgreSQL database. To keep us all on the same page, we will describe SCRAM-SHA-256 password authentication.

The scram-sha-256 password authentication method performs SCRAM-SHA-256 authentication, such as RFC 7677. It is a challenge-response scheme that prevents password sniffing on untrusted connections and supports the storage of passwords on the server in the form of encrypted hashes, which is considered safe. source: PostgreSQL page

Now that the procedures no longer exist, we will proceed with SSL and SCRAM-SHA-256 password authentication according to the following steps.

Note: In this example, we will use CentOS. In different distributions, the displayed directory path may be different.

Step 1: Install PostgreSQL database

If you don’t have a PostgreSQL database, the guide listed below will enable it in your server as soon as possible.

Install PostgreSQL 13 on Fedora

How to install PostgreSQL 13 on CentOS/RHEL 8

Install PostgreSQL 13 on Debian

How to install PostgreSQL 13 on CentOS 7

After completion, we can continue to implement SSL on the PostgreSQL database.

Step 2: Create a certificate

PostgreSQL provides native support for using SSL connections to encrypt client/server communication to improve security. If you have not purchased a certificate, please create a server certificate whose identity can be verified by the client.To do this, first create a certificate signing request (CSR) and a public/private key file, as shown below

openssl req -new -nodes -text -out root.csr 
  -keyout root.key -subj "/CN=root.computingforgeeks.com"

Make sure you change “root.computingforgeeks.com“To match your domain name. After creating the root key, make sure its permissions are set as secure as possible. Make sure that “group” and “other” will not be able to read, write, and execute.

sudo chmod og-rwx root.key

Once done, use the key to sign the request to create a root certificate authority (using the default OpenSSL configuration file location on Linux). You can check the location of the OpenSSL configuration file on Linux by running the “openssl version -d” command.

openssl x509 -req -in root.csr -text -days 3650 
  -extfile /etc/pki/tls/openssl.cnf -extensions v3_ca 
  -signkey root.key -out root.crt

Finally, create a server certificate signed by the new root certification authority:

##Generate private server key and set secure permissions
openssl req -new -nodes -text -out server.csr 
  -keyout server.key -subj "/CN=datab.computingforgeeks.com"

sudo chmod og-rwx server.key

Then also create a server certificate signed by the new root certification authority

openssl x509 -req -in server.csr -text -days 365 
  -CA root.crt -CAkey root.key -CAcreateserial 
  -out server.crt

The above command will generate server.crt and server.key. These are the server certificates that we will add to PostgreSQL. On the other hand, root.crt should be stored on the client so that the client can verify that the server’s certificate has been signed by its trusted root certificate. Now, root.crt should be embedded in applications written in Java and any other programming language. For Java, it should be added to its keystore.

Keep root.key in a safe place, as it can be used to create future certificates.

For convenience, we copy the server and root certificate to the location where PostgreSQL keeps its main configuration file. This way, Postgres will be able to read them in the same place where it reads its configuration file. The location in CentOS is /var/lib/pgsql/13/data/ for PostgreSQL 13 (/var/lib/pgsql/12/data/ for PostgreSQL 12) and /var/lib/pgsql/11/data for PostgreSQL 12 /For PostgreSQL 11. In this example, we will use PostgreSQL 11.

sudo cp server.* root.crt /var/lib/pgsql/11/data/  ##For PostgreSQL 11

Step 3: Configure the PostgreSQL database to enable SSL

Now that we have the certificate, there is nothing to deny us the opportunity to enable SSL in the PostgreSQL server. According to the version of PostgreSQL database, open its configuration file. We will use version 11 as an example here. Find the commented ssl-related line in the file, and then uncomment it. These lines are shared below.

$ sudo vim /var/lib/pgsql/11/data/postgresql.conf 
ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'
ssl_prefer_server_ciphers = on
ssl_ecdh_curve = 'prime256v1'

After this is done, we need to enable the hostssl record in the pg_hba.conf file, which will match the connection attempt made using TCP/IP, but only when the connection is made using SSL encryption. Open the file and edit as follows:

$ sudo vim /var/lib/pgsql/11/data/pg_hba.conf
hostssl    all             all             10.38.87.40/32       md5   clientcert=verify-full
hostssl    all             all             10.38.87.41/32       md5   clientcert=verify-full
hostssl    all             all             10.38.87.42/32       md5   clientcert=verify-full
hostssl    all             all             10.38.87.43/32       md5   clientcert=verify-full

To keep the changes made so far, we need to restart the PostreSQL service like this:

sudo systemctl restart postgresql-<version>

Confirm that SSL is enabled

To ensure that the SSL settings are valid, we will log in to the database and check the message that will be displayed to us. You can run the following commands inside the server or from another server that has psql installed and allowed to connect to the database. You should see a message similar to the following, and SSL should be successfully enabled.

$ psql -U postgres -h 10.38.87.46 -d postgres
Password for user postgres: 
psql (11.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Step 4: Configure the PostgreSQL database to enable SCRAM-SHA-256

Now that SSL is enabled and working as configured, let’s now add another layer of security by enabling SCRAM-SHA-256 password authentication. This is a very simple process. First, open the main postgresql configuration file and change the following line to look like the following.

$ sudo vim /var/lib/pgsql/11/data/postgresql.conf
password_encryption = scram-sha-256

After this is done, we need to force the client to authenticate through this new scheme by editing the pg_hba.conf file, as shown below. You will notice that all occurrences of md5 have been replaced with scram-sha-256.

$ sudo vim /var/lib/pgsql/11/data/pg_hba.conf
hostssl    all             all             10.38.87.40/32      scram-sha-256   clientcert=verify-full
hostssl    all             all             10.38.87.41/32      scram-sha-256   clientcert=verify-full
hostssl    all             all             10.38.87.42/32      scram-sha-256   clientcert=verify-full
hostssl    all             all             10.38.87.43/32      scram-sha-256   clientcert=verify-full

To keep the changes made so far, we need to restart the PostreSQL service like this:

sudo systemctl restart postgresql-11

After making these changes, we will have to update the passwords of all users in the database, otherwise you will not be able to log in. To do this, please access the database as follows. You must have sudo privileges.

$ sudo su - postgres
Last login: Fri Feb 19 17:16:16 EAT 2021 on pts/0
-bash-4.2$ psql
psql (11.11)
Type "help" for help.

postgres=#

Then run the password command like this and press Enter. You will be prompted to enter the password twice. You can enter the same password as before. It will now be encrypted using the new scram-sha-256 scheme.

postgres=#password <db_user>
Enter new password:
Enter it again:
postgres=#

Once completed, the only step left is to add the certificate to the client application. For *nix systems, you can put the client certificate in ${user.home}/. postgresql/ directory. How to embed client certificates in various applications written in various languages ​​is beyond the scope of this guide. An example is provided in Java PostgreSQL JDBC documentation

When using the JDBC driver to connect, the connection string should conform to the following format.note ssl = true Option has been added.

jdbc:postgresql://<database-server-ip-or-fqdn>:<database-port>/<database-name>?ssl=true

##For example
jdbc:postgresql://datab.computingforgeeks.com:5432/all_cent_customers?ssl=true

Final words

As they continue to manage and manage their beloved systems, ensuring the security of the organization remains a top priority. As the custodian of its infrastructure, you are responsible for protecting, preventing and proactively fighting against any kind of vulnerability. Seal them as much as possible. We hope this guide is helpful to you, just in case, if you have other ideas, please feel free to suggest.

Finally, we thank you very much for visiting this website, and we will continue to thank you for your continued great support. Other guides you might like include:

SQLPad-Web-based SQL editor for MySQL / PostgreSQL / SQL Server

Best Microsoft SQL Server Books

The best books to learn Rust programming

You can download this article in PDF format via the link below to support us.
Download the guide in PDF formatshut down

Related Posts