How to implement Column and Row level security in PostgreSQL
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.
- Column-level security
- Row-level security
- How to combine row-level security with column grants
- Application users vs. row-level security
- 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.
- Column-level security
- 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
上一篇： jQuery里面能不能写PHP方法？