chore: add MAINTAIN support for postgres >= 17

[sudo-dliberty-eiq]

The MAINTAIN privilege was added in Postgres 17.
https://www.postgresql.org/docs/17/ddl-priv.html

This change adds support for users on Postgres 17, however, opening up means users of this provider attempting to add MAINTAIN permissions on databases <= 17 will get an error.

This change addresses this issue:
#506

This pull request includes changes to the postgresql/helpers.go and postgresql/helpers_test.go files to add the new "MAINTAIN" privilege for tables. The most important changes are as follows:

Privileges update:

• postgresql/helpers.go: Added "MAINTAIN" to the list of allowed privileges for tables.

Test updates:

• postgresql/helpers_test.go: Updated the test case in TestArePrivilegesEqual to include the "MAINTAIN" privilege.

[chafouin]

Hello ! Do you think it would be possible to get a release of the provider soon with this PR merged ? That would help us to get unblocked as we are trying to add the MAINTAIN to permission to some of our users. Thanks !

[yakloinsteak]

I needed this fix as well, but though it now is able to apply MAINTAIN, if you run a plan after that, it seems that postgres has "expanded" this into individual permissions, so you're forever out of sync, oscillating between MAINTAIN and that set.

This is what happens. It either wants to do this or add the MAINTAIN priv.

~ privileges        = [
    - "MAINTAIN",
    + "DELETE",
    + "INSERT",
    + "REFERENCES",
    + "SELECT",
    + "TRIGGER",
    + "TRUNCATE",
    + "UPDATE",
  ]

I think the fix might need to account for this, but I haven't done a lot of testing. I'm going to replace all of our MAINTAINs with this set and move on, though supporting this priv would be nice.

[ooaklee]

Any update on this?

[sudo-dliberty-eiq]

Just waiting on approval to merge by a reviewer with write access

[mohammedelnabawy]

Any update on this?

[lsowen]

@cyrilgdn would you be able to review this change?

[mr-andres-carvajal]

I needed this fix as well, but though it now is able to apply MAINTAIN, if you run a plan after that, it seems that postgres has "expanded" this into individual permissions, so you're forever out of sync, oscillating between MAINTAIN and that set.

This is what happens. It either wants to do this or add the MAINTAIN priv.

~ privileges        = [
    - "MAINTAIN",
    + "DELETE",
    + "INSERT",
    + "REFERENCES",
    + "SELECT",
    + "TRIGGER",
    + "TRUNCATE",
    + "UPDATE",
  ]

I think the fix might need to account for this, but I haven't done a lot of testing. I'm going to replace all of our MAINTAINs with this set and move on, though supporting this priv would be nice.

I assume this will need investigating before merging?

[logan-hcg]

I don't see the "expanded permissions" issue mentioned above when running against a postgres 17 server.

[msdousti24]

I closed my PR as a duplicate of this.

Just for further details, quoting what I wrote there:

The MAINTAIN privilege is added in Postgres 17 (see here). Without it, even the owner of the table cannot perform tasks like VACUUM, ANALYZE, or REINDEX.

Upon adding this privilege, we got this error:

Error: MAINTAIN is not an allowed privilege for object type table

Upon a closer investigation, the list of table privileges in allowedPrivileges needs to be augmented.

The following query on Postgres 17 shows all valid privileges for different types of Postgres objects:

with t(col) as (
select string_to_table('cdfFlLnprsStT', null)
)
select col as object_type, array_agg(privilege_type) as privileges
from t
join lateral aclexplode(acldefault(col::"char", 0)) as a on true
group by 1
order by 1;

Result:

[manish-hashicorp]

Hello, I also need this. Please merge and release if possible. Really appreciate this project. Thanks!

[chafouin]

@cyrilgdn I saw you made a release recently, would it be possible to review/merge this PR and make another release please ? Thanks 🙇