Help Center/ Relational Database Service_RDS for PostgreSQL/ Best Practices/ User Data Isolation Solution Based on Row-Level Security Policies
Updated on 2026-09-23 GMT+08:00
View PDF
Share

User Data Isolation Solution Based on Row-Level Security Policies

Context

In a multi-user architecture, multiple users share the same DB instance and application-layer resources. Therefore, data must be strictly isolated between users. The query, modification, and deletion operations of any user must not affect the data of another user. Otherwise, serious consequences such as data leakage, compliance violations, and legal liabilities may occur.

In traditional solutions, user isolation is usually implemented at the application layer. That is, before each SQL statement is executed, the application code appends the user ID condition (for example, WHERE user_id = ?). This approach has the following limitations:

  • Code vulnerability risks: If even a single SQL query omits user ID filtering, all user data is immediately exposed. New fields, complex subqueries, and SQL statements automatically generated by the ORM framework can all become potential vulnerabilities.
  • High maintenance costs: As the business scale and development team expand, it becomes difficult to ensure that all personnel, modules, and historical code comply with isolation specifications. As a result, long-term audit costs remain high.
  • Bypass risks: When DBAs, O&M personnel, or third-party tools directly connect to the database to perform queries, application-layer isolation becomes completely ineffective.
  • Difficult auditing: It is impossible to uniformly record which queries have crossed user boundaries at the database level. Troubleshooting relies on application logs, resulting in a long fault locating period.

PostgreSQL 9.5 and later versions natively provide row-level security (RLS), which moves the user isolation capability down to the database kernel. The database enforces data visibility, fundamentally addressing the limitations of the application-layer isolation solution. RDS for PostgreSQL, based on PostgreSQL 11 and later versions, fully supports the RLS feature. This section provides a complete and practical isolation solution along with best practices for multi-user scenarios.

RLS Principles

RLS is a feature introduced in PostgreSQL 9.5 that allows a set of policies to be defined on a table. After RLS is enabled for a table, when any SELECT, INSERT, UPDATE, or DELETE statement involving the table is executed, the database automatically appends the conditions defined in the policy to the original SQL query's WHERE clause using an AND operator (or the USING/WITH CHECK predicate for INSERT/UPDATE). This restricts the visibility and writability of each row of data.

The core value of RLS lies in that isolation rules are stored in the database kernel in a centralized manner. All access paths (applications, ORM, third-party tools, and DBAs) must undergo policy verification and cannot bypass the verification.

Function Description

The syntax for creating a policy is as follows:

CREATE [OR REPLACE] POLICY name ON table_name
    [AS { PERMISSIVE | RESTRICTIVE }]
    [FOR { ALL | SELECT | INSERT | UPDATE | DELETE }]
    [TO { role_spec | PUBLIC } [, ...] ]
    [USING ( using_expression ) ]
    [WITH CHECK ( check_expression ) ];

Parameter description:

  • AS PERMISSIVE (default): permissive policy. Multiple policies are combined using OR.
  • AS RESTRICTIVE: restrictive policy. It is combined with all permissive policies using AND. It is usually used for blacklist-based forcible filtering.
  • FOR clause: specifies the type of commands for which the policy takes effect. ALL indicates that the policy takes effect for all DML statements.
  • TO clause: specifies the role to which the policy applies. PUBLIC indicates all roles.
  • USING: visibility predicate applied to existing rows (old rows during SELECT, UPDATE, or DELETE).
  • WITH CHECK: validity predicate applied to newly inserted or updated rows (new values during INSERT or UPDATE). If the predicate is not met, an error is reported.

A policy expression usually references the following context functions to determine the identity and ownership of the current user:

  • Role used by the current session to execute SQL statements (affected by SET ROLE) 
    SELECT current_user;
  • Original role used for login in the current session (not affected by SET ROLE) 
    SELECT session_user;
  • Custom GUC parameter, which is usually used to pass the user ID within a transaction 
    SELECT current_setting('app.user_id');

In the solution, the application layer injects the user ID into the session context through SET LOCAL app.user_id = xxx at the start of each transaction. The policy expression then retrieves the user ID through current_setting('app.user_id') and uses it as the basis for row-level filtering.

RLS involves two key attributes:

  • BYPASSRLS: By default, the RLS policy is not applied to the table owner and roles with the BYPASSRLS attribute. This attribute can be explicitly granted to or revoked from application roles.
  • FORCE RLS: The RLS policy is forcibly enabled for the table owner to prevent the owner from bypassing isolation through their own identity.

Command examples:

  • Grant an application role the capability to bypass RLS (use with caution in the production environment). 
    ALTER ROLE app_user BYPASSRLS;
  • Revoke the capability to bypass RLS. 
    ALTER ROLE app_user NOBYPASSRLS;
  • Forcibly enable RLS for the table owner. 
    ALTER TABLE orders FORCE ROW LEVEL SECURITY;

RLS is not a replacement for standard GRANT/REVOKE permissions; instead, it is a supplement.

  • Standard permissions determine whether a certain type of operation can be performed, while RLS determines which specific rows can be operated on.
  • The standard permission check must be passed before the RLS policy check. The operation can be performed only when both checks are passed.
  • Even if a user has the SELECT permission on a table, no rows can be queried (no error is reported, but 0 rows are returned) if no corresponding RLS policy permits it.
  • For INSERT/UPDATE, RLS checks both old rows (USING) and new rows (WITH CHECK) to prevent unauthorized writes.

The detailed procedure is as follows:

  1. Prepare the environment and configure account permissions.

    Environment preparation includes planning database accounts, creating user tables and business tables, and configuring permissions.

    1. Create an application connection role. 
      CREATE ROLE app_user LOGIN PASSWORD 'your_password';
    2. Create a primary user table. 
      CREATE TABLE users (
    user_id   BIGINT PRIMARY KEY,
    user_name VARCHAR(128) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
    3. Create a business table named orders (containing the user_id column). 
      CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    order_no   VARCHAR(64) NOT NULL,
    amount     NUMERIC(12,2) NOT NULL,
    status     INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
    4. Create an index on the user_id column (mandatory for performance improvement). 
      CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
    5. Grant permissions to the app_user user. 
      GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT SELECT ON users TO app_user;
  2. Enable RLS and create an isolation policy.

    Enable RLS for the business table and create an isolation policy based on user_id.

    1. Enable RLS. 
      ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
    2. Create an isolation policy. 
      CREATE POLICY user_isolation ON orders
    FOR ALL
    TO app_user
    USING (user_id = current_setting('app.user_id')::bigint)
    WITH CHECK (user_id = current_setting('app.user_id')::bigint);
    3. Force the table owner to also comply with RLS (to prevent bypass). 
      ALTER TABLE orders FORCE ROW LEVEL SECURITY;
  3. Test the user data isolation effect.

    Test data isolation using different user contexts to verify that each user can access only their own data.

    1. Prepare test data (as an administrator). 
      INSERT INTO orders (user_id, order_no, amount) VALUES
    (1001, 'ORD001', 100.00),
    (1001, 'ORD002', 200.00),
    (1002, 'ORD003', 300.00);
    2. Simulate the connection of user 1001. 
      SET ROLE app_user;
SET app.user_id = '1001';
    3. Query data as user 1001 (only their own data can be viewed). 
      SELECT * FROM orders;

      Two records (user_id=1001) are expected to be returned.

    4. Attempt to insert data without authorization (the operation will be rejected). 
      INSERT INTO orders (user_id, order_no, amount) VALUES (1002, 'ORD004', 400.00);

      Expected return:

      ERROR: permission denied for sequence orders_id_seq
    5. Switch to user 1002. 
      RESET app.user_id;
SET app.user_id = '1002';
    6. Query data as user 1002. 
      SELECT * FROM orders;

      One record (user_id=1002) is expected to be returned.

User Isolation Solution Design

The overall architecture of the user isolation solution consists of three layers from top to bottom:

  • Application layer: When each request starts, the application parses the current user ID based on the login status and immediately injects the user context after obtaining a database connection.
  • Connection pool layer: For example, PgBouncer or an application-built connection pool is used to reuse physical connections. When a connection is checked out, the application executes SET LOCAL app.user_id. Before the connection is returned, the application executes RESET or ensures that the transaction has ended.
  • Database layer (RDS for PostgreSQL instance): Each business table contains a user_id column with RLS enabled. The policy expression is user_id = current_setting('app.user_id')::bigint.

PostgreSQL's custom GUC parameters allow the application layer to inject arbitrary named parameters at the transaction level. The SET LOCAL setting takes effect only in the current transaction and is automatically reset when the transaction ends. This naturally avoids user ID conflicts caused by connection reuse.

BEGIN;
SET LOCAL app.user_id = '10086';
SELECT * FROM orders WHERE status = 1;  -- RLS automatically appends AND user_id = 10086.
COMMIT;

Each business table must contain a user_id column and meet the following requirements:

  • Data type: BIGINT is recommended to accommodate large-scale user IDs. For small-scale scenarios, INT or UUID can also be used.
  • NOT NULL constraint: user_id should be set to NOT NULL to prevent rows with no user from becoming ghost data after being filtered out by the policy.
  • Index: An index must be created on user_id to prevent full table scans caused by RLS predicates. For high-frequency columns in join queries, it is recommended to create a composite index (user_id, column_name).
  • Foreign key: Foreign key columns between business tables should also carry user_id and include user_id in the foreign key to prevent cross-user references.

The recommended general policy template is as follows (applicable to most business tables):

CREATE POLICY user_isolation ON schema.table_name
    USING (user_id = current_setting('app.user_id')::bigint)
    WITH CHECK (user_id = current_setting('app.user_id')::bigint);

Keeping USING and WITH CHECK identical ensures that the visible rows for queries, modifications, and deletions belong to the same user as the newly inserted or updated rows. If a scenario allows cross-user read-only access but does not allow cross-user write access, you can omit WITH CHECK. However, it is recommended that both be configured to achieve true, strict row-level isolation.

The following roles are recommended:

  • Application role (app_user): This role is granted only the DML permission on business tables, NOBYPASSRLS permission, and FORCE RLS permission that takes effect for the table owner, ensuring that the RLS policy is not bypassed.
  • DBA role (dba_role): This role is granted DDL and O&M permissions. A security administrator can temporarily grant BYPASSRLS to this role for handling faults under audit and approval.
  • Read-only role (ro_user): Data middle platform and BI report role. This role is granted the SELECT permission and configured with the same RLS policy as app_user to ensure that only the data of the current user is viewed.

Suggestions on Best Practices

  • Performance impact: The RLS policy automatically appends the WHERE condition. Therefore, ensure that an index (B-tree) has been created for the user_id column. Otherwise, efficient queries may deteriorate into full table scans. For frequently queried columns, it is recommended that a composite index (user_id, business_column) be created.
  • Connection pool configuration: When connection pools like PgBouncer are used in transaction pooling mode, SET LOCAL only applies to the current transaction and automatically resets upon completion. However, if session pooling is used, the application must explicitly reset app.user_id before returning the connection to prevent the user ID from being carried over to the next user.
  • Policy maintenance: When a new business table is added, an RLS policy must be created immediately and enabled or forced. It is recommended that the DDL approval process or automated scripts be used for unified management to avoid omissions.
  • Monitoring: Use pg_stat_user_tables.seq_scan/idx_scan to monitor whether the RLS predicate triggers a full table scan. Use pg_stat_statements to check whether user_id in slow SQL statements fails to hit an index.
  • Transaction boundary: SET LOCAL is valid only within a transaction and automatically becomes invalid when the transaction ends. Do not use SET LOCAL outside a transaction. Otherwise, it does not take effect or degrades to SET (session-level), which poses security risks.
  • Testing: The test must cover cross-user access scenarios, including unauthorized INSERT, unauthorized UPDATE (modifying the user_id column), and unauthorized DELETE test cases. It is recommended that cross-user visibility regression tests be added to the automated CI pipeline.
  • Views and functions: By default, views inherit the RLS context of the caller. The SECURITY DEFINER function runs as the function owner, which may bypass RLS. Therefore, use this function with caution and explicitly declare SECURITY INVOKER.

FAQ

  • Question 1: Does RLS affect query performance?

    RLS is represented as an additional WHERE predicate in the SQL execution plan. As long as an index (B-tree or composite index) is created for the user_id column, the query planner will prioritize index scans, making the performance impact negligible. However, if no index is created for the user_id column, the original single-table query may degrade to a full table scan. Therefore, proper indexing is a performance prerequisite for any RLS implementation. You can use EXPLAIN ANALYZE to check whether the RLS predicate hits an index.

  • Question 2: Why does the table owner bypass RLS by default?

    The table owner has full control over the table. PostgreSQL considers the owner trustworthy by default, allowing the owner to bypass RLS. In the production environment, ALTER TABLE ... FORCE ROW LEVEL SECURITY must be executed on each business table to enforce this policy on the owner. This ensures that even if the owner's credentials are leaked or misused, unauthorized access to user data is prevented.

  • Question 3: How do I pass user IDs in a connection pool (such as PgBouncer)?

    PgBouncer's transaction pooling mode (pool_mode=transaction) is recommended. At the start of a transaction, the application executes SET LOCAL app.user_id = xxx, which is automatically reset when the transaction ends, leaving no residue after the connection is returned to the pool. If the session pooling mode (pool_mode=session) is used, the application must explicitly execute RESET app.user_id before returning the connection. In transaction pooling mode, if SET (session-level) is used instead of SET LOCAL, the settings will persist in the session, leading to cross-user data leaks. This must be strictly avoided.

  • Question 4: How does RLS interact with views?

    By default, a regular view follows the RLS policy of the current caller. That is, when underlying tables are accessed within a view, the RLS context of the caller is applied. This is the recommended usage. However, functions or views with SECURITY DEFINER are executed as the definer, which may bypass RLS. Therefore, you should use SECURITY INVOKER (default) where possible or explicitly specify the execution context. The refresh of a materialized view is performed by its owner, bypassing RLS. However, when the materialized view is queried, filtering is still performed based on the caller's policy.

  • Question 5: How do I audit which queries are filtered by RLS?

    The following methods can be used for auditing:

    • Use the pg_stat_statements view to observe the number of SQL executions and average execution time to identify slow SQL statements.
    • Enable DDL/DML audit using the pg_audit extension (supported by RDS) to record the SQL executor and SQL text one by one.
    • Add log functions to the policy expression (for example, use triggers or user-defined functions to record access behavior). However, this increases performance overhead and should be used only for troubleshooting.
  • Question 6: Does RLS take effect in multi-layer nested queries?

    Yes. RLS takes effect across all SQL constructs, such as subqueries, JOIN, CTE, views, and UNION, because RLS operates on physical tables rather than the SQL syntax layer. Even in a nested subquery like SELECT * FROM (SELECT * FROM orders) t, access to the orders table in the inner query is filtered by RLS. However, note that the SECURITY DEFINER function switches the context and may bypass RLS.

  • Question 7: How do I handle user_id when importing data in batches?

    Batch imports can be handled in two ways:

    • Application-layer import: Use the app_user role for the connection, execute SET LOCAL app.user_id = xxx at the start of the transaction, and then execute COPY or INSERT. WITH CHECK will verify that the user_id of the imported data must be consistent with the context to prevent unauthorized writes.
    • O&M import: Temporarily use the BYPASSRLS role to directly execute COPY. However, this must be approved and audit logs must be kept. The BYPASSRLS role must be revoked immediately after the import is complete.
  • Question 8: Does RLS support complex business rules?

    Yes. RLS expressions can reference any SQL expression, including subqueries, functions, and JOIN. For example, you can implement a combination of multiple policies to allow user administrators to view all data of the users and regular users to view only the data they created. This can be achieved by creating multiple PERMISSIVE policies (OR relationship) for the same table. However, overly complex policies can affect the optimization space of the SQL planner. It is recommended that policy expressions be simple and indexable. Complex business rules should be placed at the application layer to work with RLS.