Row-level security

Contents

Preparing
Creating a table
Creating users
- Creating a policy
Enabling the row-level security
Bypassing the row-level security policy
Drop a policy
Disabling the row-level security

Row-level security allows you to create policies for rows of the table in such a way that each user has access to them. The row-level policy is applied before the user attempts to perform any operation.

Also, a row-level security policy can be created for commands such as SELECT, INSERT, UPDATE and DELETE. Row-level security policies can be created for a specific role or for multiple roles.

The article provides an example of creating a row-level security policy to delimit user access.

Preparing

Get access to the ADPG server console by logging via a trusted account.
Switch to the postgres user.
```
$ sudo su - postgres
```
Go to the ADPG interactive terminal.
```
$ psql
```

Creating a table

Create a demo table.

CREATE TABLE demo ( numb int, role text, name text, perm int );

Result

postgres=# CREATE TABLE demo ( numb int, role text, name text, perm int );
CREATE TABLE

Add the data to the first row.

INSERT INTO demo VALUES (1, 'user', 'student01', 1 );

Result

postgres=# INSERT INTO demo values (1, 'user', 'student01', 1 );
INSERT 0 1

Add the data to the second row.

INSERT INTO demo VALUES (2, 'non-user', 'student02', 0 );

Result

postgres=# INSERT INTO demo values (2, 'non-user', 'student02', 0 );
INSERT 0 1

Output the data from the table.

SELECT * FROM demo;

Result

postgres=# select * from demo;
numb | role | name | perm
------+----------+-----------+------
1 | user | student 01 | 1
2 | non-user | student 02 | 0
(2 rows)

Creating users

Now you need to create users based on the records in the rows and grant them access to the table.

Create the user student01.

CREATE USER student01;

Result

postgres=# CREATE USER student01;
CREATE ROLE

Create the user student02.

CREATE USER student02;

Result

postgres=# CREATE USER student02;
CREATE ROLE

Grant the user student01 permission to access the demo table.

GRANT SELECT ON demo TO student01;

Result

postgres=# GRANT SELECT ON demo TO student01;
GRANT

Grant the user student02 permission to access the demo table.
```
GRANT SELECT ON demo TO student02;
```
Result
```
postgres=# GRANT SELECT ON demo TO student02;
GRANT
```
Now the users student01 and student02 have privileges to view data in the demo table.

Connect to the database as the user student01.

\c postgres student01

Result

postgres=# \c postgres student01
You are now connected to database "postgres" as user "student01".

Check the user student01 privileges to access to the demo table.

SELECT * FROM demo;

Result

postgres=> SELECT * FROM demo;
 numb |   role   |   name    | perm
------+----------+-----------+------
    1 | user     | student01 |    1
    2 | non-user | student02 |    0
(2 rows)

Connect to the database as user student02.

\c postgres student02

Result

postgres=> \c postgres student02
You are now connected to database "postgres" as user "student02".

Check the right of the user student02 to access the demo table.

SELECT * FROM demo;

Result

postgres=> SELECT * FROM demo;
 numb |   role   |   name    | perm
------+----------+-----------+------
    1 | user     | student01 |    1
    2 | non-user | student02 |    0
(2 rows)

Creating a policy

Connect to the database as the user postgres, who is the owner of the demo table.

\c postgres postgres

Result

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

Create a policy

CREATE POLICY demo_policy ON demo FOR ALL TO PUBLIC USING (name=current_user);

Result

postgres=# CREATE POLICY demo_policy ON demo FOR ALL TO PUBLIC USING (name=current_user);
CREATE POLICY

A policy named demo_policy was created for the demo table with the right to run all commands for all roles. The expression name=current_user is a filter and allows you to return the logical value of the name column and thereby compare this value with the user who is currently connected to the database.

Instead of the FOR ALL value, you can specify other values: SELECT, INSERT, UPDATE and DELETE, depending on which command you want to apply the policy to. The value PUBLIC indicates that the policy applies to all roles, while you can specify certain roles to which the policy will be applied.

Connect to the database as user the student01 and check access to the table.

\c postgres student01

SELECT * from demo;

Result

postgres=> SELECT * from demo;
 numb |   role   |   name    | perm
------+----------+-----------+------
    1 | user     | student01 |    1
    2 | non-user | student02 |    0
(2 rows)

The access of the user student01 (also true for the user student02) isn’t prohibited, since a policy has been created allowing this type of access.

Enabling the row-level security

Connect to the database as the user postgres.
```
\c postgres postgres
```

Enable the row-level table security.

ALTER TABLE demo ENABLE ROW LEVEL SECURITY;

Result

postgres=# ALTER TABLE demo ENABLE ROW LEVEL SECURITY;
ALTER TABLE

To force the policy to be enabled, use the command:

ALTER TABLE .. FORCE ROW LEVEL SECURITY;

To drop the policy, use the command:

ALTER TABLE .. DISABLE ROW LEVEL SECURITY;

Connect to the database as the user student01 and check access to the table by sequentially run the following commands.

\c postgres student01

select current_user;

SELECT * from demo;

Result

postgres=# \c postgres student01
You are now connected to database "postgres" as user "student01".
postgres=> select current_user;
 current_user
--------------
 student01
(1 row)

postgres=> SELECT * from demo;
 numb | role |   name    | perm
------+------+-----------+------
    1 | user | student01 |    1
(1 row)

Connect to the database as the user student02 and check access to the table by sequentially running the following commands.

\c postgres student02

select current_user;

SELECT * from demo;

Result

postgres=> \c postgres student02
You are now connected to database "postgres" as user "student02".
postgres=> select current_user;
 current_user
--------------
 student02
(1 row)

postgres=> SELECT * from demo;
 numb |   role   |   name    | perm
------+----------+-----------+------
    2 | non-user | student02 |    0
(1 row)

The result of executing the commands is that the user (current_user) can only access his own row within the current client.

Bypassing the row-level security policy

In ADPG, it’s possible to assign permissions BYPASSRLS and NOBYPASSRLS for the roles. The BYPASSRLS permission is assigned by default for the table owners and for the superuser, and they can skip the security policy. The NOBYPASSRLS permission is assigned by default for all other roles.

NOTE

Default rule assignment is only applicable if row-level security policy is enabled.

You should use the BYPASSRLS and NOBYPASSRLS commands for managing the access permissions to a row when you need to grant a user bypass the policy.

Connect to the database as the user postgres, who is the owner of the demo table.

\c postgres postgres

Result

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

Grant the student02 user the BYPASSRLS privileges.

ALTER USER student02 BYPASSRLS;

Result

postgres=# ALTER USER student02 BYPASSRLS;
ALTER ROLE

Check the right of the user student02 to access the demo table.

SELECT * FROM demo;

Result

postgres=# ALTER USER student02 BYPASSRLS;
ALTER ROLE
postgres=# SELECT * FROM demo;
numb |   role   |   name    | perm
------+----------+-----------+------
1 | user     | student01 |    1
2 | non-user | student02 |    0
(2 rows)

The result of executing the commands is that the user student02, having received the BYPASSRLS privileges, has access to the row, bypassing the policy that previously prohibited such access.

Drop a policy

Connect to the database as the user postgres, who is the owner of the demo table.

\c postgres postgres

Result

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

Drop the policy.

DROP POLICY demo_policy ON demo;

Result

postgres=# DROP POLICY demo_policy ON demo;
DROP POLICY

Connect to the database as the user student01.

\c postgres student01

Result

postgres=# \c postgres student01
You are now connected to database "postgres" as user "student01".

The SELECT command return the name of the user who is authenticated in the current client.

SELECT current_user;

Result

postgres=> SELECT current_user;
current_user
--------------
student01
(1 row)

Check the privileges of the user student01 to access the demo table.

SELECT * FROM demo;

Result

postgres=> SELECT * FROM demo;
numb | role | name | perm
------+------+------+------
(0 rows)

The result shown preceding demonstrates that despite the fact that the policy has been removed, the user student01 doesn’t have access to the data. When deleting a given policy, the use of a row-level security policy isn’t automatically disabled. If this policy was enabled by default for ADPG, then the so-called default-deny is used, which restricts everything that isn’t explicitly allowed.

Disabling the row-level security

Connect to the database as the user postgres, who is the owner of the demo table.

\c postgres postgres

Result

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

Disable the row-level policies.

ALTER TABLE demo DISABLE ROW LEVEL SECURITY;

Result

postgres=# ALTER TABLE demo DISABLE ROW LEVEL SECURITY;
ALTER TABLE

Check that row policy-based security is disabled and users allowed to view the data. Run the following commands sequentially.

\c postgres student01

SELECT * from demo;

Result

postgres=> \c postgres student01
You are now connected to database "postgres" as user "student01".
postgres=> SELECT * from demo;
numb | role | name | perm
------+----------+-----------+------
1 | user | student01 | 1
2 | non-user | student02 | 0
(2 rows)

The user student01 has access to data in the table. The situation is similar for the user student02.

Found a mistake? Seleсt text and press Ctrl+Enter to report it