Row Access Policy

Use the CREATE ACCESS POLICY statement to create a row access policy for a specific row in a table. You must use a WHERE clause to set the access policy's condition.

Example

Suppose you have a customers table but want to limit users with the broker role to being able to view only customers to which they are assigned. This example shows how to create a row access policy that allows managers to view everything and brokers to view only their customers.

Run the following SQL statement:

=> SELECT * FROM customers_table; 

The customers_table appears as follows:

custID  |  password  |    SSN
--------+------------+--------------
  1     |   secret   |    123456789
  2     |   secret   |    123456780
  3     |   secret   |    123456781
(3 rows)

Run the following SQL statement:

=> SELECT * FROM broker_info; 

The broker_info table shows that each customer has an assigned broker:

broker  |  custID 
--------+---------
  user1 |        1  
  user2 |        2  
  user3 |        3  
(3 rows)

Create the following access policy that only allows brokers to see customers to which they are associated:

=> CREATE ACCESS POLICY on customers_table for rows
WHERE
   ENABLED_ROLE('manager')
   or
   (ENABLED_ROLE('broker') AND customers_table.custID in (SELECT broker_info.custID FROM broker_info
   WHERE broker = CURRENT_USER()))
ENABLE;

As user1, run the following SQL command:

user1=> SELECT * FROM customers_table; 

The following is returned because user1 is associated with custID 1:

custID  |  password  |    SSN
--------+------------+--------------
  1     |   secret   |    123456789
(1 rows)