PostgreSQL

Connect your PostgreSQL databases to Memex for AI-powered data analysis.

What is PostgreSQL?

PostgreSQL is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance. It's used by organizations of all sizes for everything from small applications to large-scale data warehousing. Once connected, Memex can query your PostgreSQL databases, explore schemas, and help you analyze your data through natural conversation.

This connector is for generic PostgreSQL databases. If you're using Neon (serverless PostgreSQL) or Supabase, use their dedicated connectors for an optimized experience.

What You'll Need

Credential	Description	Example
Host	PostgreSQL server hostname or IP	postgres.example.com or 127.0.0.1
Port	PostgreSQL server port	5432 (default)
Database	Name of the database	myapp_production
Username	PostgreSQL username	app_user
Password	PostgreSQL password	Your secure password

Finding Your Credentials

For Cloud PostgreSQL Services

Amazon RDS for PostgreSQL:

1. Log in to the AWS Console and go to RDS

2. Select your PostgreSQL instance

3. Find the Endpoint (this is your host) and Port in the Connectivity section

4. Use the master username or a dedicated user you've created

Google Cloud SQL:

1. Go to the Cloud SQL instances page

2. Select your instance

3. Find the Public IP address or Private IP address for the host

4. Use credentials created through the Users section

Azure Database for PostgreSQL:

1. Go to your PostgreSQL server in the Azure Portal

2. Find the Server name in the Overview (this is your host)

3. Use credentials set up during server creation or additional users you've created

Heroku Postgres:

1. Go to your app's Resources tab

2. Click on Heroku Postgres

3. Go to Settings → Database Credentials → View Credentials

For Self-Hosted PostgreSQL

Contact your database administrator to obtain:

• The server hostname or IP address

• The port number (default is 5432)

• The database name

• A username and password with appropriate permissions

For security, request a database user with read-only access. Avoid using the postgres superuser account.

Connecting to Memex

1. Open the Memex Hub from the sidebar

2. Click on the Connectors tab

3. Find and click the PostgreSQL card

4. Enter your Host, Port, Database, Username, and Password

5. Give your connection a memorable name (e.g., "Production PostgreSQL")

6. Click Add Connection

Verifying Your Connection

Once connected, try asking Memex:

Show all tables in my PostgreSQL database

You should see a list of tables in your database.

Using Your Connection

Here are some examples of what you can ask:

What columns are in the users table and what are their types?

Show me the top 10 customers by total order value

How many rows are in each table in my database?

Create a monthly sales summary from my orders table

Security Considerations

User Permissions

Create a dedicated read-only user for Memex:

CREATE USER memex_reader WITH PASSWORD 'YourSecurePassword';
GRANT CONNECT ON DATABASE your_database TO memex_reader;
GRANT USAGE ON SCHEMA public TO memex_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO memex_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO memex_reader;

Never use the postgres superuser account for application connections. Always create dedicated users with minimal required permissions.

SSL/TLS Connections

PostgreSQL supports SSL/TLS for encrypted connections:

• Most cloud providers enable SSL by default

• For self-hosted instances, configure SSL in postgresql.conf

• Use sslmode=require or stricter for production connections

Network Security

• Configure pg_hba.conf to restrict which hosts can connect

• Use firewall rules to limit access to trusted IP addresses

• For cloud databases, use private networking or VPC when possible

• Consider using SSH tunnels for additional security

Troubleshooting

Connection Refused

• Verify the hostname and port are correct

• Check that PostgreSQL is running (systemctl status postgresql on Linux)

• Ensure PostgreSQL accepts remote connections (check listen_addresses in postgresql.conf)

• Verify pg_hba.conf allows connections from your location

• Check firewall rules allow connections on port 5432

Authentication Failed

• Double-check your username and password

• Verify the authentication method in pg_hba.conf matches your connection (e.g., md5, scram-sha-256)

• Ensure the user exists and has a password set

• Check if the user has CONNECT permission on the database

Database Does Not Exist

• Verify the database name is spelled correctly (PostgreSQL database names are case-sensitive by default)

• Ensure the database exists: \l in psql lists all databases

• Check that the user has permission to connect to the database

SSL Connection Required

• If the server requires SSL, ensure your connection is configured for SSL

• For cloud databases, SSL is often required by default

• Check the sslmode parameter in your connection settings

Learn More

• PostgreSQL Documentation

• PostgreSQL Administration

• PostgreSQL Client Authentication