PostgreSQL provides a SQL-standards-compliant privileges-based access control system using GRANT/REVOKE for restricting access to table-level and column-level data and operations. However, PostgreSQL – and some other major RDBMSs – provide a feature to control data access at an individual row level also. This feature is popularly known as Row Level Security (RLS). Read on to learn more about RLS and its implementation in Postgres.
Roles, Users and Groups
Unlike other RDBMSes, PostgreSQL (9.x+) has a single concept of a role (rather than users and groups), and cleanly separates the concepts of identity, authentication and authorization. In the Postgres world, a user is a role that can login, and the closest match of a group (in a Unix-style sense of users and groups) is a role that cannot login.
Roles are created like so:
CREATE ROLE sales; -- this role will serve as a "group"
CREATE ROLE accounting; -- so will this
CREATE USER alice; -- alice can login
CREATE ROLE bob LOGIN; -- same as CREATE USER bob;
GRANT sales TO alice; -- "add" alice to sales "group"
GRANT accounting TO bob;-- "add" bob to accounting "group"
Having a user (alice) belong to a group (sales), allows the database administrator to specify permissions for a group (see below) rather than for individual users. By default, a user that “belongs” to a group inherits all privileges granted to the group.
The roles created above define the identities in the Postgres’ access control specification infrastructure. Any client that connects to a Postgres server must authenticate itself as one of these identities. The most popular authentication mechanism is password authentication. Here is how we can set a password for alice:
ALTER ROLE alice PASSWORD 's3cr3t'; -- change alice's password
Passwords are only one of the 11 available methods of authentication in PostgreSQL! Another authentication method, for example, is using an external LDAP server to validate the password supplied to Postgres by the client.
Whichever authentication method you decide to use, each user who wants to login must be configured to use exactly one method. This is specified via pg_hba.conf.
Let’s check the current set of users and groups using \du command of psql:
postgres=# \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+--------------
accounting | Cannot login | {}
alice | | {sales}
bob | | {accounting}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
sales | Cannot login | {}
The role postgres is created during the database creation and initialization process. This database was created by a process running as Unix system user postgres, which is why this database user is also named postgres.
There are more aspects of Postgres roles that we didn’t cover: changing identity using SET ROLE, users that do not inherit group privileges, predefined roles and more. See chapters 21 and 22 of the PostgreSQL documentation for in-depth information.
Privileges
Privileges are what can be GRANT-ed to roles on a certain object. Objects can be specific tables, columns of a table, sequences and so on. The list of grants associated with an object is called an access control list (ACL).
There are 14 privileges in PostgreSQL. SELECT, INSERT, UPDATE and DELETE are four of these, which we’ll use in the discussion below.
See this section of the Postgres docs for more information about privileges.
Table and Column Level Access Control
Restricting access at table and column level involves editing the ACLs associated with a table or a column. This is done using GRANT and REVOKE. Let’s have a look.
First, let’s have the administrator create a table:
postgres=# create database corp;
CREATE DATABASE
postgres=# \c corp
You are now connected to database "corp" as user "postgres".
corp=# CREATE TABLE invoices (
id int not null primary key,
finalized bool not null default false,
sales_notes text,
details jsonb
);
CREATE TABLE
The table invoices now has the default permissions, which only permit the owner (in this case the user postgres) to do anything on the table. For example, alice can’t even see the data in this table:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> select * from invoices;
ERROR: permission denied for table invoices
The psql command \dp can be used to list the permissions on the table:
corp=# \dp invoices
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------+-------+-------------------+-------------------+----------
public | invoices | table | | |
(1 row)
Let’s allow alice to work with all data in the table:
corp=# GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO alice;
GRANT
corp=# \dp invoices
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------+-------+---------------------------+-------------------+----------
public | invoices | table | postgres=arwdDxt/postgres+| |
| | | alice=arwd/postgres | |
(1 row)
The ACL of the table now has two lines, which is intrepreted as:
postgres=arwdDxt/postgres– the userpostgrescan do the operations a, r, w, d, D, x and t; this was granted by the userpostgresitselfalice=arwd/postgres– the useralicecan do the operations a (for append, that is insert), r (for read, that is select), w (for write, that is update) and d (for delete); this was granted by the userpostgres- (the
+at the end of the first line indicates the end of line / newline and is not part of the ACL itself)
Let’s also have everyone from accounting be able to see all invoices, except for their sales_notes column:
corp=# GRANT SELECT (id, finalized, details) ON invoices TO accounting;
GRANT
corp=# \dp invoices
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------+-------+---------------------------+-------------------------+----------
public | invoices | table | postgres=arwdDxt/postgres+| id: +|
| | | alice=arwd/postgres | accounting=r/postgres+|
| | | | finalized: +|
| | | | accounting=r/postgres+|
| | | | details: +|
| | | | accounting=r/postgres |
(1 row)
The ACL now reads that accounting has read (r) access to the columns id, finalized and details, and no access to the table itself. Had we granted access to the table, accounting would implicitly be able to access all columns of the table.
Let’s see these controls in action:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> insert into invoices (id, finalized, sales_notes, details) values (202345, true, 'repeat customer', '{"details":"..."}');
INSERT 0 1
corp=> insert into invoices (id, finalized, sales_notes, details) values (202346, true, 'volume discount', '{"details":"..."}');
INSERT 0 1
corp=> insert into invoices (id, finalized, sales_notes, details) values (202347, false, 'call to be scheduled', '{"details":"..."}');
INSERT 0 1
Alice has added three invoices; can Bob see them?
corp=> select current_user;
current_user
--------------
bob
(1 row)
corp=> select * from invoices;
ERROR: permission denied for table invoices
corp=> select id, finalized, details from invoices;
id | finalized | details
--------+-----------+--------------------
202345 | t | {"details": "..."}
202346 | t | {"details": "..."}
202347 | f | {"details": "..."}
(3 rows)
corp=> update invoices set finalized=true where id=202347;
ERROR: permission denied for table invoices
Sure enough, Bob can see only the 3 columns that he has access to, and cannot update rows either.
Now let’s bring in RLS to restrict the rows that accounting has access to.
Row Level Access Control
Setting up row level access control using RLS is slightly different from the ACL-based access control we have seen so far. Firstly, RLS is not enabled by default, and has to be enabled on a per-table basis. Let’s do that for invoices:
corp=# ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE
corp=# \d invoices
Table "public.invoices"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
id | integer | | not null |
finalized | boolean | | not null | false
sales_notes | text | | |
details | jsonb | | |
Indexes:
"invoices_pkey" PRIMARY KEY, btree (id)
Policies (row security enabled): (none)
We see that psql now tells us that row level security is enabled for this table, and that there are no “policies”.
While ACLs are used to specify table and column level access controls, they are NOT used for row level access control. For that, policies are used.
Before we jump into policies, let’s check if alice can still access the data in the table:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> select * from invoices;
id | finalized | sales_notes | details
----+-----------+-------------+---------
(0 rows)
None of the rows are visible to alice now. This is the effect of enabling RLS on a table without setting any policies on it. None of the rows are made available for read or write for anyone (except table owner and bypassrls users, more on this later) in such a situation.
Policies
Policies are created using the CREATE POLICY SQL command. A policy is always defined on a particular table, for a particular role, and has a filter expression. Let’s create a couple of policies:
CREATE POLICY only_finalized ON invoices
TO accounting
USING (finalized = TRUE);
CREATE POLICY all_rows ON invoices
TO sales
USING (TRUE);
The first policy (named only_finalized, policy names are unique within a table), created on table invoices and applies to the accounting role, has the filter expression finalized = TRUE. The filter expression selects the set of rows which will be made available, like a table-level CHECK clause or a WHERE clause. The policy, in English, states “allow accounting to SELECT, INSERT, UPDATE and DELETE all rows where finalized = TRUE”.
The second policy allows sales to perform all four operations on all rows of invoices.
This is how the table policies are shown by psql:
corp=# \d invoices
Table "public.invoices"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
id | integer | | not null |
finalized | boolean | | not null | false
sales_notes | text | | |
details | jsonb | | |
Indexes:
"invoices_pkey" PRIMARY KEY, btree (id)
Policies:
POLICY "all_rows"
TO sales
USING (true)
POLICY "only_finalized"
TO accounting
USING ((finalized = true))
Let’s see what happens when bob of accounting tries to fetch all rows of invoices:
corp=> select current_user;
current_user
--------------
bob
(1 row)
corp=> select id, finalized, details from invoices;
id | finalized | details
--------+-----------+--------------------
202345 | t | {"details": "..."}
202346 | t | {"details": "..."}
(2 rows)
Bob’s query returned only rows with finalized = TRUE. Note that rows that do not match a policy’s filter do not create errors – any rows that do not match are simply discarded. This is how SELECT, UPDATE and DELETE work when RLS is enabled.
Let’s see the same operation by alice from sales:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> select * from invoices;
id | finalized | sales_notes | details
--------+-----------+----------------------+--------------------
202345 | t | repeat customer | {"details": "..."}
202346 | t | volume discount | {"details": "..."}
202347 | f | call to be scheduled | {"details": "..."}
(3 rows)
Indeed, alice can see all the rows since the filter expression always evaluates to TRUE.
Restrictive Policies
Now let’s create a policy that does not let anyone delete finalized invoices. Here is how it looks:
CREATE POLICY cant_delete_finalized ON invoices
AS RESTRICTIVE
FOR DELETE
TO PUBLIC
USING (finalized = FALSE);
The policy now includes a AS RESTRICTIVE clause. Policies can be either RESTRICTIVE or PERMISSIVE. The policies we created earlier where PERMISSIVE, since that is the default.
In general, for a given user and an operation, there will be a set of restrictive policies and a set of permissive policies. To determine which rows are available for this operation for this user, Postgres will first select all the rows that match any permissive policy. Effectively, this is like OR-ing together all the filters of all the relevant permissive policies. Next, each row must match every restrictive policy, or it will be discarded. That is to say, each row must match the expression created by AND-ing together the restrictive filters.
Applying the above logic for a DELETE operation on invoices, the rows selected will only include the rows where finalized is false.
Also in this policy, the FOR DELETE clause specifies that the policy applies only for DELETE and not the other operations. The PUBLIC role is a pre-defined role that every role implicitly belongs to. PUBLIC has no default or pre-defined privileges.
Let’s see what rows can be DELETEd by alice with this policy:
corp=> begin;
BEGIN
corp=*> select current_user;
current_user
--------------
alice
(1 row)
corp=*> delete from invoices returning id, finalized;
id | finalized
--------+-----------
202347 | f
(1 row)
DELETE 1
corp=*> rollback;
ROLLBACK
Even though alice tries to delete all rows in the table, Postgres acts like there was only one row in the table. Only the row with finalized = FALSE was selected by Postgres as relevant for this operation.
The same restriction can be placed on UPDATEs also, but another policy is needed since only one explicit operation may be specified per policy:
CREATE POLICY cant_update_finalized ON invoices
AS RESTRICTIVE
FOR UPDATE
USING (finalized = FALSE);
Finally, note that if there are no permissive policies defined on a table, the first part of the logic (get all rows that any permissive policy matches) selects no rows. It is therefore not useful to have only restrictive policies for a table. If you need to define a restrictive policy, you will also need at least one permissive policy.
WITH CHECK Clauses
RLS is defined for INSERT operations also. It works a bit differently since the check is for data that is yet to be stored as a row, and the usual logic of filtering out rows does not apply.
First let’s add a new column sales_rep to invoices (as the table owner). This column contains the name of a user.
corp=# select current_user;
current_user
--------------
postgres
(1 row)
corp=# ALTER TABLE invoices ADD sales_rep text;
ALTER TABLE
corp=# UPDATE invoices SET sales_rep='alice';
UPDATE 3
corp=# SELECT * FROM invoices;
id | finalized | sales_notes | details | sales_rep
--------+-----------+----------------------+--------------------+-----------
202345 | t | repeat customer | {"details": "..."} | alice
202346 | t | volume discount | {"details": "..."} | alice
202347 | f | call to be scheduled | {"details": "..."} | alice
(3 rows)
Let’s also create a new user charlie, also belonging to sales. We earlier granted permissions on invoices only to alice, and not sales – an oversight that we’ll fix now:
corp=# CREATE USER charlie;
CREATE ROLE
corp=# GRANT sales TO charlie;
GRANT ROLE
corp=# GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO sales;
GRANT
Now we can define a policy for insert:
CREATE POLICY sales_rep_is_self ON invoices
AS RESTRICTIVE
WITH CHECK (sales_rep = CURRENT_USER);
The policy is restrictive, and has a new WITH CHECK clause. This clause specifies an expression that the data-to-be-inserted must satisfy. In this case, it states that the value of sales_rep must be the name of the user inserting the row. PostgreSQL will also apply this same check to the values of a row touched by UPDATE – the new values of an updated row must also pass this check.
Let’s see this in action:
corp=> select current_user;
current_user
--------------
charlie
(1 row)
corp=> update invoices set finalized=true where id=202347;
ERROR: new row violates row-level security policy "sales_rep_is_self" for table "invoices"
corp=> insert into invoices (id, finalized, sales_notes, sales_rep, details) values (202348, false, 'new lead', 'alice', '{"details":"..."}');
ERROR: new row violates row-level security policy "sales_rep_is_self" for table "invoices"
Unlike the other operations, the failures of INSERT and UPDATEs when the (new) values of a row fail a WITH CHECK expression will result in errors. When the new values pass all WITH CHECK policies, INSERTs and UPDATEs go through normally:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> select id, sales_notes, sales_rep from invoices;
id | sales_notes | sales_rep
--------+----------------------+-----------
202345 | repeat customer | alice
202346 | volume discount | alice
202347 | call to be scheduled | alice
(3 rows)
corp=> update invoices set sales_notes='waiting on customer' where id=202347;
UPDATE 1
corp=> select id, sales_notes, sales_rep from invoices;
id | sales_notes | sales_rep
--------+---------------------+-----------
202345 | repeat customer | alice
202346 | volume discount | alice
202347 | waiting on customer | alice
(3 rows)
In some cases, it is possible to achieve the same result using column-level CHECK clauses or table-level CHECK constraints. However, the expressions in policies are more powerful than column or table-level CHECK clauses. To illustrate, let us modify our policy such that the current user must also be explicitly listed in a table if the insert has to succeed:
ALTER POLICY sales_rep_is_self ON invoices
WITH CHECK (sales_rep = CURRENT_USER AND CURRENT_USER IN (
SELECT name FROM allowed_users
));
The policy now additionally checks that CURRENT_USER must be present in a table called allowed_users. Here is the policy in action:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> select * from allowed_users;
name
---------
charlie
(1 row)
corp=> insert into invoices (id, sales_notes, sales_rep) values (202348, 'new invoice', 'alice');
ERROR: new row violates row-level security policy "sales_rep_is_self" for table "invoices"
Alice is not allowed to insert a row into invoices since she’s not listed in allowed_users. If she is, then the insert succeeds:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> select * from allowed_users;
name
---------
charlie
alice
(2 rows)
corp=> insert into invoices (id, sales_notes, sales_rep) values (202348, 'new invoice', 'alice');
INSERT 0 1
Finally, note that various other forms of INSERT and UPDATE (like CONFLICT clauses, INSERTs during MERGE etc) are also covered by RLS.
Table Owner’s Privileges
The owner of a table is, by default, not bound by the RLS policies defined on the table. To have the owner also abide by the RLS policies, you can ALTER TABLE .. FORCE ROW LEVEL SECURITY.
Let’s create a table with the owner as alice:
corp=# create table test1 (a int);
CREATE TABLE
corp=# alter table test1 owner to alice;
ALTER TABLE
Now let’s have alice enable RLS for this table without any policies and try to insert a row. As there are no policies, the insert should not succeed..
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> alter table test1 enable row level security;
ALTER TABLE
corp=> insert into test1 (a) values (10);
INSERT 0 1
..but it does, since alice is the owner. If we FORCE ROW LEVEL SECURITY on the table, then the RLS policies will apply to the owner also:
corp=> alter table test1 force row level security;
ALTER TABLE
corp=> insert into test1 (a) values (20);
ERROR: new row violates row-level security policy for table "test1"
This might seem more secure, but note the owner can always change the policies on the table:
corp=> create policy allow_alice on test1 to alice using (true);
CREATE POLICY
corp=> insert into test1 (a) values (20);
INSERT 0 1
Users With BYPASSRLS Attribute
PostgreSQL roles have an attribute that can be set, called BYPASSRLS. Roles with this attribute set will not be bound by any RLS policy on any table.
Let’s make charlie a BYPASSRLS user:
corp=# ALTER ROLE charlie BYPASSRLS;
ALTER ROLE
Using this superpower, he can now update the invoices table even though the RLS policies on the table forbid him from doing so:
corp=> select current_user;
current_user
--------------
charlie
(1 row)
corp=> begin;
BEGIN
corp=*> update invoices set finalized=true where id=202347;
UPDATE 1
corp=*> rollback;
ROLLBACK
Typically this attribute should be set only for database administration users, as all queries run by such users totally bypass any and all RLS policies.
Table Inheritance
PostgreSQL has a feature called table inheritance – does RLS work on such tables? Turns out that it does.
Inheritance allows a child table to “inherit” the columns from it’s parent. More interestingly, querying the parent will return rows from the child also. If you’re using RLS, make sure that RLS is enabled for both parent and child. Specifically, if RLS is enabled only for the child and not the parent, the rows will still be accessible via the parent.
The following snippet shows the case where RLS is enabled only for the child table and not the parent. In this situation, querying the parent can return the child’s rows also, bypassing the RLS on the child.
create table parent (parentcol int);
create table child (childcol int) inherits (parent);
insert into parent values (10), (20), (30);
insert into child (parentcol, childcol) values (40, 400), (50, 500), (60, 600);
create role alice;
grant select on parent to alice;
grant select on child to alice;
-- rls enabled only for child
alter table child enable row level security;
set role alice;
select * from parent; -- returns all 6 rows
select * from child; -- returns 0 rows
Partitioning
Partitioning refers to the sharding of large tables into smaller tables. See the docs to learn all about it. Does RLS work with partitioned tables? Let’s find out.
First, let’s recreate the example table from the docs, and insert 4 rows that go into 2 different partitions:
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
INSERT INTO MEASUREMENT (city_id, logdate, peaktemp, unitsales) VALUES
(100, '2006-02-09', 30, 500),
(101, '2006-02-09', 25, 600),
(100, '2006-03-09', 40, 400),
(101, '2006-03-09', 45, 450);
Here is how the logical table and the 2 physical tables look like after this:
corp=# select * from measurement;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
100 | 2006-02-09 | 30 | 500
101 | 2006-02-09 | 25 | 600
100 | 2006-03-09 | 40 | 400
101 | 2006-03-09 | 45 | 450
(4 rows)
corp=# select * from measurement_y2006m02;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
100 | 2006-02-09 | 30 | 500
101 | 2006-02-09 | 25 | 600
(2 rows)
corp=# select * from measurement_y2006m03;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
100 | 2006-03-09 | 40 | 400
101 | 2006-03-09 | 45 | 450
(2 rows)
Now let’s enable RLS and create two policies on the partition root table alone. The policies let alice query all the data, and only update measurements of city with ID 100.
GRANT SELECT, UPDATE ON measurement TO alice;
ALTER TABLE measurement ENABLE ROW LEVEL SECURITY;
CREATE POLICY select_all ON measurement
FOR SELECT TO alice USING (true);
CREATE POLICY update_city_100 ON measurement
FOR UPDATE TO alice USING (city_id = 100);
Are these policies sufficient? Let’s try:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> update measurement set unitsales=1000;
UPDATE 2
corp=> select * from measurement;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
101 | 2006-02-09 | 25 | 600
100 | 2006-02-09 | 30 | 1000
101 | 2006-03-09 | 45 | 450
100 | 2006-03-09 | 40 | 1000
(4 rows)
corp=> update measurement set unitsales=200 where city_id=101;
UPDATE 0
It is indeed. Alice was able to update only 2 rows (those with city_id = 100). Even though these two rows lived in two different partitions, they were both updated. Other operations also similarly respect the RLS when routed through the partition root table.
A key point here is that as long as all operations are performed via the partition root table, the RLS policies are applied in a propogative manner down the inheritance hierarchy. To effectively enforce RLS for a partitoned table, do not grant permissions to directly manipulate data in child tables, and route all operations via the partition root.
Physical Replication
Do RLS policies get replicated to standbys when using physical replication? Yes.
The roles, their attributes and memberships, the RLS status of a table and the RLS policies associated with a table are all stored in the system tables in the pg_catalog schema. When a database cluster is replicated physically, these get synced over to the standby, and all these will be present exactly how it is in the primary.
However, one thing is not: pg_hba.conf. This lives as a standalone file that contains the authentication configuration of access control. For historical and technical reasons, this is not stored inside the database itself. The administrator has to manually ensure that the pg_hba.conf of the primary is in sync with the standby.
The user that the standby connects as needs the REPLICATION attribute. During physical replication, this user is not bound by RLS policies.
Logical Replication
In logical replication, subscriptions on one PostgreSQL server connect to publications (usually) on another PostgreSQL server. Each subscription connects as a valid user on the publication-server. Such users need to have LOGIN and REPLICATION attributes. RLS policies are applied to the subscription connection also. For example, if the user that the subscription is using is only allowed to see a subset of rows in a table, only those rows get replicated via the subscription.
All rows that the subscription user has access to on the publication-side will appear on the subscription-side. Further access control, including users, grants, RLS status and policies can be configured totally independently of the publication-side.
Views
Creating views over RLS-enabled tables has a gotcha, which has a proper solution only in PostgreSQL v15. Let’s have a look.
First, let’s create a table t with a few numbers..
corp=# create table t (a int);
CREATE TABLE
corp=# insert into t values (10),(15),(18),(21),(24),(27),(30);
INSERT 0 7
corp=# select * from t;
a
----
10
15
18
21
24
27
30
(7 rows)
..and set up alice to see only rows where a is even:
corp=# grant select on t to alice;
GRANT
corp=# alter table t enable row level security;
ALTER TABLE
corp=# create policy only_even on t to alice using (a % 2 = 0);
CREATE POLICY
Let’s check if it works as intended:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> select * from t;
a
----
10
18
24
30
(4 rows)
So far so good. Now let’s create a view v that simply selects all rows from t. Let’s also enable alice to query this view:
corp=# create view v as select * from t;
CREATE VIEW
corp=# grant select on v to alice;
GRANT
Intutively, we expect querying from v to return the same rows as querying from t. However:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> select * from v;
a
----
10
15
18
21
24
27
30
(7 rows)
It returned all the rows! Although the user is alice, and the query underlying v returns only 4 rows for alice, the view itself bypasses the RLS and returns all the rows. Oops.
This is because the identity of the owner of v is used in the RLS rules, and not the identity of the user invoking the view, as one would intutively expect. Here, the view is owned is by postgres, so the underlying query is executed in the owner’s (postgres’) context rather than the invoker’s (alice’s).
Luckily, we can tell PostgreSQL to use the invoker’s identity rather than the owner’s identity, on a per-view basis:
corp=# alter view v set ( security_invoker = true );
ALTER VIEW
And now let’s see if it works:
corp=> select current_user;
current_user
--------------
alice
(1 row)
corp=> select * from v;
a
----
10
18
24
30
(4 rows)
It works as expected now. However, the ALTER VIEW .. SET security_invoker is available only in version 15. The usual workaround for prior versions is to use a table-returning function instead.
Materialized Views
PostgreSQL currently (v15) does not support RLS for materialized views.
Performance and Overhead
Enabling RLS and setting policies has a measurable overhead. Exactly how much though, is impossible to state generically. If you are doing a non-trivial RLS implementation, plan for measuring and quantifying the impact of the overhead in your production deployment.
RLS is implemented in core PostgreSQL and its efficiency has continued to improve over the versions since it was first added. As compared to an access control layer built into the application, the in-database implementation can be more performant because PostgreSQL’s query execution engine can typically handle the filtering of data more efficiently than application logic can. This also, however, depends a lot on the specific implementation and not much more can be stated generically.
Application Users vs Database Users
There is one aspect of RLS that makes it all but useless for a certain class of applications.
Consider a typical web application where users can signup, login, perform work and logout. Typically, when a user signs up into this application, a backend process adds a row into a users table in the database. The “user” here is an “application user” and not a “database user”. Nearly no modern web application framework tries to do a CREATE USER when a user signs up into the web application.
The backend processes that connect to the PostgreSQL database in a typical web application deployment do so as a single, specific user. Typically there are many such processes, and there is a connection pooler like PgBouncer that enables sharing of PostgreSQL connections between these processes.
RLS would not be a bad idea in such a multi-tenanted database. The ability to protect a user’s data from other users is an important requirement in all SaaS and similar web applications.
To enforce RLS in such a setup, not only must the backend process be able to CREATE USER for each application user signup, it must also establish a new PostgreSQL connection as the database user when the application user logs in. Connections cannot be pooled and shared between backend processes connecting as different users.
There is no easy solution for web applications to use RLS. Most approaches try to push application session management into the database; however this is at best cumbersome and error-prone.
Alternatives to RLS
RLS is not the only way to restrict access to data within the database. Depending on your use case, other in-database options might work too:
CHECKclauses and constraints: column-level and table-levelCHECKclauses might suffice to restrict INSERT operations based solely on the tuple values being inserted or updatedEXCLUDEtable constraint (see docs) can be used to place restrictions based on relations between data in rows of the same table- Triggers can control the behavior of operations on any table, and complex logic can be specified in any supported programming language
- Views with security barrier attribute can be used to provide subsets of rows and columns to approved users
About pgDash
pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics.
pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, diagnostics, alerting, teams and more. Checkout the features here or signup today for a free trial.