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.
