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)
testdb=# create user "[email protected]" password 'secret';
CREATE ROLE
testdb=# create user "[email protected]" password 'secret';
CREATE ROLE
testdb=# create user "[email protected]" password 'secret';
CREATE ROLE
testdb=# create user "[email protected]" password 'secret';
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)
testdb=# grant select on bonus to "[email protected]";
GRANT
testdb=# grant select on bonus to "[email protected]";
GRANT
testdb=# grant select on bonus to "[email protected]";
GRANT
testdb=# grant select on bonus to "[email protected]";
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
-------------------
 [email protected]
(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
----------------------
 [email protected]
(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
----------------------
 [email protected]
(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
-------------------
 [email protected]
(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
-------------------
 [email protected]
(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

Post Views: 993
Thumbnail [60x60]
by
Oracle Team