citusdata / citusdata/citus

Recursive planning with views might throw unnecessary permission errors

Open
#4,406 2 comments 0 reactions 0 assignees View on GitHub
bug
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

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.