In the previous posts, we discussed GDPR and how we could enforce it in the scope of Oracle  and SQL Server databases. We now cover the same topic with Postgresql databases in mind.

As always, please refer to GDPR Data Security requirements laid down in the initial post. We will continue to walk the same task and activity structure for all RDBMs we cover. Let’s go to the identified GDPR tasks and requireements and then the identified database options, features and/or related products to implement recommendations.

 

 

Risk Assessment

• Personal Data identification
• Access, role and privilege analysis
• Security configuration analysis

Attack Prevention

• Encryption of data and data transfers
• Anonimyzation/Pseudonymization of Personal Data
• Personal Data Access Control

Monitoring

• Audit implementation and centralization
• Audit event notification implementation

 

Postgresql built-in features and tools of the trade

In contrast with our previous reviewed RDBMs (Oracle and SQL Server), Postgresql is an Open Source project. This post does not compare each engine’s capabilities nor review the whole set of features offered by each system. The purpose is to review features and tools that help us enforce GDPR recommendations. We will therefore focus on core Postgresql features  and open-source extensions and projects for this purpose.

Postgresql databases provides the necessary tools and technologies to address each of the above mentioned tasks. Some third-party open source projects come in handy as well for implementing certain GDPR recommendations.

 

Risk Assessment

• Personal Data identification: pgAdmin, the postgresql metadata set of views.
• Access, role and privilege analysis: pgAdmin, postgresql metadata views and object-access privilege built-in functions
• Security configuration analysis: the DevAudit project is a very powerful auditing tool for multiple targets, including postgresql security configuration and vulnerabilities. It implements security rules based on multiple vulnerability databases and a few of US DISA’s Security Technical Implementation Guide (STIG) for Postgresql v1R3.

Attack Prevention

• Encryption of data and data transfers: pgCrypto, Linux Unified Key Setup (LUKS), eCryptfs, encfs and built-in SSL for data in transfer.

Note: The concept of Transparent Data Encryption (TDE) as known in SQL Server or Oracle databases doesn’t exist natively in postgresql for data at rest. TDE relies on encryption of data at the file level. However, there are two ways to easily implement TDE in conjunction with Postgresql databases: Disk-level encryption (LUKS) and filesystem-level encryption (eCryptfs, encfs, etc).

Here is a summary of the encryption options available in Postgresql.

• Anonimyzation/Pseudonymization of Personal Data: ARX
• Personal Data Access Control: Column-level access control through standard GRANT,  Row security policies, pg_hba.conf, Security-Enhanced Postgresql (sepgsql)

 

 

Monitoring

• Audit implementation and centralization: pgAudit, pgAudit analyzer
• Audit event notification: DIY with pgAgent

Surprise, surprise

The official Postgresql project and its extensions provide a complete set of security features that make it a very secure database system but it may take a bit of organization and care to glue all the required components together. Some development will be necessary to leverage pgCrypto cryptographic functions for the encryption or obfuscation of sensitive data. Key management must be planned carefully for filesystem encryption. Ciphers and key lengths will require to ponder risk versus performance impact. Audit centralization and suspicious activity monitoring of Personal Data may require some scripting as well. Fine-grained access control will require a careful definition of roles and granted privileges.

Other Postgresql distributions such as Percona, EnterpriseDB, Fujitsu or PostgresPURE, to name a few, provide additional management, configuration and security tools similar to those provided by commercial vendors under a subscription/support model. We will not discuss these distributions as we limit our review to freely available projects released for production and with quite some mileage.

Conclusion

Postgresql is probably one of the largest open source project and its community is very active. The flexibility and “DIY” culture particular to the OSS movement may not be the right solution for companies looking to externalize support and maintain a more homogeneous, out-of-the-box database ecosystem. Excellent Postgresql distributions and support plans will cover these needs.

Next post will cover GDPR and Mysql/mariadb in the same way. Stay tuned.