Setting up row-level security

Row-level security is one of the advanced features in PostgreSQL to limit access to a subset of records of a table using policies. For example, a manager can be allowed to read or modify only the records of the employees to which they are a manager. In this recipe, we shall discuss the steps involved in enabling row-level security.

Getting ready

The policies for enabling row-level security must be created by a superuser who has access to create policies.

How to do it...

In the following steps, we are creating a table and inserting some sample records to demonstrate how row-level security can be enabled:

1. Create an employee table and insert three records that have two different managers:

CREATE TABLE employee (id INT, first_name varchar(20), last_name varchar(20), manager varchar(20));

INSERT INTO employee VALUES (1,'avi','kumar','john');
INSERT INTO employee VALUES (2,'naga','vallarapu','steven'...