# Enhancement of Row Level Security

> Source chapter 3.8 for PostgreSQL 10.

---

LLMS index: [llms.txt](/en/llms.txt)

---

<div class="feature-source-note"><span class="feature-edition-badge">Beta 1</span><span><strong>Source pages:</strong> PDF 62-65</span><span><a href="https://github.com/nori-shinoda/documents/blob/1773200c5362476c4a549222b7d17fa91f546cae/postgresql10_beta1_new_features_en_20170522-1.pdf">Original PDF on GitHub</a></span></div>

<p class="feature-page-marker" id="pgfeat-10-security-pdf-page-62">PDF page 62</p>

#### 3.8.1 Overview {#pg10-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 {#pg10-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.

**Table 24.** Added column \(pg\_policy catalog\)

| Column name | Data type | Description |
| --- | --- | --- |
| polpermissive | boolean | POLICY mode (PERMISSIVE in the case of true) |

**Table 25.** Added column \(pg\_policies catalog\)

| Column name | Data type | Description |
| --- | --- | --- |
| permissive | text | POLICY mode (PERMISSIVE or RESTRICTIVE) |

#### Syntax 11 - CREATE POLICY statement {#pg10-syntax-11}

```sql
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 {#pg10-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.

<p class="feature-page-marker" id="pgfeat-10-security-pdf-page-63">PDF page 63</p>

**Example 63.** Creating tables and POLICY \(PERMISSIVE \+ RESTRICTIVE\)

```text
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))
```

<p class="feature-page-marker" id="pgfeat-10-security-pdf-page-64">PDF page 64</p>

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

**Example 64.** Confirmation of execution plan \(PERMISSIVE \+ RESTRICTIVE\)

```text
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)
```

```sql
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

```text
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))
```

<p class="feature-page-marker" id="pgfeat-10-security-pdf-page-65">PDF page 65</p>

**Example 66.** Confirmation of execution plan \(RESTRICTIVE \+ RESTRICTIVE\)

```text
postgres=> EXPLAIN SELECT * FROM poltbl1 ;
                 QUERY PLAN
--------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=108)
   One-Time Filter: false
(2 rows)
```

```text
If all policies are in RESTRICTIVE mode, the execution plan does not seem to be displayed.
```