In this post, we review how you can build an enhanced and secure PostgreSQL database environment using community software. We look at the features that are available in PostgreSQL that, when implemented, provide improved security.
As discussed in the introductory blog post of this series, in our webinar of October 10, 2018 we highlight important aspects an enterprise should consider for their PostgreSQL environments. This series of blogs addressing particular aspects of the enterprise-grade postgres environment complements the webinar. This post addresses security.
Client connections to PostgreSQL Server using host based authentication
PostgreSQL uses a host based authentication file (pg_hba.conf) to authorize incoming connections. This file contains entries with a combination of 5 categories: type, database, user, address, and method. A client is allowed to connect to a database only when the combination of username, database and the hostname of the client matches an entry in the pg_hba.conf file.
Consider the following entry in pg_hba.conf file :
# TYPE DATABASE USER ADDRESS METHOD host percona pguser 192.168.0.14/32 md5
This entry says that connections from server 192.168.0.14 are only allowed from user pguser and only to the database percona. The method md5 forces password authentication.
The order of the entries in the pg_hba.conf file matters. If you have an entry that rejects connections from a given server followed by another that allows connections from it, the first entry in the order is considered. So, in this case, the connection is rejected.
This is the first layer of protection in authentication. If this criteria is not satisfied in this Access Control List (ACL), PostgreSQL will discard the request without considering even the server authentication.
Historically, PostgreSQL uses MD5 digest as a password hash by default. The problem with pure MD5 hashing is that this function will always return the same hash for a given password, which renders a MD5 digest more susceptible for password cracking. Newer versions of PostgreSQL implement SCRAM Authentication (Simple Authentication and Secured Layer) that stores passwords in salted and iterated hash formats to strengthen PostgreSQL against offline attacks. SCRAM-SHA-256 support was introduced in PostgreSQL 10. What matters most in terms of “enterprise-grade” security is that PostgreSQL supports industry-standard authentication methods out of the box, like SSL certificates, PAM/LDAP, Kerberos, etc.
User management through roles and privileges
It is always recommended to implement segregation of users through roles and privileges. There may be several user accounts in your PostgreSQL server. Only a few of them may be application accounts while the rest are developers or admin accounts. In such cases, PostgreSQL allows you to create multiple roles. Those can be assigned with a set of privileges. Thus, instead of managing user privileges individually, standard roles can be maintained and the appropriate role from the list can be assigned to a user. Through roles, database access can be standardized, which helps in user management and avoids granting too much or too little privilege to a given user.
For example, we might have six roles:
app_read_write app_read_only dev_read_write dev_read_only admin_read_write admin_read_only
Now, if you need to create a new dev user who can only have read access, grant one among the appropriate roles, such as dev_read_only:
GRANT dev_read_only to avi_im_developer;
Row level Security
Starting with version 9.5, PostgreSQL implements row level security, which can limit access to only a subset of records/rows in a table. Usually a user is granted a mix of SELECT, INSERT, DELETE and UPDATE privileges on a given table, which allows access to all records in the table. Through row level security, however, such privileges can be restricted to a subset of the records by means of a policy, which in turn can be assigned to a role.
In the next example, we create an employee table and two manager accounts. We then enable row level security on the table and create a policy that allows the managers to only view/modify their own subordinates’ records:
CREATE TABLE scott.employee (id INT, first_name VARCHAR(20), last_name VARCHAR(20), manager VARCHAR(20)); INSERT INTO scott.employee VALUES (1,'avinash','vallarapu','carina'); INSERT INTO scott.employee VALUES (2,'jobin','augustine','stuart'); INSERT INTO scott.employee VALUES (3,'fernando','laudares','carina'); CREATE USER carina WITH ENCRYPTED PASSWORD 'carina'; CREATE USER stuart WITH ENCRYPTED PASSWORD 'stuart'; CREATE ROLE managers; GRANT managers TO carina, stuart; GRANT SELECT, INSERT, UPDATE, DELETE ON scott.employee TO managers; GRANT USAGE ON SCHEMA scott TO managers; ALTER TABLE scott.employee ENABLE ROW LEVEL SECURITY; CREATE POLICY employee_managers ON scott.employee TO managers USING (manager = current_user);
In the log we can see that only certain records are visible to each manager:
$ psql -d percona -U carina psql (10.5) Type "help" for help. percona=> select * from scott.employee ; id | first_name | last_name | manager ----+------------+-----------+--------- 1 | avinash | vallarapu | carina 3 | fernando | laudares | carina (2 rows) $ psql -d percona -U stuart psql (10.5) Type "help" for help. percona=> select * from scott.employee ; id | first_name | last_name | manager ----+------------+-----------+--------- 2 | jobin | augustine | stuart (1 row)
You can read more about row level security in the manual page.
1. Encryption of data over the wire using SSL
PostgreSQL allows you to use SSL to enable encryption of data in motion. In addition, you may enable certification based authentication to ensure that the communication is happening between trusted parties. SSL is implemented by OpenSSL and thus it requires the OpenSSL package to be installed in your PostgreSQL server and PostgreSQL to be built –with-openssl support.
The following entry in a pg_hba.conf file says that connections to any database and from any user are allowed from server 126.96.36.199 as long as the communication is encrypted over SSL. Also, the connection is only established when a valid client certificate is provided:
# TYPE DATABASE USER ADDRESS METHOD hostssl all all 192.168.0.13/32 md5
Optionally, you may also use Client Certificate Authentication using the following method:
# TYPE DATABASE USER ADDRESS METHOD hostssl all all 192.168.0.13/32 cert clientcert=1
2. Encryption at Rest – pgcrypto
The pgcrypto module provides cryptographic functions for PostgreSQL, allowing certain fields to be stored encrypted. pgcrypto implements PGP encryption, which is part of the OpenPGP (RFC 4880) standard. It supports both symmetric-key and public-key encryption. Besides the advanced features offered by PGP for encryption, pgcrypto also offers functions for running simple encryption based on ciphers. These functions only run a cipher over data.
Accounting and Auditing
Logging in PostgreSQL
PostgreSQL allows you to log either all of the statements or a few statements based on parameter settings. You can log all the DDLs or DMLs or any statement running for more than a certain duration to the log file when logging_collector is enabled. To avoid write overload to the data directory, you may also move your log_directory to a different location. Here’s a few important parameters you should review when logging activities in your PostgreSQL server:
log_connections log_disconnections log_lock_waits log_statement log_min_duration_statement
Please note that detailed logging takes additional disk space and may impose an important overhead in terms of write IO depending on the activity in your PostgreSQL server. You should be careful when enabling logging and should only do so after understanding the overhead and performance degradation it may cause to your workload.
Auditing – pgaudit and set_user
Some essential auditing features in PostgreSQL are implemented as extensions, which can be enabled at will on highly secured environments with regulatory requirements.
pgaudit helps to audit the activities happening in the database. If any unauthorized user has intentionally obfuscated the DDL or DML, the statement the user has passed and the sub-statement that was actually executed in the database will be logged in the PostgreSQL log file.
set_userprovides a method of privilege escalations. If properly implemented, it provides the highest level of auditing, which allows the monitoring of even SUPERUSER actions.
You can read more about pgaudit here.
Security Bug Fixes
PostgreSQL Global Development Group (PGDG) considers security bugs seriously. Any security vulnerabilities can be reported directly to firstname.lastname@example.org. The list of security issues fixed for all the supported PostgreSQL versions can be found here. Security fixes to PostgreSQL are made available through minor version upgrades. This is the main reason why it is advised to always maintain PostgreSQL servers upgraded to the latest minor version.
If you liked this post…
Please join Percona’s PostgreSQL Support Technical Lead, Avinash Vallarapu; Senior Support Engineer, Fernando Laudares; and Senior Support Engineer, Jobin Augustine, on Wednesday, October 10, 2018 at 7:00 AM PDT (UTC-7) / 10:00 AM EDT (UTC-4), as they demonstrate an enterprise-grade PostgreSQL® environment built using a combination of open source tools and extensions.
The post Securing PostgreSQL as an Enterprise-Grade Environment appeared first on Percona Database Performance Blog.