citusdata / citusdata/citus

UX disparity in security definer views on distributed tables

Open
#6,161 3 comments 0 reactions 0 assignees View on GitHub
RLS
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

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

```sql
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:
```sql
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

```sql
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:
```sql
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
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.