Support database parameter in postgresql_server and postgresql_user_mapping resources

[bcmedeiros]

I'm trying to create the following server resource after creating my fdw extension:

resource "postgresql_extension" "mysql_fdw" {
  name     = "mysql_fdw"
  database = postgresql_database.this.name
  schema   = "public"

  drop_cascade = var.drop_cascade
}

resource "postgresql_server" "mysql1" {
  fdw_name     = "mysql_fdw"
  server_name  = "mysql1"
  server_owner = postgresql_role.owner.name

  options = {
    host = "localhost"
    port = "3306"

    fetch_size    = 1000
    reconnect     = true
    character_set = "UTF-8"
  }

  drop_cascade = var.drop_cascade
  depends_on   = [postgresql_extension.mysql_fdw]
}

It fails with the following error:

│ Error: pq: foreign-data wrapper "mysql_fdw" does not exist
│ 
│   with module.pg1.module.stack_db.postgresql_server.mysql1,
│   on modules/database/main.tf line 130, in resource "postgresql_server" "mysql1":
│  130: resource "postgresql_server" "mysql1" {

Running the following query works, though, so this error seems to be a limitation of either this connector or the pg Go library.

CREATE SERVER mysql_main
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host 'localhost', port '3306');

[bcmedeiros]

After some investigation, I figured out that the error occurs because I'm declaring the mysql_fdw extension to be setup in a database I'm also provisioning in this same file, using database = postgresql_database.this.name, but as the postgresql_server resource does not allow a database to be specified, the server creation is attempted in the postgres database which is the one the provider is connected to.

The workaround I'm going to have to use now is to declare a second provider with the newly created database and given that Terraform won't mix up any dependencies (I'm not even sure provider declarations wait for any dependencies) the server is created:

resource "postgresql_extension" "mysql_fdw" {
  name     = "mysql_fdw"
  database = postgresql_database.this.name
  schema   = "public"

  drop_cascade = var.drop_cascade
}

provider "postgresql" {
  alias     = "database"
  host     = var.host
  port     = var.port
  username = var.user
  password = var.pass

  database = postgresql_database.this.name

  superuser = false
  sslmode   = var.ssl ? "require" : "disable"
}

resource "postgresql_server" "mysql1" {
  provider     = postgresql.database
  server_name  = "mysql1"
  fdw_name     = "mysql_fdw"
  server_owner = postgresql_role.owner.name

  options = {
    host = var.mysql1.host
    port = var.mysql1.port

    fetch_size    = 1000
    reconnect     = true
    character_set = "UTF-8"
  }

  drop_cascade = var.drop_cascade
  depends_on   = [postgresql_extension.mysql_fdw]
}

We need a database configuration for postgresql_server as we have for postgresql_extension.

[isaias-sanchez-utiq]

Just facing this issue as well.