Try SpotterAdd or remove PostgreSQL roles from groups
Authors: Andrew Klychkov (@Andersson007)
Install with ansible-galaxy collection install community.postgresql:==3.4.0
collections:
- name: community.postgresql
version: 3.4.0Adds or removes PostgreSQL roles from groups (other roles).
Users are roles with login privilege.
Groups are PostgreSQL roles usually without LOGIN privilege.
Common use case:
1) add a new group (groups) by M(community.postgresql.postgresql_user) module with I(role_attr_flags=NOLOGIN)
2) grant them desired privileges by M(community.postgresql.postgresql_privs) module
3) add desired PostgreSQL users to the new group (groups) by this module
- name: Grant role read_only to alice and bob
community.postgresql.postgresql_membership:
group: read_only
target_roles:
- alice
- bob
state: present
# you can also use target_roles: alice,bob,etc to pass the role list
- name: Revoke role read_only and exec_func from bob. Ignore if roles don't exist
community.postgresql.postgresql_membership:
groups:
- read_only
- exec_func
target_role: bob
fail_on_role: false
state: absent
- name: >
Make sure alice and bob are members only of marketing and sales.
If they are members of other groups, they will be removed from those groups
community.postgresql.postgresql_membership:
group:
- marketing
- sales
target_roles:
- alice
- bob
state: exact
- name: Make sure alice and bob do not belong to any groups
community.postgresql.postgresql_membership:
group: []
target_roles:
- alice
- bob
state: exact
db:
aliases:
- login_db
description:
- Name of database to connect to.
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
state:
choices:
- absent
- exact
- present
default: present
description:
- Membership state.
- I(state=present) implies the I(groups)must be granted to I(target_roles).
- I(state=absent) implies the I(groups) must be revoked from I(target_roles).
- I(state=exact) implies that I(target_roles) will be members of only the I(groups)
(available since community.postgresql 2.2.0). Any other groups will be revoked from
I(target_roles).
type: str
groups:
aliases:
- group
- source_role
- source_roles
description:
- The list of groups (roles) that need to be granted to or revoked from I(target_roles).
elements: str
required: true
type: list
ca_cert:
aliases:
- ssl_rootcert
description:
- 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.
type: str
ssl_key:
description:
- Specifies the location for the secret key used for the client certificate.
type: path
version_added: 2.4.0
version_added_collection: community.postgresql
ssl_cert:
description:
- Specifies the file name of the client SSL certificate.
type: path
version_added: 2.4.0
version_added_collection: community.postgresql
ssl_mode:
choices:
- allow
- disable
- prefer
- require
- verify-ca
- verify-full
default: prefer
description:
- Determines whether or with what priority a secure SSL TCP/IP connection will be
negotiated with the server.
- See U(https://www.postgresql.org/docs/current/static/libpq-ssl.html) for more information
on the modes.
- Default of C(prefer) matches libpq default.
type: str
login_host:
aliases:
- host
default: ''
description:
- Host running the database.
- If you have connection issues when using C(localhost), try to use C(127.0.0.1) instead.
type: str
login_user:
aliases:
- login
default: postgres
description:
- The username this module should use to establish its PostgreSQL session.
type: str
trust_input:
default: true
description:
- If C(false), check whether values of parameters I(groups), I(target_roles), I(session_role)
are potentially dangerous.
- It makes sense to use C(false) only when SQL injections via the parameters are possible.
type: bool
version_added: 0.2.0
version_added_collection: community.postgresql
fail_on_role:
default: true
description:
- If C(true), fail when group or target_role doesn't exist. If C(false), just warn
and continue.
type: bool
session_role:
description:
- 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.
type: str
target_roles:
aliases:
- target_role
- users
- user
description:
- The list of target roles (groups will be granted to them).
elements: str
required: true
type: list
connect_params:
default: {}
description:
- Any additional parameters to be passed to libpg.
- These parameters take precedence.
type: dict
version_added: 2.3.0
version_added_collection: community.postgresql
login_password:
default: ''
description:
- The password this module should use to establish its PostgreSQL session.
type: str
login_unix_socket:
aliases:
- unix_socket
default: ''
description:
- Path to a Unix domain socket for local connections.
type: str
granted:
description: Dict of granted groups and roles.
returned: if I(state=present)
sample:
ro_group:
- alice
- bob
type: dict
queries:
description: List of executed queries.
returned: success
sample:
- GRANT "user_ro" TO "alice"
type: str
revoked:
description: Dict of revoked groups and roles.
returned: if I(state=absent)
sample:
ro_group:
- alice
- bob
type: dict
state:
description: Membership state that tried to be set.
returned: success
sample: present
type: str