/terraform-yc-postgresql

Primary LanguageHCLMIT LicenseMIT

Yandex Cloud Managed PostgreSQL Cluster

Features

  • Create a managed PostgreSQL cluster with a predefined number of DB hosts
  • Create a list of users and databases with permissions
  • Easy to use in other resources via outputs

PostgreSQL cluster definition

First, you need to create a VPC network with three subnets.

PostgreSQL module requires the following input variables:

  • VPC network ID.
  • VPC network subnet IDs.
  • PostgreSQL host definitions: List of maps with DB host name, zone name, and subnet ID.
  • Databases: List of databases with database names, owners, and other parameters.
  • Owners: List of database owners.
  • Users: All other users with a list of permissions to databases.

Notes:

  1. The owners variable defines a list of databases owners. It does not support the permissions list because these users will be linked with databases via the owner parameter. This means each database must have an owner.
  2. The users variable defines a list of separate DB users with the permissions list, which points to a list of databases. This means each user will have an access to each database from the permissions list.
  3. The settings_options parameter may be null, in which case the default values will be used.

Example

See examples section

How to configure Terraform for Yandex Cloud

  • Install YC CLI
  • Add environment variables for terraform auth in Yandex.Cloud
export YC_TOKEN=$(yc iam create-token)
export YC_CLOUD_ID=$(yc config get cloud-id)
export YC_FOLDER_ID=$(yc config get folder-id)
export TF_VAR_network_id=_vpc id here_

Requirements

Name Version
terraform >= 1.0.0
random > 3.3
yandex > 0.8

Providers

Name Version
local n/a
random > 3.3
yandex > 0.8

Modules

No modules.

Resources

Name Type
local_file.pgpass_file resource
random_password.password resource
yandex_mdb_postgresql_cluster.this resource
yandex_mdb_postgresql_database.database resource
yandex_mdb_postgresql_user.owner resource
yandex_mdb_postgresql_user.user resource
yandex_client_config.client data source

Inputs

Name Description Type Default Required
access_policy Access policy from other services to the PostgreSQL cluster. 
object({
    data_lens     = optional(bool, null)
    web_sql       = optional(bool, null)
    serverless    = optional(bool, null)
    data_transfer = optional(bool, null)
  })
 {} no
autofailover (Optional) Configuration setting which enables/disables autofailover in cluster. bool true no
backup_retain_period_days (Optional) The period in days during which backups are stored. number null no
backup_window_start (Optional) Time to start the daily backup, in the UTC timezone. 
object({
    hours   = string
    minutes = optional(string, "00")
  })
 null no
databases A list of PostgreSQL databases.

Required values:
- name - (Required) The name of the database.
- owner - (Required) Name of the user assigned as the owner of the database. Forbidden to change in an existing database.
- extension - (Optional) Set of database extensions.
- lc_collate - (Optional) POSIX locale for string sorting order. Forbidden to change in an existing database.
- lc_type - (Optional) POSIX locale for character classification. Forbidden to change in an existing database.
- template_db - (Optional) Name of the template database.		 
list(object({
    name        = string
    owner       = string
    lc_collate  = optional(string, null)
    lc_type     = optional(string, null)
    template_db = optional(string, null)
    extensions  = optional(list(string), [])
  }))
 n/a yes
default_user_settings The default user settings. These settings are overridden by the user's settings.
Full description https://cloud.yandex.com/en-ru/docs/managed-postgresql/api-ref/grpc/user_service#UserSettings1		 map(any) {} no
deletion_protection Inhibits deletion of the cluster. bool false no
description PostgreSQL cluster description string "Managed PostgreSQL cluster" no
disk_size Disk size for every cluster host number 20 no
disk_type Disk type for all cluster hosts string "network-ssd" no
environment Environment type: PRODUCTION or PRESTABLE string "PRODUCTION" no
folder_id Folder id in that contains the PostgreSQL cluster string null no
host_master_name Name of the master host. string null no
hosts_definition A list of PostgreSQL hosts. 
list(object({
    name                    = optional(string, null)
    zone                    = string
    subnet_id               = optional(string, null)
    assign_public_ip        = optional(bool, false)
    replication_source_name = optional(string, null)
    priority                = optional(number, null)
  }))
 [] no
labels A set of label pairs to assing to the PostgreSQL cluster. map(any) {} no
maintenance_window (Optional) Maintenance policy of the PostgreSQL cluster.
- type - (Required) Type of maintenance window. Can be either ANYTIME or WEEKLY. A day and hour of window need to be specified with weekly window.
- day - (Optional) Day of the week (in DDD format). Allowed values: "MON", "TUE", "WED", "THU", "FRI", "SAT", "SUN"
- hour - (Optional) Hour of the day in UTC (in HH format). Allowed value is between 0 and 23.		 
object({
    type = string
    day  = optional(string, null)
    hour = optional(string, null)
  })
 
{
  "type": "ANYTIME"
}
 no
name PostgreSQL cluster name string "pgsql-cluster" no
network_id Network id of the PostgreSQL cluster string n/a yes
owners A list of special PostgreSQL DB users - database owners. These users are created first and assigned to database as owner.
There is also an aditional list for other users with own permissions.

Required values:
- name - (Required) The name of the user.
- password - (Optional) The user's password. If it's omitted a random password will be generated.
- grants - (Optional) A list of the user's grants.
- login - (Optional) The user's ability to login.
- conn_limit - (Optional) The maximum number of connections per user.
- settings - (Optional) A user setting options.
- deletion_protection - (Optional) A deletion protection.		 
list(object({
    name                = string
    password            = optional(string, null)
    grants              = optional(list(string), [])
    login               = optional(bool, null)
    conn_limit          = optional(number, null)
    settings            = optional(map(any), {})
    deletion_protection = optional(bool, null)
  }))
 n/a yes
performance_diagnostics (Optional) PostgreSQL cluster performance diagnostics settings. 
object({
    enabled                      = optional(bool, null)
    sessions_sampling_interval   = optional(number, 60)
    statements_sampling_interval = optional(number, 600)
  })
 {} no
pg_version PostgreSQL version string "15" no
pgpass_path Where create the .pgpass file. If it's omitted the file won't be created string null no
pooler_config Configuration of the connection pooler.
- pool_discard - Setting pool_discard parameter in Odyssey. Values: yes | no
- pooling_mode - Mode that the connection pooler is working in. Values: POOLING_MODE_UNSPECIFIED, SESSION, TRANSACTION, STATEMENT		 
object({
    pool_discard = optional(bool, null)
    pooling_mode = optional(string, null)
  })
 null no
postgresql_config A map of PostgreSQL cluster configuration.
Details info in a 'PostgreSQL cluster settings' of official documentation.
Link: https://registry.terraform.io/providers/yandex-cloud/yandex/latest/docs/resources/mdb_postgresql_cluster#postgresql-cluster-settings		 map(any) null no
resource_preset_id Preset for hosts string "s2.micro" no
restore_parameters The cluster will be created from the specified backup.
NOTES:
- backup_id must be specified to create a new PostgreSQL cluster from a backup.
- time format is 'yyy-mm-ddThh:mi:ss', where T is a delimeter, e.g. "2023-04-05T11:22:33".
- time_inclusive indicates recovery to nearest recovery point just berfore (false) or right after (true) the time.		 
object({
    backup_id      = string
    time           = optional(string, null)
    time_inclusive = optional(bool, null)
  })
 null no
security_groups_ids_list A list of security group IDs to which the PostgreSQL cluster belongs list(string) [] no
users This is a list for additional PostgreSQL users with own permissions. They are created at the end.

Required values:
- name - (Required) The name of the user.
- password - (Optional) The user's password. If it's omitted a random password will be generated.
- grants - (Optional) A list of the user's grants.
- login - (Optional) The user's ability to login.
- conn_limit - (Optional) The maximum number of connections per user.
- permissions - (Optional) A list of databases names for an access
- settings - (Optional) A user setting options.
- deletion_protection - (Optional) A deletion protection.		 
list(object({
    name                = string
    password            = optional(string, null)
    grants              = optional(list(string), [])
    login               = optional(bool, null)
    conn_limit          = optional(number, null)
    permissions         = optional(list(string), [])
    settings            = optional(map(any), {})
    deletion_protection = optional(bool, null)
  }))
 [] no

Outputs

Name Description
cluster_fqdns_list PostgreSQL cluster nodes FQDN list
cluster_host_names_list PostgreSQL cluster host name
cluster_id PostgreSQL cluster ID
cluster_name PostgreSQL cluster name
connection_step_1 1 step - Install certificate
connection_step_2 How connect to PostgreSQL cluster?

1. Install certificate

mkdir --parents ~/.postgresql && \
curl -sfL "https://storage.yandexcloud.net/cloud-certs/CA.pem" -o ~/.postgresql/root.crt && \
chmod 0600 ~/.postgresql/root.crt

2. Run connection string from the output value, for example

psql "host=rc1a-g2em5m3zc9dxxasn.mdb.yandexcloud.net \
port=6432 \
sslmode=verify-full \
dbname=db-b \
user=owner-b \
target_session_attrs=read-write"
databases A list of databases names.
owners_data A list of owners with passwords.
users_data A list of users with passwords.