Re: [HACKERS] CREATE POLICY bug ?

[Dean Rasheed]

[Please reply to the list, not just to me, so that others can benefit
from and contribute to the discussion]

On 31 August 2016 at 11:52, Andrea Adami  wrote:
> Thnaks Dean, i did further investigations:
> i set the owner of the view to: "mana...@scuola247.it" with:
> ALTER TABLE public.policy_view OWNER TO "mana...@scuola247.it";
> and i thinking to see from the select:
> select * from policy_view
> the rows: 1,2,3
> then
> set role 'mana...@scuola247.it';
> select * from policy_view;
> return rows 1,2,3 as expected but:
> set role 'teac...@scuola247.it';
> select * from policy_view;
> returns rows 4,5 and
> set role 'postgres'
> select * from policy_view
> return nothing ...
> what you thinking about ?
>
> Andrea

That's correct. With the table owned by postgres and the view owned by
"mana...@scuola247.it", access to the table via the view is subject to
the policies that apply to "mana...@scuola247.it". So regardless of
who the current user is, when selecting from the view, the policy
"standard" will be applied, and that will limit the visible rows to
those for which usr = current_user.

Regards,
Dean


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

Re: [HACKERS] CREATE POLICY bug ?

[Dean Rasheed]

On 20 August 2016 at 03:15, Andrea Adami  wrote:
> when i run the query: "select * from public.policy_view"
> the ouput is the same (all rows)  for all users
> i'm doing some mistakes or this is a bug ?
>

No, it looks correct to me. When going through a view, the policies
and permission checks that apply are those that would apply to the
view's owner, which in this case is postgres, so no policies are
applied.

Or, quoting from the notes in the CREATE POLICY documentation:

As with normal queries and views, permission checks and policies for
the tables which are referenced by a view will use the view owner's
rights and any policies which apply to the view owner.

Regards,
Dean


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

[HACKERS] CREATE POLICY bug ?

[Andrea Adami]

Hello,
i'm testing the new row security level  functionality in postgresql 9.5.
To do that i run this script:

---cut here --

CREATE TABLE public.policy_tab
(
  id bigint NOT NULL,
  description character varying(160) NOT NULL,
  usr name NOT NULL,
  CONSTRAINT policy_tab_pk PRIMARY KEY (id)
);

ALTER TABLE public.policy_tab OWNER TO postgres;

GRANT ALL ON TABLE public.policy_tab TO public;

CREATE OR REPLACE VIEW public.policy_view AS
 SELECT id,
description,
usr
   FROM public.policy_tab;

ALTER TABLE public.policy_view
  OWNER TO postgres;
GRANT ALL ON TABLE public.policy_view TO public;

ALTER TABLE public.policy_tab ENABLE ROW LEVEL SECURITY;

CREATE POLICY standard ON public.policy_tab
FOR ALL
TO PUBLIC
USING (usr = current_user);

INSERT INTO public.policy_tab (id, description, usr) VALUES (1,'uno','
mana...@scuola247.it');
INSERT INTO public.policy_tab (id, description, usr) VALUES (2,'due','
mana...@scuola247.it');
INSERT INTO public.policy_tab (id, description, usr) VALUES (3,'tre','
mana...@scuola247.it');
INSERT INTO public.policy_tab (id, description, usr) VALUES (4,'quattro','
teac...@scuola247.it');
INSERT INTO public.policy_tab (id, description, usr) VALUES (5,'cinque','
teac...@scuola247.it');

---cut here --

after that i run the query: "select * from public.policy_tab"

and the the oupt was what i excpected:

rows 1,2,3 for user: mana...@scuola247.it
rows 4,5 for user: teac...@scuola247.it
rows 1,2,3,4,5  for user:  postgres (the policy doesn't work for him)

but when i run the query: "select * from public.policy_view"

the ouput is the same (all rows)  for all users

i'm doing some mistakes or this is a bug ?

thank you in advance for the time you would like dedicate to me.

Andrea Adami