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;

...