Re: BUG #14060: row security policy does not work for updatable views

Stephen,

First, Thank you for your answer!

> Try creating the view as the 'test' user and you'll see that then accessing
the table through the view will result in the RLS policy being applied.

Sure, you are right.

Excuse me for stupid question, but may be you will describe how I can
create view for table with restricted access to rows per roles?

For clirefy question, desered scenario:

1. create table
2. enable row level security policy for roles
3. create view without `where` with same rules
4. switch to one of restricted roles, receive on select restricted rows by
policy permissions.

I tryed to avoid additional code, that is why I choose this `view` way. But
now it's looks like wrong way for me.

Anyway thank you!

Regards,
Artur Dudnik

On Sun, Apr 3, 2016 at 4:40 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Artur,
>
> * adudnik(at)gmail(dot)com (adudnik(at)gmail(dot)com) wrote:
> > -- usage scenario:
> > -- 1. make a table
> > -- 2. enable row security for a role
> > -- 3. create view for restricted table
> > -- 4. grant to restricted role select and update for view and table
> >
> > -- expected behavior - view and table could select/update same records
> > -- bug behavior - view return/update all rows (policy ignored) and
> > security_barrier too
>
> The views you create here:
>
> > set role postgres;
> >
> > CREATE TABLE t AS SELECT n, 'secret'||n AS secret FROM
> generate_series(1,20)
> > n;
> >
> > create role test;
> >
> > grant select, update on t to test;
> > ALTER TABLE t ENABLE ROW LEVEL SECURITY;
> > CREATE POLICY t_all ON t TO test USING (n % 2 = 1);
> > CREATE VIEW t_odd WITH (security_barrier) AS SELECT * FROM t ;
> > CREATE VIEW t2_odd AS SELECT * FROM t ;
> > CREATE VIEW t3_odd WITH (security_barrier) AS SELECT * FROM t where n %
> 2 =
> > 1;
>
> Are all owned by the postgres user.
>
> As such, the accesses to the table through the view are done with the
> privileges of the postgres user. The postgres user owns the table, is
> the superuser, and has the BYPASSRLS role attribute, any of which are
> sufficient to bypass the RLS policies on the table.
>
> Try creating the view as the 'test' user and you'll see that then
> accessing the table through the view will result in the RLS policy being
> applied.
>
> Note that this is consistent with regular privileges for views and
> tables (a user accessing a view only needs access to the view, but the
> view owner must have access to the table).
>
> Thanks!
>
> Stephen
>