pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPolicy function not working (IN parameter not passed)
2+ messages / 2 participants
[nested] [flat]
* Policy function not working (IN parameter not passed)
@ 2020-05-29 08:47 Rainer Floegel (Suva) <rainer.floegel@suva.ch>
0 siblings, 1 reply; 2+ messages in thread
From: Rainer Floegel (Suva) @ 2020-05-29 08:47 UTC (permalink / raw)
To: 'pgsql-admin@lists.postgresql.org' <pgsql-admin@lists.postgresql.org>
Hello alltogether,
I'm pretty much a newbie to Postgresql having spent the last 20 years
almost exclusively with Oracle. When trying to set up row level policy
in Postgres, got stuck with the issue mentioned at the end of this post.
The testcase provided is reduced as much as possible in order not to
distract from the issue.
Tests were performed in PG 12.2 and 12.3.
The test PG Cluster version 12.3 was installed from scratch.
Yet, same outcome as in 12.2
-- as postgres
create database db1;
create user db1_owner password 'db1_owner';
alter database db1 owner to db1_owner;
grant all privileges on database db1 to db1_owner;
-- as db1_owner in db1
create schema policy_test;
-- drop table policy_test.partners;
create table policy_test.partners(business_partner text);
insert into policy_test.partners ( business_partner) values ('125210000');
insert into policy_test.partners ( business_partner) values ('125210001');
-- alter table policy_test.partners disable row level security;
alter table policy_test.partners enable row level security;
-- drop function policy_test.get_partners(text);
create or replace function policy_test.get_partners(text)
returns boolean as
$$
declare
is_true boolean := FALSE;
the_count integer := 0;
begin
select count(*)
into the_count
from policy_test.partners a
where a.business_partner = '125210000'
and a.business_partner = $1;
if the_count = 1 then
is_true := TRUE;
end if;
return is_true;
end;
$$ language plpgsql;
-- drop policy test_partner_policy on policy_test.partners;
create policy test_partner_policy on policy_test.partners
using (policy_test.get_partners(business_partner));
-- as postgres create pol_ex user;
create user pol_ex password 'pol_ex';
-- as db1_owner in db1
grant usage on schema policy_test to pol_ex;
grant select on policy_test.partners to pol_ex;
psql -U pol_ex -d db1
-- connect as pol_ex (ex here stands for "external" user)
select * from policy_test.partners;
Above select throws numerous lines shown underneath; sometimes in between the echoed SQL
error message max_stack_depth exceeded occurs. Beyond that no other error messages appear.
Whatever I tried the function does not accept / resolve the business_partner argument.
Is there a special way of indicating that this argument should be the tables' current row attribute?
Many examples on the Internet just pass the argument the way I tried.
Tried with prefixing scheme / table name to the argument, explict argument name instead of $ notation,
returning table of business_partners instead of boolean and so on, no avail.
Granting execute on the policy function to pol_ex did not help either.
I suspect a very basic issue due to my inexperience with Postgres.
If anyone of the seasoned Postgres Admins quickly can see what I did wrong, I'd be grateful for
a hint.
Thank you very much
Rainer Floegel
-- "Output" when running select * from policy_test.partners; (as pol_ex user)
SQL statement "select count(*)
from policy_test.partners a
where a.business_partner = '125210000'
and a.business_partner = $1"
PL/pgSQL function policy_test.get_partners(text) line 9 at SQL statement
SQL statement "select count(*)
from policy_test.partners a
where a.business_partner = '125210000'
and a.business_partner = $1"
PL/pgSQL function policy_test.get_partners(text) line 9 at SQL statement
and so on
[HTML tutorial]<https://www.suva.ch/de-ch/die-suva/news-und-medien/news/covid-19-informationen-fuer-unsere-kunden;
Coronavirus
Informationen f?r unsere Kunden
Informations actuelles destin?es ? nos clients
Informazioni aggiornate per i nostri clienti
www.suva.ch/covid-19<https://www.suva.ch/de-ch/die-suva/news-und-medien/news/covid-19-informationen-fuer-unsere-kunden;
________________________________
Disclaimer:
Diese Nachricht und ihr eventuell angeh?ngte Dateien sind nur f?r den Adressaten bestimmt. Sie kann vertrauliche oder gesetzlich gesch?tzte Daten oder Informationen beinhalten. Falls Sie diese Nachricht irrt?mlich erreicht hat, bitten wir Sie h?flich, diese unter Ausschluss jeglicher Reproduktion zu l?schen und die absendende Person zu benachrichtigen. Danke f?r Ihre Hilfe.
This message and any attached files are for the sole use of the recipient named above. It may contain confidential or legally protected data or information. If you have received this message in error, please delete it without making any copies whatsoever and notify the sender. Thank you for your assistance.
Attachments:
smime.p7s (application/pkcs7-signature, 5.3K) download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Policy function not working (IN parameter not passed)
@ 2020-05-29 16:06 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rainer Floegel (Suva) <rainer.floegel@suva.ch>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2020-05-29 16:06 UTC (permalink / raw)
To: Rainer Floegel (Suva) <rainer.floegel@suva.ch>; 'pgsql-admin@lists.postgresql.org' <pgsql-admin@lists.postgresql.org>
On Fri, 2020-05-29 at 08:47 +0000, Rainer Floegel (Suva) wrote:
> create or replace function policy_test.get_partners(text)
> returns boolean as
> $$
> declare
>
> is_true boolean := FALSE;
> the_count integer := 0;
>
> begin
>
> select count(*)
> into the_count
> from policy_test.partners a
> where a.business_partner = '125210000'
> and a.business_partner = $1;
>
> if the_count = 1 then
> is_true := TRUE;
> end if;
>
> return is_true;
>
> end;
> $$ language plpgsql;
>
> create policy test_partner_policy on policy_test.partners
> using (policy_test.get_partners(business_partner));
>
> select * from policy_test.partners;
>
> Above select throws numerous lines shown underneath; sometimes in between the echoed SQL
> error message max_stack_depth exceeded occurs. Beyond that no other error messages appear.
>
> SQL statement "select count(*)
> from policy_test.partners a
> where a.business_partner = '125210000'
> and a.business_partner = $1"
> PL/pgSQL function policy_test.get_partners(text) line 9 at SQL statement
> SQL statement "select count(*)
> from policy_test.partners a
> where a.business_partner = '125210000'
> and a.business_partner = $1"
> PL/pgSQL function policy_test.get_partners(text) line 9 at SQL statement
What do you expect?
In the function you select from the table, so the policy is applied, which calls
the function, and so on. Infinite recursion.
I am quite uncertain what your policy is supposed to achieve, but you should not
SELECT from the same table in the function.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-05-29 16:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-29 08:47 Policy function not working (IN parameter not passed) Rainer Floegel (Suva) <rainer.floegel@suva.ch>
2020-05-29 16:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox