Role cannot access aggregated view data via security barrier view

[lukasJendrzejczyk]

Relevant system information: OS: Windows 10 PostgreSQL version: Postgres 12 TimescaleDB version: 2.0.0-rc4 Installation method: Docker container

Describe the bug When a security barrier view is created on a continuous aggregated view and a user is granted select privileges on the view, the user cannot access data from the continuous aggregated view via the security barrier view.

To Reproduce Steps to reproduce the behavior: Execute the following sql statements:

-- Create base table
CREATE TABLE base_table (
    received_data int,
    received_at timestamp without time zone
);
SELECT * FROM create_hypertable('base_table', 'received_at');

-- Create continuous aggregated view
CREATE MATERIALIZED VIEW base_table_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket(INTERVAL '1 hour', received_at) AS bucket,
       AVG(received_data)
FROM base_table
GROUP BY bucket;

-- Create security barrier view
CREATE VIEW view_on_aggregated WITH (security_barrier)
    AS SELECT * FROM base_table_hourly;

-- Create role and access security barrier view
CREATE ROLE bob;
GRANT SELECT ON view_on_aggregated TO bob;

SET ROLE bob;
SELECT * FROM view_on_aggregated;

Expected behavior Corresponding to regular materialized views, the privileges should be checked against the privileges of the continuous aggregated view. Roles with select privileges on the security barrier view should be able to access data from the aggregated view. For example, the following script would execute without problems:

-- Create base table
CREATE TABLE base_table (
    received_data int,
    received_at timestamp without time zone
);
SELECT * FROM create_hypertable('base_table', 'received_at');

-- Create materialized view
CREATE MATERIALIZED VIEW base_table_hourly AS
    SELECT * FROM base_table;

-- Create security barrier view
CREATE VIEW view_on_aggregated WITH (security_barrier)
    AS SELECT * FROM base_table_hourly;

-- Create role and access security barrier view
CREATE ROLE bob;
GRANT SELECT ON view_on_aggregated TO bob;

SET ROLE bob;
SELECT * FROM view_on_aggregated;

Actual behavior Error is returned. The role is expected to have privileges for the aggregated view.

ERROR:  permission denied for materialized view base_table_hourly
SQL state: 42501

Actual context The goal is to restrict access to an aggregated view on row level. Because aggregated views cannot be secured by row level security policies, an alternative is created by creating a security barrier view on the aggregated view.

[erimatnor]

@lukasJendrzejczyk Thank you for reporting this issue. This is certainly something we need to take a closer look at.

[svenklemm]

The permission checks are coming from _timescaledb_internal.cagg_watermark. As a workaround you can disable realtime aggregation so the view only returns materialized data.

alter materialized view base_table_hourly set (timescaledb.materialized_only=true);

[lukasJendrzejczyk]

@svenklemm Thank you for the suggestion. That definitely is a useful workaround. However, it would still be nice to utilize the real-time aggregation feature of continuous aggregated views, if possible.

[phemmer]

Is there any update to this?

What do we need to grant access to to allow querying a real time aggregate? I was going to try grant select on _timescaledb_internal.cagg_watermark, but it seems _timescaledb_internal.cagg_watermark doesn't exist any more.

[phemmer]

Ok, looks like cagg_watermark is a function, not a table or view, and was moved into the _timescaledb_functions schema.
I ended up solving this with:

alter function _timescaledb_functions.cagg_watermark security definer;