PostgreSQL

This page covers PostgreSQL as a source database, the database LakeXpress exports data from. To use PostgreSQL as the LakeXpress DB, see PostgreSQL as LakeXpress DB.

Connection Parameters

Parameter	Required	Description
ds_type	Yes	Must be "postgres"
auth_mode	Yes	"classic" (username/password) or "connection_string" (supply a full connection string)
username	Classic only	PostgreSQL username
password	Classic only	PostgreSQL password
server	Classic only	Hostname or IP
port	No	Port (default: 5432)
database	Classic only	Database name
sslmode	No	PostgreSQL SSL mode, e.g. disable, prefer, require, verify-full (default: prefer)

With auth_mode: "connection_string", only the connection_string field is used; username, password, server, port, database, and sslmode are ignored.

Required Permissions

LakeXpress only needs read access to the schemas it exports:

GRANT USAGE ON SCHEMA public TO lakexpress_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lakexpress_user;

information_schema access is granted by default.

Example: Source Database

{
  "source_postgres": {
    "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: Connection String Mode

{
  "postgres_cs": {
    "ds_type": "postgres",
    "auth_mode": "connection_string",
    "info": {
      "connection_string": "host=pg-prod.company.com port=5432 dbname=analytics user=$env{LX_PG_USER} password=$env{LX_PG_PASSWORD} sslmode=require"
    }
  }
}

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 - Credentials file format, security best practices