Re: How to limit access only to certain records?

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