postgresql_privs lacks parameters privileges support

[RealGreenDragon]

SUMMARY

Until PostgreSQL 14 you are unable to grant the privilege to edit specific system parameters via SET or ALTER SYSTEM commands, so a superuser is needed.

From PostgreSQL 15 it is changed, as reported on v15 changelog:

Allow GRANT to grant permissions to change individual server variables via SET and ALTER SYSTEM (Mark Dilger)

The new function has_parameter_privilege() reports on this privilege.

As precised here, this is the difference between SET and ALTER SYSTEM commands/privileges:

SET
Allows a server configuration parameter to be set to a new value within the current session. (While this privilege can be granted on any parameter, it is meaningless except for parameters that would normally require superuser privilege to set.)

ALTER SYSTEM
Allows a server configuration parameter to be configured to a new value using the ALTER SYSTEM command.

But postgresql_privs module does not support parameters privileges yet.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

postgresql_privs

ADDITIONAL INFORMATION

Desired module usage to grant permissions to change:

• in current session (SET privilege) parameters log_destination and log_line_prefix at user logtest
• permanently (ALTER SYSTEM privilege) parameters primary_conninfo and synchronous_standby_names at user replicamgr

- hosts: dbs
  tasks:
    - name: GRANT SET ON PARAMETER log_destination,log_line_prefix TO logtest
      community.postgresql.postgresql_privs:
        database: logtest
        state: present
        privs: SET
        type: parameter
        objs: log_destination,log_line_prefix
        roles: logtest

    - name: GRANT ALTER SYSTEM ON PARAMETER primary_conninfo,synchronous_standby_names TO replicamgr
      community.postgresql.postgresql_privs:
        database: replicamgr
        state: present
        privs: ALTER_SYSTEM
        type: parameter
        objs: primary_conninfo,synchronous_standby_names
        roles: replicamgr

[Andersson007]

@RealGreenDragon hi, thanks for the well-filed issue!
I'll put the help_wanted label.
Here's the Quick-start guide if someone from newcomers wants to pick it up (please put your intention here explicitly).
Thanks

[RealGreenDragon]

Fixed issue title and text to add more details.

I found the time to implement it so I opened PR #500.

[tcraxs]

As this is fixed in #500 and released in v3.1.0; @RealGreenDragon can this issue be closed?

[Andersson007]

@tcraxs thanks for spotting and pinging! Let's close it
@RealGreenDragon feel free to re-open if there's anything else to be done and once more thanks for the contribution!