> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/v1/resources/nilus/roles-and-permissions/postgresql-minimum-permissions.md).

# PostgreSQL

This page is a provisioning reference for platform and database administrators. It lists the minimum PostgreSQL grants required to run each Nilus pipeline mode against a PostgreSQL depot.

## Pipeline modes supported

| Pipeline type    | URI prefix               | Description                                       |
| ---------------- | ------------------------ | ------------------------------------------------- |
| `type: batch`    | `postgresql://`          | Full or incremental reads and writes              |
| `type: cdc`      | `debezium+postgresql://` | Log-based change capture via WAL using `pgoutput` |
| `type: metadata` | `metadata+postgresql://` | Hera metadata ingestion                           |

## Read data: PostgreSQL as source (`type: batch`)

Nilus uses this pattern when the depot is referenced in `source.address` of a pipeline configuration. It issues `SELECT` queries, either a full scan or an incremental read using a cursor column, against the target table.

### Minimum grants

```sql
-- Database access
GRANT CONNECT ON DATABASE <database_name> TO <user>;

-- Schema access
GRANT USAGE ON SCHEMA <schema_name> TO <user>;

-- Table read: existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user>;

-- Table read: future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
    GRANT SELECT ON TABLES TO <user>;
```

{% hint style="info" %}
These grants cover every table in the schema. To restrict access to a single table, replace `ALL TABLES IN SCHEMA` with `TABLE <table_name>`.
{% endhint %}

## CDC source: PostgreSQL (`type: cdc`)

### Server-side prerequisite (one-time, requires superuser or admin)

Nilus requires logical replication to be enabled on the PostgreSQL server before it can capture changes through Debezium. Enabling logical replication is a server-level configuration change: it requires a full restart of the PostgreSQL server and cannot be applied at the session level.

```properties
# postgresql.conf: requires a server restart
wal_level = logical
```

{% hint style="warning" %}
Verify the current setting with `SHOW wal_level;` before changing it. Coordinate this change with the database administrator, since `wal_level` affects the entire PostgreSQL server, not only the Nilus pipeline user.
{% endhint %}

### Minimum grants

```sql
-- User must have LOGIN and REPLICATION attributes
CREATE USER cdc_user WITH LOGIN REPLICATION PASSWORD '<password>';

-- SELECT on captured tables (required during the initial snapshot phase)
GRANT CONNECT ON DATABASE <database_name> TO cdc_user;
GRANT USAGE ON SCHEMA <schema_name> TO cdc_user;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO cdc_user;

-- CREATE on the database (required if Nilus auto-creates the publication)
GRANT CREATE ON DATABASE <database_name> TO cdc_user;
```

If a superuser or DBA pre-creates the publication instead of letting Nilus create it, the connector still needs to alter or manage the publication during CDC operation. Grant this by assigning ownership of the publication to the CDC user, or by granting `CREATE` on the database.

{% hint style="info" %}
To pre-create the publication instead of granting `CREATE ON DATABASE`, run:

```sql
CREATE PUBLICATION nilus_pub FOR TABLE <schema_name>.<table_name>;
ALTER PUBLICATION nilus_pub OWNER TO cdc_user;
```

{% endhint %}

## Write data: PostgreSQL as destination (`type: batch`, `type: cdc`)

Nilus uses this pattern when the depot is referenced in `sink.address` of a pipeline configuration. It manages table creation and data loading.

### Minimum grants (covers all strategies)

```sql
-- Database access
GRANT CONNECT ON DATABASE <database_name> TO <user>;

-- Schema access
GRANT USAGE ON SCHEMA <schema_name> TO <user>;

-- Schema create (required if Nilus auto-creates the destination table)
GRANT CREATE ON SCHEMA <schema_name> TO <user>;

-- DML on existing tables
GRANT INSERT, UPDATE, DELETE, SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user>;

-- DML on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
    GRANT INSERT, UPDATE, DELETE, SELECT ON TABLES TO <user>;
```

### Grants by incremental strategy

| Strategy  | Required privileges                                                                 |
| --------- | ----------------------------------------------------------------------------------- |
| `append`  | `CONNECT`, `USAGE`, `INSERT`, `CREATE` (if new table)                               |
| `merge`   | `CONNECT`, `USAGE`, `INSERT`, `UPDATE`, `DELETE`, `SELECT`, `CREATE` (if new table) |
| `replace` | `CONNECT`, `USAGE`, `INSERT`, `TRUNCATE`, `CREATE` (if new table)                   |

## Hera metadata ingestion: PostgreSQL source connector (`type: metadata`)

Nilus uses this pattern when the depot is referenced in `source.address` of a `type: metadata` pipeline. Nilus drives the Hera PostgreSQL source connector.

### Basic metadata (tables, schemas, columns, views)

```sql
GRANT CONNECT ON DATABASE <database_name> TO <user>;
GRANT USAGE ON SCHEMA <schema_name> TO <user>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
    GRANT SELECT ON TABLES TO <user>;
```

### Usage and lineage: the `pg_stat_statements` extension

Hera reads query history from the `pg_stat_statements` extension to build lineage and usage statistics. This requires a one-time admin setup and an additional user grant.

**Step 1. Enable the extension (admin, requires server restart).**

```properties
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000          # increase from default 5000 to reduce eviction
pg_stat_statements.track = 'all'        # captures queries inside functions and procedures
```

**Step 2. Create the extension and grant access (run as superuser after restart).**

```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Grant read access to the ingestion user
GRANT pg_read_all_stats TO <user>;
```

{% hint style="info" %}
`pg_stat_statements` is an in-memory statistics collector, not a persistent query log:

* Queries are deduplicated by shape (literal values are replaced with `$1`, `$2`, and so on).
* No timestamps are stored, only cumulative call counts since the last reset.
* Entries are silently evicted when the hash table is full.

For the best coverage, set `pg_stat_statements.max` to `10000` or higher, and schedule frequent Hera ingestion runs (every 1-2 hours) to capture queries before eviction.
{% endhint %}

### Stored procedure lineage (additional postgresql.conf change)

To capture SQL executed inside stored procedures:

```properties
# postgresql.conf
log_statement = 'all'
pg_stat_statements.track = 'all'
```

No additional SQL grants are needed beyond `pg_read_all_stats`.

### Additional grants by workflow type

| Workflow                     | Requirement                                                                       | What it needs                |
| ---------------------------- | --------------------------------------------------------------------------------- | ---------------------------- |
| **Basic metadata**           | SQL grants above                                                                  | `CONNECT`, `USAGE`, `SELECT` |
| **Usage & lineage**          | `pg_stat_statements` enabled + `GRANT pg_read_all_stats`                          | `pg_stat_statements` view    |
| **Stored procedure lineage** | `log_statement = 'all'` + `pg_stat_statements.track = 'all'` in `postgresql.conf` | Internal query tracking      |

## Permission matrix

| Use case              | `CONNECT` DB | `USAGE` schema | `SELECT`       | `INSERT`/`UPDATE`/`DELETE` | `CREATE` schema   | `REPLICATION` attr | `pg_read_all_stats` |
| --------------------- | ------------ | -------------- | -------------- | -------------------------- | ----------------- | ------------------ | ------------------- |
| Read (source, batch)  | Yes          | Yes            | Yes            | No                         | No                | No                 | No                  |
| CDC source            | Yes          | Yes            | Yes (snapshot) | No                         | Yes (if auto-pub) | Yes                | No                  |
| Write: `append`       | Yes          | Yes            | No             | INSERT                     | Yes (if new)      | No                 | No                  |
| Write: `merge`        | Yes          | Yes            | Yes            | Yes                        | Yes (if new)      | No                 | No                  |
| Write: `replace`      | Yes          | Yes            | No             | INSERT + TRUNCATE          | Yes (if new)      | No                 | No                  |
| Hera metadata (basic) | Yes          | Yes            | Yes            | No                         | No                | No                 | No                  |
| Hera usage / lineage  | Yes          | Yes            | Yes            | No                         | No                | No                 | Yes                 |

## Notes

* **`REPLICATION` attribute**: This is a server-level user attribute set at user creation time (`CREATE USER ... WITH REPLICATION`). It cannot be granted with a regular `GRANT` statement. A superuser or a user with `CREATEROLE` rights must create or alter the CDC user.
* **`wal_level = logical` requires a server restart**: This is a server configuration change and cannot be applied at the session level. Coordinate with the database administrator before enabling CDC on a production instance.
* **Publication ownership**: Nilus uses `pgoutput` and manages the publication lifecycle. If the publication is pre-created, assign ownership to the CDC user, or grant the CDC user `CREATE` on the database so the connector can alter the publication.
* **`ALTER DEFAULT PRIVILEGES`**: This applies only to objects created by the grantor. If multiple users create tables in the schema, repeat the statement for each table owner to keep access consistent.
* **`pg_stat_statements` is not a query log**: It evicts entries silently and stores no timestamps. Frequent ingestion scheduling is the primary mitigation.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://v2.dataos.info/references/v1/resources/nilus/roles-and-permissions/postgresql-minimum-permissions.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.