Row level security in Postgres

In PostgreSQL, controlling access to data can be achieved in different ways. Traditional methods such as GRANT and REVOKE allow you to manage permissions at the table and column level. However, sometimes, access restrictions need to be applied at a finer granularity, like restricting access to specific rows within a table. For example, in a banking system, different users need different levels of access to data. While some users, like the auditing team, might only need high-level summaries of transactions, others, such as the branch manager, may need detailed access to transactions, but only for their specific branch. This is where Row Level Security (RLS) comes into picture.

Access control using GRANT

1. Setting Up the Database and Table

First, we create a database and a table. In this example, we're using a table called posts that holds data about blog posts.

Create database rls;
 
\c rls
 
Create table posts (
    id serial primary key,
    title text,
    content text,
    published boolean default false,
    created_by text
);
 
INSERT INTO posts (title, content, created_by, published) VALUES ('Post 1', 'Content 1', 'test1', true);
INSERT INTO posts (title, content, created_by, published) VALUES ('Post 2', 'Content 2', 'test1', true);
INSERT INTO posts (title, content, created_by, published) VALUES ('Post 3', 'Content 3', 'sujayprabhu', true);
 
 
Select * from posts;
 
 id | title  |  content  | published | created_by  
----+--------+-----------+-----------+-------------
  1 | Post 1 | Content 1 | t         | test1
  2 | Post 2 | Content 2 | t         | test1
  3 | Post 3 | Content 3 | t         | sujayprabhu

2. Restricting Access with GRANT

Next, we create a new user (test1) and restrict their access to the posts table. By default, this user will not have permission to access the table.

CREATE USER test1 WITH PASSWORD 'test1';

When the test1 user tries to select data from the posts table, they will get a permission error:

psql -U test1 -d rls
 
rls=> Select * from posts;
ERROR:  permission denied for table posts

To grant the necessary permissions, we use the GRANT command:

GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO test1;
GRANT USAGE, SELECT ON SEQUENCE posts_id_seq TO test1;
GRANT ALL PRIVILEGES ON posts TO test1;

Now, the test1 user can access the posts table:

psql -U test1 -d rls
 
rls=> Select * from posts;
 id | title  |  content  | published | created_by  
----+--------+-----------+-----------+-------------
  1 | Post 1 | Content 1 | t         | test1
  2 | Post 2 | Content 2 | t         | test1
  3 | Post 3 | Content 3 | t         | sujayprabhu
(3 rows)

Enabling Row Level Security (RLS)

By default, RLS is not enabled on a table. To enable it, you can use the following query:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Note: RLS can be disabled using the below query:

ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

Once RLS is enabled, the test1 user will not be able to see any records in the posts table because no policies are defined yet:

rls=> Select * from posts;
 id | title | content | published | created_by 
----+-------+---------+-----------+------------
(0 rows)

1. Creating Row-Level Security Policies

To control which rows a user can access, you need to define policies. A policy specifies a condition that must be satisfied for a user to access a row.

In this case, let's create a policy that allows users to only access rows where the created_by column matches the current user:

CREATE POLICY posts_policy
    ON posts
    USING (created_by = current_user);

Lets understand the syntax of this policy. We are creating a policy with name posts_policy on posts table and the policy will return the record only if it is created by the user logged in or the current user.

Note: Policy names should be unique in a table

Lets see the table structure of posts once the policy is created

rls=# \d posts
                              Table "public.posts"
   Column   |  Type   | Collation | Nullable |              Default              
------------+---------+-----------+----------+-----------------------------------
 id         | integer |           | not null | nextval('posts_id_seq'::regclass)
 title      | text    |           |          | 
 content    | text    |           |          | 
 published  | boolean |           |          | false
 created_by | text    |           |          | 
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)
Policies:
    POLICY "posts_policy"
      USING ((created_by = CURRENT_USER))

Now, when test1 user runs the SELECT query again, they will only see the rows they have created:

rls=> Select * from posts;
 id | title  |  content  | published | created_by 
----+--------+-----------+-----------+------------
  1 | Post 1 | Content 1 | t         | test1
  2 | Post 2 | Content 2 | t         | test1
(2 rows)

As you can see, the user test1 can now view only the records they created. However, by default, superusers, admins, and table owners have unrestricted access and can see all records. Later, we'll explore how to enforce RLS for table owners and administrators as well.

2. Types of Policies: Permissive vs. Restrictive

In PostgreSQL, policies can be permissive or restrictive:

• Restrictive Policies: By default, policies are restrictive. This means if a user violates the condition specified in a policy, the operation (such as SELECT, INSERT, UPDATE, or DELETE) is denied.

For example, let's create a restrictive policy that allows users to delete posts only if they are not published:

Create POLICY posts_policy_restrictive
    ON posts
    AS RESTRICTIVE
    FOR DELETE
    USING (published = FALSE);

This policy restricts DELETE operations to only those posts where the published column is set to FALSE. Other way to read this is, unless the published is set to FALSE, the DELETE operation is restricted.

• Permissive Policies: You can also create permissive policies that allow operations if at least one policy condition is satisfied.

CREATE POLICY posts_policy_permissive
    ON posts
    AS PERMISSIVE
    FOR INSERT
    WITH CHECK (published = FALSE);

This permissive policy ensures that only unpublished posts can be inserted into the table.

3. Restricting Insertions

To restrict the creation of records, you can use the WITH CHECK clause. This allows you to specify conditions under which new rows can be inserted.

For example, the following policy prevents the creation of new posts unless they are unpublished:

CREATE POLICY posts_policy_restrictive_published_posts
    ON posts
    AS RESTRICTIVE
    WITH CHECK (published = FALSE);

If a user tries to insert a post with published = TRUE, they will get an error:

rls=> INSERT INTO posts (title, content, created_by, published) VALUES ('Post 4', 'Content 4', 'test1', true);
ERROR:  new row violates row-level security policy "posts_policy_restrictive_published_posts" for table "posts"

4. Applying RLS to table owners

By default, RLS does not apply to table owners or superusers. To enforce RLS even for the owner of the table, you can use the FORCE ROW LEVEL SECURITY command:

ALTER TABLE posts force ROW LEVEL SECURITY;

If you see the table structure using \d posts, you will see this

Policies (forced row security enabled)

Now, even the table owner (e.g., test2 user) will be subject to the RLS policies:

psql -U test2 -d rls
 
rls=> Select * from posts;
 id | title | content | published | created_by 
----+-------+---------+-----------+------------
(0 rows)

5. Bypassing RLS for specific users

If you need to allow a specific user to bypass RLS, you can assign them the BYPASSRLS role. This is useful for administrators or other special roles who need unrestricted access to the data:

ALTER role test2 BYPASSRLS;

After this, the test2 user can bypass RLS policies:

psql -U test2 -d rls
 
rls=> Select * from posts;
 id | title  |  content  | published | created_by  
----+--------+-----------+-----------+-------------
  1 | Post 1 | Content 1 | t         | test1
  2 | Post 2 | Content 2 | t         | test1
  3 | Post 3 | Content 3 | t         | sujayprabhu
(3 rows)

With RLS, you can ensure that users only see the data they are authorized to access, improving data security and maintaining privacy. By combining traditional access control with RLS, you can build a secure system for managing your data.

References

• Row Level Security