Granting user access to specific rows

Recent PostgreSQL versions support granting users privileges on some rows only.

Getting ready

This recipe uses row-level security (RLS), which is available only on 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);

Row-level security 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 us assume that the contents of the table are as shown by the following command:

SELECT * FROM someschema.sometable3;
 col1 |   col2   
------+-----------
    1...