In my Postgres database I have a table of employees:
CREATE TABLE employee ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY name text, email text, phone text, department text );
I would need to give a user access to a set of rows in the table so that for each row they would see a different set of fields. For instance, if the employee works in the same department as you, you will see all of their info, otherwise you would not see their phone number. The hidden data could be set to
NULL. The user should also be able to update the data they can see.
So for somebody working in the IT department:
SELECT * FROM employee; | id | name | email | phone | department | |----|----------|-----------|-----------|----------------| | 1 | John | firstname.lastname@example.org | 123456 | IT | | 2 | James | email@example.com | NULL | Ethics | | 3 | Jeremy | firstname.lastname@example.org | NULL | Morality |
When talking about users (to whom the security restrictions must apply to) I am thinking about rows in the
users table, who might have and might not have an associated database user.
How to implement that?
Solutions I am thinking about
Solution 1: reducing the problem to row level security by splitting the data to different views
CREATE VIEW public_employee_data AS SELECT id, name, email, department FROM employee; -- Perhaps also some WHERE clause ... CREATE VIEW private_employee_data AS SELECT id, phone FROM employee WHERE department=(SELECT department FROM employee WHERE id=current_setting('employee_id')); -- That is the end interface CREATE VIEW employee_view AS SELECT pub.id, pub.name, priv.phone, pub.department FROM public_employee_data LEFT JOIN private_employee_data USING id;
In this case there is need for only row level security: you either see or not see a row in a small view and the small views are then
LEFT JOINed together.
That solution should do the job, but it is hard to implement support for updating the data in that way.
Solution 2: A view/function returning a matrix of access rights
SELECT * FROM employee_permissions; | id | name | email | phone | department | |----|----------|-----------|-----------|----------------| | 1 | READ | READ | READ | READ_WRITE | | 2 | READ | READ | NULL | READ | | 3 | READ | READ | NULL | READ |
That data could then we used by some view, procedure, or application code to determine what the user can do. But these views/procedures can also be a little complex to write and maintain.