How to Configure DuckDB Column Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up duckdb column level security by creating restricted database views and separating underlying storage files. Because the engine runs inside your process, it lacks native role checks for individual columns. Teams often share full database files with analysts to speed up local work. This broad sharing exposes sensitive fields like emails and phone numbers to every user. How to set up duckdb column level security in your workflow The engine runs in the same memory space as the host app

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 duckdb column level security by creating restricted database views and separating underlying storage files. Because the engine runs inside your process, it lacks native role checks for individual columns. Teams often share full database files with analysts to speed up local work. This broad sharing exposes sensitive fields like emails and phone numbers to every user.

How to set up duckdb column level security in your workflow

The engine runs in the same memory space as the host app. It does not run a central server that checks user logins. Any user who can read the local file can read every table and column.

To limit column reads, teams build SQL views that omit private fields. You select only the safe columns and save that query as a clean view. Still, a view inside the same file does not stop a local user. The user can still query the base table right next to the view.

To enforce the rule, you must split your data across separate storage paths. You keep the raw tables with card numbers and national IDs in a locked folder. Then you export a second file or view layer for general team members. Your storage permissions decide which OS user or cloud role can read each path.

Cloud teams apply the same idea with object storage buckets. They grant a cloud role permission to read only the curated Parquet files. The engine uses those cloud keys when it pulls data into memory. If a column is not in the file, the user cannot query it.

What native file splitting solves and where it falls short

Splitting files and using views works well for static reporting datasets. Analysts get fast local queries on safe columns without touching raw storage buckets. Data engineers control which fields leave the main warehouse during nightly batch exports.

Yet this file approach breaks down when teams run ad-hoc checks on live data. Duplicating large tables just to drop two columns wastes storage and pipeline time. Every new role needs its own file export and its own storage rule.

Column-Level Encryption + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

File permissions only decide who can open a file at the start. They do not inspect what individual SQL statements do while a session runs. If an engineer needs a live table to fix a bug, they get every column. Native file controls cannot rewrite emails or card numbers in query results on the fly.

Managing sensitive columns during live data sessions

Teams need a way to run live queries without copying files for every role. Instead of splitting storage, you can filter results as data leaves the engine. Dynamic data masking rewrites matched fields like phone numbers and national IDs in results.

This approach keeps one source of truth for your tables. Analysts and scripts run standard queries against the same underlying dataset. The system checks the user identity and applies column rules to each response. You also get a clear log of every query that touched the database.

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 DuckDB have built-in user roles and column grants?

No, it is an embedded engine without a built-in user login system. It relies on host OS file permissions or external controls to restrict data.

Can SQL views alone secure sensitive columns in a local file?

No, a user with read rights to the file can bypass the view. They can run a select statement straight against the base table.

Which sensitive fields should teams mask in analytical queries?

Teams commonly mask emails, phone numbers, card numbers, and national IDs. Masking rewrites these matched fields in query results while keeping the query working.

The query runs. The emails stay hidden.