Documentation  ·  PostgreSQL

Connecting to Postgres

The connection is configuration, not something anyone types into a form. As on Oracle, the Design pages have no connect form: host, port, database, role and the schemas to list all come from the environment, and everything except the password is shown in the console so an operator can check what they are pointed at.

docker run -d --name mcpdbwizard-postgres \
  -p 8080:8080 \
  -v mcpdbwizard-data:/data \
  -e MCPDBWIZARD_DB_HOST=db.example.com \
  -e MCPDBWIZARD_DB_PORT=5432 \
  -e MCPDBWIZARD_DB_NAME=appdb \
  -e MCPDBWIZARD_DB_USER=mcp_agent \
  -e MCPDBWIZARD_DB_OTHER_USER='sales%' \
  -e DB_PASS_FILE=/run/secrets/db \
  ghcr.io/srmadscience/mcpdbwizard-postgres:2.0.33

The image already says it is for Postgres (MCPDBWIZARD_DB_PRODUCT=POSTGRESQL), so a deployment never sets that itself. Each required setting that is missing gets its own warning in docker logs at start-up.

Which schemas are listed

The Design pages list the objects in the schema named like the login role, plus every schema matching MCPDBWIZARD_DB_OTHER_USER:

MCPDBWIZARD_DB_OTHER_USER=sales        ->  the sales schema
MCPDBWIZARD_DB_OTHER_USER='sales%'     ->  sales, sales_archive, sales_eu, ...

The usual shape is a login role that owns nothing, with grants on the schema that holds the data. Then set MCPDBWIZARD_DB_USER_OBJECTS=false so that only that schema is listed. The strip along the top of every Design tab says which you are getting.

Nothing is upper-cased. Postgres folds unquoted names to lower case, so Sales in a setting does not match a schema created as sales. A schema created with a quoted mixed-case name is matched by that exact name.

The role the agent uses

Every caller shares one login role, and that role’s grants are the outer limit of what any generated server can do, whatever the config selects. Give it a role of its own with only what the selected objects need, rather than the schema owner:

create role mcp_agent login password 'change-me';
grant usage on schema sales to mcp_agent;

-- tables and views the config exposes, with only the operations it exposes
grant select on sales.orders, sales.customers to mcp_agent;
grant insert, update on sales.orders to mcp_agent;

-- functions and procedures it calls
grant execute on function sales.book_order(text, date, integer) to mcp_agent;

-- sequences it exposes as nextval tools
grant usage on sequence sales.order_id_seq to mcp_agent;

Postgres grants EXECUTE on new functions to PUBLIC by default. For a role that should reach only what you grant it, revoke that in the schemas it can see (revoke execute on all functions in schema sales from public), and grant back what other roles need. A SECURITY DEFINER function runs with its owner’s rights, which is the Postgres way to let the agent change data through a function without granting it the tables.

The generator reads the system catalogs, which every role can read, so introspection needs no extra grant.

The password is the one secret

Supply it as DB_PASS, or better as DB_PASS_FILE pointing at a Docker or Kubernetes secret, which stays out of docker inspect and can be rotated by replacing the file. Set exactly one: setting both is an error, because two sources for one secret hide which credential is in use. A trailing newline is stripped; a trailing space is not.

A saved config never contains the password. It records a placeholder that is filled from the environment when the server starts.

When it cannot connect

The console becomes a single PostgreSQL unavailable page naming the reason, what it is pointed at, and whether reloading will help: a missing setting is read once at start-up and needs a restart, while a database that is down is retried on every page load.

Servers that were already running are not affected. Each holds its own connection pool and keeps answering agents.