Steps to reproduce
- Deploy postgresql from the
16/stable channel in a VM model.
- Once postgresql has been deployed, enable the timescaledb plugin with
juju config postgresql plugin-timescaledb-enable=true
- Deploy the
data-integrator charm using juju deploy data-integrator --config database-name=test-db
- Integrate the charms using
juju integrate data-integrator postgresql. This will create a new username, password and database for the relation.
- Obtain DB credentials using
juju run data-integrator/leader get-credentials and ssh into the postgres unit.
- Login to postgres using
psql <connection-string>. This will log you in to the test-db that was created for the relation.
- Running
\du; shows that the charmed_test-db_owner role has the Cannot login message in role attributes.
- Run
SET ROLE "charmed_test-db_owner"; as the owner role is normally used to run schema-migrations and queries.
- Create a temporary table and hypertable
--- 1. Create a test table
CREATE TABLE demo_metrics (
time TIMESTAMPTZ NOT NULL,
temperature DOUBLE PRECISION
);
-- 2. Convert it into a TimescaleDB hypertable
SELECT create_hypertable('demo_metrics', 'time');
-
Add a TimescaleDB retention policy on the hypertable: SELECT add_retention_policy('demo_metrics', INTERVAL '30 days');.
This is a background process that will delete old data from the hypertable.
-
Trying to add a retention policy will result in
ERROR: permission denied to start background process as role "charmed_test-db_owner"
HINT: Hypertable owner must have LOGIN permission to run background tasks.
This means that the default user and role that will be created for a relation, will never be able to run queries or schema migrations that set up background processes for TimescaleDB.
Expected behavior
When the TimescaleDB extension is enabled, the relation user must have permissions to login, thereby enabling it to set up TimescaleDB background processes, such as, retention and aggregation policies.
Actual behavior
Because the default user and role created for a relation does not have any login privileges, any attempts to create TimescaleDB retention or aggregation policies, will result in an error.
Versions
Operating system: Ubuntu 22.04.5
Juju CLI: 3.6.23-genericlinux-amd64
Juju agent: 3.6.23
Charm revision: 1089
LXD: 5.0.6
Log output
Juju debug log:
log.txt
Additional context
Steps to reproduce
16/stablechannel in a VM model.juju config postgresql plugin-timescaledb-enable=truedata-integratorcharm usingjuju deploy data-integrator --config database-name=test-dbjuju integrate data-integrator postgresql. This will create a new username, password and database for the relation.juju run data-integrator/leader get-credentialsand ssh into the postgres unit.psql <connection-string>. This will log you in to thetest-dbthat was created for the relation.\du;shows that thecharmed_test-db_ownerrole has theCannot loginmessage in role attributes.SET ROLE "charmed_test-db_owner";as the owner role is normally used to run schema-migrations and queries.Add a TimescaleDB retention policy on the hypertable:
SELECT add_retention_policy('demo_metrics', INTERVAL '30 days');.This is a background process that will delete old data from the hypertable.
Trying to add a retention policy will result in
This means that the default user and role that will be created for a relation, will never be able to run queries or schema migrations that set up background processes for TimescaleDB.
Expected behavior
When the TimescaleDB extension is enabled, the relation user must have permissions to login, thereby enabling it to set up TimescaleDB background processes, such as, retention and aggregation policies.
Actual behavior
Because the default user and role created for a relation does not have any login privileges, any attempts to create TimescaleDB retention or aggregation policies, will result in an error.
Versions
Operating system: Ubuntu 22.04.5
Juju CLI: 3.6.23-genericlinux-amd64
Juju agent: 3.6.23
Charm revision: 1089
LXD: 5.0.6
Log output
Juju debug log:
log.txt
Additional context