Cannot use postgresql_grant to grant permission

[nguyenhoaibao]

Hi there,

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

Terraform Version

Terraform v1.0.4 on linux_amd64

• provider registry.terraform.io/cyrilgdn/postgresql v1.14.0

Affected Resource(s)

postgresql_grant

Terraform Configuration Files

provider "postgresql" {
  host     = "127.0.0.1"
  port     = "5432"
  username = "[email protected]"
  sslmode  = "disable"
}

resource "postgresql_grant" "read_only_role_usage_schema" {
  database    = "test_db"
  role        = "read_only_role"
  schema      = "public"
  object_type = "schema"
  privileges  = ["USAGE"]
}

Debug Output

╷
│ Error: Error granting role cloudsqlsuperuser to [email protected]: pq: must be superuser or cloudsqlsuperuser to grant or revoke role "cloudsqlsuperuser"
│
│   with postgresql_grant.read_only_role_usage_schema["test_db"],
│   on postgresql.tf line 9, in resource "postgresql_grant" "read_only_role_usage_schema":
│    9: resource "postgresql_grant" "read_only_role_usage_schema" {

Expected Behavior

Run the grant successfully.

Actual Behavior

Can't grant the permissions.

Steps to Reproduce

• The DB instance is hosted by GCP Cloud SQL, version 11.
• Connect through the DB instance on local, using the cloud sql proxy.

  cloud_sql_proxy -instances=my-project:my-instance=tcp:5432
  

• Using postgres user to grant the service account [email protected] permission to in turn grant permission to other roles:

  $ psql -U postgres -h 127.0.0.1 -p 5432
  test_db=> GRANT USAGE ON SCHEMA public TO [email protected] WITH GRANT OPTION;
  

• terraform apply.
• Got the error above.

But if I connect to the DB and grant permission manually, it succeed:

$ psql -U [email protected] -h 127.0.0.1 -p 5432
test_db=> GRANT USAGE ON SCHEMA public TO read_only_role;

Important Factoids

[ggramal]

@nguyenhoaibao maybe your issue is somehow related to https://github.com/cyrilgdn/terraform-provider-postgresql/issues/158#issuecomment-986843526