How to implement Column and Row level security in PostgreSQL

Advanced How-tos Beginner

Rajkumar Raghuwanshi Senior Software Engineer, PL/SQL

This article discusses how to add column-level and row-level security as components of table-level security to restrict users from accessing certain data.

  1. Column-level security
  2. Row-level security
  3. How to combine row-level security with column grants
  4. Application users vs. row-level security
  5. Row-level security performance

 

PostgreSQL is a secure database with extensive security features at various levels.

At the top-most level, database clusters can be made secure from unauthorized users using host-based authentication, different authentication methods (LDAP, PAM), restricting listen address, and many more security methods available in PostgreSQL. 

When an authorized user gets database access, further security can be implemented at the object level by allowing or denying access to a particular object. This can be done using various role-based authentication measures and using GRANT and REVOKE commands.

In this article, we are going to talk about security at a more granular level, where a column or a row of a table can be secured from a user who has access to that table but whom we don’t want to allow to see a particular column or a particular row. So let’s explore these options.

Table-level security can be implemented in PostgreSQL at two levels.

  1. Column-level security
  2. Row-level security

Let’s explore column-level security first.

 

Column-level security

What is column-level security?

As the name suggests, at this level of security we want to allow the user to view only a particular column or set of columns, making all other columns private by blocking access to them, so users can not see or use those columns when selecting or sorting. Now let’s see how we can implement this.

 

How to enable column-level security

This can be achieved by various methods. Let's explore each of them one by one.

Using a table view

The simplest way to achieve column-level security is to create a view that includes only the columns you want to show to the user, and provide the view name to the user instead of the table name. 

Example

I have an employee table with basic employee details and salary-related information. I want to provide information to an admin user, but do not want to show the admin information about employee salary and account numbers. 

Let’s create a user and table with some data:

postgres=# create user admin;

CREATE ROLE

postgres=# create table employee ( empno int, ename text, address text, salary int, account_number text );

CREATE TABLE

postgres=# insert into employee values (1, 'john', '2 down str',  20000, 'HDFC-22001' );

INSERT 0 1

postgres=# insert into employee values (2, 'clark', '132 south avn',  80000, 'HDFC-23029' );

INSERT 0 1

postgres=# insert into employee values (3, 'soojie', 'Down st 17th',  60000, 'ICICI-19022' );

INSERT 0 1

postgres=# select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)

 

An admin user with full access to the employee table can currently access salary information, so the first thing we want to do here is to revoke the admin user’s access to the employee table, then create a view with only required columns—empno, ename and address—and provide this view access to the admin user instead.

postgres=# revoke SELECT on employee from admin ;

REVOKE

postgres=# create view emp_info as select empno, ename, address from employee;

CREATE VIEW

postgres=# grant SELECT on emp_info TO admin;

GRANT

postgres=# \c postgres admin

You are now connected to database "postgres" as user "admin".

postgres=> select * from employee;

ERROR:  permission denied for table employee

postgres=> select * from emp_info;

 empno | ename  |    address    

-------+--------+---------------

     1 | john   | 2 down str

     2 | clark  | 132 south avn

     3 | soojie | Down st 17th

(3 rows)



postgres=> select * from emp_info where salary > 200;

ERROR:  column "salary" does not exist

LINE 1: select * from emp_info where salary > 200;

 

As we can see, admin can find employee information via the emp_info view, but cannot access the salary and account_number columns from the table.

Column-level permissions

Another good option for securing a column is to grant access to particular columns only to the intended user. In the above example, we don’t want the admin user to access the salary and account_number columns of the employee table. Instead of creating views, we can instead provide access to all columns except salary and account_number. 

Example

Let’s take a look at how this works using queries. We have already revoked SELECT privileges on the employee table, so admin cannot access employees.

postgres=# \c postgres admin

You are now connected to database "postgres" as user "admin".

postgres=> select * from employee;

ERROR:  permission denied for table employee

 

Now let’s give SELECT permission on all columns except salary and account_number:

postgres=> \c postgres edb

You are now connected to database "postgres" as user "edb".



postgres=# grant select (empno, ename, address) on employee to admin;

GRANT



postgres=# \c postgres admin

You are now connected to database "postgres" as user "admin".

postgres=> select empno, ename, address, salary from employee;

ERROR:  permission denied for table employee

postgres=> select empno, ename, address from employee;

 empno | ename  |    address    

-------+--------+---------------

     1 | john   | 2 down str

     2 | clark  | 132 south avn

     3 | soojie | Down st 17th

(3 rows)

 

As we see, the admin user has access to the employee table’s columns except for salary and account_number.

An important thing to remember in this case is that the user should not have GRANT access on table. You must revoke SELECT access on the table and provide column access with only columns you want the user to access. Column access to particular columns will not work if users already have SELECT access on the whole table.

Column-level encryption

Another way to secure a column is to encrypt just the column data, so the user can access the column but can not see the actual data. PostgreSQL has a pgcrypto module for this purpose. Let’s explore this option with the help of a basic example.

Example

Here we want user admin to see the account_number column, but not the exact data from that column; at the same time, we want another user, finance, to be able to access the actual account_number information. To accomplish this, we will insert data in the employee table using pgcrypto functions and a secret key.

postgres=> \c postgres edb

You are now connected to database "postgres" as user "edb".

postgres=# create user finance;

CREATE ROLE

postgres=# grant select (empno, ename, address,account_number) on employee to finance;

GRANT



postgres=# CREATE EXTENSION pgcrypto;

CREATE EXTENSION

postgres=# TRUNCATE TABLE employee;

TRUNCATE TABLE

postgres=# insert into employee values (1, 'john', '2 down str',  20000, pgp_sym_encrypt('HDFC-22001','emp_sec_key'));

INSERT 0 1

postgres=# insert into employee values (2, 'clark', '132 south avn',  80000, pgp_sym_encrypt('HDFC-23029', 'emp_sec_key'));

INSERT 0 1

postgres=# insert into employee values (3, 'soojie', 'Down st 17th',  60000, pgp_sym_encrypt('ICICI-19022','emp_sec_key'));

INSERT 0 1

postgres=# select * from employee;

 empno | ename  |    address    | salary |                                                                        account_number                                                                        

-------+--------+---------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------

     1 | john   | 2 down str    |  20000 | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302

     2 | clark  | 132 south avn |  80000 | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf

     3 | soojie | Down st 17th  |  60000 | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535

(3 rows)



postgres=# revoke SELECT on employee from admin;

REVOKE

postgres=# grant select (empno, ename, address,account_number) on employee to admin;

GRANT

 

As we can see, selecting data from the employee table’s account_number column is showing encryption. Now if an admin user wants to see data it can view it, but in the encrypted form.

postgres=# \c postgres admin

You are now connected to database "postgres" as user "admin".

postgres=> select empno, ename, address,account_number from employee;

 empno | ename  |    address    |                                                                        account_number                                                                        

-------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------

     1 | john   | 2 down str    | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302

     2 | clark  | 132 south avn | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf

     3 | soojie | Down st 17th  | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535

(3 rows)

 

If the table owner wants to share actual data with the finance user, the key can be shared, and finance can view actual data:

postgres=> \c postgres finance

You are now connected to database "postgres" as user "finance".

postgres=> select empno, ename, address, account_number from employee;

 empno | ename  |    address    |                                                                        account_number                                                                        

-------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------

     1 | john   | 2 down str    | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302

     2 | clark  | 132 south avn | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf

     3 | soojie | Down st 17th  | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535

(3 rows)



postgres=> select empno, ename, address,pgp_sym_decrypt(account_number::bytea,'emp_sec_key') from employee;

 empno | ename  |    address    | pgp_sym_decrypt 

-------+--------+---------------+-----------------

     1 | john   | 2 down str    | HDFC-22001

     2 | clark  | 132 south avn | HDFC-23029

     3 | soojie | Down st 17th  | ICICI-19022

(3 rows)

 

When a user who does not have a key tries to see data with a random key, they get an error:

postgres=> \c postgres admin

You are now connected to database "postgres" as user "admin".

postgres=> select empno, ename, address,pgp_sym_decrypt(account_number::bytea,'random_key') from employee;

ERROR:  Wrong key or corrupt data

 

The method shown above is highly based on trust. The pgcrypto module has other methods that use private and public keys to do the same work. 

 

Row-level security

What is row-level security?

Row-level security (RLS for short) is an important feature in the PostgreSQL security context. This feature enables database administrators to define a policy on a table such that it can control viewing and manipulation of data on a per user basis. A row-level policy can be understood as an additional filter; when a user tries to perform an operation on a table, this filter is applied before any query condition or filtering, and data is shrunk down or access is denied based on the specific policy.

Row-level security policies can be created specific to a command, such as SELECT or DML commands (INSERT/UPDATE/DELETE), or with ALL. Row-level security policies can also be created on a particular role or multiple roles.

Example

As we saw above, we can protect columns and column data from other users like admin, but we can also protect data at the row level so that only a user whose data that row contains can view it. So let’s drop the employee table and recreate it with new data:

postgres=> \c postgres edb

You are now connected to database "postgres" as user "edb".

postgres=# DROP TABLE employee;

DROP TABLE

postgres=# create table employee ( empno int, ename text, address text, salary int, account_number text );

CREATE TABLE

postgres=# insert into employee values (1, 'john', '2 down str',  20000, 'HDFC-22001' );

INSERT 0 1

postgres=# insert into employee values (2, 'clark', '132 south avn',  80000, 'HDFC-23029' );

INSERT 0 1

postgres=# insert into employee values (3, 'soojie', 'Down st 17th',  60000, 'ICICI-19022' );

INSERT 0 1

postgres=# select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)

 

Employee john can view only rows that have john’s information. Similarly, employees clark and soojie can only view information in their respective row, while the superuser or table owner can view all the information. Now let’s look at how we can achieve this user-level security using row-level security policies. 

First, create users based on entries in rows and provide table access to them:

postgres=# create user john;

CREATE ROLE

postgres=# grant select on employee to john;

GRANT

postgres=# create user clark;

CREATE ROLE

postgres=# grant select on employee to clark;

GRANT

postgres=# create user soojie;

CREATE ROLE

postgres=# grant select on employee to soojie;

GRANT

 

As of now, each user can see all data:

postgres=# \c postgres john

You are now connected to database "postgres" as user "john".

postgres=> select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)



postgres=> \c postgres clark

You are now connected to database "postgres" as user "clark".

postgres=> select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)



postgres=> \c postgres soojie 

You are now connected to database "postgres" as user "soojie".

postgres=> select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)

 

Now, let’s create a policy:

Creating a policy

postgres=> \c postgres edb 

You are now connected to database "postgres" as user "edb".



postgres=# CREATE POLICY emp_rls_policy ON employee FOR ALL TO PUBLIC USING (ename=current_user);

CREATE POLICY

 

Let’s understand the syntax used above: 

  • We first connected to superuser edb, who in this case is also owner of table employee, and then created the policy. 
  • The name of the policy, emp_rls_policy, is a user-defined name. 
  • Then, employee is the name of the table.
  • ALL here represent for all commands, Alternatively, we can specify select/insert/update/delete—whatever operation we want to restrict. 
  • PUBLIC here represents all roles. Alternatively we can provide specific role names to which the policy would apply.
  • Using (ename=current_user): this part is called expression. It is a filter condition that returns a boolean value. As we know each role is in the table in column ename, so we have compared ename to the user currently connected to the database.

Now, let’s try to access data using user john:

postgres=# \c postgres john

You are now connected to database "postgres" as user "john".

postgres=> select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)

 

As we can see, john is still able to view all rows, because creating the policy alone is not sufficient; we must explicitly enable it. Let’s see how to enable or disable a policy

How to enable row-level security

postgres=> \c postgres edb

You are now connected to database "postgres" as user "edb".

postgres=# ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

ALTER TABLE

 

To enable the policy we have connected as the superuser. The syntax to disable or forcefully enable the policy is similar:

ALTER TABLE ... DISABLE ROW LEVEL SECURITY;

ALTER TABLE .. FORCE ROW LEVEL SECURITY;

ALTER TABLE .. NO FORCE ROW LEVEL SECURITY;

 

Now let’s see what each user can view from the employee table:

postgres=# \c postgres edb

You are now connected to database "postgres" as user "edb".

postgres=# select current_user;

 current_user 

--------------

 edb

(1 row)



postgres=# select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)



postgres=# \c postgres john

You are now connected to database "postgres" as user "john".

postgres=> select current_user;

 current_user 

--------------

 john

(1 row)



postgres=> select * from employee;

 empno | ename |  address   | salary | account_number 

-------+-------+------------+--------+----------------

     1 | john  | 2 down str |  20000 | HDFC-22001

(1 row)



postgres=> \c postgres clark

You are now connected to database "postgres" as user "clark".

postgres=> select current_user;

 current_user 

--------------

 clark

(1 row)



postgres=> select * from employee;

 empno | ename |    address    | salary | account_number 

-------+-------+---------------+--------+----------------

     2 | clark | 132 south avn |  80000 | HDFC-23029

(1 row)



postgres=> \c postgres soojie 

You are now connected to database "postgres" as user "soojie".

postgres=> select current_user;

 current_user 

--------------

 soojie

(1 row)



postgres=> select * from employee;

 empno | ename  |   address    | salary | account_number 

-------+--------+--------------+--------+----------------

     3 | soojie | Down st 17th |  60000 | ICICI-19022

(1 row)

 

As we can see, the current_user can only access his or her own row.

If you want one of the users to be able to access all data—for example, let’s assume soojie is in HR and needs to access all other employee data—let’s see how to achieve this.

 

Bypassing row-level security 

PostgreSQL has BYPASSRLS and NOBYPASSRLS permissions, which can be assigned to a role; NOBYPASSRLS is assigned by default. The table owner and superuser have BYPASSRLS permissions, so they can skip row level security policy.

Let’s assign the same permission to soojie.

postgres=> \c postgres edb

You are now connected to database "postgres" as user "edb".

postgres=#  alter user soojie bypassrls;

ALTER ROLE

postgres=# \c postgres soojie 

You are now connected to database "postgres" as user "soojie".

postgres=> select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)

 

Drop a policy

Let’s take a look at how to drop a policy.

postgres=> \c postgres edb 

You are now connected to database "postgres" as user "edb".

postgres=# DROP POLICY emp_rls_policy ON employee;

DROP POLICY

 

The syntax is simple: just provide the policy name and table name to drop the policy from that table. Now, let’s try to access the data:

postgres=# \c postgres john

You are now connected to database "postgres" as user "john".

postgres=> select current_user;

 current_user 

--------------

 john

(1 row)



postgres=> select * from employee;

 empno | ename | address | salary | account_number 

-------+-------+---------+--------+----------------

(0 rows)

 

As we can see, though we have dropped the policy, user john is still not able to view any data. This is because the row-level security policy is still enabled on the employee table.

If row-level security is enabled by default, PostgreSQL uses a default-deny policy. Now let’s disable it and try to access the data:

postgres=> \c postgres edb

You are now connected to database "postgres" as user "edb".

postgres=# ALTER TABLE employee DISABLE ROW LEVEL SECURITY;

ALTER TABLE

postgres=# \c postgres john 

You are now connected to database "postgres" as user "john".

postgres=> select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)

 

Now john can see all the data again.

 

How to combine row-level security with column grants

There may be cases where you need to implement both row-level and column-level security on the same table. 

For example, in the table above, all employees can view only their own information only, but let’s say we don’t want to show financial information to employees. We can apply column-level permissions on the employee level as well.

Right now john can see all of the information, as the policy has been deleted and row-level security is disabled.

postgres=> \c postgres john 

You are now connected to database "postgres" as user "john".

postgres=> select * from employee;

 empno | ename  |    address    | salary | account_number 

-------+--------+---------------+--------+----------------

     1 | john   | 2 down str    |  20000 | HDFC-22001

     2 | clark  | 132 south avn |  80000 | HDFC-23029

     3 | soojie | Down st 17th  |  60000 | ICICI-19022

(3 rows)

 

Let’s create a policy and enable row-level security. Now, john can view only his information:

postgres=> \c postgres edb

You are now connected to database "postgres" as user "edb".

postgres=# CREATE POLICY emp_rls_policy ON employee FOR all TO public USING (ename=current_user);

CREATE POLICY

postgres=# ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

ALTER TABLE

postgres=# \c postgres john 

You are now connected to database "postgres" as user "john".

postgres=> select * from employee;

 empno | ename |  address   | salary | account_number 

-------+-------+------------+--------+----------------

     1 | john  | 2 down str |  20000 | HDFC-22001

(1 row

 

Next, let’s remove access to the employee table from john and give access to all columns except the salary and account_number columns. Now, john can view all his details except for financial information.

postgres=> \c postgres edb

You are now connected to database "postgres" as user "edb".

postgres=# revoke SELECT on employee from john;

REVOKE

postgres=# grant select (empno, ename, address) on employee to john;

GRANT

postgres=# \c postgres john

You are now connected to database "postgres" as user "john".

postgres=> select * from employee;

ERROR:  permission denied for table employee

postgres=> select empno, ename, address from employee;

 empno | ename |  address   

-------+-------+------------

     1 | john  | 2 down str

(1 row)

 

Application users vs. row-level security

While creating policies for users we have used current_user and matched it with the user entry present in the table. But there are cases where there are many users, like web applications, and it’s not feasible to create an explicit role for each application user. Our objective in these cases remains the same: a user should only be able to view their own data and not others. Let’s see how we can implement this with a basic example.

Example

Let’s add some more data in our employee table:

postgres=> \c postgres edb

You are now connected to database "postgres" as user "edb".



postgres=# insert into employee values (4, 'smith', 'ash dwn str',  85000, 'HDFC-22121' );

INSERT 0 1

postgres=# insert into employee values (5, 'mark', 'lake river south',  61000, 'ICICI-11119' );

INSERT 0 1

postgres=# 

postgres=# select * from employee;

 empno | ename  |     address      | salary | account_number 

-------+--------+------------------+--------+----------------

     1 | john   | 2 down str       |  20000 | HDFC-22001

     2 | clark  | 132 south avn    |  80000 | HDFC-23029

     3 | soojie | Down st 17th     |  60000 | ICICI-19022

     4 | smith  | ash dwn str      |  85000 | HDFC-22121

     5 | mark   | lake river south |  61000 | ICICI-11119

(5 rows)

 

We have already created three users—john, clark, and soojie—and we don’t want to have to create users for each new entry. So instead of using current_user, we can change our policy to use a session variable. Session variables can be initialized each time a new user tries to see data.

So first let’s grant select access to PUBLIC, drop the old policy, and create a new policy with session variables.

postgres=# grant  SELECT on employee to PUBLIC;

GRANT

postgres=# DROP POLICY emp_rls_policy ON employee;

DROP POLICY

postgres=# CREATE POLICY emp_rls_policy ON employee FOR all TO public USING (ename=current_setting('rls.ename'));

CREATE POLICY

postgres=# ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

ALTER TABLE

postgres=# 

postgres=# \c postgres john

You are now connected to database "postgres" as user "john".

postgres=> set rls.ename = 'smith';

SET

postgres=> select * from employee;

 empno | ename |   address   | salary | account_number 

-------+-------+-------------+--------+----------------

     4 | smith | ash dwn str |  85000 | HDFC-22121

(1 row)

postgres=> set rls.ename = 'wrong';

SET

postgres=> select * from employee;

 empno | ename | address | salary | account_number 

-------+-------+---------+--------+----------------

(0 rows)

 

As we can see, smith is a role in a database, but by using a session variable smith can only access their own data.

 

Row-level security performance

If you have observed in all examples adding an RLS just means adding a WHERE clause in every query. Each row must satisfy this WHERE clause to pass through row-level security. Naturally, this additional check may cause some performance impact.

Row-level security has an additional CHECK clause, which adds yet another condition, so keep in mind the larger you make your policy, the more performance impact you may face. Just like optimizing any simple SQL query, RLS can be optimized by carefully designing these CHECK expressions.

 

References :

https://www.postgresql.org/docs/current/pgcrypto.html

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

https://www.postgresql.org/docs/current/sql-createpolicy.html

 

Rajkumar RaghuwanshiSenior Software Engineer, PL/SQL