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

Most developers I talk to connect Cursor or Claude directly to their databases using a full admin connection string. Wether it's a local Docker database, or cloud-based QA or even production database, since it's not hidden in a subnet and publicly available. Many of developers have open-to-world QA databases, replicating prod data, where multiple developers work.

It works. But it's the equivalent of giving someone your house keys because they need to water your plants.

This post covers how to set up PostgreSQL permissions specifically for AI tools - what to create, what to restrict, and how to actually enforce it at the query level.

The problem with a direct connection string

When you drop a raw postgresql://user:password@host:5432/db into Cursor, you're giving it whatever privileges that user has. If that's your admin user - and it usually is - the AI can:

SELECT anything
UPDATE anything
DELETE anything
DROP tables
Run TRUNCATE on production data


Not that Cursor would do this on purpose. But AI tools generate SQL based on context, and mistakes happen. One misunderstood prompt, one "fix this data" instruction gone wrong - and you're restoring from backup. If you have one. If it's restorable.

The fix is two things: a restricted PostgreSQL role, and a permission layer that validates queries before they reach the database.

Step 1: Create a dedicated PostgreSQL role for AI access

Never use your admin user for AI tools. Create a separate role with only the privileges the AI actually needs.

-- Create the role
CREATE ROLE ai_readonly WITH LOGIN PASSWORD 'your-strong-password';

-- Grant connection to the database
GRANT CONNECT ON DATABASE your_database TO ai_readonly;

-- Grant usage on the schema
GRANT USAGE ON SCHEMA public TO ai_readonly;

-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_readonly;

-- Grant SELECT on future tables too
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT TO ai_readonly;

For a read-write setup where the AI needs to insert or update (for example, a coding assistant that runs migrations):

CREATE ROLE ai_readwrite WITH LOGIN PASSWORD 'your-strong-password';

GRANT CONNECT ON DATABASE your_database TO ai_readwrite;
GRANT USAGE ON SCHEMA public TO ai_readwrite;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO ai_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE TO ai_readwrite;

-- Explicitly block DELETE and destructive operations
-- (just don't grant them - absence of GRANT = denied)

Key point: in PostgreSQL, not granting a privilege is enough to block it. You don't need to explicitly REVOKE anything you never granted.

Step 2: Block access to sensitive tables

Even with a read-only role, you probably don't want the AI seeing everything. Billing data, PII, internal audit tables - these should be off limits.

-- Revoke access to specific tables after the blanket GRANT
REVOKE SELECT ON TABLE users FROM ai_readonly;
REVOKE SELECT ON TABLE billing_events FROM ai_readonly;
REVOKE SELECT ON TABLE api_keys FROM ai_readonly;

-- Or lock down an entire schema
REVOKE USAGE ON SCHEMA internal FROM ai_readonly;

The pattern: grant broadly, then revoke specifically. This is easier to maintain than trying to grant table by table.

Step 3: Restrict to specific schemas

If your database has multiple schemas and the AI only needs access to one of them:

-- Only grant access to the 'app' schema, not 'internal' or 'audit'
GRANT USAGE ON SCHEMA app TO ai_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO ai_readonly;

-- Don't grant anything on other schemas

This is the cleanest approach for teams where different schemas have different sensitivity levels.

Step 4: Add a permission layer on top of PostgreSQL

PostgreSQL role-level permissions are necessary, but not sufficient on their own. They don't:

Log what queries the AI actually ran
Block TRUNCATE (which requires only ownership, not a special privilege)
Prevent the AI from querying system catalogs (pg_catalog, information_schema)
Give you per-tool control (different permissions for Cursor vs a contractor's Claude setup)

This is where a tool like DataMCP comes in. It sits between your AI tool and PostgreSQL, adds a second permission layer, and validates every query before execution.
The setup looks like this:

Cursor / Claude / VS Code
        |
        | MCP protocol
        v
   DataMCP (permission check + query validation)
        |
        | only validated queries pass through
        v
   PostgreSQL (your ai_readonly role)

With this setup, effective permissions = PostgreSQL role permissions AND DataMCP permission rules. Both have to allow a query for it to execute.
In DataMCP you can:

• Set a permission preset per MCP link (READ_ONLY, READ_WRITE, CUSTOM)
• Block specific tables at the application layer without touching PostgreSQL roles
• See every query that ran, when, and what it returned Revoke a specific AI tool's access instantly without changing your database

Step 5: Test that restrictions actually work

Don't assume the permissions are working - verify them.

-- Connect as the AI user and test
psql postgresql://ai_readonly:password@host:5432/db

-- This should work
SELECT id, email FROM users LIMIT 5;

-- This should fail with "permission denied"
DELETE FROM users WHERE id = 1;

-- This should fail
DROP TABLE users;

-- This should fail if you revoked it
SELECT * FROM billing_events;

Run through the operations you blocked and confirm they return permission errors, not results.

What this setup gives you

With a dedicated PostgreSQL role plus a permission layer:

• The AI can only do what you explicitly allowed
• Every query is logged with execution time and row count
• You can give different tools different access levels
• You can revoke access for a specific tool without touching other integrations = TRUNCATE and DROP are blocked even if the PostgreSQL role somehow got too many privileges

The PostgreSQL role is your first line of defense. The permission layer is your second. Neither alone is enough - you want both.

If you're using Cursor or Claude with a PostgreSQL database and want to skip the manual setup, DataMCP handles the permission layer out of the box. Free tier covers one database connection.

FAQ

Do I need both a restricted PostgreSQL role AND a separate permission layer?
Yes. PostgreSQL roles control what the database user can do. A permission layer like DataMCP adds query-level validation, audit logging, and per-tool access control on top. Use both.

Can I use row-level security (RLS) instead?
RLS is great for multi-tenant apps but adds complexity for AI tools. The AI needs to understand the RLS policies to write correct queries. A simpler approach: restricted role + table-level REVOKE + a permission layer.

What if I'm using Supabase or Neon?
The same SQL commands work. Supabase and Neon both expose standard PostgreSQL role management. Create the restricted role through the SQL editor in their dashboard.

How do I connect the restricted role to Cursor?
Add it to .cursor/mcp.json - either directly as a connection string (basic, no permission layer) or via an MCP gateway like DataMCP (recommended). The MCP approach gives you the audit trail and query validation on top of the PostgreSQL role restrictions.

Does this work for Claude Desktop?
Yes. Claude Desktop supports MCP via mcp-remote. The same DataMCP MCP URL works for Cursor, Claude Desktop, VS Code, and any other MCP-compatible tool.