Message-ID: <527A0A9D.3090308@2ndquadrant.com>
Date: Wed, 06 Nov 2013 17:23:41 +0800
From: Craig Ringer <craig@2ndquadrant.com>
Organization: 2nd Quadrant
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.0
MIME-Version: 1.0
To: Dean Rasheed <dean.a.rasheed@gmail.com>
CC: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 Stephen Frost <sfrost@snowman.net>, Josh Berkus <josh@agliodbs.com>,
 Kohei KaiGai <kaigai@kaigai.gr.jp>, "ktm@rice.edu" <ktm@rice.edu>,
 Alexander Korotkov <aekorotkov@gmail.com>,
 Oleg Bartunov <obartunov@gmail.com>, Greg Smith <greg@2ndquadrant.com>,
 PgHacker <pgsql-hackers@postgresql.org>
Subject: Re: [v9.4] row level security
References: 
 <CADyhKSWkHrPqbRsFK0LjPMOOTsbKGWOuN75GqYJrxCDO3CKWtA@mail.gmail.com>
	<521F7DFB.8060602@agliodbs.com>
	<CADyhKSW_o2nVWFytdTY84DUXZSuZkD_EtY2d8wAjR18UruOXYQ@mail.gmail.com>
	<WM!fb94ecb91cd381e69d8e4773d3810f48e4e0ef000525cc806c4ee509f215ce1a39c8c2a1c3ffde72ba2ddb1a07ebcdc9!@asav-2.01.com>
	<5220D832.7090104@agliodbs.com>	<20130830192729.GO2706@tamriel.snowman.net>
	<16798.1377891794@sss.pgh.pa.us>
	<CA+TgmoZYv_me1iB6iDRA_oO-o9xkxXYqr-0UtwK5itpZbDsqYA@mail.gmail.com>
	<1398.1378305956@sss.pgh.pa.us>
	<CA+TgmoYTf+4RUOW8stVz1RE+3TD+QR5TNKzYuFDCehSkieL5OQ@mail.gmail.com>
	<1543.1378306249@sss.pgh.pa.us>	<2620.1378308159@sss.pgh.pa.us>
	<52775604.9090306@2ndquadrant.com>	<11101.1383575839@sss.pgh.pa.us>
	<CA+TgmoZS46fGTHCMo1FpNiBYDU7Wrx7R8PL+48Xc7UcVCsuaUA@mail.gmail.com>
	<52784DF9.9040205@2ndquadrant.com>
	<CA+TgmobvM9t8y6+BoG0DXMVA5jcNc9V6fzNt2tNSaVqsRmGpHA@mail.gmail.com>
	<5279E3CA.7050400@2ndquadrant.com>
 <CAEZATCXP-ZLtR0S3gMZwGdeYR=VEqoUoVCt9ntvkzdU9zU7RQQ@mail.gmail.com>
In-Reply-To: 
 <CAEZATCXP-ZLtR0S3gMZwGdeYR=VEqoUoVCt9ntvkzdU9zU7RQQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 11/06/2013 05:02 PM, Dean Rasheed wrote:

> The basic idea is to have rewriteTargetView() collect up any quals
> from SB views in a new list on the target RTE, instead of adding them
> to the main query's predicates (it needs to be a list of SB quals, in
> case there are SB views on top of other SB views, in which case they
> need to be kept separate from one another). Then at the end of the
> rewriting process (after any views referenced in the SB quals have
> been expanded), a new piece of code kicks in to process any RTEs with
> SB quals, turning them into (possibly nested) subquery RTEs.

That makes sense, though presumably you face the same problem that the
existing RLS code does with references to system columns that don't
normally exist in subqueries?

Since this happens during query rewrite, what prevents the optimizer
from pushing outer quals down into the subqueries?

> The complication is that the query's resultRelation RTE mustn't be a
> subquery.

I think this is what Robert was alluding to earlier with his comments
about join relations:

____
Robert Haas wrote:
> I don't really see why.  AIUI, the ModifyTable node just needs to get
> the right TIDs.  It's not like that has to be stacked directly on top
> of a scan; indeed, in cases like UPDATE .. FROM and DELETE .. USING it
> already isn't.  Maybe there's some reason why the intervening level
> can be a Join but not a  SubqueryScan, but if so I'd expect we could
> find some way of lifting that limitation without suffering too much
> pain.
(http://www.postgresql.org/message-id/CA+TgmoYr1PHw3X9vnVuWDcfXkzK2p_jhtWc0fV2Q58NEgcxyTA@mail.gmail.com)
____


Maybe we just need to make a subquery scan a valid target for an update,
so those fixups aren't required anymore?

> This is handled this in a similar way to the
> trigger-updatable views code, producing 2 RTEs --- the resultRelation
> RTE becomes a direct reference to the base relation, and a separate
> subquery RTE acts as the source of rows to update.

Some of the complexity of the current RLS code is caused by the need to
do similar fixups to handle the case where the input relation isn't the
same as the target relation, but is a subquery over it instead.

> Anyway, feel free to do what you like with this. I wasn't planning on
> submitting it to the next commitfest myself, because my non-PG
> workload is too high, and I don't expect to get much time to hack on
> postgresql during the next couple of months.

Thanks for sending what you have. It's informative, and it shows that
some of the same issues must be solved for writable security barrier
views and for RLS.


-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers