UX disparity in security definer views on distributed tables

[hanefi]

PG15 introduced support for security invoker views. All views before PG15 are security definer views.

A security definer view checks privileges of the view owner, rather than checking for permissions for accessing its underlying base relations using the privileges of the user of the view. Additionally, if any of the base relations are tables with RLS enabled, the policies of the view owner are applied, rather than those of the user of the view.

There is a UX difference between vanilla views accessing distributed tables and views accessing local tables.

Repro steps

Vanilla experience

Run the following as privileged user

create user test;
create table source_data as select id from generate_series(1,10) id;
create view old_type_view as select * from source_data;
grant select on old_type_view to test;

Connect with user test:

select * from old_type_view;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
(10 rows)

Citus experience

Run the following as privileged user

create user test;
create table source_data as select id from generate_series(1,10) id;
SELECT create_distributed_table('source_data','id');
create view old_type_view as select * from source_data;
grant select on old_type_view to test;

Connect with user test:

select * from old_type_view;
NOTICE:  00000: issuing SELECT id FROM public.source_data_102028 source_data WHERE true
DETAIL:  on server test@localhost:9701 connectionId: 3
LOCATION:  LogRemoteCommand, remote_commands.c:355
NOTICE:  00000: issuing SELECT id FROM public.source_data_102029 source_data WHERE true
DETAIL:  on server test@localhost:9702 connectionId: 4
LOCATION:  LogRemoteCommand, remote_commands.c:355
ERROR:  42501: permission denied for table source_data_102028
CONTEXT:  while executing command on localhost:9701
LOCATION:  ReportResultError, remote_commands.c:322

[onderkalaci]

Probably similar to https://github.com/citusdata/citus-enterprise/issues/816, and we could leave as is for Citus 11. Though, a good improvement area for future

[naisila]

Another way to repro:

-- for less log remote commands
SET citus.shard_count TO 2;

-- create a new user for testing
CREATE USER test;

-- create a source table for the view
CREATE TABLE source_data AS SELECT id FROM generate_series(1,10) id;

-- create a view (which is security definer by default)
CREATE VIEW sec_def_view AS SELECT * FROM source_data;

-- make it possible for our new test user to invoke this view
-- NOTE HERE: user "test" can invoke this view
--            whereas the privileged user defined this view, and can invoke this view
GRANT SELECT ON sec_def_view TO test;

-- let's try with our new role
SET ROLE test;

-- works because source_data is not distributed
-- user test doesn't have select permission on the source table
-- however he has select permission on the view
-- that is enough because this is a security definer view
-- this means that the permissions for tables inside the view are
-- checked for the view definer user which is the privileged user
SELECT * FROM sec_def_view;

RESET role;

-- now let's distribute the source table for the view
-- the view will be automatically distributed as well
SELECT create_distributed_table('source_data','id');

-- now let's try with our new role
SET ROLE test;

SET citus.log_remote_commands TO true;
SET client_min_messages TO DEBUG4;

-- fails because Citus sends direct queries of the source table to the shards
-- however user test doesn't have select permission on the source table
-- user test only has select permission on the view
-- when the query goes to the shards permissions are not checked with the view definer
-- permissions are checked with the user test

SELECT * FROM sec_def_view;

DEBUG:  generated sql query for task 1
DETAIL:  query string: "SELECT id FROM public.source_data_102008 source_data WHERE true"

DEBUG:  generated sql query for task 2
DETAIL:  query string: "SELECT id FROM public.source_data_102009 source_data WHERE true"

-- we are here: user is "test"
-- we are trying to select from the shard source_data_102008 with this user
-- Citus has no idea we are part of a view here
-- select query fails since user "test" doesn't have permissions on source data table
-- possible fix: before querying we set the user to the view definer user
-- from what I checked it is not a trivial fix at all
NOTICE:  issuing SELECT id FROM public.source_data_102008 source_data WHERE true
DETAIL:  on server test@localhost:9701 connectionId: 5

NOTICE:  issuing SELECT id FROM public.source_data_102009 source_data WHERE true
DETAIL:  on server test@localhost:9702 connectionId: 6

ERROR:  permission denied for table source_data_102008
CONTEXT:  while executing command on localhost:9701

[naisila]

If we try to wrap the security definer view with a security invoker view, we still get the same error, i.e. we don't escape anything. Check out the following example:

-- try this in PG15+
CREATE TABLE events (tenant_id int, event_id int, descr text);
SELECT create_distributed_table('events','tenant_id');
INSERT INTO events VALUES (1, 1, 'push');

CREATE VIEW sec_definer_view AS SELECT * FROM events;

CREATE ROLE rls_tenant_1 WITH LOGIN;

GRANT SELECT ON sec_definer_view TO rls_tenant_1;

SET ROLE rls_tenant_1;

SELECT * FROM sec_definer_view ORDER BY event_id;
ERROR:  permission denied for table events_102008
CONTEXT:  while executing command on localhost:9701

RESET ROLE;

-- wrap with a security invoker view
-- we still get the error in the shard level, not the table level
CREATE VIEW sec_invoker_view WITH (security_invoker=true) AS SELECT * FROM sec_definer_view;

GRANT SELECT ON sec_invoker_view TO rls_tenant_1;

SET ROLE rls_tenant_1;

SELECT * FROM sec_invoker_view ORDER BY event_id;
ERROR:  permission denied for table events_102008
CONTEXT:  while executing command on localhost:9701

RESET ROLE;