0

I have a table which I'm trying to apply a policy on, the setup looks something like this:

create role anonymous nologin;
create schema api;
create schema private;
create table private.mytable(
    id  serial primary key,
    description text default ''
);
create view api.mytable as select * from private.mytable;
insert into api.mytable (description) values ('row 1'), ('row 2');
grant usage on schema api to anonymous;
grant select on api.mytable to anonymous;
alter table private.mytable enable row level security;
create policy mytable_policy on private.mytable
    for select
    using (null);

When I set the role to anonymous and select all records from mytable:

set role anonymous;
select * from api.mytable;

I excpect no rows to be returned since my expression in the using clause in the policy is null but I get everything.
I tried different postgresql versions (9.5, 9.6, 10.3) but they all have the same behaviour, am I doing something wrong here?

Alex Kubica
  • 67
  • 10

1 Answers1

0

update

https://stackoverflow.com/a/33863371/5315974

RLS won't work with views like that. You can use RLS for views though it is limited.

what you can do is

alter view api.mytable owner to anonymous ;
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Using your exact commands I cannot reproduce it, but try my script from the question. I see the differences are that you started a transaction with BEGIN, and that you're granting permissions to anonymous on the private schema (which I don't want to do). – Alex Kubica May 09 '18 at 12:12
  • I fixed typos and added missing permissions. the rest is exactly your script - no?.. – Vao Tsun May 09 '18 at 12:13
  • No, you did the select on the private table rather than the public view: select * from private.mytable; It seems that policies do not apply on views too. – Alex Kubica May 09 '18 at 12:32
  • ues! indeed. now I see what you mean :) look here then https://stackoverflow.com/questions/33858030/why-isnt-row-level-security-enabled-for-postgres-views – Vao Tsun May 09 '18 at 12:40
  • Thank you very much for your help! – Alex Kubica May 09 '18 at 12:48
  • sure, my pleasure – Vao Tsun May 09 '18 at 12:53