PostgreSQL Read-Only User Setup Guide

Add comprehensive documentation for creating a dedicated read-only PostgreSQL user before connecting to Lamatic, following security best practices similar to Airbyte's integration guide.

Problem

Currently, users may connect to Lamatic using admin/superuser credentials, which poses security risks. A clear guide for creating properly scoped read-only users is needed to ensure secure database connections.

Proposed Solution

Add a pre-setup section to the PostgreSQL integration documentation with step-by-step instructions for creating a dedicated read-only user.

Suggested Documentation Structure:

Step 1: Create a Dedicated Read-Only PostgreSQL User

1. Connect to your PostgreSQL database as a superuser:

psql -h <host> -p <port> -U <admin_user> -d <database>

2. Create a dedicated user for Lamatic:

CREATE USER lamatic_user WITH PASSWORD 'your_secure_password';

3. Grant connection privileges:

GRANT CONNECT ON DATABASE <your_database> TO lamatic_user;

4. Grant schema usage:

GRANT USAGE ON SCHEMA <schema_name> TO lamatic_user;

5. Grant read permissions on tables:

-- For existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO lamatic_user;

-- For future tables (optional)
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO lamatic_user;

6. Grant read permissions on sequences (if needed):

GRANT SELECT ON ALL SEQUENCES IN SCHEMA <schema_name> TO lamatic_user;

Step 2: Configure Lamatic Connection

Use the newly created lamatic_user credentials in your Lamatic PostgreSQL connection settings.

Step 3: Verify Connection

Test the connection to ensure the read-only user has appropriate access without write permissions.

Benefits

• Enhanced Security: Limits database access to read-only operations

• Principle of Least Privilege: Grants only necessary permissions

• Audit Trail: Clear separation between admin and integration users

• Risk Mitigation: Reduces potential impact of credential compromise

Acceptance Criteria

• [ ] Documentation includes complete SQL commands

• [ ] Examples cover common schema configurations

• [ ] Verification steps are provided

• [ ] Security considerations are highlighted

• [ ] Troubleshooting section for common permission issues

This addition would significantly improve the security posture of PostgreSQL integrations with Lamatic while providing users with clear, actionable guidance.