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.

Row Access Policy Use Case

You can also create a row access policy on the same table. For example, you can modify access to a customer table so a manager can view data in all rows. However, a broker can see a row only if the customer is associated with that broker:

      
=> select * from customers_table;

custID | password |     ssn
-------+----------+---------
    1  | secret   | 12345678901
    2  | secret   | 12345678902
    3  | secret   | 12345678903
(3 rows)
    

Each customer in the customers_table has an assigned broker:

      
=> select * from broker_info;

broker  | custID
--------+---------
u1      |     1
u2      |     2
u3      |     3
    

Create the access policy to allow a manager to see all data in all rows. Limit a broker's view to only those customers to which the broker is assigned:

=> CREATE ACCESS POLICY ON schema.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;

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)