From: | Joe Carlson <jwcarlson(at)lbl(dot)gov> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Row level security policy policy versus SQL constraints. Any performance difference? |
Date: | 2017-10-17 20:44:24 |
Message-ID: | 59E66BA8.6060009@lbl.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2017-10-17 21:35:58 | Re: Row level security policy policy versus SQL constraints. Any performance difference? |
Previous Message | nijam J | 2017-10-17 09:28:45 | memory allocation |