public inbox for [email protected]
help / color / mirror / Atom feedRow level security policy policy versus SQL constraints. Any performance difference?
4+ messages / 3 participants
[nested] [flat]
* Row level security policy policy versus SQL constraints. Any performance difference?
@ 2017-10-17 20:44 Joe Carlson <[email protected]>
2017-10-17 21:35 ` Re: Row level security policy policy versus SQL constraints. Any performance difference? Tomas Vondra <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Joe Carlson @ 2017-10-17 20:44 UTC (permalink / raw)
To: pgsql-performance
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?
Thanks,
Joe
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Row level security policy policy versus SQL constraints. Any performance difference?
2017-10-17 20:44 Row level security policy policy versus SQL constraints. Any performance difference? Joe Carlson <[email protected]>
@ 2017-10-17 21:35 ` Tomas Vondra <[email protected]>
2017-10-17 22:06 ` Re: Row level security policy policy versus SQL constraints. Any performance difference? Tom Lane <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Tomas Vondra @ 2017-10-17 21:35 UTC (permalink / raw)
To: Joe Carlson <[email protected]>; pgsql-performance
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Row level security policy policy versus SQL constraints. Any performance difference?
2017-10-17 20:44 Row level security policy policy versus SQL constraints. Any performance difference? Joe Carlson <[email protected]>
2017-10-17 21:35 ` Re: Row level security policy policy versus SQL constraints. Any performance difference? Tomas Vondra <[email protected]>
@ 2017-10-17 22:06 ` Tom Lane <[email protected]>
2017-10-17 22:18 ` Re: Row level security policy policy versus SQL constraints. Any performance difference? Joe Carlson <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2017-10-17 22:06 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Joe Carlson <[email protected]>; pgsql-performance
Tomas Vondra <[email protected]> writes:
> On 10/17/2017 10:44 PM, Joe Carlson wrote:
>> What I was wondering is what is the performance differences between a
>> row level security implementation:
>> ...
>> and an implementation where I add on the constraints as part of each
>> select statement:
> The main point of the RLS is enforcing an order in which the conditions
> are evaluated.
Yeah. Because of that, I would *not* recommend RLS if you can equally
well stick the equivalent conditions into your queries. There is way
too much risk of taking a serious performance hit due to a bad plan.
An alternative you might consider, if simplifying the input queries
is useful, is to put the fixed conditions into a view and query the
view instead. That way there's not an enforced evaluation order.
regards, tom lane
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Row level security policy policy versus SQL constraints. Any performance difference?
2017-10-17 20:44 Row level security policy policy versus SQL constraints. Any performance difference? Joe Carlson <[email protected]>
2017-10-17 21:35 ` Re: Row level security policy policy versus SQL constraints. Any performance difference? Tomas Vondra <[email protected]>
2017-10-17 22:06 ` Re: Row level security policy policy versus SQL constraints. Any performance difference? Tom Lane <[email protected]>
@ 2017-10-17 22:18 ` Joe Carlson <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Joe Carlson @ 2017-10-17 22:18 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Tomas Vondra <[email protected]>; +Cc: pgsql-performance
Thanks for your suggestions.
I had pretty much given up on this idea. At first, I had thought there
would only be 2 or 3 different constraint cases to consider. I had
thought of using distinct credentials for my connection and using RLS to
give different cuts on the same table. The different policies could be
established in advance and never touched.
But then it became clear that I actually would need a very large number
of different restrictions on the tables - too many to create in advance.
At this point it's easiest to apply constraints on each select rather
than apply a policy every time.
Thanks,
Joe
On 10/17/2017 03:06 PM, Tom Lane wrote:
> Tomas Vondra <[email protected]> writes:
>> On 10/17/2017 10:44 PM, Joe Carlson wrote:
>>> What I was wondering is what is the performance differences between a
>>> row level security implementation:
>>> ...
>>> and an implementation where I add on the constraints as part of each
>>> select statement:
>> The main point of the RLS is enforcing an order in which the conditions
>> are evaluated.
> Yeah. Because of that, I would *not* recommend RLS if you can equally
> well stick the equivalent conditions into your queries. There is way
> too much risk of taking a serious performance hit due to a bad plan.
>
> An alternative you might consider, if simplifying the input queries
> is useful, is to put the fixed conditions into a view and query the
> view instead. That way there's not an enforced evaluation order.
>
> regards, tom lane
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-10-17 22:18 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-17 20:44 Row level security policy policy versus SQL constraints. Any performance difference? Joe Carlson <[email protected]>
2017-10-17 21:35 ` Tomas Vondra <[email protected]>
2017-10-17 22:06 ` Tom Lane <[email protected]>
2017-10-17 22:18 ` Joe Carlson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox