Need superuser privileges to use pg_dump and pg_restore in Aiven Managed Timescale service

PostgreSQL
1

Hi!

I am a Managed Timescale customer (Managed Service for TimescaleDB) and I have an issue with user privileges.

I am not able to create a role with superuser privileges and I do not have the credentials for the 2 existing superuser roles (postgres and _aiven).

Superuser privileges are necessary to use properly pg_dump and pg_restore as indicated in Timescale documentation:

This is needed, for example, to dump a database from the Cloud to my local Linux instance for development purposes.

By following the instructions above without accessing with superuser privileges, I am able to dump and restore the hypertables but something weird happens with the continuous aggregates.

The materialized hypertables are listed as hypertables (SELECT * FROM timescaledb_information.hypertables) rather than as materialization hypertables (SELECT * FROM timescaledb_information.continuous_aggregates);

Also, when trying to access the content of the materialized views, an error is shown: invalid materialized hypertable ID.

Any hint how I could solve this issue?

Thanks,

Bernardo

2

Hi @Bernardo_Di_Chiara I’ve seen your post in Aiven Community too, I’ve asked our team for clarification. Hope to get back to you shortly!

3

Hey @Bernardo_Di_Chiara the team tells me that while we can’t give superuser rights, there’s a section in the Docs about using pg_dump: Create manual PostgreSQL® backups - Aiven

Does that help at all?

4

Hi Floor!

Thanks for your reply! I have been following the instructions available at www.postgresql.org.

I have also opened a ticket with timescale support. I hope I will get some help from there.

I still do not understand why I cannot get super user rights.

Best regards,

Bernardo

5

Hey @Bernardo_Di_Chiara! The way I understand it is that having superuser rights would allow one to make changes to the filesystem, which as you might expect is a no-go from a security perspective.

6

Some more relevant documentation: Aiven Security Agent for PostgreSQL®

7

Hi!

With a bit help from timescale.support I found a way to obtain the creation of all hypertables in the target database correctly.

Basically what I did was:

  • To create a new database in Timescale to be used as target database (rather than using the cleaned up defaultdb)
  • To create in the target database the same user with the same user privileges as in the source database
  • To upgrade the Timescale version in the source database to the same version as the target database: 2.11.0
  • Otherwise the target database is empty

Doing this way I got few notices, warning and even errors. This is due to the missing superuser privileges. The warnings are harmless. I am still checking about the errors with timescale support.

Best regards,

Bernardo

1 Like
8

Wow, thanks for reporting back! I’ll share this internally too!