Postgres MCP Server: A Safe Read-Only Setup

Create a constrained database role

create role ai_reader
  login
  password 'replace-with-a-generated-secret';

grant connect on database app_db to ai_reader;
grant usage on schema public to ai_reader;
grant select on all tables in schema public to ai_reader;

alter default privileges in schema public
  grant select on tables to ai_reader;

alter role ai_reader set default_transaction_read_only = on;
alter role ai_reader set statement_timeout = '10s';
alter role ai_reader set idle_in_transaction_session_timeout = '30s';

Run this as the database owner, change the database and schema names, and store the generated password in the MCP client's secret or environment configuration rather than in a prompt.

Choose the server before copying a command

The original PostgreSQL reference server in the Model Context Protocol repository is archived. An old install command may still run, but archived software is a poor default for a component that receives database credentials.

Check the implementation's current repository, release activity, dependency history, and documented access modes. Postgres MCP Pro is one maintained option and includes restricted execution, schema inspection, query-plan tools, and performance checks. Its safeguards do not replace database permissions.

Enforce access in PostgreSQL

A system prompt that says only run SELECT is guidance, not an access-control boundary. PostgreSQL grants are the boundary. If the login cannot update a table, a mistaken tool call cannot talk its way around the missing privilege.

Default read-only transactions add a useful second layer. Keep the underlying grants narrow anyway, because role settings can be changed by sufficiently privileged users and functions can introduce behavior that is not obvious from the outer query.

• Use a separate login for each agent or integration so activity can be attributed and revoked.
• Grant access only to the database, schemas, tables, and views the workflow needs.
• Prefer curated views when raw tables contain secrets, personal data, or internal columns.
• Do not grant CREATE on application schemas or membership in owner roles.

Test the restrictions without the AI client

Connect as the new role using psql or a database GUI. A normal SELECT should work. UPDATE, DELETE, CREATE TABLE, and attempts to disable read-only mode should fail. Also run a deliberately expensive query in a disposable database to confirm the statement timeout is active.

This direct test separates PostgreSQL configuration from MCP configuration. If the role is wrong, fix it before placing the password in another tool.

Minimum permission test

select current_user, current_setting('transaction_read_only');
select * from public.projects limit 1;

-- These should fail for ai_reader:
update public.projects set name = name;
create table public.mcp_permission_test (id integer);

Run the failure checks only against a role and database where you are authorized to test permissions.

Add the connection to the MCP client

Follow the selected server's current configuration format. Most local MCP clients start a command and pass the PostgreSQL URL through an environment variable. Avoid configurations that place credentials in a command argument, checked-in JSON file, issue, or chat transcript.

Start with manual approval for tool calls. Inspect the schema list and run a small SELECT. Only enable automatic execution after you understand which tools the server exposes and which database the credentials reach.

Keep production separate

The safest target is a disposable branch, local copy, or sanitized read replica. For production reads, add network restrictions, auditing, result limits, and a tested revocation path. The broader checklist is in secure PostgreSQL access for AI agents.

An MCP server should make authorized inspection easier. It should not turn one leaked local configuration file into unrestricted database access.