PostgreSQL

PostgreSQL can be used as a source database (the database LakeXpress extracts data from) and as the LakeXpress DB (the internal database LakeXpress uses for configuration, sync tracking, and pipeline logging). Both roles use the same connection parameter format.

Connection Parameters

Parameter	Required	Description
ds_type	Yes	Must be "postgres"
auth_mode	Yes	Must be "classic"
username	Yes	PostgreSQL username
password	Yes	PostgreSQL password
server	Yes	Hostname or IP
port	Yes	Port (default: 5432)
database	Yes	Database name

Required Permissions

Permissions differ depending on how you are using PostgreSQL:

-- When used as a source database (read-only access):
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lakexpress_user;
GRANT USAGE ON SCHEMA public TO lakexpress_user;

-- information_schema access is granted by default

-- When used as the LakeXpress DB (LakeXpress manages its own tables here):
GRANT ALL PRIVILEGES ON DATABASE lakexpress_log TO lakexpress_user;

Example: Source Database

{
  "postgres_prod": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_PG_USER}",
      "password": "$env{LX_PG_PASSWORD}",
      "server": "pg-prod.company.com",
      "port": 5432,
      "database": "analytics"
    }
  }
}

Example: LakeXpress DB

{
  "lxdb_postgres": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_LXDB_USER}",
      "password": "$env{LX_LXDB_PASSWORD}",
      "server": "pg-prod.company.com",
      "port": 5432,
      "database": "lakexpress_log"
    }
  }
}

Troubleshooting

Connection refused: Check PostgreSQL is running (systemctl status postgresql), verify pg_hba.conf allows your IP, check firewall (sudo ufw allow 5432/tcp).

Authentication failed: Verify credentials, check password encryption in pg_hba.conf, confirm user exists: SELECT * FROM pg_user WHERE usename='your_user';

See Also

• Database Connections Overview - Auth file format, security best practices