Skip to content

10 Enhancement of Row Level Security

Source chapter 3.8 for PostgreSQL 10.
Beta 1Source pages: PDF 62-65Original PDF on GitHub

PDF page 62

3.8.1 Overview

When multiple policies were set for a table, policies were determined by OR condition in PostgreSQL

9.6 and earlier. In PostgreSQL 10 it is possible to specify a policy with an AND condition. The AS

PERMISSIVE clause and the AS RESTRICTIVE clause can now be specified in the CREATE POLICY statement that create the policy. When specifying the AS PERMISSIVE clause, the restriction becomes loose (OR), and if AS RESTRICTIVE is specified, the limit becomes strict (AND). When designation is omitted, it becomes the same as in the previous version. Along with this, a column indicating condition specification has been added to the pg_policy catalog and the pg_policies catalog.

Column nameData typeDescription
polpermissivebooleanPOLICY mode (PERMISSIVE in the case of true)
Table 24 Added column (pg_policy catalog)
Column nameData typeDescription
permissivetextPOLICY mode (PERMISSIVE or RESTRICTIVE)
Table 25 Added column (pg_policies catalog)

Syntax 11 - CREATE POLICY statement

CREATE POLICY policy_name ON table_name
 [ AS { PERMISSIVE | RESTRICTIVE } ]
 [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
 [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
 [ USING ( using_expression ) ]

3.8.2 Validation of multiple POLICY setting

The author set multiple POLICY for the table and verified the effect. POLICY pol1 in PERMISSIVE mode and POLICY pol2, pol3 in RESTRICTIVE mode were prepared for the table poltbl1 and veryfied by combining them.

PDF page 63

Example 63 Creating tables and POLICY (PERMISSIVE + RESTRICTIVE)
postgres=> CREATE TABLE poltbl1 (c1 NUMERIC, c2 VARCHAR(10), uname VARCHAR(10)) ;
CREATE TABLE
postgres=> ALTER TABLE poltbl1 ENABLE ROW LEVEL SECURITY ;
ALTER TABLE
postgres=> CREATE POLICY pol1 ON poltbl1 FOR ALL USING (uname = current_user) ;
CREATE POLICY
postgres=> CREATE POLICY pol2 ON poltbl1 AS RESTRICTIVE FOR ALL USING (c2 =
'data') ;
CREATE POLICY
postgres=> SELECT polname, polpermissive FROM pg_policy ;
 polname | polpermissive
---------+---------------
 pol1    | t
 pol2    | f
(2 rows)
postgres=> SELECT tablename, policyname, permissive FROM pg_policies ;
 tablename | policyname | permissive
-----------+------------+-------------
 poltbl1   | pol1       | PERMISSIVE
 poltbl1   | pol2       | RESTRICTIVE
(2 rows)
postgres=> \d poltbl1
                     Table "public.poltbl1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | numeric               |           |          |
 c2     | character varying(10) |           |          |
 uname  | character varying(10) |           |          |
Policies:
    POLICY "pol1"
      USING (((uname)::name = CURRENT_USER))
    POLICY "pol2" AS RESTRICTIVE
      USING (((c2)::text = 'data'::text))

PDF page 64

In the above example, the setting of the table poltbl1 shows that POLICY pol2 is RESTRICTIVE.

Example 64 Confirmation of execution plan (PERMISSIVE + RESTRICTIVE)
postgres=> EXPLAIN SELECT * FROM poltbl1 ;
                                 QUERY PLAN
--------------------------------------------------------------------------
 Seq Scan on poltbl1  (cost=0.00..20.50 rows=1 width=108)
   Filter: (((c2)::text = 'data'::text) AND ((uname)::name = CURRENT_USER))
(2 rows)
It can be seen that the two conditions are combined by "AND". Next, delete POLICY pol1 and create
a table applying POLICY pol3 in RESTRICTIVE mode.
Example 65 Create POLICIES in RESTRICTIVE mode
postgres=> CREATE POLICY pol3 ON poltbl1 AS RESTRICTIVE FOR ALL USING
(c1 > 1000) ;
CREATE POLICY
postgres=> \d poltbl1
                     Table "public.poltbl1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | numeric               |           |          |
 c2     | character varying(10) |           |          |
 uname  | character varying(10) |           |          |
Policies:
    POLICY "pol2" AS RESTRICTIVE
      USING (((c2)::text = 'data'::text))
    POLICY "pol3" AS RESTRICTIVE
      USING ((c1 > (1000)::numeric))

PDF page 65

Example 66 Confirmation of execution plan (RESTRICTIVE + RESTRICTIVE)
postgres=> EXPLAIN SELECT * FROM poltbl1 ;
                 QUERY PLAN
--------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=108)
   One-Time Filter: false
(2 rows)
If all policies are in RESTRICTIVE mode, the execution plan does not seem to be displayed.