PostgreSQL Backend for Pulumi

PostgreSQL Backend for Pulumi#

The pkg/backend/diy/postgres package provides a PostgreSQL-based backend implementation for Pulumi state storage. It stores Pulumi state in a PostgreSQL database table, allowing you to use PostgreSQL as your state storage mechanism.

Features#

  • Store Pulumi state in a PostgreSQL database

  • Use standard PostgreSQL authentication and connection options

  • Supports all Pulumi stack operations

  • Configurable table name

Connection String Format#

The PostgreSQL backend connection string follows the standard PostgreSQL connection string format with a postgres:// prefix:

postgres://username:password@hostname:port/database?param1=value1&param2=value2

Configuration Options#

The following query parameters are supported in the connection string:

  • table: The name of the table to use for state storage (default: pulumi_state)

  • All standard PostgreSQL connection parameters (sslmode, connect_timeout, etc.)

PostgreSQL Connection Parameters#

The following PostgreSQL connection parameters can be included in the connection string query parameters:

SSL and Security Parameters#

  • sslmode: SSL connection mode (disable, allow, prefer, require, verify-ca, verify-full)

  • sslcert: Client certificate file path

  • sslkey: Client private key file path

  • sslrootcert: Root certificate file path

  • sslcrl: Certificate revocation list file path

  • sslcompression: Enable SSL compression (0 or 1)

  • sslsni: Enable SSL Server Name Indication (0 or 1)

Connection and Timeout Parameters#

  • connect_timeout: Maximum time to wait for connection (seconds)

  • keepalives_idle: Time before sending keepalive probe (seconds)

  • keepalives_interval: Interval between keepalive probes (seconds)

  • keepalives_count: Number of keepalive probes before giving up

  • tcp_user_timeout: Time for transmitted data to be acknowledged (milliseconds)

Application and Session Parameters#

  • application_name: Application name for connection identification

  • client_encoding: Client character set encoding

  • options: Command-line options to send to server on connection startup

  • timezone: Session timezone setting

Authentication Parameters#

  • passfile: Password file path (alternative to inline password)

Connection Pool and Behavior Parameters#

  • target_session_attrs: Required session attributes (any, read-write, read-only, primary, standby, prefer-standby)

  • load_balance_hosts: Enable connection load balancing (disable, random)

  • hostaddr: Numeric IP address (can be used instead of or in addition to host)

Examples with Parameters#

# Basic SSL connection
postgres://user:pass@localhost:5432/mydb?sslmode=require

# Connection with custom table and SSL verification
postgres://user:pass@db.example.com:5432/pulumi?table=my_state&sslmode=verify-full&sslrootcert=/path/to/ca.crt

# Connection with timeout and keepalive settings
postgres://user:pass@localhost:5432/mydb?connect_timeout=30&keepalives_idle=600&keepalives_interval=30

# Read-only replica connection with application name
postgres://user:pass@replica.example.com:5432/mydb?target_session_attrs=read-only&application_name=pulumi-state

# Connection with custom search path and timezone
postgres://user:pass@localhost:5432/mydb?search_path=pulumi,public&timezone=UTC

Usage#

To use PostgreSQL as your Pulumi state backend:

pulumi login postgres://username:password@hostname:port/database

Environment Setup#

For most secure setups, use environment variables to store your PostgreSQL credentials. The following standard PostgreSQL environment variables are supported:

export PGUSER=username        # Defaults to current system user
export PGPASSWORD=password    # Required - no default
export PGHOST=hostname        # Defaults to "localhost"
export PGPORT=5432           # Defaults to 5432
export PGDATABASE=database   # Defaults to the value of PGUSER
# Then login with minimal connection string
pulumi login postgres://

Note: Only PGPASSWORD is required when using environment variables. All others have sensible defaults as noted above.

Table Schema#

The PostgreSQL backend will automatically create the necessary table for state storage. The table schema is defined in schema.sql.

Security Considerations#

  • Always use SSL connections in production (sslmode=require or sslmode=verify-full)

  • Create a dedicated database user with limited permissions for Pulumi state storage

  • Ensure your database is properly secured with appropriate network access controls

  • Enable database backups to prevent data loss

Limitations#

  • Signed URLs (used for state permalinks in the Pulumi CLI) are not supported with the PostgreSQL backend

  • Performance may be slower compared to cloud-specific object storage backends for very large states

Contents