What are row security policies? - AnalyticDB for PostgreSQL

In most cases, you can use database accounts that have read and write permissions on a table to query and update all rows of the table. AnalyticDB for PostgreSQL V7.0 provides row security policies that allow database accounts to query or update only specific rows.

Usage notes

Row security policies do not take effect on privileged accounts or database accounts that have the BYPASSRLS attribute.
If you use the ALTER TABLE ... ENABLE ROW LEVEL SECURITY statement to enable row security, row security policies do not take effect on the table owner. To allow row security policies to take effect on the table owner, execute the ALTER TABLE ... FORCE ROW LEVEL SECURITY statement.
If you configured multiple row security policies for a table, all row security policies take effect on the table. For example, Policy A allows only rows 1 and 2 to be returned and Policy B allows only row 4 to be returned. In this case, rows 1, 2, and 4 are returned.

Row security policy parameters

For information about the row security policy parameters, see Row Security Policies and CREATE POLICY.

Configure row security policies for a table

Use a client tool to connect to an AnalyticDB for PostgreSQL database.

Create a table named logtest and insert test data into the table.

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');

Create two standard accounts named report and messaging, and grant the SELECT permission on the logtest table to the accounts.

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;

Create a row security policy that allows only the rows whose value in the username column is the same as the current database account to be returned.
```
CREATE POLICY policy_user_log ON logtest
   FOR ALL
   TO PUBLIC
   USING (username = current_user);
```
Enable row security for the logtest table. After you enable row security for the logtest table, all row security policies of the table take effect.
```
ALTER TABLE logtest ENABLE ROW LEVEL SECURITY;
```
Important
- If you use the preceding method to enable row security for the logtest table, the row security policy that you configured does not take effect on the table owner. To allow the row security policy to take effect on the table owner, execute the ALTER TABLE ... FORCE ROW LEVEL SECURITY statement.
- You can configure multiple row security policies for each table.

Query the logtest table by using the privileged account and the two standard accounts.

Use the report account to query the logtest table.

SELECT * FROM logtest;

Only the rows whose value is report in the username column are returned.

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

Use the messaging account to query the logtest table.

SELECT * FROM logtest;

Only the rows whose value is messaging in the username column are returned.

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

Use the privileged account to query the logtest table.

SELECT * FROM logtest;

All rows are returned.

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

Create another row security policy that allows only the rows whose value is an even number in the id column to be returned.
```
 CREATE POLICY policy_even_ids_only ON logtest
   FOR ALL
   TO PUBLIC
   USING (id % 2 = 0);
```

Use the report account to query the logtest table.

SELECT * FROM logtest;

The rows whose value is an even number in the id column and the rows whose value is report in the username column are returned.

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

Apply row security policies to views

Usage notes

You can apply row security policies to views only for AnalyticDB for PostgreSQL instances of V7.0.6.0 or later.
All queries on a view for which the security_invoker parameter is specified are executed by using the permissions of the view caller. In this case, the view caller must have the SELECT permission on all data of the view.

Overview

You may need to create views by using base tables, but the row security policies that are configured for base tables do not take effect on standard accounts. In this case, you can create secure views that inherit the row security policies from base tables.

Examples of row security policies that do not take effect on standard accounts

Create a view name v1 based on the logtest table and grant the SELECT permission to the report account.
```
CREATE VIEW v1 as SELECT * FROM logtest;
GRANT SELECT ON v1 TO report;
```
Use the report account to query the v1 view.
```
SELECT * FROM v1;
```

The row security policies that you configured for the logtest table do not take effect.

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

Create a secure view

If you want to apply row security policies to a view, you must specify the security_invoker parameter when you create the view to improve data security. The security_invoker parameter specifies that all queries on the view are executed by using the permissions of the view caller instead of the permissions of the view definer.

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

Modify the row security policies of a view

You can modify the row security policies of a view by enabling or disabling row security.

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

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

Query a secure view

After you enable row security, use the report account to query the v1 view.

SELECT * FROM v1;

The row security policies that you configured for the logtest table take effect.

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