Connect PostgreSQL to AnalytAI

Connect PostgreSQL Database to AnalytAI

Get your PostgreSQL database connected to AnalytAI in minutes! This guide covers two secure connection methods - choose the one that fits your setup.

⏱️ Time to complete: 5-15 minutes
🔧 Difficulty: Beginner to Intermediate
🔒 Security: Read-only access with dedicated users

Prerequisites

Before connecting PostgreSQL to AnalytAI, ensure you have:

• ✅ PostgreSQL server running (version 9.5 or higher)
• ✅ Admin access to create users and modify settings
• ✅ Network access to your PostgreSQL server
• ✅ Database name and credentials ready

---

Connection Types

AnalytAI supports two main ways to connect to PostgreSQL:

1. Cloud/Remote Server - Direct connection to hosted PostgreSQL
2. Local Computer with VPN - Secure access via Tailscale VPN

---

Setup 1: Cloud or Remote PostgreSQL Server

Perfect for databases hosted on cloud providers (AWS RDS, Google Cloud SQL, Azure Database, Heroku, etc.) or any remote server.

Step 1: Create a Secure Read-Only User

Connect to your PostgreSQL server as an admin user and run these commands:

-- Create a dedicated user for AnalytAI (read-only access only)
CREATE USER analytai_user WITH PASSWORD 'YourSecurePassword123!';

-- Allow the user to connect to your database
GRANT CONNECT ON DATABASE your_database_name TO analytai_user;

-- Grant access to the public schema (where most tables are)
GRANT USAGE ON SCHEMA public TO analytai_user;

-- Grant read-only access to all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytai_user;

-- Grant read-only access to all future tables created in this schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytai_user;

Security Note: Never use your postgres/admin password. Create a dedicated user with minimal required permissions.

Step 2: Add to AnalytAI

In the AnalytAI application, enter these connection details:

• Host: Your server address (e.g., postgres.example.com or 192.168.1.100)
• Port: 5432 (default PostgreSQL port)
• Username: analytai_user
• Password: YourSecurePassword123!
• Database: your_database_name

Step 3: Test Connection

AnalytAI will test the connection automatically. If successful, you’ll see your database tables and can start querying!

---

Setup 2: Local Computer with Tailscale VPN

Best for accessing PostgreSQL running on your local computer from anywhere securely. Uses VPN technology to create a secure connection.

Step 1: Install Tailscale

Download and install Tailscale on your computer:

1. Download: Visit tailscale.com/download and download for your operating system
2. Install: Run the installer and follow the setup wizard
3. Don’t login yet: When Tailscale opens, close it without logging in

Step 2: Get Your VPN Key

Contact AnalytAI support team to receive your personal authentication key. We’ll provide this securely.

Step 3: Connect to AnalytAI Network

Open Command Prompt or PowerShell as Administrator and connect:

# Join AnalytAI's secure network
tailscale up --login-server=https://headscale.byvent.com --authkey=YOUR_KEY_HERE --accept-routes

# Check if you're connected
tailscale status

# Get your VPN IP address
tailscale ip -4

Expected output: Something like 100.64.0.6 - this is your secure VPN address.

Step 4: Set Up PostgreSQL User for VPN

Create a PostgreSQL user that can connect through the VPN:

# Connect to PostgreSQL as admin
cd "C:\Program Files\PostgreSQL\17\bin"
.\psql.exe -U postgres

Run these SQL commands:

-- Create dedicated user for VPN connections (read-only)
CREATE USER analytai_user WITH PASSWORD 'YourSecurePassword123!';

-- Allow connection to your database
GRANT CONNECT ON DATABASE your_database_name TO analytai_user;

-- Grant access to the public schema
GRANT USAGE ON SCHEMA public TO analytai_user;

-- Grant read-only access to all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytai_user;

-- Grant read-only access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytai_user;

-- Exit
\q

Step 5: Configure PostgreSQL for Network Access

Edit PostgreSQL configuration to accept connections from outside:

# Open PostgreSQL config file as Administrator
notepad "C:\Program Files\PostgreSQL\17\data\postgresql.conf"

Find and change this line:

# Change FROM:
#listen_addresses = 'localhost'

# Change TO:
listen_addresses = '*'

Save the file and restart PostgreSQL:

Restart-Service postgresql-x64-17

Step 6: Configure Client Authentication

Allow VPN connections in the authentication file:

# Open the authentication config file
notepad "C:\Program Files\PostgreSQL\17\data\pg_hba.conf"

Add this line at the end of the file:

# Allow VPN connections from AnalytAI network
host    all    analytai_user    100.64.0.0/10    md5

Save the file and restart PostgreSQL again:

Restart-Service postgresql-x64-17

Step 7: Configure Firewall

Allow VPN connections to reach PostgreSQL:

# Create firewall rule for VPN access
New-NetFirewallRule -DisplayName "PostgreSQL VPN Access" -Direction Inbound -LocalPort 5432 -Protocol TCP -RemoteAddress 100.64.0.0/10 -Action Allow

Step 8: Connect in AnalytAI

Use your VPN IP address in the connection settings:

• Host: Your Tailscale IP (from Step 3, e.g., 100.64.0.6)
• Port: 5432
• Username: analytai_user
• Password: YourSecurePassword123!
• Database: your_database_name

---

Connection Checklist ✅

Before testing your connection, verify:

• PostgreSQL service is running
• Tailscale is connected (tailscale status shows “Tailscale is up”)
• You have your VPN IP (tailscale ip -4)
• Firewall rule is active
• PostgreSQL user exists and has correct permissions

---

Troubleshooting Guide

Having trouble connecting? Follow these steps in order to identify and fix common issues.

---

🔍 Quick Connection Tests

Test 1: Is PostgreSQL Running?

# Check if PostgreSQL service is running
Get-Service postgresql*

# Or check if port 5432 is listening
netstat -an | findstr 5432

Test 2: Can You Connect Locally?

# Try connecting to PostgreSQL locally first
cd "C:\Program Files\PostgreSQL\17\bin"
.\psql.exe -U postgres -d your_database -c "SELECT 1 as test;"

Test 3: Verify Your User Exists

-- Connect to PostgreSQL and check users
\du analytai_user
SELECT * FROM pg_user WHERE usename = 'analytai_user';

---

🚨 Common Issues & Solutions

❌ “Connection Refused” or “Can’t Connect”

For Cloud/Remote Servers:

• Firewall blocking port 5432: Ask your server admin to allow port 5432 from AnalytAI’s IP ranges
• PostgreSQL not accepting remote connections: Check listen_addresses in postgresql.conf is not set to localhost
• Wrong authentication method: User might need different auth method in pg_hba.conf

For Local VPN Setup:

• Tailscale not connected: Run tailscale status - should show “Tailscale is up”
• Wrong IP address: Use tailscale ip -4 to get your current VPN IP
• Firewall blocking: Ensure the firewall rule was created successfully

❌ “Authentication Failed” Errors

• Wrong username/password: Double-check credentials (case-sensitive!)
• User doesn’t exist: Run the user creation commands again
• Wrong authentication method: Check pg_hba.conf - might need md5 instead of peer
• Password expired: PostgreSQL passwords don’t expire by default, but check anyway

❌ “Permission Denied” Errors

• Missing schema permissions: User needs GRANT USAGE ON SCHEMA public
• Missing table permissions: Run GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytai_user
• Future tables not covered: Add ALTER DEFAULT PRIVILEGES for new tables

❌ “Database Does Not Exist” Error

• Typo in database name: Check the exact database name in PostgreSQL
• Wrong database selected: User might not have CONNECT permission to that database
• Database doesn’t exist: Verify the database was created and spelled correctly

❌ VPN-Specific Issues

• Tailscale won’t connect: Try tailscale down then tailscale up again
• Auth key expired: Contact AnalytAI support for a new key
• Network conflicts: Restart Tailscale service if having issues

---

🔧 Advanced Diagnostics

Check PostgreSQL Error Logs:

# View recent PostgreSQL errors (location varies by version)
Get-Content "C:\Program Files\PostgreSQL\17\data\log\postgresql-*.log" -Tail 20

Test Connection with psql Client:

# Test with psql command line client
cd "C:\Program Files\PostgreSQL\17\bin"
.\psql.exe -h YOUR_HOST -p 5432 -U analytai_user -d YOUR_DATABASE -c "SELECT 1;"

Check Network Connectivity:

# Test if port 5432 is reachable
Test-NetConnection -ComputerName YOUR_HOST -Port 5432

---

📞 Still Having Issues?

Before contacting support, please provide:

1. Error message: Exact error you’re seeing
2. Setup type: Cloud server or local VPN?
3. Test results: Output from the diagnostic commands above
4. PostgreSQL version: SELECT version();
5. Operating system: Windows version and edition

Contact AnalytAI Support:

• Email: support@analytai.com
• Include your diagnostic information for faster resolution!

---

Security Best Practices

🔒 Important Security Notes:

• Never use postgres superuser for application connections
• Use strong, unique passwords for database users
• Limit user permissions to SELECT only when possible
• Regularly rotate passwords and monitor access logs
• Keep PostgreSQL updated with latest security patches
• Use VPN for local databases instead of exposing them directly

---

Success Indicators ✅

Your PostgreSQL connection is working when you can:

• ✅ See your database tables in AnalytAI
• ✅ Run queries without connection errors
• ✅ View data in your tables and columns
• ✅ Create visualizations from your PostgreSQL data

🎉 Setup Complete! Your PostgreSQL database is now connected to AnalytAI. Start exploring your data! 🚀