public inbox for [email protected]  

help / color / mirror / Atom feed

From: Joe Carlson <[email protected]>
To: [email protected]
Subject: Row level security policy policy versus SQL constraints. Any performance difference?
Date: Tue, 17 Oct 2017 13:44:24 -0700
Message-ID: <[email protected]> (raw)
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Row level security policy policy versus SQL constraints. Any performance difference?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox