Row Level Security in PostgreSQL

6 min readJun 30, 2020

PostgreSQL provides features to restrict access to rows based on certain conditions. This means that when we send a query to the Postgres, Postgres will allow the operation to happen provided a preset condition is fulfilled. An example of a condition is — Select query should return rows only where the user_id ( a field in an imaginary postgres table) is equal to the name of the postgres user sending the query. Another example is a user should be able to insert a row only if the user_id in the incoming row is equal to the name of the postgres user. This functionality is very useful in circumstances where we have a multitenant database and we want to protect the data of one tenant from another. We don’t want the data to leak under any circumstances. (even when our developer forgets to put in a where clause). It provides additionaly protection over and above what you do in your application.

Conditions for different SQL operations like SELECT, INSERT, UPDATE or DELETE can be provided.

Lets consider a table in our schema called users that stores information about users(id, name) and another table called books that contains information about the book (bookName) and who owns that book. For simplicity, I am not following the best practices in database design here.

CREATE TABLE public.users
(
    name character varying(30) COLLATE pg_catalog."default" NOT NULL,
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    CONSTRAINT users_pkey PRIMARY KEY (id)
)
CREATE TABLE public.books
(
    "bookName" character varying COLLATE pg_catalog."default" NOT NULL,
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "userId" integer NOT NULL,
    CONSTRAINT books_pkey PRIMARY KEY (id),
    CONSTRAINT "user_id_FK" FOREIGN KEY ("userId")
        REFERENCES public.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

The users table contains two columns — the id and the name.

The books table contains two columns — the id, bookName and the userId. The UserId has a foreign key constraint to the users table indicating the user to which the book belongs.

Now lets insert some data.

INSERT INTO public.users(name) VALUES ('sagar');
INSERT INTO public.users(name) VALUES ('suraj');INSERT INTO public.books("bookName","userId") VALUES ('book1', '1');
INSERT INTO public.books("bookName","userId") VALUES ('book2', '1');
INSERT INTO public.books("bookName","userId") VALUES ('book3', '1');
INSERT INTO public.books("bookName","userId") VALUES ('book4', '2');
INSERT INTO public.books("bookName","userId") VALUES ('book5', '2');
INSERT INTO public.books("bookName","userId") VALUES ('book6', '2');

Doing a simple select on both the tables returns the following

select * from users;
 name  | id
-------+----
 sagar |  1
 suraj |  2
(2 rows)select * from books;
 bookName | id | userId
----------+----+--------
 book1    |  1 |      1
 book2    |  2 |      1
 book3    |  3 |      1
 book4    |  4 |      2
 book5    |  5 |      2
 book6    |  6 |      2
(6 rows)

“book1”, “book2” and “book3” belong to user 1 while “book4”, “book5” and “book6” belong to user 2.

Now we have an application that performs CRUD operations on the rows inside the books table.

When a user tries to get his own books, the application performs a select operation on the database using the following query.

select * from books where userId = <userId>;

This will return the books for the user.

This is however prone to error in case the where clause is forgotten in a future release of the application.

We can provide protection at the database level using row level security policies.

First we enable row security using the following command

alter table books enable row level security

Once we enable this, the default deny policy comes into picture where 0 rows are returned for the user in case of SELECT statement and in case of INSERT statment, an error similar to the following is returned.

INSERT INTO public.books("bookName","userId") VALUES ('book10', '1');
ERROR:  new row violates row-level security policy for table "books"

NOTE -: The row security policy does not apply for the table owner, however it can be forced using ALTER TABLE … FORCE ROW LEVEL SECURITY.

Ensure that the database user you use for testing from here onwards should not be owner of the table and should be granted access to both the tables. Create a second user if required and give it access to both the tables.

Run the following command using the owner of the table

CREATE POLICY user_isolation_policy ON books FOR SELECT USING ("userId" = current_setting('app.userId')::integer);

This creates a policy where the user is allowed to select rows only where the userId column value is equal to the app.userId session variable.

Now using the second database user, run the following command.

select set_config('app.userId', '1', false);

This command sets a session variable app.userId to value 1.

The false indicates that it is for the entire session while true indicates that it is only for the current transaction.

While integrating this in your application code, it needs to be set to true and be wrapped in a transaction. When the connection is passed across application threads it ensures that the app.UserId variable from one application thread does not go into another application thread.

I have set it to false since I am running it from the psql shell. The variable lasts as long as the psql session lasts.

Once done, run the following command

select * from books;

and you should see the following output

bookName | id | userId
----------+----+--------
 book1    |  1 |      1
 book2    |  2 |      1
 book3    |  3 |      1
(3 rows)

as you can see there is no where clause applied and all the books for the user with userId 1 are returned.

A condition for select or delete is provided in the USING clause while a condition for an insert is provided in the WITH CHECK clause.

If the value inside the USING clause is set to true, it is as good as no row level security even if the row level security is enabled at the table level. Its like a hack through RLS.

Now if you try to do an INSERT, it will still fail since we have not specified any condition yet for the WITH CHECK clause.

So we create a policy

CREATE POLICY user_isolation_policy_insert ON books FOR INSERT WITH CHECK( "userId" = current_setting('app.userId')::integer);

Now if we retry the insert policy with “app.UserId” set to 1, it does work.

If you try to set the userId different for the session variable and in the row being inserted, it fails.

//SUCCESS
select set_config('app.userId', '1', false);
INSERT INTO public.books("bookName","userId") VALUES ('book10', '1');//FAIL
select set_config('app.userId', '2', false);
INSERT INTO public.books("bookName","userId") VALUES ('book10', '1');

Moving on, If you try an update it will not work.

update books set "bookName" = 'book32' where "userId" = 1;
UPDATE 0

Lets create a policy for update

CREATE POLICY user_isolation_policy_update ON books FOR UPDATE USING("userId" = current_setting('app.userId')::integer) WITH CHECK( "userId" = current_setting('app.userId')::integer);

Since an update is a combination of select and insert, we need to specify both a USING clause for selection and WITH CHECK clause for insertion.

If we retry the update it works.

Similarly a delete won’t work. Creating a policy for DELETE

CREATE POLICY user_isolation_policy_delete ON books FOR DELETE USING("userId" = current_setting('app.userId')::integer);

A DELETE operation is similar to a selection in terms where we need to filter the rows that we need to delete.

Now if we try to delete again, it does work.

Combining all the above into a single policy

CREATE POLICY user_isolation_policy ON books FOR ALL USING("userId" = current_setting('app.userId')::integer) WITH CHECK( "userId" = current_setting('app.userId')::integer);

We have made use of ALL in case of SELECT, DELETE, UPDATE or INSERTION.

Postgres automatically decides whether to use the USING and WITH CHECK clause for a particular operation.

When using it in your application ensure that you set the variable only for the particular transaction, else you may face issues if multiple threads use the same database connection.

CHEERS.

Row Level Security in PostgreSQL

Written by Sagar Gulabani