When I try connecting to PostgreSQL DB on Azure it fails with below error Error: Error running plan: 2 error(s) occurred:

• provider.postgresql: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp :5432: getsockopt: connection refused
• provider.postgresql.pgconnect: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp 191.X.X.X:5432: getsockopt: connection timed out

I am trying to connect DB and create users, below is my tf file appreciate if anyone can help in this regard as it has been 2 weeks but no success. I had already tried multiple things like commenting some variables but still same issue. Also since this is a PAAS I can't use provisioner like local-exec or remote-exec with psql commands to create user as we don't have access to Server.

postgresql region begin ---------------------------------------

variable hostname { description = "hostname of PostgreSQL Instance" }

variable administrator_login { description = "administrator login user name" }

variable administrator_login_password{ description = "administrator login password" }

variable user_password { description="user password" }

provider "postgresql" { alias = "pgconnect" host = "${var.hostname}" port = "5432" #database = "postgres" username = "${var.administrator_login}" password = "${var.administrator_login_password}" sslmode = "require" #connect_timeout = 15 #expected_version = "9.6.0" }

resource "postgresql_database" "postgres" { provider = "postgresql.pgconnect" name = "postgres" }

resource "postgresql_role" "approle" { name = "app_role" login = true password = "${var.user_password}" }

resource "postgresql_role" "monitorrole" { name = "monitor_role" login = true password = "${var.user_password}" } #--------------------------------------------------------------------#

I got the same problem when I specify the alias for the provider. When I remove the alias, everything works fine.

Tried commenting alias part but still same error. This comes in at terraform plan stage itself, any suggestions will be helpful

provider.postgresql: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp 191.x.x.x:5432: getsockopt: connection timed out

appreciate if anyone can suggest how to resolve this issue ??

@hypnoglow did you tried for Azure PostgreSQL ??

@gaurav218

I have this working after a fashion, I create an Azure PostgreSQL instance and then create roles and users. I have to use -target on the first pass of my plan to prevent chicken and egg errors as the PostgreSQL provider doesn't understand that the server doesn't exist, then re-plan and apply. You'll also hit #50 and #55 . Getting past #55 just involves compiling the provider yourself as PR #40 resolves the underlying issue (quite why there hasn't been a new release is anyone's guess...). To get past #50 will need you to update the code and recompile. As I only need this to work for Azure I took a hammer to the problem and replaced all instances of c.config.Username with strings.Split(c.config.Username, "@")[0] in resource_postgresql_database.go and resource_postgresql_role.go

My provider looks like this:

provider "postgresql" {
  #version = "0.1.2"
  host            = "${azurerm_postgresql_server.pgServer.fqdn}"
  username        = "${azurerm_postgresql_server.pgServer.administrator_login}@${azurerm_postgresql_server.pgServer.name}"
  password        = "${azurerm_postgresql_server.pgServer.administrator_login_password}"
  sslmode         = "require"
  connect_timeout = 15
  expected_version = "9.6.10"
}

And the role and database creation looks like:

resource "postgresql_role" "dbusers" {
  count    = "${length(var.pg_databases)}"
  name             = "${lookup(var.pg_databases[count.index], "db_user")}"
  login            = true
  create_database  = true
  password         = "${element(random_string.db_passwords.*.result, count.index)}"
}

resource "postgresql_database" "databases" {
  count    = "${length(var.pg_databases)}"
  name              = "${lookup(var.pg_databases[count.index], "db_name")}"
  owner             = "${lookup(var.pg_databases[count.index], "db_user")}"
  template          = "template0"
  lc_collate        = "English_United States.1252"
  connection_limit  = -1
  allow_connections = true
  depends_on        = [ "postgresql_role.dbusers" ]
}

Hope this helps :-)

So far, I learned a few things from Azure PostgreSQL servers:

1. You are not connecting directly to the DB server, but you are connecting to a gateway which is in front of all managed DB servers. See the Connectivity architecture for more info.
2. You need to create Azure DB firewall rules to allow the IP of your client to connect to the database server. Under the hood, Azure is managing pg_hba.conf here.
3. Since all connections end up on the gateway, you need to specify your username in the form username@servername for the gateway to know to which server to forward. This is the reason the databaseUsername propertt was introduced. See #55 for more info.

I got the same problem when I specify the alias for the provider. When I remove the alias, everything works fine.

@hypnoglow 's solution worked for me in AWS Aurora RDS. For some reason the Alias was causing issues.