Обсуждение: CREATE POLICY bug ?

Поиск
Список
Период
Сортировка

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','manager@scuola247.it');
INSERT INTO public.policy_tab (id, description, usr) VALUES (2,'due','manager@scuola247.it');
INSERT INTO public.policy_tab (id, description, usr) VALUES (3,'tre','manager@scuola247.it');
INSERT INTO public.policy_tab (id, description, usr) VALUES (4,'quattro','teacher@scuola247.it');
INSERT INTO public.policy_tab (id, description, usr) VALUES (5,'cinque','teacher@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: manager@scuola247.it
rows 4,5 for user: teacher@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 


Re: CREATE POLICY bug ?

От
Dean Rasheed
Дата:
On 20 August 2016 at 03:15, Andrea Adami <fol@fulcro.net> 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
willuse the view owner's   rights and any policies which apply to the view owner.
 

Regards,
Dean

Re: 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 <fol@fulcro.net> wrote:
> Thnaks Dean, i did further investigations:
> i set the owner of the view to: "manager@scuola247.it" with:
> ALTER TABLE public.policy_view OWNER TO "manager@scuola247.it";
> and i thinking to see from the select:
> select * from policy_view
> the rows: 1,2,3
> then
> set role 'manager@scuola247.it';
> select * from policy_view;
> return rows 1,2,3 as expected but:
> set role 'teacher@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
"manager@scuola247.it", access to the table via the view is subject to
the policies that apply to "manager@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