Row-level security (RLS), also known as row security policy, is used to control access to the table rows including INSERT, UPDATE, SELECT, and DELETE. Table truncate as well as referential integrity constraints such as the primary key and unique and foreign keys are not subject to row security. In addition to this, superusers bypass the row-level security. To enable row security, one needs to use the ALTER statement for each table as follows:

ALTER TABLE <table_name> ENABLE ROW LEVEL SECURITY

In order to use RLS, one needs to define some polices on how a certain role or roles in the database can access a certain set of rows. For this reason, often the role is embedded in the tables. The following sample code creates two users and a table and enables RLS for that table:

CREATE DATABASE test_rls;
c test_rls
CREATE USER admin;
CREATE USER guest;
CREATE TABLE account ( 
  account_name NAME,
  password TEXT
  );
INSERT INTO account VALUES('admin', 'admin'), ('guest', 'guest');
GRANT ALL ON  account to admin, guest;
ALTER TABLE account ENABLE ROW LEVEL SECURITY;

By default, if no policy is defined, then the user will be restricted to access the rows as follows:

test_rls=# SET ROLE admin;
test_rls=> table account;
 account_name | password 
--------------+----------
(0 rows)

One could define a policy as follows:

CREATE POLICY account_policy_user ON account USING (account_name = current_user);
test_rls=# SET ROLE admin;
test_rls=> Table account;
 account_name | password 
--------------+----------
 admin | admin
(1 row)

The preceding code simply matches each row account_name and current_user and if the match returned true, then the row is returned. Note that this policy is not restricted for a certain operation, so the policy is applicable for INSERT, UPDATE, and DELETE as well.

test_rls=# SET ROLE admin;
test_rls=> INSERT INTO account values('guest', 'guest');
ERROR: new row violates row-level security policy for table "account"

The syntax to create a policy is as follows:

CREATE POLICY name ON table_name
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

The create policy is very flexible, one can creates policy for a certain operation or a certain condition, using any expression returning a Boolean value. WITH CHECK is used to validate new inserted or updated rows. For example, we would like the users to see all the content of the account table but only modify their own rows:

CREATE POLICY account_policy_write_protected ON account USING (true) WITH CHECK (account_name = current_user);

test_rls=# SET ROLE admin;
test_rls=> Table account;
 account_name | password 
--------------+----------
 admin | admin
 guest | guest
(2 rows)

test_rls=> INSERT INTO account values('guest', 'guest');
ERROR: new row violates row-level security policy for table "account"

The policies by default are permissive policies, which means that they are combined using the OR operator. Two polices were created as shown, one allows the users to see their own rows and one allows the users to see all rows, in total the user will be able to see all rows:

d account 
                Table "public.account"
    Column | Type | Collation | Nullable | Default 
--------------+------+-----------+----------+---------
 account_name | name | | | 
 password | text | | | 
Policies:
    POLICY "account_policy_write_protected"
      USING (true)
      WITH CHECK ((account_name = CURRENT_USER))
    POLICY "account_user"
      USING ((account_name = CURRENT_USER))

Restrictive polices are supported in PostgreSQL, that is, the policies are combined using the AND logical operator. Let's now restrict table access based on working hours:

CREATE POLICY account_policy_time ON account AS RESTRICTIVE USING ( date_part('hour', statement_timestamp()) BETWEEN 8 AND 16 ) WITH CHECK (account_name = current_user);
test_rls=# set role admin;
test_rls=# select now();
              now 
-------------------------------
 2017-10-07 17:42:34.663909+02
(1 row)
test_rls=> table account;
 account_name | password 
--------------+----------
(0 rows)

As seen in the example, no rows were returned due to adding the restrictive policy. The rows are filtered out by the expression; the filtering of rows is calculated using admin as the role:

All the tuples or rows are filtered out because of the account_policy_time policy , in the preceding example the final result for the tuple (admin, admin) is calculated as True OR True AND False which evaluates to False.