T
T
tyvaldr2012-10-22 14:53:03
PostgreSQL
tyvaldr, 2012-10-22 14:53:03

Differentiation of user rights to table records in PostgreSQL?

Hello!
Such a question: is it possible in PostgreSQL to differentiate access rights to a table, and if so, how (a little misunderstood in the manuals).
For example, we have a cities table with columns "region" (region), "city" (city). We have a separate user for each region, the task is to display records from the cities table for each user only for his region, prohibiting access to data from neighboring regions at the level of access rights.
Please tell me the solution to this problem.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
P
pasha_golub, 2012-10-22
@pasha_golub

Right now it's not possible. Developments are underway. It can be done through the rules system.

CREATE VIEW myview AS SELECT ... FROM mytable ... WHERE region = 'Moscow District';

CREATE RULE myview_rule_upd AS ON UPDATE TO myview
  DO INSTEAD
    UPDATE mytable SET (column1, column2, ...) = (NEW.column1, NEW.column2, ... );
 

And two more rules for adding and removing.
But as for me, it's easier to create an access table for the user and disable editing in the trigger. Faster and easier to implement. But this is just a modification. And if it is forbidden for users to look, then create a function that checks access and returns the necessary lines.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question