PostgreSQL Setup

Connect Slateo to your PostgreSQL database using a dedicated read-only user. This guide covers user creation, network access options (PrivateLink, SSH tunnel, or direct connection), and security best practices.

Overview

PostgreSQL supports fine-grained access control through users, roles, and grants. Slateo can connect to your PostgreSQL database using multiple network access methods depending on your security requirements:

• AWS PrivateLink: Secure private connection without exposing your database to the public internet (AWS only)
• SSH Tunnel: Secure connection through a bastion host
• Direct Connection: Direct TCP connection (requires network access configuration)

Prerequisites

Before setting up the connection, ensure you have:

• An active PostgreSQL database (version 10 or higher)
• Superuser or CREATEDB privileges to create users and grant permissions
• At least one schema with tables you want Slateo to access
• Network access configured (see network access options below)

Setup steps

Step 1: Create a read-only database user

Create a dedicated user for Slateo with read-only permissions. Connect to your PostgreSQL database using psql or a SQL client, then run:

-- Create the user
CREATE USER slateo_readonly WITH PASSWORD '<secure-password>';

-- Grant connect privilege on the database
GRANT CONNECT ON DATABASE <your_database> TO slateo_readonly;

-- Connect to the database
\c <your_database>

-- Grant usage on the schema
GRANT USAGE ON SCHEMA <your_schema> TO slateo_readonly;

-- Grant SELECT on all existing tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA <your_schema> TO slateo_readonly;

-- Grant SELECT on all existing sequences (for serial columns)
GRANT SELECT ON ALL SEQUENCES IN SCHEMA <your_schema> TO slateo_readonly;

-- Grant SELECT on all future tables in the schema
ALTER DEFAULT PRIVILEGES IN SCHEMA <your_schema>
  GRANT SELECT ON TABLES TO slateo_readonly;

-- Grant SELECT on all future sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA <your_schema>
  GRANT SELECT ON SEQUENCES TO slateo_readonly;

-- Verify the grants
\dp <your_schema>.*

Replace:

• <secure-password>: A strong, unique password
• <your_database>: Your database name
• <your_schema>: Your schema name (typically public unless you use custom schemas)

PostgreSQL user management documentation →

ALTER DEFAULT PRIVILEGES FOR USER <etl_user> IN SCHEMA <your_schema>
  GRANT SELECT ON TABLES TO slateo_readonly;

SELECT DISTINCT tableowner FROM pg_tables WHERE schemaname = '<your_schema>';

Multiple schemas:

If you have multiple schemas, repeat the grants for each one:

-- Example: Grant access to multiple schemas
GRANT USAGE ON SCHEMA public TO slateo_readonly;
GRANT USAGE ON SCHEMA analytics TO slateo_readonly;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO slateo_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO slateo_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO slateo_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO slateo_readonly;

Security best practices:

• Use a strong, unique password (minimum 16 characters)
• Only grant SELECT permissions—no write access needed
• Limit access to specific schemas containing data you want Slateo to access
• Consider using pg_read_all_data role for PostgreSQL 14+ (see Advanced Configuration)

Step 2: Configure network access

Choose the network access method that best fits your security requirements.

Option A: AWS PrivateLink (recommended for AWS)

AWS PrivateLink provides secure private connectivity without exposing your database to the public internet.

Prerequisites:

• PostgreSQL database running in AWS (RDS, EC2, or Aurora)
• Network Load Balancer (NLB) in front of your database
• VPC Endpoint Service configured

See the AWS PrivateLink connectivity guide for detailed setup instructions. Select Databases → PostgreSQL and follow Steps 1-4.

Once PrivateLink is configured, Slateo will provide you with the endpoint DNS name to use in Step 3.

Option B: SSH Tunnel (recommended for non-AWS or strict security)

SSH tunneling provides secure access through a bastion host.

Prerequisites:

• SSH bastion host with public IP or hostname
• SSH key pair for authentication
• Bastion host has network access to your PostgreSQL database

Setup:

1. Create an SSH user on your bastion host for Slateo
2. Generate an SSH key pair (or use an existing one)
3. Add the public key to ~/.ssh/authorized_keys on the bastion host
4. Verify SSH access:

   ssh -i /path/to/private_key user@bastion-host
   

   CopyCopied!
5. Verify the bastion can reach your database:

   nc -zv <database-host> 5432
   

   CopyCopied!

Contact your Slateo account manager to provide:

• Bastion host IP or hostname
• SSH port (typically 22)
• SSH username
• SSH private key

Option C: Direct Connection (development only)

For development or when your database is in a secure network, you can use a direct TCP connection.

Prerequisites:

• PostgreSQL database with a publicly accessible IP or hostname
• Firewall rules allowing inbound traffic on port 5432 from Slateo's IP addresses

Contact your Slateo account manager for the current list of IP addresses to allowlist.

Configure PostgreSQL to accept remote connections:

1. Edit postgresql.conf:

   listen_addresses = '*'  # or specific IP addresses
   

   CopyCopied!

2. Edit pg_hba.conf to allow connections from Slateo's IP addresses:

   # Allow Slateo IP addresses
   host    <your_database>    slateo_readonly    1.2.3.4/32    md5
   host    <your_database>    slateo_readonly    5.6.7.8/32    md5
   

   CopyCopied!

3. Restart PostgreSQL:

   sudo systemctl restart postgresql
   

   CopyCopied!

PostgreSQL client authentication documentation →

Step 3: Configure the connection in Slateo

Add your PostgreSQL connection in the Slateo admin panel.

1. Log in to your Slateo workspace
2. Navigate to Admin → Data Sources → Add Data Source
3. Select PostgreSQL
4. Enter the connection details:
   • Name: A friendly name for this connection (e.g., Production Analytics)
   • Host: Your database hostname or IP address
     • For PrivateLink: Use the endpoint DNS name provided by Slateo
     • For SSH tunnel: Use localhost or the internal database hostname
     • For direct connection: Use the public hostname or IP
   • Port: 5432 (default PostgreSQL port, or your custom port)
   • Database: Your database name
   • User: slateo_readonly
   • Password: The password you created in Step 1
   • SSL Mode: require (recommended) or prefer
   • SSH Tunnel (if applicable):
     • Enable SSH tunnel
     • Enter bastion host, port, username, and private key
5. Click Test Connection to verify connectivity
6. Click Save to complete the setup

Next steps

After you complete the setup:

1. Schema discovery: Slateo automatically scans your database schema to discover available tables and views (typically within 5 minutes)
2. Verify access: Check that all expected schemas and tables appear in your Slateo workspace
3. Start querying: Navigate to your workspace to start analyzing your PostgreSQL data

Security considerations

Credential management

• Use strong passwords: Generate passwords with at least 16 characters including uppercase, lowercase, numbers, and special characters
• Rotate credentials periodically: Update the password every 90 days
• Store credentials securely: Use a password manager or secure vault
• Enable SSL/TLS: Always use encrypted connections

To rotate the password:

ALTER USER slateo_readonly WITH PASSWORD '<new-secure-password>';

Then update the password in the Slateo admin panel.

Access control

• Principle of least privilege: Only grant access to schemas that Slateo needs
• Use read-only permissions: Never grant INSERT, UPDATE, DELETE, or DDL permissions
• Review grants regularly: Audit user permissions quarterly
• Enable audit logging: Monitor query activity for unusual patterns

To review current grants:

-- View all grants for the user
\du slateo_readonly

-- View table-level grants
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'slateo_readonly';

SSL/TLS encryption

Always use SSL/TLS to encrypt connections between Slateo and your database.

Configure PostgreSQL to require SSL:

1. Edit postgresql.conf:

   ssl = on
   ssl_cert_file = '/path/to/server.crt'
   ssl_key_file = '/path/to/server.key'
   

   CopyCopied!

2. Edit pg_hba.conf to require SSL:

   hostssl    <your_database>    slateo_readonly    0.0.0.0/0    md5
   

   CopyCopied!

3. Restart PostgreSQL

PostgreSQL SSL documentation →

Monitoring and auditing

Enable query logging to monitor Slateo activity:

-- Enable logging for the Slateo user
ALTER USER slateo_readonly SET log_statement = 'all';
ALTER USER slateo_readonly SET log_duration = on;

-- View recent queries by the user
SELECT
  usename,
  datname,
  query,
  query_start,
  state
FROM pg_stat_activity
WHERE usename = 'slateo_readonly'
ORDER BY query_start DESC;

PostgreSQL monitoring documentation →

Troubleshooting

Authentication errors

If Slateo reports authentication failures:

1. Verify the user exists:

   SELECT usename FROM pg_user WHERE usename = 'slateo_readonly';
   

   CopyCopied!
2. Test the credentials locally:

   psql -h <host> -p 5432 -U slateo_readonly -d <database>
   

   CopyCopied!
3. Check pg_hba.conf allows connections from Slateo's IP addresses
4. Verify the password is correct

Connection timeouts

If Slateo reports connection timeouts:

1. Verify the database is running:

   sudo systemctl status postgresql
   

   CopyCopied!
2. Check firewall rules allow inbound traffic on port 5432
3. For PrivateLink: Verify the VPC endpoint is in "Available" status
4. For SSH tunnel: Verify the bastion host is reachable and can connect to the database
5. Test connectivity:

   nc -zv <host> 5432
   

   CopyCopied!

Permission errors

If Slateo can connect but cannot query certain tables or views:

1. Verify the user has the correct grants:

   SELECT grantee, table_schema, table_name, privilege_type
   FROM information_schema.table_privileges
   WHERE grantee = 'slateo_readonly' AND table_schema = '<your_schema>';
   

   CopyCopied!
2. Check if default privileges are configured:

   SELECT * FROM pg_default_acl;
   

   CopyCopied!
3. Verify the user has USAGE on the schema:

   SELECT nspname, nspacl
   FROM pg_namespace
   WHERE nspname = '<your_schema>';
   

   CopyCopied!
4. Test access by switching to the Slateo user:

   psql -h <host> -U slateo_readonly -d <database> -c "SELECT * FROM <schema>.<table> LIMIT 10;"
   

   CopyCopied!

SSL/TLS errors

If connection fails with SSL errors:

1. Verify SSL is enabled in postgresql.conf:

   SHOW ssl;
   

   CopyCopied!
2. Check SSL certificate validity:

   openssl x509 -in /path/to/server.crt -text -noout
   

   CopyCopied!
3. Try connecting with sslmode=prefer instead of require to diagnose the issue
4. Check PostgreSQL logs for SSL-related errors:

   sudo tail -f /var/log/postgresql/postgresql-*.log
   

   CopyCopied!

Schema not appearing in Slateo

If a schema doesn't appear in Slateo after granting access:

1. Verify the schema exists and contains tables:

   SELECT schemaname, tablename FROM pg_tables WHERE schemaname = '<your_schema>';
   

   CopyCopied!
2. Check that the user has USAGE on the schema:

   SELECT nspname, nspacl FROM pg_namespace WHERE nspname = '<your_schema>';
   

   CopyCopied!
3. Trigger a manual schema refresh in Slateo:
   • Go to Admin → Data Sources
   • Find your PostgreSQL connection
   • Click Refresh Schema

Advanced configuration

Using pg_read_all_data role (PostgreSQL 14+)

PostgreSQL 14 introduced the pg_read_all_data predefined role, which grants read access to all tables, views, and sequences in all schemas:

-- Grant the pg_read_all_data role
GRANT pg_read_all_data TO slateo_readonly;

-- Grant connect privilege
GRANT CONNECT ON DATABASE <your_database> TO slateo_readonly;

This simplifies permission management but grants broader access than schema-specific grants.

PostgreSQL predefined roles documentation →

Using read replicas

For production databases, consider connecting Slateo to a read replica to avoid impacting primary database performance:

1. Create a read replica of your primary database
2. Configure the read-only user on the replica
3. Use the replica hostname in the Slateo connection settings

PostgreSQL replication documentation →

Connection pooling

For high-concurrency workloads, consider using a connection pooler like PgBouncer:

1. Install and configure PgBouncer in front of your PostgreSQL database
2. Configure PgBouncer with appropriate pool settings
3. Use the PgBouncer hostname and port in the Slateo connection settings

PgBouncer documentation →

Using IAM authentication (AWS RDS)

For AWS RDS PostgreSQL, you can use IAM database authentication instead of passwords:

1. Enable IAM database authentication on your RDS instance
2. Create an IAM policy granting rds-db:connect permission
3. Create the database user with rds_iam role
4. Contact your Slateo account manager for IAM authentication setup

RDS IAM authentication documentation →

Additional resources

• PostgreSQL access control overview
• PostgreSQL security best practices
• PostgreSQL connection settings
• PostgreSQL performance tuning

For additional support or questions, contact your Slateo account manager.