Pulumi PostgreSQL

Common errors

• Error connecting to PostgreSQL server (scheme: postgres): XXXXpXXXXq: SSL is not enabled on the server

  cause The PostgreSQL provider's default `sslmode` is `require`, meaning it attempts an SSL connection, but the target PostgreSQL server is not configured to accept SSL connections.
  fix Set the `sslmode` configuration option to `disable`. This can be done via `pulumi config set postgresql:sslmode disable` or by setting the `PGSSLMODE` environment variable to `disable`. Ensure this is appropriate for your environment.

• missing required configuration key: host

  cause The PostgreSQL provider requires the `host` (server address) to connect, but it was not provided via environment variables (`PGHOST`), Pulumi configuration (`postgresql:host`), or explicit provider arguments.
  fix Provide the PostgreSQL host. For example, `export PGHOST=your_db_host` or `pulumi config set postgresql:host your_db_host`. Ensure `host` is reachable from where Pulumi is executed.

• pg: permission denied for database "my_new_database"

  cause The PostgreSQL user configured for the provider (`postgresql:username` or `PGUSER`) does not have sufficient privileges to perform the requested operation on the specified database (e.g., creating a new database or role).
  fix Grant the necessary permissions to the connecting PostgreSQL user, or configure the Pulumi provider to use a user with adequate privileges (e.g., a superuser or a user with `CREATEDB` privilege for database creation).

Warnings

• gotcha The default `sslmode` for the PostgreSQL provider is `require` (always SSL, also skip verification). If your PostgreSQL server is not configured for SSL, connection attempts will fail.
  Fix: Set `sslmode` to `disable` in your provider configuration or via `PGSSLMODE` environment variable if your server doesn't use SSL. For production, ensure SSL is configured and `sslmode` is set to `verify-ca` or `verify-full`.
• gotcha Sensitive configuration values, such as `postgresql:password`, should always be managed using Pulumi's secret encryption to prevent them from being stored in plain text in your state file.
  Fix: Use `pulumi config set --secret postgresql:password <YOUR_PASSWORD>` when setting passwords via Pulumi configuration. If using environment variables, ensure proper secure handling of `PGPASSWORD`.
• gotcha Some PostgreSQL provider features, such as refreshing state passwords from the database, require the connecting user to be a PostgreSQL superuser. If the user is not a superuser (common in cloud-managed databases like AWS RDS or GCP SQL), these features might be disabled.
  Fix: Set `superuser` to `false` in your provider configuration or ensure the connecting user has the necessary privileges for operations you intend to perform. Consult provider documentation for specific resource requirements.
• breaking Upgrades to the `pulumi-terraform-bridge` or `terraform-provider-postgresql` dependencies can occasionally introduce changes in how resources are identified or managed, potentially leading to 'replace' actions or resources being removed from the Pulumi state during a `pulumi refresh`.
  Fix: Always run `pulumi preview` before `pulumi up` after provider upgrades to understand proposed changes. If resources are incorrectly marked for replacement or removal, investigate changes in input properties or provider behavior. A `pulumi refresh` followed by a `pulumi up` might be necessary, but ensure you understand the implications, particularly for functions or complex objects.

Install

• pip install pulumi_postgresql Install with pip

Imports

• postgresql

  import pulumi_postgresql as postgresql

  This is the conventional alias used in Pulumi Python programs for the PostgreSQL provider.
• Database

  from pulumi_postgresql import Database

• Role

  from pulumi_postgresql import Role

Quickstart

This quickstart demonstrates how to create a new PostgreSQL database and a new role (user) using the `pulumi-postgresql` provider. It assumes PostgreSQL connection details are configured via environment variables (`PGHOST`, `PGUSER`, `PGPASSWORD`, `PGDATABASE`, `PGPORT`, `PGSSLMODE`) or Pulumi configuration. For local development, `PGSSLMODE` is often set to `disable` to avoid SSL connection errors. Always manage sensitive data like passwords using Pulumi secrets.

import pulumi
import pulumi_postgresql as postgresql
import os

# Configure PostgreSQL provider using environment variables for sensitive data
# Alternatively, use `pulumi config set postgresql:host <value>` etc.
# For passwords, use `pulumi config set --secret postgresql:password <value>`

pg_host = os.environ.get('PGHOST', 'localhost')
pg_user = os.environ.get('PGUSER', 'postgres')
pg_password = os.environ.get('PGPASSWORD', 'password')
pg_database = os.environ.get('PGDATABASE', 'postgres') # Database for initial connection
pg_port = os.environ.get('PGPORT', '5432')
pg_sslmode = os.environ.get('PGSSLMODE', 'disable') # Often 'disable' for local dev, 'require' for production

# The provider needs to be explicitly configured if not using default environment variables or pulumi config.
# For this example, we're relying on environment variables or `pulumi config`.
# If you need to explicitly create a provider instance:
# pg_provider = postgresql.Provider("pg-provider",
#     host=pg_host,
#     username=pg_user,
#     password=pg_password,
#     database=pg_database,
#     port=pg_port,
#     sslmode=pg_sslmode
# )

# Create a new PostgreSQL database
new_db = postgresql.Database("my-new-database",
    name="mydatabase",
    opts=pulumi.ResourceOptions(delete_before_replace=True) # Optional: ensures clean replacement if name changes
)

# Create a new PostgreSQL role (user)
new_role = postgresql.Role("my-app-role",
    name="app_user",
    login=True,
    password="strong_password_here", # In production, this should be a secret!
    opts=pulumi.ResourceOptions(delete_before_replace=True) # Optional
)

# Export the database name and role name
pulumi.export('database_name', new_db.name)
pulumi.export('role_name', new_role.name)