Granting user access to specific rows

PostgreSQL supports granting users privileges on some rows only.

 

Getting ready

This recipe uses RLS, which is available only in PostgreSQL version 9.5 or later, so start by checking that you are not using an older version.

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