AnalyticDB:Row security policies

Policy combination

When multiple policies apply to the same table, their USING expressions are combined with OR (permissive by default). If Policy A allows rows 1 and 2, and Policy B allows row 4, the query returns rows 1, 2, and 4.

Limits

• Row security policies do not apply to privileged accounts or accounts with the BYPASSRLS attribute.

• ALTER TABLE ... ENABLE ROW LEVEL SECURITY does not apply policies to the table owner. To enforce policies on the table owner, run ALTER TABLE ... FORCE ROW LEVEL SECURITY.

For the full CREATE POLICY parameter reference, see Row Security Policies and CREATE POLICY.

Prerequisites

Before you begin, make sure that you have:

• A client tool connected to an AnalyticDB for PostgreSQL database. See Connect to an AnalyticDB for PostgreSQL database.

• Sufficient privileges to create tables, roles, and policies.

Procedure

1. Create a table named logtest and insert test data.

   CREATE TABLE logtest (
       id serial primary key,
       username text,
       log_event text);
   
   INSERT INTO logtest VALUES (1,'report','DELETE issued'),
                              (2,'messaging', 'Message queue purged'),
                              (3,'messaging','Reset accounts'),
                              (4,'report', 'Reset accounts');

2. Create two standard accounts named report and messaging, and grant the SELECT privilege on logtest to both.

   CREATE ROLE report WITH LOGIN ENCRYPTED PASSWORD '111111';
   CREATE ROLE messaging WITH LOGIN ENCRYPTED PASSWORD '111111';
   
   GRANT SELECT ON logtest TO report;
   GRANT SELECT ON logtest TO messaging;

3. Create a row security policy that restricts each account to rows where username matches the current account name.

   CREATE POLICY policy_user_log ON logtest
      FOR ALL
      TO PUBLIC
      USING (username = current_user);

4. Enable row-level security on logtest. All policies take effect immediately.

   Important

   ALTER TABLE ... ENABLE ROW LEVEL SECURITY does not apply policies to the table owner. To apply policies to the table owner, run ALTER TABLE logtest FORCE ROW LEVEL SECURITY.

   ALTER TABLE logtest ENABLE ROW LEVEL SECURITY;

5. Verify that the policy is working by querying logtest with each account.

   • `report` account — only rows where username = 'report' are returned:

     SELECT * FROM logtest;

     id| username | log_event
     --+----------+--------------
     1 | report   | DELETE issued
     4 | report   | Reset accounts
     (2 rows)

   • `messaging` account — only rows where username = 'messaging' are returned:

     SELECT * FROM logtest;

     id| username  | log_event
     --+-----------+---------------------
     2 | messaging | Message queue purged
     3 | messaging | Reset accounts
     (2 rows)

   • Privileged account — all rows are returned because row security policies do not apply to privileged accounts:

     SELECT * FROM logtest;

     id| username  | log_event
     --+-----------+---------------------
     1 | report    | DELETE issued
     2 | messaging | Message queue purged
     3 | messaging | Reset accounts
     4 | report    | Reset accounts
     (4 rows)

6. Add a second policy that restricts results to rows with an even id.

   CREATE POLICY policy_even_ids_only ON logtest
      FOR ALL
      TO PUBLIC
      USING (id % 2 = 0);

   Both policies are now active. Because multiple policies combine with OR, the report account sees rows that satisfy either policy — rows with an even id (rows 2 and 4) or rows where username = 'report' (rows 1 and 4):

   SELECT * FROM logtest;

   id| username  | log_event
   --+-----------+---------------------
   1 | report    | DELETE issued
   2 | messaging | Message queue purged
   4 | report    | Reset accounts
   (3 rows)

Prerequisites

• AnalyticDB for PostgreSQL V7.0.6.0 or later.

• The view caller must have the SELECT privilege on all data exposed by the view.

Create a secure view

When creating the view, set security_invoker to make all queries run under the caller's privileges:

CREATE VIEW v1 WITH (security_invoker) AS SELECT * FROM logtest;

To understand why this matters, compare the behavior without and with security_invoker.

Without `security_invoker` — the report account sees all rows, because the view runs under the definer's (privileged) account:

CREATE VIEW v1 AS SELECT * FROM logtest;
GRANT SELECT ON v1 TO report;

-- Queried by the report account:
SELECT * FROM v1;

id| username  | log_event
--+-----------+---------------------
1 | report    | DELETE issued
2 | messaging | Message queue purged
3 | messaging | Reset accounts
4 | report    | Reset accounts
(4 rows)

With `security_invoker` — the report account sees only its authorized rows, because the view now runs under the caller's privileges:

-- Queried by the report account:
SELECT * FROM v1;

id| username  | log_event
--+-----------+---------------------
1 | report    | DELETE issued
2 | messaging | Message queue purged
4 | report    | Reset accounts
(3 rows)

Enable or disable security invoker on an existing view

-- Enable row security.
ALTER VIEW v1 SET (security_invoker = on);

-- Disable row security.
ALTER VIEW v1 SET (security_invoker = off);