How to Configure TimescaleDB Column Level Security
You set up timescaledb column level security by assigning PostgreSQL column permissions to database roles. You first remove default table read rights and then grant read rights on safe columns. Many data teams skip this step and give broad read roles to every engineer. When permissions are too wide, any query can return emails, phone numbers, or card numbers. A single broad query can pull thousands of private records onto a local laptop. How timescaledb column level security works TimescaleDB
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 timescaledb column level security by assigning PostgreSQL column permissions to database roles. You first remove default table read rights and then grant read rights on safe columns. Many data teams skip this step and give broad read roles to every engineer. When permissions are too wide, any query can return emails, phone numbers, or card numbers. A single broad query can pull thousands of private records onto a local laptop.
How timescaledb column level security works
TimescaleDB runs as an extension on top of PostgreSQL. It uses the standard PostgreSQL role and permission system to control data reads. By default, a role with table read rights can read every column in that table. To restrict a column, an admin must first take away the broad table permission. Next, the admin grants read or write rights on a strict list of safe columns.
Time-series workloads use special tables called hypertables to store data across many time chunks. When you set column rules on the parent hypertable, the rules apply to the chunks. If a user runs a broad select query, the database blocks the whole request. The user must name only the allowed columns in their query to get results. Some teams also build database views that leave out private fields like national IDs. They then grant role permissions on those views instead of the base hypertable.
What native column rules solve and what they miss
Native column permissions work well for fixed apps and static reporting tools. You can lock down a dashboard role so it only reads timestamps and metric values. This stops automated charts from pulling emails or card numbers by mistake.
Still, native database roles are hard to manage for live human fixing. Engineers often need to inspect full rows during an outage or a bug fix. If a query fails because of one restricted column, the engineer asks for an admin role. Once an engineer logs in with a broad role, native column rules no longer apply. The database checks who logs in, not what individual statements do in flight.
Maintaining column grants across dozens of hypertables and views also creates heavy admin work. Every time a schema changes and adds a new column, admins must update role grants. If a team uses shared database accounts, audit logs cannot show which person ran a query. Also, native permissions are all or nothing. They cannot rewrite matched fields in results, such as masking phone numbers while showing the rest. Teams need a way to inspect queries and mask sensitive fields in real time.
Column-Level Encryption + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Managing schema changes and continuous aggregates
Time-series databases often use continuous aggregates to speed up slow queries over long time ranges. These aggregates work like materialized views that refresh on a set schedule. Importantly, a continuous aggregate does not copy the column rules from the base hypertable. Admins must set up separate role permissions on every aggregate view they create.
Schema updates add another risk for data teams. When a developer adds a new column to a hypertable, the permission model shifts. If an admin forgets to update the role grants, apps or users might lose read rights. Worse, an admin might grant full table read rights just to fix a broken build. Relying only on static database grants leaves gaps during fast daily operations.
Check database and API queries on the wire
Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev sits inline in the wire protocol beside those services as an open-source proxy, a sidecar for your Postgres or API. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.
Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.
Frequently asked questions
Does TimescaleDB have its own separate column permission system?
No, it uses the native PostgreSQL role and permission system under the hood. You manage column read and write access using standard PostgreSQL role grants on hypertables.
How does column security affect broad select queries?
If a role lacks permission on even one column, a select-all query will fail. Users and apps must list the exact allowed columns in every query they run.
Do continuous aggregates inherit column rules from the parent hypertable?
No, continuous aggregates act as separate database views with their own permission sets. You must grant role permissions on each aggregate view to control who reads that data.
Related guides
In this post