PostgreSQL

Connect a PostgreSQL database to let CloudThinker agents read schemas, rank slow queries, recommend indexes, and check vacuum, lock, and replication health. You give CloudThinker one connection URL for a dedicated read-only user, and the connection runs in restricted mode: every query is read-only, so agents can’t write even with your approval.

Prerequisites

A PostgreSQL endpoint CloudThinker can reach from the internet, such as self-hosted PostgreSQL, Amazon RDS or Aurora, Cloud SQL, or Azure Database for PostgreSQL. Agents connect from CloudThinker Cloud, so localhost and private addresses resolve there, not on your network.
No private-link or VPN option exists for this connection yet. If the database is private, expose an endpoint restricted to CloudThinker’s egress IPs. Exposing a read replica keeps the primary private, but its query statistics cover only the replica’s own workload. See Reaching private services.
An admin account that can run CREATE ROLE and GRANT.
The pg_stat_statements extension, if you want slow-query ranking and workload index advice. Everything else works without it.

Setup

Create a read-only user

Connect as an admin and create a login role for CloudThinker:

CREATE ROLE cloudthinker_readonly WITH LOGIN PASSWORD '<secure-password>';

Grant read access

Run these in the database you want analyzed. Repeat the schema lines for every schema besides public that agents should read.

GRANT CONNECT ON DATABASE your_database TO cloudthinker_readonly;
GRANT USAGE ON SCHEMA public TO cloudthinker_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cloudthinker_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO cloudthinker_readonly;
GRANT pg_monitor TO cloudthinker_readonly;

Replace app_owner with the role that creates your application’s tables, often the one your migrations run as. Default privileges only cover tables created by the role you name, so repeat the line for each role that creates tables.pg_monitor lets agents see every session, lock, and query statistic, not only their own.

Turn on pg_stat_statements

Check first, since many managed services already preload it:

SHOW shared_preload_libraries;

If pg_stat_statements is missing, add it to shared_preload_libraries and restart PostgreSQL. On RDS, Aurora, or Cloud SQL, set it in the parameter group or database flags and reboot. Skip the restart if it’s already listed. Then, as an admin in the target database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Success state: signed in as cloudthinker_readonly, SELECT count(*) FROM pg_stat_statements; returns a number instead of an error. That also proves the pg_monitor grant works.

Open network access

Allow inbound connections on the PostgreSQL port (default 5432) from CloudThinker’s egress IPs in your firewall, security group, or authorized networks list. These docs don’t publish CloudThinker’s egress IP list: ask your CloudThinker contact, or contact us.On self-hosted PostgreSQL, also let cloudthinker_readonly sign in from those addresses in pg_hba.conf, one line per IP:

hostssl your_database cloudthinker_readonly <cloudthinker-ip>/32 scram-sha-256

Then reload the config with SELECT pg_reload_conf();. Managed services handle this for you.

Add the connection in CloudThinker

Open More → Workspace settings → Connections, select PostgreSQL, and click Connect on its detail panel. In the dialog, optionally change the Alias and Description (agents read the description to pick the right database when you connect several). Paste the URL into DATABASE_URI and click Connect:

postgresql://cloudthinker_readonly:<secure-password>@db.example.com:5432/your_database?sslmode=require

Success state: the connection shows Connecting and Verifying, then Connected. CloudThinker then runs Discovery once: a read-only look at the database whose results show in the Discovery section of the connection’s detail panel.

Connection details

Field	Required	Description	Example
Alias	No	Short name for this database. Prefilled with the next free alias, such as `postgres-1`	`prod-db`
Description	No	What this database is. Agents read it to choose between instances	`Production orders database`
DATABASE_URI	Yes	Full connection URL: user, password, host, port, database, and SSL mode	`postgresql://cloudthinker_readonly:<password>@db.example.com:5432/shop?sslmode=require`

The form labels the URL field DATABASE_URI. Each connection covers the one database named in the URL. To analyze another database, add another PostgreSQL connection with a different Alias. If the password contains @, :, /, or #, URL-encode it (for example, @ becomes %40), or the URL won’t parse.

Required permissions

You want agents to	Grant
Read schemas, tables, indexes, and run read-only queries	`CONNECT` on the database, `USAGE` on each schema, `SELECT` on its tables
See all sessions, locks, and blocking queries	`pg_monitor`
Rank slow queries and get workload index advice	`pg_stat_statements` extension plus `pg_monitor`
Test an index without building it	The `hypopg` extension, installed by an admin. Managed services differ, so check your provider’s supported-extensions list

Keep the user read-only. Restricted mode already blocks writes from agents (unlike MySQL, where writes go through Approvals), but a SELECT-only role means a mistake in either layer still can’t change data.

Agent capabilities

Capability	What agents can do
Schema inspection	List schemas, tables, views, and sequences, and read column types, constraints, and indexes
Read-only SQL	Run `SELECT` queries to answer questions about your data
Slow queries	Rank statements by total time, mean time, or overall resource use from `pg_stat_statements`
Query plans and index advice	Explain a query, recommend indexes for one query or the whole workload, and test hypothetical indexes with `hypopg`
Health checks	Find invalid, duplicate, bloated, and unused indexes, cache hit rates, sequences near their limit, and connection use
Vacuum and locks	Report table bloat, overdue autovacuum, transaction ID wraparound risk, and blocking queries
Replication	Check replica lag and replication slot health

Verify the connection

List the schemas and tables in the connected PostgreSQL database with their sizes

Expect table names grouped by schema with their sizes. A permission denied error names the object the user can’t read.

Example prompts

Which 10 queries use the most resources on PostgreSQL, and what indexes would help them?
Run a health check on the database and list the indexes I can safely drop
Is anything blocking right now? Show the blocking and waiting queries

Troubleshooting

Connection refused or timed out

CloudThinker can’t reach the host. Check that the host and port in DATABASE_URI are public or otherwise reachable, that your firewall or security group allows CloudThinker, and that listen_addresses in postgresql.conf isn’t localhost. On RDS, the instance must be publicly accessible or reachable through your network setup.

Password authentication failed or no pg_hba.conf entry

The user or password is wrong, or pg_hba.conf doesn’t allow this user from CloudThinker’s address. Check that the password is URL-encoded in DATABASE_URI, add a pg_hba.conf line for the user, and reload PostgreSQL.

pg_stat_statements not found, or no slow-query data

The extension isn’t loaded or created. Confirm SHOW shared_preload_libraries; includes pg_stat_statements, restart if you just added it, and run CREATE EXTENSION pg_stat_statements; in the target database. Statistics start from the last reset, so a fresh server has little history.

Permission denied for a table or schema

The user lacks USAGE on the schema or SELECT on the table. Tables created after your grants need the ALTER DEFAULT PRIVILEGES FOR ROLE line, naming the role that creates them.

HypoPG not installed

Hypothetical index tests need the hypopg extension. Install it, or ask for index recommendations without testing them.

Security

Least privilege — grant only the permissions the agents need for your use case; start read-only and widen later.
Read-only by default — use read-only credentials unless you want agents to make changes through this connection.
Rotate credentials — rotate keys and tokens on your normal schedule; CloudThinker picks up the new value when you update the connection.
Revoke on offboarding — remove the credential at the provider when you delete a connection or a teammate leaves.

Encrypt in transit: put sslmode=require in DATABASE_URI so credentials and results never cross the network in plain text. verify-full also needs your server’s CA certificate on CloudThinker’s side, and the connection has no field to supply one, so use require.
Restricted mode on every query: CloudThinker launches the PostgreSQL connection read-only, so agents can’t write even if the role’s grants would allow it.

CockroachDB

Connect a PostgreSQL-compatible CockroachDB cluster with separate read and write users.

MySQL

Connect MySQL with a read-only user too. Unlike PostgreSQL, MySQL writes go through Approvals instead of being blocked.

Connections

Customize

Approvals

Workspace settings

Admin Settings

Artifacts and sharing

Prerequisites

Setup

Connection details

Required permissions

Agent capabilities

Verify the connection

Example prompts

Troubleshooting

Security

CockroachDB

MySQL

​Prerequisites

​Setup

​Connection details

​Required permissions

​Agent capabilities

​Verify the connection

​Example prompts

​Troubleshooting

​Security

​Related

CockroachDB

MySQL

Prerequisites

Setup

Connection details

Required permissions

Agent capabilities

Verify the connection

Example prompts

Troubleshooting

Security

Related