Give AI read-only PostgreSQL access, not your application password

By Oleksandr Korol · July 27, 2026

#ai#postgresql#databases#docker#security#automation

AI is becoming another database client.

Coding agents inspect data while debugging. Database applications expose MCP servers. Assistants generate reports, verify migrations, compare application behavior with stored state, and answer questions that are difficult to resolve from source code alone.

Giving an AI tool access to the database can be genuinely useful. Giving it the application’s database credentials is not.

The application user normally needs to insert, update, delete, create indexes, and run migrations. An assistant investigating a bug usually needs only to inspect schemas and execute SELECT queries. Sharing the application account gives a probabilistic system far more authority than its task requires.

The safer default is simple:

Give every AI integration its own read-only PostgreSQL login.

I wanted this login to be created automatically with Docker Compose, work across every database, include tables and views created later, and remain safe to run on every container start.

A read-only account is useful for more than protecting against a badly generated query. It creates a clear security boundary for every tool connected through an MCP server, database client, coding agent, or local automation.

With separate credentials, I can:

keep write privileges out of AI configuration;
revoke AI access without affecting the application;
rotate its password independently;
identify its sessions in PostgreSQL activity and logs;
apply connection limits and timeouts specifically to automated queries;
reuse the same access policy across several tools.

This follows the principle of least privilege. The assistant receives the capabilities required to investigate data, but not the ability to modify it.

It is still powerful access. A read-only user may see personal information, tokens, internal notes, or other sensitive values. “Read-only” means “cannot write,” not “safe to expose everywhere.” Production access should still be exceptional, network-restricted, audited, and preferably directed to a replica or sanitized environment.

The complicated way

The traditional solution is to grant privileges manually:

GRANT CONNECT ON DATABASE app TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

This works for one database, one schema, and one object owner. It becomes repetitive when a PostgreSQL cluster contains several databases or schemas. Default privileges also belong to the role that creates future objects, so migrations running under another owner can leave the read-only account unable to inspect new tables.

Views, materialized views, sequences, new schemas, and additional databases make the script longer still.

PostgreSQL already has a better abstraction.

Use `pg_read_all_data`

PostgreSQL provides the predefined pg_read_all_data role. It grants read access to all tables, views, and sequences, together with usage on all schemas. Its behavior also covers objects created in the future, removing the need to manage default privileges for every owner.

The role is available in PostgreSQL 14 and newer, including PostgreSQL 16 and 17.

It does not grant database connection rights, so I grant CONNECT separately for every connectable, non-template database.

The result is a small startup script:

#!/bin/sh
set -eu

: "${POSTGRES_USER:?POSTGRES_USER must be set}"
: "${POSTGRES_DB:?POSTGRES_DB must be set}"
: "${POSTGRES_RO_PASSWORD:?POSTGRES_RO_PASSWORD must be set}"

until pg_isready --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" >/dev/null 2>&1; do
  sleep 1
done

psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" \
  --set=readonly_password="$POSTGRES_RO_PASSWORD" \
  --set=ON_ERROR_STOP=1 <<'SQL'
-- Create the login once without changing an existing role or its password.
SELECT format('CREATE ROLE readonly LOGIN PASSWORD %L', :'readonly_password')
WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'readonly')
\gexec

SELECT 'GRANT pg_read_all_data TO readonly'
WHERE NOT pg_has_role('readonly', 'pg_read_all_data', 'MEMBER')
\gexec

SELECT format('GRANT CONNECT ON DATABASE %I TO readonly', datname)
FROM pg_database
WHERE datallowconn AND NOT datistemplate
\gexec
SQL

I save it as:

docker/postgres/configure-readonly-user.sh

and make it executable:

chmod +x docker/postgres/configure-readonly-user.sh

There are a few useful details in the script:

pg_isready waits until PostgreSQL accepts connections. A post-start command can begin before the server is ready for SQL.
POSTGRES_RO_PASSWORD is required, so startup fails clearly instead of creating a login with an unintended password.
format(... %L ...) safely quotes the password as a SQL literal.
format(... %I ...) safely quotes database names as identifiers.
\gexec executes the SQL statements produced by each query.
The role and membership checks make repeated execution idempotent.
If readonly already exists, its password is left unchanged.

The connection grants can safely run again. They also ensure that databases added since the previous container start receive the same policy.

Run it after every PostgreSQL start

Docker Compose supports post_start lifecycle commands. I mount the script into the PostgreSQL container and invoke it after the service starts:

services:
  db:
    image: postgres:17-alpine
    env_file:
      - .env
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./docker/postgres/configure-readonly-user.sh:/usr/local/bin/configure-readonly-user.sh:ro
    post_start:
      - command: /bin/sh /usr/local/bin/configure-readonly-user.sh

The environment contains the normal PostgreSQL settings plus a separate password:

POSTGRES_DB=app
POSTGRES_USER=app
POSTGRES_PASSWORD=application-password
POSTGRES_RO_PASSWORD=ai-readonly-password

The script runs whenever Compose starts the database container. On the first run it creates the login and grants access. On later runs it preserves the existing account and idempotently verifies the required permissions.

This is preferable to placing the script only in /docker-entrypoint-initdb.d. PostgreSQL’s official image runs initialization scripts only when it creates an empty data directory. They do not run again for an existing volume, so they cannot reconcile access after databases are added later.

Give the AI only the read-only credentials

The AI-facing connection should use:

DATABASE_URL=postgresql://readonly:ai-readonly-password@db:5432/app

The application continues using its normal account. The read-only URL belongs only in the MCP server, database tool, or agent environment that needs it.

Do not place either password directly in a committed Compose file. Use a secret manager in production and an ignored environment file for local development. Treat AI tool configuration as credential-bearing infrastructure, especially when a desktop client or MCP server can expose database tools to several assistants.

Read-only is a boundary, not a complete safety system

pg_read_all_data solves authorization cleanly, but it does not solve every database risk.

A read-only query can still be expensive. An agent can accidentally request a large join, scan a huge table, hold a transaction open, or return sensitive data into a prompt or log. I would consider additional controls for any important environment:

ALTER ROLE readonly CONNECTION LIMIT 5;
ALTER ROLE readonly SET statement_timeout = '30s';
ALTER ROLE readonly SET idle_in_transaction_session_timeout = '60s';

Other practical protections include:

connecting the agent to a read replica;
exposing only the databases it genuinely needs;
masking personal and secret data;
restricting network access to trusted hosts;
logging queries and connection activity;
limiting result sizes in the MCP or database tool;
requiring human approval before an agent receives production credentials.

The predefined role also does not bypass row-level security. If a table uses RLS, its policies still apply. That is usually the safer behavior—do not add BYPASSRLS merely because an assistant expects to see more rows.

AI should make least privilege easier

AI tools are becoming normal participants in development environments. They should not inherit application credentials simply because those credentials already exist and are convenient.

A dedicated read-only login takes little effort, makes intent visible, and dramatically reduces the consequences of a mistaken query. With pg_read_all_data, the PostgreSQL side is much simpler than maintaining grants for every schema, table, view, and future migration.

The general pattern extends beyond AI:

One workload
    ↓
One identity
    ↓
Only the permissions that workload needs

For an assistant checking a database, that identity should almost always be read-only.

Read the current version at https://dufran.org/posts/read-only-postgresql-access-for-ai/

Why a separate login matters