DOCSEARCHbyAlgolia
Submit Docs

Terraform Provider for PostgreSQL

repository·main·Indexed 20 days ago

https://github.com/cyrilgdn/terraform-provider-postgresql

A Terraform provider for managing PostgreSQL resources, including extensions, databases, and other server-side objects. It provides data sources for retrieving schema names (postgresql_schemas), sequence names (postgresql_sequences), and table names (postgresql_tables). The provider supports configuration via provider blocks, environment variables (PGHOST, PGPORT, PGUSER, PGPASSWORD), and external secret stores.

Tokens
14.6K
Snippets
44
Records
70
Agent score
68%

What's inside terraform-provider-postgresql

  1. Manage PostgreSQL roles with postgresql_role

    main

    The postgresql_role resource creates and manages a role on a PostgreSQL server.

    Important Lifecycle Note: When a postgresql_role is removed, the provider automatically attempts to run REASSIGN OWNED and DROP OWNED to the CURRENT_USER.

    If a role owns objects across multiple databases in the same cluster, you must create one provider per database. For all but the final postgresql_role being removed, you must set skip_drop_role = true to allow the role to be dropped from the catalog after ownership cleanup in other databases.

    resource "postgresql_role" "my_role" {
  name     = "my_role"
  login    = true
  password = "mypass"
}

  2. Use write-only passwords for enhanced security

    main

    To prevent passwords from being stored in plain-text in the Terraform state file, use the password_wo and password_wo_version attributes instead of the standard password attribute.

    How it works:

    • Security: The password value is never stored in the state file.
    • Idempotency: Terraform only triggers a password update when the password_wo_version value changes.
    • Conflict: password_wo and password_wo_version conflict with the standard password attribute; you must use one method or the other.
    resource "postgresql_role" "secure_role" {
  name                = "secure_role"
  login               = true
  password_wo         = "secure_password_123"
  password_wo_version = "1"
}

  3. Manage default privileges with postgresql_default_privileges

    main

    The postgresql_default_privileges resource is used to create and manage default privileges that are automatically granted to a specific role whenever new objects (like tables or functions) are created within a database schema by a specific owner.

    Requirement: This resource requires PostgreSQL version 9 or above.

    resource "postgresql_default_privileges" "read_only_tables" {
  role     = "test_role"
  database = "test_db"
  schema   = "public"

  owner       = "db_owner"
  object_type = "table"
  privileges  = ["SELECT"]
}

  4. Manage PostgreSQL privileges with postgresql_grant

    main

    The postgresql_grant resource is used to create and manage privileges granted to a specific role for various PostgreSQL objects (databases, schemas, tables, etc.).

    Requirements & Constraints:

    • Requires PostgreSQL version 9 or above.
    • Warning: Using both column-level and table-level grants on the same table with the same privileges can lead to unexpected behavior.
    • To revoke all privileges for a role, provide an empty list [] to the privileges argument.
    resource "postgresql_grant" "example" {
  database    = "test_db"
  role        = "test_role"
  schema      = "public"
  object_type = "table"
  objects     = ["table1", "table2"]
  privileges  = ["SELECT"]
}

  5. Manage PostgreSQL physical replication slots with postgresql_physical_replication_slot

    main

    The postgresql_physical_replication_slot resource is used to create and manage physical replication slots on a PostgreSQL server. Physical replication slots are essential for setting up cross-datacenter replication (e.g., using Patroni) or allowing standby clusters to perform physical data replication.

    resource "postgresql_physical_replication_slot" "my_slot" {
  name  = "my_slot"
}

  6. Run Acceptance Tests Locally with Docker

    main

    To manually run specific acceptance tests locally without running the full suite, you can use a Docker-based workflow. This involves setting up a local PostgreSQL container, configuring environment variables, running the specific Go test, and then cleaning up.

    Follow this sequence:

    1. Setup: Use make testacc_setup to spin up a local Docker PostgreSQL container.
    2. Environment: Source tests/switch_superuser.sh to load required environment variables.
    3. Execute: Run the specific test using go test with the TF_LOG environment variable set to INFO.
    4. Cleanup: Use make testacc_cleanup to tear down the container and clean the environment.
    # 1. spins up a local docker postgres container
make testacc_setup 

# 2. Load the needed environment variables for the tests
source tests/switch_superuser.sh

# 3. Run a specific test (example: TestAccPostgresqlRole_Basic)
TF_LOG=INFO go test -v ./postgresql -run ^TestAccPostgresqlRole_Basic$

# 4. cleans the env and tears down the postgres container
make testacc_cleanup

  7. Run Provider Tests

    main

    The provider includes different test suites for verification:

    • Unit Tests: Run make test to execute standard tests.
    • Acceptance Tests: Run make testacc to run the full suite of acceptance tests.

    Warning: Acceptance tests create real resources and may incur costs.

    # Run unit tests
$ make test

# Run full acceptance tests
$ make testacc

  8. Manage PostgreSQL functions with postgresql_function

    main

    The postgresql_function resource allows you to create and manage functions on a PostgreSQL server using Terraform. You can define the function's name, arguments, return type, programming language, and the logic within the function body.

    resource "postgresql_function" "increment" {
    name = "increment"
    arg {
        name = "i"
        type = "integer"
    }
    returns = "integer"
    language = "plpgsql"
    body = <<-EOF
        BEGIN
            RETURN i + 1;
        END;
    EOF
}

  9. Manage PostgreSQL user mappings with postgresql_user_mapping

    main

    The postgresql_user_mapping resource is used to create and manage user mappings on a PostgreSQL server, typically used in conjunction with Foreign Data Wrappers (FDW). This allows a local user to be mapped to a specific user (and credentials) on a remote foreign server.

    To use this resource, you generally need an existing postgresql_extension (like postgres_fdw), a postgresql_server configured with that extension, and a postgresql_role that will act as the local user being mapped.

    resource "postgresql_extension" "ext_postgres_fdw" {
  name = "postgres_fdw"
}

resource "postgresql_server" "myserver_postgres" {
  server_name = "myserver_postgres"
  fdw_name    = "postgres_fdw"
  options = {
    host   = "foo"
    dbname = "foodb"
    port   = "5432"
  }

  depends_on = [postgresql_extension.ext_postgres_fdw]
}

resource "postgresql_role" "remote" {
  name = "remote"
}

resource "postgresql_user_mapping" "remote" {
  server_name = postgresql_server.myserver_postgres.server_name
  user_name   = postgresql_role.remote.name
  options = {
    user = "admin"
    password = "pass"
  }
}

  11. Manage PostgreSQL schemas with postgresql_schema

    main

    The postgresql_schema resource is used to create and manage schema objects within a PostgreSQL database. You can define the schema name, the owner, and various access policies for different roles.

    resource "postgresql_role" "app_www" {
  name = "app_www"
}

resource "postgresql_role" "app_dba" {
  name = "app_dba"
}

resource "postgresql_role" "app_releng" {
  name = "app_releng"
}

resource "postgresql_schema" "my_schema" {
  name  = "my_schema"
  owner = "postgres"

  policy {
    usage = true
    role  = "${postgresql_role.app_www.name}"
  }

  # app_releng can create new objects in the schema.  This is the role that
  # migrations are executed as.
  policy {
    create = true
    usage  = true
    role   = "${postgresql_role.app_releng.name}"
  }

  policy {
    create_with_grant = true
    usage_with_grant  = true
    role              = "${postgresql_role.app_dba.name}"
  }
}

  12. Manage PostgreSQL publications with postgresql_publication

    main

    The postgresql_publication resource allows you to create and manage a publication on a PostgreSQL server. Publications are used to define sets of data that can be sent to other servers via logical replication.

    To use this resource, define the publication name and optionally specify the tables to include. If all_tables is set to true, all tables in the database will be added to the publication.

    resource "postgresql_publication" "publication" {
  name  = "publication"
  tables = ["public.test","another_schema.test"]
}