Row-Level Security in Action: A Hands-On Demo with PostgreSQL

Data security is an important element of any application. As organizations increasingly turn to cloud-based services, it is critical to ensure that sensitive data is only available to authorized personnel. Failure to implement proper measures can result in unauthorized access, leading to data leaks, regulatory violations, and significant financial losses.

In 2024, the global average cost of a data breach reached an all-time high of $4.88 million, a 10% increase from the previous year. Notably, nearly 46% of these breaches involved the compromise of customer personally identifiable information (PII), including tax identification numbers, emails, phone numbers, and home addresses. This underscores the necessity of implementing strict access controls to mitigate the risks associated with unauthorized data access.

Row-level security (RLS) offers a robust solution by embedding access controls directly into the database layer. This ensures that users only retrieve data relevant to them. Implementing RLS simplifies security management and reduces the risk of accidental data exposure. 

Benefits of Row-level security

• Enhanced data privacy: Ensures users only see the data they are authorized to access.
• Simplified application logic: Moves access control logic to the database, reducing complexity in the application layer.
• Improved compliance: Helps organizations meet regulatory requirements by enforcing strict data access policies.
• Performance optimization: When properly implemented, RLS can improve performance by limiting the volume of data retrieved.

Use cases of Row-level security

• Multi-tenant application: Ensures that each tenant (customer) only accesses their own data.
• Role-based access control applications: Restricts access to sensitive data based on roles (e.g., HR personnel can view salary data, but other employees cannot).
• Financial systems: Enforces data access based on account ownership.

RLS supporting databases

1. Postgres
2. Microsoft SQL Server
3. Snowflake
4. Amazon Redshift

Note: Row-level security (RLS) can be applied to any of the mentioned use cases. However, this blog specifically focuses on RLS in the context of multi-tenant applications. Postgres is also used as the database for the demo purpose.

Why Row-level security (RLS)?

Traditionally, tenant-specific filtering is handled at the application level using WHERE tenant_id = ? clauses. However, this approach relies heavily on developers implementing filtering consistently across all queries. A single missed WHERE clause can result in data leakage, compromising security.

RLS mitigates this risk by enforcing row-level constraints directly in the database. This ensures that the database will restrict access even if a query explicitly requests unauthorized data.

RLS enables policies at the database level to control access to individual rows within a table. The core concept involves defining policies that specify conditions under which a user can access rows.

Syntax:

CREATE POLICY policy_name ON table_name USING condition;

RLS policies evaluate the condition specified in the USING clause and return only rows that satisfy the condition.

Approaches for RLS implementation

Approach 1: Separate database user for each tenant

One way to implement RLS is by creating a separate database user for each tenant and defining policies based on the current database user.

CREATE POLICY tenants_isolation_policy ON users
USING (tenant_id = current_user);

Drawbacks

• Not scalable for large multi-tenant applications.
• Increases complexity in managing database connections and credentials.

Approach 2: Use a database runtime parameter

A more scalable approach is to use a single database user and define RLS policies that rely on a runtime parameter to hold the current tenant context. The parameter can be set using the set_config() function or the SET command. Queries can then access the parameter value using the current_setting() function.

CREATE POLICY tenant_users_isolation_policy ON users
USING (tenant_id = current_setting('app.tenant_id',true)::int);

Implementing Row-level security

Create an application user

Create a database user for the application. This user will not own the tables (since table owners bypass RLS by default). This user will be used by the application to query the data from the database.

-- Create a new DB user named 'application_user' with password 'password'
CREATE ROLE application_user LOGIN NOBYPASSRLS PASSWORD 'password';

-- Give access to all the schema and tables to this user
GRANT INSERT, SELECT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA public TO "application_user";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "application_user";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "application_user";

Define RLS policies

Define policies that restrict access to rows based on the app.tenant_id runtime parameter.

CREATE POLICY tenant_users_isolation_policy ON users
USING (tenant_id = current_setting('app.tenant_id',true)::int);

Enable Row-level security

By default, RLS is disabled for tables. It must be explicitly enabled.

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

RLS in action

Let’s consider that we created the users table using the database user db_manager. Also, we have created the above policy and enabled row level security.

For the demo purpose, we will have 2 tenants and 4 users. User1 and User2 will belong to tenant1, while User3 and User4 will belong to tenant2.

Scenario 1: Querying as table owner

If the table owner (db_manager) queries the table, all rows are returned, as table owners bypass RLS policies by default.

Conclusion: RLS is not enforced for table owners unless explicitly overridden using:

ALTER TABLE users FORCE ROW LEVEL SECURITY;

Scenario 2: Querying without setting app.tenant_id runtime parameter

Create the application user as mentioned in the above section. Login to the database server using the application_user. Run the SELECT query. As you can see in the below image, no data is returned. This is because the runtime parameter app.tenant_id is not set for the session and hence no rows satisfy the policy.

Conclusion: The application does not leak any data, even if it forgets to set the db session parameter app.tenant_id.

Scenario 3: Querying with app.tenant_id Set

To set the runtime parameter, you can use set_config function or SET command.

SELECT set_config('app.tenant_id', 2, false);
-- OR
SET app.tenant_id=2

When the application sets app.tenant_id to a specific tenant, only users belonging to that tenant are returned.

Conclusion: User can access their own data.

Scenario 4: Attempting to access another tenant’s data

-- Set app.tenant_id='2'
SELECT set_config('app.tenant_id', '2', false);

-- Lets try fetching other tenant1 records
SELECT * FROM users where tenant_id=1;

The above screenshot shows that even after adding an explicit filter for tenant1, we were not able to fetch tenant1 users as we have set app.tenant_id=2.

Conclusion: Data isolation is enforced, even for explicit attempts to bypass it.

Key points to remember

1. Superusers and Roles with BYPASSRLS bypass RLS policies by default.
2. Table Owners bypass RLS policies unless FORCE ROW LEVEL SECURITY is enabled.
3. No data is fetched if policy is not satisfied. In our case, if app.tenant_id is not set, no data is returned.
4. Users will not get other tenant’s data even after trying to fetch it explicitly.
5. Application needs to set the runtime parameter app.tenant_id for every session.

Performance considerations

• Indexing – Properly index columns used in RLS policies to optimize query performance.
• Query Overhead – RLS policies introduce additional query evaluation, which may impact performance.
• Logging & Auditing – Use database logging to track access attempts and policy enforcement.

Security best practices

1. Ensure that the runtime parameter is correctly set at the beginning of the session and properly reset before the session ends.
2. Consistently audit and test policies to identify and correct any misconfigured RLS policies.

Conclusion

Row-level security is a powerful mechanism for enforcing data access restrictions directly at the database level. By integrating RLS into your database security strategy, you can significantly reduce the risk of data leaks and ensure strict data isolation.

References

1. PostgreSQL: Documentation: 17: 5.9. Row Security
2. PostgreSql Row Level Security || Talentica Software