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/9.1/static/sql-createview.html>
http://www.postgresql.org/docs/
<http://www.postgresql.org/docs/9.1/static/sql-createfunction.html>current
<http://www.postgresql.org/docs/9.1/static/sql-createview.html>/static/sql-createfunction.html
<http://www.postgresql.org/docs/9.1/static/sql-createfunction.html>
http://www.postgresql.org/docs/current/static/sql-grant.html
<http://www.postgresql.org/docs/9.1/static/sql-grant.html>
http://www.postgresql.org/docs/current/static/sql-revoke.html
<http://www.postgresql.org/docs/9.1/static/sql-revoke.html>
Hope this helps.
--
Craig Ringer
