-- PostgreSQL Read-Only User Setup Script
-- Run this as a superuser (postgres) to create a restricted user for the MCP server
-- Configuration: Change these values as needed
\set mcp_user 'mcp_readonly'
\set mcp_password 'your_secure_password_here'
\set target_database 'your_database_name'
-- Create the read-only user
CREATE USER :mcp_user WITH PASSWORD :'mcp_password';
-- Connect to the target database
\c :target_database
-- Grant connect permission
GRANT CONNECT ON DATABASE :target_database TO :mcp_user;
-- Grant usage on public schema (and any other schemas you want to expose)
GRANT USAGE ON SCHEMA public TO :mcp_user;
-- Grant SELECT on all existing tables in public schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO :mcp_user;
-- Grant SELECT on all existing sequences in public schema
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO :mcp_user;
-- Grant EXECUTE on all existing functions in public schema (for read-only functions)
-- Comment this out if you don't want function access
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO :mcp_user;
-- Set default privileges for future tables (important!)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO :mcp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES TO :mcp_user;
-- ============================================================
-- OPTIONAL: Grant access to additional schemas
-- Uncomment and modify as needed
-- ============================================================
-- -- For a schema named 'app'
-- GRANT USAGE ON SCHEMA app TO :mcp_user;
-- GRANT SELECT ON ALL TABLES IN SCHEMA app TO :mcp_user;
-- GRANT SELECT ON ALL SEQUENCES IN SCHEMA app TO :mcp_user;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA app
-- GRANT SELECT ON TABLES TO :mcp_user;
-- ============================================================
-- OPTIONAL: Enable write operations
-- Only uncomment if ALLOW_WRITE_OPERATIONS=true in your .env
-- ============================================================
-- -- Grant INSERT, UPDATE, DELETE on all tables
-- GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO :mcp_user;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public
-- GRANT INSERT, UPDATE, DELETE ON TABLES TO :mcp_user;
-- -- Grant USAGE on sequences (needed for INSERT with SERIAL columns)
-- GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO :mcp_user;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public
-- GRANT USAGE ON SEQUENCES TO :mcp_user;
-- -- Grant CREATE on schema (needed for CREATE TABLE)
-- GRANT CREATE ON SCHEMA public TO :mcp_user;
-- ============================================================
-- Verification queries
-- ============================================================
-- List all privileges for the user
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.role_table_grants
WHERE grantee = :'mcp_user'
ORDER BY table_schema, table_name, privilege_type;
-- Show the user's role memberships
SELECT
r.rolname as role,
r.rolsuper as is_superuser,
r.rolinherit as inherit,
r.rolcreaterole as can_create_role,
r.rolcreatedb as can_create_db,
r.rolcanlogin as can_login,
r.rolreplication as replication,
r.rolconnlimit as connection_limit
FROM pg_roles r
WHERE r.rolname = :'mcp_user';
-- ============================================================
-- Revoke commands (if you need to remove the user later)
-- ============================================================
-- -- Revoke all privileges
-- REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM :mcp_user;
-- REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM :mcp_user;
-- REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM :mcp_user;
-- REVOKE USAGE ON SCHEMA public FROM :mcp_user;
-- REVOKE CONNECT ON DATABASE :target_database FROM :mcp_user;
-- -- Drop the user
-- DROP USER IF EXISTS :mcp_user;
