> ## Documentation Index
> Fetch the complete documentation index at: https://docs.rootly.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Private Agent for PostgreSQL and MySQL (Early Access)

> Investigate private PostgreSQL and MySQL databases with bounded diagnostics and read-only SQL.

<Warning>
  **Early Preview:** Rootly Private Agent is under active development and available only to approved customers. Features, configuration, limits, and APIs may change before general availability. Confirm the approved agent, backend, and chart versions with your Rootly representative before production use.
</Warning>

Rootly Private Agent connects AI SRE to PostgreSQL and MySQL instances reachable from inside your network. Each configured instance becomes an independently routed provider. The adapter performs live investigation queries only: it does not continuously collect database telemetry, install an extension, retain an offline cache, or replace a database monitoring product.

PostgreSQL and MySQL share aligned diagnostic tool names where the engines expose equivalent data. Engine-specific catalog queries stay inside the agent. Configure every database with a stable provider `id` that is unique across all providers in your Rootly account and identifies its environment and role, such as `postgresql-checkout-production`.

## Create a dedicated read-only user

Always use a dedicated database identity. Do not configure an application owner, migration user, administrator, PostgreSQL superuser, or MySQL account with write privileges. The examples below are a starting point; narrow them further to the schemas and system views your investigation policy needs.

### PostgreSQL

```sql theme={null}
SET password_encryption = 'scram-sha-256';
CREATE USER rootly_agent WITH PASSWORD '<generated-secret>'
  NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT CONNECT ON DATABASE production TO rootly_agent;
GRANT USAGE ON SCHEMA app TO rootly_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO rootly_agent;
ALTER DEFAULT PRIVILEGES FOR ROLE <table_owner> IN SCHEMA app GRANT SELECT ON TABLES TO rootly_agent;
```

Run the `ALTER DEFAULT PRIVILEGES` statement as an administrator for every role that creates tables in `app`, replacing `<table_owner>` with that role. PostgreSQL applies default privileges only to objects later created by the named role; existing tables still need the explicit `GRANT SELECT` above.

PostgreSQL grants `TEMPORARY` on each database and `EXECUTE` on newly created routines to `PUBLIC` by default. PostgreSQL 14 databases created with the historical defaults may also grant `CREATE` on the `public` schema. Before enabling `execute_sql`, verify all three paths for the agent identity:

```sql theme={null}
SELECT has_database_privilege('rootly_agent', 'production', 'TEMPORARY');
SELECT has_schema_privilege('rootly_agent', 'public', 'CREATE');
SELECT p.oid::regprocedure AS executable_routine
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND has_schema_privilege('rootly_agent', n.oid, 'USAGE')
  AND has_function_privilege('rootly_agent', p.oid, 'EXECUTE');
```

The two Boolean checks must return `false`, and the routine query must return no rows. If they do not, either leave `execute_sql` disabled or tighten the shared defaults. The following changes affect every database user, so first identify legitimate application roles and grant those privileges back explicitly:

```sql theme={null}
REVOKE TEMPORARY ON DATABASE production FROM PUBLIC;
GRANT TEMPORARY ON DATABASE production TO <legitimate_temp_role>;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO <legitimate_creator_role>;

REVOKE EXECUTE ON ALL ROUTINES IN SCHEMA <routine_schema> FROM PUBLIC;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA <routine_schema> TO <legitimate_runtime_role>;

ALTER DEFAULT PRIVILEGES FOR ROLE <routine_owner> IN SCHEMA <routine_schema>
  REVOKE EXECUTE ON ROUTINES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE <routine_owner> IN SCHEMA <routine_schema>
  GRANT EXECUTE ON ROUTINES TO <legitimate_runtime_role>;
```

Replace `<routine_schema>` and repeat the routine statements for every non-system schema the agent can use, including `app` and `public`. Run the default-privilege statements for every role that creates routines in each schema. They protect future routines only; the `REVOKE ... ON ALL ROUTINES` statement handles existing ones. Recheck the agent privileges after every grant or ownership change.

PostgreSQL diagnostic tools also read standard `pg_catalog` and `pg_stat_*` views available to ordinary users. PostgreSQL may redact another session's query text unless you deliberately grant an additional monitoring role. Start with the least privilege above and add `pg_monitor` only if your investigation requirements justify the wider session visibility.

### MySQL

```sql theme={null}
CREATE USER 'rootly_agent'@'10.%'
  IDENTIFIED WITH caching_sha2_password BY '<generated-secret>' REQUIRE SSL;
GRANT SELECT, SHOW VIEW ON app.* TO 'rootly_agent'@'10.%';
GRANT SELECT ON performance_schema.* TO 'rootly_agent'@'10.%';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'rootly_agent'@'10.%';
```

Restrict the MySQL host pattern to the agent's actual network range. `PROCESS`, `REPLICATION CLIENT`, and selected Performance Schema views support session, wait, query-pattern, lock, and replication diagnostics. On a shared MySQL instance, `PROCESS` plus Performance Schema access can expose session, wait, and query details for schemas and users outside `app.*`; the application-schema allowlist does not narrow that instance-level visibility. Remove those grants, and disable the corresponding diagnostics, when that visibility is not acceptable.

## Configure providers

Store the username and optional password in a Kubernetes Secret and mount them as files. Never put credentials in Helm values or a connection URL.

```yaml theme={null}
providers:
  postgresql:
    - id: postgresql-checkout-production
      host: postgres.production.internal
      port: 5432
      database: production
      username_file: /run/secrets/postgresql/username
      password_file: /run/secrets/postgresql/password
      ca_bundle_file: /run/secrets/postgresql/ca.crt
      allowed_schemas:
        - app
      execute_sql_enabled: true
      policy:
        maximum_concurrency: 2
        maximum_rows: 200
        maximum_result_bytes: 262144
        maximum_query_bytes: 16384
        maximum_timeout_seconds: 15

  mysql:
    - id: mysql-billing-production
      host: mysql.production.internal
      port: 3306
      database: app
      username_file: /run/secrets/mysql/username
      password_file: /run/secrets/mysql/password
      ca_bundle_file: /run/secrets/mysql/ca.crt
      allowed_schemas:
        - app
      execute_sql_enabled: false

podSecurityContext:
  fsGroup: 65532

extraVolumes:
  - name: postgresql-credentials
    secret:
      secretName: rootly-private-agent-postgresql
      defaultMode: 0440
  - name: mysql-credentials
    secret:
      secretName: rootly-private-agent-mysql
      defaultMode: 0440

extraVolumeMounts:
  - name: postgresql-credentials
    mountPath: /run/secrets/postgresql
    readOnly: true
  - name: mysql-credentials
    mountPath: /run/secrets/mysql
    readOnly: true
```

Each Secret in this example contains keys named `username`, `password`, and `ca.crt`.

TLS with server certificate validation is required by default. `ca_bundle_file` is mandatory for both private- and public-CA certificates and must contain the trust chain needed to validate the server; the provider does not fall back to the image's system trust store. Set `server_name` when the certificate identity differs from `host`. `client_certificate_file` and `client_key_file` enable mTLS. `allow_insecure: true` disables TLS and is intended only for isolated local tests.

Supported authentication and transport combinations are:

| Engine     | Password authentication | TLS                                             | Client certificate authentication                                |
| ---------- | ----------------------- | ----------------------------------------------- | ---------------------------------------------------------------- |
| PostgreSQL | SCRAM-SHA-256           | Private or public CA with hostname verification | PostgreSQL `cert` authentication; `password_file` may be omitted |
| MySQL      | `caching_sha2_password` | Private or public CA with hostname verification | `REQUIRE X509` with the configured password                      |

The provider uses a fixed TCP host and port. Unix sockets, multi-host connection strings, legacy password plugins, interactive authentication, and automatic cloud-IAM token refresh are not supported in this early preview. An SSH tunnel, private network path, or managed-database proxy can still be used when it presents a stable TCP endpoint and the configured TLS identity.

Credential and TLS paths must be absolute and resolve to regular files. The agent image runs as UID/GID `65532`; ensure mounted files are readable by that identity without making them world-readable.

## Local scope and limits

`allowed_schemas` is mandatory and is enforced for typed catalog diagnostics, such as table health, index health, and object search. It does not inspect or restrict arbitrary SQL. The model cannot change the configured host, database, or credentials, but SQL can access anything granted to that fixed database identity.

The configured database identity is the authorization boundary for SQL. Create a dedicated read-only user and grant it access only to the data you want Rootly to investigate. If you configure a write-capable identity, an AI SRE tool call can use those privileges.

`maximum_result_bytes` caps the encoded tool result returned to Rootly after the database driver has decoded each cell. It does not prevent the database or driver from materializing one large value first. Keep the read-only identity scoped to bounded investigative views, apply database-side resource controls where appropriate, and size the agent container for the queries that identity can run.

See [Private Agent Limits](/private-agent-limits#postgresql-and-mysql) for defaults and hard ceilings.

## Tools

Rootly treats typed database diagnostics as sensitive reads. `execute_sql` is classified with write-risk metadata because the agent does not parse SQL, but it does not pause an investigation for interactive approval. It is opt-in, its tool description directs AI SRE to use only read-only `SELECT`, `SHOW`, or non-executing `EXPLAIN` statements, and the database identity must enforce read-only access.

| Capability suffix    | PostgreSQL           | MySQL                                 | Arguments                                                                            |
| -------------------- | -------------------- | ------------------------------------- | ------------------------------------------------------------------------------------ |
| `database_health`    | Yes                  | Yes                                   | None                                                                                 |
| `session_activity`   | Yes                  | Yes                                   | Optional `limit`, `timeout_seconds`                                                  |
| `lock_waits`         | Yes                  | Yes                                   | Optional `limit`, `timeout_seconds`                                                  |
| `wait_events`        | Yes                  | Yes                                   | Optional `limit`, `timeout_seconds`                                                  |
| `top_queries`        | Live active sessions | Cumulative Performance Schema digests | Optional `limit`, `timeout_seconds`                                                  |
| `table_health`       | Yes                  | Yes                                   | Optional `schema`, `relation`, `limit`, `timeout_seconds`                            |
| `index_health`       | Yes                  | Yes                                   | Optional `schema`, `relation`, `limit`, `timeout_seconds`                            |
| `replication_status` | Yes                  | Yes                                   | Optional `limit`, `timeout_seconds`                                                  |
| `search_objects`     | Yes                  | Yes                                   | Search table and view names; required `pattern`; optional `limit`, `timeout_seconds` |
| `vacuum_health`      | Yes                  | No                                    | Optional `schema`, `relation`, `limit`, `timeout_seconds`                            |
| `explain`            | Yes                  | Yes                                   | Required `query`; optional `timeout_seconds`                                         |
| `execute_sql`        | Opt-in               | Opt-in                                | Required `query`; optional `timeout_seconds`                                         |

Prefix each suffix with the provider type, for example `postgresql.lock_waits` or `mysql.table_health`.

The response includes `observed_at` and `live_snapshot: true`. PostgreSQL `top_queries` reports currently active sessions from `pg_stat_activity`; it does not require `pg_stat_statements` and does not provide historical query rankings. MySQL `top_queries` reads cumulative digests from `performance_schema.events_statements_summary_by_digest`. Other engine statistics may also be cumulative since a server or statistics reset, so compare their timestamps and reset state before treating counters as a rate. Empty lock, wait, or replication results are valid snapshots, not evidence that the provider failed.

## Execute SQL

`execute_sql` is disabled unless `execute_sql_enabled: true`. Enabling it makes the capability available to unattended AI SRE investigations and authorized owner/admin user surfaces without an interactive confirmation step.

The agent sends SQL to the database using the configured identity. It does not parse, classify, rewrite, or ask a human to approve the statement. The agent applies query-byte, timeout, row, result-byte, and concurrency limits, but database permissions are what prevent writes or administrative actions.

```json theme={null}
{
  "query": "SELECT id, service_name, status, latency_ms FROM app.orders WHERE status = 'failed' ORDER BY latency_ms DESC",
  "timeout_seconds": 10
}
```

Keep the configured login read-only. The tool description guides the model toward read-only investigation, but it does not replace database authorization.

`explain` prepends the engine's JSON `EXPLAIN` form to the submitted SQL. It does not offer `EXPLAIN ANALYZE` because that executes the query.

## Health and compatibility

Readiness opens a bounded connection and authenticates to the configured database. An unhealthy database provider does not change another provider's routing identity, but overall `/readyz` remains not ready while any registered provider is unhealthy.

Pull-request integration tests start digest-pinned PostgreSQL 14, 16, and 18 images and MySQL 8.4 and 9.7 LTS images. Each compatibility job creates deterministic `services` and `orders` data, executes every supported tool, retrieves seeded failed checkout orders through `execute_sql`, and proves the configured database user cannot delete rows. Focused PostgreSQL 16 and MySQL 8.4 jobs also exercise CA-verified TLS and mTLS, verify that the session is encrypted, and prove that wrong hostnames, an incorrect CA certificate, missing client certificates, and plaintext transport fail closed. The matrix includes only versions that have not reached upstream end of life and removes them when vendor support ends. It is not a production capacity test; validate representative catalog sizes and query plans before raising limits.