postgresql_privs is not always idempotent

[caseyandgina]

It doesn't seem that postgresql_privs is always idempotent as it should be.

The following always makes a change even on repetitive runs:

- name: Revoke default public privileges on public schema
  community.postgresql.postgresql_privs:
    database: '{{ database }}'
    type: schema
    objs: public
    privs: create,usage
    state: absent
    roles: PUBLIC

Tedious workaround:

- name: Determine if default public privileges are set on public schema
  community.postgresql.postgresql_query:
    db: '{{ database }}'
    query: select ("pg_catalog"."has_schema_privilege" ('public', 'public', 'create') or "pg_catalog"."has_schema_privilege" ('public', 'public', 'usage'))::int "has_schema_privilege"
  register: public_schema_has_default_public_permissions

- name: Revoke default public privileges on public schema
  community.postgresql.postgresql_privs:
    database: '{{ database }}'
    type: schema
    objs: public
    privs: create,usage
    state: absent
    roles: PUBLIC
  when: public_schema_has_default_public_permissions.query_result[0].has_schema_privilege == 1

Likewise for this one:

- name: Revoke default public permissions on {{ database }} database
  community.postgresql.postgresql_privs:
    database: '{{ database }}'
    type: database
    privs: connect,temporary
    state: absent
    roles: PUBLIC

Tedious workaround:

- name: Determine if {{ database }} database has default public permissions
  community.postgresql.postgresql_query:
    db: '{{ database }}'
    query: select ("pg_catalog"."has_database_privilege" ('public', '{{ database }}', 'connect') or "pg_catalog"."has_database_privilege" ('public', '{{ database }}', 'temporary'))::int "has_database_privilege"
  register: database_has_default_public_permissions

- name: Revoke default public permissions on {{ database }} database
  community.postgresql.postgresql_privs:
    database: '{{ database }}'
    type: database
    privs: connect,temporary
    state: absent
    roles: PUBLIC
  when: database_has_default_public_permissions.query_result[0].has_database_privilege == 1

[hunleyd]

thanks for the report @caseyandgina! we'll look into it shortly.

[komima]

Is the always-changed state for public-only grants/revokes for consistency? I've also encountered this issue when this module is used to ensure public schema is usage-only for public, and any custom schemas have all public privileges absent (even though that is the default for new schemas, this ensures any excess privs are removed if present)

At least for schemas pg documentation describes on how the nspacl column in pg_namespace works (where the pre- and post-state of schema privileges is queried from). Initially it is null for new schemas (built-in defaults used instead), and any grant will first populate that value from the built-in defaults and then modify it accordingly.

With the public-always-changed state, for example REVOKE ALL FROM SCHEMA x FROM PUBLIC; is consistent (but incorrect) for first and second run: initial state is null, after first run it is {owner=UC/owner} (nothing has actually changed however, but pre- and post-states for the nspacl differ, changes are reported), and after second run it is still {owner=UC/owner} (nothing has changed, and states are equal, but changes are still reported).

This is different behaviour compared to using GRANT USAGE,CREATE ON SCHEMA x TO owner;, which does not use the public-always-changed state. This will report changes incorrectly on first run (null -> {owner=UC/owner}), and no changes correctly on second run ({owner=UC/owner} -> {owner=UC/owner})).

Could this be fixed by adding the built-in defaults somehow to the queries? There is discussion in some earlier issue for this same thing that no known way exists, but I think there could be a way to coalesce the null column to the actual default value? For example with schemas query could possibly do something like this using acldefault function:

SELECT
  nspname,
  nspacl, -- this is null if nothing has been granted/revoked
  coalesce(nspacl, acldefault('n', nspowner)) actual_nspacl -- this is resolved to the built-in-defaults for the owner
FROM pg_catalog.pg_namespace ns
;

This function seems to be present in at least in PG 10+, even though documentation search only returns hits for 12+.

[Andersson007]

@caseyandgina hello, thanks for reporting the issue! @komima hello, thanks for investigating the case! Any help with solving it would be much appreciated. I think it's OK if it's present in 10 (we can add a condition if the function is present based on PG version or query to the db, use another code). I'll put the help wanted label. We have the Quick start guide. Feel free to experiment and submit a PR. We'd be happy to answer any questions. Thanks!

[RealGreenDragon]

Should be solved with PR #502 .

[Andersson007]

@RealGreenDragon thanks for fixing! Closing the issue. Thanks everyone!