postgresq_grant for schema not working when provider has no expected version

[nadirsidi]

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, postgresq_grant not working when provider has no expected version #38
• Interesting background on the x509 thing I mentioned, crypto/x509: Go 1.15 will break default connections to AWS RDS golang/go#39568
• More related info on x509 and Go's interaction with the cloud-- this one is pending changes, crypto/x509: Google Cloud SQL TLS connections broken in Go 1.15+ golang/go#40748

Thank you for your great work!

[cyrilgdn]

Hi,

Thanks for opening this issue and sorry that I didn't answer before.

Do you still have this issue? Did you find some leads?

I can't see the link between expected version and the grant on schema 🤔 .
I'll need to create an Aurora cluster if I want to reproduce it but it's not a little task.

Do you have Postgresql logs when this problem occurs? It's possible that the GRANT silently failed (for example if you don't have the permissions to change the schema privileges).

[nadirsidi]

Hello @cyrilgdn, thank you for your response.

To be honest, I had forgotten this issue and I must have found a workaround.

Looking at what I posted here, versus what I now have in my working configuration, perhaps I was originally missing the table-level permissions within the schema. It works if I also have the following permissions assigned:

resource "postgresql_grant" "schema_crud" {
    database = data.aws_rds_cluster.postgresql.database_name
    role = postgresql_role.readwrite.name
    schema = var.schema
    object_type = "table"
    privileges = ["SELECT", "INSERT", "UPDATE", "DELETE"]
}

Thank you for following-up. If the change above is expected for the required behavior, or you don't feel there's much value is pursuing this further, I'm happy to close this issue.

[velkovb]

Hello,

I am experiencing the certificate issue mentioned in this post. Could find any other open issues on this topic.

@nadirsidi are you able to connect with this provider configuration now?

I tried both awspostgres and just postgres.

[nadirsidi]

Hello @velkovb, it's been a while and I don't have access to the source code anymore because I've since changed jobs.

I believe that was my provider config that worked. The trick was that I had to download that Amazon root certificate. The path listed here is a local file inside a docker container I was running in, where manifest is just a bind-mounted sub-dir in my repo where I have all my terraform files.

sslrootcert = "/code/manifest/AmazonRootCA1.pem"

I believe this is the correct documentation to get the root certificate for the provider trust store, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ssl-certificate-rotation-aurora-postgresql.html#ssl-certificate-rotation-aurora-postgresql.updating-trust-store

[velkovb]

@nadirsidi Thanks for getting back to me. I was not able to get the awspostgres scheme to work. For some reason the regular postgres one didn't work with providing the pem file. Finally, I got it working with only postgres and not sslrootcert.