Provider does not handle grants on partitioned tables correctly

[talbenari-okta]

Description

The provider is able to CREATE grants on partitioned tables, but is not able to see the grants upon a subsequent SELECT. This leads to the following problems:

• It tries (and fails) to CREATE the grants on each subsequent apply, because it doesn't know the grants already exist
• It is unable to modify or delete grants that it previously created

Root Cause

Essentially, the problem is that the SELECT query is doing this:

https://github.com/cyrilgdn/terraform-provider-postgresql/blob/a961e75c59b360f901020c612deef1e1da8ecfea/postgresql/resource_postgresql_grant.go#L347-L353

The value being passed in for $4 (relkind) is objectTypes["table"], where objectTypes is defined here:

https://github.com/cyrilgdn/terraform-provider-postgresql/blob/a961e75c59b360f901020c612deef1e1da8ecfea/postgresql/resource_postgresql_grant.go#L29-L35

This objectTypes definition is a map from resource type to the corresponding Postgres entry for the pg_default_acl.defaclobjtype. However, pg_class.relkind does not use r for partitioned tables - it uses p.

A similar issue exists here: https://github.com/cyrilgdn/terraform-provider-postgresql/blob/a961e75c59b360f901020c612deef1e1da8ecfea/postgresql/resource_postgresql_grant.go#L475

Recommended Fix

 FROM (SELECT relname, attname, (aclexplode(attacl)).* 
       FROM pg_class 
                JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid 
                JOIN pg_attribute ON pg_class.oid = attrelid 
       WHERE nspname = $2 
         AND relname = $3
-        AND relkind = $4) 
+        AND relkind = ANY($4::text[]))

And pass in a slice of all relkind values that correspond to the "table" object.

Terraform Version

Applies to all versions of Terraform.

Affected Resource(s)

• postgresql_grant

References

Someone had found this previously: https://github.com/cyrilgdn/terraform-provider-postgresql/issues/141#issuecomment-944577517. Since the issue was already closed, I imagine it didn't get visibility.

This has existed since the introduction of the postgresql_grant resource in https://github.com/cyrilgdn/terraform-provider-postgresql/commit/d9f18f39fc37eb81f78b73f3f022a69d9f666d5c. When column-level grants were introduced in #135, this issue was carried over to that as well.

[cyrilgdn]

Hi @talbenari-okta ,

Thanks for opening this well-detailed issue and sorry for the response delay.

I get the problem and indeed, provider will not read the state of the partitioned tables because of the relkind. However, during my tests, I don't have the same behavior than you.

The grant works successfully, the only thing that doesn't work is that the provider will not fix the partitioned table permissions if they are modified manually.

Can you share an example of code and/or detail how to reproduce your issue?

Do you target specific table with your grant or all tables of the schema?

Thanks in advance.