Why Security Management Can be Challenging in Postgres

Regarding open-source database popularity, Postgres is second only to MySQL. Lightweight and easily integrated across various platforms and IDEs, Postgres is a labor of love for many throughout its developer community – and it shows. Beyond just SQL, Postgres supports many other database writing languages such as Perl, Java, and Python. Not to mention all the data types from basic numbers and strings to more robust ones like multidimensional arrays and JSONB.

But with so many options, features, and integration opportunities come complications if you’re not careful. For every degree of flexibility and customization, there’s typically a need to use capabilities or write SQL code that is maintained by data teams, not security teams.

In this article, we’ll explore the basics of Postgres security, some complications with security, and recommendations to tighten your security on this widely-used, widely-loved database platform.

PostgreSQL (or Postgres) is an open-source relational database system. As mentioned, it supports relational SQL querying as well as non-relational querying in JSON. Its flexibility makes it a favorite choice for web and mobile applications as well as large data analytics systems. This is especially true for organizations that prefer DevOps and agile development since Postgres is built for continuous development, integration, and deployment.

Postgres security is comprised of three main pillars:

• Network-Level Security: this includes Unix Domain sockets, TCP/IP sockets, and firewalls.

• Transport-Level Security: this includes encryption using SSL/TLS for secure communication.

• Database-Level Security: this includes features such as roles and permissions, row-level security, and auditing.

By using all three pillars, you can create a robust Postgres security policy that leverages the platform’s strengths against various threats.

Check out our guide on the basics of Postgres security to learn more.

Postgres’ security architecture benefits from various factors compared to other SQL platforms. While this list isn’t exhaustive, here are a few of the most common benefits:

Due to its open-source nature, Postgres is constantly updated and adapted by an active community of security experts. Of course, if none of their updates suit your needs, you are freely able to modify and implement Postgres as needed.

Being open source enabled Postgres to become highly flexible, growing and adapting to your organization and its needs. This is very useful in periods of fast growth, industry pivots, or deploying new applications.

With such an active community of individual developers and entire companies working on Postgres’ development, bugs are removed as quickly as they’re found and new, useful features are rolled out with successive updates. The same holds true with security vulnerabilities found natively within the platform.

If Postgres doesn’t have the security features you need or you don’t have the time or expertise to code them yourself, its active community has various plugins to solve this problem for you. Moreover, you can choose which features you need to build out your security policy to fit your database’s needs making it easy to keep data secure and your organization in compliance with various regulations.

The plethora of benefits highlights why this database is a favorite of developers. However, even though Postgres is a flexible database platform, keeping it secure, with proper access controls and compliance takes work. While Postgres can be adapted to any organization’s needs, that flexibility can turn into a liability if it isn’t used properly. We discuss a few issues that can occur with Postgres security management, especially when managing data and users at scale:

Postgres access control has to be managed effectively. For instance, row-level security sometimes leads to operational issues such as performance loss, so it must be used carefully. In addition, once access control becomes granular or changes rapidly, it often becomes challenging to manage it at scale by data teams. In these cases relying solely on Postgres for access control can reduce the effectiveness of access controls.

Auditing can become challenging when using Postgres at scale. A few examples:

• When setting up native audit logs, these need to be properly configured, securely stored (sometimes redacting sensitive query data is required) and a maintenance plan built.
• In several cases, BI tools are communal with a single user id for several individuals (sometimes the entire organization), and so either the login information is of very poor quality (it does not tell us who the user that was accessing specific data), or you need to perform complex correlations with the BI tool logs.

Row-level security in particular can be challenging to maintain within Postgres. We elaborated on this issue in detail in a separate blog post, however, to summarize we identified that Postgres struggles to optimize queries, it can drain resources, restrictive SELECT policies, and results in table inheritance issues between generational table access. 

Postgres provides many benefits and has a wide range of useful native security measures, however, there can be some drawbacks when relying solely on Postgres’s native capabilities. 

With Satori you can apply just-in-time access for data at scale, easily apply RBAC & ABAC on Postgres, implement dynamic masking and row-level security policies quickly and easily, and ensure auditing and monitoring capabilities without the need for additional DBA or Data Engineering resources.

To learn more:

• Satori For a Scalable Postgres Security 
• Book a demo with one of our experts
• Frictionless access control
• Auditing & Monitoring