pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Craig Ringer <ringerc@ringerc.id.au>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to limit access only to certain records?
Date: Sun, 24 Jun 2012 14:58:28 +0800
Message-ID: <4FE6BA94.9020908@ringerc.id.au> (raw)
In-Reply-To: <4FE458AB.4000109@gmx.net>
References: <4FE458AB.4000109@gmx.net>

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

view thread (7+ messages)  latest in thread

Message-ID: <4FE6BA94.9020908@ringerc.id.au>
Permalink:  ../4FE6BA94.9020908@ringerc.id.au/
Also on:    postgresql.org/message-id/4FE6BA94.9020908@ringerc.id.au

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: ringerc@ringerc.id.au, maps.on@gmx.net
  Subject: Re: How to limit access only to certain records?
  In-Reply-To: <4FE6BA94.9020908@ringerc.id.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox