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
, orDELETE
) 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.