0

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     | john@e.ee | 123456    | IT             |
| 2  | James    | jam@e.ee  | NULL      | Ethics         |
| 3  | Jeremy   | jer@e.ee  | 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.

1
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Sep 25 '21 at 1:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.