Users, roles and grants

Hardcharger™’s access control keeps users, roles and grants in a PostgreSQL database that every replica of a cluster shares, and you manage them in SQL. Each statement is checked inside DuckDB against its own plan, after binding and again before optimizing, so nested SQL, views and macros can’t route around a grant. Anything the check can’t classify is refused.

Turn it on

Point rbac_store_uri at a PostgreSQL database. Hardcharger creates its tables in the schema hardcharger_rbac there:

HARDCHARGER_ADMIN_PASSWORD='change-me' \
HARDCHARGER_RBAC_STORE_URI='postgresql://hardcharger:secret@db.internal:5432/hardcharger' \
  hardcharger-server --database analytics.duckdb

The URI is postgresql://user:password@host[:port]/database, or a libpq string such as 'host=db.internal dbname=hardcharger user=hardcharger'. Every replica names the same database; changes reach all of them within about a second.

A DuckDB file, for one server

Use PostgreSQL in production, and for any cluster of more than one replica. For development, or a server that will only ever run as one replica, the store can be a DuckDB file of its own instead: duckdb: and a path, or any path ending in .duckdb.

HARDCHARGER_ADMIN_PASSWORD='change-me' \
HARDCHARGER_RBAC_STORE_URI='duckdb:/var/lib/hardcharger/rbac.duckdb' \
  hardcharger-server --database analytics.duckdb

• The file and its write-ahead log are created 0600, and each change is checkpointed into the file at once. An existing file that other users of the host can read or write is refused at startup (chmod 600 it), and so is a store file that is also the database, the instrumentation or the log file.
• One server per file: a second server on the same file stops at startup, saying the DuckDB store serves a single server and to use PostgreSQL for several replicas.
• The file holds password hashes and grants, so no SQL reaches it, the administrator’s included, however the path is written or computed (see the server’s own files).
• There is no migration between the two stores: moving to PostgreSQL later means creating the users, roles and grants there again.

With the store in rbac.duckdb, not even the administrator can read it in SQL, and a second server on the same file stops at startup:

$ HARDCHARGER_PASSWORD='change-me' hardcharger --host localhost --tls=false --username hardcharger \
    --command "SELECT count(*) FROM read_blob('rbac.duckdb')"
Error: Permission Error: Permission denied: Hardcharger blocked access to 'rbac.duckdb' (the access-control store). It is one of the server's own files, which no SQL may open, read or write, whatever the role.

LINE 1: SELECT count(*) FROM read_blob('rbac.duckdb')
                             ^^^^^^^^^^^^^^^^^^^^^^^^

$ HARDCHARGER_ADMIN_PASSWORD='change-me' hardcharger-server --port 31400 --health-port 31401 \
    --database other.duckdb --rbac-store-uri duckdb:rbac.duckdb
…
… A DuckDB store serves a single server; for several replicas, use a PostgreSQL store (rbac_store_uri: postgresql://...)

Note

The administrator (--admin-username, hardcharger by default) is a superuser and is never subject to grants. Everyone else should be a store user. A store user’s name can’t be the administrator’s.

Users and roles

The rest of this page runs as the administrator. Create some data, a role, and a user in it:

CREATE SCHEMA analytics;
CREATE TABLE analytics.orders AS
  SELECT * FROM (VALUES (1, 'EU', 120.0), (2, 'US', 80.0)) t(id, region, amount);
CREATE TABLE main.customers AS
  SELECT * FROM (VALUES (1, 'ann@example.com', '1990-01-02'), (2, 'bob@example.com', '1985-07-08')) t(id, email, birthday);

CREATE ROLE analyst;
GRANT SELECT ON SCHEMA analytics TO analyst;               -- every table there, now and later
GRANT SELECT (id, email) ON main.customers TO analyst;     -- two columns only
CREATE USER joe WITH PASSWORD 'joe-secret';
GRANT analyst TO joe;

Access-control statements run one at a time: send each as its own statement (the CLI and the drivers do, one per ;).

Now sign in as joe. What he may read works; what he may not is refused, with the reason:

from adbc_driver_hardcharger import dbapi as hardcharger

with hardcharger.connect(uri="hardcharger://localhost?tls=false", username="joe", password="joe-secret") as conn:
    with conn.cursor() as cur:
        cur.execute(operation="SELECT sum(amount) AS total FROM analytics.orders")
        print(cur.fetchone())
        cur.execute(operation="SELECT id, email FROM main.customers ORDER BY id")
        print(cur.fetchall())
        try:
            cur.execute(operation="SELECT birthday FROM main.customers")
        except hardcharger.ProgrammingError as e:
            print("refused:", e)

(Decimal('200.0'),)
[(1, 'ann@example.com'), (2, 'bob@example.com')]
refused: UNAUTHORIZED: [warpsql] ExecuteQuery: Permission Error: Hardcharger blocked this statement: user joe has no SELECT on column(s) birthday of my.main.customers. Access control on this server lets user joe do only what their roles are granted (see SHOW GRANTS) (permission_denied, HTTP 403, request …). SQLSTATE: 42501

Privileges

Privilege	On
SELECT (optionally on columns: SELECT (a, b))	a table or view, a schema, a catalog
INSERT, UPDATE, DELETE, ALL [PRIVILEGES]	a table, a schema, a catalog
CREATE TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, ALTER TABLE, ALTER VIEW	a schema or catalog
CREATE SCHEMA, DROP SCHEMA	a catalog

CREATE ROLE loader;
GRANT SELECT, INSERT ON TABLE analytics.orders TO loader;
GRANT ALL PRIVILEGES ON SCHEMA analytics TO loader;
GRANT CREATE SCHEMA, SELECT ON CATALOG my TO loader WITH GRANT OPTION;   -- the catalog of my.duckdb
GRANT SELECT ON analytics.orders TO PUBLIC;                               -- every store user
REVOKE GRANT OPTION FOR SELECT ON CATALOG my FROM loader;
REVOKE INSERT ON analytics.orders FROM loader;                            -- CASCADE by default
-- RESTRICT fails if other grants depend on this one:
REVOKE SELECT ON analytics.orders FROM PUBLIC RESTRICT;

WITH GRANT OPTION lets the grantee pass a privilege on; WITH ADMIN OPTION lets a role member grant the role. A revoke cascades to the grants made through it unless you say RESTRICT. A grant on a schema or catalog covers objects created there later.

System privileges

Reaching outside the database needs a system privilege, granted to a role with no ON clause:

System privilege	Opens
READ FILESYSTEM	read_csv, read_parquet, FROM 'file', COPY … FROM, local and remote
WRITE FILESYSTEM	COPY … TO a file or URL
MANAGE CATALOGS	ATTACH, DETACH
MANAGE EXTENSIONS	INSTALL, LOAD, extension repositories
MANAGE SECRETS	CREATE/DROP SECRET, duckdb_secrets()
MANAGE CONFIG	SET GLOBAL, instance-wide settings, reading settings that hold credentials
EXPORT DATABASE, IMPORT DATABASE	those statements
CHECKPOINT	CHECKPOINT, FORCE CHECKPOINT

CREATE ROLE ingest;
GRANT READ FILESYSTEM TO ingest;
GRANT ingest TO joe;

A few things no privilege opens, such as query() with SQL text built at run time; those stay with the administrator.

Ownership and views

The user who creates an object owns it and can grant on it. Move ownership with:

ALTER TABLE analytics.orders OWNER TO ROLE loader;

A view reads with its owner’s rights, so you can publish a filtered or aggregated view of a table without granting the table:

CREATE SCHEMA published;
CREATE VIEW published.revenue_by_region AS
  SELECT region, sum(amount) AS revenue FROM analytics.orders GROUP BY region;
CREATE ROLE viewer;
GRANT SELECT ON published.revenue_by_region TO viewer;
CREATE USER vic WITH PASSWORD 'vic-secret';
GRANT viewer TO vic;

$ HARDCHARGER_PASSWORD='vic-secret' hardcharger --host localhost --tls=false --username vic --csv \
    --command "SELECT * FROM published.revenue_by_region ORDER BY region"
region,revenue
EU,120.0
US,80.0
$ HARDCHARGER_PASSWORD='vic-secret' hardcharger --host localhost --tls=false --username vic --csv \
    --command "SELECT * FROM analytics.orders" > /dev/null 2>&1 || echo "refused"
refused

Creating a view takes more than CREATE VIEW on its schema: the creator must be able to SELECT everything the view’s query reads, each table, view and column, as their own SELECT of the query would. Otherwise a view would let its owner see the shape of a table they hold nothing on.

CREATE ROLE view_maker;
GRANT CREATE VIEW ON SCHEMA published TO view_maker;
GRANT view_maker TO vic;

$ HARDCHARGER_PASSWORD='vic-secret' hardcharger --host localhost --tls=false --username vic --csv \
    --command "CREATE VIEW published.all_orders AS SELECT * FROM analytics.orders"
Error: Permission Error: Hardcharger blocked this statement: user vic may create a view only over what it can SELECT itself, and the query of view my.published.all_orders reads more: user vic has no SELECT privilege on table my.analytics.orders. Access control on this server lets user vic do only what their roles are granted (see SHOW GRANTS)

Once created, a view still reads with its owner’s rights, so one granted to others keeps working, and revoking the owner’s access closes it.

What users can see

Metadata follows privileges, table by table. A user sees a table or view only when they hold a privilege on it (SELECT on it or on any of its columns, INSERT, UPDATE or DELETE; owning it holds them all), a column only when they may read it, and a schema only when they hold a grant on or in it or own something there. That covers the drivers’ metadata calls (ADBC GetObjects, JDBC DatabaseMetaData) and SQL’s own listings: information_schema, duckdb_tables(), duckdb_columns() and the other duckdb_*() functions, SHOW TABLES, SHOW ALL TABLES, pragma_table_info(). DuckDB’s own views (catalog system) and the session’s temporary objects stay listed. vic sees the one view he may read:

$ HARDCHARGER_PASSWORD='vic-secret' hardcharger --host localhost --tls=false --username vic --csv \
    --command "SELECT table_schema, table_name FROM information_schema.tables WHERE table_catalog = 'my'"
table_schema,table_name
published,revenue_by_region

DESCRIBE (and SHOW <table>) of a table the user holds no privilege on is refused, and so is DESCRIBE of a table they may read only some columns of, since it would show them all; pragma_table_info() lists the columns they may read:

$ HARDCHARGER_PASSWORD='vic-secret' hardcharger --host localhost --tls=false --username vic --csv \
    --command "DESCRIBE analytics.orders"
Error: Hardcharger blocked this statement: user vic holds no privilege on table my.analytics.orders, so it cannot describe it
$ HARDCHARGER_PASSWORD='joe-secret' hardcharger --host localhost --tls=false --username joe --csv \
    --command "DESCRIBE main.customers"
Error: Hardcharger blocked this statement: user joe may read only some columns of table my.main.customers, and DESCRIBE shows them all; pragma_table_info('my.main.customers') lists the ones it may read

$ HARDCHARGER_PASSWORD='joe-secret' hardcharger --host localhost --tls=false --username joe --csv \
    --command "SELECT name FROM pragma_table_info('main.customers')"
name
id
email

A grant or revoke applies to the next listing, prepared ones included. Scoped tokens, SET ROLE, tenants and catalog access rules narrow what is listed the same way they narrow what can be read.

Seeing grants

$ HARDCHARGER_PASSWORD='change-me' hardcharger --host localhost --tls=false --username hardcharger --csv \
    --command "SHOW GRANTS FOR ROLE analyst"
privilege,object_type,object,columns,granted_to,grantee_type,grant_option,granted_by
…

SHOW GRANTS alone shows your own. Its columns are privilege, object_type, object, columns, granted_to, grantee_type, grant_option, granted_by. Store users may look at themselves and the roles they hold.

The same, as tables to query: hardcharger_grants(), hardcharger_role_members(), hardcharger_roles(), hardcharger_users(), hardcharger_policies() and hardcharger_effective_privileges([user_name]) (SQL reference). hardcharger_effective_privileges() lists what a user may do and through which role:

$ HARDCHARGER_PASSWORD='joe-secret' hardcharger --host localhost --tls=false --username joe --csv \
    --command "SELECT privilege, object_type, schema_name, object_name, column_name, via FROM hardcharger_effective_privileges() ORDER BY ALL"
privilege,object_type,schema_name,object_name,column_name,via
READ FILESYSTEM,SYSTEM,NULL,NULL,NULL,ingest
SELECT,SCHEMA,analytics,NULL,NULL,analyst
SELECT,TABLE,main,customers,email,analyst
SELECT,TABLE,main,customers,id,analyst

They read the server’s copy of the store, so they cost no store query. The administrator sees everything; a store user sees its own grants, memberships, roles and user row, and the policies on tables it can see; a tenant admin also its own tenant’s. The SQL standard’s views (table_privileges, column_privileges, role_table_grants, applicable_roles, enabled_roles) are in _hardcharger_system.hardcharger_information_schema, with the standard’s columns.

Managing users

CREATE USER ann WITH PASSWORD 'ann-secret';
ALTER USER joe WITH PASSWORD 'n3w-secret';   -- ends joe's tokens and sessions at once
ALTER USER joe DISABLE;                      -- and ENABLE
ALTER USER joe PASSWORD NULL;                -- token sign-in only (SSO, scoped tokens)
ALTER USER ann SET ATTRIBUTE region = 'EU';  -- for row policies: session_attribute('region')
DROP USER IF EXISTS ann;

Dropping or disabling a user, changing their password, or revoking a role takes effect on every replica within about a second, including in sessions that are open: a revoke can stop a statement that is running. The user’s tokens stop working at once (401 invalid_token, with a reason that says why), and once they sign in again, their old sessions answer 401 session_ended with a reason: principal_dropped (dropped or disabled) or credentials_changed (a new password, or anything else access control revoked).

Users who sign in with a certificate

With certificate sign-in on, a client certificate signs in as the store user its identity names (by default its e-mail SAN; ASCII case doesn’t matter, except in URI identities such as SPIFFE IDs). Such a user needs no password, and roles decide what it may do, as for any user. The administrator signs in with a password here over a plain client certificate, since this server requires one on every connection:

$ HARDCHARGER_PASSWORD='change-me' hardcharger --host localhost --username hardcharger --tls --tls-roots ca.crt \
    --mtls-cert client.crt --mtls-key client.key --csv --command 'CREATE ROLE sales_reader;
      CREATE TABLE sales AS SELECT 42 AS amount;
      GRANT SELECT ON sales TO sales_reader;
      CREATE USER "joe@example.com";
      GRANT sales_reader TO "joe@example.com"'
result
Role sales_reader created
OK, 1 row affected
result
Granted SELECT on my.main.sales to sales_reader
result
User joe@example.com created
result
Granted sales_reader to joe@example.com

joe.crt carries email:joe@example.com. Joe signs in with it alone:

$ hardcharger --host localhost --tls --tls-roots ca.crt --auth-type mtls \
    --mtls-cert joe.crt --mtls-key joe.key --csv --command "SELECT HARDCHARGER_USER() AS who, amount FROM sales"
who,amount
joe@example.com,42

A certificate whose identity is no user (or a disabled one) is refused with unknown_certificate_identity.

Active roles

A user’s roles are all active by default. SET ROLE analyst narrows a session to one role, SET ROLE ALL restores them, SET ROLE NONE drops to PUBLIC’s grants. Row policies can test roles with is_role_active('hr').

Next

• Row policies and masking: who sees which rows and values.
• SSO: sign store users in through your identity provider, with group mappings.
• Tenants and scoped tokens: isolation for SaaS and short-lived, narrowed tokens.
• The audit log: every decision, in a tamper-evident chain.