and foreign key references, will bypass row security to ensure that

data integrity is maintained. Care must be taken when developing

schemas and row level policies to avoid a "covert channel" leak of

information through thesereferntial integrity checks.

information through thesereferential integrity checks.

</para>

<para>

normal privileges system.

</para>

<para>

Below is a larger example of how this feature can be used in

production environments, based on a unix password file.

</para>

<programlisting>

-- Simple passwd-file based example

CREATE TABLE passwd (

username text UNIQUE NOT NULL,

pwhash text,

uid int PRIMARY KEY,

gid int NOT NULL,

real_name text NOT NULL,

home_phone text,

extra_info text,

home_dir text NOT NULL,

shell text NOT NULL

);

CREATE ROLE admin; -- Administrator

CREATE ROLE bob; -- Normal user

CREATE ROLE alice; -- Normal user

-- Populate the table

INSERT INTO passwd VALUES

('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');

INSERT INTO passwd VALUES

('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');

INSERT INTO passwd VALUES

('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- Be sure to enable row level security on the table

ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Create policies

-- Administrator can see all rows and add any rows

CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);

-- Normal users can view all rows

CREATE POLICY all_view ON passwd FOR SELECT USING (true);

-- Normal users can update their own records, but

-- limit which shells a normal user is allowed to set

CREATE POLICY user_mod ON passwd FOR UPDATE

USING (current_user = username)

WITH CHECK (

current_user = username AND

shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')

);

-- Allow admin all normal rights

GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;

-- Users only get select access on public columns

GRANT SELECT

(username, uid, gid, real_name, home_phone, extra_info, home_dir, shell)

ON passwd TO public;

-- Allow users to update certain columns

GRANT UPDATE

(pwhash, real_name, home_phone, extra_info, shell)

ON passwd TO public;

</programlisting>

<para>

As with any security settings, it's important to test and ensure that

the system is behaving as expected. Using the example above, this

demonstrates that the permission system is working properly.

</para>

<programlisting>

-- admin can view all rows and fields

postgres=> set role admin;

SET

postgres=> table passwd;

username | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell

----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------

admin | xxx | 0 | 0 | Admin | 111-222-3333 | | /root | /bin/dash

bob | xxx | 1 | 1 | Bob | 123-456-7890 | | /home/bob | /bin/zsh

alice | xxx | 2 | 1 | Alice | 098-765-4321 | | /home/alice | /bin/zsh

(3 rows)

-- Test what Alice is able to do

postgres=> set role alice;

SET

postgres=> table passwd;

ERROR: permission denied for relation passwd

postgres=> select username,real_name,home_phone,extra_info,home_dir,shell from passwd;

username | real_name | home_phone | extra_info | home_dir | shell

----------+-----------+--------------+------------+-------------+-----------

admin | Admin | 111-222-3333 | | /root | /bin/dash

bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh

alice | Alice | 098-765-4321 | | /home/alice | /bin/zsh

(3 rows)

postgres=> update passwd set username = 'joe';

ERROR: permission denied for relation passwd

-- Allowed to change her own real_name, but no others

postgres=> update passwd set real_name = 'Alice Doe';

UPDATE 1

postgres=> update passwd set real_name = 'John Doe' where username = 'admin';

UPDATE 0

postgres=> update passwd set shell = '/bin/xx';

ERROR: new row violates WITH CHECK OPTION for "passwd"

postgres=> delete from passwd;

ERROR: permission denied for relation passwd

postgres=> insert into passwd (username) values ('xxx');

ERROR: permission denied for relation passwd

-- Alice can change her own password

postgres=> update passwd set pwhash = 'abc';

UPDATE 1

</programlisting>

</sect1>

<sect1 id="ddl-schemas">

security-barrier qualification to queries which use the table

automatically. If multiple policies are being applied for a given

table then they are all combined and added using OR. The USING

expression applies to records which are beingretrived from the table.

expression applies to records which are beingretrieved from the table.

</para>

</listitem>

</varlistentry>

referenced table. These issues can be addressed by carefully crafting

policies which prevent users from being able to insert, delete, or update

records at all which might possibly indicate a value they are not otherwise

able to see, or by using generated values (eg: surrogate keys) instead.

able to see, or by using generated values (e.g.: surrogate keys) instead.

</para>

<para>

records from the relation which pass the <literal>SELECT</literal>

policy will be returned, even if other records exist in the relation.

The <literal>SELECT</literal> policy only accepts the USING expression

as it only ever applies in cases where records are beingretrived from

as it only ever applies in cases where records are beingretrieved from

the relation.

</para>

</listitem>

for the <literal>DELETE</literal>, and rows which are not visible

through the <literal>SELECT</literal> policy may be deleted if they

pass the <literal>DELETE</literal> USING policy. The

<literal>DELETE</literal> policy onlyaccept the USING expression as

<literal>DELETE</literal> policy onlyaccepts the USING expression as

it only ever applies in cases where records are being extracted from

the relation for deletion.

</para>

return false;

if (policy1->cmd!=policy2->cmd)

return false;

if (policy1->hassublinks!=policy2->hassublinks);

if (policy1->hassublinks!=policy2->hassublinks)

return false;

if (strcmp(policy1->policy_name,policy2->policy_name)!=0)

return false;

return false;

}

returnfalse;

returntrue;

}

boolhasindex;/* does it have any indexes? */

boolhasrules;/* does it have any rules? */

boolhastriggers;/* does it have any triggers? */

boolrowsec;/*does it have anyrow-securitypolicy? */

boolrowsec;/*isrow-securityenabled? */

boolhasoids;/* does it have OIDs? */

uint32frozenxid;/* for restore frozen xid */

uint32minmxid;/* for restore min multi xid */

( 7, 33, 2, 'rls_regress_user2', 'great technology book'),

( 8, 44, 1, 'rls_regress_user2', 'great manga');

ALTER TABLE document ENABLE ROW LEVEL SECURITY;

-- user's security level must be higherthat or equal to document's

-- user's security level must be higherthan or equal to document's

CREATE POLICY p1 ON document

USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));

-- viewpoint from rls_regress_user1

DELETE FROM category WHERE cid = 33; -- fails with FK violation

ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"

DETAIL: Key (cid)=(33) is still referenced from table "document".

--cannot insert FK referencing invisible PK

--can insert FK referencing invisible PK

SET SESSION AUTHORIZATION rls_regress_user2;

SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;

did | cid | dlevel | dauthor | dtitle | cid | cname

-----+-----+--------+---------+--------

(0 rows)

-- database superusercannot bypass RLS policy when enabled

-- database superuserdoes bypass RLS policy when enabled

RESET SESSION AUTHORIZATION;

SET row_security TO ON;

SELECT * FROM document;

44 | manga

(4 rows)

-- database superusercannot bypass RLS policy when FORCE enabled.

-- database superuserdoes not bypass RLS policy when FORCE enabled.

RESET SESSION AUTHORIZATION;

SET row_security TO FORCE;

SELECT * FROM document;

-----+-------

(0 rows)

-- database superusercan bypass RLS policy when disabled

-- database superuserdoes bypass RLS policy when disabled

RESET SESSION AUTHORIZATION;

SET row_security TO OFF;

SELECT * FROM document;

ALTERTABLE document ENABLE ROW LEVEL SECURITY;

CREATE POLICY p1ON document

USING (dlevel<= (SELECT seclvFROM uaccountWHERE pguser=current_user));

SELECT*FROM document d FULL OUTERJOIN category cond.cid=c.cid;

DELETEFROM categoryWHERE cid=33;-- fails with FK violation

SET SESSION AUTHORIZATION rls_regress_user2;

SELECT*FROM document d FULL OUTERJOIN category cond.cid=c.cid;

INSERT INTO documentVALUES (10,33,1,current_user,'hoge');

INSERT INTO documentVALUES (8,44,1,'rls_regress_user1','my third manga');-- Must fail with unique violation, revealing presence of did we can't see

SELECT*FROM documentWHERE did=8;-- and confirm we can't see it

RESET SESSION AUTHORIZATION;

SET row_security TOON;

SELECT*FROM document;

SELECT*FROM category;

RESET SESSION AUTHORIZATION;

SET row_security TO FORCE;

SELECT*FROM document;

SELECT*FROM category;

RESET SESSION AUTHORIZATION;

SET row_security TO OFF;

SELECT*FROM document;