How to Configure TimescaleDB Backup User
You set up a timescaledb backup user by making a dedicated Postgres role with read access to user schemas and internal extension tables. This role needs read rights on both your data and the hidden metadata that tracks time chunks. Many teams skip this step and use the main database admin account for nightly dumps. That choice creates a huge risk if a backup script or server gets breached. A dedicated role limits what an attacker or a broken script can do to your live data. How a timescaledb b
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You set up a timescaledb backup user by making a dedicated Postgres role with read access to user schemas and internal extension tables. This role needs read rights on both your data and the hidden metadata that tracks time chunks. Many teams skip this step and use the main database admin account for nightly dumps. That choice creates a huge risk if a backup script or server gets breached. A dedicated role limits what an attacker or a broken script can do to your live data.
How a timescaledb backup user works in Postgres
TimescaleDB runs as an extension inside a standard Postgres database. It splits big time-series tables into smaller pieces called chunks. It tracks these chunks, compression rules, and background jobs inside its own internal schemas. Because of this structure, a normal read-only role is not enough for a clean backup.
To make a working role, an admin starts by creating a new user that can log in. This user does not get the right to make databases or add other roles. Next, the admin grants this user the built-in Postgres role for reading all data. If the team runs an older database version, the admin grants usage on each schema instead. They must include both the public tables and the internal extension schemas.
The backup role also needs read access to all sequences in those schemas. Without sequence access, the dump tool cannot record the current value of auto-incrementing keys. Finally, the admin sets default privileges on the database. This step makes sure the backup role can read new chunks created tomorrow. When the dump tool runs, it reads the catalog tables and exports every chunk in order.
Why native roles leave a gap during backups and restores
A dedicated role solves the problem of using a full admin account for daily dumps. The role cannot drop tables, change rows, or stop background jobs. This setup follows the rule of least privilege for automated scripts.
Still, native database roles only check who logs in at the start of a session. They do not inspect what individual queries do while the session is open. A backup role must be able to read every table to do its job. That means anyone who gets those login keys can read every sensitive row in the database. If your time-series tables hold user emails, phone numbers, or card numbers, the role reads them in plain text.
User Provisioning (SCIM) + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Restoring a backup brings a second problem. Creating the extension and running pre-restore scripts often requires elevated rights on the target server. Teams frequently hand out shared admin passwords to engineers during a recovery drill or incident. Once an engineer logs in with that shared key, the database logs only show the generic role name. You lose track of which person ran which command during the restore.
Managing backup and restore sessions safely
Good security splits automated dump jobs from human database sessions. Automated scripts should use the strict read-only role from a locked-down runner. Human engineers who check backups or test restores should not share static database passwords. They need a way to run checks where every query ties back to their real identity. They also need controls that rewrite sensitive fields like emails or card numbers in query results.
Add runtime guardrails and masking beside your databases and APIs
Login rules, role grants, and CI checks govern who connects to your stack. Yet any allowed script, engineer, or AI agent still runs commands with the full scope of its role. For workloads on Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes, SSH, or internal HTTP services, hoop.dev sits inline in the protocol beside the service as an open-source proxy, a sidecar for your database or API. On every production query an agent or human runs, hoop.dev decides whether to pass, mask, block, or escalate it for review.
Before a query runs, hoop.dev guardrails check the statement against your rules, an AI Analyzer you configure can score intent or hold risky calls for human approval, and hoop.dev data masking rewrites sensitive fields in responses in memory. Explore the MIT-licensed project on GitHub.
Frequently asked questions
Does a timescaledb backup user need superuser rights?
No, the role that takes the dump only needs read access to tables, sequences, and internal schemas. You only need elevated rights when you restore the extension into a fresh database.
Why do my backups fail on new time-series chunks?
New chunks are created as separate tables behind the scenes. If you do not set default privileges or use a global read role, the backup user cannot read those new tables.
Can the same role run pre-restore setup commands?
No, a read-only role cannot put the database into restore mode or create extensions. You should use a separate role with higher rights for the restore target.
Related guides
In this post