Read-only DB user for a Postgres source

Mirrored from iblai/ontology · docs/read-only-db-user.md. This page is generated — edit it in the repository, not here.

How to provision the dedicated read-only Postgres role the ontology uses to connect to a source database. This assumes you already have connection details for the target database (host, port, database name) and an admin/owner account you can use once to create the role.

The ontology never connects to a source with an admin/superuser account. Its read-only safety suite attempts seven write operations (CREATE/INSERT/UPDATE/DELETE/DROP/ALTER/TRUNCATE) and requires all seven to be denied before it will provision the source. A superuser passes all writes, so the suite (correctly) marks it FAILED and refuses to proceed. The fix is a dedicated role that can only SELECT.

What you need

Set these as shell variables so the commands below are copy-paste:

DB_HOST=db.example.internal
DB_PORT=5432
DB_NAME=appdb
DB_ADMIN=postgres            # admin/owner used only to create the role
RO_PW=$(openssl rand -base64 24 | tr -dc 'A-Za-z0-9' | head -c 28)   # generated read-only password
echo "Save this read-only password: $RO_PW"

Any Postgres client works. If you don't have psql on the host, run it from a throwaway container: docker run --rm -i postgres:16-alpine psql "$@" (add --network <net> and use the DB's network hostname if the DB is only reachable on a private Docker/VPC network).

1. Create the read-only role

Run these statements once, connected to the target database as the admin/owner account. Replace appdb with your database name and <STRONG_PASSWORD_HERE> with a generated password. The block is idempotent — safe to re-run to rotate the password or re-apply grants. This is plain SQL: paste it into any client (pgAdmin, DBeaver, psql, …).

-- Create the login role if it does not already exist
DO $$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'ontology_ro') THEN
      CREATE ROLE ontology_ro WITH LOGIN;
   END IF;
END $$;

-- Set / rotate its password
ALTER ROLE ontology_ro WITH PASSWORD '<STRONG_PASSWORD_HERE>';

-- Allow it to connect and read the public schema (repeat per schema if needed)
GRANT CONNECT ON DATABASE appdb TO ontology_ro;
GRANT USAGE ON SCHEMA public TO ontology_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ontology_ro;

-- Auto-grant SELECT on tables created in future (see caveat below)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ontology_ro;

Scripted alternative (psql)

The same thing driven from the shell, with the password/DB name injected via psql variables so no secret is typed inline. -i is required so the heredoc reaches psql on stdin.

PGPASSWORD='<ADMIN_PASSWORD>' psql \
  -h "$DB_HOST" -p "$DB_PORT" -U "$DB_ADMIN" -d "$DB_NAME" \
  -v ON_ERROR_STOP=1 -v ro_pw="$RO_PW" <<'SQL'
DO $$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'ontology_ro') THEN
      CREATE ROLE ontology_ro WITH LOGIN;
   END IF;
END $$;
ALTER ROLE ontology_ro WITH PASSWORD :'ro_pw';
GRANT CONNECT ON DATABASE :"DBNAME" TO ontology_ro;
GRANT USAGE ON SCHEMA public TO ontology_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ontology_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ontology_ro;
SQL

:"DBNAME" — psql substitutes the database name automatically; no need to pass it. If your client doesn't support that, replace it with the literal DB name.

ALTER DEFAULT PRIVILEGES grants SELECT on future tables too — but only for tables created by the role that ran the statement (the admin account). If your schema migrations run as a different role, re-run GRANT SELECT ON ALL TABLES IN SCHEMA public TO ontology_ro; after each migration, or run the ALTER DEFAULT PRIVILEGES as (or FOR ROLE) the migrating role.

Multiple schemas? Repeat the USAGE / SELECT / ALTER DEFAULT PRIVILEGES grants for each schema (they are not recursive across schemas).

2. Verify (read works, writes denied)

# READ — should print the table count
PGPASSWORD="$RO_PW" psql -h "$DB_HOST" -p "$DB_PORT" -U ontology_ro -d "$DB_NAME" -tAc \
  "select count(*) from information_schema.tables where table_schema='public';"

# WRITE — must be DENIED ("permission denied for schema public")
PGPASSWORD="$RO_PW" psql -h "$DB_HOST" -p "$DB_PORT" -U ontology_ro -d "$DB_NAME" \
  -c "CREATE TABLE __ro_probe (id int);"

The permission denied message is one of the ontology safety suite's PERMISSION_INDICATORS, so the suite will report this role as PASSED.

3. Wire it into the ontology

Put the secret only in .env.mcp (git-ignored), never in tools.yaml:

# .env.mcp
SRC_DB_HOST=db.example.internal
SRC_DB_PORT=5432
SRC_DB_NAME=appdb
SRC_RO_USER=ontology_ro
SRC_RO_PASSWORD=<generated-read-only-password>

# config/tools.yaml
kind: source
name: my-postgres
type: postgres
host: ${SRC_DB_HOST}
port: ${SRC_DB_PORT}
database: ${SRC_DB_NAME}
user: ${SRC_RO_USER}
password: ${SRC_RO_PASSWORD}

Then generate the Toolbox config and validate:

ontology mcp validate       # checks the DSL
ontology mcp build          # writes config/generated/toolbox.yaml (deploy up does this too)

The mcp-toolbox container must be able to reach SRC_DB_HOST — ensure network routing/firewall allows it (for a DB on a private Docker/VPC network, attach the toolbox to that network and use the DB's network hostname). Note the Toolbox connects to every source at startup, so keep only reachable sources in tools.yaml (see deployment.md).

MySQL variant

The same principle applies to a MySQL source. Run as an admin, replacing <db> with your database name and the password:

CREATE USER IF NOT EXISTS 'ontology_ro'@'%' IDENTIFIED BY '<STRONG_PASSWORD_HERE>';
GRANT SELECT ON `<db>`.* TO 'ontology_ro'@'%';
FLUSH PRIVILEGES;

Verify (read works, write denied — ERROR 1142 ... command denied):

mysql -h "$DB_HOST" -P 3306 -u ontology_ro -p"$RO_PW" -N \
  -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='<db>';"
mysql -h "$DB_HOST" -P 3306 -u ontology_ro -p"$RO_PW" \
  -e "CREATE TABLE \`<db>\`.__ro_probe (id int);"   # must be denied

Wire it into tools.yaml as a type: mysql source; its tools use mysql-sql with ? placeholders (not $1). See the client-mysql source and the list-mysql-tables / describe-mysql-table tools for a working example.

Rotate / revoke

-- rotate password (or just re-run step 1)
ALTER ROLE ontology_ro WITH PASSWORD '<NEW_PASSWORD>';

-- revoke everything and remove the role
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM ontology_ro;
REVOKE ALL ON SCHEMA public FROM ontology_ro;
REVOKE ALL ON DATABASE appdb FROM ontology_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM ontology_ro;
DROP ROLE ontology_ro;