PolarDB:Row Security Policies

How it works

When a user runs a query against a table with RLS enabled:

1. The database checks which policies apply to the current user and command type.

2. Each applicable policy expression is evaluated for every candidate row, before any conditions from the user's own query are applied.

3. Rows where the expression does not return true are filtered out silently.

Policy expressions run with the privileges of the user executing the query. Use security-definer functions when a policy needs to access data that the calling user cannot directly read.

leakproof functions are an exception: the optimizer may evaluate them before the row-security check, because they are guaranteed not to leak information.

Key concepts

Use case: restrict managers to their own accounts

Allow only members of the managers role to access rows in the accounts table, and only rows where the manager column matches their own username.

CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

Because no separate WITH CHECK is specified, PolarDB for Oracle applies the same USING expression as the write constraint. A manager cannot SELECT, UPDATE, or DELETE rows belonging to another manager, and cannot INSERT or UPDATE rows to set a different manager's name.

Use case: let all users read, but only modify their own row

To allow all users to access only their own row in a users table, a simple policy can be used:

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

To use a different policy for rows that are being added to the table compared to those rows that are visible, multiple policies can be combined. This pair of policies allows all users to view all rows in the users table, but only modify their own:

-- All users can read all rows
CREATE POLICY user_sel_policy ON users
    FOR SELECT
    USING (true);

-- Each user can only modify their own row
CREATE POLICY user_mod_policy ON users
    USING (user_name = current_user);

For SELECT, the two policies combine with OR: all rows are visible. For INSERT, UPDATE, and DELETE, only user_mod_policy applies, so users are limited to their own rows.

Use case: production example with multiple roles and policies

This example emulates a Unix-style password file with separate administrator and user permissions.

-- Schema
CREATE TABLE passwd (
    user_name   text UNIQUE NOT NULL,
    pwhash      text,
    uid         int  PRIMARY KEY,
    gid         int  NOT NULL,
    real_name   text NOT NULL,
    home_phone  text,
    extra_info  text,
    home_dir    text NOT NULL,
    shell       text NOT NULL
);

CREATE ROLE admin;  -- Administrator
CREATE ROLE bob;    -- Normal user
CREATE ROLE alice;  -- Normal user

-- Seed data
INSERT INTO passwd VALUES
    ('admin', 'xxx', 0, 0, 'Admin', '111-222-3333', null, '/root',       '/bin/dash');
INSERT INTO passwd VALUES
    ('bob',   'xxx', 1, 1, 'Bob',   '123-456-7890', null, '/home/bob',   '/bin/zsh');
INSERT INTO passwd VALUES
    ('alice',  'xxx', 2, 1, 'Alice', '098-765-4321', null, '/home/alice', '/bin/zsh');

ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Admin can read and write all rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);

-- All users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);

-- Users can update only their own record, and only to an allowed shell
CREATE POLICY user_mod ON passwd FOR UPDATE
    USING (current_user = user_name)
    WITH CHECK (
        current_user = user_name AND
        shell IN ('/bin/bash', '/bin/sh', '/bin/dash', '/bin/zsh', '/bin/tcsh')
    );

-- Grant admin full access
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;

-- Public can read non-sensitive columns
GRANT SELECT
    (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
    ON passwd TO public;

-- Users can update their own profile fields
GRANT UPDATE
    (pwhash, real_name, home_phone, extra_info, shell)
    ON passwd TO public;

-- admin sees all rows and all columns
SET ROLE admin;
TABLE passwd;
-- Returns all 3 rows with all columns including pwhash

-- alice cannot access the table directly (no SELECT on all columns granted)
SET ROLE alice;
TABLE passwd;
-- ERROR: permission denied for table passwd

-- alice can only read permitted columns
SELECT user_name, real_name, home_phone, extra_info, home_dir, shell FROM passwd;
-- Returns all 3 rows (allowed by all_view + column-level GRANT)

-- alice cannot update the user_name column (no permission)
UPDATE passwd SET user_name = 'joe';
-- ERROR: permission denied for table passwd

-- alice can update her own real_name
UPDATE passwd SET real_name = 'Alice Doe';
-- UPDATE 1

-- alice cannot update a different user's record (RLS silently filters)
UPDATE passwd SET real_name = 'John Doe' WHERE user_name = 'admin';
-- UPDATE 0

-- alice cannot set an unlisted shell
UPDATE passwd SET shell = '/bin/xx';
-- ERROR: new row violates WITH CHECK OPTION for "passwd"

-- alice cannot delete rows (no DELETE privilege)
DELETE FROM passwd;
-- ERROR: permission denied for table passwd

-- alice cannot insert rows (no INSERT privilege)
INSERT INTO passwd (user_name) VALUES ('xxx');
-- ERROR: permission denied for table passwd

-- alice can change her own password; other rows are silently excluded
UPDATE passwd SET pwhash = 'abc';
-- UPDATE 1

Use case: combine permissive and restrictive policies

Building on the passwd example, add a restrictive policy so the admin role can only access records when connected over a local Unix socket (not over a network connection).

CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
    USING (pg_catalog.inet_client_addr() IS NULL);

With this policy in place, an admin connecting from a remote IP sees no rows:

-- Connected remotely (inet_client_addr() returns an IP address, not NULL)
SELECT current_user;
--  current_user
-- --------------
--  admin

SELECT inet_client_addr();
--  inet_client_addr
-- ------------------
--  127.0.0.1

TABLE passwd;
--  user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-- -----------+--------+-----+-----+-----------+------------+------------+----------+-------
-- (0 rows)

UPDATE passwd SET pwhash = NULL;
-- UPDATE 0

Performance considerations

Policy expressions that reference only columns of the current row have the lowest overhead and are the most straightforward to reason about. Design policies this way when possible.

If a policy must consult other rows or tables—using subqueries or functions containing SELECT statements—be aware of the following:

• Race conditions: A subquery in a policy expression reads a snapshot of the referenced table. If another transaction modifies that table concurrently, the policy may evaluate against stale data, potentially allowing a user to see rows they should not. The following schema illustrates this risk. A policy grants access to rows in information based on the user's group_id in the users table:

  -- Privilege groups
  CREATE TABLE groups (group_id int PRIMARY KEY, group_name text NOT NULL);
  INSERT INTO groups VALUES (1, 'low'), (2, 'medium'), (5, 'high');
  GRANT ALL ON groups TO alice;
  GRANT SELECT ON groups TO public;
  
  -- User privilege levels
  CREATE TABLE users (user_name text PRIMARY KEY, group_id int NOT NULL REFERENCES groups);
  INSERT INTO users VALUES ('alice', 5), ('bob', 2), ('mallory', 2);
  GRANT ALL ON users TO alice;
  GRANT SELECT ON users TO public;
  
  -- Protected data
  CREATE TABLE information (info text, group_id int NOT NULL REFERENCES groups);
  INSERT INTO information VALUES ('barely secret', 1), ('slightly secret', 2), ('very secret', 5);
  ALTER TABLE information ENABLE ROW LEVEL SECURITY;
  
  -- A row is visible to users whose group_id >= the row's group_id
  CREATE POLICY fp_s ON information FOR SELECT
    USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
  CREATE POLICY fp_u ON information FOR UPDATE
    USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
  
  GRANT ALL ON information TO public;

  If alice runs the following transaction to revoke mallory's access and update a sensitive row:

  BEGIN;
  UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
  UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
  COMMIT;

  And mallory concurrently runs:

  SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

  In READ COMMITTED mode, mallory's transaction may read alice's updated row in information while still using the pre-update snapshot of users. This happens because mallory's transaction blocks waiting for alice's to commit, then fetches the updated information row due to the FOR UPDATE clause—but the users sub-SELECT does not have FOR UPDATE, so it reads mallory's old (higher) privilege level and grants access to the newly written value. To mitigate this:

  • Use SELECT ... FOR SHARE in the subquery to take a shared lock on referenced rows. This requires granting UPDATE privilege on the referenced table, or encapsulating the subquery in a security-definer function. Note that heavy concurrent use of row share locks on the referenced table can pose a performance problem, especially if updates are frequent.

  • Take an ACCESS EXCLUSIVE lock on the referenced table before updating it, preventing concurrent reads of stale values.

  • Wait for all concurrent transactions to finish before making changes that rely on the updated security configuration.

• Referential integrity bypass: Unique constraints, primary key constraints, and foreign key checks always bypass row security to preserve data integrity. Design your schema carefully to avoid "covert channel" leaks through these checks.

Usage notes

• Set the row_security configuration parameter to off to detect queries whose results would be silently filtered by a policy. Instead of filtering rows, the database raises an error, making it easier to identify and investigate unexpected RLS behavior. This does not bypass row security—it makes silent filtering visible. When taking a database backup, it could be disastrous if row security silently caused some rows to be omitted; setting row_security = off ensures such omissions surface as errors rather than going undetected.

• Policies are table-specific. Enabling or disabling RLS on a table does not affect policies on other tables.

Troubleshooting