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

The following bug has been logged on the website:

Bug reference: 14060
Logged by: Artur Dudnik
Email address: adudnik(at)gmail(dot)com
PostgreSQL version: 9.5.1
Operating system: Windows
Description:

-- row security policy does not work for updatable views.

-- 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

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;

grant select, update on t_odd to test;
grant select, update on t2_odd to test;
grant select, update on t3_odd to test;

set role test;

update t3_odd set secret = '!!!' where n in (2, 1);

select * from t3_odd;

update t_odd set secret = '!!!' where n in (4, 3);

select * from t_odd;

update t2_odd set secret = '!!!' where n in (6, 5);

select * from t2_odd;

set role postgres;

select * from t;

drop view t_odd cascade;
drop view t2_odd cascade;
drop table t cascade;
drop role test;

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

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
>

Artur,

* Artur Dudnik (adudnik(at)gmail(dot)com) wrote:
> > 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?

You can create the view as a role with a restricted set of access, as
described.

Alternatively, you can create a view owned by a role who has
unrestricted access to the table (such as the table owner) and then
implement the per-row restrictions there using WHERE and making the view
a security barrier view.

As with the regular GRANT system, it isn't possible to use the logged-in
role to perform access permission checks on a table which is being
queried through a view.

We may add support for defining policies on views in the future, but
that won't be until at least 9.7.

Thanks!

Stephen