Give Claude Code Database Access Without Losing the Database

1. Separate the agent from database administration

Use Ubuntu 24.04 LTS with PostgreSQL already running on your VPS. You need a human administrator session and a separate Linux account for Claude Code, called agentops here. The agent account must have no unrestricted sudo, no access to the PostgreSQL data directory and no access to application credentials.

A restricted database password cannot protect a local database from an agent that can become the postgres operating system user. Docker socket access and writable deployment scripts can provide similar escape routes. Establish these boundaries using the production server guardrails procedure before connecting the database.

We recommend Claude Code for this workflow, starting with a sanitized copy whenever live data is unnecessary. A live connection is appropriate for narrowly scoped operational questions, such as checking a service status view. If answering the question requires customer records, decide explicitly which fields may leave the server through agent tool responses.

Install the PostgreSQL client from your human administrator session. The remaining examples configure an existing database named appdb, with an existing application owner named app_owner. Treat SQL and authentication snippets as configuration to adapt and review, rather than a script to paste into an unknown production schema.

sudo apt-get update
sudo apt-get install -y postgresql-client

2. Create a database role that owns nothing

Open an administrator connection to appdb outside Claude Code. Create a fresh role rather than repurposing the application's login, because existing memberships can carry unexpected privileges. Leave login disabled until the permission review is complete.

CREATE ROLE agent_reader
  NOLOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS
  CONNECTION LIMIT 3;

GRANT CONNECT ON DATABASE appdb TO agent_reader;

ALTER ROLE agent_reader IN DATABASE appdb
  SET default_transaction_read_only = on;
ALTER ROLE agent_reader IN DATABASE appdb
  SET statement_timeout = '5s';
ALTER ROLE agent_reader IN DATABASE appdb
  SET lock_timeout = '1s';
ALTER ROLE agent_reader IN DATABASE appdb
  SET idle_in_transaction_session_timeout = '15s';
ALTER ROLE agent_reader IN DATABASE appdb
  SET search_path = pg_catalog, agent_api;

Do not make this role the database owner or grant it membership in an application owner role. Avoid broad predefined roles that expose every table or server files. Ownership includes powers that ordinary table grants cannot remove, as the PostgreSQL privilege documentation explains.

The read-only default and timeouts reduce accidental damage, but ordinary sessions can change these settings. They are operating defaults, not an authorization boundary. PostgreSQL describes the scope of read-only transactions; your actual write protection comes from withholding privileges.

3. Expose approved views and inspect inherited access

Grant access to a deliberately small reporting surface. This example assumes app.service_status already contains service_name, health_state and last_seen_at, and that those fields are approved for agent use. Substitute an equally narrow operational view from your application.

BEGIN;

CREATE SCHEMA agent_api AUTHORIZATION app_owner;

SET LOCAL ROLE app_owner;
CREATE VIEW agent_api.service_status AS
SELECT service_name, health_state, last_seen_at
FROM app.service_status;
RESET ROLE;

REVOKE ALL ON SCHEMA agent_api FROM PUBLIC;
REVOKE ALL ON agent_api.service_status FROM PUBLIC;
GRANT USAGE ON SCHEMA agent_api TO agent_reader;
GRANT SELECT ON agent_api.service_status TO agent_reader;

COMMIT;

Keep the explicit column list when the application schema changes. Do not add automatic grants on all future tables, and do not use SELECT * in an access boundary. Ordinary views use their owner's permissions for underlying access, so do not assume underlying row security will restrict this view for the agent.

Next inspect effective privileges, including those received through PUBLIC, which means every database role. Revoking a privilege from agent_reader does not cancel a grant to PUBLIC. In particular, look for writable schemas and callable application functions, including SECURITY DEFINER functions that execute with their owner's authority.

SELECT nspname AS schema_name
FROM pg_namespace
WHERE has_schema_privilege('agent_reader', oid, 'CREATE');

SELECT n.nspname AS schema_name,
       p.proname AS routine_name,
       pg_get_function_identity_arguments(p.oid) AS arguments,
       p.prosecdef AS security_definer
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE n.nspname NOT LIKE 'pg_%'
  AND n.nspname <> 'information_schema'
  AND has_schema_privilege('agent_reader', n.oid, 'USAGE')
  AND has_function_privilege('agent_reader', p.oid, 'EXECUTE');

Review every result before enabling login. If an application function is available through PUBLIC, revoke that grant only after explicitly granting required access to legitimate application callers. Review function default privileges for each creator role as well, because future routines can otherwise reopen access.

4. Restrict authentication and set the agent password

Keep PostgreSQL reachable through loopback for this local setup. Do not expose its port publicly to connect Claude Code running on the same VPS. Inspect your existing listener configuration and firewall before assuming that local connection examples imply local-only exposure.

Find the active authentication file with SHOW hba_file; in your administrator session. Put the following entries before broader matching entries, because PostgreSQL uses the first matching authentication rule. These rules allow this role into appdb through local TCP and reject its other database connections.

host appdb agent_reader 127.0.0.1/32 scram-sha-256
host appdb agent_reader ::1/128 scram-sha-256
local all agent_reader reject
host all agent_reader 0.0.0.0/0 reject
host all agent_reader ::/0 reject

In the administrator's psql session, set password encryption, use the password prompt and enable login. Do not put the password in a SQL literal or a command argument. Reload authentication configuration after checking pg_hba_file_rules for errors, then keep the administrator connection open while you verify a new connection.

SET password_encryption = 'scram-sha-256';
\password agent_reader
ALTER ROLE agent_reader LOGIN;

SELECT line_number, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

SELECT pg_reload_conf();

Store only this restricted credential in the agent's credential workflow. Keep application and administrator passwords inaccessible to its Linux account, including copies in shell history and deployment files. Follow the guide to secrets and scoped credentials for storage and rotation.

5. Verify permissions through the actual agent account

From the agentops Linux session, connect using the restricted database password. This catches authentication differences that an administrator's SET ROLE session would miss. Use the same host and database settings you will give the agent.

psql -X -h 127.0.0.1 -U agent_reader -d appdb

Inspect the session identity and effective privileges before attempting any failure demonstration. The application table should be unreadable, while the approved view should permit reads only. Also enumerate accessible application relations so an old PUBLIC grant cannot hide behind a successful spot check.

SELECT current_user, current_database();
SHOW default_transaction_read_only;

SELECT service_name, health_state, last_seen_at
FROM agent_api.service_status
LIMIT 20;

SELECT
  has_table_privilege(current_user, 'app.service_status', 'SELECT') AS base_read,
  has_table_privilege(current_user, 'agent_api.service_status', 'SELECT') AS view_read,
  has_table_privilege(current_user, 'agent_api.service_status', 'UPDATE') AS view_update,
  has_database_privilege(current_user, current_database(), 'CREATE') AS database_create;

SELECT n.nspname, c.relname
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname NOT LIKE 'pg_%'
  AND n.nspname <> 'information_schema'
  AND c.relkind IN ('r', 'p', 'v', 'm', 'f')
  AND has_schema_privilege(current_user, n.oid, 'USAGE')
  AND has_table_privilege(current_user, c.oid, 'SELECT');

For a safe denial check, try creating an empty table inside a transaction after disabling the read-only default for that transaction. Permission denial proves that schema privileges provide protection independently of the default. Run ROLLBACK afterward whether the statement fails or unexpectedly succeeds; investigate any success before continuing.

BEGIN READ WRITE;
CREATE TABLE agent_api.permission_probe (probe integer);
ROLLBACK;

6. Add a local database MCP connection

You can stop with psql if shell-based investigation meets your needs. For database discovery inside Claude Code, we would use DBHub over local stdio with the same restricted role. MCP provides the tool interface; PostgreSQL still decides what the connection may do.

Install @bytebase/dbhub using an administrator-controlled dependency build with a lockfile, and review it before deploying. The example assumes its installed executable is /opt/agent-db/node_modules/.bin/dbhub and that a compatible Node.js runtime is available. Make the deployed package tree unwritable by agentops, and avoid downloading an unreviewed package whenever a session starts.

Save this as /home/agentops/.config/agent-db/dbhub.toml, readable only by agentops. DBHub supports environment interpolation and tool restrictions in its TOML configuration. The result cap limits returned data, not the work PostgreSQL performs.

[[sources]]
id = "ops"
type = "postgres"
host = "127.0.0.1"
port = 5432
database = "appdb"
user = "agent_reader"
password = "${AGENT_DB_PASSWORD}"
query_timeout = 5
pool_max_connections = 2

[[tools]]
name = "execute_sql"
source = "ops"
readonly = true
max_rows = 50

[[tools]]
name = "search_objects"
source = "ops"

In the agent's Linux session, enter the restricted password without echoing it, then register the installed executable. Launch Claude Code from that environment so the MCP process inherits the credential. This keeps the password out of command arguments, but processes running as the same Linux user can still access it.

read -r -s -p 'Restricted database password: ' AGENT_DB_PASSWORD
export AGENT_DB_PASSWORD
claude mcp add --transport stdio --scope local ops-db -- /opt/agent-db/node_modules/.bin/dbhub --config /home/agentops/.config/agent-db/dbhub.toml
claude

Follow the Claude Code MCP connection documentation to inspect connection status and tool permissions. Ask it to report current_user and read the approved view with a small limit. Review the exposed tools before approving them, and do not enable a blanket permission bypass.

7. Keep production changes behind a separate operator

Use the connection for bounded investigation, then have Claude Code propose SQL changes as reviewable files. A human should inspect the target database and expected affected rows before running approved changes through a separate operator identity. Never make the read-only role temporarily powerful to save a context switch.

A read query can still consume memory or hold locks long enough to affect application work. Require bounded filters and review query plans before expensive investigations. If repeated analysis competes with the application, move that work to a sanitized restore or a reporting system.

Keep connection and error logs attributable to agent_reader, and inspect unexpected authentication attempts. Avoid indiscriminate statement logging when SQL can contain sensitive literals. Treat text returned from database rows as untrusted input, never as permission to run another tool.

Recheck access whenever migrations add functions, change view definitions or alter role grants. New tables should remain unavailable until explicitly approved. Track these grants alongside your schema migrations so a later deploy does not quietly widen the agent's access.

8. Rehearse revocation and restore an isolated backup

Before handing this connection recurring work, rehearse cutting it off from your human administrator session. Disabling login blocks new authentication but leaves existing connections alive. Terminate the role's current sessions as a separate action.

ALTER ROLE agent_reader NOLOGIN;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'agent_reader'
  AND pid <> pg_backend_pid();

Rotate the restricted password if exposure is suspected, then explicitly re-enable login after resolving the cause. Keep the revocation commands somewhere available without the agent. Removing an MCP configuration alone does not revoke a credential the process already received.

Your next task is to restore a current backup into an isolated database using the PostgreSQL backup and restore drill procedure. Keep application workers disconnected from the restored copy and verify representative records there. Record the backup location and restore commands before authorizing recurring production investigations.