community.postgresql.postgresql_membership module – Add or remove PostgreSQL roles from groups

Note

This module is part of the community.postgresql collection (version 5.1.0).

You might already have this collection installed if you are using the ansible package. It is not included in ansible-core. To check whether it is installed, run ansible-galaxy collection list.

To install it, use: ansible-galaxy collection install community.postgresql. You need further requirements to be able to use this module, see Requirements for details.

To use it in a playbook, specify: community.postgresql.postgresql_membership.

Synopsis 

Adds or removes PostgreSQL roles from groups (other roles).
Users are roles with login privilege.
Groups are PostgreSQL roles usually without LOGIN privilege.
Each memberships row names the groups and target roles of one membership and, on PostgreSQL 16 and later, its granting role and membership options.
Common use case:
1. add a new group by community.postgresql.postgresql_user module with role_attr_flags=NOLOGIN
1. grant it the desired privileges by community.postgresql.postgresql_privs module
1. add the desired PostgreSQL users to the new group with memberships

Requirements 

The below requirements are needed on the host that executes this module.

psycopg2 >= 2.5.1

Parameters 

Parameter	Comments
ca_cert aliases: ssl_rootcert string	Specifies the name of a file containing SSL certificate authority (CA) certificate(s). If the file exists, the server’s certificate will be verified to be signed by one of these authorities.
connect_params dictionary added in community.postgresql 2.3.0	Any additional parameters to be passed to libpg. These parameters take precedence. Default: `{}`
fail_on_role boolean	If `true`, fail when group or target_role doesn’t exist. If `false`, just warn and continue. A granted_by role that does not exist always fails. Choices: `false` `true` ← (default)
granted_by_any boolean added in community.postgresql 5.0.0	Manage every grant of a membership, whoever made it, instead of the one grant recorded under the granting role named or derived. `GRANT` then names no granting role and leaves the choice to PostgreSQL, and state=absent and state=exact revoke every grant of a membership, one `REVOKE` per granting role, so the membership goes away. This is what the deprecated groups does. The default for the rows of memberships that set neither their own `granted_by_any` nor `granted_by`, and what state=exact uses for the groups no row names. The connecting role must hold the privileges of every granting role found recorded; the module checks that before revoking anything. Before PostgreSQL 16 a membership has one grant, so this changes nothing there. Choices: `false` ← (default) `true`
groups aliases: group, source_role, source_roles list / elements=string	DEPRECATED. This option will be removed in version 6.0.0. granted_by_any=true with the `groups` key of memberships does the same on every version. The list of groups (roles) that need to be granted to or revoked from target_roles. Mutually exclusive with memberships, and one of the two is required. On PostgreSQL 16 and later it treats the (group, target role) pair as the membership, whoever granted it; see the notes.
login_db string	Name of database to connect to.
login_host string	Host running the database. If you have connection issues when using `localhost`, try to use `127.0.0.1` instead. Default: `""`
login_password string	The password this module should use to establish its PostgreSQL session. Default: `""`
login_port integer	Database port to connect to. Default: `5432`
login_unix_socket string	Path to a Unix domain socket for local connections. Default: `""`
login_user string	The username this module should use to establish its PostgreSQL session. Default: `"postgres"`
memberships list / elements=dictionary added in community.postgresql 5.0.0	A list of objects, each describing one membership. Use this to manage memberships that need a different granting role or different options from one another, which the top-level parameters cannot express because they name one value for the whole task. On PostgreSQL 16 and later a row manages the one grant recorded under its granting role; see the notes. Mutually exclusive with groups. The top-level target_roles may still be given, as the default for rows that do not name their own. state, fail_on_role and the connection parameters describe the task and stay at the top level. The whole task is one transaction, so a failure part-way leaves none of the memberships applied. This is the difference from looping this module over a list, which gives one transaction per item. Two rows may not describe the same grant, meaning the same group, target role and granting role, the derived one included, since the second would only overwrite the options of the first. An option key left out leaves that option as it is, so a new grant gets PostgreSQL’s default for it and an existing grant keeps its value. The option keys are ignored with state=absent. An empty list with state=exact leaves the target roles a member of no group, as an empty groups list does. It needs the top-level target_roles. With the other states it does nothing.
admin_option boolean	Controls the membership option `ADMIN` for this membership. Requires PostgreSQL 16 or later. Choices: `false` `true`
granted_by string	Role to record as the granting role of this membership. When not set, the granting role is the bootstrap superuser if the connecting role is a superuser, and the connecting role itself otherwise. The connecting role must hold the privileges of the role named, which a superuser always does. The role must exist, whatever fail_on_role says. With state=absent it selects which grant of the pair is revoked. Requires PostgreSQL 16 or later.
granted_by_any boolean	Manage every grant of this membership, whoever made it; see the top-level granted_by_any, whose value is the default for a row that names no `granted_by`. Cannot be combined with `granted_by` or the option keys, which describe one grant. Choices: `false` `true`
groups list / elements=string / required	The list of groups (roles) granted to or revoked from the target roles of this membership.
inherit_option boolean	Controls the membership option `INHERIT` for this membership. Requires PostgreSQL 16 or later. Choices: `false` `true`
set_option boolean	Controls the membership option `SET` for this membership. Requires PostgreSQL 16 or later. Choices: `false` `true`
target_roles list / elements=string	The list of target roles of this membership. Replaces the top-level target_roles for this membership rather than adding to it. Falls back to it when not named.
session_role string	Switch to session_role after connecting. The specified session_role must be a role that the current login_user is a member of. Permissions checking for SQL commands is carried out as though the session_role were the one that had logged in originally.
ssl_cert path added in community.postgresql 2.4.0	Specifies the file name of the client SSL certificate.
ssl_key path added in community.postgresql 2.4.0	Specifies the location for the secret key used for the client certificate.
ssl_mode string	Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server. See https://www.postgresql.org/docs/current/static/libpq-ssl.html for more information on the modes. Default of `prefer` matches libpq default. Choices: `"allow"` `"disable"` `"prefer"` ← (default) `"require"` `"verify-ca"` `"verify-full"`
state string	Membership state. state=present implies the groupsmust be granted to target_roles. state=absent implies the groups must be revoked from target_roles. state=exact leaves the target roles members of the named groups and of no other group, revoking every other membership they hold. Roles not named in target_roles are never touched (available since community.postgresql 2.2.0). On PostgreSQL 16 and later a membership can be held through several grants, one per granting role, and a task can only revoke the grant recorded under its own granting role (see granted_by). With memberships, state=absent and state=exact therefore remove a membership only when this task’s granting role made it, unless granted_by_any is set. A grant made by another role stays, the target role stays a member, and the module warns. To remove such a grant, name its granting role in granted_by in a state=absent task, connected as a role that holds that role’s privileges, or set granted_by_any. With memberships and state=exact, a target role keeps every group any row names for it; the groups no row names are revoked under the granting role derived from the connection, since no row names one for them. granted_by_any, and the deprecated groups, revoke every grant of a membership, whoever made it, so with them state=absent and state=exact always remove the membership. Choices: `"absent"` `"exact"` `"present"` ← (default)
target_roles aliases: target_role, users, user list / elements=string	The list of target roles (groups will be granted to them). With memberships it is the default for the rows that do not name their own; a row naming its own replaces it. Required with the deprecated groups.
trust_input boolean added in community.postgresql 0.2.0	If `false`, check whether values of parameters groups, target_roles, session_role, granted_by are potentially dangerous. It makes sense to use `false` only when SQL injections via the parameters are possible. Choices: `false` `true` ← (default)

Attributes 

Attribute	Support	Description
check_mode	Support: full	Can run in check_mode and return changed status prediction without modifying target.
idempotent	Support: full	When run twice in a row outside check mode, with the same arguments, the second invocation indicates no change. This assumes that the system controlled/queried by the module has not changed in a relevant way.

Notes 

Note

On PostgreSQL 16 and later, the same membership can be granted independently by several roles, each grant carrying its own options. memberships therefore identifies a membership by the group, the target role and the granting role, and manages one of those grants. The deprecated groups names neither a granting role nor an option, so it identifies a membership by the group and the target role alone. It is present when any role has granted it, its GRANT leaves the choice of granting role to PostgreSQL, and state=absent and state=exact revoke every grant of the pair, one REVOKE per granting role. The connecting role must hold the privileges of each of those granting roles; the module checks that before revoking anything and fails naming the role it lacks.
The connecting role is session_role when set, and login_user otherwise.
PostgreSQL chooses the granting role itself when a statement does not name one. It is the bootstrap superuser when the connecting role is a superuser, otherwise the connecting role itself when it holds ADMIN OPTION on the group, or else the nearest role it inherits that does. memberships always names one; see granted_by.
When the connecting role is not a superuser, a memberships row names that role itself. If it holds ADMIN OPTION on a group only indirectly, through another role, PostgreSQL refuses a grant naming it, and granted_by must name the role that holds the option. The deprecated groups needs nothing in that case, because PostgreSQL picks the role that holds it.
Under memberships the module checks, before emitting a GRANT, that the granting role holds ADMIN OPTION on the group, and fails naming the roles that do. Only the groups a GRANT is emitted for are checked, so a task with nothing left to do keeps succeeding even after the granting role lost the option. The deprecated groups leaves the check to the server.
A membership granted by another role does not count as the module’s own, unless granted_by_any is set. state=present makes its own grant beside it. state=absent and state=exact leave it in place and warn. Set granted_by to that role to manage its grant; the connecting role must hold that role’s privileges, which a superuser always does.
Before PostgreSQL 16 a grant whose granting role has since been dropped is revoked like any other grant. PostgreSQL 16 and later refuse to drop a role that a grant records as its granting role, so the case does not arise there.
PostgreSQL applies membership options as the union of all grants of a pair, so a target role keeps an option as long as any grant carries it. Setting for example admin_option=false only clears it on the grant this module manages. The effective_options return value reports what the target role actually holds.
Before PostgreSQL 16 a pair can be granted only once and carries no per-grant options, so the two forms behave the same. granted_by and the option keys are refused there.
Names are stripped of surrounding whitespace, and a name repeated in a list counts once.
The default authentication assumes that you are either logging in as or sudo’ing to the postgres account on the host.
To avoid “Peer authentication failed for user postgres” error, use postgres user as a become_user.
This module uses psycopg, a Python PostgreSQL database adapter. You must ensure that psycopg2 >= 2.5.1 or psycopg3 >= 3.1.8 is installed on the host before using this module.
If the remote host is the PostgreSQL server (which is the default case), then PostgreSQL must also be installed on the remote host.
For Ubuntu-based systems, install the postgresql, libpq-dev, and python3-psycopg2 packages on the remote host before using this module.

Examples 

- name: Grant role read_only to alice and bob
  community.postgresql.postgresql_membership:
    target_roles:
    - alice
    - bob
    memberships:
    - groups: read_only
    state: present

- name: Revoke role read_only and exec_func from bob. Ignore if roles don't exist
  community.postgresql.postgresql_membership:
    target_role: bob
    memberships:
    - groups:
      - read_only
      - exec_func
    fail_on_role: false
    state: absent

- name: >
    Make sure alice and bob are members only of marketing and sales.
    They are removed from any other group this task's granting role granted them;
    a grant made by another role is warned about instead (see the notes)
  community.postgresql.postgresql_membership:
    target_roles:
    - alice
    - bob
    memberships:
    - groups:
      - marketing
      - sales
    state: exact

- name: Make sure alice and bob do not belong to any groups
  community.postgresql.postgresql_membership:
    target_roles:
    - alice
    - bob
    memberships: []
    state: exact

- name: Grant read_write to alice with SET and INHERIT but without ADMIN (PostgreSQL 16+)
  community.postgresql.postgresql_membership:
    target_role: alice
    memberships:
    - groups: read_write
      admin_option: false
      inherit_option: true
      set_option: true
    state: present

# Needed when the connecting role holds ADMIN OPTION on read_write only through
# dba_team rather than directly.
- name: Grant read_write to alice as dba_team (PostgreSQL 16+)
  community.postgresql.postgresql_membership:
    target_role: alice
    memberships:
    - groups: read_write
      granted_by: dba_team
    state: present

# Every grant of the membership goes, whoever made it, as the deprecated groups
# option did. Needs the privileges of every role that granted it.
- name: Make sure bob is not a member of read_write at all
  community.postgresql.postgresql_membership:
    target_role: bob
    granted_by_any: true
    memberships:
    - groups: read_write
    state: absent

- name: Remove the read_write membership dba_team granted, leaving other grants alone (PostgreSQL 16+)
  community.postgresql.postgresql_membership:
    target_role: alice
    memberships:
    - groups: read_write
      granted_by: dba_team
    state: absent

# One task, one transaction. The granting role and the options differ per group,
# which one set of top-level parameters could not express.
- name: Grant two groups whose ADMIN OPTION is held by different roles (PostgreSQL 16+)
  community.postgresql.postgresql_membership:
    target_roles:
    - alice
    - bob
    memberships:
    - groups: read_only
      granted_by: app_team
      admin_option: false
    - groups: read_write
      granted_by: dba_team
      admin_option: false
      set_option: false
    state: present

# alice is left a member of reporting and read_only, even though no single row
# names both. Any other membership granted by the connecting role is revoked;
# one granted by another role survives with a warning.
- name: Make alice a member of exactly these groups (PostgreSQL 16+)
  community.postgresql.postgresql_membership:
    target_roles: alice
    memberships:
    - groups: reporting
      granted_by: app_team
    - groups: read_only
      granted_by: dba_team
    state: exact

Return Values 

Common return values are documented here, the following are the fields unique to this module:

Key	Description
effective_options dictionary added in community.postgresql 5.0.0	Membership options the target role effectively holds for a group, keyed by group and then by target role. PostgreSQL applies membership options as the union of every grant of the pair, so an option is `true` when any grant carries it, including a grant this module does not manage. Only pairs where the target role is a member are reported. The `inherit_option` and `set_option` keys are only present on PostgreSQL 16 and later. In check mode this describes the state the run would produce, because it is read before the transaction is rolled back. Returned: success Sample: `{"ro_group": {"alice": {"admin_option": false, "inherit_option": true, "set_option": true}}}`
granted dictionary	Dict of granted groups and roles. Contains an entry for every requested group that exists, empty when nothing was granted for it. Returned: if state=present or state=exact Sample: `{"ro_group": ["alice", "bob"]}`
grants dictionary added in community.postgresql 5.0.0	Every grant of the requested groups to the requested target roles, keyed by group and then by target role, with one entry per granting role. Includes the grants made by roles other than the connecting one, which this module does not manage. Only pairs where the target role is a member are reported. The `inherit_option` and `set_option` keys are only present on PostgreSQL 16 and later. `grantor` is `null` for a grant whose granting role has since been dropped, which PostgreSQL only allows before version 16. In check mode this describes the state the run would produce, because it is read before the transaction is rolled back. Returned: success Sample: `{"ro_group": {"alice": [{"admin_option": false, "grantor": "postgres", "inherit_option": true, "set_option": true}]}}`
groups list / elements=string	Groups the task named, in order of first appearance, without those found not to exist. With memberships it spans every row. Returned: success Sample: `["ro_group"]`
queries list / elements=string	List of executed queries. With memberships on PostgreSQL 16 and later, each statement names the granting role, as in the sample. With the deprecated groups, `GRANT` names none and `REVOKE` names the granting role of each grant it removes. Before 16 there is no `GRANTED BY` clause. Returned: success Sample: `["GRANT \"user_ro\" TO \"alice\" GRANTED BY \"postgres\""]`
revoked dictionary	Dict of revoked groups and roles. With state=absent it contains an entry for every requested group that exists, empty when nothing was revoked for it. With state=exact only the groups actually revoked appear, because those are discovered on the server rather than requested. Returned: if state=absent or state=exact Sample: `{"ro_group": ["alice", "bob"]}`
state string	Membership state that tried to be set. Returned: success Sample: `"present"`
target_roles list / elements=string	Target roles the task named, in order of first appearance, without those found not to exist. With memberships it spans every row. Returned: success Sample: `["alice", "bob"]`

Authors

Andrew Klychkov (@Andersson007)