v1.7.0 breaks permission grants on RDS

[mdespriee]

Our terraform scripts started failing just after your release of v1.7.0 of postgresql provider (we did not pin a version, that's now the case). It seems to be related to AWS RDS context.

I can't produce a small reproducible case right now, sorry. But here are the main elements:

The resource that fails:

resource "postgresql_grant" "reader_access_to_public" {
 provider = postgresql.dwh
 database = aws_db_instance.dwh.name
 role     = postgresql_role.dwh_reader.name
 schema   = "public"

 object_type = "table"
 privileges  = ["SELECT"]
}

That resource is already existing, but terraform wants to update it nevertheless and fails with the following error:

Error: Error granting role rdsadmin to postgres: pq: must be superuser to alter superusers
  on dwh_permissions.tf line 203, in resource "postgresql_grant" "reader_access_to_public":

Note that postgres is our master user for the DB in RDS. It's not a postgresql superuser, because AWS. IIRC rdsadmin is an internal user used by AWS to manage the instance (which is superuser).

The role is defined as follows:

resource "postgresql_role" "dwh_reader" {
  provider            = postgresql.dwh
  name                = "reader"
  login               = true
  password            = data.aws_ssm_parameter.dwh_reader_pwd.value
  skip_reassign_owned = true
}

The DB:

resource "aws_db_instance" "dwh" {
  identifier            = "dwh-${terraform.workspace}"
  max_allocated_storage = var.rds_dwh_autoscale_max_storage
  allocated_storage     = 20
  storage_type          = "gp2"
  engine                = "postgres"
  engine_version        = "11.4"
  instance_class        = var.rds_dwh_instance_class
  name                  = "dwh"
  username              = "postgres"
  password              = data.aws_ssm_parameter.rds_master_pwd.value
  parameter_group_name  = "default.postgres11"

  apply_immediately       = true
  multi_az                = false
  maintenance_window      = "Mon:00:00-Mon:01:00"
  backup_window           = "01:00-02:00"
  backup_retention_period = var.rds_dwh_backup_retention_period

  deletion_protection       = var.rds_dwh_deletion_protected
  skip_final_snapshot       = var.rds_dwh_skip_final_snapshot
  final_snapshot_identifier = "dwh-${terraform.workspace}-final-snapshot"

  vpc_security_group_ids = ["sg-xxx"]
}

The provider:

provider "postgresql" {
  alias           = "dwh"
  host            = aws_db_instance.dwh.address
  port            = aws_db_instance.dwh.port
  database        = aws_db_instance.dwh.name
  username        = aws_db_instance.dwh.username
  password        = data.aws_ssm_parameter.rds_master_pwd.value
  sslmode         = "require"
  connect_timeout = 15
  superuser = false
  max_connections = 10
}

[cyrilgdn]

Hi @mdespriee ,

Thanks for opening this issue. I see what happens here, we need to check how to fix that. Meanwhile, there's a few possible workarounds.

You could (if it does not have other impact for you) alter the owner of the public schema to set it to postgres (alter schema public owner to postgres) instead of rdsadmin. Did you create this database with Terraform too?

When creating a database, the provider forces the use of template0 as template database where the owner of the schema public is rdsadmin. If you run CREATE DATABASE manually it will use template1by default where the owner of the schema public is postgres so postgres user has all privileges.

So we create RDS databases with template = "template1" to fix that. (We also try to understand why template0 has been set as the default value and if we can change it).

It should not fail anyway as it was working before even with rdsadmin as owner so we'll see how to fix it.

[smartpierre]

I can confirm that I have the same issue in 1.7.0 whereas 1.6.0 worked great. I just rolled back to 1.6.0 👍