# Role-based Access Control (RBAC)

> **For AI agents:** the complete documentation index is at [llms.txt](https://questdb.com/docs/llms.txt). Every page is available as markdown by appending `.md` to its URL, or by sending an `Accept: text/markdown` request header.

Users, groups, service accounts, and permissions from database down to column and row level, plus per-principal query memory limits, in QuestDB Enterprise.

<EnterpriseNote>
  Role-based Access Control (RBAC) provides fine-grained permissions for your QuestDB instance.
</EnterpriseNote>

QuestDB Enterprise provides fine-grained access control that can restrict access
at **database**, **table**, **column**, and even **row** level (using views).

## Quick start

Here's a complete example to create a read-only analyst user in under a minute:

```questdb-sql
-- 1. Create the user
CREATE USER analyst WITH PASSWORD 'secure_password_here';

-- 2. Grant endpoint access (required to connect)
GRANT PGWIRE, HTTP TO analyst;

-- 3. Grant read access to specific tables
GRANT SELECT ON trades, prices TO analyst;

-- Done! The analyst can now connect and query trades and prices tables
```

To verify:

```questdb-sql
SHOW PERMISSIONS analyst;
```

## Access control depth

QuestDB's access control operates across two dimensions:

### Data access granularity

Control *what data* users can access:

| Level        | What you can control            | Example                                               |
| ------------ | ------------------------------- | ----------------------------------------------------- |
| **Database** | All tables, global operations   | `GRANT SELECT ON ALL TABLES TO user`                  |
| **Table**    | Specific tables                 | `GRANT SELECT ON trades TO user`                      |
| **Column**   | Specific columns within a table | `GRANT SELECT ON trades(ts, price) TO user`           |
| **Row**      | Specific rows via views         | Create a view with WHERE clause, grant access to view |

### Connection access granularity

Control *how* users can connect:

| Permission | Protocol                        | Use case                                   |
| ---------- | ------------------------------- | ------------------------------------------ |
| `HTTP`     | REST API, Web Console, ILP/HTTP | Interactive users, web applications        |
| `PGWIRE`   | PostgreSQL Wire Protocol        | SQL clients, BI tools, programmatic access |
| `ILP`      | InfluxDB Line Protocol (TCP)    | High-throughput data ingestion             |

```questdb-sql
-- User can connect via PostgreSQL protocol only (not web console)
GRANT PGWIRE TO analyst;

-- Service can only ingest via ILP, cannot query
GRANT ILP TO ingest_service;

-- Full interactive access
GRANT HTTP, PGWIRE TO developer;
```

These dimensions are independent: a user might have `SELECT` on all tables but
only be allowed to connect via `PGWIRE`, or have `INSERT` permission but only
via `ILP`.

### Column-level access

Restrict users to see only certain columns:

```questdb-sql
-- User can only see timestamp and price, not quantity or trader_id
GRANT SELECT ON trades(ts, price) TO analyst;
```

To grant on every column except a few, use the `*` wildcard with an `EXCLUDE`
list:

```questdb-sql
-- User can see all columns except trader_id
GRANT SELECT ON trades(* EXCLUDE (trader_id)) TO analyst;
```

The wildcard covers the columns that exist when the statement runs, not columns
added later. See
[GRANT](/docs/query/sql/acl/grant/#grant-on-all-columns-of-a-table) for details.

### Row-level access with views

For row-level security, create a [view](/docs/concepts/views/) that filters rows,
then grant access to the view instead of the underlying table:

```questdb-sql
-- Create a view that only shows AAPL trades
CREATE VIEW aapl_trades AS (
  SELECT * FROM trades WHERE symbol = 'AAPL'
);

-- Grant access to the view, not the base table
GRANT SELECT ON aapl_trades TO aapl_analyst;
-- No GRANT on trades table = user cannot see other symbols
```

The user `aapl_analyst` can only see AAPL trades. They have no access to the
underlying `trades` table.

## Common scenarios

### Read-only analyst

A user who can query data but cannot modify anything:

```questdb-sql
CREATE USER analyst WITH PASSWORD 'pwd';
GRANT HTTP, PGWIRE TO analyst;
GRANT SELECT ON ALL TABLES TO analyst;
```

### Application service account

A service account for an application that ingests data into specific tables:

```questdb-sql
CREATE SERVICE ACCOUNT ingest_app WITH PASSWORD 'pwd';
GRANT ILP TO ingest_app;                    -- InfluxDB Line Protocol access
GRANT INSERT ON sensor_data TO ingest_app;  -- Can only insert into sensor_data
```

### Cap a noisy tenant's query memory

Bound the native memory each of a user's queries may allocate, so one tenant's
runaway query fails instead of exhausting shared memory:

```questdb-sql
ALTER USER tenant_a SET MEMORY LIMIT 1G;
```

See [Memory limits](#memory-limits) for how limits resolve.

### Team-based access with groups

Multiple users sharing the same permissions:

```questdb-sql
-- Create a group
CREATE GROUP trading_team;

-- Grant permissions to the group
GRANT HTTP, PGWIRE TO trading_team;
GRANT SELECT ON trades, positions TO trading_team;
GRANT INSERT ON trades TO trading_team;

-- Add users to the group - they inherit all permissions
CREATE USER alice WITH PASSWORD 'pwd1';
CREATE USER bob WITH PASSWORD 'pwd2';
ADD USER alice TO trading_team;
ADD USER bob TO trading_team;
```

### Column-level restrictions (hide sensitive data)

Allow access to a table but hide sensitive columns:

```questdb-sql
CREATE USER auditor WITH PASSWORD 'pwd';
GRANT HTTP, PGWIRE TO auditor;

-- Grant access to non-sensitive columns only
GRANT SELECT ON employees(id, name, department, hire_date) TO auditor;
-- Columns salary and ssn are not granted = invisible to auditor
```

### Row-level security (multi-tenant)

Different users see different subsets of data:

```questdb-sql
-- Base table has data for all regions
CREATE TABLE sales (ts TIMESTAMP, region SYMBOL, amount DOUBLE) TIMESTAMP(ts);

-- Create region-specific views
CREATE VIEW sales_emea AS (SELECT * FROM sales WHERE region = 'EMEA');
CREATE VIEW sales_apac AS (SELECT * FROM sales WHERE region = 'APAC');

-- Grant users access to their region only
CREATE USER emea_manager WITH PASSWORD 'pwd';
GRANT HTTP, PGWIRE TO emea_manager;
GRANT SELECT ON sales_emea TO emea_manager;

CREATE USER apac_manager WITH PASSWORD 'pwd';
GRANT HTTP, PGWIRE TO apac_manager;
GRANT SELECT ON sales_apac TO apac_manager;
```

### Database administrator

A user with full control (but not the built-in admin):

```questdb-sql
CREATE USER dba WITH PASSWORD 'pwd';
GRANT DATABASE ADMIN TO dba;
```

:::warning

`DATABASE ADMIN` grants all current and future permissions. Use sparingly.

:::

### Failover operator

A service account that can move the primary role between nodes, for an external
coordinator or a runbook, without any other administrative right:

```questdb-sql
CREATE SERVICE ACCOUNT failover_bot WITH PASSWORD 'pwd';
GRANT HTTP TO failover_bot;         -- POST /lifecycle/switch on port 9003
GRANT SWITCH ROLE TO failover_bot;  -- SWITCH ROLE, SWITCH STATUS, the endpoint
```

`SYSTEM ADMIN` is neither required nor sufficient for a role switch, and
`DATABASE ADMIN` includes `SWITCH ROLE`. Monitoring accounts do not need it:
`node_role()` and `GET /lifecycle` are open to any authenticated principal. See
[Failover and role switch](/docs/high-availability/failover/).

## Core concepts

[Screenshot: Diagram showing users, service accounts and groups in QuestDB](https://questdb.com/docs/images/docs/acl/users_service_accounts_groups.webp)

### Users and service accounts

QuestDB has two types of principals:

- **Users**: For human individuals. Can belong to multiple groups and inherit
  permissions from them. Cannot be assumed by others.
- **Service accounts**: For applications. Cannot belong to groups - all
  permissions must be granted directly. Can be assumed by authorized users for
  testing.

```questdb-sql
CREATE USER human_user WITH PASSWORD 'pwd';
CREATE SERVICE ACCOUNT app_account WITH PASSWORD 'pwd';
```

Names must be unique across all users, service accounts, and groups.

#### Why service accounts?

Service accounts provide **clean, testable application access**:

| Aspect               | User                           | Service Account        |
| -------------------- | ------------------------------ | ---------------------- |
| Permission source    | Direct + inherited from groups | Direct only            |
| Can belong to groups | Yes                            | No                     |
| Can be assumed (SU)  | No                             | Yes                    |
| Typical use          | Human individuals              | Applications, services |

Because service accounts have no inherited permissions, their access is fully
explicit and predictable. Combined with the ability to assume them, this makes
it easy to verify exactly what an application can and cannot do:

```questdb-sql
-- Create service account with specific permissions
CREATE SERVICE ACCOUNT trading_app WITH PASSWORD 'pwd';
GRANT ILP TO trading_app;
GRANT INSERT ON trades TO trading_app;
GRANT SELECT ON positions TO trading_app;

-- Developer can assume the service account to test its access
GRANT ASSUME SERVICE ACCOUNT trading_app TO developer;

-- Developer switches to service account context
ASSUME SERVICE ACCOUNT trading_app;
-- Now operating with trading_app's exact permissions
-- Test what works and what doesn't...
EXIT SERVICE ACCOUNT;
```

This makes service accounts ideal for applications where you need predictable,
auditable, and testable access control.

### Groups

Groups simplify permission management when multiple users need the same access:

```questdb-sql
CREATE GROUP analysts;
GRANT SELECT ON ALL TABLES TO analysts;

-- All users added to this group can read all tables
ADD USER alice TO analysts;
ADD USER bob TO analysts;
```

Users inherit permissions from their groups. Inherited permissions cannot be
revoked directly from the user - revoke from the group instead. When a group is
dropped, all members lose the permissions they inherited from that group.

### Authentication methods {#authentication}

[Screenshot: Diagram shows authentication and authorization flow in QuestDB](https://questdb.com/docs/images/docs/acl/auth_flow.webp)

QuestDB supports three authentication methods:

| Method             | Use case                 | Endpoints                 |
| ------------------ | ------------------------ | ------------------------- |
| **Password**       | Interactive users        | REST API, PostgreSQL Wire |
| **JWK Token**      | ILP ingestion            | InfluxDB Line Protocol    |
| **REST API Token** | Programmatic REST access | REST API                  |

Users can have multiple authentication methods enabled simultaneously:

```questdb-sql
-- Add JWK token for ILP access
ALTER USER sensor_writer CREATE TOKEN TYPE JWK;

-- Add REST API token (with 30-day expiry)
ALTER USER api_user CREATE TOKEN TYPE REST WITH TTL '30d';
```

:::warning

QuestDB does not store private keys or tokens after creation. Save them
immediately - they cannot be recovered.

:::

:::tip

Authentication should happen via a [secure TLS connection](/docs/security/tls/)
to protect credentials in transit.

:::

### Endpoint permissions

Before a user can connect, they need endpoint permissions:

| Permission | Allows access to                       |
| ---------- | -------------------------------------- |
| `HTTP`     | REST API, Web Console, ILP over HTTP   |
| `PGWIRE`   | PostgreSQL Wire Protocol (port 8812)   |
| `ILP`      | InfluxDB Line Protocol TCP (port 9009) |

```questdb-sql
-- Typical setup for an interactive user
GRANT HTTP, PGWIRE TO analyst;

-- Typical setup for an ingestion service
GRANT ILP TO ingest_service;
```

### Built-in admin

Every QuestDB instance starts with a built-in admin account:

- Default username: `admin`
- Default password: `quest`

**Change these immediately in production** via `server.conf`:

```ini
acl.admin.user=your_admin_name
acl.admin.password=your_secure_password
```

The built-in admin has irrevocable root access. After creating other admin
users, disable it:

```ini
acl.admin.user.enabled=false
```

In a replicated cluster, keep in mind that the built-in admin authorizes a
[role switch](/docs/high-availability/failover/) from its own credentials,
independently of the replicated access lists. It is the break-glass account
when a `SWITCH ROLE` grant has not yet replicated to the node you need to
promote.

## Permission levels

Permissions have different granularities determining where they can be applied:

| Granularity | Can be granted at                     |
| ----------- | ------------------------------------- |
| Database    | Database only                         |
| Table       | Database or specific tables           |
| Column      | Database, tables, or specific columns |

Examples:

```questdb-sql
-- Database-level: applies to all tables
GRANT SELECT ON ALL TABLES TO user;

-- Table-level: applies to specific tables
GRANT SELECT ON trades, prices TO user;

-- Column-level: applies to specific columns
GRANT SELECT ON trades(ts, symbol, price) TO user;
```

### The GRANT option

When granting permissions, you can allow the recipient to grant that permission
to others:

```questdb-sql
GRANT SELECT ON trades TO team_lead WITH GRANT OPTION;

-- team_lead can now grant SELECT on trades to others
```

### Owner permissions {#owner-grants}

When a user creates a table, they automatically receive all permissions on it
with the GRANT option. This ownership does not persist - if revoked, they cannot
get it back without someone re-granting it.

## Advanced topics

### Permission re-adjustment {#permission-level-re-adjustment}

Database-level permissions include access to future tables. If you revoke access
to one table, QuestDB automatically converts the database-level grant to
individual table-level grants:

```questdb-sql
GRANT SELECT ON ALL TABLES TO user;  -- Database level
REVOKE SELECT ON secret_table FROM user;

-- Result: user now has table-level SELECT on all tables EXCEPT secret_table
-- Future tables will NOT be accessible
```

The same applies from table to column level:

```questdb-sql
GRANT SELECT ON trades TO user;           -- Table level
REVOKE SELECT ON trades(ssn) FROM user;   -- Revoke one column

-- Result: user has column-level SELECT on all columns EXCEPT ssn
-- Future columns will NOT be accessible
```

:::note

When dropping a table, permissions on it are preserved by default (useful if
the table is recreated). Use `DROP TABLE ... CASCADE PERMISSIONS` to also
remove all associated permissions.

:::

### Implicit timestamp permissions {#implicit-permissions}

If a user has SELECT or UPDATE on any column of a table, they automatically get
the same permission on the designated timestamp column. This ensures time-series
operations (SAMPLE BY, LATEST ON, etc.) work correctly.

### Granting on non-existent objects {#grant-verification}

You can grant permissions on tables/columns that don't exist yet:

```questdb-sql
GRANT INSERT ON future_table TO app;
-- Permission activates when future_table is created
```

Use `WITH VERIFICATION` to catch typos:

```questdb-sql
GRANT SELECT ON trdaes TO user WITH VERIFICATION;
-- Fails immediately because 'trdaes' doesn't exist
```

### Service account assumption

Users can temporarily assume a service account's permissions for debugging:

```questdb-sql
-- Grant ability to assume
GRANT ASSUME SERVICE ACCOUNT ingest_app TO developer;

-- Developer can now switch context
ASSUME SERVICE ACCOUNT ingest_app;
-- ... debug with app's permissions ...
EXIT SERVICE ACCOUNT;
```

## User management reference {#user-management}

### Creating and removing principals

```questdb-sql
-- Users
CREATE USER username WITH PASSWORD 'pwd';
DROP USER username;

-- Service accounts
CREATE SERVICE ACCOUNT appname WITH PASSWORD 'pwd';
DROP SERVICE ACCOUNT appname;

-- Groups
CREATE GROUP groupname;
DROP GROUP groupname;
```

### Managing group membership

```questdb-sql
ADD USER username TO group1, group2;
REMOVE USER username FROM group1;
```

### Managing authentication

```questdb-sql
-- Change password
ALTER USER username WITH PASSWORD 'new_pwd';

-- Remove password (disables password auth)
ALTER USER username WITH NO PASSWORD;

-- Create tokens
ALTER USER username CREATE TOKEN TYPE JWK;
ALTER USER username CREATE TOKEN TYPE REST WITH TTL '30d';
ALTER USER username CREATE TOKEN TYPE REST WITH TTL '1d' REFRESH;  -- Auto-refresh

-- Remove tokens
ALTER USER username DROP TOKEN TYPE JWK;
ALTER USER username DROP TOKEN TYPE REST;  -- Drops all REST tokens
ALTER USER username DROP TOKEN TYPE REST 'token_value_here';  -- Drop specific token
```

Removing all authentication methods (password and tokens) effectively disables
the user - they can no longer connect to the database.

### Viewing information

```questdb-sql
SHOW USERS;                    -- List all users
SHOW SERVICE ACCOUNTS;         -- List all service accounts
SHOW GROUPS;                   -- List all groups
SHOW GROUPS username;          -- List groups for a user
SHOW USER username;            -- Show auth methods for user
SHOW PERMISSIONS username;     -- Show permissions for user
```

`SHOW USERS`, `SHOW GROUPS`, and `SHOW SERVICE ACCOUNTS` also report each
entity's [memory limit](#memory-limits).

Example output from `SHOW USER`:

```
auth_type    enabled
---------    -------
Password     true
JWK Token    false
REST Token   true
```

:::note

Viewing other users' information requires `LIST USERS` (to list all) or
`USER DETAILS` (to see details) permissions. Users can always view their own
information without these permissions.

:::

## Memory limits {#memory-limits}

QuestDB Enterprise can limit the native memory tracked for a single query,
overriding the server-wide query memory limit for a specific user, group, or
service account. Use it to help prevent one tenant's runaway query from
exhausting shared memory, or to grant a trusted principal more headroom than the
default. Per-principal limits are available since QuestDB Enterprise 4.0.2; the
server-wide [workload limits](/docs/configuration/cairo-engine/#memory-limits)
they override are available since QuestDB 10.0.0.

Set a limit with [`ALTER USER`](/docs/query/sql/acl/alter-user/),
[`ALTER GROUP`](/docs/query/sql/acl/alter-group/), or
[`ALTER SERVICE ACCOUNT`](/docs/query/sql/acl/alter-service-account/):

```questdb-sql
ALTER USER john SET MEMORY LIMIT 512M;
ALTER GROUP analysts SET MEMORY LIMIT 2G;
ALTER SERVICE ACCOUNT ingest_app SET MEMORY LIMIT 1G;
ALTER USER john SET MEMORY LIMIT UNLIMITED;  -- clear the user's own limit
```

The value is a byte count or a size with a `K`, `M`, or `G` suffix. Each suffix
multiplies by 1024, so `512M` is 536870912 bytes. Setting a limit requires the
`SET MEMORY LIMIT` permission, which is included in `GRANT ALL` and held
implicitly by database admins.

:::warning

Treat `SET MEMORY LIMIT` as an administrative permission, not a self-service
one. It takes no entity name, so its holder can set the limit of any principal,
including its own. Because a per-principal limit overrides the workload limit
rather than tightening it (see [How limits resolve](#how-limits-resolve)), a
non-admin who holds it can raise its own ceiling above
`cairo.query.memory.limit.bytes`. The built-in admin is exempt only as a
target: its limit cannot be set at all.

`GRANT ALL` expands to individual permissions at the moment it is granted. A
principal granted `ALL` before upgrading to a version with this feature does
not acquire `SET MEMORY LIMIT`; only grants issued after the upgrade include it,
and no migration backfills it. Grant it explicitly to existing administrators:
`GRANT SET MEMORY LIMIT TO admins;`.

:::

### How limits resolve

QuestDB resolves the limit for a query by strict precedence. It takes the first
level that is set, not the smallest across levels:

1. **The principal's own limit.** For a user this is the user's own limit; for a
   query that assumes a service account it is the service account's limit.
2. **A group limit** (users only). When a user has no limit of its own, it
   inherits the
   most restrictive (smallest positive) limit among the groups it belongs to.
   Service accounts never inherit group limits.
3. **The workload limit.** Otherwise the server-wide
   [`cairo.query.memory.limit.bytes`](/docs/configuration/cairo-engine/#cairoquerymemorylimitbytes)
   applies.

A value of `0` (or `UNLIMITED`) means "not set" at that level, so resolution
falls through to the next one. A more specific level, when set, fully overrides
the broader one and binds even when it is larger, so a per-user or per-group
override can raise a principal's ceiling above the workload limit, not only lower
it. A user who assumes a service account takes on the service account's limit.
The built-in admin cannot be given a limit, so `ALTER USER admin SET MEMORY
LIMIT` is rejected with `Cannot set memory limit for built-in admin`, and the
admin runs under the workload limit. Size that limit with the admin's
diagnostic queries in mind.

The cap applies to the principal's queries on both the primary and replicas. The
statement itself runs on the primary only: a replica rejects it with
`replica cannot set memory limit` and receives the new value through the
replicated ACL tables. A changed limit applies to the
principal's next query, including on connections that are already open, while a
query already running keeps the limit it started with.

A query that crosses its limit fails with the same
`query memory limit exceeded [workload=QUERY, ...]` error as a breach of the
workload limit. See
[memory limits](/docs/configuration/cairo-engine/#memory-limits) for the message
format.

:::note

A limit bounds a single query. Two concurrent queries by the same principal each
run under the full limit, so the principal's aggregate usage can exceed it. The
cap guards against one runaway workload, not total concurrent usage.

:::

### What a per-principal limit covers

Per-principal limits have the same
[coverage](/docs/configuration/cairo-engine/#memory-limits) as workload limits.
They apply to tracked native allocations for:

- The principal's queries.
- Its background [`COPY ... TO`](/docs/query/sql/copy/) exports, which use the
  issuing principal's limit. Some memory used to produce the export file is not
  yet covered, and exports do not appear in `query_activity`, so their usage
  cannot be observed while they run.
- `UPDATE` on a non-WAL table, which is applied on the caller's own thread and
  acquires its own query-workload tracker.

It does not reach work that runs under an internal context rather than a
principal. That work stays bounded only by its own
[workload limit](/docs/configuration/cairo-engine/#memory-limits):

- `UPDATE` on a WAL table, the default table type, because the statement
  is applied by the WAL apply job and draws on that job's
  `cairo.wal.apply.memory.limit.bytes` budget instead. Whether a large `UPDATE`
  is capped by a `SET MEMORY LIMIT` override therefore depends on the table
  type. A WAL-table `UPDATE` that breaches the WAL apply limit suspends the
  table for every principal until
  [`ALTER TABLE RESUME WAL`](/docs/query/sql/alter-table-resume-wal/), so the
  failure is not isolated to the tenant that issued it.
- Materialized view refresh, live view refresh, and WAL apply itself. A WAL
  apply batches many principals' transactions into one tracker, so it could not
  attribute usage to a single principal in any case.
- `COPY ... FROM` imports, which acquire no memory tracker at all and are
  unaffected by either kind of limit.

:::note

An external (SSO/OIDC) user can only receive a limit by inheriting one from a
group: `ALTER USER ... SET MEMORY LIMIT` on an external user is rejected with
`Cannot set memory limit for external user`. The user receives its inherited
group limit at login. On the primary, a later `ALTER GROUP ... SET MEMORY LIMIT`
also reaches the user's open sessions at their next query. On a replica, and on
the primary after a restart or promotion, an existing external session keeps
the limit it logged in with until it reconnects.

:::

### Inspecting limits

- `SHOW USERS`, `SHOW GROUPS`, and `SHOW SERVICE ACCOUNTS` report per-principal
  limits in a `memory_limit` column, in bytes. For users, it is the user's own
  limit or, when it has none, the most restrictive of its groups'. For groups
  and service accounts, it is the entity's own limit. The column excludes the
  server-wide workload limit. `null` means the entity has no limit of its own
  and, for a user, no inherited one either, so the workload limit applies to
  its queries.
- The filtered forms `SHOW GROUPS userName` and
  `SHOW SERVICE ACCOUNTS { userName | groupName }` carry the column too, reporting each
  listed group's or service account's own limit. This shows which inherited
  limit binds for a user with no limit of its own.
- [`query_activity`](/docs/query/functions/meta/#query_activity) exposes the
  effective limit and live usage of each running query through its `memory_limit`
  and `memory_used` columns.
- `SHOW USER` does not carry the column. The unfiltered `SHOW USERS`,
  `SHOW GROUPS`, and `SHOW SERVICE ACCOUNTS` require `LIST USERS`; the filtered
  forms require `USER DETAILS` unless the caller names itself or one of its own
  groups. A user without `LIST USERS` can still read its own effective cap from the
  `memory_limit` column of `query_activity`, which always lists the caller's
  own queries.
- The stored value is persisted on the `sys.acl_entities` system table. That
  table is protected: only the built-in admin can read it, and an ACL principal
  holding `DATABASE ADMIN` is still denied.

### Upgrading {#memory-limit-upgrade}

:::warning Breaking change

`memory_limit` is appended as the last column of `SHOW USERS`, `SHOW GROUPS`,
and `SHOW SERVICE ACCOUNTS`, including their filtered forms, whether or not any
limit is set. `SELECT *` on `sys.acl_entities` returns one more column as well.
Clients that read these results by position must be updated; clients that read
by column name are unaffected.

:::

The `SHOW` results carry the column from the first start of the upgraded
binary, reading `null` until limits are set. The `sys.acl_entities` column that
stores the value is added by an automatic migration when an upgraded node first
starts as a primary or is promoted from replica to primary. Persisted principal
limits are not enforced until the migration has been applied. The migration passes
through two windows, and each refuses a different set of statements:

- Before the column exists, a `SET MEMORY LIMIT` with a non-zero size is refused
  with:

  ```
  Cannot modify ACL entities: the memory_limit column has not been migrated in yet; retry shortly, or restart the node if it persists
  ```

  `SET MEMORY LIMIT UNLIMITED` and `ALTER ... ENABLE` or `DISABLE` still
  succeed.

- Once the column has been added but WAL apply has not yet reached it,
  `SET MEMORY LIMIT UNLIMITED` and `ALTER ... ENABLE` or `DISABLE` are refused
  as well, so that a stale in-memory value cannot overwrite a stored limit. A
  non-zero `SET MEMORY LIMIT` keeps the first message; the others read:

  ```
  Cannot set memory limit while the ACL memory_limit column migration is still being applied, retry [name=john]
  ```

  or `Cannot enable while ...` and `Cannot disable while ...` for a status
  change.

The window normally closes on its own once WAL apply catches up, so retry the
statement first. If the error persists, restart the node: the migration runs
again at startup.

## Permissions reference {#permissions}

Use `all_permissions()` to see all available permissions:

```questdb-sql
SELECT * FROM all_permissions();
```

<details>
<summary>Full permissions table (click to expand)</summary>

### Database permissions

| Permission                | Level                               | Description                             |
| ------------------------- | ----------------------------------- | --------------------------------------- |
| ADD COLUMN                | Database &#124; Table               | Add columns to tables                   |
| ADD INDEX                 | Database &#124; Table &#124; Column | Add index on symbol columns             |
| ALTER COLUMN CACHE        | Database &#124; Table &#124; Column | Enable/disable symbol caching           |
| ALTER COLUMN TYPE         | Database &#124; Table &#124; Column | Change column types                     |
| ATTACH PARTITION          | Database &#124; Table               | Attach partitions                       |
| BACKUP DATABASE           | Database                            | Create database backups                 |
| CANCEL ANY COPY           | Database                            | Cancel COPY operations                  |
| CREATE TABLE              | Database                            | Create tables                           |
| CREATE MATERIALIZED VIEW  | Database                            | Create materialized views               |
| CREATE LIVE VIEW          | Database                            | Create live views                       |
| DEDUP ENABLE              | Database &#124; Table               | Enable deduplication                    |
| DEDUP DISABLE             | Database &#124; Table               | Disable deduplication                   |
| DETACH PARTITION          | Database &#124; Table               | Detach partitions                       |
| DISABLE STORAGE POLICY    | Database &#124; Table               | Disable storage policies                |
| DROP COLUMN               | Database &#124; Table &#124; Column | Drop columns                            |
| DROP INDEX                | Database &#124; Table &#124; Column | Drop indexes                            |
| DROP PARTITION            | Database &#124; Table               | Drop partitions                         |
| DROP TABLE                | Database &#124; Table               | Drop tables                             |
| DROP MATERIALIZED VIEW    | Database &#124; Table               | Drop materialized views                 |
| DROP LIVE VIEW            | Database &#124; Table               | Drop live views                         |
| ENABLE STORAGE POLICY     | Database &#124; Table               | Enable storage policies                 |
| INSERT                    | Database &#124; Table               | Insert data                             |
| REFRESH MATERIALIZED VIEW | Database &#124; Table               | Refresh materialized views              |
| REINDEX                   | Database &#124; Table &#124; Column | Reindex columns                         |
| REMOVE STORAGE POLICY     | Database &#124; Table               | Remove storage policies                 |
| RENAME COLUMN             | Database &#124; Table &#124; Column | Rename columns                          |
| RENAME TABLE              | Database &#124; Table               | Rename tables                           |
| RESUME WAL                | Database &#124; Table               | Resume WAL processing                   |
| SELECT                    | Database &#124; Table &#124; Column | Read data                               |
| SET STORAGE POLICY        | Database &#124; Table               | Set storage policies                    |
| SET TABLE PARAM           | Database &#124; Table               | Set table parameters                    |
| SET TABLE TYPE            | Database &#124; Table               | Change table type                       |
| SETTINGS                  | Database                            | Change instance settings in Web Console |
| SNAPSHOT                  | Database                            | Create snapshots                        |
| SQL ENGINE ADMIN          | Database                            | List/cancel running queries             |
| SWITCH ROLE               | Database                            | Switch the replication role, read SWITCH STATUS |
| SYSTEM ADMIN              | Database                            | System functions (reload_tls, etc.)     |
| TRUNCATE TABLE            | Database &#124; Table               | Truncate tables                         |
| UPDATE                    | Database &#124; Table &#124; Column | Update data                             |
| VACUUM TABLE              | Database &#124; Table               | Reclaim storage                         |

### User management permissions

| Permission             | Description                             |
| ---------------------- | --------------------------------------- |
| ADD EXTERNAL ALIAS     | Create external group mappings          |
| ADD PASSWORD           | Set user passwords                      |
| ADD USER               | Add users to groups                     |
| CREATE GROUP           | Create groups                           |
| CREATE JWK             | Create JWK tokens                       |
| CREATE REST TOKEN      | Create REST API tokens                  |
| CREATE SERVICE ACCOUNT | Create service accounts                 |
| CREATE USER            | Create users                            |
| DISABLE USER           | Disable users                           |
| DROP GROUP             | Drop groups                             |
| DROP JWK               | Drop JWK tokens                         |
| DROP REST TOKEN        | Drop REST API tokens                    |
| DROP SERVICE ACCOUNT   | Drop service accounts                   |
| DROP USER              | Drop users                              |
| ENABLE USER            | Enable users                            |
| LIST USERS             | List users/groups/service accounts      |
| REMOVE EXTERNAL ALIAS  | Remove external group mappings          |
| REMOVE PASSWORD        | Remove passwords                        |
| REMOVE USER            | Remove users from groups                |
| SET MEMORY LIMIT       | Set memory limits on users, groups, and service accounts |
| USER DETAILS           | View user/group/service account details |

### Special permissions

| Permission     | Description                                                           |
| -------------- | --------------------------------------------------------------------- |
| ALL            | All permissions at the granted level (database/table/column)          |
| DATABASE ADMIN | All permissions including future ones; can assume any service account |

A few operations are reserved for database administrators and are not grantable as permissions at all:

| Operation                                                               | Notes                                                                                       |
| ----------------------------------------------------------------------- | ------------------------------------------------------------------------------------------- |
| [`ALTER TABLE REBASE WAL`](/docs/query/sql/alter-table-rebase-wal/)     | Rebuilds a suspended WAL table under a fresh sequencer                                      |
| [`SWITCH COLD STORAGE ROLE`](/docs/query/sql/switch-cold-storage-role/) | Moves the [cold storage](/docs/concepts/cold-storage/) manager role, including with `FORCE` |

By contrast, [`SWITCH ROLE`](/docs/query/sql/switch-role/), which moves the
replication role, is an ordinary grantable permission.

</details>

## SQL commands reference

- [ADD USER](/docs/query/sql/acl/add-user/)
- [ALTER GROUP](/docs/query/sql/acl/alter-group/)
- [ALTER SERVICE ACCOUNT](/docs/query/sql/acl/alter-service-account/)
- [ALTER USER](/docs/query/sql/acl/alter-user/)
- [ASSUME SERVICE ACCOUNT](/docs/query/sql/acl/assume-service-account/)
- [CREATE GROUP](/docs/query/sql/acl/create-group/)
- [CREATE SERVICE ACCOUNT](/docs/query/sql/acl/create-service-account/)
- [CREATE USER](/docs/query/sql/acl/create-user/)
- [DROP GROUP](/docs/query/sql/acl/drop-group/)
- [DROP SERVICE ACCOUNT](/docs/query/sql/acl/drop-service-account/)
- [DROP USER](/docs/query/sql/acl/drop-user/)
- [EXIT SERVICE ACCOUNT](/docs/query/sql/acl/exit-service-account/)
- [GRANT](/docs/query/sql/acl/grant/)
- [GRANT ASSUME SERVICE ACCOUNT](/docs/query/sql/acl/grant-assume-service-account/)
- [REMOVE USER](/docs/query/sql/acl/remove-user/)
- [REVOKE](/docs/query/sql/acl/revoke/)
- [REVOKE ASSUME SERVICE ACCOUNT](/docs/query/sql/acl/revoke-assume-service-account/)
- [SHOW USER](/docs/query/sql/show/#show-user)
- [SHOW USERS](/docs/query/sql/show/#show-users)
- [SHOW GROUPS](/docs/query/sql/show/#show-groups)
- [SHOW SERVICE ACCOUNT](/docs/query/sql/show/#show-service-account)
- [SHOW SERVICE ACCOUNTS](/docs/query/sql/show/#show-service-accounts)
- [SHOW PERMISSIONS](/docs/query/sql/show/#show-permissions-for-current-user)