Management SQL reference

The policy of the extension - virtual catalogs, the objects in them, roles, issuers and grants - is administered with a small SQL grammar. Every statement of it compiles, with no side effect at parse time, into a call of one acl_* administration function (SELECT acl_<fn>(<constants>)), which then runs through the normal bind -> execute path. The grammar and the functions are therefore the same operation; use whichever a script is easier to write in. Both are listed here, family by family.

Where management SQL runs

A statement is administered from behind one of the ACL prefixes:

Written as	Who writes it	What it needs
ACL ADMIN <management statement> (or, marker explicit, ACL ADMIN ACL …)	the gateway, anonymously	in the in-memory dev store: always allowed; once a policy source is enabled (acl_use_db / acl_use_functions): SET GLOBAL acl_allow_anonymous_admin = true
ACL ADMIN <plain sql> / ACL ADMIN ACL NATIVE <sql>	the gateway	native SQL outside the virtual catalog, not rewritten; as above
ACL ROLE "r" [ACL] <management statement>	a principal	a bundle that administers (policy, passthrough), a catalog's manage, or a point grant on the function (see Batches and authorization) - the ACL marker is optional (spec 117)
ACL TOKEN '<jwt>' [ACL] <management statement>	a principal	the same; ACL SESSION '<handle>' … is the door's equivalent
ACL ROLE "r" ACL NATIVE <sql>	a principal	passthrough only

ACL ADMIN is the native context: a statement after it that is not a management form (CREATE TABLE, INSERT, SELECT, duckdb's own ALTER TABLE or COMMENT ON TABLE) is plain SQL. The management forms are recognized by their first words - ADD, GRANT, REVOKE, MAP, CREATE [OR REPLACE] VIRTUAL|ROLE|ISSUER|CLIENT, ALTER VIRTUAL|ROLE|ISSUER|CLIENT|GRANT, DROP VIRTUAL|RELATION| REFERENCE|ROLE|ISSUER|CLIENT|MAP, COMMENT ON VIRTUAL, ANALYZE VIRTUAL, CHECK VIRTUAL, REPAIR VIRTUAL - and a typo after one of those is an error, never a fallthrough into native SQL.

The acl_* functions themselves can only be called in the native context (ACL ADMIN SELECT acl_…(…), ACL … ACL NATIVE SELECT acl_…(…), or a plain connection). A principal's own query may not name any of them.

Under a principal prefix the marker is optional (spec 117): ACL ROLE "r" GRANT CATALOG sales TO ROLE analyst WITH (select) is the management statement, recognized by the same leading phrases; everything else stays an ordinary query rewritten in the virtual catalog (CREATE TABLE, DROP TABLE, duckdb's COMMENT ON TABLE, ANALYZE t, CREATE FUNCTION are answered as before). A principal reaches every management operation also as a function of the system catalog, SELECT platform.<op>(…) at the top level of a statement, judged by the same authorizer, and reads the policy and the node through platform's views - see the platform catalog.

Writing policy needs a catalog policy source enabled with acl_use_db. The function driver (acl_use_functions) is read-only and refuses every write; without any source the store is in-memory, where CREATE ROLE, acl_define_token and the legacy wrappers work and the virtual catalog forms refuse.

Notation

• <catalog>.<name> - a virtual name. The first component is the virtual catalog, the rest the path inside it: sales.raw.orders is object raw.orders of catalog sales. Each part is an identifier as SQL writes one (spec 116): a bare word (A-Z a-z 0-9 _) or a double-quoted identifier with "" for a quote - any case, spaces, a dot inside: "Sales Mart"."Order Items", c."Raw Data".sub."T", "a.b" (one schema, never the path a.b). A double-quoted name is always ONE identifier.
• <phys> - a physical path such as phys.main.orders or pg."Raw Data"."Order Items", written like a virtual name, or as the legacy single-quoted string holding the whole path ('pg."Raw Data".orders').
• Names compare case-insensitively, as duckdb's catalog does: "Upper" is read as upper and UPPER, and a write naming an existing record lands on it, in the case the policy stores. A new catalog, schema, object, function or reference that differs from an existing name of its catalog only by case is refused (… differs only by case from the existing …); IF NOT EXISTS keeps the one there.
• <role>, <catalog> - one identifier each (a bare word or double-quoted). A role, a group, an issuer or a client is a value and compares exactly; an issuer or a client may also be a single-quoted string (an issuer named by its URL).
• The acl_* functions take a name argument as its key: the parts joined by ., a part that holds a . or a " in double quotes (acl_add_relation('Sales Mart', 'Raw Data.Order Items', …), acl_create_catalog('"x.y"')). Listings answer each name unquoted (table_schema = Raw Data); a nested schema answers its path, and a schema part with a dot is shown quoted ("a.b", a."b.c") so it never reads as two levels. Lineage names a dataset by the same key rules (Sales Mart.main.Order Items, c."a.b".Sink).
• Quoted values take '…' or "…"; a doubled quote is a literal one.
• A body (after AS or MACRO) is either a quoted string or written inline to the end of the statement; inline text is read quote- and parenthesis-aware, so a ; inside a literal or a parenthesised list does not end it.
• A list is written ( … ) or as the legacy quoted csv/JSON string; both are accepted wherever a list is taken. WITH (select, insert) and CAPS '{"select": true, "insert": true}' store the same.
• Keywords are case-insensitive. Statements of a batch are separated by ;.
• Write modes on CREATE: CREATE refuses an existing object, CREATE OR REPLACE overwrites, CREATE … IF NOT EXISTS keeps what is there. The legacy ADD forms always upsert. Where a function takes a mode argument the values are create, replace, skip and upsert (or omitted, which is upsert). DROP … IF EXISTS makes a missing target silent; ALTER has no IF EXISTS - a missing target is an error.

Choosing the policy source

There is no SQL form; these are called directly.

SELECT acl_use_db('aclcat');                 -- read policy from the ATTACHed database 'aclcat', schema 'acl'
SELECT acl_use_db('aclcat', 'acl', true);    -- init := true creates/migrates the managed schema first
SELECT acl_use_functions('{"policy_version": "pol_version", "role_catalogs": "pol_role_catalogs",
  "relations": "pol_relations", "relation_columns": "pol_columns", "schema_aliases": "pol_aliases",
  "functions": "pol_functions", "role_claims": "pol_claims",
  "function_categories": "pol_fcats", "function_category_members": "pol_fmembers", "function_grants": "pol_fgrants"}');

• acl_use_db(db[, schema[, init]]) - schema defaults to acl; init defaults to false. A catalog whose schema_version does not match the build is refused by name.
• acl_use_functions(slot_map_json) - the six slots policy_version, role_catalogs, relations, relation_columns, schema_aliases, functions are required; every named function must be a registered table function. The two sources are exclusive: the last call wins. The three function category slots (spec 072) - function_categories(), function_category_members(), function_grants(), listings with no arguments and the columns the tables have - are declared together or not at all; without them the shipped seed decides what a principal may call.
• Both refuse while allow_parser_override_extension is DEFAULT, because nothing would be enforced.

Virtual catalogs

CREATE [OR REPLACE] VIRTUAL CATALOG [IF NOT EXISTS] <catalog> [COMMENT '<text>']
ALTER VIRTUAL CATALOG <catalog> SET COMMENT '<text>'
DROP VIRTUAL CATALOG [IF EXISTS] <catalog> [CASCADE]

A virtual catalog is a shared tree of virtual names with their definitions; roles are granted catalogs, never objects of their own. Dropping one always removes its definitions; grants held on it by roles are removed only with CASCADE, otherwise the drop fails and names the roles.

ACL ADMIN CREATE VIRTUAL CATALOG sales COMMENT 'orders and customers';
ACL ADMIN DROP VIRTUAL CATALOG sales CASCADE;

Functions: acl_create_catalog(vcat[, comment[, mode]]), acl_alter_catalog(vcat, comment), acl_drop_catalog(vcat[, cascade[, mode]]).

Virtual tables

CREATE [OR REPLACE] VIRTUAL TABLE [IF NOT EXISTS] <catalog>.<name> AS <phys>
    [COLUMNS (<item>, …)] [RLS (<predicate>)] [PRIMARY KEY (<column>, …)] [COMMENT '<text>']
ADD TABLE <phys> AS <catalog>.<name> [COLUMNS (<item>, …)] [RLS (<predicate>)]        -- legacy, upsert

The four clauses after AS <phys> come in any order. A COLUMNS item is one of:

Item	Meaning
name	expose the physical column
new_name = column	rename
name = <expression>	a mask (ssn = NULL) or a computed column (total = amount * 2), evaluated over the physical row
name NOT NULL / name NULL	expose and declare nullability (spec 048); name = <expr> NOT NULL is the promise that lets a masked column be a key
"odd name"	a quoted identifier means the name it quotes (spec 065); a quoted name containing , or = is refused

Any column not listed is hidden. With no COLUMNS and no RLS the relation is an alias (RENAME in place, writable); a list made only of bare names and renames keeps the alias form; a mask, a computed column or an RLS predicate makes it a read-only subquery. RLS is a predicate over the physical columns, AND-ed into every read and write; acl_claim('<name>') inside it is replaced by the principal's claim. PRIMARY KEY is declared, never enforced; it may name only columns the declaration has, and a masked or explicitly nullable column is refused as a key. The physical <phys> is not checked at write time: a source attached later is fine - except for a catalog admin, whose <phys> must lie in a source granted to it (spec 118.3), which a grant requires to be attached. A two-part <phys> over an attached database (pg.orders) is stored whole, as duckdb finds the object there: pg.public.orders (MySQL: its database; SQL Server: the ATTACH's default_schema, else dbo). One that also names a schema of the node's default database is refused as ambiguous - write it whole; one that names only such a schema is kept as written, since the default database is that node's own (spec 137).

ACL ADMIN CREATE VIRTUAL TABLE sales.created AS phys.main.orders_physical
    COLUMNS (id, amount) RLS (amount > 0) COMMENT 'positive orders';
ACL ADMIN CREATE VIRTUAL TABLE c.orders AS phys.main.orders
    COLUMNS (id = pk, tenant = internal_tenant, "odd name" = "odd name");
ACL ADMIN CREATE VIRTUAL TABLE c.masked AS phys.main.orders
    COLUMNS (id = CASE WHEN id > 0 THEN id END NOT NULL, tenant) PRIMARY KEY (id);
ACL ADMIN ADD TABLE phys.main.orders_physical AS sales.orders COLUMNS (id, amount, ssn = NULL) RLS (tenant = acl_claim('tenant'));

Function: acl_add_relation(vcat, vname, phys, columns_csv, rls[, comment[, mode[, pk_csv]]]) - columns_csv is the item list as csv (id, ssn = NULL), pk_csv the key columns.

Related: ALTER VIRTUAL TABLE, DROP VIRTUAL TABLE / DROP RELATION, COMMENT ON VIRTUAL TABLE below.

Virtual views

CREATE [OR REPLACE] VIRTUAL VIEW [IF NOT EXISTS] <catalog>.<name> [(<column> <TYPE> [NOT NULL | NULL], …)]
    [PRIMARY KEY (<column>, …)] [COMMENT '<text>'] AS <select>
ADD VIEW <catalog>.<name> [(<column> <TYPE>, …)] AS <select>                             -- legacy, upsert

A view is a full SELECT in physical names, always read-only and always wrapped as a subquery. acl_claim('<name>') in the body is replaced per principal. A declared column list is stored as the view's shape instead of probing the body at write time; PRIMARY KEY and COMMENT come before AS, in either order, because the body runs to the end of the statement.

ACL ADMIN CREATE VIRTUAL VIEW sales.mine AS SELECT id, amount FROM phys.main.orders_physical WHERE tenant = acl_claim('tenant');
ACL ADMIN CREATE VIRTUAL VIEW c.stats (day VARCHAR NOT NULL, total INTEGER) PRIMARY KEY (day)
    AS 'SELECT tenant AS day, sum(amount)::INTEGER AS total FROM phys.main.orders GROUP BY 1';
ACL ADMIN CREATE VIRTUAL VIEW sales.created_view (n BIGINT) COMMENT 'row count' AS SELECT count(*) AS n FROM phys.main.orders_physical;

Function: acl_add_view(vcat, vname, select_sql[, returns[, comment[, mode[, pk_csv]]]]) - returns is the declared column list (day VARCHAR NOT NULL, total INTEGER).

Column types (spec 099)

How a column's type is exposed - in the rows and in every listing alike - is the node's setting, overridden per object:

	values	node setting (SET GLOBAL, or the cluster profile)	default
an extension's alias type (MSSQL_VARCHAR(n))	base (its base type) / keep	acl_alias_types	base
an ENUM, at any depth of a struct, list, map, array or union	varchar / keep	acl_enum_types	keep

ACL ADMIN ALTER VIRTUAL TABLE c.accounts SET TYPES (enums = varchar);
ACL ADMIN ALTER VIRTUAL TABLE c.legacy SET TYPES (aliases = keep, enums = default);  -- default = the node's

A key not written keeps its value; an operator's redefinition (ALTER … SET RLS, CREATE OR REPLACE VIRTUAL …) keeps both - a principal's own CREATE registers a fresh record, as it does its comment. The cast sits above the row filter: predicates read the physical values, the projection and masks the exposed ones. The types are probed where the object is written and stored (relation_types); SET TYPES and ANALYZE VIRTUAL TABLE probe them again. base is what lets a quack client without the source's extension attach the catalog at all; keep is for clients that have it. varchar hides an ENUM's labels from readers (see the security model).

Virtual schemas

CREATE [OR REPLACE] VIRTUAL SCHEMA [IF NOT EXISTS] <catalog>.<path> AS <phys schema> [COMMENT '<text>']    -- live alias
CREATE [OR REPLACE] VIRTUAL SCHEMA [IF NOT EXISTS] <catalog>.<path> FROM <phys schema> [COMMENT '<text>']  -- expansion
ADD SCHEMA <phys schema> AS <catalog>.<path>                                                            -- legacy alias, upsert
ALTER VIRTUAL SCHEMA <catalog>.<path> SET PHYS <phys schema>
ALTER VIRTUAL SCHEMA <catalog>.<path> REFRESH [PRUNE]
DROP VIRTUAL SCHEMA [IF EXISTS] <catalog>.<path> [CASCADE]

• AS <phys> records a live alias: any name under the prefix resolves through it in place, a table added physically is visible at once, and nothing under it can be excluded.
• FROM <phys> records an expansion: the physical schema is read once, at write time, and one alias-form relation record is written per object it holds. A table created physically later is invisible until REFRESH. Records may then be edited or dropped individually; a record an admin changed with ALTER stays part of the expansion, one rewritten with CREATE OR REPLACE leaves it.
• REFRESH adds records for objects that appeared and never rewrites an existing one; records dropped on purpose are not re-added. PRUNE also removes records whose physical object is gone, but only the ones the expansion itself produced. The call returns how many records changed. REFRESH on a live alias is an error.
• SET PHYS retargets an alias. A path may nest (sales.raw.eu).
• An expansion's records are relations of the catalog in their own right, so the schema is dropped with them only under CASCADE; without it the drop fails and says how many would be orphaned.

ACL ADMIN CREATE VIRTUAL SCHEMA sales.raw AS phys.main COMMENT 'everything in main, live';
ACL ADMIN CREATE VIRTUAL SCHEMA sales.curated FROM phys.main;
ACL ADMIN ALTER VIRTUAL SCHEMA sales.curated REFRESH PRUNE;
ACL ADMIN DROP VIRTUAL SCHEMA sales.curated CASCADE;

Functions: acl_add_schema_alias(vcat, path, phys[, comment[, mode]]), acl_expand_schema(vcat, path, phys[, comment[, mode]]), acl_alter_schema_alias(vcat, path, phys), acl_refresh_schema_objects(vcat, path[, prune]) (returns BIGINT), acl_drop_schema_alias(vcat, path[, mode[, cascade]]).

Virtual functions

CREATE [OR REPLACE] VIRTUAL TABLE FUNCTION [IF NOT EXISTS] <catalog>.<name>[(<param> <TYPE>, …)]
    [RETURNS [TABLE] (<column> <TYPE> [NOT NULL | NULL], …)] [PRIMARY KEY (<column>, …)] [COMMENT '<text>']
    AS <select template>
  | ALIAS OF <physical function> [COMMENT '<text>']

CREATE [OR REPLACE] VIRTUAL SCALAR [IF NOT EXISTS] <catalog>.<name>[(<param> <TYPE>, …)]
    [RETURNS <TYPE>] [COMMENT '<text>']
    AS <expression template>
  | ALIAS OF <physical function> [COMMENT '<text>']

ADD TABLE FUNCTION <catalog>.<name>[(<param> <TYPE>, …)] [RETURNS [TABLE] (<column> <TYPE>, …)]
    MACRO <select template> | ALIAS [OF] <physical function>                              -- legacy, upsert
ADD SCALAR <catalog>.<name>[(<param> <TYPE>, …)] [RETURNS <TYPE>]
    MACRO <expression template> | ALIAS [OF] <physical function>                          -- legacy, upsert

Two forms per kind. A macro (AS / MACRO) is a template in physical names: acl_arg(n) stands for the caller's n-th argument, acl_claim('<name>') for a claim; a table-function macro expands as a read-only subquery, a scalar macro as an expression. An alias (ALIAS OF) retargets the call to a physical function in place. The parameter list types the probe that derives the result schema; a declared RETURNS replaces the probe. PRIMARY KEY is accepted on a table function only, after RETURNS, and describes the result; a scalar cannot carry one. The clause order is as written above: parameters, RETURNS, PRIMARY KEY, COMMENT, then the body or ALIAS OF. A table function and a scalar of the same name are different objects.

ACL ADMIN CREATE VIRTUAL TABLE FUNCTION sales.created_fn(threshold INTEGER) RETURNS TABLE (id INTEGER) COMMENT 'big orders'
    AS SELECT id FROM phys.main.orders_physical WHERE amount >= acl_arg(1);
ACL ADMIN CREATE VIRTUAL TABLE FUNCTION c.perday(m INTEGER)
    RETURNS (id INTEGER NOT NULL, amount INTEGER NOT NULL) PRIMARY KEY (id)
    AS 'SELECT id, amount FROM phys.main.orders WHERE amount >= acl_arg(1)';
ACL ADMIN CREATE VIRTUAL TABLE FUNCTION sales.created_rng ALIAS OF range;
ACL ADMIN CREATE VIRTUAL SCALAR sales.created_scalar(text VARCHAR) RETURNS VARCHAR AS upper(acl_arg(1));
ACL ADMIN ADD SCALAR sales.tag MACRO acl_arg(1) || '@' || acl_claim('tenant');

Functions: acl_add_table_function(vcat, vname, sql_template[, params, returns[, comment[, mode[, pk_csv]]]]), acl_add_table_function_alias(vcat, vname, target[, '', '', comment[, mode]]), acl_add_scalar(vcat, vname, expr_template[, params, returns[, comment[, mode]]]), acl_add_scalar_alias(vcat, vname, target[, '', '', comment[, mode]]). params is the parameter list text (threshold INTEGER), returns the result declaration (id INTEGER, amount INTEGER for a table function, VARCHAR for a scalar); the two empty strings of the alias forms are the unused parameter and result slots.

References

CREATE [OR REPLACE] VIRTUAL REFERENCE [IF NOT EXISTS] <catalog>.<name>
    FROM <object> TO <object> ON (<from column> = <to column>, …) | ON EXPRESSION '<sql>'
    [CARDINALITY many_to_one | one_to_many | one_to_one | many_to_many] [OPTIONAL]
    [JOIN asof | positional] [COMMENT '<text>']

CREATE [OR REPLACE] VIRTUAL REFERENCE [IF NOT EXISTS] <catalog>.<name>
    FROM <object> TO FUNCTION <table function>[(<param> => <from column>, …)]
    [ON (<from column> = <result column>, …) | ON EXPRESSION '<sql>'] [CARDINALITY …] [OPTIONAL] [JOIN …] [COMMENT '<text>']

DROP [VIRTUAL] REFERENCE [IF EXISTS] <catalog>.<name>

A reference is a declared join path between two objects of one virtual catalog - a hint an agent reads through acl_references([object]). It is never enforced and grants nothing; a principal sees it only when both ends and every column it names are visible. Its to_has_key column says whether the far end declares a key whose every column the principal sees (what a tool's foreign-key listing names). acl_references() / acl_keys() asked about an object - their argument, or WHERE vcat = … AND object | from_object | to_object = … - read only the sources behind it (and, for a reference, its other end), not every source of the node (spec 121). Ends are virtual names, written with or without the reference's own catalog in front (FROM orders and FROM c.orders are the same in c.…). ON (…) lists column pairs; ON EXPRESSION '…' is arbitrary SQL in which every column must be qualified by its end. Exactly one of the two is written for a relation end. For a TO FUNCTION end the parenthesis is the argument substitution - which column of the source row feeds which parameter, checked against the declared signature - and ON names columns of the function's result; either may stand alone. CARDINALITY and JOIN accept only the values listed. OPTIONAL says the far side may be absent. The trailing clauses come in any order.

Its hints change in place (spec 120) - ALTER VIRTUAL REFERENCE c.n SET COMMENT '…' | SET CARDINALITY <c> | SET OPTIONAL true | false, COMMENT ON VIRTUAL REFERENCE c.n IS '…' (acl_alter_reference(catalog, name, property, value)); its ends and condition only by a re-create. A mapping (MAP …) has no ALTER: it is only its key, so DROP MAP + MAP is the change.

ACL ADMIN CREATE VIRTUAL REFERENCE c.orders_customer FROM c.orders TO c.customers
    ON (customer_id = id) CARDINALITY many_to_one COMMENT 'the ordering customer';
ACL ADMIN CREATE VIRTUAL REFERENCE c.order_rate FROM orders TO rates
    ON EXPRESSION 'orders.amount >= rates.rate' CARDINALITY many_to_one OPTIONAL JOIN asof;
ACL ADMIN CREATE VIRTUAL REFERENCE c.cust_orders FROM customers TO FUNCTION orders_of(cust => id)
    CARDINALITY one_to_many COMMENT 'the orders of this customer';
ACL ADMIN DROP VIRTUAL REFERENCE IF EXISTS c.by_secret;

Functions: acl_add_reference(vcat, name, from, to[, to_kind, args, pairs, expr, cardinality, optional, join_method, comment, mode]) with to_kind relation (default) or function, args the substitution text (cust => id), pairs the pair list (customer_id = id), optional 'true'/'false'; acl_drop_reference(vcat, name[, mode]).

Roles

CREATE [OR REPLACE] ROLE [IF NOT EXISTS] <role> [CLAIMS (<name> = '<value>', …) | CLAIMS '<name>=<value>,…']
ALTER ROLE <role> SET CLAIMS (<name> = '<value>', …) | '<name>=<value>,…'
ALTER ROLE <role> SET COMMENT '<text>'
DROP ROLE [IF EXISTS] <role>

A value in the list form is one quoted literal and may hold ,, = or ' (doubled); in the quoted csv form a value with a comma is written in quotes of its own: CLAIMS 'tenant=''a,b'''.

A role is an internal name that grants attach to. Its claims are defaults: they are what acl_claim('<name>') answers under the bare ACL ROLE "r" form, and under a token they fill in only what the token's claim map did not supply (explicit token claims win). SET COMMENT (spec 120) is what a console shows of the role (acl_comment_role(role, comment)); a later SET CLAIMS keeps it. ALTER … SET CLAIMS replaces the whole list. Dropping a role takes its grants, object capabilities, admin scope and mappings with it.

ACL ADMIN CREATE ROLE analyst CLAIMS (tenant = 'acme');
ACL ADMIN CREATE ROLE IF NOT EXISTS analyst;
ACL ADMIN ALTER ROLE analyst SET CLAIMS 'tenant=globex';
ACL ADMIN DROP ROLE IF EXISTS nobody;

Functions: acl_define_role(role, claims_csv[, mode]), acl_alter_role(role, claims_csv), acl_drop_role(role[, mode]).

Issuers and clients

CREATE [OR REPLACE] ISSUER [IF NOT EXISTS] <name | '<url>'> [URL '<url>'] [FROM SECRET <s> [IN <service>]]
    [<client clause> …] [CLIENT FROM SECRET <s> [IN <service>]]
ALTER ISSUER <name> SET [URL '<url>'] [FROM SECRET <s> [IN <service>]] [<client clause> …]
ALTER ISSUER <name> DROP FROM SECRET | DROP CLIENT FROM SECRET
DROP ISSUER [IF EXISTS] <name>

CREATE [OR REPLACE] CLIENT [IF NOT EXISTS] <name> ISSUER <issuer> <client clause> … [FROM SECRET <s> [IN <service>]]
ALTER CLIENT <name> SET <client clause> … | DROP FROM SECRET
DROP CLIENT [IF EXISTS] <name>

<client clause> := AUDIENCES ('<aud>', …) | AZP ('<azp>', …)
    | REQUIRE (<path> = '<v>' | <path> IN ('<v>', …) | '<v>' IN <path> | <path> LIKE '<p>', …)
    | ROLES FROM ('<path>', …) | ROLES CONSTANT ('<role>', …) | ROLE CLAIM '<path>'
    | UNMAPPED IGNORE | UNMAPPED AS ROLE
    | ATTRIBUTES (<name> = '<path>' | <name> = ('<path>', …) | <name> = CONSTANT '<v>', …)
    | CLAIM MAP (<jwt path> => <name>, …) | CLAIM MAP '<json>'
    | SUBJECT '<path>' | SUBJECT ('<path>', …) | TOKEN TYPE '<typ>'
    | CLIENT ID '<id>' | FLOWS (password | authcode | device | client_credentials, …)

An issuer is the trust anchor of one IdP (spec 095); a client is which of its tokens count and what they become. The model, the routing and the role rules are in authentication.md.

• The short form. A quoted URL in place of a name, with client clauses, is one issuer and its implicit client of the same name - UNMAPPED AS ROLE, flows authcode, device for a public client id (password when its CLIENT FROM SECRET carries a CLIENT_SECRET), set when it is created. The door's password flow is otherwise written explicitly, and only one client of a node may run it. ALTER ISSUER changes the implicit client too; ALTER CLIENT / DROP CLIENT refuse it.
• No key and no credential here. KEYS, ALGS and CLIENT SECRET are refused and name where they go: an oidc_issuer (URL, KEYS, KEYS_FROM, ALGS) or oidc_client (AUDIENCES, CLIENT_ID, CLIENT_SECRET) secret of the node's secrets service, named by FROM SECRET. Without them the keys come from the issuer's OIDC discovery.
• Write checks: one URL per issuer; every client has audiences (on it or in its secret); a parameter is on the object or in its secret, never both; two clients of one issuer that would accept the same tokens are refused (judged at use when REQUIRE is involved); FLOWS needs a client id; a role with an administration scope is never a ROLES CONSTANT nor mapped for every client of an issuer. A named secret must exist in the service, with the right type.
• CREATE refuses an existing issuer or client, OR REPLACE overwrites it, IF NOT EXISTS keeps it (spec 013). DROP ISSUER and CREATE OR REPLACE ISSUER take its implicit client and every mapping scoped to either (DROP is refused while an explicit client names the issuer); DROP CLIENT takes its mappings, and so does a CREATE OR REPLACE CLIENT that moves it to another issuer.
• A write judges what it changes: a client or mapping it did not touch is not refused again (a role made an administrator after it was mapped issuer-wide stops counting at use, it does not block the next write).

ACL ADMIN CREATE ISSUER 'https://kc/realms/x' AUDIENCES ('account') ROLE CLAIM 'realm_access.roles'
    CLAIM MAP (tid => tenant) CLIENT ID 'acl-cli';
ACL ADMIN CREATE ISSUER kc URL 'https://kc/realms/y';
ACL ADMIN CREATE CLIENT desktop ISSUER kc AUDIENCES ('account') AZP ('acl-desktop')
    ROLES FROM ('realm_access.roles') ATTRIBUTES (tenant = 'tenant') CLIENT ID 'acl-desktop' FLOWS (authcode, device);
ACL ADMIN CREATE CLIENT door ISSUER kc AUDIENCES ('account') AZP ('acl-door') ROLES FROM ('roles')
    FLOWS (password) FROM SECRET kc_door IN corp;
ACL ADMIN ALTER CLIENT desktop SET UNMAPPED AS ROLE;
ACL ADMIN DROP CLIENT door;

Functions: acl_define_issuer(name, spec_json[, mode]), acl_alter_issuer(name, spec_json), acl_drop_issuer(name[, mode]), acl_define_client(name, issuer, spec_json[, mode]), acl_alter_client(name, spec_json), acl_drop_client(name[, mode]) - the specs are the JSON the SQL forms compile to (authentication.md). Listings: acl_issuers(), acl_clients().

Every location the node reads keys from - the discovery document, its JWKS, a secret's KEYS_FROM - must start with a prefix in acl_jwks_locations (GLOBAL, default https://; .. is refused anywhere), judged where it is read (specs 071, 095).

Role mappings

MAP GROUP | CLAIM '<value>' FROM CLIENT <client> | ISSUER <issuer> TO ROLE <role>
DROP MAP GROUP | CLAIM '<value>' FROM CLIENT <client> | ISSUER <issuer> TO ROLE <role>

A mapping turns a value found at a client's ROLES FROM paths into a role: GROUP for a group identifier (an EntraID GUID), CLAIM for a plain claim value - scoped to one client, or to every client of one issuer. One value may map to several roles; a token's roles are the union. A role that holds an administration scope is mapped FROM CLIENT only.

ACL ADMIN MAP GROUP 'g-0001' FROM CLIENT desktop TO ROLE analyst;
ACL ADMIN MAP CLAIM 'ops' FROM ISSUER kc TO ROLE operator;
ACL ADMIN DROP MAP GROUP 'g-0001' FROM CLIENT desktop TO ROLE analyst;

Functions: acl_map_role(scope_kind, scope, source, external_value, role) and acl_drop_role_mapping(scope_kind, scope, source, external_value, role); scope_kind = client | issuer, source = group | claim-value. Listing: acl_role_mappings().

Catalog grants

GRANT CATALOG <catalog> TO ROLE <role>
    [WITH (<capability>, …) | CAPS '<json>'] [MAIN] [RLS (<predicate>)] [COLUMNS (<item>, …)]
ALTER GRANT CATALOG <catalog> TO ROLE <role>
    SET CAPS '<json>' | SET RLS '<predicate>' | SET COLUMNS '<list>' | SET MAIN true | false
REVOKE CATALOG <catalog> FROM ROLE <role>
REVOKE TABLE | VIEW | OBJECT <catalog>.<name> FROM ROLE <role>

The grant that makes a catalog resolve for a role. REVOKE TABLE | VIEW | OBJECT (spec 120) takes one object's own grant (GRANT TABLE … CAPS …) - its caps, policy and columns; the catalog's or a schema's grant then decides that object again (acl_revoke_object(role, catalog, name)). Policy-level, like every grant. Clauses after the role come in any order.

• Capabilities. WITH (…) is the list form of CAPS '{"select": true, …}'. A grant that states nothing holds every data capability - select, insert, update, delete, merge - and never manage; CAPS '{}' holds none. The capabilities outside that default are explicit-only and never implied: manage (administer this catalog, see below), create/drop (reserved - a principal never creates a schema, spec 113), temp (session temp tables on the Flight door), explain (EXPLAIN) and secrets (the node's secrets service, see below) on the main catalog grant, and lineage (the catalog's definition lineage, acl_lineage_definitions - lineage) on any catalog grant, each held only when named. An unknown name is stored as written and enforces nothing.
• MAIN marks the catalog whose objects the role addresses unqualified. A principal with more than one main catalog across its roles resolves only qualified names.
• RLS is the grant's own predicate, AND-ed onto every object of the catalog for this role (and onto the object's own). COLUMNS is the grant's own column list, intersected with the object's; a name the object hides cannot be re-exposed here. A grant narrows, it never widens. Across roles the effective policy is the union, so a role without a narrowing grant lifts it for a principal holding both. A catalog-level column list is not probed for its types.
• CAPS '{"manage": true}' is the catalog-scoped administration scope: the role may run the management grammar over this catalog's content (and only that - see Batches and authorization). Managing a catalog does not imply reading it.
• ALTER GRANT changes one property and keeps the rest; its values are always quoted strings, and MAIN takes true or false only.

ACL ADMIN GRANT CATALOG sales TO ROLE analyst WITH (select, insert) MAIN;
ACL ADMIN GRANT CATALOG sales TO ROLE compliance CAPS '{"select": true}' MAIN RLS 'tenant = acl_claim(''tenant'')';
ACL ADMIN GRANT CATALOG c TO ROLE fnarrow WITH (select, explain) MAIN COLUMNS (amount, tenant);
ACL ADMIN ALTER GRANT CATALOG sales TO ROLE analyst SET CAPS '{"select": true, "manage": true}';
ACL ADMIN REVOKE CATALOG sales FROM ROLE auditor;

Functions: acl_grant_catalog(role, vcat, caps_json[, is_main[, rls, columns]]), acl_alter_grant(role, vcat, field, value) with field = caps | rls | columns | main, acl_revoke_catalog(role, vcat) - the revoke also removes the role's object grants and probed grant columns in that catalog.

Schema grants

GRANT SCHEMA <catalog>.<path> TO ROLE <role> [WITH (<capability>, …) | CAPS '<json>']
    [INTO <phys schema> | VIRTUAL ONLY] [COMMENT '<text>']
REVOKE SCHEMA <catalog>.<path> FROM ROLE <role>

The middle level of the grant chain: capabilities for everything under a schema path, including objects that appear in it later. It carries capabilities only - RLS and COLUMNS parse but are refused, and manage is refused at this level. Capabilities resolve by the longest granted prefix of a name (object -> schema -> catalog); a level that states none inherits from the nearest ancestor that does, and the inheritance is materialised when a grant or a schema changes. REVOKE re-points the subtree at the next ancestor. A schema grant does not make names resolve - the role still needs the catalog grant.

create/drop on a schema grant are the right to create/drop objects in it (neither implies the other; schemas themselves are the operator's). Where a role's CREATE lands is the grant's decision: INTO <phys schema> names the physical schema (checked to exist); VIRTUAL ONLY lets the role only register objects that already exist physically; neither follows the schema declaration (an alias creates in what it aliases, an expansion in its origin). A principal's own DDL then records the object through acl_register_created / acl_register_existing / acl_register_view, which a principal cannot call directly.

ACL ADMIN GRANT SCHEMA sales.raw TO ROLE analyst WITH (select) COMMENT 'raw zone, read only';
ACL ADMIN GRANT SCHEMA sales.vs TO ROLE ingest WITH (select, insert, create, drop);
ACL ADMIN GRANT SCHEMA sales.vs TO ROLE lander WITH (select, create) INTO phys.staging;
ACL ADMIN GRANT SCHEMA sales.vs TO ROLE curator WITH (select, create) VIRTUAL ONLY;
ACL ADMIN REVOKE SCHEMA sales.raw.eu FROM ROLE analyst;

What a principal's DDL may say (spec 113 - so a stock dbt project runs unchanged):

• Names are [<vcat>.]<schema path>.<object>. The first part is a catalog when it names one the principal holds a grant on (any of them, not only the MAIN one - with several catalogs the three-part name picks one); otherwise the name is a path in the MAIN catalog. Schemas nest: the object's home is the longest granted schema prefix. A first part that is both a catalog of the principal and a schema of its MAIN catalog is refused as ambiguous.
• CREATE [OR REPLACE] TABLE|VIEW and DROP TABLE|VIEW [IF EXISTS] in the home; CASCADE is taken off, so a drop never reaches past the one object (an object with dependents refuses).
• CREATE SCHEMA IF NOT EXISTS on a schema the principal holds is a no-op; without IF NOT EXISTS it is "already exists"; a schema it does not hold is refused - schemas are the operator's.
• ALTER TABLE|VIEW … RENAME TO in the same home, priced at create + drop on it: the new name must not be a record of the catalog, an object carrying the operator's declarations (a predicate, columns, keys, references, a grant by name) keeps its name, an expansion's record follows the object, and a rename is a lineage run (old name in, new name out) with a DROP of the old name and, where a record exists, a CREATE of the new one. Every other ALTER is refused.
• dbt through quack: the table, view and incremental (append) materializations run as they are. delete+insert / merge incremental strategies fail in the quack client itself (DELETE through an attached quack catalog is not planned there), before the node sees them.

Functions: acl_grant_schema(role, vcat, path, caps_json[, comment[, into, virtual_only]]), acl_revoke_schema(role, vcat, path), and the repair call acl_rematerialize_schema_caps(vcat[, path]), which rebuilds a subtree's inherited rows from the nearest ancestor that states capabilities (function only).

Object grants

GRANT TABLE | VIEW | OBJECT <catalog>.<name> TO ROLE <role>
    [WITH (<capability>, …) | CAPS '<json>'] [RLS (<predicate>)] [COLUMNS (<item>, …)]

The three keywords are the same statement: the name may be a table, a view, a table function or a scalar of the catalog (not a bare schema alias). An object grant refines the catalog grant for this role: capabilities it does not state are inherited from the catalog grant, never widened by omission; CAPS '{}' takes every capability away. RLS is AND-ed onto the catalog grant's predicate and the object's own; COLUMNS intersects, and a bare name the object does not expose is refused. On a view the policy applies to the view's output, on a table function to its result; RLS/COLUMNS on a scalar is refused. The target must exist. When the grant states columns, its projection is bound where it is written and stored, so DESCRIBE and information_schema.columns describe what the role reads (a mask that changes a type, a computed column the object never had).

A COLUMNS item with a value on a writable table is an assignment as well as a mask: tenant = acl_claim('tenant') is added to an INSERT when absent, overrides a supplied value, and is applied to an UPDATE's SET.

Writing the grant again replaces it whole (caps, RLS, COLUMNS); REVOKE TABLE | VIEW | OBJECT <catalog>.<name> FROM ROLE <role> takes it away (spec 120), and the catalog's or a schema's grant decides the object again.

Fields of a structured column (spec 102)

A COLUMNS item may name fields of a STRUCT column, at any depth, and through a list of structs. Paths work in a catalog grant, an object grant, and an object's own COLUMNS. A field mask (address.ssn = …) is a grant's only.

item	the role reads
address.city, address.zip	STRUCT(city, zip): only those fields (a NULL struct stays NULL)
address.geo.lat	STRUCT(geo STRUCT(lat))
address, address.ssn = '***'	the whole struct with ssn masked
items[].price	LIST(STRUCT(price)): every element narrowed

ACL ADMIN GRANT TABLE c.customers TO ROLE support COLUMNS (id, address.city, address.zip, orders[].total);
ACL ADMIN GRANT TABLE c.customers TO ROLE audit  COLUMNS (id, address, address.tax_id = '***');

• Checked where it is written. Every step is checked against the column's type and must be a STRUCT field or [] of a list. A path into a MAP or a UNION is refused: those are shown or masked whole. The fields are stored in the source's order.

• Levels and roles. Levels narrow: an object grant never re-exposes a field the catalog grant dropped. A principal's roles unite: {city} and {zip} read {city, zip}, a visible field beats a masked one, and two different masks on one field are refused. Every listing, DESCRIBE, the DDL a quack client binds, and the Arrow schema describe what is read.

• Writes. A column a grant narrows stays writable, and a write touches only what the role sees:

  • an UPDATE/MERGE keeps the stored values of the hidden fields;
  • an INSERT leaves them NULL;
  • a masked field is assigned (like a column mask);
  • a written value that carries a field the role cannot see is refused.

  RETURNING cannot read the column. A column narrowed through [], or by the object's own COLUMNS, is read-only. A field mask that reads the row (left(ssn, 1) || '***') is a mask and not an assignment, so it makes the column read-only too. SET address = NULL sets the visible fields to NULL and keeps the hidden ones.

• A write statement reads what the role reads. In an UPDATE's SET and WHERE, a DELETE's WHERE and a MERGE's conditions:

  • a narrowed column has only its visible fields;
  • a masked column is its mask;
  • a column the grant does not list is refused.

  Beside another relation (UPDATE … FROM, DELETE … USING, MERGE), qualify the other relation's columns. A MERGE's UPDATE SET * / UPDATE BY NAME is refused under a column policy: name the columns.

ACL ADMIN GRANT TABLE c.orders TO ROLE narrow WITH (select, update, delete, merge) RLS (tenant = acl_claim('tenant'));
ACL ADMIN GRANT TABLE sales.orders TO ROLE analyst WITH (select) RLS (amount > 50) COLUMNS (id, amount);
ACL ADMIN GRANT TABLE c.orders TO ROLE inj WITH (select, insert, update)
    RLS (tenant = acl_claim('tenant')) COLUMNS (id, amount, tenant = acl_claim('tenant'));
ACL ADMIN GRANT TABLE sales.shout TO ROLE ingest CAPS '{"select": true}';   -- read on one function only

Function: acl_grant_object(role, vcat, vname, caps_json[, rls, columns]).

Function categories

What a principal may call (spec 072). A function's key is database.schema.name plus its kind - scalar (the default: scalars, aggregates, windows, macros) or TABLE (a function in FROM). A category is a named set of keys; a call is admitted when its key is in a category granted to one of the principal's roles (or to all roles) or is granted by name; a deny anywhere wins; a key in no category is refused. The shipped categories are seeded once, when the catalog is created, and never touched again by the extension: base, generators, node_facts, json, icu, spatial, inet, h3, hashfuncs, a5, geosilo are every role's from the start; readers, meta, environment, node, plan are nobody's until granted. A function of a newly loaded extension, a builtin a pin bump adds, a macro an admin creates: in no category, refused, until put somewhere - SELECT * FROM acl_function_status() WHERE status = 'uncategorized' is the screen. A principal reads its own side of it through duckdb_functions(): what it may call, and nothing else (spec 098) - the virtual functions of every catalog it holds (spec 115), each called by its qualified name (other.f(…), FROM c.raw.eu.feed(); a flat name of the MAIN catalog also bare, and under a session's USE a short name is read in its catalog first), and acl_function_columns([catalog[, schema[, function]]]) lists their parameters, result columns and a scalar's return type.

CREATE FUNCTION CATEGORY <name> [COMMENT '<text>']
ALTER FUNCTION CATEGORY <name> ADD (<function> [, …]) | DROP (<function> [, …])
ALTER FUNCTION CATEGORY <name> SET COMMENT '<text>'      -- it must exist; the members stay (spec 120)
DROP FUNCTION CATEGORY [IF EXISTS] <name>

GRANT  FUNCTION CATEGORY <name> TO ROLE <role> | TO ALL ROLES
DENY   FUNCTION CATEGORY <name> TO ROLE <role> | TO ALL ROLES
REVOKE FUNCTION CATEGORY <name> FROM ROLE <role> | FROM ALL ROLES

GRANT  FUNCTION <function> TO ROLE <role> | TO ALL ROLES
DENY   FUNCTION <function> TO ROLE <role> | TO ALL ROLES
REVOKE FUNCTION <function> FROM ROLE <role> | FROM ALL ROLES

A <function> is name, schema.name or database.schema.name - a bare name is system.main, where every builtin lives - optionally followed by TABLE; an operator is double-quoted ("+", "IS DISTINCT FROM"). A member or an admitting grant must name a function this node has, unless the kind was written explicitly: that is how a fleet-shared catalog records a function of an extension the writing node does not carry. The never set - acl_*, ducklake_*, quack_*, arrow_scan*, query, query_table, json_execute_serialized_sql, a scanner's *_query / *_execute / *_attach - is refused where it is written: only ACL NATIVE runs those.

ACL ADMIN CREATE FUNCTION CATEGORY sequences COMMENT 'read and advance a sequence';
ACL ADMIN ALTER FUNCTION CATEGORY sequences ADD (nextval, currval, setval);
ACL ADMIN GRANT FUNCTION CATEGORY sequences TO ROLE etl;
ACL ADMIN GRANT FUNCTION CATEGORY readers TO ROLE etl;          -- unconfined: any path the node can read
ACL ADMIN DENY FUNCTION read_text TABLE TO ROLE etl;            -- readers, but not this one
ACL ADMIN GRANT FUNCTION lake.main.peek TABLE TO ROLE analyst;  -- an admin's macro: only by a grant on its key
ACL ADMIN DENY FUNCTION CATEGORY base TO ROLE quarantine;       -- takes back what every role holds
ACL ADMIN REVOKE FUNCTION CATEGORY spatial FROM ALL ROLES;      -- spatial is nobody's from now on
ACL ADMIN DROP FUNCTION CATEGORY IF EXISTS sequences;           -- with its members and grants

The bulk form is the native mode: ACL NATIVE SELECT acl_function_category_add('myext', list(function_name)) FROM duckdb_functions() WHERE function_name LIKE 'myext_%';. A category acts in every catalog a role holds, so these statements take an unrestricted manage scope - a catalog-scoped one is refused as "not catalog-specific".

Secrets

The node's secrets are kept by a secrets service attached to it - a catalog of type tresor (ATTACH 'tresor:<host>' AS corp) - never by the node's own secret manager. A principal manages them under the explicit secrets capability of its MAIN catalog grant (WITH (select, secrets) MAIN); an administration scope does not include it (spec 082):

ACL GRANT  SECRET <name> TO   ROLE | GROUP <principal> [FROM | IN <catalog>]
ACL REVOKE SECRET <name> FROM ROLE | GROUP <principal> [FROM | IN <catalog>]
CREATE [PERSISTENT] SECRET [<name>] [IN <catalog>] (TYPE …, …)
DROP [PERSISTENT] SECRET <name> [FROM <catalog>]

• The catalog is the one written, which must be a secrets service; otherwise the one attached. Several unnamed is refused with what to write.
• No service attached (a standalone node, spec 118): CREATE / DROP SECRET that name no storage keep the secret on the node by duckdb's own rule - PERSISTENT in its secret files (a file on the node's disk: secret_directory, in the clear), otherwise in memory until the node restarts. The secrets capability and the constant parameters still apply; GRANT / REVOKE SECRET are the service's and are refused; a storage named (IN memory, IN local_file) must still be a service. The node keeps no owner per secret: there, secrets is the administration of the node's own secrets - the operator's included (a DROP, a CREATE OR REPLACE, a longer SCOPE that the node's reads then pick) - so grant it only to whoever administers the node. Where secrets need an owner and an admin check, a standalone node attaches a secrets service too, logged in as its own service identity: the service then decides who may create, drop and grant (its administrators), and records the owner - the session's user when the node acts for an ACL session (tresor's delegation), the node's identity otherwise (spec 082). A cluster node always does.
• GRANT / REVOKE SECRET follow the principal prefix with the ACL marker (ACL TOKEN '…' ACL GRANT SECRET …; unmarked after the gateway's ACL ADMIN) and compile to the service's own calls, <catalog>.main.grant_secret('<name>', 'role:<r>', ['use']) / revoke_secret(…). A secret is granted to a role or a group, never to one user, and only its use. A batch holds nothing else.
• CREATE / DROP SECRET keep the secret in the service. With a service attached, TEMPORARY is refused (a temporary secret would serve every statement on the node after it), and so is any storage that is not a service. A secret's parameters are constants - literals and lists of them: duckdb evaluates them on the node, so a call there (SECRET getenv('…')) would store what the node knows.
• The service decides too. It manages only for a principal it knows as an administrator (tresor's spec 009); the capability is acl's half of the gate.
• A direct call into the service catalog (corp.whoami(), corp.main.secrets()) is the function gate's like any other: the operator puts what a role may call in a category - ALTER FUNCTION CATEGORY secrets_service ADD (corp.main.whoami TABLE, corp.main.secrets TABLE) - and grants it. Manage through the statements above, which also need secrets, rather than by categorizing grant_secret. quack's own whoami() (the system catalog's) stays never callable.
• No secret value is written to an audit or profile event: a decision names the service and the secret's name with capability secrets.

Resource groups

CREATE [OR REPLACE] RESOURCE GROUP <group> [WITH] (<limit> <value>, …) [DEFAULT] [COMMENT '<text>']
ALTER RESOURCE GROUP <group> SET DEFAULT | DROP DEFAULT
ALTER RESOURCE GROUP <group> SET (<limit> <value>, …)   -- only the limits named change (spec 120)
ALTER RESOURCE GROUP <group> SET COMMENT '<text>'
DROP RESOURCE GROUP [IF EXISTS] <group>
GRANT  RESOURCE GROUP <group> TO   ROLE <role>
REVOKE RESOURCE GROUP <group> FROM ROLE <role>

A resource group is a set of named limits bound to roles (spec 085). It is resolved once when a session opens, and a limit it does not name is the node's setting.

limit	what it limits	0 means
window_start	the quack fetch window at the start of a stream (acl_quack_fetch_window)	no window
window_max	how far that window grows (acl_quack_fetch_window_max)	no cap
batch_bytes	the quack batch target (acl_quack_target_batch_bytes); a size like '32MiB' is accepted	-
max_result_rows	the rows a Flight stream hands out before its refusal (acl_max_result_rows)	unlimited
queue_priority	the order in the stream budget's line; higher goes first, and waiting raises it	-
max_sessions	the live sessions charged to this group	unlimited

• Validation. A name outside the table is refused, and so is a negative value. Only queue_priority may be negative.

• Several groups. A principal whose roles are in several groups gets the most generous value of each limit, the same rule capabilities follow. The session is charged to the group that allows the most sessions. A group that limits none leaves the session uncharged.

• Node settings. A group may exceed the node's settings. The node's capacity bounds, which are acl_node_stream_budget, the quack seats and acl_max_sessions, still hold for everyone.

• Re-creating. A re-create replaces the group's limits, and the roles bound to it stay bound. A drop removes the group's bindings, and so does dropping a role.

• Authorization. A group acts on the whole node, so these statements need an unrestricted manage scope.

• Listings. acl_resource_groups() and acl_role_resource_groups() list the groups (with is_default) and their bindings. acl_sessions() shows each session's groups and its charged_group.

• Functions. The admin functions behind the statements are acl_create_resource_group(group, limits_json[, comment[, is_default]]), acl_alter_resource_group(group, 'default', 'true' | 'false') / (group, 'limits', limits_json) (only the named limits change; the result is judged as CREATE judges it) / (group, 'comment', text) (spec 120), acl_drop_resource_group(group[, 'skip']), acl_grant_resource_group(role, group) and acl_revoke_resource_group(role, group).

• The default group (DEFAULT, or ALTER RESOURCE GROUP g SET DEFAULT): a principal whose roles are in no group is its member - for its limits. At most one group is the default: marking a second is refused, naming the current one. A re-create without DEFAULT keeps the mark; DROP DEFAULT removes it.

A group as a set of nodes (spec 096: a node of a group serves only that group's principals), the cluster profile (ACL CLUSTER …, spec 093), drift and the cluster bundle (spec 118) are the hugr platform's: they live in hugr-node (its docs/cluster.md), which builds this acl into the platform node's extension, hugr_node (spec 127). An open node is configured by its bootstrap SQL and its admin's ACL NATIVE.

Sources (spec 118.3)

GRANT SOURCE <database>[.<schema>] TO ROLE <role>
REVOKE SOURCE <database>[.<schema>] FROM ROLE <role>

A source granted to a role - by policy or passthrough - is what a catalog admin sees of the node's physical sources (platform.attached.<alias>.<schema>, metadata only) and the only physical names it may build over: a table, a schema alias / expansion, a function alias, the tables and views a view / macro / RLS / mask body names - each judged by the object it resolves to (spec 138: a physical view by its own database, t alone in the node's default database, a name of no object refused; what a granted view or function reads inside is not judged). policy and passthrough build over every source; cluster sees every source but, holding a catalog's manage too, builds only over granted ones (it hands out no data). Never the policy store's database, a secrets service or a hidden database (another catalog's internals, ducklake's metadata). A revoke takes back nothing already built; REVOKE ADMIN FROM ROLE r removes its source grants with its other administration. Functions: acl_grant_source(role, source), acl_revoke_source(role, source) (platform.grant_source / revoke_source).

Administration scopes

GRANT ADMIN observe | operate | cluster | policy | manage | passthrough TO ROLE <role>
REVOKE ADMIN [observe | operate | cluster | policy | manage | passthrough] FROM ROLE <role>
GRANT | DENY VIEW platform.<view> TO ROLE <role>
GRANT | DENY FUNCTION platform.<function> TO ROLE <role>
REVOKE VIEW | FUNCTION platform.<object> FROM ROLE <role>

The global scopes are bundles of the platform catalog (spec 117), held as a set - a role holds several, a grant adds one and keeps the others. observe (spec 097) reads the node views, the load report and /metrics and administers nothing; operate (spec 118) is observe + the node's runtime; cluster (spec 118, a cluster node's) is operate + the cluster profile and its drift; policy is the management grammar over every catalog plus the statements that belong to no catalog (roles, issuers, mappings, categories, resource groups, catalogs themselves, grants) and the policy views; manage is spec 009's name for policy + observe; passthrough is anything, including ACL NATIVE SQL outside the virtual catalog, the bundles and the grants on platform. REVOKE ADMIN <bundle> takes one bundle; REVOKE ADMIN FROM ROLE r takes every one, the manage capability of the role's catalog grants and its point grants on platform. Managing one catalog is not granted here but with GRANT CATALOG … CAPS '{"manage": true}'.

A point grant gives one view (read whole) or one function (callable for any catalog) of platform; DENY writes a deny, which wins over every bundle but passthrough; REVOKE takes the grant or the deny. Never to ALL ROLES, and never on grant_admin, revoke_admin, grant_platform, revoke_platform, migrate_catalog (the passthrough scope's own), cluster_extension, cluster_attach, cluster_detach, cluster_setting (the cluster bundle's alone) and console_info (every holder's).

Granting or revoking a bundle or a point grant needs passthrough - policy never hands them out, and no scope is self-granted.

ACL ROLE "platform_admin" GRANT ADMIN policy TO ROLE auditor;
ACL ROLE "platform_admin" GRANT VIEW platform.sessions TO ROLE support;
ACL ADMIN REVOKE ADMIN observe FROM ROLE auditor;
ACL ADMIN REVOKE ADMIN FROM ROLE sales_owner;

Functions: acl_grant_admin(role, scope), acl_revoke_admin(role[, scope]), acl_grant_platform(role, 'view' | 'function', object[, allowed]), acl_revoke_platform(role, kind, object).

Profiling a session

PROFILE SESSION CURRENT | '<ops id>' ON | ALL | SAMPLED | OFF

The operator's profile level on one live session (spec 074): what its statements' execution profiles are recorded at from its next statement on - ON / ALL every statement, SAMPLED the ones whose trace context carries the sampled flag, OFF clears the override so the registered policy's rule and the node's acl_profile_level decide again. CURRENT is the session the statement runs under (an ACL SESSION prefix - a client connected through a door); off a session it is a refusal. Another session is named by its ops id from acl_sessions(), which also shows the level in force and who decided it (profile_level, profile_source = instance / policy / override). A session is the node's, not a catalog's: the statement needs the operate bundle (spec 118; passthrough carries it - the policy bundle administers the ACL, not the node). An operator's own connection (no session) uses SET SESSION acl_profile_level = ... instead, which outranks the node's SET GLOBAL on that connection alone.

ACL SESSION 'h…' ACL PROFILE SESSION CURRENT ON;
ACL ADMIN PROFILE SESSION '62F31C2A7615' SAMPLED;
ACL ADMIN PROFILE SESSION '62F31C2A7615' OFF;

Function: acl_session_profile(id, level) ('' clears).

Operating the node

KILL SESSION '<ops id>'
SET SESSION '<ops id>' AUDIT LEVEL OFF | DENIED | DECISIONS | ALL | DEFAULT
DRAIN NODE
RESUME NODE
MIGRATE POLICY CATALOG <database>[.<schema>]

The node's runtime (spec 118), the operate bundle's (passthrough carries it; a point grant on the one function stands in): KILL SESSION ends a live session by its ops id from acl_sessions() / platform.sessions (true when one was found); SET SESSION … AUDIT LEVEL is the operator's audit level on that session, DEFAULT clears it back to the policy's and the node's (spec 069); DRAIN NODE stops seating new clients and answers how many sessions remain, RESUME NODE seats them again (spec 066) - this node, never the fleet. The session is a single-quoted id: duckdb's own SET SESSION <setting> = … (spec 068's TimeZone) is never taken for it. MIGRATE POLICY CATALOG applies the schema steps to a policy catalog (spec 094) - it moves the schema window of every node that reads it, so it is passthrough's alone.

ACL ROLE "ops" KILL SESSION '62F31C2A7615';
ACL ROLE "ops" SET SESSION '62F31C2A7615' AUDIT LEVEL all;
ACL ROLE "ops" DRAIN NODE;
ACL ROLE "root" MIGRATE POLICY CATALOG store.acl;

Functions: acl_session_kill(id), acl_session_audit_level(id, level), acl_drain(), acl_resume(), acl_migrate_catalog(database[, schema]) - or platform.kill_session, session_audit_level, drain, resume, migrate_catalog.

Viewing the catalog as a role

SET SESSION VIEW AS ROLE '<role>'
RESET SESSION VIEW AS

On a session of its own (a door's ACL SESSION - the Flight or quack connection a console keeps), an administrator holding policy (or passthrough) sees the catalog as <role> sees it (spec 120): from then on every statement of that session is rewritten as ACL ROLE "<role>" would be - information_schema, the duckdb_* listings, SHOW TABLES, DESCRIBE, the Flight catalog RPCs and the platform listings answer what the role sees - and metadata only: a read of an object's rows, a table function, a virtual scalar (its template may read), SUMMARIZE, a write, DDL, management, ACL NATIVE and the secrets statements are refused (a view-as session reads metadata only). RESET SESSION VIEW AS (judged as the session's own principal) leaves the mode. A view never shows more than the viewer may read: a role that holds administration (a bundle, a catalog's manage, a point grant) is viewed by passthrough only, and the right is judged again on every statement - a viewer that loses policy is out of the mode at once. Inside it, USE is refused (and the viewer's own USE and temp tables do not apply), functions of attached catalogs are refused (system functions run), and a Flight statement prepared before the mode does not run inside it. The mode is the session's: another session of the same administrator is untouched, a per-statement prefix (ACL ROLE …, a gateway's shared connection) has no session to set it on, and the call acts on the session the statement runs under - never one named by an argument. A catalog admin may not take a view (the role's tree spans catalogs it does not administer). The audit keeps who looked (the session's principal), with the detail view_as.

ACL SESSION 'h…' SET SESSION VIEW AS ROLE 'analyst';
ACL SESSION 'h…' SELECT table_schema, table_name FROM information_schema.tables;
ACL SESSION 'h…' RESET SESSION VIEW AS;

A console that checks how a role sees the catalog (a DBeaver data source, for one) declares the mode on every call instead: on the Flight door the header x-acl-view-as: <role> (percent-encoded) puts the call's session in the mode - judged like the statement, or the call is refused - so a session the door seats afresh (the old one went idle, was killed, the node restarted, another node took the connection) is in the mode again, never the viewer's own. The JDBC driver's acl.viewAs sends it. On such a connection a SET / RESET SESSION VIEW AS is refused (open another connection to change the role); a DENY FUNCTION platform.view_as refuses the mode however it is asked for. A client that only SETs the mode is guarded on this node for a day, and on any node its connection moves to for as long as its session-state cookie says the session was in the mode (spec 135): its connection is refused once its session ended in the mode (… viewed the catalog as a role and is gone - connect again); a SET on a per-call session (a Flight call without the session cookie) is refused - the mode would end with the call. Another principal's token on a connection in the mode is refused (… needs a connection of its own); the viewer's session is kept. When the viewer loses the right, or the role comes to hold administration or is dropped, the session's statements - and its statements prepared in the mode - are refused (the view-as mode can no longer be kept … - connect again), never run as the viewer; a RESET is the policy bundle's too, so the way out is a new connection. A data preview is refused in the mode.

Function: acl_session_view_as(role) ('' leaves) - or platform.view_as(role).

Comments

COMMENT ON VIRTUAL TABLE | VIEW | SCHEMA | TABLE FUNCTION | SCALAR <catalog>.<name> [COLUMN <column>] IS '<text>'

Documents a virtual object or one of its columns. The target - and, with COLUMN, the column in the object's stored schema - must exist; an object whose schema is unknown is refreshed first (below). A comment survives a definition change and goes with its object when that is dropped. The CREATE forms take the same comment inline (COMMENT '<text>'), and ALTER VIRTUAL CATALOG … SET COMMENT comments a catalog.

ACL ADMIN COMMENT ON VIRTUAL VIEW sales.stats IS 'per-tenant totals';
ACL ADMIN COMMENT ON VIRTUAL VIEW sales.stats COLUMN top IS 'the largest order';
ACL ADMIN COMMENT ON VIRTUAL SCHEMA sales.raw IS 'raw zone';

Function: acl_comment(vcat, vname, kind, column, comment) with kind = relation (table or view) | schema | table (table function) | scalar; column empty for the object itself.

Refreshing stored schemas

ANALYZE VIRTUAL CATALOG <catalog>
ANALYZE VIRTUAL [TABLE [FUNCTION] | VIEW | SCALAR] <catalog>.<name>

Query-defined objects (views, macros, projections) have their schema derived by binding the definition when it is written and stored. When the physical schema moves under them, ANALYZE VIRTUAL re-derives it for one object or for every object of a catalog and returns how many were re-probed. Alias-form objects read their schema live and never need it. An expansion's object list is refreshed with ALTER VIRTUAL SCHEMA … REFRESH instead.

ACL ADMIN ANALYZE VIRTUAL CATALOG sales;

Function: acl_refresh_schema(vcat[, vname]) (returns BIGINT).

Checking and repairing a catalog

CHECK VIRTUAL CATALOG <catalog>
REPAIR VIRTUAL TABLE <catalog>.<name> REMAP (<column> = <expression>, …)
REPAIR VIRTUAL TABLE <catalog>.<name> DROP MISSING COLUMNS [AND MASKS]

The source changes under the catalog - a column dropped or renamed, a table gone, a view's dependency moved - and nothing on a principal's query path may look (spec 065). CHECK VIRTUAL CATALOG is the administrator's look, off the query path: it probes every stored fact of the catalog against the source and answers one row per finding - vcat, kind (table, view, function, schema, grant, reference), object, role (a grant's, else NULL), problem, detail (our sentence; it may name the physical column or path - this is the admin's surface) and repair (the statement that mends it, ready to paste). It writes nothing. The problems:

problem	what no longer holds	the repair it names
source_missing	the relation's physical source does not bind (dropped, renamed)	ALTER VIRTUAL TABLE … SET PHYS / DROP VIRTUAL TABLE
phys_incomplete	the physical name is stored in two parts over an attached database (written before spec 137, or before its source was attached): it is read, but not listed	ALTER VIRTUAL TABLE … SET PHYS <db>.<schema>.<object> (the finding names it)
column_missing	a declared COLUMNS entry whose expression no longer binds against the source	REPAIR VIRTUAL TABLE … REMAP (…) or … DROP MISSING COLUMNS
definition_broken	a view's SQL, a macro's template (with its declared parameter types), or an alias's target function does not bind / exist	CREATE OR REPLACE VIRTUAL VIEW … AS / acl_alter_function
schema_stale	a query-defined object's stored (derived) schema - a view's, a macro's, or a declared-list table's projection - differs from what its definition binds to now	ANALYZE VIRTUAL … (re-derives a table's projection too since spec 039, keeping its marks and column comments)
rls_broken / rls_unchecked	a predicate (object or grant) fails to bind / was accepted unchecked and binds now (spec 027)	… SET RLS, a re-grant / ANALYZE VIRTUAL CATALOG
grant_column_missing	a bare item of a grant's COLUMNS list matches no column of the object (an object grant), or of any object of the catalog (a catalog grant)	acl_grant_object(…) / ALTER GRANT CATALOG … SET COLUMNS
mask_broken	a name = expr grant item names a column the object does not expose, or its expression does not bind - every read of that object by that role refuses (spec 038)	the same
schema_missing	a schema alias's or expansion's physical schema has no schema behind it	ALTER VIRTUAL SCHEMA … SET PHYS / DROP VIRTUAL SCHEMA
expansion_stale	an expansion's source has tables it did not record (and did not exclude), or records whose source is gone	ALTER VIRTUAL SCHEMA … REFRESH [PRUNE]
reference_dangling	a reference's end object, or a column it names, is not in the catalog (catalog facts only)	DROP VIRTUAL REFERENCE
types_stale	the source's column types differ from the type facts stored with the object (spec 099) - a retyped column, or a source that did not exist when the object was written	ANALYZE VIRTUAL TABLE … / … VIEW …
enum_domain_exposed	an object with an ENUM column whose labels are exposed (enums = keep) while a predicate - the object's or a grant's - narrows its rows (a view's own WHERE is not seen)	ALTER VIRTUAL TABLE … SET TYPES (enums = varchar)
types_incompatible	a declared entry or a grant's mask that binds over the source but not over the exposed type (enum_code(tier) once ENUMs are VARCHAR) - every read refuses	… SET TYPES (enums = keep), or rewrite the expression
source_not_granted	an object whose current definition an administrator below policy wrote (spec 120) reads a physical source its roles are no longer granted (REVOKE SOURCE) - its physical name, an alias' schema, a function alias' database, the objects its view / template / RLS / computed columns / reference expression name (each as it resolves, spec 138); it goes on working	GRANT SOURCE <db>.<schema> TO ROLE <r> (a role that still administers), or - when those roles are gone - redefine or drop the object
body_function_denied	a stored body (view, template, alias, RLS, column or mask, a grant's policy) calls a function no principal may call - written before spec 117 judged bodies by their author's gate	redefine it without the call, or drop it
types_mismatch	a declared entry that makes a type of its own (CAST(x AS ENUM(…))) - described as the exposed type, read as its own	REPAIR VIRTUAL TABLE … REMAP (n = CAST(… AS <type>))

A bare alias (no declared list) has no contract beyond "binds", so only source_missing (and spec 137's phys_incomplete) can be found on it - declaring COLUMNS is the opt-in to column_missing, as it is to spec 065's clean refusals. A column added to the source is not a finding: it appears live under a whole-table grant by design (docs/security.md, "a grant on the source's future").

REPAIR VIRTUAL TABLE mends a declared list on purpose, and never drops a mask silently:

• REMAP (ssn = ssn_v2, …) gives the named entries new expressions, each probed to bind before anything is written (a name the list does not declare, or an expression that does not bind, is refused and nothing changes); the grants' projections on the object are re-probed after.
• DROP MISSING COLUMNS removes every entry whose expression no longer binds. It is refused while an object grant on that object masks one of those names (… would orphan the mask(s) on "ssn" (role "analyst") - REMAP the column, alter those grants, or DROP MISSING COLUMNS AND MASKS). A catalog grant's mask is not touched either way: it protects the column on every other object, and spec 038 refuses this object's reads for it - which CHECK reports as mask_broken.
• DROP MISSING COLUMNS AND MASKS also removes exactly those mask items from the object grants that carried them, by name; the count includes them. The mask protected a column that no longer exists; what the administrator acknowledges is that a column of that name is gone for good.

Both return how many entries changed (0 when nothing was missing). A view has nothing to repair here (ANALYZE VIRTUAL VIEW re-derives, CREATE OR REPLACE VIRTUAL VIEW redefines), and a bare alias has no list to mend.

While a declared-list object is broken, the tables listings a principal sees (information_schema. tables, duckdb_tables()) carry the mark in the object's comment - acl: broken - declared column(s) ssn no longer exist in the source - rather than quietly describing a narrower object; the columns surface keeps the contract as written. A role whose grant masks the vanished column (ssn = NULL) never reads it and keeps its rows; a role whose grant reads it gets the binder error spec 065 accepted. The mark judges declared entries that read a bare source column - a computed column written as an expression, a constant or one of SQL's bare-word functions (current_user, current_date, …) is not judged; write anything else that is not a column with parentheses or a cast so it cannot be mistaken for one.

A source the listing could not read at all (spec 136) is said before anything else: the object's comment begins acl: source <database> could not be read (<class>) - the class of the exception (IO, Binder, not attached, ...), never its text, and the database only to a principal that may read the object - and the object stays listed with no physical columns. Nothing about its declared list is known then, so the broken mark waits for the source.

ACL ADMIN CHECK VIRTUAL CATALOG sales;
ACL ADMIN REPAIR VIRTUAL TABLE sales.customers REMAP (ssn = ssn_v2);
ACL ADMIN REPAIR VIRTUAL TABLE sales.customers DROP MISSING COLUMNS AND MASKS;

Functions: acl_check_catalog([vcat]) (a table function; no argument walks every catalog), acl_repair_relation(vcat, vname, action[, spec]) (returns BIGINT; action is remap with the list in spec, drop_missing, or drop_missing_and_masks). Both are the administrator's: denied to a principal, and under a role's management scope confined to the catalogs it manages (the no-argument function form, which walks every catalog, is the operator's own connection's - the management grammar always names one).

ALTER

ALTER changes one property of an existing object: a missing target is an error (there is no IF EXISTS), every property not named keeps its value, and one statement carries one SET. The object forms carry the VIRTUAL marker so duckdb's own ALTER TABLE stays native.

Statement	Function
ALTER VIRTUAL CATALOG <c> SET COMMENT '…'	acl_alter_catalog(vcat, comment)
ALTER VIRTUAL TABLE <c>.<n> SET PHYS <phys>	acl_alter_relation(vcat, vname, 'phys', value)
ALTER VIRTUAL TABLE <c>.<n> SET COLUMNS (<item>, …)	acl_alter_relation(vcat, vname, 'columns', csv)
ALTER VIRTUAL TABLE <c>.<n> SET RLS (<predicate>)	acl_alter_relation(vcat, vname, 'rls', predicate)
ALTER VIRTUAL TABLE <c>.<n> SET PRIMARY KEY (<column>, …) / DROP PRIMARY KEY	acl_set_key(vcat, vname, 'relation', pk_csv) (empty = drop)
ALTER VIRTUAL TABLE <c>.<n> SET TYPES (aliases = …, enums = …) (or VIEW)	acl_alter_relation(vcat, vname, 'types', list) (spec 099)
ALTER VIRTUAL VIEW <c>.<n> SET AS <select>	acl_alter_relation(vcat, vname, 'view', sql)
ALTER VIRTUAL VIEW <c>.<n> SET PRIMARY KEY (…) / DROP PRIMARY KEY	acl_set_key(vcat, vname, 'relation', pk_csv)
ALTER VIRTUAL SCHEMA <c>.<path> SET PHYS <phys schema>	acl_alter_schema_alias(vcat, path, phys)
ALTER VIRTUAL SCHEMA <c>.<path> REFRESH [PRUNE]	acl_refresh_schema_objects(vcat, path, prune)
CHECK VIRTUAL CATALOG <c>	SELECT * FROM acl_check_catalog(vcat) (spec 039)
REPAIR VIRTUAL TABLE <c>.<n> REMAP (<v> = <expr>, …) / DROP MISSING COLUMNS [AND MASKS]	acl_repair_relation(vcat, vname, 'remap', list) / (…, 'drop_missing[_and_masks]')
ALTER VIRTUAL TABLE FUNCTION <c>.<n> SET MACRO <select> / SET ALIAS [OF] <fn>	acl_alter_function(vcat, vname, 'table', 'macro' or 'alias', definition)
ALTER VIRTUAL TABLE FUNCTION <c>.<n> SET PRIMARY KEY (…) / DROP PRIMARY KEY	acl_set_key(vcat, vname, 'table', pk_csv)
ALTER VIRTUAL SCALAR <c>.<n> SET MACRO <expression> / SET ALIAS [OF] <fn>	acl_alter_function(vcat, vname, 'scalar', 'macro' or 'alias', definition)
ALTER ROLE <r> SET CLAIMS (…)	acl_alter_role(role, claims_csv)
ALTER ISSUER <i> SET … / ALTER CLIENT <c> SET …	acl_alter_issuer(name, spec) / acl_alter_client(name, spec)
ALTER GRANT CATALOG <c> TO ROLE <r> SET CAPS '…' / SET RLS '…' / SET COLUMNS '…' / SET MAIN true / SET MAIN false	acl_alter_grant(role, vcat, field, value)

ALTER VIRTUAL VIEW on a table (or the reverse) is refused. The SET PHYS, SET COLUMNS and SET RLS forms take a list in parentheses or quoted; ALTER GRANT takes quoted values only. Redefining an object with CREATE OR REPLACE or ALTER carries its comment, declared key and nullability marks unless the statement states them; a key whose column the new shape no longer has lapses.

ACL ADMIN ALTER VIRTUAL TABLE c.two SET COLUMNS (order_id = id, total = amount);
ACL ADMIN ALTER VIRTUAL TABLE c.orders SET PRIMARY KEY (id, tenant);
ACL ADMIN ALTER VIRTUAL VIEW c.daily SET AS 'SELECT tenant AS day, count(*)::INTEGER AS total FROM phys.main.orders GROUP BY 1';
ACL ADMIN ALTER VIRTUAL TABLE FUNCTION c.report SET MACRO 'SELECT id, amount FROM phys.main.orders WHERE amount > acl_arg(1)';

DROP

A drop removes a definition from the policy and leaves the physical object alone (a principal's own DROP TABLE through a virtual name, under the drop capability, is the one that removes both). Without IF EXISTS a missing target is an error.

Statement	Function
DROP VIRTUAL CATALOG [IF EXISTS] <c> [CASCADE]	acl_drop_catalog(vcat, cascade, mode)
DROP VIRTUAL TABLE [IF EXISTS] <c>.<n> / DROP VIRTUAL VIEW … / DROP RELATION [IF EXISTS] <c>.<n>	acl_drop_relation(vcat, vname, mode)
DROP VIRTUAL SCHEMA [IF EXISTS] <c>.<path> [CASCADE]	acl_drop_schema_alias(vcat, path, mode, cascade)
DROP VIRTUAL TABLE FUNCTION [IF EXISTS] <c>.<n>	acl_drop_function(vcat, vname, 'table', mode)
DROP VIRTUAL SCALAR [IF EXISTS] <c>.<n>	acl_drop_function(vcat, vname, 'scalar', mode)
DROP [VIRTUAL] REFERENCE [IF EXISTS] <c>.<name>	acl_drop_reference(vcat, name, mode)
DROP ROLE [IF EXISTS] <r>	acl_drop_role(role, mode)
DROP ISSUER [IF EXISTS] <i>	acl_drop_issuer(name, mode) (its implicit client and mappings)
DROP CLIENT [IF EXISTS] <c>	acl_drop_client(name, mode) (its mappings)
`DROP MAP GROUP '' FROM CLIENT	ISSUER TO ROLE (orMAP CLAIM`)

mode is skip for IF EXISTS, otherwise empty. Dropping an object takes its stored schema, comments, declared key and every reference that names it with it; a schema dropped with CASCADE takes those of its expanded records too.

Batches and authorization

• One prefix per batch. The prefix names one principal (or the anonymous gateway) and one mode for every statement after it; a second ACL … inside the text is not a prefix.

• A batch is all management or all queries (spec 117): CREATE ROLE r; SELECT 1; and SELECT 1; CREATE ROLE r; are refused for mixing, under a principal prefix and under ACL ADMIN alike, and so is a batch of platform.<op>(…) calls beside a query (a batch mixing management statements and queries is refused).

• Parsed and authorized statement by statement, before anything runs. A management batch compiles to one function call per statement; each call is checked against the principal's rights at parse time, and a refusal anywhere executes nothing. Execution itself is not atomic: a batch that passes and then fails at runtime leaves the earlier statements applied, like any SQL batch.

• What each scope may do (spec 009):

  Scope	May run
  anonymous ACL ADMIN (where allowed)	everything, native SQL included
  passthrough (GRANT ADMIN passthrough)	everything, native SQL included
  policy (GRANT ADMIN policy; spec 009's global manage = policy + observe)	every management statement except GRANT ADMIN / REVOKE ADMIN, the grants on platform, CLUSTER … and the node's operations (operate's); no ACL NATIVE
  cluster (GRANT ADMIN cluster, spec 118, a cluster node's)	CLUSTER … (settings: resources and tuning only) and what operate runs; reads the cluster views and drift; no ACL NATIVE
  operate (GRANT ADMIN operate, spec 118)	KILL SESSION, SET SESSION … AUDIT LEVEL, PROFILE SESSION, DRAIN NODE, RESUME NODE; reads what observe reads; no ACL NATIVE
  observe (GRANT ADMIN observe)	no management statement and no ACL NATIVE - it reads the node views, the load report and /metrics (spec 097)
  a point grant on platform.<f> (GRANT FUNCTION platform.f)	that one operation (the grammar form and the call alike), for any catalog
  catalog-scoped manage (GRANT CATALOG c … CAPS '{"manage": true}')	statements whose target names one of its catalogs; not GRANT/REVOKE CATALOG, GRANT/REVOKE SCHEMA, GRANT TABLE/VIEW/OBJECT, ALTER GRANT, DROP VIRTUAL CATALOG (handing out or taking away access is privilege administration), and not the statements that belong to no catalog (roles, issuers, mappings, CREATE VIRTUAL CATALOG)

  Catalog names compare as names do (case-insensitively, spec 116). A manage scope can create anything the duckdb instance can reach under its catalogs, so it belongs to trusted operators.

• Anonymous administration is the gateway's escape hatch: always on in the in-memory dev store, and off by default once a policy source is enabled (SET GLOBAL acl_allow_anonymous_admin = true turns it on; the setting is global, a session SET changes nothing). The doors refuse to serve while it is on. The usual bootstrap is one GRANT ADMIN passthrough TO ROLE platform through the hatch, then closing it.

Function reference

All functions are registered by the extension; unless noted they return BOOLEAN and take VARCHAR arguments. Optional arguments are in brackets; mode is the write mode of the Notation section.

Policy source

Function	Purpose
acl_use_db(db[, schema[, init BOOLEAN]])	read policy from an ATTACHed database (schema acl; init creates/migrates)
acl_use_functions(slot_map_json)	read policy from registered table functions (read-only)

Virtual catalog content

Function	Purpose
acl_create_catalog(vcat[, comment[, mode]])	create a virtual catalog
acl_alter_catalog(vcat, comment)	set its comment
acl_drop_catalog(vcat[, cascade BOOLEAN[, mode]])	drop it; cascade removes the grants on it
acl_add_relation(vcat, vname, phys, columns_csv, rls[, comment[, mode[, pk_csv]]])	virtual table over a physical one
acl_add_view(vcat, vname, select_sql[, returns[, comment[, mode[, pk_csv]]]])	virtual view
acl_alter_relation(vcat, vname, field, value)	change phys / columns / rls / view
acl_drop_relation(vcat, vname[, mode])	drop a table or view definition
acl_add_schema_alias(vcat, path, phys[, comment[, mode]])	live schema alias
acl_expand_schema(vcat, path, phys[, comment[, mode]])	schema expansion (one record per object, now)
acl_alter_schema_alias(vcat, path, phys)	retarget an alias
acl_refresh_schema_objects(vcat, path[, prune BOOLEAN]) -> BIGINT	re-read an expansion's source; records changed
acl_drop_schema_alias(vcat, path[, mode[, cascade BOOLEAN]])	drop a schema; cascade takes its expanded records
acl_add_table_function(vcat, vname, sql_template[, params, returns[, comment[, mode[, pk_csv]]]])	table-function macro
acl_add_table_function_alias(vcat, vname, target[, '', '', comment[, mode]])	table-function alias
acl_add_scalar(vcat, vname, expr_template[, params, returns[, comment[, mode]]])	scalar macro
acl_add_scalar_alias(vcat, vname, target[, '', '', comment[, mode]])	scalar alias
acl_alter_function(vcat, vname, kind, form, definition)	redefine: kind table/scalar, form macro/alias
acl_drop_function(vcat, vname, kind[, mode])	drop a function definition
acl_set_key(vcat, vname, kind[, pk_csv])	declared primary key; kind relation/table; empty list drops it
acl_comment(vcat, vname, kind, column, comment)	comment an object (column empty) or a column
acl_refresh_schema(vcat[, vname]) -> BIGINT	re-derive stored schemas; objects re-probed
acl_check_catalog([vcat]) (table function)	what no longer holds against the source, one row per finding (spec 039)
acl_repair_relation(vcat, vname, action[, spec]) -> BIGINT	mend a declared list: remap, drop_missing, drop_missing_and_masks
acl_effective_rights(role, vcat[, vname]) (table function)	what a role gets of each object and which grant decides it (spec 120)
acl_check_grant(role, vcat, vname, rls, columns[, caps]) (table function)	what writing that grant would say, written nowhere (spec 122)
acl_exposures(source[, schema[, object]]) (table function)	the virtual objects reading a physical source, per role (spec 120)
acl_add_reference(vcat, name, from, to[, to_kind, args, pairs, expr, cardinality, optional, join_method, comment, mode])	declare a join path
acl_drop_reference(vcat, name[, mode])	drop it
acl_register_created(vcat, vname, phys[, origin])	internal: the record a principal's own CREATE writes
acl_register_existing(vcat, vname, phys[, origin])	internal: the VIRTUAL ONLY form (refuses a missing physical object)
acl_register_view(vcat, vname, body)	internal: the record a principal's own CREATE VIEW writes
acl_rematerialize_schema_caps(vcat[, path])	rebuild a subtree's inherited schema capabilities

Grants and scopes

Function	Purpose
acl_grant_catalog(role, vcat, caps_json[, is_main BOOLEAN[, rls, columns]])	grant a catalog; empty caps_json = every data capability, '{}' = none
acl_alter_grant(role, vcat, field, value)	change caps / rls / columns / main of a catalog grant
acl_revoke_catalog(role, vcat)	revoke it, with the role's object grants in it
acl_grant_schema(role, vcat, path, caps_json[, comment[, into, virtual_only BOOLEAN]])	schema grant
acl_revoke_schema(role, vcat, path)	revoke it
acl_grant_object(role, vcat, vname, caps_json[, rls, columns])	object grant (table, view or function)
acl_grant_admin(role, scope)	global scope manage / passthrough
acl_revoke_admin(role)	drop the role's global scope
acl_session_profile(id, level)	the operator's profile level on a live session; '' clears (spec 074)

Principals

Function	Purpose
acl_define_role(role, claims_csv[, mode])	role with default claims
acl_alter_role(role, claims_csv)	replace its claims
acl_drop_role(role[, mode])	drop it and everything attached to it
acl_define_issuer(name, spec_json[, mode])	an issuer, and its implicit client when the spec has one (spec 095)
acl_alter_issuer(name, spec_json) / acl_drop_issuer(name[, mode])	change / drop it (with its implicit client and mappings)
acl_define_client(name, issuer, spec_json[, mode]) / acl_alter_client / acl_drop_client	a client of an issuer
acl_map_role(scope_kind, scope, source, external_value, role)	map group / claim-value to a role, per client or issuer
acl_drop_role_mapping(scope_kind, scope, source, external_value, role)	drop a mapping
acl_define_token(token, role, claims_csv)	legacy, dev: bind a non-JWT token to a role in memory

Legacy wrappers - the pre-catalog API, kept for dev and test scripts. Without a policy source they fill the in-memory store; with a catalog they write the same content into the implicit virtual catalog default, granted to the role as its main catalog.

Function	Purpose
acl_grant_table(role, vname, phys, cols_csv, rls, caps_csv)	legacy: table policy for one role (caps_csv defaults to select)
acl_grant_view(role, vname, select_sql)	legacy: view for one role
acl_grant_table_function(role, vname, sql_template)	legacy: table-function macro
acl_grant_table_function_alias(role, vname, target)	legacy: table-function alias
acl_grant_scalar(role, vname, expr_template)	legacy: scalar macro
acl_grant_scalar_alias(role, vname, target)	legacy: scalar alias
acl_deny_function(name) / acl_allow_function(name)	legacy: a grant by name to every role, denied or allowed, for both kinds (spec 072)
acl_create_function_category(name[, comment]) / acl_drop_function_category(name)	spec 072: a category of the operator's own; dropping one takes its members and grants with it
acl_function_category_add(category, members) / acl_function_category_remove(category, members)	spec 072: members as a list or a csv of [db.schema.]name [TABLE] (default system.main, default scalar); the never set is refused
acl_grant_function_category(role, category[, allowed]) / acl_revoke_function_category(role, category)	spec 072: role '' is every role; allowed = 'false' is a deny, which wins
acl_grant_function(role, spec[, allowed]) / acl_revoke_function(role, spec)	spec 072: a grant by name admits the key whatever its categories (an admin's macro: 'lake.main.peek TABLE'); a deny by name refuses it whatever they grant
acl_function_status([role]) (table function)	spec 072: every function of the node with its categories and status (never / categorized / uncategorized), present for members this node has no function for; with a role, allowed and decided_by

Doors and sessions - registered alongside the admin functions; they are the operator's and the door's, never a principal's, and have no management-SQL form. acl_flight_serve / acl_flight_stop are registered elsewhere and documented with the Flight door.

Function	Returns	Purpose
acl_quack_serve(uri, token[, mode]) / (uri, token, cert, key[, mode]), acl_quack_stop(uri)	VARCHAR	open the quack door (mode embedded / plain); close it and, if it was the last door, sweep its sessions
acl_quack_authenticate(session_id, client_token, server_token), acl_quack_authorize(connection_id, query)	BOOLEAN, VARCHAR	the door's per-connection and per-statement callbacks (prefixed SQL, or NULL to refuse)
acl_session_open(token), acl_session_sql(handle, sql), acl_session_reason(handle)	VARCHAR	mint a handle (NULL if the token fails), prefix a statement (NULL if unusable), live/expired/idle/unknown
acl_session_close(handle), acl_session_kill(id)	BOOLEAN	end a session by handle, or by the ops id acl_sessions() shows
acl_sessions()	VARCHAR	live sessions as JSON: ops ids (never handles), door, audit level in force and its source
acl_session_sweep(), acl_session_count()	BIGINT	drop dead sessions / count live ones
acl_drain(), acl_resume(), acl_drain_status()	BIGINT, BOOLEAN, VARCHAR	stop seating new clients, resume, draining/serving