Re: How to limit access only to certain records?

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