PostgreSQL MCP 只读接入：从专用角色到同一条查询的权限验证

\set ON_ERROR_STOP on
DO $mcp_prereq$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_database WHERE datname = 'analytics_lab')
     OR EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'mcp_reader') THEN
    RAISE EXCEPTION 'analytics_lab 或 mcp_reader 已存在；停止，勿覆盖现有对象';
  END IF;
END;
$mcp_prereq$;

CREATE DATABASE analytics_lab;
\connect analytics_lab
CREATE SCHEMA reporting;
CREATE TABLE reporting.monthly_orders (
  month date PRIMARY KEY,
  order_count integer NOT NULL CHECK (order_count >= 0)
);
INSERT INTO reporting.monthly_orders VALUES
  ('2026-08-01', 12), ('2026-09-01', 18);

CREATE ROLE mcp_reader LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
\password mcp_reader
REVOKE ALL ON DATABASE analytics_lab FROM PUBLIC;
GRANT CONNECT ON DATABASE analytics_lab TO mcp_reader;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA reporting TO mcp_reader;
GRANT SELECT ON reporting.monthly_orders TO mcp_reader;
ALTER ROLE mcp_reader IN DATABASE analytics_lab SET default_transaction_read_only = on;
ALTER ROLE mcp_reader IN DATABASE analytics_lab SET statement_timeout = '3s';

psql -h 127.0.0.1 -p 5432 -U mcp_reader -d analytics_lab -W

SELECT current_database(), current_user;
SHOW default_transaction_read_only;
SHOW statement_timeout;
SELECT month, order_count
FROM reporting.monthly_orders
ORDER BY month DESC LIMIT 10;
SELECT has_table_privilege(current_user, 'reporting.monthly_orders', 'SELECT') AS can_read,
       has_table_privilege(current_user, 'reporting.monthly_orders', 'INSERT') AS can_insert,
       has_table_privilege(current_user, 'reporting.monthly_orders', 'UPDATE') AS can_update;

[mcp_servers.postgres_lab]
command = "uvx"
args = ["postgres-mcp", "--access-mode=restricted"]
env_vars = ["DATABASE_URI"]
enabled_tools = ["execute_sql"]

仅使用 postgres_lab 的 execute_sql。
先运行 SELECT current_database(), current_user;
只有返回 analytics_lab 和 mcp_reader 时才继续。
随后运行：
SELECT month, order_count FROM reporting.monthly_orders ORDER BY month DESC LIMIT 10;
逐项报告两行日期与数量，不添加其他 schema 查询或性能分析任务。
结果与我在 psql 的同一查询不一致时，先停止并保留实际 SQL。

BEGIN READ WRITE;
UPDATE reporting.monthly_orders SET order_count = order_count WHERE false;
ROLLBACK;