​

PostgreSQL Integration

​

What Annie can do

• Discover the schema — list schemas, tables, views, sequences, and extensions, and read columns, constraints, and indexes
• Run SQL — execute read-only SELECT queries to answer questions about your application data
• Explain queries — show execution plans with EXPLAIN
• Find slow queries — rank the slowest or most resource-hungry queries from pg_stat_statements
• Check database health — indexes, connections, vacuum, sequences, replication, buffer cache, and constraints
• Recommend indexes — for your overall workload or for specific queries (uses the hypopg extension)

​

Quickstart

1. Create a dedicated read-only role for Annie.
2. Allow Anyshift’s outbound IPs to reach your database.
3. Go to the Anyshift integrations page.
4. In the Analytics section, open PostgreSQL and click Add connection.
5. Fill in the form:
   • Name — a label for this connection, for example Production PostgreSQL
   • Host — the public hostname or IP of your database, for example db.example.com
   • Port — defaults to 5432
   • Database — the database Annie should query
   • Username and Password — the read-only role you created
   • SSL mode — keep the default require (see SSL mode)
6. Click Save PostgreSQL connection.

​

Multiple connections

​

Security model

• Queries run through Annie’s PostgreSQL MCP server in restricted mode: every statement runs in a read-only transaction with a statement timeout. Restricted mode is enforced server-side and cannot be turned off.
• INSERT, UPDATE, DELETE, and DDL are refused.
• Your password is encrypted at rest with AWS KMS and is never exposed to the AI agent.
• Only publicly reachable hosts are accepted. Hosts that resolve to private, loopback, link-local, or other non-public addresses are rejected.

​

Create a read-only role

-- Login role for Annie
CREATE ROLE annie_ro LOGIN PASSWORD 'change-me-to-a-strong-password';

-- Allow connecting to the database
GRANT CONNECT ON DATABASE app_db TO annie_ro;

-- Read access to existing objects in the schema
GRANT USAGE ON SCHEMA public TO annie_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO annie_ro;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO annie_ro;

-- Read access to tables and sequences created later.
-- Run as the role that creates the tables, or add FOR ROLE <owner>.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO annie_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO annie_ro;

-- Optional: let Annie see query statistics from every role (pg_stat_statements)
GRANT pg_read_all_stats TO annie_ro;

-- Every session starts read-only
ALTER ROLE annie_ro SET default_transaction_read_only = on;

​

Network access

​

SSL mode

​

Example questions

• “Which tables in the orders database grew the most, and do they have the indexes they need?”
• “What are the slowest queries right now, and why is the top one slow?”
• “Run a health check on the production database: bloat, unused indexes, vacuum, and connections.”
• “How many orders failed per hour since yesterday’s deploy?”
• “Explain this query and suggest an index: SELECT * FROM events WHERE account_id = $1 ORDER BY created_at DESC LIMIT 50”

​

Troubleshooting

​

”Invalid PostgreSQL host”

• “host resolves to a non-public address” — the host resolves to a private, loopback, link-local, or other internal address. Annie only connects to publicly reachable databases. Use the database’s public endpoint.
• “could not resolve host” — the hostname does not exist in public DNS. Check the spelling, or use the public IP.

​

Status Error with a timeout

• Allow Anyshift’s outbound IPs on the PostgreSQL port
• Make sure the database is publicly accessible, for example Publicly accessible on Amazon RDS
• Check the host and port

​

Status Error with “password authentication failed”

• Check the username and password
• Make sure your pg_hba.conf (or provider settings) allows password logins from Anyshift’s IPs
• Make sure the role has LOGIN and CONNECT on the database

​

SSL errors

• “SSL is not enabled on the server” — the server does not accept TLS. Enable SSL on the server; switch to a weaker mode only if you accept unencrypted traffic.
• Certificate verification errors — switch from verify-ca / verify-full to require (see SSL mode).

​

Annie says a table is missing or permission is denied

​

Annie doesn’t use PostgreSQL

• Verify the connection shows Active on the integrations page
• Make sure Annie’s access to this connection is not disabled in your integration settings
• After fixing a connection in Error, update it so Anyshift re-tests it