Granting user access to specific rows

PostgreSQL supports granting privileges on a subset of rows in a table using RLS.

Getting ready

Just as we did for the previous recipe, we assume that there is already a schema called someschema and a role called somerole with USAGE privileges on it. We create a new table to experiment with row-level privileges:

CREATE TABLE someschema.sometable3(col1 int, col2 text);

RLS must also be enabled on that table:

ALTER TABLE someschema.sometable3 ENABLE ROW LEVEL SECURITY;

How to do it…

First, we grant somerole the privilege to view the contents of the table, as we did in the previous recipe:

GRANT SELECT ON someschema.sometable3 TO somerole;

Let’s assume that the contents of the table are as shown by the following command:

SELECT * FROM someschema.sometable3;
col1 |   col2   
------+-----------
    1 | One
   -1 | Minus one
(2 rows)

In order to grant the ability to access some rows only...