Recursive planning with views might throw unnecessary permission errors
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
#4392 has multiple parts that we should investigate separately.
This is the issue for the permission errors that we might get when planning views recursively.
--------
In #4392, the main problem was incorrect pushdown of views.
However, we realized this thanks to citus requiring permissions for underlying tables too in some cases.
Normally postgres doesn't require per-table permissions when querying views.
However, when planning views recursively in citus, we require per-table permissions.
Supposing `pg_stat_activity` view, which accesses to `pg_authid`, here is the difference between postgres & citus:
```sql
-- create table & grant privileges
RESET ROLE;
CREATE TABLE distributed_table (a int, application_name text);
INSERT INTO distributed_table VALUES (1, 'psql');
CREATE USER new_user;
GRANT USAGE on SCHEMA public TO new_user ;
GRANT ALL ON distributed_table TO new_user;
```
```sql
-- postgres
SET ROLE new_user;
SELECT * FROM distributed_table
LEFT JOIN (
SELECT pg_stat_activity.application_name,
pg_stat_activity.query
FROM pg_catalog.pg_stat_activity
) pg_stat_activity_sub USING (application_name);
┌──────────────────┬───┬──────────────────────────┐
│ application_name │ a │ query │
├──────────────────┼───┼──────────────────────────┤
│ psql │ 1 │ │
└──────────────────┴───┴──────────────────────────┘
(1 rows)
```
```sql
--citus
RESET ROLE;
SELECT create_distributed_table('distributed_table', 'a');
SET ROLE new_user;
SELECT * FROM distributed_table
LEFT JOIN (
SELECT pg_stat_activity.application_name,
pg_stat_activity.query
FROM pg_catalog.pg_stat_activity
) pg_stat_activity_sub USING (application_name);
ERROR: permission denied for table pg_authid
```
Contributor guide
Assessment
This issue has not been assessed yet.