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

A developer has outlined a method for restricting PostgreSQL permissions when connecting AI coding tools like Cursor, Claude, and Copilot to databases. The approach involves creating a dedicated PostgreSQL role with only the necessary privileges—such as read-only or limited read-write access—rather than using a full admin connection string. The developer also recommends granting broad access and then revoking it from sensitive tables or schemas to protect data like billing information and PII.

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. 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. 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. 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. 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. 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 https://datamcp.app 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: 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. With a dedicated PostgreSQL role plus a permission layer: 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 https://datamcp.app handles the permission layer out of the box. Free tier covers one database connection. 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.