[Bug]: refresh_continuous_aggregate only allows view owner to execute

[pgloader]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Continuous aggregate

What happened?

=> CALL refresh_continuous_aggregate('cag_censor_data', '2024-06-01', '2024-06-11');
ERROR: must be owner of view cag_censor_data

TimescaleDB version affected

2.15.2

PostgreSQL version used

16.3

What operating system did you use?

RHEL8

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

create a materialized view
have a user not the owner of the view to CALL refresh_continuous_aggregate('cag_censor_data', '2024-06-01', '2024-06-11');

[erimatnor]

@pgloader This is not a bug, but the expected behavior. The situation is the same for a materialized view. You can create a new role and make it owner of the cagg, then GRANT that role to roles.

Note that in p17, there's a MAINTAIN privilege for materialized views that caggs do not support (yet). It is possible that it can be supported in the future.

What is the behavior you need?

[pgloader]

We want to limit the application only have the execution privilege, not the broad owner ones.

[olssojoe]

We have a similar problem. We don't want application users to own objects, and we don't want owners of objects to have login rights. For regular materialized views, we solve it by having the owner of the materialized view have a procedure that contains refresh and that has the security definer set. For that procedure, execution rights are assigned to the application user and that user can then refresh by executing the procedure. If you do in an analogous way to refresh cagg, ie create a procedure owned by the cagg owner that contains "call refresh_continuous_aggregate" and that has the security definer set. On that procedure, execution rights are assigned to application user. When the application user or cagg owner executes the procedure, the following error occurs:

SQL Error [XX000]: ERROR: portal snapshots (1) did not account for all active snapshots (2)
Where: SQL statement "call timescaledb.refresh_continuous_aggregate('daily_stats','2024-08-22T00:00:00Z','2024-08-25T00:00:00Z')"

Is this a bug or expected behavior? Hard to interpret the error.

If security invoker is set, the procedure works for the owner of cagg, but of course not for the application user: SQL Error [42501]: ERROR: must be owner of view daily_stats.

Would be very good to have a solution to this so that it does not need to be the owner of cagg to do the refresh.

[mkindahl]

SQL Error [XX000]: ERROR: portal snapshots (1) did not account for all active snapshots (2)
Where: SQL statement "call timescaledb.refresh_continuous_aggregate('daily_stats','2024-08-22T00:00:00Z','2024-08-25T00:00:00Z')"

@olssojoe This is a bug resulting from an incorrect setup and teardown of the portal state when executing a call through a background worker. We've had these before, but seems not all are dealt with. If you have a reproducible case, we would be more than happy to fix it.

[olssojoe]

Below is my test case:

dsa=# select version();
                                                 version                                                
 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 15.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
(1 row)

dsa=# \dx timescaledb
                                      List of installed extensions
    Name     | Version |   Schema    |                            Description                           
 
-------------+---------+-------------+------------------------------------------------------------------
 timescaledb | 2.16.1  | timescaledb | Enables scalable inserts and complex queries for time-series data
(1 row)

dsa=# select user;
   user   
----------
 postgres
(1 row)

dsa=# create role data_owner;
CREATE ROLE
dsa=# create schema data authorization data_owner;
CREATE SCHEMA
dsa=# grant usage on schema timescaledb to data_owner;
GRANT
dsa=# grant execute on all functions in schema timescaledb to data_owner;
GRANT
dsa=# grant execute on all procedures in schema timescaledb to data_owner;
GRANT
dsa=# set role data_owner;
SET
dsa=> create table data.item_data (
dsa(> item_id int,
dsa(> data_time timestamptz,
dsa(> val double precision,
dsa(> primary key (item_id, data_time)
dsa(> );
CREATE TABLE
dsa=> select timescaledb.create_hypertable('data.item_data', 'data_time');
   create_hypertable    
------------------------
 (477,data,item_data,t)
(1 row)

dsa=> insert into data.item_data
dsa-> select
dsa-> item_id,
dsa-> '2024-08-22T00:00:00Z'::timestamptz + random() * ('2024-08-31T00:00:00Z'::timestamptz - '2024-08-22T00:00:00Z'::timestamptz) as data_time,
dsa-> random() * 100 as val
dsa-> from pg_catalog.generate_series(1, 100) item_id;
INSERT 0 100
dsa=> create materialized view data.item_daily_stats
dsa-> with (timescaledb.continuous) as
dsa-> select
dsa->    timescaledb.time_bucket(interval '1 day', data_time) as day_bucket,
dsa->    avg(val) as avg_val,
dsa->    max(val) as max_val,
dsa->    min(val) as min_val,
dsa->    count(1) as val_count
dsa-> from data.item_data
dsa-> group by day_bucket
dsa-> with no data;
CREATE MATERIALIZED VIEW
dsa=> create or replace procedure data.refresh_item_daily_stats() security definer as 
dsa-> $$
dsa$> begin
dsa$>   call timescaledb.refresh_continuous_aggregate('data.item_daily_stats','2024-08-22T00:00:00Z','2024-08-25T00:00:00Z');
dsa$> end;
dsa$> $$
dsa-> language plpgsql;
CREATE PROCEDURE
dsa=> set role none;
SET
dsa=# create user app_user with encrypted password 'app_user';
CREATE ROLE
dsa=# set role data_owner;
SET
dsa=# grant usage on schema data to app_user;
GRANT
dsa=# grant execute on procedure data.refresh_item_daily_stats to app_user;
GRANT
dsa=# set role app_user;
SET
dsa=# call data.refresh_item_daily_stats();
ERROR:  portal snapshots (1) did not account for all active snapshots (2)
CONTEXT:  SQL statement "call timescaledb.refresh_continuous_aggregate('data.item_daily_stats','2024-08-22T00:00:00Z','2024-08-25T00:00:00Z')"
PL/pgSQL function data.refresh_item_daily_stats() line 3 at CALL

[olssojoe]

Sorry, some bad formating when is was pasted.

[mkindahl]

Sorry, some bad formating when is was pasted.

No worries, fixed it.