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