feat(postgresql): support WITH INHERIT FALSE on role membership grants (PostgreSQL 16+)

[matthewgreenwaldagility]

Summary

The PostgreSQL Grant managed resource currently supports withOption for privilege grants (WITH GRANT OPTION), but has no way to express WITH INHERIT FALSE on role membership grants — a feature introduced in PostgreSQL 16.

Background

PostgreSQL 16 added per-grant inheritance control:

GRANT some_role TO some_user WITH INHERIT FALSE;

This grants membership (the grantee can SET ROLE) without inheriting the role's privileges automatically. It is distinct from WITH GRANT OPTION and cannot be expressed through the existing withOption field.

Use Case

A common Crossplane pattern for RDS PostgreSQL with IAM database authentication:

1. Create an admin role with rds_iam membership (so migration tools like Flyway/Liquibase/Atlas can connect via IAM tokens).
2. Grant the RDS master user membership in admin so it can run ALTER DEFAULT PRIVILEGES FOR ROLE admin.

On PostgreSQL 15, step 2 creates a transitive chain: master_user → admin → rds_iam. This causes the master user's password authentication to break — RDS routes all connections for rds_iam members through PAM (IAM token verification), rejecting the regular password.

On PostgreSQL 16, GRANT admin TO master_user WITH INHERIT FALSE solves this cleanly: the master user has membership (satisfying the ALTER DEFAULT PRIVILEGES requirement) but does not inherit rds_iam and retains password authentication.

Without this field in the Grant CRD, the composition must either omit default privileges management entirely or work around the limitation in ways that push responsibility to the application layer.

Proposed Change

Add a withInherit boolean field to spec.forProvider on the PostgreSQL Grant resource:

apiVersion: postgresql.sql.m.crossplane.io/v1alpha1
kind: Grant
spec:
  forProvider:
    role: master_user
    memberOf: admin
    withInherit: false   # emits: GRANT admin TO master_user WITH INHERIT FALSE

When withInherit is omitted, behavior is unchanged (PostgreSQL defaults to INHERIT TRUE).

This field should only be applied when memberOf is set (role membership grants). It has no meaning for privilege grants (privileges field).

References

• PostgreSQL 16 release notes: https://www.postgresql.org/docs/16/release-16.html
• GRANT syntax: https://www.postgresql.org/docs/16/sql-grant.html
• AWS RDS IAM database authentication: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html