Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aEKXv-0003gG-6L for pgsql-sql@arkaria.postgresql.org; Wed, 30 Dec 2015 17:28:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aEKXu-0002Sy-Hb for pgsql-sql@arkaria.postgresql.org; Wed, 30 Dec 2015 17:28:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aEKXs-0002Oa-AO for pgsql-sql@postgresql.org; Wed, 30 Dec 2015 17:28:20 +0000 Received: from mail-wm0-x22a.google.com ([2a00:1450:400c:c09::22a]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aEKXo-0000gf-K4 for pgsql-sql@postgresql.org; Wed, 30 Dec 2015 17:28:19 +0000 Received: by mail-wm0-x22a.google.com with SMTP id f206so43403048wmf.0 for ; Wed, 30 Dec 2015 09:28:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=subject:to:references:cc:from:message-id:date:user-agent :mime-version:in-reply-to:content-type; bh=YWvRSTIfLLQsviJPYkPZ6Tdu351301C45SwbCCjVqCo=; b=Vkjm0ufJmrPOv/Dfwa1jGahgNwCOL/XbG3ZhbuQAS3Rd4+V/Rfs7/7b90KyYUneqll ZL+jewtqXu7cTZpOJ41ce6UJ+dJr2B7dhWJY0oIkWPrAvy7VdXw/ydauesi8y85tglyX OdteESyFF7VWj+U0A8F4t+33ejYQidMiY880JZT1bkQI2UJ1A8gaB9285Um5mmWdigdE fHhIV2zJSeTUfF0rfSCdUE0E4Enxc5b3wXlfEax7k2UJ6YQWf9vO9afXCWbCpBPsMM+3 zRa5PBQ+cleHWASGViwA/79pT7mQRm+i1udfzxG/n1NBTo49aYVb7yzBepJb/IYm9pyG ueQw== X-Received: by 10.194.158.135 with SMTP id wu7mr72445157wjb.142.1451496495538; Wed, 30 Dec 2015 09:28:15 -0800 (PST) Received: from timbomac.home (host86-147-73-225.range86-147.btcentralplus.com. [86.147.73.225]) by smtp.googlemail.com with ESMTPSA id r10sm41832223wjz.24.2015.12.30.09.28.14 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 30 Dec 2015 09:28:14 -0800 (PST) Subject: Re: question on row level security To: "David G. Johnston" References: <56840D1A.8030203@gmail.com> Cc: "pgsql-sql@postgresql.org" From: Tim Dudgeon Message-ID: <5684142D.9070701@gmail.com> Date: Wed, 30 Dec 2015 17:28:13 +0000 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:38.0) Gecko/20100101 Thunderbird/38.5.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------020407030404090600000905" X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------020407030404090600000905 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 30/12/2015 17:19, David G. Johnston wrote: > On Wed, Dec 30, 2015 at 9:58 AM, Tim Dudgeon >wrote: > > The new row level security feature in 9.5 looks great. > I guess its designed around the need to restrict access based on > the current database user (current_user) where this maps to a > database user. > But most applications now access the database using an application > user and manages data for the applications multiple users > (probably with each user being a row in a USERS table somewhere). > Is there any way to "inject" the application user so that this can > be used in a RLS check? > e.g. conceptually: > > set app_user 'john'; > select * from foo; > > where the select * is restricted by a RLS check that includes > 'john' as the app_user. > Of course custom SQL could be generated for this, but it would be > safer if it could be handled using RLS. > > Any ways to do this > ​ ? > > > ​ Does this address your concerns? > > ​ """ > The session_user is normally the user who initiated the current > database connection; but superusers can change this setting with SET > SESSION AUTHORIZATION. The current_user is the user identifier that is > applicable for permission checking. Normally it is equal to the > session user, but it can be changed with SET ROLE. It also changes > during the execution of functions with the attribute SECURITY DEFINER. > In Unix parlance, the session user is the "real user" and the current > user is the "effective user". > """ > > http://www.postgresql.org/docs/9.5/static/functions-info.html > > RLS uses "current_user" when performing checks. > > David J. > It might, but does it mean that that user (the app_user in my original question) still has to be a regular database user (e.g. one who has a database account and can connect to the database)? This is what I want to avoid. Tim --------------020407030404090600000905 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 30/12/2015 17:19, David G. Johnston wrote:
On Wed, Dec 30, 2015 at 9:58 AM, Tim Dudgeon <tdudgeon.ml@gmail.com> wrote:
The new row level security feature in 9.5 looks great.
I guess its designed around the need to restrict access based on the current database user (current_user) where this maps to a database user.
But most applications now access the database using an application user and manages data for the applications multiple users (probably with each user being a row in a USERS table somewhere).
Is there any way to "inject" the application user so that this can be used in a RLS check?
e.g. conceptually:

set app_user 'john';
select * from foo;

where the select * is restricted by a RLS check that includes 'john' as the app_user.
Of course custom SQL could be generated for this, but it would be safer if it could be handled using RLS.

Any ways to do this
​ ?

​ Does this address your concerns?

​ """
The session_user is normally the user who initiated the current database connection; but superusers can change this setting with SET SESSION AUTHORIZATION. The current_user is the user identifier that is applicable for permission checking. Normally it is equal to the session user, but it can be changed with SET ROLE. It also changes during the execution of functions with the attribute SECURITY DEFINER. In Unix parlance, the session user is the "real user" and the current user is the "effective user".
"""


RLS uses "current_user" when performing checks.

David J.


It might, but does it mean that that user (the app_user in my original question) still has to be a regular database user (e.g. one who has a database account and can connect to the database)? This is what I want to avoid.

Tim

--------------020407030404090600000905--