Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id D63EBB8BE76
	for <pgsql-sql@postgresql.org>; Sun, 24 Jun 2012 03:58:54 -0300 (ADT)
Received: from mail-pb0-f46.google.com ([209.85.160.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1SigmZ-0004gv-BO
	for pgsql-sql@postgresql.org; Sun, 24 Jun 2012 06:58:54 +0000
Received: by pbbrp8 with SMTP id rp8so5241508pbb.19
	for <pgsql-sql@postgresql.org>; Sat, 23 Jun 2012 23:58:35 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:x-gm-message-state;
	bh=FCaxg/4GKSUg1OOiTwBxC/F92EgHKYIO8zrGvm0AsNg=;
	b=egiB5Q2gbVDVPaDt0NlTI3KJgBnuCETCAvrSvXSWvor02tN1Pm+Oyh2mNnYN5psMH3
	rdOzVuqozGudQVDGDTRQz/jZ7Gb2qn3rkKsN9mQ0jXsSGX9rn7mTgT7uw0KvwClmZ0Vc
	U2m8c7mhjv/luwYZHpwQYSV/jL1iImbZhKMsVGJ1+PTK4/VmI2tln06UdRrOgwMeV1T7
	H4gkFixCxighx24hXW7y7r9S79qc9G8rtUcD9SJn8yMB+IXQNqJVPNFd5pGJCHiUe4KW
	TunZTun19h88Id1mLBcNUMNQ2J7uINKozG1t/zSq0rrVotYuJxU/Wb37UqfT3vhzrOtC
	Rw+Q==
Received: by 10.68.232.161 with SMTP id tp1mr27788702pbc.44.1340521115351;
	Sat, 23 Jun 2012 23:58:35 -0700 (PDT)
Received: from ayaki.localdomain (124-169-169-118.dyn.iinet.net.au.
	[124.169.169.118])
	by mx.google.com with ESMTPS id qa5sm4556602pbb.19.2012.06.23.23.58.31
	(version=SSLv3 cipher=OTHER); Sat, 23 Jun 2012 23:58:33 -0700 (PDT)
Message-ID: <4FE6BA94.9020908@ringerc.id.au>
Date: Sun, 24 Jun 2012 14:58:28 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Andreas <maps.on@gmx.net>
CC: pgsql-sql@postgresql.org
Subject: Re: How to limit access only to certain records?
References: <4FE458AB.4000109@gmx.net>
In-Reply-To: <4FE458AB.4000109@gmx.net>
Content-Type: multipart/alternative;
	boundary="------------080806050907090606070308"
X-Gm-Message-State: 
 ALoCoQlAN80kXiVi0ZJiNlHzyTsp/9dWu2syuSnPb8IY1/Kuk7DL0Y+Ytd8pWqn3NbjMGMnZ52Zp
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201206/76
X-Sequence-Number: 36730

This is a multi-part message in MIME format.
--------------080806050907090606070308
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------080806050907090606070308
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 06/22/2012 07:36 PM, Andreas wrote:<br>
    </div>
    <blockquote cite="mid:4FE458AB.4000109@gmx.net" type="cite">Hi,
      <br>
      <br>
      is there a way to limit access for some users only to certain
      records?
      <br>
      <br>
      e.g. there is a customer table and there are account-managers.
      <br>
      Could I limit account-manager #1 so that he only can access
      customers only acording to a flag?<br>
    </blockquote>
    <br>
    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.<br>
    <br>
    As far as I know PostgreSQL does not currently offer native
    facilities for row-level access control (except possibly via
    SEPostgreSQL
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <a href="http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction">http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction</a>).
    There's discussion of adding such a feature here <a
      href="http://wiki.postgresql.org/wiki/RLS">http://wiki.postgresql.org/wiki/RLS</a>
    .<br>
    <br>
    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.<br>
    <br>
    See:<br>
     
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <a
      href="http://www.postgresql.org/docs/9.1/static/sql-createview.html">http://www.postgresql.org/docs/current/static/sql-createview.html</a><br>
     
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <a
      href="http://www.postgresql.org/docs/9.1/static/sql-createfunction.html">http://www.postgresql.org/docs/</a><a
href="http://www.postgresql.org/docs/9.1/static/sql-createview.html">current</a><a
href="http://www.postgresql.org/docs/9.1/static/sql-createfunction.html">/static/sql-createfunction.html</a><br>
     
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <a href="http://www.postgresql.org/docs/9.1/static/sql-grant.html">http://www.postgresql.org/docs/current/static/sql-grant.html</a><br>
     
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <a href="http://www.postgresql.org/docs/9.1/static/sql-revoke.html">http://www.postgresql.org/docs/current/static/sql-revoke.html</a><br>
      <br>
    Hope this helps.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------080806050907090606070308--