Open fmarino-412 opened 3 months ago
Hello,
yesterday we encountered the same issue for table and sequence default privileges in our databases (Aurora PostgreSQL 14.6 and Aurora PostgreSQL 15.5). After doing some debugging here is what we found out:
In the database logs we could see that the provider correctly executes the ALTER DEFAULT PRIVILEGES
SQL statement originating from the grantRoleDefaultPrivileges
function:
https://github.com/cyrilgdn/terraform-provider-postgresql/blob/f46ec221181b09b153c7fc816e75c7030a3e8ab9/postgresql/resource_postgresql_default_privileges.go#L298-L304
After executing the ALTER DEFAULT PRIVILEGES
SQL statement, the function readRoleDefaultPrivileges
is called to check if the planned new state matches the actual new state. And here is the issue:
To check the provisioned default privileges the readRoleDefaultPrivileges
function executes the following SQL statement:
https://github.com/cyrilgdn/terraform-provider-postgresql/blob/f46ec221181b09b153c7fc816e75c7030a3e8ab9/postgresql/resource_postgresql_default_privileges.go#L231-L248
BUT, the SELECT to pg_default_acl
does not return any rows if you only grant the standard set of privileges for a given object type to the database owner. For schemas the standard set is USAGE and CREATE which can be seen here: postgreSQL docs. In your example "custom-database-role"
is probably the owner of "custom-database-name"
which is why the SELECT mentioned in the code would not return any rows if you grant the standard set (USAGE and CREATE) to it.
Because the SELECT is not returning any rows, terraform thinks that the actual new state differs from the planned new state. This results in the inconsistency mentioned in the supplied error message:
Provider produced inconsistent result after apply
. Specifically Root object was present, but now absent
. Because terraform expects the function readRoleDefaultPrivileges
to return the applied state, which it doesn't when applying the standard set.
But if you would provision default privileges that differ from the standard set (e.g. only USAGE for schemas) to the owner OR provision default privileges to any other role which is not the owner, it will work, as the SELECT correctly returns the expected privileges.
Probably the correct way would be to fix this inside the provider (e.g. updating the readRoleDefaultPrivileges
logic so it assumes the standard set if no rows are returned).
However a workaround could be, to only provision the resource if the privileges differ from the standard set of the object type.
Maybe as an additional note: As we are also using Aurora PostgreSQL it's probably worth to check if for native postgreSQL the behaviour is different.
Hi there,
We're having issues when assigning default privileges on schema to a specific role. The provider returns an error related to a Root object that was present and now absent. Nothing changed between plan and apply operations.
Below all the details:
Terraform Version
Terraform v1.6.1
Affected Resource(s)
Please list the resources as a list, for example:
Expected Behavior
Should grant "custom-database-role" with privileges to use and create schemas on the specified database.
Actual Behavior
This is the output that we are getting back from the previous resource creation:
Steps to Reproduce
terraform apply
Important Factoids
This is running on AWS Aurora with 15.5 as engine version. At steady state we expect to have a custom schema whose owner will be "custom-database-role". The creation of the schema happens after the creation of the role and the assignmenet of deafult permissions.
Thanks in advance for your help/support :)