[Bug]: Permissions issues when granting SELECT only access to Hypertable via default permissions

[michaelkitson]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Continuous aggregate

What happened?

If a role is granted default permissions to a hypertable, those permissions don't propagate to its continuous aggregates.

This is related to #2630

TimescaleDB version affected

2.7.2

PostgreSQL version used

14.4

What operating system did you use?

MacOS 12.4

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

ERROR:  permission denied for table _materialized_hypertable_2
CONTEXT:  SQL statement "SELECT max(hour) FROM _timescaledb_internal._materialized_hypertable_2"

How can we reproduce the bug?

CREATE SCHEMA test;
CREATE ROLE read_only;
GRANT USAGE ON SCHEMA "test" TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA "test" TO read_only; -- not really necessary here.
ALTER DEFAULT PRIVILEGES IN SCHEMA "test" GRANT SELECT ON TABLES TO read_only;

CREATE USER reader WITH PASSWORD 'test_read';
GRANT read_only TO reader;

CREATE TABLE test.data (
  "time" timestamp with time zone DEFAULT now() NOT NULL,
  "asset" integer NOT NULL,
  value numeric NOT NULL
);
SELECT create_hypertable('test.data', 'time', 'asset', 5, chunk_time_interval => INTERVAL '1 day');

CREATE TABLE test.data_nothyper (
  "time" timestamp with time zone DEFAULT now() NOT NULL,
  "asset" integer NOT NULL,
  value numeric NOT NULL
);

CREATE MATERIALIZED VIEW test.data_1hr
            WITH (timescaledb.continuous) AS
SELECT time_bucket(INTERVAL '1 hour', "time") AS "hour",
  asset,
  avg(value) as "average"
FROM test.data
GROUP BY "hour", asset
WITH NO DATA;

INSERT INTO test.data VALUES (now() - INTERVAL '240 minutes', 1, 12);
INSERT INTO test.data VALUES (now() - INTERVAL '210 minutes', 1, 10);
INSERT INTO test.data VALUES (now() - INTERVAL '180 minutes', 1, 8);
INSERT INTO test.data VALUES (now() - INTERVAL '150 minutes', 1, 14);
INSERT INTO test.data VALUES (now() - INTERVAL '120 minutes', 1, 12);
INSERT INTO test.data VALUES (now() - INTERVAL '90 minutes', 1, 14);
INSERT INTO test.data VALUES (now() - INTERVAL '60 minutes', 1, 19);
INSERT INTO test.data VALUES (now() - INTERVAL '30 minutes', 1, 21);
INSERT INTO test.data VALUES (now() - INTERVAL '1 minutes', 1, 23);

SET ROLE reader;
select * from test.data; -- works
select * from test.data_nothyper ; -- works
select * from test.data_1hr ; -- ERROR:  permission denied for table _materialized_hypertable_2

[fabriziomello]

@michaelkitson thanks for reporting this... seems this is an oversight and we're not dealing properly with default privileges for continuous aggregates (propagating to the underlying materialization hypertable). The problem here is that the underlying materialization hypertable resides in another schema (different than defined in the default privilege configuration).

I'm working on a fix for that, but for now the only workaround for this is explicit execute GRANT in the continuous aggregate that will propagate it down to the underlying hypertable.

[fabriziomello]

@michaelkitson fixed in PR #4580

[leppaott]

@fabriziomello does this cover also FOR ROLE:

ALTER DEFAULT PRIVILEGES FOR ROLE :"diagnostics_user" IN SCHEMA "wm_diagnostics"
GRANT SELECT ON TABLES TO :"user";

We are still getting permission denied when creating a cagg on a hypertable in a different schema.