7 Row Level Security

PDF page 60

3.5.1 What is Row Level Security

In previous PostgreSQL, the privilege to access to tables and columns is specified by GRANT statement. This style is available even in PostgreSQL 9.5. Row Level Security is a feature that allows you to limit the tuples (records), which were allowed in the GRANT statement also with tuple level. Row Level Security to by the access restriction you create an object called POLICY.

PDF page 60

3.5.2 Preparation

In order to use Row Level Security, execute the ALTER TABLE ENABLE ROW LEVEL SECURITY statement to the table to be restricted by policy. By default Row Level Security setting of the table is disabled. To disable the setting for the table, run the ALTER TABLE DISABLE ROW LEVEL SECURITY statement.

PDF page 61

postgres=> ALTER TABLE poltbl1 ENABLE ROW LEVEL SECURITY ;
ALTER TABLE
postgres=> \d+ poltbl1
                               Table "public.poltbl1"
 Column |         Type      | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+------------
 c1     | numeric            |        | main     |          |
 c2     | character varying(10) |         | extended |          |
 uname  | character varying(10) |         | extended |          |
Policies (Row Security Enabled): (None)

3.5.3 Create POLICY object

To specify access privileges for a table, create the policy. Policy is created with the CREATE POLICY statement. General users can create POLICY. Syntax CREATE POLICY policy_name ON table_name [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ] [ TO { roles | PUBLIC, …} ] USING (condition) [ WITH CHECK (check_condition) ]

PDF page 62

In the example below, the POLICY to the table poltbl1 is created. Since TO clause is omitted, the subject is all users (PUBLIC), the operation is executed to all the SQL (FOR ALL), performed tuple is the one whose “uname” column’s value is the same as the current username (current_user function).

postgres=> CREATE POLICY pol1 ON poltbl1 FOR ALL USING (uname = current_user) ;
CREATE POLICY
postgres=> \d+ poltbl1
                               Table "public.poltbl1"
Column |         Type       | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+------------
 c1     | numeric            |        | main     |            |
 c2     | character varying(10) |         | extended |            |
 uname  | character varying(10) |         | extended |            |
Policies:
    POLICY "pol1" FOR ALL
      USING (((uname)::name = "current_user"()))
Information of the created policy can be checked from pg_policy catalog. The information of the
table that has been set a policy can be checked from pg_policies catalog.

PDF page 63

  In the following example, I verify the effect of the policy as follows:

User “user1”, who is the owner of the table poltbl1, stores 3 records (2-12 lines).

Although table tblpol1 is searched with the privilege of user “user2”, only one record whose

“uname” column’s value is “user2” is referenced (15-19 lines).

The user was trying to change the value of “uname” column, however, because of the

deviation of the condition specified in USING clause of the CREATE POLICY statement, UPDATE statement failed (20-21 lines).

1
$ psql -U user1
2
postgres=> INSERT INTO poltbl1 VALUES (100, 'Val100', 'user1') ;
3
INSERT 0 1
4
postgres=> INSERT INTO poltbl1 VALUES (200, 'Val200', 'user2') ;
5
INSERT 0 1
6
postgres=> INSERT INTO poltbl1 VALUES (300, 'Val300', 'user3') ;
7
INSERT 0 1
8
postgres=> SELECT COUNT(*) FROM poltbl1 ;
9
 count
10
-------
11
     3
12
(1 row)
13
14
$ psql -U user2
15
postgres=> SELECT * FROM poltbl1 ;
16
c1  |   c2   | uname
17
-----+--------+-------
18
 200 | val200 | user2
19
(1 row)
20
postgres=> UPDATE poltbl1 SET uname='user3' ;
21
ERROR:  new row violates row level security policy for "poltbl1"
CREATE POLICY statement, which create a policy, does not result in an error when you run it to
the table which is not specified ENABLE ROW LEVEL SECURITY clause. In this case, ROW
LEVEL SECURITY feature is not enabled for the corresponding table; only the authorization by
the GRANT statement is enabled.

PDF page 64

To change or delete policy setting is performed in ALTER POLICY statement or DROP POLICY statement, respectively.

3.5.4 Parameter Settings

Feature of Row Level Security is controlled by the parameter row_security. It can take the following values.

User Privileges

User with SUPERUSER privilege will be able to bypass the policy setting. User with BYPASSRLS privilege can bypass the policy by setting the parameter row_security to off (row_security can be set per session). General user who don’t have BYPASSRLS privileged cannot access the table. NOBYPASSRLS is specified as the default of CREATE USER statement.