> For the complete documentation index, see [llms.txt](https://docs.prestd.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.prestd.com/get-started/permissions.md).

# Permissions

How to manage tables read/writes

This guide will allow you to understand how `pREST` to understand and manage your table's permission management and how you can tailor it to your needs by using the `prest.toml` file.

## Restrict mode

The `prest.toml` file allows you to configure each table's read/write/delete permissions.

```toml
[access]
restrict = true  # can access only the tables listed below
```

`restrict = false`: (default) The pREST will serve in public mode. You can write/read/delete every data without configuring permissions.

`restrict = true`: you need to configure the permissions of all tables.

The same rules apply to `/show/{database}/{schema}/{table}` and the catalog routes from v2.5.2: with `restrict = true`, a table not granted `read` is **401** there as it is on CRUD ([GHSA-fhf5-m388-4w4p](https://github.com/prest/prest/security/advisories/GHSA-fhf5-m388-4w4p)).

## Ignore table

If you need to ignore restricted access mode for some tables, you can use the `ignore_table` option, it receives a string list with the names of the tables to be *"ignored"*, by **default,** is an empty list `[]`.

```toml
[access]
restrict = true
ignore_table = ["news"]
```

## Unscoped writes (v2.5.3)

From v2.5.3 ([GHSA-r867-ghgv-mrp9](https://github.com/prest/prest/security/advisories/GHSA-r867-ghgv-mrp9)), `DELETE`, `PUT`, and `PATCH` on `/{database}/{schema}/{table}` need at least one filter in the query string. Without one there is no SQL `WHERE`, so the statement would hit every row in the table. The request is refused before it runs:

```
DELETE /db/public/orders
```

```json
{"error":"refusing DELETE without a WHERE condition: add a filter query parameter"}
```

* The status is **400**, with `PUT` or `PATCH` in the message for those methods.
* This applies whatever `restrict` is set to.
* Parameters such as `_returning` or `_page` are not filters. `DELETE /db/public/orders?_returning=id` is still refused.
* To change every row on purpose, use a filter that matches every row, for example `?id=$notnull`.

To allow writes with no filter again, as before v2.5.3:

```toml
[access]
allow_unscoped_writes = true  # env: PREST_ACCESS_ALLOW_UNSCOPED_WRITES=true
```

With this on, `DELETE /db/public/orders` with no filter deletes every row in `orders`.

## Reserved tables (v2.5.2)

pREST's own control-plane tables are never reachable through data routes, whatever `restrict`, `ignore_table`, or per-user rules say — and even when auth or database-backed queries are turned off ([GHSA-2x9q-r7j9-g85f](https://github.com/prest/prest/security/advisories/GHSA-2x9q-r7j9-g85f)). The names are matched case-insensitively, in any schema:

* `prest_queries` — stored [custom queries](https://docs.prestd.com/api-reference/custom-queries.md#database-backed-storage-v220)
* `prest_users` — [auth](https://docs.prestd.com/api-reference/auth.md) users
* the configured `queries.table`
* the configured `auth.table` (`PREST_AUTH_TABLE`)

| Request                                                                       | Response                                                               |
| ----------------------------------------------------------------------------- | ---------------------------------------------------------------------- |
| Any method on `/{database}/{schema}/{reserved}`, `/batch/...`, or `/show/...` | **401** `{"error":"authorization required"}`, even with an admin token |
| `_join` that targets a reserved table                                         | **400** `{"error":"table is reserved for pREST internal use"}`         |
| MCP `select_table` / `describe_table`                                         | JSON-RPC permission error                                              |

Manage stored queries through the [`/_QUERIES/registry` API](https://docs.prestd.com/api-reference/custom-queries.md#registry-api) and users with SQL or `prestd migrate`. There is no opt-out: if an application table has one of these names, rename it.

## Table permissions

Example:

```toml
[[access.tables]]
name = "test"
permissions = ["read", "write", "delete"]
fields = ["id", "name"]
```

One rule applies per table. When several `[[access.tables]]` entries name the same table, pREST uses the most specific match (`database` + `schema`, then `schema`, then name only). If two are equally specific, **the last one wins** and the earlier one is ignored: its permissions and fields are not merged in. Put every permission for a table in one entry:

```toml
[access]
restrict = true  # can access only the tables listed below

[[access.tables]]
name = "test"
permissions = ["read", "write"]
fields = ["id", "name"]
```

{% hint style="warning" %}
Splitting a table into one `read` entry and one `write` entry does not work. With the entries below, only the `write` entry applies: `GET /db/public/test` is **401** and only `name` can be written.

```toml
[[access.tables]]
name = "test"
permissions = ["read"]
fields = ["id", "name"]

[[access.tables]]
name = "test"
permissions = ["write"]
fields = ["name"]
```

To give different callers different columns, use [user-level permissions](#user-level-permissions).
{% endhint %}

| attribute   | description                                                                                                |
| ----------- | ---------------------------------------------------------------------------------------------------------- |
| name        | Table name                                                                                                 |
| database    | Optional. Database alias when using a registry ([#973](https://github.com/prest/prest/pull/973))           |
| schema      | Optional. Schema name (default matching applies when omitted)                                              |
| permissions | Table permissions. Options: `read`, `write` and `delete`                                                   |
| fields      | Columns the rule allows for each operation it grants. See [Column permissions](#column-permissions-fields) |

## Column permissions (`fields`)

With `restrict = true`, a rule's `fields` applies to every operation listed in its `permissions`. `["*"]` allows every column. Leaving `fields` out behaves differently for reads and writes:

| Operation                                  | `fields = ["id", "name"]`                                                    | `fields = ["*"]` | No `fields`                                                                                                               |
| ------------------------------------------ | ---------------------------------------------------------------------------- | ---------------- | ------------------------------------------------------------------------------------------------------------------------- |
| `read`                                     | Only `id` and `name` can be selected, filtered, ordered, grouped, or counted | Every column     | No column: `GET` is **400** `you don't have permission for this action, please check the permitted fields for this table` |
| `write` (`POST`, `PUT`, `PATCH`, `/batch`) | The body may set only `id` and `name` (v2.5.3)                               | Every column     | Every column                                                                                                              |
| `delete`                                   | Not used: `delete` has no column list                                        | —                | —                                                                                                                         |

A matching [user-level rule](#user-level-permissions) that grants the operation replaces the table rule's `fields` for that user. With `restrict = false`, `fields` is not checked.

### Writes (v2.5.3)

From v2.5.3 ([#1072](https://github.com/prest/prest/pull/1072), [GHSA-h25q-9rmj-646h](https://github.com/prest/prest/security/advisories/GHSA-h25q-9rmj-646h)), a write body that sets a column outside `fields` is rejected before any SQL runs. Before v2.5.3, `fields` was not checked on writes at all, so a caller allowed to write `name` could also set `role` or `is_admin`.

```toml
[access]
restrict = true

[[access.tables]]
name = "accounts"
permissions = ["read", "write", "delete"]
fields = ["id", "name", "email"]
```

```
POST /db/public/accounts
{"name": "ana", "role": "admin"}
```

```json
{"error":"column \"role\" is not writable on this table"}
```

* The status is **400**. Every top-level key of the body is checked, and for `/batch` every key of every record. One disallowed key rejects the whole request, so nothing is written.
* A body that is not valid JSON is **400** `could not parse request body: ...`.
* The check applies to `POST`, `PUT`, `PATCH`, and `POST /batch/...`. The query string of a `PUT` or `PATCH` (its `WHERE` filter) is not checked against the write list.

### What write responses show (v2.5.3)

A write response shows only the columns the caller may **read**, whichever list allowed the write. This covers the row a `POST` returns, `/batch` responses, and [`_returning`](https://docs.prestd.com/api-reference/parameters.md#returning-rows-from-writes-_returning) on `PUT`, `PATCH`, and `DELETE`:

* With `read` fields `["id", "name", "email"]`, an insert returns `{"id":3,"name":"ana","email":"ana@example.com"}`. Other columns, such as a `role` filled in by a column default, are left out.
* With `read` fields `["*"]`, every column is returned.
* A rule that grants `write` without `read` gets `{}` back, so a write-only caller cannot read back what the database filled in.

### Reads: every referenced column is checked (v2.5.3)

From v2.5.3 ([#1083](https://github.com/prest/prest/pull/1083), [GHSA-48r9-g6f9-x3gm](https://github.com/prest/prest/security/advisories/GHSA-48r9-g6f9-x3gm)), every column a read request names must be readable, not only the selected ones. Before, a caller could filter or sort on a hidden column, or `_count` it, and work out its values without ever selecting it.

These parameters are checked:

* Filters, including JSONB, full-text (`:tsquery`), and vector distance (`:vecdist`) filters, and [`_or`](https://docs.prestd.com/api-reference/parameters.md#or-clause-filtering)
* `_order`, and the column of `_korder`
* `_count`
* `_groupby`: the grouped columns, the `HAVING` aggregate's column, and the column arguments of functions such as `lower(name)`
* `_time_bucket` on TimescaleDB

```
GET /db/public/accounts?salary=$gt.1000
```

```json
{"error":"column \"salary\" is not readable on this table"}
```

* The status is **403**, and the request does not run.
* `_count=*` names no column, so it is allowed.
* On a [JOIN](#joins-and-restrict-v250), `table.column` is checked against that table's rule. If the queried table allows `["*"]`, a bare column name must also be readable on every joined table, because SQL resolves it to whichever joined table has that column.
* `_select` keeps its existing behavior: columns you may not read are dropped silently, and the request is **400** only when none are left.

### Per-database permissions

When a [database registry](https://docs.prestd.com/get-started/multi-database.md) is active, scope permissions to a specific alias and schema:

```toml
[[access.tables]]
database = "tenant-a"
schema = "public"
name = "users"
permissions = ["read"]
fields = ["id", "name"]
```

Permissions are matched against **alias + schema + table name** when the registry is configured. When `database` is omitted, the rule applies to all aliases.

## JOINs and restrict (v2.5.0)

From v2.5.0 ([#1032](https://github.com/prest/prest/pull/1032), [#1033](https://github.com/prest/prest/pull/1033), fixes [#364](https://github.com/prest/prest/issues/364)), a [`_join`](https://docs.prestd.com/api-reference/advanced-queries.md#join) is checked table by table. With `restrict = true`:

* **The queried table** must be readable, or the request is **401** `{"error":"authorization required"}`.
* **Each joined table** goes through the same `[[access.tables]]` and `[[access.users.tables]]` rules for the operation. A readable table adds its permitted `fields`, qualified as `table.column`, or `table.*` when its fields are `["*"]` or it is listed in `ignore_table`.
* **A joined table you cannot read adds no columns, but the JOIN still runs** — it still filters which rows come back.
* **Without `_select`**, the response carries the queried table's permitted fields plus every permitted joined table's fields.
* **With `_select`**, a bare name means the queried table; a joined column must be written `table.column` (or `table.*`). Entries you are not permitted to read are dropped silently. If none remain, the request is **400** `you don't have permission for this action, please check the permitted fields for this table`.

With `restrict = false`, `_select` passes through unchanged. In both modes, joining the queried table itself or the same table twice is **400**, and joining a [reserved table](#reserved-tables-v252) is **400**.

Example:

```toml
[access]
restrict = true

[[access.tables]]
name = "friends"
permissions = ["read"]
fields = ["id", "userid"]

[[access.tables]]
name = "users"
permissions = ["read"]
fields = ["id", "name"]

# no entry for "salaries"
```

```
GET /prest/public/friends?_join=inner:users:friends.userid:$eq:users.id&_join=inner:salaries:users.id:$eq:salaries.user_id
```

Returns `friends.id`, `friends.userid`, `users.id`, and `users.name` — only for friends whose user has a `salaries` row. No `salaries` column is returned.

## User-level permissions

v2 supports per-user table permissions via `[[access.users]]`. The authenticated user's identity (from the JWT `sub` claim or username) is matched against `access.users.name`.

User-level table permissions restrict or extend the global `access.tables` rules for that specific user.

Example:

```toml
[access]
restrict = true

[[access.tables]]
name = "read_table"
permissions = ["read"]
fields = ["id", "name", "age", "gender"]

[[access.users]]
name = "foo_read"
[[access.users.tables]]
name = "read_table"
permissions = ["read"]
fields = ["id", "name"]
```

In this example, user `foo_read` can only read the `id` and `name` fields on `read_table`, even if the global table permission allows more fields.

For a comprehensive example with multiple users and permission combinations, see [testdata/prest.toml](https://github.com/prest/prest/blob/main/testdata/prest.toml).

MCP (`/_mcp`, v2.1.0+) inherits the same ACL rules as REST table routes — read tools only see tables and fields your access config allows. See [MCP over HTTP](https://docs.prestd.com/get-started/mcp-over-http.md).

## Example configuration

Configuration example: [prest.toml](https://github.com/prest/prest/blob/main/testdata/prest.toml)

```toml
[auth]
table = "prest_users"  # reserved: not reachable through REST, _join, or MCP (v2.5.2)
username = "username"
password = "password"
metadata = ["first_name", "last_name", "last_login"]

[http]
port = 3000

[cache]
enabled = true

    [[cache.endpoints]]
    endpoint = "/prest/public/test"
    time = 5

[access]
restrict = true  # can access only the tables listed below

    [[access.tables]]
    name = "Reply"
    permissions = ["read", "write", "delete"]
    fields = ["id", "name"]

    [[access.tables]]
    name = "test"
    permissions = ["read", "write", "delete"]
    fields = ["id", "name"]

    [[access.tables]]
    name = "testarray"
    permissions = ["read", "write", "delete"]
    fields = ["id", "data"]

    [[access.tables]]
    name = "test2"
    permissions = ["read", "write", "delete"]
    fields = ["id", "name"]

    [[access.tables]]
    name = "test3"
    permissions = ["read", "write", "delete"]
    fields = ["id", "name"]

    [[access.tables]]
    name = "test4"
    permissions = ["read", "write", "delete"]
    fields = ["id", "name"]

    [[access.tables]]
    name = "test5"
    permissions = ["read", "write", "delete"]
    fields = ["*"]

    [[access.tables]]
    name = "test_readonly_access"
    permissions = ["read"]
    fields = ["id", "name"]

    [[access.tables]]
    name = "test_write_and_delete_access"
    permissions = ["write", "delete"]

    [[access.tables]]
    name = "test_list_only_id"
    permissions = ["read"]
    fields = ["id"]

    [[access.tables]]
    name = "test6"
    permissions = ["read", "write", "delete"]
    fields = ["nuveo", "name"]

    [[access.tables]]
    name = "view_test"
    permissions = ["read"]
    fields = ["player"]

    [[access.tables]]
    name = "test_group_by_table"
    permissions = ["read"]
    fields = ["id", "name", "age", "salary"]
```

## Responses

| Situation                                                                              | Response                                                                                              |
| -------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------- |
| Table not granted for the operation (`restrict = true`)                                | **401** `{"error":"authorization required"}`                                                          |
| Reserved table on CRUD, `/batch`, or `/show` (v2.5.2)                                  | **401** `{"error":"authorization required"}`                                                          |
| `_join` to a reserved table (v2.5.2)                                                   | **400** `{"error":"table is reserved for pREST internal use"}`                                        |
| `_select` with no permitted column left, or no `read` fields configured                | **400** `you don't have permission for this action, please check the permitted fields for this table` |
| Filter, `_or`, `_order`, `_count`, or `_groupby` on a column you may not read (v2.5.3) | **403** `{"error":"column \"<name>\" is not readable on this table"}`                                 |
| Write body sets a column outside the `write` fields (v2.5.3)                           | **400** `{"error":"column \"<name>\" is not writable on this table"}`                                 |
| `DELETE`, `PUT`, or `PATCH` with no filter (v2.5.3)                                    | **400** `{"error":"refusing DELETE without a WHERE condition: add a filter query parameter"}`         |
| Same table joined twice, or joined to itself                                           | **400** `table "<name>" is joined more than once: invalid join clause`                                |

## Related

* [Advanced queries — JOIN](https://docs.prestd.com/api-reference/advanced-queries.md#join)
* [v2.5.3 release notes](https://docs.prestd.com/releases/v2.5.3.md)
* [v2.5.2 release notes](https://docs.prestd.com/releases/v2.5.2.md)
* [Multi-database](https://docs.prestd.com/get-started/multi-database.md)
* [MCP over HTTP](https://docs.prestd.com/get-started/mcp-over-http.md)
* [Auth](https://docs.prestd.com/api-reference/auth.md)
* [Acronyms](https://docs.prestd.com/readme/acronyms.md) · [ACL](https://docs.prestd.com/readme/acronyms.md#acl) · [MCP](https://docs.prestd.com/readme/acronyms.md#mcp)
