Another month, another database security disaster. It seems database security breaches are becoming more common as bad actors realize that databases contain lots of, well, data.
How does this keep happening? Usually, the answer is poorly executed database security protocols. How can we stop it from happening?
The good news is that it’s preventable. Databases do not need to be at risk, as long as you take the necessary precautions. Usually the answer is configuration. Don’t just use the out-of-box settings. Do use iptables to secure access. (As far as can be determined in the recent Exactis breach, had the setup made use of iptables or a similar feature, then the breach would have been prevented.)
In this article, we’ll look at steps you can take to secure PostgreSQL against intrusions and attacks. First we’ll look at implementing SSL connections and certificate-based authentication in PostgreSQL. Then we’ll examine how to create users and groups in PostgreSQL that provide the minimum appropriate level of database access.
As a tried-and-true open source database, PostgreSQL is used in many enterprise and start-up database deployments. There is certainly lots of data in PostgreSQL to protect.
Configure secure connections in PostgreSQL
PostgreSQL allows you to enable encryption of data over the wire through SSL. PostgreSQL uses SSL connections to encrypt the communication between client and server. In order to use SSL, you must have OpenSSL installed both on your client and server.
Steps to encrypt connections using SSL
To proceed further, you must create the server and client certification files that are signed by a Certification Authority.
1. Create a certificate signing request (CSR) and a key file.
$ openssl genrsa -out rootCA.key 1024
$ openssl req -x509 -new -key rootCA.key -days 365 -out rootCA.crt -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=root'
2. Create a server certificate signed by the root certificate authority.
$ openssl genrsa -out server.key 1024
$ openssl req -new -key server.key -out server.csr -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=pgservername'
$ openssl x509 -req -in server.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out server.crt -days 365
(Note that CN in the second line above should be the server name for server validation.)
3. Create a client certificate signed by the root certificate authority.
$ openssl genrsa -out postgresql.key 1024
$ openssl req -new -key postgresql.key -out postgresql.csr -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=postgres'
$ openssl x509 -req -in postgresql.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out postgresql.crt -days 365
4. Copy the server certification file (server.crt), server private key (server.key), and root certification authority (rootCA.crt) to either the PostgreSQL data directory or a secured location accessible by PostgreSQL superuser (postgres). Once copied, allow read-only privileges to PostgreSQL users.
$ cd $PGDATA
$ chmod 0400 server.crt server.key rootCA.crt
5. Set the appropriate parameters in PostgreSQL to enable SSL mode.
$ psql
ALTER SYSTEM SET ssl TO 'ON';
ALTER SYSTEM SET ssl_ca_file TO 'root.crt';
ALTER SYSTEM SET ssl_cert_file TO 'server.crt';
ALTER SYSTEM SET ssl_key_file TO 'server.key';
6. OpenSSL supports a wide range of ciphers. You will want to choose the cipher that suits your organizational standards. Once you have chosen the cipher you wish to use, set the following parameter in PostgreSQL.
psql
ALTER SYSTEM SET ssl_ciphers TO 'your_desired_cipher'; (=> Change requires Server Reload)
Default cipher is: HIGH:MEDIUM:+3DES:!aNULL
7. You may either reload or restart PostgreSQL server to get these parameters into effect.
$ psql -c "select pg_reload_conf()"
or
$ pg_ctl -D $PGDATA restart -mf
8. To enable encryption using SSL, you must modify host to hostssl in the pg_hba.conf file. For example, to enable SSL for local connections, replace host with hostssl for local connections.
$ vim $PGDATA/pg_hba.conf
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# IPv4 local connections:
# host all all 127.0.0.1/32 trust
hostssl all all 127.0.0.1/32 trust
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
9. You may add similar entries to enable encryption of connections from your remote applications or clients.
$ vim $PGDATA/pg_hba.conf
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# IPv4 local connections:
# host all all 127.0.0.1/32 trust
hostssl all all 127.0.0.1/32 trust
hostssl all all app_server_1/32 md5
hostssl all all app_server_2/32 md5
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
10. Once you have made the changes to pg_hba.conf file, you must perform a SIGHUP or reload.
$ psql -c "select pg_reload_conf()"
11. Test your local connections for SSL. This is how it looks before and after enabling SSL for local connections.
Before
=======
$ psql -h localhost
psql (10.4)
Type "help" for help.
postgres=#
After
=======
$ psql -h localhost
psql (10.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
If you use hostssl for your remote application connections, the communication to the database server is automatically encrypted.
Steps to enable client certificate authentication
12. A client certificate can be used for user identity by the client or application server. It is especially useful for authentication. You can now copy the client certificates generated in Step 3 (above) to your remote application server. Once copied, set appropriate privileges. For example, the IP of my application server for this test is 192.168.0.13.
$ scp postgresql.crt postgresql.key rootCA.crt postgres@192.168.0.13:/var/lib/pgsql
Once copied to the application server, give read-only privileges to the appropriate OS user in the application server.
$ chmod 0400 postgresql.crt postgresql.key rootCA.crt
13. In order to use client certificate authentication, add the following entry to the pg_hba.conf of your remote PostgreSQL server.
$ vi $PGDATA/pg_hba.conf
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
hostssl all all 192.168.0.13/32 cert clientcert=1
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Here, 192.168.0.13 is the application server's IP address. You may wish to have multiple entries this way.
Observe the difference between Step 9 and Step 13. The pg_hba.conf entry in Step 9 would enable communication over encryption, with connections encrypted automatically. The pg_hba.conf entry in Step 13 goes further. It would also force the client certification authentication to ensure that the communication is happening between trusted parties.
Give a SIGHUP or reload to put the changes to pg_hba.conf into effect.
$ pg_ctl -D $PGDATA reload
14. Validate the remote connection using the psql client.
$ psql "port=5432 host=192.168.0.12 user=postgres sslcert=/var/lib/pgsql/postgresql.crt sslkey=/var/lib/pgsql/postgresql.key sslrootcert=/var/lib/pgsql/rootCA.crt sslmode=require"
psql (10.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-DES-CBC3-SHA, bits: 168, compression: off)
Type "help" for help.
postgres=#
You should see the connections happening over SSL.
Create secure user and group roles in PostgreSQL
PostgreSQL allows you to enable encryption of data over the wire through SSL. However, it is also important to understand how security hardening and user management works in PostgreSQL. A user in PostgreSQL must have appropriate privileges that don’t give too much access. The steps below should help you understand how PostgreSQL implements user management. They also present some best practices.
What is a PostgreSQL user?
A PostgreSQL user is a role that has CONNECT privilege. Both CREATE USER and CREATE ROLE work well to create a PostgreSQL user. However, the user must have a LOGIN role. The login role is assigned to a user when you use any of the following three approaches:
CREATE USER percuser WITH ENCRYPTED PASSWORD 'secret';
or
CREATE ROLE percuser WITH ENCRYPTED PASSWORD 'secret';
or
CREATE ROLE percuser;
ALTER ROLE percuser WITH LOGIN ENCRYPTED PASSWORD 'secret';
When you use CREATE USER with any of the above, PostgreSQL automatically modifies the syntax internally with the following one:
CREATE ROLE percuser
WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS
ENCRYPTED PASSWORD 'secret';
In the above syntax automatically modified by PostgreSQL, we see that it is not granting the ROLES that are needed to manage PostgreSQL.
After creating the user or role you may use the following query to see if the user can login (has the CONNECT privilege):
postgres=# select rolcanlogin from pg_roles where rolname = 'percuser';
rolcanlogin
-------------
t
(1 row)
This returns a boolean that tells whether the user can log in or not. For example, when you use the following syntax to create a ROLE you see that the role does not have CONNECT privilege.
postgres=# CREATE ROLE percrole;
CREATE ROLE
postgres=# select rolcanlogin from pg_roles where rolname = 'percrole';
rolcanlogin
-------------
f
(1 row)
Please note: Always avoid creating any users or roles with prefix pg_%.
Difference between CREATE USER and CREATE ROLE in PostgreSQL
When you create a user using CREATE USER, the user automatically gets a LOGIN role:
postgres=# CREATE USER percuser WITH ENCRYPTED PASSWORD 'secret';
CREATE ROLE
postgres=# select rolcanlogin from pg_roles where rolname = 'percuser';
rolcanlogin
-------------
t
(1 row)
However, when you create a role you must explicitly add the LOGIN role to allow the user to log in:
postgres=# CREATE ROLE percuser WITH ENCRYPTED PASSWORD 'secret';
CREATE ROLE
postgres=# select rolcanlogin from pg_roles where rolname = 'percuser';
rolcanlogin
-------------
f
(1 row)
postgres=# ALTER ROLE percuser WITH LOGIN;
ALTER ROLE
postgres=# select rolcanlogin from pg_roles where rolname = 'percuser';
rolcanlogin
-------------
t
(1 row)
What is a group or a group role in PostgreSQL?
In PostgreSQL, a role can INHERIT another role. This means one role can get access using the privileges of another role. This can be achieved using GRANT or INHERIT keywords.
It is always recommended to provide access privileges to users by using GROUP ROLES.
For example, consider an organization with:
- 1 database (percona)
- 2 schemas (scottand tiger)
Let’s say we then need to create 10 individual users and 10 application users observing the following requirements:
- Five of the application users must have read-only access to the tables of the schema scott and read-write access to tables of schema tiger.
- The other five application users must have read-write access to the tables of schema scott and read-only access to tables of schema tiger.
- All 10 individual user accounts can have only read-only access to the tables of schemas scott and tiger.
In this situation, you can create four group roles like the following:
scott_readonly:
CREATE ROLE scott_readonly;
GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA scott TO scott_readonly;
scott_readwrite:
CREATE ROLE scott_readwrite;
GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA scott TO scott_readwrite;
tiger_readonly:
CREATE ROLE tiger_readonly;
GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA tiger TO tiger_readonly;
tiger_readwrite:
CREATE ROLE tiger_readwrite;
GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tiger TO tiger_readwrite;
Now, you can GRANT these group roles to the users based on the above three requirements.
GRANT scott_readonly,tiger_readonly TO user1;
GRANT scott_readonly,tiger_readwrite TO appuser1;
GRANT scott_readwrite,tiger_readonly TO appuser2;
By assigning group roles to these users, you can segregate which privileges are allowed to which user.
Reference: https://www.postgresql.org/docs/10/static/sql-createrole.html
Are users and roles global to the entire instance?
Yes, users and roles in PostgreSQL are global to the entire instance. One user can be used to access any database. Similarly, a group role in PostgreSQL can be granted privileges of multiple schemas/objects in multiple databases.
The following example shows that the group role (grantee) scott_readonly has SELECT privilege on two different tables of two different databases: