postgresql_membership doesn't recognize ADMIN, SET and INHERIT options

[HauserV]

SUMMARY

Make it possible to control the ADMIN, SET and INHERIT options when GRANTing roles.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

community.postgresql.postgresql_membership

ADDITIONAL INFORMATION

When a non-superuser creates a role in PostgreSQL 16, it is automatically granted the new role only with ADMIN option set. However, that's not enough to create objects owned by the user. To quote the documentation:

To create an object owned by another role or give ownership of an existing object to another role, you must have the ability to SET ROLE to that role; otherwise, commands such as ALTER ... OWNER TO or CREATE DATABASE ... OWNER will fail.

In earlier versions of PostgreSQL, I simply could use the community.postgresql.postgresql_membership to grant the new role to the non-superuser, but that doesn't work anymore because the module doesn't do anything now because the grant is there (albeit not with the options I need).

In my scenario, I cannot use a superuser to do this (superuser wouldn't receive this automatic grant) because I'm using a managed database as a service.

- name: Demo
  hosts: integration
  gather_facts: true
  tasks:
    - name: Create role
      community.postgresql.postgresql_user:
        name: ansible_new_role
        password: dummy123!
        login_host: example.com
        login_db: postgres
        login_user: dbadmin
        login_password: password
        login_port: 5432
    - name: Grant new role to the admin user
      community.postgresql.postgresql_membership:
        group: ansible_new_role
        target_role: dbadmin
        login_host: example.com
        login_db: postgres
        login_user: dbadmin
        login_password: password
        login_port: 5432
    - name: Create database owned by the new role
      community.postgresql.postgresql_db:
        name: ansible_new_database
        owner: ansible_new_role
        login_host: example.com
        login_user: dbadmin
        login_password: password
        login_port: 5432

$ ansible-playbook play.yml -v -i inventory.yml 
Using /etc/ansible/ansible.cfg as config file

PLAY [Demo] **********************************************************************************************************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************************************************
[WARNING]: Platform linux on host integration is using the discovered Python interpreter at /usr/bin/python3.6, but future installation of another Python interpreter could
change the meaning of that path. See https://docs.ansible.com/ansible-core/2.16/reference_appendices/interpreter_discovery.html for more information.
ok: [integration]

TASK [Create role] ***************************************************************************************************************************************************************
changed: [integration] => changed=true 
  queries:
  - 'ALTER USER "ansible_new_role" WITH ENCRYPTED PASSWORD %(password)s '
  user: ansible_new_role

TASK [Grant new role to the admin user] ******************************************************************************************************************************************
ok: [integration] => changed=false 
  granted:
    ansible_new_role: []
  groups:
  - ansible_new_role
  queries: []
  state: present
  target_roles:
  - dbadmin

TASK [Create database owned by the new role] *************************************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: psycopg2.errors.InsufficientPrivilege: must be able to SET ROLE "ansible_new_role"
fatal: [integration]: FAILED! => changed=false 
  msg: |-
    Database query failed: must be able to SET ROLE "ansible_new_role"

PLAY RECAP ***********************************************************************************************************************************************************************
integration                : ok=3    changed=1    unreachable=0    failed=1    skipped=0    rescued=0    ignored=0   

It seems to me that this is partly a bug report because the module would otherwise run a regular GRANT statement which defaults to SET and INHERIT options enabled. In my case, however, it doesn't do anything because it sees the implicit GRANT with the ADMIN option only. There's the obvious workaround to use the community.postgresql.postgresql_query to apply the GRANT no matter what, but that's not clean.

[Andersson007]

@HauserV hello, thanks for the feature idea!
How do you see a possible interface change to adjust those attributes you mentioned, i.e. how would you like it to look in the tasks in your playbooks?

[HauserV]

@Andersson007, I would prefer to introduce three separate boolean parameters: admin_option, inherit_option and set_option. Here's why it would make sense to me:

• It would be consistent with postgresql_privs which already provides a boolean parameter admin_option.
• It would be consistent with pg_auth_members which has three boolean columns with these names.
• It would be consistent with the raw SQL syntax which also operates with three separate boolean options.
• An optional boolean with no default makes it easy to represent the PostgreSQL's semantics:
  • True - ensure that the option is enabled;
  • False - ensure that the option is disabled;
  • None - retain current value when altering an existing membership, follow option-specific default for a new one.

The admin_option should be available for all versions (well, > 8.0), while the other two should raise an exception with PostgreSQL < 16 (e.g., a similar check was introduced into postgresql_user in #222).

Since the module doesn't (yet) expose a parameter to specify the grantor (GRANTED BY <grantor>), it should take into account only memberships granted by the role executing the command. E.g., the implicit membership with the ADMIN option that causes the problem I described above is granted by postgres and therefore shouldn't be "visible" to the module running as dbadmin. Rather than noting that a grant already exists, it should execute GRANT ansible_new_role TO dbadmin creating a new row with grantor equal to dbadmin in pg_auth_members.

(As you can probably see, it took me until I wrote the last paragraph here to understand that my actual problem is due to the conflation of memberships granted by different roles that could be solved by hiding memberships granted by everybody except the role executing the command. Should I split it off to a separate bug ticket?)

[Andersson007]

@HauserV thanks for the detailed feedback! SGTM
would you like to try to implement it yourself? Here's the quick start guide that can help.
if not, let us know, i'll put the help wanted label