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

  1. Get access to the ADPG server console by logging via a trusted account.

  2. Switch to the postgres user.

    $ sudo su - postgres
  3. Go to the ADPG interactive terminal.

    $ psql

Creating a table

  1. Create the 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
  2. 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
  3. 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
  4. 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.

  1. Create the user student01.

    CREATE USER student01;
    Result:
    postgres=# CREATE USER student01;
    CREATE ROLE
  2. Create the user student02.

    CREATE USER student02;
    Result:
    postgres=# CREATE USER student02;
    CREATE ROLE
  3. 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
  4. 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.

  5. 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".
  6. 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)
  7. 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".
  8. Check the rights 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

  1. 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".
  2. 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 rights 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.

  1. 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

  1. Connect to the database as the user postgres.

    \c postgres postgres
  2. 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.

  1. 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".
  2. Grant the student02 user the BYPASSRLS privileges.

    ALTER USER student02 BYPASSRLS;
    Result:
    postgres=# ALTER USER student02 BYPASSRLS;
    ALTER ROLE
  3. Check the rights 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

  1. 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".
  2. Drop the policy.

    DROP POLICY demo_policy ON demo;
    Result:
    postgres=# DROP POLICY demo_policy ON demo;
    DROP POLICY
  3. 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".
  4. 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)
  5. 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

  1. 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".
  2. Disable the row-level policies.

    ALTER TABLE demo DISABLE ROW LEVEL SECURITY;
    Result:
    postgres=# ALTER TABLE demo DISABLE ROW LEVEL SECURITY;
    ALTER TABLE
  3. 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