Hello guys, Since last two months I have published about the SQL Server Interview Questions and Answers.
During this time, I got lots of messages and requests about the PostgreSQL.
In this August – 2016, I am going to post about only PostgreSQL and will try to complete all pending requests of readers.
In this post, I am going to publish about The Row Level Security which is now a common feature of all newly version of RDBMSs like SQL Server 2016, PostgreSQL 9.5.
With the concept of Row Level Security, we can restrict the user for a particular set of records.
Previously, we are doing this kind of restriction using by creating filtered VIEWS and assign related VIEWS to related Users.
PostgreSQL 9.5 has this Row Level Security feature which extends the overall security policy of the PostgreSQL.
Let me demonstrate this.
Execute below all steps using Postgres Super user:
First create two sample users:
CREATE USER abc LOGIN PASSWORD '123';
CREATE USER xyz LOGIN PASSWORD '321';
Create a table with sample data:
CREATE TABLE tbl_Employees
,EmpName CHARACTER VARYING
INSERT INTO tbl_Employees VALUES
Add one new column for row level security:
ALTER TABLE tbl_Employees ADD pgUser CHARACTER VARYING;
Update table with specific user:
UPDATE tbl_Employees SET pgUser = 'abc' WHERE EmpID IN (1,2,3);
UPDATE tbl_Employees SET pgUser = 'xyz' WHERE EmpID IN (4,5,6);
Give some DML grants to these users:
GRANT SELECT,UPDATE,INSERT,DELETE ON tbl_Employees TO abc;
GRANT SELECT,UPDATE,INSERT,DELETE ON tbl_Employees TO xyz;
Create a security policy on tbl_Employee for current logged-in user only:
CREATE POLICY policy_employee_user ON tbl_Employees FOR ALL
TO PUBLIC USING (pgUser = current_user);
Enable ROW LEVEL SECURITY on tbl_Employees:
ALTER TABLE tbl_Employees ENABLE ROW LEVEL SECURITY;
Execute this SELECT with the different logins:
SELECT *FROM tbl_Employees;
Result of User abc:
empid empname pguser
1 Anvesh abc
2 Martin abc
3 Roy abc
Result of User xyz:
empid empname pguser
4 Jeeny xyz
5 Kavita xyz
6 Neevan xyz
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.