Access Control
Row level security
13
Copyright
© Postgres Professional, 2015–2022
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Alexander Meleshko
Use of course materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
What is row level security
When RLS policies apply
Multiple policies, same table
3
Row level security policy
Determines the visibility and mutability of table rows
the predicate is calculated for each row using the initiating user’s privileges
the client can only access the rows for which the predicate is true
Predicate for existing rows (USING)
used by SELECT, UPDATE, DELETE commands
policy violation does not trigger an error
(unless the row_security parameter is unset)
Predicate for new rows (WITH CHECK)
used by INSERT, UPDATE commands
if omitted, the first predicate is used
policy violation triggers an error
Row level security (RLS) policies allow the system administrator to control
user access to a table at the level of individual rows. This mechanism is also
known as Fine-Grained Access Control.
It it a supplemental tool, as the role must have necessary privileges to
access the table in the first place.
Row level security policies determine if a certain user should be able to
read or modify a certain row by calculating one of two predicates (binary
expressions) for each of the queried rows. The predicate result determines
if the user would be allowed to access the row.
The first predicate is used for existing rows. It is used by the commands
SELECT, UPDATE, and DELETE. If the predicate for a given row does not
return true (that is, the function returns false or NULL), the row isn't included
into the client's result set. As a gross oversimplification, you can imagine
that the predicate is simply appended to the WHERE clause of the query.
In reality, however, it is more complicated.
If the row_security parameter value is set to off, a false predicate for even a
single row will result in an query error. This is useful when making a logical
backup to ensure that all rows of all tables got in.
The second predicate determines the visibility of new rows. It is used by the
INSERT and UPDATE commands and always throws an error if the policy is
violated.
4
When RLS policies apply
Policy applies
to the table for which RLS is enabled
for specified roles and operators
(SELECT, INSERT, UPDATE, DELETE)
Policy does not apply
during integrity constraints verification
for superusers and roles with the BYPASSRLS attribute
for the owner (unless enabled explicitly)
In order for row level security policies to start working, this mechanism must
be explicitly enabled for each table.
When creating a RLS policy, you can specify what roles (by default, all) and
what operators (by default, also all) will the policy apply to.
Policies are not applied during integrity constraints verification: PostgreSQL
must guarantee data integrity regardless of security configurations.
Policies are not applied for superusers (as with any other security checks)
and for roles with the BYPASSRLS attribute.
For the owner of the table, the policies do not apply by default, but can be
enabled if necessary.
6
Multiple policies
Permissive policies
visibility must be allowed by at least one permissive policy
if no policy allows visibility, the row is not visible
Restrictive policies
visibility must be allowed by all restrictive policies, unless none exist
Multiple policies can be defined on a single table. In this case, all predicates
will be considered.
By default, created policies are permissive. For a row to be visible, at least
one of the predicates of these policies must be true.
But if row level security is enabled and no permissive policy is defined, not a
single row will be available.
Additionally, you can define restrictive policies. If such policies are defined,
all of them must return true for the row to be visible.
In other words, if only permissive policies are defined and the predicates are
P
1
, …, P
N
, then for each row the following expression is evaluated:
P
1
OR … OR P
N
.
And if restrictive policies R
1
, ..., R
M
are also defined, then the evaluated
expression will be
(P
1
OR ... OR P
N
) AND R
1
AND ... AND R
M
.
The bottom line is, visibility must be allowed by at least one permissive
policy and by all restrictive policies.
8
Takeaways
Privileges control access to tables and columns,
row level security policies control access to rows
Policies are easier to set up and work more efficiently than
view and trigger based implementations
9
Practice
1. Continuing the example from the demo, create a role for Charlie
and assign him two departments in the user table.
2. Define row level security policies in such a way that:
- roles can only see the rows from their departments,
- roles associated with a single department could add rows with the amount
of up to $100,
- roles associated with multiple departments could add rows with any
amount.
3. Verify that the policies are set up correctly.
4. Estimate the overhead costs of row level security policies by
running the same query as a regular user and as a superuser.
