By Mouhamadou Diaw
In this article we will talk about a nice feature Row Level Security on PostgreSQL. We are using EDB Postgres Advanced Server 9.5.
Suppose that I am a team manager and that employee bonus are stored in a table Bonus. I want that each employee can see only data related to him and not data for other. How Can I implement this? I can simply use Row Level Security.
Let’s go on. Below is the structure of my table Bonus
1
2
3
4
5
6
7
8
9
|
testdb=# d Bonus Table "public.bonus" Column | Type | Modifiers --------+-----------------------+----------- id | numeric | not null login | character varying (20) | bonus | numeric | Indexes: "bonus_pkey" PRIMARY KEY , btree (id) |
Below data inside Bonus
1
2
3
4
5
6
7
8
|
testdb=# table bonus; id | login | bonus ----+----------------------+------- 1 | [email protected] | 2500 2 | [email protected] | 1500 3 | [email protected] | 7500 4 | [email protected] | 3520 (4 rows ) |
Let’s create users with corresponding logins
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
testdb=# select current_user ; current_user -------------- enterprisedb (1 row) CREATE ROLE CREATE ROLE CREATE ROLE CREATE ROLE |
And let’s grant them select on Table Bonus
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
testdb=# select current_user ; current_user -------------- enterprisedb (1 row) GRANT GRANT GRANT GRANT |
We can verify that by default each user can see all data (what I don’t want). For example with user [email protected]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
testdb=> select current_user ; current_user ------------------- (1 row) testdb=> select * from bonus; id | login | bonus ----+----------------------+------- 1 | [email protected] | 2500 2 | [email protected] | 1500 3 | [email protected] | 7500 4 | [email protected] | 3520 (4 rows ) |
And with user [email protected]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
testdb=> select current_user ; current_user ---------------------- (1 row) testdb=> select * from bonus; id | login | bonus ----+----------------------+------- 1 | [email protected] | 2500 2 | [email protected] | 1500 3 | [email protected] | 7500 4 | [email protected] | 3520 (4 rows ) |
To allow user to see only his data. I have first to create a policy on the table Bonus with an expression which will filter data.
1
2
3
4
5
6
7
8
9
|
testdb=# select current_user ; current_user -------------- enterprisedb (1 row) testdb=# create policy bonus_policy on bonus for all to public using (login= current_user ); CREATE POLICY testdb=# |
After creating the policy, let’s enable the RLS on table Bonus
1
2
3
4
5
6
7
8
9
|
testdb=# select current_user ; current_user -------------- enterprisedb (1 row) testdb=# alter table bonus enable row level security; ALTER TABLE testdb=# |
And now bingo we can verify that each user can only see his corresponding data
1
2
3
4
5
6
7
8
9
10
11
|
testdb=> select current_user ; current_user ---------------------- (1 row) testdb=> select * from bonus; id | login | bonus ----+----------------------+------- 4 | [email protected] | 3520 (1 row) |
1
2
3
4
5
6
7
8
9
10
11
12
13
|
testdb=> select current_user ; current_user ------------------- (1 row) testdb=> select * from bonus; id | login | bonus ----+-------------------+------- 1 | [email protected] | 2500 (1 row) testdb=> |
Now let’s drop the policy but let’s still keep table bonus with the RLS enabled. What happens?
1
2
3
4
5
6
7
8
9
|
testdb=# select current_user ; current_user -------------- enterprisedb (1 row) testdb=# drop policy bonus_policy on bonus; DROP POLICY testdb=# |
Let’s Query table bonus with user [email protected] for example
1
2
3
4
5
6
7
8
9
10
11
12
|
testdb=> select current_user ; current_user ------------------- (1 row) testdb=> select * from bonus; id | login | bonus ----+-------+------- (0 rows ) testdb=> |
But if we query the table with user enterprisedb which is the table owner (should also be a superuser)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
testdb=# select current_user ; current_user -------------- enterprisedb (1 row) testdb=# select * from bonus; id | login | bonus ----+----------------------+------- 1 | [email protected] | 2500 2 | [email protected] | 1500 3 | [email protected] | 7500 4 | [email protected] | 3520 (4 rows ) |
So we see that if RLS is enabled on a table and that there is no defined policy, a default-deny policy will be applied. Only owners, super users and users with the BYPASSRLS attribute will be able to see data in the table