Postgres Local

• PostgreSQL client tools installed on the system (usually included with PostgreSQL server installation)
• Configured PostgreSQL connection profiles in VS Code (managed by the PostgreSQL extension or MCP tools)
• Terminal access via zsh or bash

Procedure

1. Discover Saved Connection Profiles

Goal: List all configured PostgreSQL connections and their details.

Steps:

1. In chat, request to list PostgreSQL connection profiles
2. Agent calls mcp_postgresql_mc_pgsql_list_connection_profiles
3. Review returned profiles with:
   • Profile ID (internal identifier)
   • Profile name (human-readable label)
   • Hostname
   • Port (if non-standard)
   • Default database

Example output:

Profile: "Local"
- ID: 7DF9E14E-4178-417B-B073-F4F9382645DD
- Host: 127.0.0.1
- Default DB: nutrivision

2. Connect to a Database

Goal: Establish an active connection to a specific database.

Steps:

1. Obtain the target profile ID from step 1 (or request a specific profile name)
2. Specify the optional database name if different from profile's default
3. Agent calls mcp_postgresql_mc_pgsql_connect with the profile ID
4. On success, you receive a connection ID for use in subsequent queries

Connection ID format: mcp://<profile-id>[/<database-name>]

Example:

Connected: mcp://7DF9E14E-4178-417B-B073-F4F9382645DD/nutrivision

3. List All Databases on the Connected Server

Goal: Enumerate all databases available on the PostgreSQL instance.

Steps:

1. Ensure you have an active connection (from step 2)
2. Agent calls mcp_postgresql_mc_pgsql_list_databases with the connection ID
3. Review the database names returned

Example:

Databases:
- karigarverse
- nutrivision
- postgres
- redpetal
- upi_fraud_db

4. Run SQL Queries and Inspect Data

Goal: Execute read-only queries (SELECT, EXPLAIN, SHOW) against the connected database.

Steps:

1. Specify the SQL query (only SELECT, SHOW, and EXPLAIN are allowed)
2. Optionally provide:
   • Query name (for documentation)
   • Query description (purpose)
   • Validation queries (to verify literal values used in the query)
3. Agent calls mcp_postgresql_mc_pgsql_query with connection ID and formatted query
4. Review results returned as CSV or structured data

Best practices:

• Include validation queries for any literal values (e.g., checking if a value exists before filtering by it)
• Use EXPLAIN prefix for performance analysis
• Format queries across multiple lines for readability

Example query: Get active user and current database

SELECT
  current_user AS current_user,
  session_user AS session_user,
  current_database() AS current_database;

Example result:

current_user | session_user | current_database
-------------|--------------|------------------
proximus     | proximus     | nutrivision

5. Inspect Database Schema

Goal: Retrieve table structures, columns, indexes, and relationships.

Steps:

1. Connect to the target database (step 2)
2. Request schema inspection for a specific object type:
   • all — returns complete schema context
   • tables — list of all tables
   • views — list of all views
   • indexes — list of all indexes
   • functions — list of all user-defined functions
3. Agent calls mcp_postgresql_mc_pgsql_db_context with connection ID and object type
4. Schema is returned and can be used in subsequent queries for validation or reference

6. Find Stored PostgreSQL Credentials

Goal: Locate credentials stored locally (without exposing sensitive passwords).

Steps:

Option A: Check PostgreSQL password file (.pgpass)

cat ~/.pgpass

• Format: hostname:port:database:username:password
• If not found, credentials are not cached locally

Option B: Check environment variables

env | grep -i postgres

• Look for: PGPASSWORD, PGUSER, PGHOST, PGPORT, PGDATABASE

Option C: Verify active connection user

psql -U <username> -d <database> -c "SELECT current_user, session_user;"

• Returns the authenticated PostgreSQL role for the current session

Option D: List available PostgreSQL roles

psql -U <username> -d <database> -c "\du"

• Lists all roles and their attributes in the PostgreSQL instance

7. Test Connection Without Running a Query

Goal: Verify that a connection can be established without executing full queries.

Steps:

1. In chat, request to test connection to a specific profile or database
2. Agent calls mcp_postgresql_mc_pgsql_connect (establishes connection)
3. If successful, connection ID is returned
4. Optionally, run a simple query like SELECT version(); to confirm full connectivity
5. Disconnect using mcp_postgresql_mc_pgsql_disconnect with the connection ID

8. Troubleshoot Authentication Issues

Goal: Debug "permission denied" or "password required" errors.

Diagnosis checklist:

1. Can you connect to postgres database?

   • Run: psql -U <username> -d postgres
   • If yes → authentication is working
   • If no → check .pgpass or environment variables (step 6)

2. Is the username correct?

   • Run: whoami (your OS user)
   • For PostgreSQL, verify the role name with \du after connecting

3. Is the password required?

   • Check ~/.pgpass for pre-stored credentials
   • If not found and connection fails, password may be required
   • On local (localhost or 127.0.0.1), PostgreSQL may use peer or trust authentication (no password needed)

4. Is the database accessible by this role?

   • List databases: \l (in psql)
   • Check role permissions with: SELECT * FROM information_schema.role_table_grants WHERE grantee='<role>';

5. Check PostgreSQL logs (if local server):

   • macOS with Postgres.app: ~/Library/Application Support/Postgres/var-*/postgres.log
   • Homebrew PostgreSQL: /opt/homebrew/var/postgres/server.log

Tool Reference

Example Workflows

Workflow A: Connect and Explore a New Database

1. List profiles → Find "Local"
2. Connect to profile ID
3. List databases → Identify target DB
4. Connect to specific database
5. Inspect schema to understand structure
6. Run test query to verify access

Workflow B: Find Credentials and Authenticate

1. Check .pgpass file
2. Check environment variables
3. Test CLI connection: psql -U proximus -d nutrivision -c "\du"
4. If successful, no password needed (trust auth)

Workflow C: Run Analysis Query

1. Connect to database
2. Validate schema with db_context
3. Write query with validation queries for literal values
4. Execute query
5. Inspect results

Common Issues