On 06/22/2012 07:36 PM, Andreas wrote:
Hi,
is there a way to limit access for some users only to certain records?
e.g. there is a customer table and there are account-managers.
Could I limit account-manager #1 so that he only can access customers only acording to a flag?
What you describe is called row-level access control, row level security, or label access control, depending on who you're talking to. It's often discussed as part of multi-tenant database support.
As far as I know PostgreSQL does not currently offer native facilities for row-level access control (except possibly via SEPostgreSQL
http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction). There's discussion of adding such a feature here
http://wiki.postgresql.org/wiki/RLS .
As others have noted the traditional way to do this in DBs without row level access control is to use a stored procedure (in Pg a SECURITY DEFINER function), or a set of access-limited vies, to access the data. You then REVOKE access on the main table for the user so they can *only* get the data via the procedure/views.
See:
http://www.postgresql.org/docs/current/static/sql-createview.html http://www.postgresql.org/docs/current/static/sql-createfunction.html http://www.postgresql.org/docs/current/static/sql-grant.html http://www.postgresql.org/docs/current/static/sql-revoke.html Hope this helps.
--
Craig Ringer
