Current business needs continue to advance owing to the increased security breaches worldwide. As a result, every organization pulls up their socks as they tighten and reduce their attack surface as much as possible. Be it applications, networks, servers or databases, security standards are key requirements that should not be evaded at all. As a result of that we are going on a journey involving securing your powerful, open source object-relational PostgreSQL database.
PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. Source: PostgreSQL Page
We are going to look at how to enable SSL/TLS connection to your PostgreSQL database by first enabling SSL on the database then adding the certificate to the client for secure connection. Additionally, we are going to enable SCRAM-SHA-256 password authentication in our PostgreSQL database. So that we are all on the same page, we shall describe SCRAM-SHA-256 password authentication.
The scram-sha-256 password authentication method performs SCRAM-SHA-256 authentication, as described in RFC 7677. It is a challenge-response scheme that prevents password sniffing on untrusted connections and supports storing passwords on the server in a cryptographically hashed form that is thought to be secure. Source: PostgreSQL Page
Now that the formalities are out of the way, we shall proceed to enabling SSL and SCRAM-SHA-256 password authentication by following the steps below.
NOTE: We shall be using CentOS for this example, the directory paths presented may differ in different distributions.
Step 1: Install PostgreSQL database
In case you do not have a PostgreSQL database, the guides listed below will bring it up 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 you are done, we can proceed to implement SSL on your PostgreSQL database.
Step 2: Create Certificates
PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. In case you do not have purchased certificates, create a server certificate whose identity can be validated by clients. To do that, first create a certificate signing request (CSR) and a public/private key file like so
openssl req -new -nodes -text -out root.csr \
-keyout root.key -subj "/CN=root.geeksforgeeks.org"Make sure you change “root.geeksforgeeks.org” to match your domain name. After the root key has been created, make sure its permissions are set as secure as possible. Make sure “group” and “others” will not be able to read, write and execute.
sudo chmod og-rwx root.keyAfter that is done, sign the request with the key to create a root certificate authority (using the default OpenSSL configuration file location on Linux). You can check OpenSSL configuration file location on Linux by running “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.crtFinally, create a server certificate signed by the new root certificate authority:
\##Generate private server key and set secure permissions
openssl req -new -nodes -text -out server.csr \
-keyout server.key -subj "/CN=datab.geeksforgeeks.org"
sudo chmod og-rwx server.key
Then also create the server certificate signed by the new root certificate authority
openssl x509 -req -in server.csr -text -days 365 \
-CA root.crt -CAkey root.key -CAcreateserial \
-out server.crtThe above commands will produce server.crt and server.key. These are the server certificates that we shall be adding to PostgreSQL. On the other hand, root.crt should be stored on the client so the client can verify that the server’s certificate was signed by its trusted root certificate. root.crt should now be embedded in your applications written in Java and any other programming language. For Java, this should be added in its keystore.
Keep root.key in a secure location for it can be used in creating future certificates.
For convenience, we shall copy the server and root certificates into the place PostgreSQL keeps its main configuration files. That way, Postgres will be able to read them in the same location it reads its configuration files. The locations in CentOS are /var/lib/pgsql/13/data/ for PostgreSQL 13, /var/lib/pgsql/12/data/ for PostgreSQL 12 and /var/lib/pgsql/11/data/ for PostgreSQL 11. We shall use PostgreSQL 11 in this example.
sudo cp server.* root.crt /var/lib/pgsql/11/data/ ##For PostgreSQL 11
Step 3: Configure PostgreSQL database to enable SSL
Now that we have our certificates, nothing denies us the chance of enabling SSL in our PostgreSQL Server. Depending on the version of your PostgreSQL Database, open up its configuration file. We shall use version 11 as an example here. Look for the ssl related lines that are commented in the file and uncomment them. The 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 that is done, we need to enable hostssl record in pg_hba.conf file which will match connection attempts made using TCP/IP, but only when the connection is made with 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 persist the changes we have made thus far, we need to restart PostreSQL service thus:
sudo systemctl restart postgresql-<version>Verify that SSL is enabled
To make sure that the SSL settings are working, we are going to login to the database and check the message it will present to us. You can run the command below inside the server or from another server with psql installed and allowed to connect to the database. You should see a message like the one shared below 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 PostgreSQL database to enable SCRAM-SHA-256
Now that SSL is enabled and working as configured, let us now add another layer of security by enabling SCRAM-SHA-256 password authentication. This is a very simple process. First, open main postgresql configuration file and change the line below to be as shown.
$ sudo vim /var/lib/pgsql/11/data/postgresql.conf
password_encryption = scram-sha-256Once that is done, we need to enforce clients to authenticate via this new scheme by editing 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-fullTo persist the changes we have made thus far, we need to restart PostreSQL service thus:
sudo systemctl restart postgresql-11Once those changes have been made, we will have to update passwords for all users in your database or you will not be able to login. To do so, access your database as follows. You must have sudo rights.
$ 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 so, then press enter. You will be prompted to enter the password twice. You can enter the same same password that was there 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 that is done, the only step remaining is to add certificates to the client applications. For *nix systems, you can place the client certificate under ${user.home}/.postgresql/ directory. How to embed the client certificate in various applications written in various languages is beyond the scope of this guide. An example for Java has been provided in PostgreSQL JDBC Documentation
When making connection using JDBC driver, the connection string should conform to the following format. Notice the ssl=true option added.
jdbc:postgresql://<database-server-ip-or-fqdn>:<database-port>/<database-name>?ssl=true
##For example
jdbc:postgresql://datab.geeksforgeeks.org:5432/all_cent_customers?ssl=trueGood Video courses to Learn PostgreSQL Administration:
- SQL and PostgreSQL: The Complete Developer’s Guide
- The Complete Python/PostgreSQL Course 2.0
- SQL & PostgreSQL for Beginners: Become an SQL Expert
- Learn SQL Using PostgreSQL: From Zero to Hero
- PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours
Last Words
Ensuring your organization remains secure should remain top priority as you continue to administer and steward their dear systems. As a custodian of their infrastructure, it is incumbent upon you to protect, prevent and pro-actively fight against any form of vulnerability. Seal them as much as you can. We hopw the guide was beneficial and in case you have further ideas, feel entirely free to point them out.
Finally, we are very grateful for visiting the site and we continue to appreciate the colossal support you relentlessly extend. Other guides you might enjoy include:
SQLPad – Web-based SQL editor for MySQL / PostgreSQL / SQL Server
