Subject: Re: Row level security policy policy versus SQL
 constraints. Any performance difference?
To: Joe Carlson <jwcarlson@lbl.gov>, pgsql-performance@postgresql.org
References: <59E66BA8.6060009@lbl.gov>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <993c32d2-1389-66aa-1368-ca44de370a3a@2ndquadrant.com>
Date: Tue, 17 Oct 2017 23:35:58 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: <59E66BA8.6060009@lbl.gov>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

On 10/17/2017 10:44 PM, Joe Carlson wrote:
> Hello.
> 
> I have not used row level security policies in the past but am
> considering using them for a project in which I would like to restrict
> the set returned in a query based on specific fields. This is more as a
> convenience issue (for me) rather than a security issue.
> 
> What I was wondering is what is the performance differences between a
> row level security implementation:
> 
> CREATE POLICY <policy name> ON <table> TO <role> USING
> (<field>=ANY(<values>));
> <series of selects>
> DROP POLICY <policy name>
> 
> and an implementation where I add on the constraints as part of each
> select statement:
> 
> SELECT <whatever> FROM <table> WHERE <constraints> AND
> <field>=ANY(<values>)
> 
> In my (admittedly small) number of EXPLAINs I've looked at, it appears
> that the policy logic is added to the SELECT statement as a constraint.
> So I would not expect any fundamental performance difference in the 2
> different forms.
> 
> Is this true? Or is there some extra behind-the-scenes things to be
> aware of? Can there be excessive overhead from the CREATE/DROP POLICY
> statements?
> 

The main point of the RLS is enforcing an order in which the conditions
are evaluated. That is, the "security" quals (coming from RLS policies)
have to be evaluated first, before any quals that might leak information
about the values (imagine a custom PL/pgSQL function inserting the data
somewhere, or perhaps just printing debug messages).

(Many built-in operators are however exempt from that, as we consider
them leak-proof. This allows us to use non-RLS conditions for index
scans etc. which might be impossible otherwise)

Otherwise yes - it's pretty much the same as if you combine the
conditions using AND. It's "just" much more convenient approach.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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