Why Maintaining Row-Level Security in Postgres is Hard

PostgreSQL is consistently one of the most popular database platforms available. It has an active open source community and supports dozens of 80 IDEs to make integration easy, Postgres is a lightweight, DevOps-friendly platform that can scale for all kinds of organizations.

Despite all its strengths and advantages, however, there are still some areas that remain challenging. One such area is how hard it is to maintain row-level security on the Postgres platform.

In this article, we’ll explore row-level security (RLS), why implementing it on Postgres databases is such a struggle, and shed some light on how to better manage your RLS policies on this platform.

Row-level security (or RLS) is a security measure that limits records in a table based on a user’s authorization. When applied correctly, different users will have access to the same dataset. However, each user is only able to view the data to which they have access and are authorized. Depending on the user authorizations or restrictions will determine their ability to view sensitive data. 

Some common uses for RLS include:

• Sharing data across different departments or roles: your organization’s HR and security teams might need access to employee records, but the security team doesn’t need to see employee salary information.
• Keeping data accessible to those who need it: a client accounts management team’s table can contain all client information, but each member only sees the client records attached to them.
• Minimizing redundancy: instead of creating multiple tables for each user or team depending on their access and need, RLS allows multiple users and teams to call on the same data table that filters based on their level of access.

RLS, regardless of platform, has a few notable benefits as well as a few trade-offs. On one hand, RLS enables programmatic control over access rights and editing rows of data. This allows coding centralization, minimizing security risks, and scaling database access outside of applications when needed.

On the other hand, it’s very difficult to scale RLS if your organization grows too fast or too large, opening itself up to potential user errors and runaway costs. Moreover, RLS has a single point of failure if a bad actor gets a hold of an administrator account that can see every row of data, regardless of filters. To mitigate this, RLS should be used as a part of a robust security strategy rather than the only policy in place.

Check out our guide on the basics of row-level security to learn more.

Row-level security functions can be applied to SQL databases including Postgres. As mentioned, Postgres database administrators can use RLS to define access policies for specific rows of data depending on user access and authorization. You can even create policies for specific commands such as SELECT, INSERT, UPDATE, and DELETE.

A few examples of RLS in Postgres include:

• Ensuring managers can only edit their own rows: by using the USING clause, members of a “manager” role cannot perform SELECT, DELETE, or UPDATE operations on rows belonging to other managers.
• Applying different access permissions to new rows: by creating a policy using SELECT, you can combine two policies so that all rows are visible to specific users, but users can only modify rows they create.
• Disabling and re-enabling RLS: you can turn off row-level security without deleting the policy in Postgres. To re-enable it, you can use the following code: ALTER TABLE {table-name} ENABLE ROW LEVEL SECURITY;

Check out our guide on row-level security for Postgres for more information.

In addition to the drawbacks of row-level security regardless of platform, the following are some examples of common problems with implementing RLS in Postgres.

The benefits from Postgres RLS can dissipate when the size of the organization, the amount of data collected, and the number of restrictions and authorizations grow in size and complexity. Data engineering teams are then tasked to keep up with coding these additional restrictions and authorizations which can lead to a bottleneck in terms of access. Further, the time and resources spent to keep the authorizations up-to-date can keep engineering teams away from their core responsibilities.

While RLS can help keep data access clean, it can cause other optimization issues if not managed carefully. For instance, using query optimizers can lead to data leakage which is especially true when using custom functions. This results in poor performance and less optimization despite using tools to avoid that.

Though there isn’t a specific fix for this problem, avoiding query optimizers on tables that use RLS can help mitigate this issue.

Restrictive SELECT policies are useful when trying to add layers of intricacy to your access policies. However, they should be used sparingly with RLS as it can create problems. For instance, when mixing permissive and restrictive policies, only one permissive policy needs to be true with all restrictive policy conditions being true as well. Moreover, something permitted in one policy cannot be restrictive in another.

Layered on top of row-level security can cause access issues and unneeded complexity if you’re not careful, especially when using restrictive policies.

Applying RLS to a single layer of tables is relatively easy to implement. However, the same cannot be said of parent tables with children. For instance, queries can result in unintentional row access between parents and their children. In other words, predicates might function differently depending on their use with the parent or child table.

As row-level security is a type of access control policy, poor implementation can result in conflicts with other access control configurations such as role-based access control, attribute-based access control, and just-in-time controls. These conflicts can cause security issues, broken queries, and unnecessary complications in your database’s code which can affect processing speed and performance.

While there are many benefits associated with Postgres RLS in some cases it is difficult to maintain, especially at scale. Satori provides frictionless access control that is scalable to meet your company’s evolving data needs. Through Satori RLS is easy to configure and does not require any additional code.

To learn more:

• Book a demo with one of our experts
• Simplified Row-Level Security with Satori
• Blog: Data Security Projects Keep Data Teams Away From Their Core Responsibilities