PostgreSQL

PostgreSQL Database Connection

PostgreSQL is a powerful, open-source object-relational database system. Datablast connects to PostgreSQL servers using standard connection parameters. This guide covers how to gather and configure the required credentials for both self-hosted and cloud PostgreSQL instances.

---

1. Required Connection Information

Essential Parameters

Parameter	Description	Default
Host	PostgreSQL server address (localhost, IP, or domain name)	localhost
Port	PostgreSQL server port number	5432
Database	Database name you want to connect to	postgres
Schema	Schema within the database (optional, defaults to ‘public’)	public
User	PostgreSQL username with appropriate permissions	-
Password	Password for the PostgreSQL user	-

Connection String Format

postgresql://user:password@host:port/database

---

2. Gathering PostgreSQL Credentials

From Your Database Administrator

Contact your DBA for:

• Server hostname or IP address
• Database name
• Schema name (if using custom schemas)
• Username and password
• Port number (if different from 5432)
• SSL requirements

From Cloud Providers

Amazon RDS PostgreSQL:

• Endpoint: mydb.abc123.us-east-1.rds.amazonaws.com
• Port: 5432 (default) or custom
• SSL connections available
• Master username and password

Supabase:

• Connection string provided in dashboard
• Uses connection pooling by default
• SSL/TLS required for connections
• Direct and pooled connection options

Google Cloud SQL:

• Instance connection name
• Public or private IP options
• Cloud SQL Proxy for secure connections
• IAM database authentication available

Azure Database for PostgreSQL:

• Server name: servername.postgres.database.azure.com
• Username format: username@servername
• SSL connection required

Heroku PostgreSQL:

• DATABASE_URL environment variable
• Color-coded database names
• Connection limit based on plan

---

3. Testing Connection via Command Line

Basic Connection Command

psql -h HOST -U USER -d DATABASE -p PORT

Long Form Options

psql --host=HOST --username=USER --dbname=DATABASE --port=PORT

Connection Examples

Local PostgreSQL:

psql -h localhost -U myuser -d mydatabase -p 5432

Remote PostgreSQL:

psql -h db.example.com -U appuser -d production_db -p 5432

With SSL:

psql "host=secure-db.com port=5432 dbname=mydb user=myuser sslmode=require"

Using Connection URI:

psql postgresql://user:password@host:port/database

💡 Security Tip: PostgreSQL will prompt for password when using -U option. Never include passwords in command history or scripts.

---

4. Configure Datablast Connection

Connection Form Fields

Field	Example Value	Notes
Connection Name	Production PostgreSQL	Friendly name for the UI
Host	db.mycompany.com	Server address or IP
Port	5432	Usually 5432 for PostgreSQL
Database	analytics_db	Database name
Schema	public	Schema within database
User	datablast_user	PostgreSQL username
Password	••••••••••••	User password (hidden)
Description	Main analytics database	Optional connection notes

Advanced Connection Options

SSL Mode:

• disable - No SSL
• require - SSL required
• verify-ca - Verify CA certificate
• verify-full - Full certificate verification

Connection Pooling:

• Useful for high-traffic applications
• PgBouncer integration
• Connection limits per user

---

5. Security Best Practices

User Permissions

Create a dedicated PostgreSQL user for Datablast:

-- Create user
CREATE USER datablast WITH PASSWORD 'secure_password';

-- Grant database connection
GRANT CONNECT ON DATABASE analytics_db TO datablast;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO datablast;

-- Grant table access
GRANT SELECT ON ALL TABLES IN SCHEMA public TO datablast;

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

Connection Security

• Use SSL/TLS: Enable encrypted connections for remote databases
• Limit Host Access: Configure pg_hba.conf for specific IP addresses
• Minimal Permissions: Grant only necessary database and table access
• Strong Authentication: Use SCRAM-SHA-256 or certificate-based auth

Network Configuration

PostgreSQL Configuration (postgresql.conf):

listen_addresses = 'localhost,10.0.0.0/8'
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Host-Based Authentication (pg_hba.conf):

# TYPE  DATABASE    USER         ADDRESS          METHOD
host    analytics   datablast    10.0.0.0/8       scram-sha-256
hostssl all         all          0.0.0.0/0        scram-sha-256

---

6. Password File Configuration

Using .pgpass File

Avoid entering passwords repeatedly by creating a password file:

Linux/macOS (~/.pgpass):

host:port:database:user:password
db.example.com:5432:analytics_db:datablast_user:my_secure_password

Windows (%APPDATA%\postgresql\pgpass.conf):

host:port:database:user:password
db.example.com:5432:analytics_db:datablast_user:my_secure_password

Set Proper Permissions:

chmod 600 ~/.pgpass

Environment Variables

export PGHOST=db.example.com
export PGPORT=5432
export PGDATABASE=analytics_db
export PGUSER=datablast_user
export PGPASSWORD=my_secure_password

---

7. Troubleshooting

Connection Refused

• Check PostgreSQL service: systemctl status postgresql
• Verify port: netstat -tlnp | grep 5432
• Test network connectivity: telnet host 5432
• Check firewall rules: Ensure port 5432 is open

Authentication Failed

• Verify credentials: Double-check username and password
• Check pg_hba.conf: Ensure host-based authentication allows your connection
• User exists: Confirm user exists in PostgreSQL
• Database access: Verify user has CONNECT permission

SSL/TLS Issues

• SSL mode mismatch: Server requires SSL but client doesn’t use it
• Certificate problems: Invalid or expired SSL certificates
• Protocol versions: Ensure compatible TLS versions

Permission Denied

• Schema access: User needs USAGE permission on schema
• Table access: User needs SELECT permission on tables
• Database access: User needs CONNECT permission on database

---

8. Common Cloud Provider Settings

Amazon RDS PostgreSQL

Host: mydb.cluster-xyz.us-east-1.rds.amazonaws.com
Port: 5432
Database: postgres
SSL Mode: require

Supabase

Host: db.your-project.supabase.co
Port: 5432
Database: postgres
SSL Mode: require

Google Cloud SQL

Host: 1.2.3.4 (Public IP)
Port: 5432
Database: postgres
SSL Mode: require

Azure Database for PostgreSQL

Host: myserver.postgres.database.azure.com
Port: 5432
User: username@myserver
SSL Mode: require

Heroku PostgreSQL

5432/dbname

# Use DATABASE_URL from Heroku config
heroku config:get DATABASE_URL

---

9. Performance Optimization

Connection Settings

-- Show current connection limits
SELECT setting FROM pg_settings WHERE name = 'max_connections';

-- Show active connections
SELECT count(*) FROM pg_stat_activity;

-- Check connection pool status (if using PgBouncer)
SHOW POOLS;

Query Performance

-- Enable query logging for analysis
SET log_statement = 'all';
SET log_min_duration_statement = 1000;

-- Analyze slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

---

Useful Links

• PostgreSQL Connection Parameters
• AWS RDS PostgreSQL Connection Guide
• Supabase Connection Guide

Always use encrypted connections for production databases and implement proper user access controls with minimal required permissions.