How to set up PostgreSQL permissions for AI coding tools (Cursor, Claude, Copilot)

f the role has broad write access. A proxy can inspect the query AST (Abstract Syntax Tree) or regex patterns to block specific command types regardless of role privileges.

Core Solution

Implementing a secure AI-Database integration requires a three-layer architecture: the AI client, a permission proxy, and the PostgreSQL database. This section details the implementation of each layer.

1. Database Layer: Service Role with Least Privilege

Create a dedicated PostgreSQL role for AI access. This role should never be shared with application services or human developers. Use a read-only role as the default; grant write access only if the AI is explicitly tasked with schema migrations.

Implementation:

-- Create a dedicated role with strict login constraints
CREATE ROLE ai_cursor_agent WITH 
  LOGIN 
  PASSWORD 'strong-random-password-hash'
  NOCREATEDB 
  NOCREATEROLE 
  NOINHERIT
  CONNECTION LIMIT 5;

-- Grant minimal database connectivity
GRANT CONNECT ON DATABASE app_production TO ai_cursor_agent;

-- Restrict to specific schemas to avoid system catalog leakage
-- Assuming your application data lives in 'app_data'
GRANT USAGE ON SCHEMA app_data TO ai_cursor_agent;

-- Grant SELECT on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO ai_cursor_agent;

-- Ensure future tables are automatically accessible
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
  GRANT SELECT ON TABLES TO ai_cursor_agent;

-- Explicitly revoke access to sensitive tables
-- This is safer than trying to grant table-by-table
REVOKE SELECT ON TABLE app_data.pii_records FROM ai_cursor_agent;
REVOKE SELECT ON TABLE app_data.billing_invoices FROM ai_cursor_agent;
REVOKE SELECT ON TABLE app_data.api_keys FROM ai_cursor_agent;

-- If write access is required for migrations, use a separate role
-- CREATE ROLE ai_migrations_agent ...
-- GRANT SELECT, INSERT, UPDATE ON ALL TABLES ...
-- Note: TRUNCATE and DROP are NOT granted. 
-- Absence of grant = denial.

Architecture Decisions:

• NOINHERIT: Prevents the role from inheriting privileges from other roles it might be a member of, ensuring strict isolation.
• CONNECTION LIMIT: AI tools may spawn multiple connections. Limiting this prevents resource exhaustion.
• Schema Segmentation: Granting USAGE on public is dangerous. Restricting to app_data prevents the AI from reading internal metadata or audit schemas.
• Default Privileges: Ensures that new tables created by migrations are immediately accessible to the AI without manual intervention.

2. Proxy Layer: Query Validation and Control

PostgreSQL roles cannot validate query content or provide per-tool audit trails. A permission proxy sits between the AI tool and the database, enforcing additional rules. This proxy can be an MCP (Model Context Protocol) gateway or a custom service.

Proxy Responsibilities:

• Query Validation: Block TRUNCATE, DROP, ALTER, and DELETE commands unless explicitly allowed.
• Audit Logging: Record query text, execution time, row count, and tool identifier.
• Per-Tool Policies: Apply different rules for different AI tools.
• Result Masking: Optionally mask sensitive columns in query results.

Configuration Example (MCP Gateway):

{
  "mcpServers": {
    "postgres-secure": {
      "command": "npx",
      "args": ["-y", "@your-org/mcp-postgres-proxy"],
      "env": {
        "DATABASE_URL": "postgresql://ai_cursor_agent:password@host:5432/app_production",
        "PROXY_MODE": "strict",
        "ALLOWED_COMMANDS": "SELECT,EXPLAIN",
        "BLOCKED_TABLES": "pii_records,billing_invoices,api_keys",
        "AUDIT_LOG_LEVEL": "verbose"
      }
    }
  }
}

Rationale:

• Defense in Depth: The proxy enforces rules that PostgreSQL cannot. Even if the role is misconfigured, the proxy blocks destructive commands.
• Operational Visibility: The audit log provides insight into how the AI is using the database, enabling optimization of prompts and detection of anomalies.
• Flexibility: Policies can be updated without changing database roles or restarting connections.

3. Client Layer: Secure Connection

Configure the AI tool to use the proxy endpoint. Never store credentials in plain text in configuration files. Use environment variables or secret management systems.

Cursor/Claude Configuration:

{
  "mcpServers": {
    "db-context": {
      "url": "https://proxy.internal.net/mcp/postgres",
      "headers": {
        "Authorization": "Bearer ${MCP_PROXY_TOKEN}"
      }
    }
  }
}

Best Practice:

• Token Rotation: Use short-lived tokens for the proxy connection.
• Network Security: Ensure the proxy and database are in a private network. The AI tool should never connect directly to the database port.

Pitfall Guide

Even with a robust architecture, specific pitfalls can undermine security. Below are common mistakes and their mitigations.

Pitfall	Explanation	Fix
The TRUNCATE Blindspot	Developers assume denying DELETE prevents data destruction. TRUNCATE is a separate privilege in PostgreSQL. A role with INSERT/UPDATE can still TRUNCATE if granted that privilege.	Never grant TRUNCATE to AI roles. Use the proxy to block TRUNCATE commands explicitly.
Schema Drift	New tables created by migrations are not accessible to the AI role, causing context errors.	Use ALTER DEFAULT PRIVILEGES to automatically grant access to new tables. Verify this works after migrations.
System Catalog Leakage	AI tools can query pg_catalog or information_schema to discover table structures, potentially revealing sensitive metadata.	Restrict USAGE to application schemas only. Block access to system catalogs via the proxy.
Connection Pool Exhaustion	AI tools may open many concurrent connections, exhausting the database connection limit.	Set CONNECTION LIMIT on the role. Configure the proxy to pool connections efficiently.
RLS Complexity	Row-Level Security (RLS) is powerful but complex for AI tools. The AI may struggle to generate queries that comply with RLS policies.	Avoid RLS for AI access. Use table-level restrictions and schema segmentation instead. Reserve RLS for application users.
Credential Hardcoding	Storing passwords in .cursor/mcp.json or similar files risks credential leakage.	Use environment variables or secret managers. Never commit credentials to version control.
Over-Granting for Migrations	Granting broad write access for migrations allows the AI to modify data unexpectedly.	Use a separate, temporary role for migrations. Revoke write access immediately after migration.

Production Bundle

Action Checklist

• Create Service Role: Define a dedicated PostgreSQL role with NOINHERIT and CONNECTION LIMIT.
• Grant Schema Access: Restrict USAGE and SELECT to specific application schemas.
• Set Default Privileges: Configure ALTER DEFAULT PRIVILEGES for future tables.
• Revoke Sensitive Data: Explicitly REVOKE access to PII, billing, and key tables.
• Deploy Permission Proxy: Set up an MCP gateway or proxy service with query validation.
• Configure AI Client: Update tool configuration to use the proxy endpoint and secure credentials.
• Verify Restrictions: Run test queries to confirm DELETE, TRUNCATE, and sensitive table access are blocked.
• Enable Audit Logging: Ensure the proxy logs all queries with tool identifiers and execution metrics.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Local Development	Direct Role (Read-Only)	Speed and simplicity. Local data is disposable.	Low
QA/Staging	Proxy + Read-Only Role	Auditability and safety. Mirrors production risks.	Medium
Production	Proxy + Read-Only Role + Network Isolation	Maximum security. Prevents data loss and leakage.	High
Schema Migrations	Temporary Write Role via Proxy	Controlled write access. Revoked immediately after use.	Medium
Multi-Tool Setup	Proxy with Per-Tool Policies	Isolation between Cursor, Claude, and custom agents.	Medium

Configuration Template

PostgreSQL Setup Script:

-- 1. Create Role
CREATE ROLE ai_agent_readonly WITH 
  LOGIN 
  PASSWORD 'secure_password'
  NOCREATEDB 
  NOCREATEROLE 
  NOINHERIT
  CONNECTION LIMIT 3;

-- 2. Grant Database Access
GRANT CONNECT ON DATABASE my_app_db TO ai_agent_readonly;

-- 3. Grant Schema Access
GRANT USAGE ON SCHEMA public TO ai_agent_readonly;
-- If using custom schemas:
-- GRANT USAGE ON SCHEMA app_data TO ai_agent_readonly;

-- 4. Grant Table Access
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_agent_readonly;

-- 5. Default Privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
  GRANT SELECT ON TABLES TO ai_agent_readonly;

-- 6. Revoke Sensitive Tables
REVOKE SELECT ON TABLE users_pii FROM ai_agent_readonly;
REVOKE SELECT ON TABLE payment_methods FROM ai_agent_readonly;

-- 7. Verify
-- \du ai_agent_readonly
-- \dp public.*

MCP Proxy Configuration:

{
  "mcpServers": {
    "postgres-proxy": {
      "command": "node",
      "args": ["./proxy-server.js"],
      "env": {
        "DB_CONNECTION_STRING": "postgresql://ai_agent_readonly:password@localhost:5432/my_app_db",
        "PROXY_AUTH_TOKEN": "${PROXY_TOKEN}",
        "ALLOWED_QUERIES": "SELECT",
        "BLOCKED_TABLES": "users_pii,payment_methods",
        "LOG_LEVEL": "info"
      }
    }
  }
}

Quick Start Guide

1. Run SQL Script: Execute the PostgreSQL setup script to create the role and configure permissions.
2. Deploy Proxy: Start the permission proxy service with the configuration template.
3. Update AI Tool: Configure Cursor or Claude to use the proxy endpoint. Set environment variables for credentials.
4. Test Access: Run a SELECT query to verify access. Attempt a DELETE or access a blocked table to confirm restrictions.
5. Monitor Logs: Check the proxy audit logs to ensure queries are being recorded correctly.