How to Configure DuckDB User Roles

Published
Length
3 min read

Luis Felipe Dias

All posts

DuckDB is an in-process engine that does not have built-in duckdb user roles. Instead, you manage permissions through operating system file rules and read-only session settings. Teams often share database files across local machines, containers, or shared storage buckets. When file permissions are too broad, any user can change or delete key tables. Broad permissions also expose sensitive customer columns to every analyst on the team. How duckdb user roles work in an embedded engine Traditio

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.

DuckDB is an in-process engine that does not have built-in duckdb user roles. Instead, you manage permissions through operating system file rules and read-only session settings.

Teams often share database files across local machines, containers, or shared storage buckets. When file permissions are too broad, any user can change or delete key tables. Broad permissions also expose sensitive customer columns to every analyst on the team.

How duckdb user roles work in an embedded engine

Traditional client-server databases check usernames and passwords before running any query. They store role tables inside the database catalog to manage table access. DuckDB works differently because it runs directly inside your app or local process.

Because there is no central server, the engine inherits the permissions of the host process. If an engineer can read the database file on disk, they can query every table inside it. If they have write permissions on that file, they can drop tables or alter rows.

To mimic duckdb user roles, teams split users by how they open the database file. Data pipelines open the file in read-write mode to load new batches of records. Analysts open the same file in read-only mode so their queries cannot change data.

Teams also use cloud storage roles when querying remote Parquet or CSV files. The engine uses the storage key of the host machine to fetch remote files. You can limit what a team member reads by scoping their storage bucket keys.

Setting up file and view rules for teams

A common way to separate duties is through operating system groups and file permissions. You can create one system group for data writers and another group for data readers. The database file owner grants write rights only to the data pipeline group.

Analysts join the reader group, which gives them read-only rights on the database file. When multiple readers open the file in read-only mode, they can run queries at the same time. If one user opens the file for writing, the engine locks the file against other processes.

User Provisioning (SCIM) + Lambda Execution Roles: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

When teams need to hide specific columns, they often build separate database files or views. A build script reads the raw file and writes a clean copy for analysts. This clean copy leaves out columns with emails, phone numbers, or card numbers.

What native file permissions solve and what they leave open

Operating system rules and storage keys do a good job of stopping unauthorized file changes. They keep analysts from corrupting production files during ad-hoc data work. They also let multiple team members read shared analytical files at the same time.

Still, file permissions are all-or-nothing once a user opens the database file. File rules decide who can open the file, not what individual queries do in flight. Any user with read rights on the file can pull every row and column inside it by default.

Maintaining separate clean copies of large files also creates extra work for data teams. Every time a raw table changes, someone must update the copy script and rebuild the file. Also, local file reads do not create a central log of who ran which query.

Security teams need a way to inspect queries and mask sensitive fields in results. When masking rewrites emails, phone numbers, and national IDs on the fly, teams avoid copying files. Central query logs also show every statement that ran against the shared data.

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 support native SQL statements to create users and roles?

No, the core engine does not include user accounts or role tables. It relies on the host operating system or cloud storage permissions to control file access.

How can multiple analysts query the same DuckDB file at once?

Every analyst must open the database file in read-only mode during their session. Read-only mode lets multiple processes query the file at the same time without file lock errors.

How do teams protect sensitive columns like emails or card numbers in DuckDB?

Without built-in column roles, teams either create scrubbed file copies or apply dynamic data masking. Masking rewrites matched fields like emails and card numbers in query results before returning them.

The query runs. The emails stay hidden.