First off, thank you @cyrilgdn for maintaining this terraform provider! It's been such a wonderful way to declaratively manage Postgres roles and privileges.

Feature:

In version 11, Postgres separated procedures out from functions in grant commands. Because of this change it's no longer possible to manage procedures using this provider. The docs state: "ALL FUNCTIONS also affects aggregate functions, but not procedures, again just like the specific-object GRANT command. Use ALL ROUTINES to include procedures." (source).

I'd like to help extend this provider to support ALL PROCEDURES and ALL ROUTINES. I'm not particularly familiar with golang or terraform providers, so any guidance/assistance would be appreciated.

Plan:

As far as I can tell the main areas that would need to be updated are:

• https://github.com/cyrilgdn/terraform-provider-postgresql/blob/master/postgresql/resource_postgresql_grant.go#L349
• https://github.com/cyrilgdn/terraform-provider-postgresql/blob/master/postgresql/resource_postgresql_grant.go#L382
• https://github.com/cyrilgdn/terraform-provider-postgresql/blob/master/postgresql/resource_postgresql_grant.go#L16
• https://github.com/cyrilgdn/terraform-provider-postgresql/blob/master/postgresql/helpers.go#L242

#30 added the support of schema object_type in postgresql_grant resource, we need to do the same for postgresql_default_privileges

Very similar issue to #38, I figured I would provide some additional information since I'm not seeing the error response. Instead, my Terraform apply runs but then when I try to run queries with the user, I can see the user doesn't have the necessary schema rights.

Terraform Version

0.14.15

• provider registry.terraform.io/cyrilgdn/postgresql v1.11.0
• provider registry.terraform.io/hashicorp/aws v3.25.0

Affected Resource(s)

postgresql_grant, specifically with object_type ="schema"

Terraform Configuration Files

provider "postgresql" {
  scheme = var.pg_scheme  # postgres provider 1.11+ specifies awspostgres for RDS, but the GO CDK doesn't verify the x509 cert correctly
  host = data.aws_rds_cluster.data_science_postgresql.endpoint
  port = data.aws_rds_cluster.data_science_postgresql.port
  database = data.aws_rds_cluster.data_science_postgresql.database_name
  username = jsondecode(data.aws_secretsmanager_secret_version.master_db_credentials.secret_string)["username"]
  password = jsondecode(data.aws_secretsmanager_secret_version.master_db_credentials.secret_string)["password"]
  superuser = false
  sslmode = "require"
  sslrootcert = "/code/manifest/AmazonRootCA1.pem"
  expected_version = "10.12"
}

resource "postgresql_schema" "price" {
    name = "price"
    owner = "username"  # The username specified in provider config above
}

resource "postgresql_role" "price_readwrite" {
    name = "price_readwrite"  
    login = false
}

resource "postgresql_grant" "schema_usage" {
    database = data.aws_rds_cluster.data_science_postgresql.database_name
    role = postgresql_role.price_readwrite.name
    schema = "price"
    object_type = "schema"
    privileges = ["CREATE", "USAGE"]
}

Expected Behavior

I would expect the price_readwrite role to have CREATE privileges on the price schema even if the expected_version is not set for the provider.

Actual Behavior

The price_readwrite user does not have permission to create tables in the price schema, unless the Postgres version is specified in the provider config.

I do have other postgresql_grant resources but they are to grant database CONNECT and table CRUD privileges. The CONNECT on database seems to work fine. I can't say about the CRUD because it's dependent on the user have schema privileges to being with.

Steps to Reproduce

1. Configure Postgres DB (10.12) with non-super-user credentials, but with the ability to create roles, databases, and schemas.
2. Run the configuration above to create the price schema, price_readwrite role, and attempt to pass privileges to the role.

Important Factoids

• Output of SELECT version() = PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit

• Running Terraform in a docker container using terraform binary from Docker image, COPY --from=hashicorp/terraform:0.14.5 /bin/terraform /bin/terraform

• Docker container is running in AWS CodeBuild in private subnet with network access to RDS, also in private subnet.

• RDS is Aurora Serverless Postgres-- This seems to work great, even if it is scaled down to 0 it comes back up and allows the provider connection fast enough for Terraform to do its thing.

• Helpful query to check schema rights:

SET ROLE price_readwrite;

WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ '^pg_'
        AND n.nspname <> 'information_schema'
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
    FROM "names";

• Contrary to the documentation, I am not using the awspostgres connection scheme because the Go Cloud seemed to be downloading the certificate itself, and it kept giving me a x509 error. I found I was only able to connect with the provider configuration above. Upon investigation, this might be a Go 1.15+ thing and how AWS chooses to make their certs, or it looks like Go Cloud is looking at old *.pem files. I am not support confident about this, but I can open a separate issue if you'd like.

References

• I suspect this may be a similar issue, #38
• Interesting background on the x509 thing I mentioned, golang/go#39568
• More related info on x509 and Go's interaction with the cloud-- this one is pending changes, golang/go#40748

Thank you for your great work!

Hi,

in the previous repository there was an open feature request hashicorp/terraform-provider-postgresql#110 to allow to natively connect to cloud instances like Cloud SQL Proxy for GCP and AWS/Azure equivalent.

@benfdking implemented that feature in their fork here: https://github.com/tumelohq/terraform-provider-postgresql/ using https://gocloud.dev/.

Would you consider such approach if I were to submit a patch? I am waiting on #5 first before I start working on it.

References

benfdking/terraform-provider-postgresql#1
https://gocloud.dev/howto/sql/

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

0.12.30

Affected Resource(s)

postgresql_grant

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource postgresql_grant "all_privileges" {
  database       = var.postgresDatabaseName
  role           = postgresql_role.postgresDatabaseRole.name
  schema         = postgresql_schema.default.name
  object_type    = "table"
  privileges     = ["ALL"]
}

Expected Behavior

This infra was builat a while ago using Terraform config. There have been no changes to the infrastructure. Running a Plan today should have no changes to make.

Actual Behavior

The resource is going to be recreated due to a new setting being added that isn't in my code and isn't in the docs:

  # module.postgresqlengdev.postgresql_grant.all_privileges must be replaced
-/+ resource "postgresql_grant" "all_privileges" {
        database          = "igxcafe"
      ~ id                = "engdev_igxcafe_engdev_table" -> (known after apply)
        object_type       = "table"
      ~ privileges        = [
          + "ALL",
        ]
        role              = "engdev"
        schema            = "engdev"
      + with_grant_option = false # forces replacement
    }

The option in question is with_grant_option.

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

Important Factoids

Are there anything atypical about your accounts that we should know? For example: Running in EC2 Classic? Custom version of OpenStack? Tight ACLs?

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:

• GH-1234

Hello;

Here are my versions:

$ terraform -v
Terraform v0.14.3
+ provider registry.terraform.io/cyrilgdn/postgresql v1.11.0
+ provider registry.terraform.io/hashicorp/azurerm v2.43.0

Azure Database for Postgresql is version 11.

Here is example main.tf code:

terraform {
  required_providers {
    azurerm = {
      source = "hashicorp/azurerm"
      version = ">= 2.21.0"
    }
    postgresql = {
      source = "cyrilgdn/postgresql"
      version = ">= 1.11.0"
    }
  }
}

provider "azurerm" {
  features {}
}

resource "azurerm_postgresql_server" "postgres" {
  name                = "conzyor34db"
  location            = "eastus2"
  resource_group_name = "test-rg"

  sku_name = "GP_Gen5_2"

  storage_mb                    = 5120
  backup_retention_days         = 7
  geo_redundant_backup_enabled  = false
  auto_grow_enabled             = false
  
  administrator_login          = "admin_user"
  administrator_login_password = "<password>"
  version                      = 11
  ssl_enforcement_enabled      = true
}

resource "azurerm_postgresql_firewall_rule" "firewall_rules" {
  name                = "my_ip"
  resource_group_name = "test-rg"
  server_name         = azurerm_postgresql_server.postgres.name
  start_ip_address    = "71.239.0.0"
  end_ip_address      = "71.239.254.254"
}

resource "azurerm_postgresql_database" "postgres" {
  name                = "my_database"
  resource_group_name = "test-rg"
  server_name         = azurerm_postgresql_server.postgres.name
  charset             = "UTF8"
  collation           = "English_United States.1252"
  depends_on          = [azurerm_postgresql_firewall_rule.firewall_rules]
}

provider "postgresql" {
  host = azurerm_postgresql_server.postgres.fqdn
  port = 5432
  database = azurerm_postgresql_database.postgres.name
  username = format( "%s@%s", azurerm_postgresql_server.postgres.administrator_login, azurerm_postgresql_server.postgres.fqdn )
  password = azurerm_postgresql_server.postgres.administrator_login_password
  sslmode = "require"
  superuser = false
}

resource "postgresql_role" "role" {
  name = "my_role"
  password = "<another password>"
  login = true
}

resource "postgresql_grant" "grant" {
  role = postgresql_role.role.name
  object_type = "database"
  database = azurerm_postgresql_database.postgres.name
  privileges = ["ALL"]
}

This configuration creates the role and grant as expected:

postgresql_role.role: Creating...
postgresql_role.role: Creation complete after 2s [id=my_role]
postgresql_grant.grant: Creating...
postgresql_grant.grant: Creation complete after 2s [id=my_role_my_database_database]

However the provider is unable to destroy the role due to formatting of the role as username@host:

postgresql_grant.grant: Destroying... [id=my_role_my_database_database]
postgresql_grant.grant: Destruction complete after 1s
postgresql_role.role: Destroying... [id=my_role]

Error: could not reassign owned by role my_role to [email protected]: pq: role "[email protected]" does not exist

Running this manually produces the same result:

psqlhostname=> reassign owned by my_role to "[email protected]";
ERROR:  role "[email protected]" does not exist

But running manually without the @host portion reassigns the objects correctly:

psqlhostname=> reassign owned by my_role to admin_user;
REASSIGN OWNED

Expected Behavior

The role resource should be deleted.

Actual Behavior

The role resource isn't deleted because PostgreSQL doesn't like the format of the destination role used in the reassign owned SQL call.

Steps to Reproduce

1. terraform apply
2. terraform destroy

Discussion

Is something odd about Azure Postgresql databases that they do not accept the user@host format for "reassign owned"? It's hard to imagine I'm the first person who ever tried deleting a postgresql_role resource.

Replication of hashicorp/terraform-provider-postgresql#197

Being able to execute REVOKE ALL ON DATABASE XXX FROM PUBLIC is the one thing we're looking for if a separate, more targeted resource might be more appropriate.

Running the command terraform-bundle package -os=linux -arch=amd64 terraform-bundle.hcl does not fetch the provider.

Terraform Version

• 0.12.29

Affected Resource(s)

• provider

Terraform Configuration Files

terraform {
  version = "0.12.29"
}

providers {
  postgresql = ["1.11.1"]
}

Debug Output

- Resolving "postgresql" provider (1.11.1)...
- Checking for provider plugin on https://releases.hashicorp.com...
- Failed to resolve postgresql provider 1.11.1: unable to verify signature
The command '/bin/sh -c terraform-bundle package -os=linux -arch=amd64 terraform-bundle.hcl' returned a non-zero code: 1

Expected Behavior

Install the provider plugin

Actual Behavior

Failed to resolve postgresql provider 1.11.1

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. Create the terraform-bundle.hcl files as described above
2. Run the command terraform-bundle package -os=linux -arch=amd64 terraform-bundle.hcl

Hi there,

We currently have two Terraform configurations. One has the responsibility for creating a Postgres role and the second allows grants for this role. Are there any known workarounds to the issue with incompatibility between the postgresql_role.roles argument and the postgresql_grant_for resource?

Are there any future plans to remove the roles argument to allow these resources to play nicely with each other?

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

0.15.0

Affected Resource(s)

• provider

Terraform Configuration Files

provider "postgresql" {
  host = <blah>
  port = 5432
  database = "postgres"
  username = <user>
  password = <password>
  sslmode = "require"
  connect_timeout = 60
  expected_version = ""
  superuser = false
}

Debug Output

I could not find relevant debug output

Panic Output

No panic, at least

Expected Behavior

Nothing should have happened if the engine version is empty and no resources are defined.

Actual Behavior

Even if there are no resources for this provider, with an empty version, I get the unhelpful error:
invalid version (""): strconv.ParseUint: parsing "": invalid syntax

Steps to Reproduce

With the above config, run terraform plan

Hi,

First, well done for your provider, it's very useful!
I have a feature request, do you plan to Data sources for existing roles?

Expected code could be like this:

data "postgresql_role" "my_admin" {
  name     = "my_admin"
}

And my expected behaviour is to load an existing role, and to grant new roles to it.

Thanks in advance for your feedback
Regards

Damien

I can successfully add an Azure AD user as PostgreSQL admin, but when I try and connect to the PostgreSQL server using the same user (to add SQL roles etc.) in the PostgreSQL provider, I get the following error:

Error: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: pq: An error occurred while validating the access token. Please acquire a new token and retry

Do I have to do something different with the Terraform provider to use an Azure AD user? Here is my config:

provider "postgresql" {
host = var.sql_server
port = 5432
database = var.service_name
username = var.sql_username
password = var.sql_password
sslmode = "require"
superuser = false
connect_timeout = 15
}

Thanks,
Wayne

Terraform Version

hashicorp/terraform:0.14.11

Affected Resource(s)

• postgresql_database

Terraform Configuration Files

terraform {
  required_providers {
    postgresql = {
      source = "cyrilgdn/postgresql"
      version = "= 1.13.0"
    }
  }
}

...

resource "postgresql_role" "eric" {
  name                      = "eric"
  password                  = "eric"
  login                     = true
  encrypted_password        = true
  superuser                 = false
  create_database           = false
  create_role               = false
  replication               = false
  bypass_row_level_security = false 
}

resource "postgresql_database" "eric" {
  name              = "eric"
  owner             = postgresql_role.eric.name
  connection_limit  = 100
  allow_connections = true
  tablespace_name   = "DEFAULT"
  is_template       = false
  template          = "DEFAULT"
  encoding          = "DEFAULT"
  lc_collate        = "DEFAULT"
  lc_ctype          = "DEFAULT"
}

Expected Behavior

When I run terraform apply after the initial one, it should not try to destroy and re-create the postgres database.

Additionally, the value of "template" should be "template0".

Actual Behavior

When I run terraform apply after the initial one, it tries to destroy and re-create the postgres database.

This is because the value "DEFAULT" is substituted by the actual default value in most fields and during future iterations, it doesn't safeguard against that change, thus leading Terraform to believe that those fields have changed and that the database should be re-created.

It doesn't do that for the "template" field, because it leaves it as "DEFAULT" instead of substituting for "template0".

It is probably better to explicitly input the defaults anyways to safeguard against the future, but the documentation says you can use "DEFAULT" and I'm pretty sure someone who uses it will be in for a shock.

Steps to Reproduce

Use the configuration above, run terraform apply and then terraform plan (or just terraform apply twice)

Hi there,

Thank you for making this awesome provider. I am interested in connecting to postgresql through a bastion server, would there be any appetite for adding this as a feature? I could see the provider configuration looking something like this:

provider "postgresql" {
  host            = "postgres_server_ip"
  port            = 5432
  database        = "postgres"
  username        = "postgres_user"
  password        = "postgres_password"
  sslmode         = "require"
  connect_timeout = 15
  bastion {
    host = example.com
    port = 22
    private_key = <superSecretKey>
  }
}

Something similar to the Provisioner Connection Setting would be super helpful.

Hello,

In the resource postgresql_default_privileges, schema could be optional, in order to achieve:

alter default privileges grant select on tables to $role;

And the counter part:

alter default privileges revoke select on tables from $role;

Instead of: https://github.com/cyrilgdn/terraform-provider-postgresql/blob/master/postgresql/resource_postgresql_default_privileges.go#L214 . Is there a specific reason why this is not supported? Is it to enforce the schema management on the infrastructure level? Thank you!

Best,
Bruno

Hi,
First of all, thanks for the great work on this module.
I'm having an issue when trying to create a role that is already existing in the db. The case happens when re-applying the terraform conf on a already existing db, not when creating / initializing it for the first time.
Using postgres, this can be done catching the error using EXCEPTION WHEN DUPLICATE_OBJECT procedural language for example.

Is this the expected behavior or can we work on something (a boolean parameter ?) that could be used to catch that exception ? I would be happy to contribute. Let me know if any additional info is needed in the meantime.

Cheers,
Thomas

Terraform Version

Terraform v0.14.6
provider registry.terraform.io/cyrilgdn/postgresql v1.11.1

Affected Resource(s)

• postgresql_role

Debug Output

Error: error creating role my_role: pq: role "my_role" already exists

  on database_user_mgmt.tf line 1, in resource "postgresql_role" "my_role":
   1: resource "postgresql_role" "my_role" {

Expected Behavior

What should have happened?
Terraform should have done nothing and exiting as role is already there.

Actual Behavior

What actually happened?
Terraform exited with an error

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply on a resource "postgresql_role" "my_role" that is already existing in the db.

resource "postgresql_role" "my_role" {
  name     = "my_role"
  login    = true
  superuser = false
  replication = false
  inherit = true
  create_database = true
  create_role = false
  password = "my_password"
}

Hello,

Currently, if I want to connect to my database, I have to define login and password directly in my provider.

provider "postgresql" {
  host            = "dev-postgresql.postgres.database.azure.com"
  port            = 5432
  database        = "my-db"
  username        = "svc@dev-postgresql"
  password        = "my-password"
  sslmode         = "require"
  connect_timeout = 15
  superuser = false
}

I also discovered we cannot define variable in the provider declaration :(
From your PoV is there a way to inject login and password in another way? Because my need is to be able to commit my terraform on a git repository, and with login/password in clear, it is not a good practise :(

Thanks in advance for your help
Regards
Damien

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

Run terraform -v to show the version. If you are not running the latest version of Terraform, please upgrade because your issue may have already been fixed.

Terraform v0.14.10
+ provider registry.terraform.io/cyrilgdn/postgresql v1.12.1
+ provider registry.terraform.io/hashicorp/aws v3.39.0

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_role
• postgresql_grant_role

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "postgresql_role" "db" {
  name = var.db_role

  lifecycle {
    ignore_changes = [
      roles,
    ]
  }
}

# Elsewhere
resource "postgresql_grant_role" iam_auth" {
  role = var.db_role
  grant_role = "rds_iam"
}

I would like to be able to manage the role and the grants together in Terraform. The documentation for postgresql_grant_role says that it should not be used with postgresql_role because it will lead to a perpetual diff. I believe this can be resolved if you set the roles attribute in postgresql_role to have Computed: true. If the attribute is not set, it will simply read whatever is returned from the database, and I can do away with the ignore_changes.

Hi

at the moment this provider supports resources, but not data.

we have Postgres instances which are setup via another process, which mean we will need to reference Roles or Databases that already exist via the data technique.

Hi there,

Thank you for opening an issue. Please provide the following information:

Trying to create the RDS aurora postgres cluster and then once the cluster is created, trying to create the schema on the postgres. So, both has to happen together. Cluster creation + Schema Creation. I tried all the options suggested in this but still getting error. hashicorp/terraform-provider-postgresql#2

When running plan, i get below error -
Error: could not check if database exists: dial tcp: lookup <> on 7.7.7.7:53: no such host

Note: first time it worked, when I am trying again it started sending this errors. Worked only first time :(

Please help. I need this to be working ASAP.

Terraform Version

terraform -v
Terraform v0.12.26

• provider.aws v3.37.0
• provider.null v3.1.0
• provider.postgresql v1.7.2
• provider.random v3.1.0

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_schema

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

//Postgres Provider - For more information -> https://registry.terraform.io/providers/cyrilgdn/postgresql/latest/docs
provider "postgresql" {
  host             =  module.aurora.this_rds_cluster_endpoint  
  port             = module.aurora.this_rds_cluster_port       
  database         = module.aurora.this_rds_cluster_database_name
  username         = data.aws_ssm_parameter.username.value 
  password         = data.aws_ssm_parameter.password.value
  superuser        = false 
  expected_version = "12"
}

resource "postgresql_role" "test_role" {
  name               = data.aws_ssm_parameter.schema_name.value
  encrypted_password = true
  login              = true
  password           = data.aws_ssm_parameter.schema_password.value
  create_role        = true 
  superuser          = false
}

resource "postgresql_schema" "test_schema" {
  name          = data.aws_ssm_parameter.schema_name.value
  if_not_exists = true
  owner         = data.aws_ssm_parameter.schema_name.value
  drop_cascade = true
}

resource "postgresql_grant" "test_grant_privileges" {
  database    = module.aurora.this_rds_cluster_database_name
  role        = postgresql_role.test_role.name 
  schema      = data.aws_ssm_parameter.schema_name.value
  object_type = "database"
  privileges  = ["ALL"] 
}

Debug Output

Error: could not check if database exists: dial tcp: lookup <> on 7.7.7.7:53: no such host

Expected Behavior

Clusters and Schema should be created.

Actual Behavior

Plan is failing with below error ->
Error: could not check if database exists: dial tcp: lookup <> on 7.7.7.7:53: no such host

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform init
2. terraform plan

Important Factoids

Are there anything atypical about your accounts that we should know? For example: Running in EC2 Classic? Custom version of OpenStack? Tight ACLs?

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:
Tried the solutions from here -> hashicorp/terraform-provider-postgresql#2
None of them working.
Please help

Terraform Version

Terraform v0.13.5

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

resource "postgresql_role" "user" {
  name     = "test"
  password = "test"
  login    = true
}

resource "postgresql_grant" "user_functions" {
  role        = postgresql_role.user.name
  database    = "test"
  schema      = "public"
  object_type = "function"
  privileges  = ["EXECUTE"]
}

Debug Output

ERROR: pq: got 3 parameters but the statement requires 2

Expected Behavior

The grant privileges query for functions should work as expected.

Actual Behavior

TThe grant privileges query for functions fails because the composed query used to read role privileges passes in 3 params to a text where only 2 params are interpolated

Steps to Reproduce

1. terraform plan

Hello,
Is it possible to add a new feature to the provider : Connecting to Azure database postgresql using Azure Active DIrectory authentication as described here
The idea is that the password is a token that is fetched from Azure active directory using clientSecretCrentials.
I have already forked the project and implement this feature, I can make a pull request if you are ok to add this feature to this provider.
What do you think about it ?
Thanks for your feedback

Hello,

We run everything on AWS, RDS Aurora Postgres 9.6 and 11.9.
Our terraform CI pipelines also run in AWS EC2.

Since upgrading to provider version 1.10 some of our plans have doubled in execution time.
We use to have plan execution times of maximum 4-5 mins for all our AWS regions.
Since 1.10 the TF plans that need to connect to RDS instances in different AWS regions take double: 9-10 mins.
A plan would contain probably around 100-150 postgres_* resources, mostly grants, roles and around 10-15 databases.

Terraform Version

Terraform v0.13.0

Affected Resource(s)

• postgresql_grant
• postgresql_default_privileges

Terraform Configuration Files

resource "postgresql_database" "db" {
  name = var.database
  owner = postgresql_role.r[0].name
}

resource "postgresql_grant" "g" {
  for_each = var.grant_roles

  role        = each.key
  database    = postgresql_database.db.name
  schema      = "public"
  object_type = "table"
  privileges  = each.value
}

resource "postgresql_default_privileges" "dp" {
  for_each = var.grant_roles

  role        = each.key
  database    = postgresql_database.db.name
  owner       = postgresql_role.r[0].name
  schema      = "public"
  object_type = "table"
  privileges  = each.value
}

Expected Behavior

Plans should take no longer than 4-5 minutes, as with version 1.9.0

Actual Behavior

Plans take 9-11 minutes, hence doubling our times.

Steps to Reproduce

Upgrade provider from 1.9.0 to 1.10

Important Factoids

We suspect this PR: #5 to be the culprit. But we have no easy way of testing it. Downgrading to 1.9.0 is now quite hard for us, since we started using GRANT on schemas which was added in 1.11.

Hi

It will be good if postgresql_grant resource will allow ALL in addition to others like

privileges - (Required) The list of privileges to grant. There are different kinds of privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

as per

GRANT { { CREATE | USAGE } [,...] | **ALL** [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

edit by @cyrilgdn :
Recreation of hashicorp/terraform-provider-postgresql#72 and hashicorp/terraform-provider-postgresql#166

Terraform Version

Terraform v0.13.5

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

This should be as simple as adding a new object type called schema so it should look like this:

resource "postgresql_grant" "grant" {
  role        = "test"
  database    = "test"
  schema      = "public"
  object_type = "schema"
  privileges  = ["USAGE", "CREATE"]
}

Which should execute this under the hood:

GRANT USAGE,CREATE ON SCHEMA public TO test;

See https://www.postgresql.org/docs/9.1/sql-grant.html for supported priviledges.

I've looked everywhere but I can't find why you don't consider to allow granting permissions on column

Hello.

I cannot use provider version 1.13.0. Terraform reports that it failed to query provider packages:

% head -n 13 main.tf
terraform {
  required_version = "~> 0.13"

  backend "gcs" {
    bucket = "my-terraform-state"
    prefix = "production/postgresql_dbs"
  }

  required_providers {
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "~> 1.13.0"
    }

Here is what happens when I try to upgrade my provider plugin (previously used version 1.10.0):

% terraform init -upgrade
Upgrading modules...
...

Initializing the backend...

Initializing provider plugins...
- Finding latest version of hashicorp/vault...
- Finding cyrilgdn/postgresql versions matching "1.10.0, ~> 1.13.0"...
- Using previously-installed hashicorp/vault v2.20.0

Error: Failed to query available provider packages

Could not retrieve the list of available versions for provider
cyrilgdn/postgresql: no available releases match the given constraints 1.10.0,
~> 1.13.0

I use Terraform version 0.14.11.

% terraform version -json
{
  "terraform_version": "0.14.11",
  "terraform_revision": "",
  "provider_selections": {
    "registry.terraform.io/cyrilgdn/postgresql": "1.13.0-pre1",
    "registry.terraform.io/hashicorp/vault": "2.20.0"
  },
  "terraform_outdated": true
}

Expected Behavior

Provider plugin installs successfully.

Actual Behavior

Provider plugin is not installed

Steps to Reproduce

1. terraform init -upgrade with provider version constrain set to 1.13.0 or ~> 1.13.0

Hi,

Originally we had the following in the terraform configuration:

    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "~> 1.11.0"
    }

Which led to recent bump to 1.11.1. Since that release terraform plan errors out when trying to connect to the psql instance.

After changing it to `version = "1.11.0", it works just fine, again.

Terraform Version

0.13.0

Affected Resource(s)

all

Terraform Configuration Files

provider "postgresql" {
  superuser        = false
  host             = google_sql_database_instance.db.connection_name
  scheme           = "gcppostgres"
  username         = google_sql_user.postgres.name
  password         = google_sql_user.postgres.password
  expected_version = "10.0.0"
}

resource "postgresql_grant" "account" {
  for_each    = local.db_roles
  database    = google_sql_database.db[each.value.database].name
  role        = postgresql_role.account[each.value.role].name
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

Expected Behavior

Don't show errors.

Actual Behavior

Error: Error reading grant role: gcppostgres: DialTimeout not supported
Error: gcppostgres: DialTimeout not supported
Error: gcppostgres: DialTimeout not supported

Steps to Reproduce

Upgrade from 1.11.0 to 1.11.1

Terraform Version

Terraform v0.13.6

Affected Resource(s)

• postgresql

Terraform Configuration Files

terraform {
  required_providers {
    postgresql = {
      source = "cyrilgdn/postgresql"
    }
  }
  required_version = ">= 0.13"
}

provider "postgresql" {
  scheme          = "awspostgres"
  superuser       = false
  host            = var.host
  port            = 5432
  database        = var.root_database
  username        = var.root_username
  password        = var.root_password
  sslmode         = "require"
  connect_timeout = 10
}

resource "postgresql_role" "admin" {
  name             = "admin"
  login            = true
  password         = "theadminpassword"
  connection_limit = 100
  create_database  = true
}

example.tfvars

host          = "my-rds-db.asdf12345.us-gov-east-1.rds.amazonaws.com"
root_username = "therootuser"
root_password = "therootpassword"
root_database = "example_db"

Debug Output

https://gist.github.com/swoodford-clear/ffaa75e7a414db80648d8a5d31ff1ede

Expected Behavior

resource postgresql_role.admin created

Actual Behavior

Error: error detecting capabilities: error PostgreSQL version: x509: certificate signed by unknown authority

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

Important Factoids

I'm running MacOS Mojave 10.14.6 (18G8022).
In AWS region us-gov-east-1, the only RDS Certificate authority currently available is rds-ca-2017.
I've tried installing the relevant certs from AWS to my Keychain: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html
AWS GovCloud (US-East) (Root CA-2017) root certificate for an AWS GovCloud (US) Region
AWS GovCloud (US-East) (CA-2017) intermediate certificate for an AWS GovCloud (US) Region
And the certificate bundle that contains both the intermediate and root certificates for the AWS GovCloud (US) Regions.
I've made sure they're set to Always Trust in my keychain.
I've tried alternate sslmode settings: require, disable, prefer, verify-full. Nothing worked.
I can connect to this db using the regular psql command line.

Hello!

Im experiencing trouble with "postgresql_grant"

TF 0.14.6, posgresql provider 1.11.1

terraform plan output:

module.postgres_readonly.postgresql_grant.grant_readonly_connect will be updated in-place
  ~ resource "postgresql_grant" "grant_readonly_connect" {
        id                = "service_ro_service_test_public_table"
      ~ privileges        = [
          + "SELECT",
        ]
        # (5 unchanged attributes hidden)
    }
module.postgres_readonly.postgresql_grant.grant_readonly_sequence will be updated in-place
  ~ resource "postgresql_grant" "grant_readonly_sequence" {
        id                = "service_ro_service_test_public_sequence"
      ~ privileges        = [
          + "SELECT",
        ]
        # (5 unchanged attributes hidden)
    }
module.postgres_readonly.postgresql_grant.grant_readonly_table will be updated in-place
  ~ resource "postgresql_grant" "grant_readonly_table" {
        id                = "service_ro_service_test_public_table"
      ~ privileges        = [
          + "SELECT",
        ]
        # (5 unchanged attributes hidden)
    }
Plan: 0 to add, 3 to change, 0 to destroy.

But terraform apply fails with:

Error: Error granting role postgres to service_test: pq: must have admin option on role "postgres"
  on .terraform/modules/postgres_readonly/modules/read-only-user/main.tf line 97, in resource "postgresql_grant" "grant_readonly_table":
  97: resource "postgresql_grant" "grant_readonly_table" {
Error: Error granting role postgres to service_test: pq: must have admin option on role "postgres"
  on .terraform/modules/postgres_readonly/modules/read-only-user/main.tf line 115, in resource "postgresql_grant" "grant_readonly_sequence":
 115: resource "postgresql_grant" "grant_readonly_sequence" {

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

Terraform version is 0.14.9

Affected Resource(s)

Postgresql

Terraform Configuration Files

terraform {
  required_providers {
    aws = {
      source = "hashicorp/aws"
    }
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.12.0"
    }
  }
  required_version = ">= 0.13.0"
}
provider aws {
  region = "eu-central-1"
}
data "aws_vpc" "default" {
  tags = {
    Name = "shared-dev-vpc"
  }
}

data "aws_subnet_ids" "private" {
  vpc_id = data.aws_vpc.default.id

  filter {
    name   = "tag:Tier"
    values = ["Private", "private"]
  }
}

resource "aws_db_subnet_group" "db_subnet_group" {
  name       = "test-db-subnet-group"
  subnet_ids = data.aws_subnet_ids.private.ids
}
resource "aws_security_group" "db_secgrp" {
  vpc_id      = data.aws_vpc.default.id
  name        = "test_access_rules"
  description = "Security Group allowing access to RDS instance"
  ingress {
    description = "TLS from VPC"
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }  
}



resource "aws_db_instance" "db" {
  allocated_storage    = 20
  storage_type         = "gp2"
  engine               = "postgres"
  engine_version       = "10.11"
  instance_class       = "db.t2.micro"
  identifier           = "test-c-db"
  name                 = "foo"
  username             = "foo"
  password             = "foobarbaz"
  parameter_group_name = "default.postgres10"
  vpc_security_group_ids = [aws_security_group.db_secgrp.id]
  db_subnet_group_name = aws_db_subnet_group.db_subnet_group.name
  skip_final_snapshot  = true
}

provider "postgresql" {
  host             = aws_db_instance.db.address
  port             = aws_db_instance.db.port
  database         = aws_db_instance.db.name
  username         = aws_db_instance.db.username
  password         = aws_db_instance.db.password
  sslmode          = "require"
  connect_timeout  = 15
  superuser        = false
  expected_version = aws_db_instance.db.engine_version
}

resource "postgresql_role" "my_role" {
  name     = "user"
  login    = true
  password = "user01"
  depends_on = [
    aws_db_instance.db
  ]
  count = aws_db_instance.db == null?0:1
}

resource "aws_iam_role" "test" {
  name               = "testService"
  assume_role_policy = data.aws_iam_policy_document.enhanced-monitoring-policy-document.json
}

data "aws_iam_policy_document" "enhanced-monitoring-policy-document" {
  statement {
    actions = [
      "sts:AssumeRole",
    ]

    effect = "Allow"

    principals {
      type        = "Service"
      identifiers = ["monitoring.rds.amazonaws.com"]
    }
  }
}

Expected Behavior

Role import should work.

Actual Behavior

When importing any resource not directly related to Postgres terraform fails complaining that the provider does depend on values that cannot be determined until apply:

terraform import aws_iam_role.test postgres-gp2-true-false
aws_iam_role.test: Importing from ID "postgres-gp2-true-false"...
aws_iam_role.test: Import prepared!
  Prepared aws_iam_role for import
aws_iam_role.test: Refreshing state... [id=postgres-gp2-true-false]

Error: Invalid provider configuration

  on /home/javier/projects/tf-pg-bug/main.tf line 52:
  52: provider "postgresql" {

The configuration for provider["registry.terraform.io/cyrilgdn/postgresql"]
depends on values that cannot be determined until apply.

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform import aws_iam_role.test <existing role>

Important Factoids

Terraform apply completes successfully. Only issue is when trying to import resources.

Hi there,

Thank you for opening an issue. Please note that we try to keep the Terraform issue tracker reserved for bug reports and feature requests. For general usage questions, please see: https://www.terraform.io/community.html.

Terraform Version

Run terraform -v to show the version. If you are not running the latest version of Terraform, please upgrade because your issue may have already been fixed.
root@salt:~/terraform/psql_admins# terraform -v
Terraform v0.13.5

• provider registry.terraform.io/cyrilgdn/postgresql v1.8.1
• provider registry.terraform.io/hashicorp/aws v3.16.0
• provider registry.terraform.io/hashicorp/external v2.0.0
• provider registry.terraform.io/hashicorp/kubernetes v1.13.3
• provider registry.terraform.io/hashicorp/local v2.0.0
• provider registry.terraform.io/hashicorp/random v3.0.0

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_grant

Terraform Configuration Files

resource "postgresql_grant" "readwrite_dbs" {
  for_each = data.external.dbs.result

  database    = each.key
  role        = postgresql_role.rw-role.name
  schema      = "public"
  object_type = "database"
  privileges  = ["CONNECT"]
}

resource "postgresql_grant" "readwrite_tables" {
  for_each = data.external.dbs.result

  database    = each.key
  role        = postgresql_role.rw-role.name
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER"]
}

resource "postgresql_grant" "readwrite_sequence" {
  for_each = data.external.dbs.result

  database    = each.key
  role        = postgresql_role.rw-role.name
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT", "UPDATE"]
}

resource "postgresql_grant" "readwrite_function" {
  for_each = data.external.dbs.result

  database    = each.key
  role        = postgresql_role.rw-role.name
  schema      = "public"
  object_type = "function"
  privileges  = ["EXECUTE"]
}

Debug Output

Please provider a link to a GitHub Gist containing the complete debug output: https://www.terraform.io/docs/internals/debugging.html. Please do NOT paste the debug output in the issue; just paste a link to the Gist.

Panic Output

If Terraform produced a panic, please provide a link to a GitHub Gist containing the output of the crash.log.

Expected Behavior

no syntax error 👍

Actual Behavior

sql syntax error

Error: pq: got 3 parameters but the statement requires 2



Error: pq: got 3 parameters but the statement requires 2



Error: pq: got 3 parameters but the statement requires 2



Error: pq: got 3 parameters but the statement requires 2



Error: pq: got 3 parameters but the statement requires 2



Error: pq: got 3 parameters but the statement requires 2



Error: pq: got 3 parameters but the statement requires 2



Error: pq: got 3 parameters but the statement requires 2



Error: pq: got 3 parameters but the statement requires 2```

### Steps to Reproduce
Please list the steps required to reproduce the issue, for example:
1. `terraform apply`


### References
Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:
- this merge request bring issue: https://github.com/cyrilgdn/terraform-provider-postgresql/commit/3071ecc51eb2945eecd9eb16d7c0f82c29e70847

Hi there,
I'm currently using your solution and thank you for that.
I'm able to connect to a RDS instance of PostgreSQL and to create role, db or schema.

But I kind of struggle to understand how to define tables and fields here.
Is it something not supported ? Or maybe something I misunderstood in your solution.

If not, do you have any piece of advice on how to achieve that please?

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

Terraform v0.14.3

• provider registry.terraform.io/cyrilgdn/postgresql v1.11.0
• provider registry.terraform.io/hashicorp/aws v3.24.1

Affected Resource(s)

postgresql_grant

Terraform Configuration Files

provider "postgresql" {
  host             = local.pgsql["host"]
  port             = local.pgsql["port"]
  username         = local.pgsql["username"]
  password         = local.pgsql["password"]
  sslmode          = "require"
  connect_timeout  = 15
  superuser        = false
}

resource postgresql_grant "application" {
  database    = postgresql_database.application.name
  role        = postgresql_role.application.name
  object_type = "database"
  privileges  = ["CONNECT", "CREATE", "TEMPORARY"]
  with_grant_option = true
}

Expected Behavior

It should just work without any issues

Actual Behavior

If provider configuration contains no option expected_version the resource is failing with error message:
Error: postgresql_grant resource is not supported for this Postgres version (0.0.0)

Provider is connected to the DB instance and is able to create another resources, such as databases and roles.

It looks like validation is done against expected_version from provider configuration and not against the actual version provider connected to.

If I set the expected_version option everything is working like it should

Steps to Reproduce

Create configuration similar to mentioned above and do terraform apply

When using the postgresql_grant resource, I am not able to use output from another resource in the "database" field. When I supply a string directly, it works.

Terraform Version

Terraform: 0.13.5
AWS provider: 3.23.0
PostgreSQL provider: 1.11.0

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

resource "aws_rds_cluster" "this" {
  cluster_identifier              = "${var.rds_cluster_identifier}-${var.environment}"
  db_subnet_group_name            = aws_db_subnet_group.this.name
  db_cluster_parameter_group_name = aws_rds_cluster_parameter_group.this.name
  deletion_protection             = true
  engine                          = "aurora-postgresql"
  engine_mode                     = "serverless"
  engine_version                  = var.rds_engine_version
  master_username                 = "postgres"
  master_password                 = random_password.rds.result
  backup_retention_period         = var.rds_backup_retention_period
  copy_tags_to_snapshot           = true
  skip_final_snapshot             = true
  vpc_security_group_ids          = [aws_security_group.rds.id]

  scaling_configuration {
    auto_pause               = true
    min_capacity             = var.rds_min_capacity
    max_capacity             = var.rds_max_capacity
    seconds_until_auto_pause = 300
  }

  tags = var.tags
}

resource "postgresql_role" "service" {
  name                = var.rds_service_username
  login               = true
  password            = var.rds_service_password
  skip_reassign_owned = true
  skip_drop_role      = true
}

resource "postgresql_schema" "service" {
  name = "service"
}

resource "postgresql_grant" "service" {
  role        = postgresql_role.service.name
  database    = aws_rds_cluster.this.database_name
  schema      = postgresql_schema.service.name
  object_type = "schema"
  privileges  = ["ALL"]
}

Expected Behavior

Terraform would apply the grant described in postgresql_grant.service.

Actual Behavior

An error occurred:

Error: "database": required field is not set

  on ../../modules/test/main.tf line 253, in resource "postgresql_grant" "service":
 253: resource "postgresql_grant" "service" {

Error: "database": required field is not set

  on ../../modules/test/main.tf line 253, in resource "postgresql_grant" "service":
 253: resource "postgresql_grant" "service" {

Steps to Reproduce

1. Configure the PostgreSQL provider to connect to a local database
2. Configure an RDS database appropriately
3. terraform apply

Terraform Version

v0.14.5

Affected Resource(s)

• postgresql_schema

Terraform Configuration Files

resource "postgresql_schema" "drop_public" {
  database = "testdb"
  name = "public"
  drop_cascade = true
 
 policy {
    usage = false
  }
}

There is a warning message that "policy" is deprecated, but postgresql_grant on object_type schema with privileges set to '[ ]' does not have a "drop_cascade" option.

Expected Behavior

drop public schema cascade

Actual Behavior

What actually happened?
terraform runs without any errors, but public schema is still there

Steps to Reproduce

1. terraform apply

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

Terraform v0.14.9

Terraform Configuration Files

terraform {
  required_version = "= 0.14.9"
  required_providers {
        mypostgresql = {
            source = "registry.terraform.io/cyrilgdn/postgresql"
        }
    }
}

Debug Output

Initializing provider plugins...
- Finding latest version of cyrilgdn/postgresql...
- Finding latest version of hashicorp/time...
- Finding latest version of hashicorp/postgresql...
- Installing cyrilgdn/postgresql v1.12.0...
- Installed cyrilgdn/postgresql v1.12.0 (self-signed, key ID 3918DD444A3876A6)
- Installing hashicorp/time v0.7.0...
- Installed hashicorp/time v0.7.0 (signed by HashiCorp)

Partner and community providers are signed by their developers.
If you'd like to know more about provider signing, you can read about it here:
https://www.terraform.io/docs/cli/plugins/signing.html

Error: Failed to query available provider packages

Could not retrieve the list of available versions for provider
hashicorp/postgresql: provider registry registry.terraform.io does not have a
provider named registry.terraform.io/hashicorp/postgresql

Expected Behavior

Only "cyrilgdn/postgresql" to be installed.

Actual Behavior

Tries to install "hashicorp/postgresql" provider which is no longer available.

Tried to switch to new Terraform version but cannot test creating of resources as provider is not installed properly.

Hi there,

@cyrilgdn Thank you for creating and maintaining this terraform provider. It definitely helps us a lot in automating the maintenance of the Postgres database.

I have a Postgres instance in GCP and I'm trying to create custom roles and GRANT these roles for an existing database with the below configuration:

Terraform Version

Terraform v0.13.5

Affected Resource(s)

Google CloudSQL

Terraform Configuration Files

module "sql-db_postgresql" {
    source  = "GoogleCloudPlatform/sql-db/google//modules/postgresql"
    version = "4.3.0"
    project_id     = var.project_id
    region            = var.region
    zone               = var.zone
    database_version  = "POSTGRES_13"
    name              = "${var.db_name}-v13-${random_id.suffix.hex}"
    availability_type = "ZONAL"
    disk_size         = "25"
    tier          = "db-g1-small"
    user_name     = "postgres"
    user_password = var.db_password
}

terraform {
    required_providers {
      postgresql = {
        source = "cyrilgdn/postgresql"
        version = "1.13.0"
      }
    }
  }
  
  provider "postgresql" {
      scheme   = "gcppostgres"
      host     = "${var.project_id}/${var.region}/${module.sql-db_postgresql.instance_name}"
      username = "postgres"
      password = var.db_password
      port     = 5432
      superuser = false
  }
  
  
  resource "google_sql_database" "test-database" {
      name     = "test"
      instance = module.sql-db_postgresql.instance_name
      depends_on = [
        module.sql-db_postgresql,
    ]
  }
  
  resource "postgresql_role" "test-read-role" {
      name     = "test-read-role"
      login    = true
      password = var.ia-test-read-password
  }
  
  resource "postgresql_role" "test-write-role" {
      name     = "test-write-role"
      login    = true
      password = var.ia-test-write-password
  }
  
  resource "postgresql_grant" "test-read-grant" {
      database    = "test"
      role        = "ia-test-read-role"
      object_type = "database"
      privileges  = ["SELECT"]
  }
  
  resource "postgresql_grant" "test-write-grant" {
      database    = "test"
      role        = "ia-test-write-role"
      object_type = "database"
      privileges  = ["SELECT","INSERT","UPDATE","DELETE","TRUNCATE"]
  }

I am getting this error when I run 'terraform plan' and I'm clueless what is causing this.

=====
Error: error detecting capabilities: error PostgreSQL version: dial tcp XX.XX:XX:XX:3307: connect: connection timed out
=====

Steps to Reproduce

1. terraform plan

I'm not sure why is this trying to connect to the port 3307. Is this also using some kind of proxy within the GoCloud library? Am I missing something here? Really appreciate any help on this.

Hello and thank you @cyrilgdn for maintaining this terraform provider!

We are running AWS RDS Postgres 11.11 and I'm trying to migrate to cyrilgdn/postgresql from an older terraform-providers/postgresql.

The issue is that terraform plan with v1.13.0 almost always fails with (the number of errors are varies):

│ Error: could not get advisory lock for role sasquatch-alembic: pq: canceling statement due to statement timeout
│
│   with module.prclt_databases.module.rds_instance_sasquatch.postgresql_default_privileges.this["sasquatch-sasquatch_ro"],
│   on ../../../modules/percolate-postgres/main.tf line 179, in resource "postgresql_default_privileges" "this":
│  179: resource "postgresql_default_privileges" "this" {

| Error: could not get advisory lock for role webhook-alembic: pq: canceling statement due to statement timeout
│
│   with module.prclt_databases.module.rds_instance_webhook[0].postgresql_default_privileges.this["webhook-webhook_ro"],
│   on ../../../modules/percolate-postgres/main.tf line 179, in resource "postgresql_default_privileges" "this":
│  179: resource "postgresql_default_privileges" "this" {
...

It looks stable with the previous version 1.12.1 (plan succeeds every time), so I tend to blame #80 (the only change in-between).

Terraform Version

Terraform v1.0.1
on darwin_amd64
+ provider registry.terraform.io/cyrilgdn/postgresql v1.13.0
...

Affected Resource(s)

Failing on:

• postgresql_default_privileges

Terraform Configuration Files

resource "postgresql_default_privileges" "this" {
  for_each = { map of user role / grant / database }

  provider    = postgresql
  database    = postgresql_database.application[each.value.database].name
  role        = postgresql_role.this[each.value.role].name
  schema      = "public"
  object_type = "table"
  owner       = postgresql_role.this["${var.service_name}-alembic"].name
  privileges  = each.value.grant
}

I have used latest version 1.11.0 and have issues with
pq: SSL is not enabled on the server
I have downgraded to 1.9.0 and everything work fine

context:

terraform {
  required_providers {
    kubernetes = {
      source = "hashicorp/kubernetes"
      version = "1.13.3"
    }
    postgresql = {
      source = "cyrilgdn/postgresql"
      version = "1.9.0"
    }
  }
}

provider "kubernetes" {}

// kubectl port-forward postgres-6b66657994-j2bks 25432:5432
provider "postgresql" {
  host = "127.0.0.1"
  port = "25432"
  database = "postgres"
  username = ******
  password = ******
  sslmode = "disable"
}

Copied from hashicorp/terraform-provider-postgresql#165

Terraform Version

Terraform v0.12.24
+ provider.postgresql v1.7.0

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_schema

Terraform Configuration Files

resource "postgresql_database" "database" {
  for_each = var.databases
  name = each.key
  template = "template1"
  lc_collate = "en_US.UTF-8"
  lc_ctype = "en_US.UTF-8"
}

// Don't allow the public role to create in the public schema
resource "postgresql_schema" "public" {
  for_each = var.databases

  database = each.key
  name = "public"
  policy {
    role = "public"
    create = false
    create_with_grant = false
    usage = false
    usage_with_grant = false
  }
}

Expected Behavior

I'd expect the public role not to have the (default) usage and create permissions on the public schema

Actual Behavior

The public schema still allows any user (with the public role) to use and create tables. Note that setting create and usage to true and applying, followed by setting them back to false and applying again does remove the permissions as expected.

Important Factoids

Running on RDS created Postgres 12.3 instance

Just tested with both role = "public" and role = "" (since postgres seems to store ACLS for public as "") but the same behaviour is shown for both

Reading the code, I think there are two problems:

1. When creating a resource, it is only granting permissions, and the code does not seem to have any functionality related to revoking implicitly granted permissions
2. When refreshing the schema resource, while the code reads and parses the policies, it doesn't actually do anything with them. Meaning Terraform will only compare previously set state to desired state, with the current state being completely ignored.

Hi there,

Thank you for opening an issue. Please note that we try to keep the Terraform issue tracker reserved for bug reports and feature requests. For general usage questions, please see: https://www.terraform.io/community.html.

Terraform Version

Run terraform -v to show the version. If you are not running the latest version of Terraform, please upgrade because your issue may have already been fixed.

Affected Resource(s)

Please list the resources as a list, for example:

• Documentation
  https://registry.terraform.io/providers/cyrilgdn/postgresql/latest/docs/resources/postgresql_role

Note:

All arguments including role name and password will be stored in the raw state as plain-text. Read more about sensitive data in state.

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

N/A

Debug Output

N/A

Panic Output

N/A

Expected Behavior

What should have happened?
The link should go to the sensitive data in terraform state page. But that seems to no longer exist at the previous link. It should probably go to https://www.terraform.io/docs/state/sensitive-data.html.

Actual Behavior

What actually happened?
The link directs to https://registry.terraform.io/docs/state/sensitive-data which returns a 404 error page.

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. Go to postgresql_role.
2. Click on the Read more about sensitive data in state. link in the Note section about sensitive data.

Important Factoids

Are there anything atypical about your accounts that we should know? For example: Running in EC2 Classic? Custom version of OpenStack? Tight ACLs?
No

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:
No

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

0.13.6

Affected Resource(s)

• provider "postgresql"
• resource "postgresql_database"

Terraform Configuration Files

resource "google_sql_database_instance" "isa" {
  name                = var.db_instance
  database_version    = var.db_version
  deletion_protection = var.data_deletion_protection

  settings {
    tier = var.db_tier

    ip_configuration {
      ipv4_enabled    = false
      private_network = google_compute_network.primary.id
    }

  }

  timeouts {
    create = "20m"
    delete = "20m"
  }
}

resource "google_sql_user" "admin" {
  name     = "admin"
  instance = google_sql_database_instance.isa.name
  password = var.admin_db_pw
}

provider "postgresql" {
  username         = google_sql_user.admin.name
  password         = google_sql_user.admin.password
  scheme           = "gcppostgres"
  host             = google_sql_database_instance.isa.connection_name
  port             = 5432
  expected_version = "11.0.0"
}

resource "postgresql_database" "my_db" {
  name              = "my_db"
  owner             = google_sql_user.admin.name
  template          = "template0"
  lc_collate        = "C"
  connection_limit  = -1
  allow_connections = true
}

Debug Output

Expected Behavior

The new database is created, via cloud_sql_proxy tunnel provided by the go cloud lib.

Actual Behavior

postgresql_database.my_db: Still creating... [1m10s elapsed]

Error: Error creating database "my_db": dial tcp 10.35.0.3:3307: connect: operation timed out

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

Important Factoids

I can connect to the DB via local cloud_sql_proxy. DB does not have a public IP.

I'm using the postgresql provider to create a role for an Azure AD group (that works OK), but then I want to add that role to another role, using the below Terraform:

resource "postgresql_grant_role" "grant_db_reader" {
role = postgresql_role.reader.name (this is an existing PostgreSQL role)
grant_role = postgresql_role.azuread_db_reader.name (this is my new role based on an Azure AD group)
}

When I run the pipeline I get the following error "Error: could not execute grant query: pq: Cannot grant an AAD user to another role."

If I go into DBeaver however, I'm successfully able to do this with a GRANT statement.

Does the Terraform provider not support this?

Thanks,
Wayne

Hi there,

I'm about to assign proper readonly permission for user in public schema.

After reading this page https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/ and checking #33 (comment) I expected to configure everything as planned, but after some simple testing I see that role isn't assigned properly.

Basically I'd like to apply the following config:

CREATE ROLE test_readonly;
GRANT CONNECT ON DATABASE test TO test_readonly;
GRANT USAGE ON SCHEMA public TO test_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_readonly;

Terraform Version

Terraform v0.13.6

Terraform Configuration Files

Database is created via aws module https://registry.terraform.io/modules/terraform-aws-modules/rds/aws/latest

module "test-db" {
  source = "../../../external_modules/terraform-aws-modules/terraform-aws-rds"

  identifier = "test-db"

  engine            = "postgres"
  engine_version    = "13.1"
  instance_class    = "db.t3.small"
  allocated_storage = 10
  storage_encrypted = false

  name     = "test"
  username = "test"

  password = aws_ssm_parameter.test_db_password.value
  port     = "5432"

  # disable backups to create DB faster
  backup_retention_period = 0

  // only this log types available on postgres 13.1
  enabled_cloudwatch_logs_exports = ["postgresql", "upgrade"]

  # DB subnet group
  multi_az   = false
  subnet_ids = module.vpc-demo-eu-west-2.public_subnets

  # DB parameter group
  family = "postgres13"

  # DB option group
  major_engine_version = "13"

  # Snapshot name upon DB deletion
  final_snapshot_identifier = "${var.environment}-${var.region}-test-db"

  # Database Deletion Protection
  deletion_protection = false

  performance_insights_enabled          = false
  performance_insights_retention_period = 0

  iam_database_authentication_enabled = true

  tags = merge(
    local.tags,
  )
}


output "test-db-endpoint" {
  value = module.test-db.this_db_instance_address
}



resource "postgresql_role" "test_readonly" {
  name     = "test_readonly"
  login    = true
  password = var.test_db_password
}

resource "postgresql_grant" "grant_ro_sequence" {
  database    = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
}

resource "postgresql_grant" "grant_ro_tables" {
  database    = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

resource "postgresql_default_privileges" "alter_ro_tables" {
  database    = "test"
  owner       = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

resource "postgresql_default_privileges" "alter_ro_sequence" {
  database    = "test"
  owner       = "test"
  role        = "test_readonly"
  schema      = "public"
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
}

When I run below query, I don't see any roles assigned after terraform apply.

SELECT 
      r.rolname, 
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;

it takes effect only if I grant permissions manually via GRANT readonly TO test_readonly;

Debug Output

Please provider a link to a GitHub Gist containing the complete debug output: https://www.terraform.io/docs/internals/debugging.html. Please do NOT paste the debug output in the issue; just paste a link to the Gist.

Expected Behavior

What should have happened?

Role should be assigned with appropriate permissions

Actual Behavior

What actually happened?

Role isn't assigned to user

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

Hi there,

I use different username to connect to database, not postgres. When I try to use below config with AWS RDS postgres version 13 it throws error

postgresql_role.rdsdb-readonly-role: Creating...

Error: error detecting capabilities: error PostgreSQL version: pq: password authentication failed for user "postgres"

Does this module support postgres version 13?
Does this module support custom database usernames?

Terraform Version

Terraform v0.14.10

Terraform Configuration Files

terraform {
  required_providers {
    aws = {
      source = "hashicorp/aws"
      version = ">=3.6.0"
    },
    postgresql = {
      source = "cyrilgdn/postgresql"
      version = ">=1.12.0"
    }
  }
  required_version = ">= 0.12"
}

provider "postgresql" {
    host = "localhost"
    username = "myuser"
    password = "mypassword"
    connect_timeout = 20
    expected_version = 13
}

resource "postgresql_role" "rds-readonly-role" {
  name = "rds-readonly"
  password  = "mypassword"
  login = true
  replication = true
  connection_limit = -1
}

Debug Output

Initializing modules...

Initializing the backend...

Initializing provider plugins...
- Using previously-installed hashicorp/local v2.1.0
- Using previously-installed hashicorp/archive v2.0.0
- Using previously-installed hashicorp/null v3.1.0
- Using previously-installed hashicorp/template v2.2.0
- Using previously-installed hashicorp/helm v2.0.3
- Using previously-installed hashicorp/kubernetes v1.13.3
- Using previously-installed cyrilgdn/postgresql v1.12.0

...
...
postgresql_role.rdsdb-readonly-role: Creating...

Error: error detecting capabilities: error PostgreSQL version: pq: password authentication failed for user "postgres"

Expected Behavior

RDS Role is created

Actual Behavior

postgresql_role.rdsdb-readonly-role: Creating...

Error: error detecting capabilities: error PostgreSQL version: pq: password authentication failed for user "postgres"

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

Hi. Should You add view to object type for postgresql_grant?

Based on the error message it looks like the port is not being applied from the provider configuration.

Terraform Version

Terraform v0.13.5

Affected Resource(s)

• provider

Terraform Configuration Files

terraform {
  required_providers {
    postgresql = {
      source = "cyrilgdn/postgresql"
      version = "1.8.1"
    }
  }
}

provider "postgresql" {
  alias           = "pg-dev"
  host            = "<my_db_address>"
  port            = 14524
  database        = "<my_db>"
  username        = "<my_username>"
  password        = "<my_password>"
  sslmode         = "require"
}

resource "postgresql_extension" "uuid-ossp" {
  name = "uuid-ossp"
}

Expected Behavior

New extension is created

Actual Behavior

Error: Error initializing PostgreSQL client: error detecting capabilities: error PostgreSQL version: dial tcp :5432: connect: connection refused

  on <empty> line 0:
  (source code not available)

Steps to Reproduce

1. terraform apply

When defining a postgresql_grant resource with object_type = "function", reading the grant fails.

This is caused by the following statement:

It creates a query with 2 parameters but the statement is provided with 3 parameters (later in the function), causing the following error:

pq: got 3 parameters but the statement requires 2

Howdy!

Thanks for this provider! I'm using Aurora Postgresql 12.4 and would like to inject the RDS cert bundle (from https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem) that I have stored in Secrets Manager as plain text. I am using the Terraform data lookup for aws_secretsmanager_secret and aws_secretsmanager_secret_version to lookup the secret and get its value, respectively. My secret lookup and provider config look like:

data "aws_secretsmanager_secret" "rds_cert_bundle" {
  name = "/rds/cert/bundle"
}

data "aws_secretsmanager_secret_version" "rds_cert_bundle_version" {
  secret_id = data.aws_secretsmanager_secret.rds_cert_bundle.id
}

provider "postgresql" {
  host             = var.metadb_cluster_endpoint
  port             = var.metadb_port
  database         = "postgres"
  username         = var.metadb_user
  password         = var.metadb_password
  superuser        = false
  sslmode          = "verify-full"
  sslrootcert      = data.aws_secretsmanager_secret_version.rds_cert_bundle_version.secret_string
  connect_timeout  = 15
  expected_version = "12.4"
}

When I run a plan, I receive the error:

Error: missing "=" after "MIIEBzCCAu+gAwIBAgICEAAwDQYJKoZIhvcNAQELBQAwgZQxCzAJBgNVBAYTAlVT" in connection info string"

The newlines separating the cert contents are causing problems as I do not get this issue if I specify a local path pointing to the downloaded cert bundle. Is it even possible to inject the cert bundle via a secret?

Terraform Version

Terraform 0.14.8

Affected Resource(s)

provider "postresql"

Terraform Configuration Files

See code above

Debug Output

See code above

Panic Output

n/a

Expected Behavior

Not sure. Would like to find out if I can inject a cert bundle from a Secrets Manager lookup.

Actual Behavior

Error: missing "=" after "MIIEBzCCAu+gAwIBAgICEAAwDQYJKoZIhvcNAQELBQAwgZQxCzAJBgNVBAYTAlVT" in connection info string"

Steps to Reproduce

The error above occurs when I perform a terraform plan

References

#8