PostgresGrant does not revoke permissions during resource deletion

[jasminen]

Hello!

• Vote on this issue by adding a 👍 reaction
• To contribute a fix for this issue, leave a comment (and link to your pull request, if you've opened one already)

Issue details

When PostgresGrant resource is deleted the permissions are not revoked.

Pulumi version: ^3.1.0
Posgresql provider version: ^3.0.0

Steps to reproduce

1. Create a PostgresUser
2. Create PostgresGrant resources to grant some permissions to that user/role.
3. Try to destroy the stack.

    const testUser = new PostgresUser(
      'test-user',
      {
        name: 'test',
        password: 'test-user',
        login: true,
      },
      { provider: dbProvider }
    )
    new PostgresGrant(
      'test-usage-public-schema',
      {
        database: dbName,
        schema: 'public',
        role: testUser.name,
        privileges: ['USAGE'],
        objectType: 'schema',
      },
      { provider: dbProvider }
    )
    new PostgresGrant(
      'test-select-tables-public-schema',
      {
        database: dbName,
        schema: 'public',
        role: testUser.name,
        privileges: ['SELECT'],
        objectType: 'table',
      },
      { provider: dbProvider }
    )

Expected: Stack is destroyed - User granted permissions are revoked, and user is deleted.
Actual: Permissions are not revoked (verified in DB), hence failing to delete the User.

could not delete role test: pq: role "test" cannot be dropped because some objects depend on it

[jasminen]

LInk to an old TF provider issue (seems it was not copied to the new tracked TF provider):
hashicorp/terraform-provider-postgresql#196

[guineveresaenger]

Hi @jasminen - thank you for filing this! We'd love it if you filed an issue in the new upstream provider, and link it here. Then we'll follow up once they've released a fix.

In the meantime, a suggestion - would you perhaps be able to leverage Pulumi's DependsOn resource option to delete the grants before the user?

[jasminen]

Hey @guineveresaenger,
In the upstream provider they require a Terraform Apply output - which i don't have. How would you suggest me to open an issue for them?

The issue is not a dependency issue - the PostgresGrant is deleted before the PostgresUser, but since the deletion of PostgresGrant resource does not revoke the permissions granted by it (as i would expect), the deletion of PostgresUser is failing.

Passing an empty privileges array to PostgresGrant revokes all permissions, but that mean a 2 steps deployment:

1. pass an empty array to revoke permissions
2. destroy the stack / delete the resources
   There is no way for me to accomplish that in our automated Workflow engine, so my "workaround" is to leave all users in DB and only revoke permissions when the resource is intended to be deleted / disabled. It's not a good practice tho. So the code looks like this:

   const disableTestUser = args.disableTestUser ?? false

    const testUser = new PostgresUser(
      'test-user',
      {
        name: 'test',
        password: 'test-user',
        login: true,
      },
      { provider: dbProvider }
    )
    new PostgresGrant(
      'test-usage-public-schema',
      {
        database: dbName,
        schema: 'public',
        role: testUser.name,
        privileges: disableTestUser ? [] : ['USAGE'],
        objectType: 'schema',
      },
      { provider: dbProvider }
    )
    new PostgresGrant(
      'test-select-tables-public-schema',
      {
        database: dbName,
        schema: 'public',
        role: testUser.name,
        privileges: disableTestUser ? [] : ['SELECT'],
        objectType: 'table',
      },
      { provider: dbProvider }
    )

[jasminen]

Opened in upstream provider, hope it'll get attention.
cyrilgdn/terraform-provider-postgresql#187

[jasminen]

Seems to be fixed in Postgres 11.x