Try SpotterCreate, alter, or remove a user (role) from a PostgreSQL server instance
Authors: Ansible Core Team
Install with ansible-galaxy collection install community.general:==1.3.14
collections:
- name: community.general
version: 1.3.14Creates, alters, or removes a user (role) from a PostgreSQL server instance ("cluster" in PostgreSQL terminology) and, optionally, grants the user access to an existing database or tables.
A user is a role with login privilege.
You can also use it to grant or revoke user's privileges in a particular database.
You cannot remove a user while it still has any privileges granted to it in any database.
Set I(fail_on_user) to C(no) to make the module ignore failures when trying to remove a user. In this case, the module reports if changes happened as usual and separately reports whether the user has been removed or not.
- name: Connect to acme database, create django user, and grant access to database and products table
community.general.postgresql_user:
db: acme
name: django
password: ceec4eif7ya
priv: "CONNECT/products:ALL"
expires: "Jan 31 2020"
- name: Add a comment on django user
community.general.postgresql_user:
db: acme
name: django
comment: This is a test user
# Connect to default database, create rails user, set its password (MD5-hashed),
# and grant privilege to create other databases and demote rails from super user status if user exists
- name: Create rails user, set MD5-hashed password, grant privs
community.general.postgresql_user:
name: rails
password: md59543f1d82624df2b31672ec0f7050460
role_attr_flags: CREATEDB,NOSUPERUSER
- name: Connect to acme database and remove test user privileges from there
community.general.postgresql_user:
db: acme
name: test
priv: "ALL/products:ALL"
state: absent
fail_on_user: no
- name: Connect to test database, remove test user from cluster
community.general.postgresql_user:
db: test
name: test
priv: ALL
state: absent
- name: Connect to acme database and set user's password with no expire date
community.general.postgresql_user:
db: acme
name: django
password: mysupersecretword
priv: "CONNECT/products:ALL"
expires: infinity
# Example privileges string format
# INSERT,UPDATE/table:SELECT/anothertable:ALL
- name: Connect to test database and remove an existing user's password
community.general.postgresql_user:
db: test
user: test
password: ""
- name: Create user test and grant group user_ro and user_rw to it
community.general.postgresql_user:
name: test
groups:
- user_ro
- user_rw
# Create user with a cleartext password if it does not exist or update its password.
# The password will be encrypted with SCRAM algorithm (available since PostgreSQL 10)
- name: Create appclient user with SCRAM-hashed password
community.general.postgresql_user:
name: appclient
password: "secret123"
environment:
PGOPTIONS: "-c password_encryption=scram-sha-256"
db:
aliases:
- login_db
description:
- Name of database to connect to and where user's permissions are granted.
type: str
name:
aliases:
- user
description:
- Name of the user (role) to add or remove.
required: true
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
priv:
description:
- 'Slash-separated PostgreSQL privileges string: C(priv1/priv2), where you can define
the user''s privileges for the database ( allowed options - ''CREATE'', ''CONNECT'',
''TEMPORARY'', ''TEMP'', ''ALL''. For example C(CONNECT) ) or for table ( allowed
options - ''SELECT'', ''INSERT'', ''UPDATE'', ''DELETE'', ''TRUNCATE'', ''REFERENCES'',
''TRIGGER'', ''ALL''. For example C(table:SELECT) ). Mixed example of this string:
C(CONNECT/CREATE/table1:SELECT/table2:INSERT).'
type: str
state:
choices:
- absent
- present
default: present
description:
- The user (role) state.
type: str
groups:
description:
- The list of groups (roles) that you want to grant to the user.
elements: str
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, verifies that the server's certificate is signed by one of these
authorities.
type: str
comment:
description:
- Adds a comment on the user (equivalent to the C(COMMENT ON ROLE) statement).
type: str
version_added: 0.2.0
version_added_collection: community.general
expires:
description:
- The date at which the user's password is to expire.
- If set to C('infinity'), user's password never expires.
- Note that this value must be a valid SQL date and time type.
type: str
password:
description:
- Set the user's password, before 1.4 this was required.
- Password can be passed unhashed or hashed (MD5-hashed).
- An unhashed password is automatically hashed when saved into the database if I(encrypted)
is set, otherwise it is saved in plain text format.
- When passing an MD5-hashed password, you must generate it with the format C('str["md5"]
+ md5[ password + username ]'), resulting in a total of 35 characters. An easy way
to do this is C(echo "md5`echo -n 'verysecretpasswordJOE' | md5sum | awk '{print
$1}'`").
- Note that if the provided password string is already in MD5-hashed format, then
it is used as-is, regardless of I(encrypted) option.
type: str
ssl_mode:
choices:
- allow
- disable
- prefer
- require
- verify-ca
- verify-full
default: prefer
description:
- Determines how an SSL session is 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
encrypted:
default: true
description:
- Whether the password is stored hashed in the database.
- You can specify an unhashed password, and PostgreSQL ensures the stored password
is hashed when I(encrypted=yes) is set. If you specify a hashed password, the module
uses it as-is, regardless of the setting of I(encrypted).
- 'Note: Postgresql 10 and newer does not support unhashed passwords.'
- Previous to Ansible 2.6, this was C(no) by default.
type: bool
conn_limit:
description:
- Specifies the user (role) connection limit.
type: int
login_host:
description:
- Host running the database.
type: str
login_user:
default: postgres
description:
- The username used to authenticate with.
type: str
trust_input:
default: true
description:
- If C(no), checks whether values of options I(name), I(password), I(privs), I(expires),
I(role_attr_flags), I(groups), I(comment), I(session_role) are potentially dangerous.
- It makes sense to use C(no) only when SQL injections through the options are possible.
type: bool
version_added: 0.2.0
version_added_collection: community.general
fail_on_user:
aliases:
- fail_on_role
default: true
description:
- If C(yes), fails when the user (role) cannot be removed. Otherwise just log 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
login_password:
description:
- The password used to authenticate with.
type: str
role_attr_flags:
choices:
- '[NO]SUPERUSER'
- '[NO]CREATEROLE'
- '[NO]CREATEDB'
- '[NO]INHERIT'
- '[NO]LOGIN'
- '[NO]REPLICATION'
- '[NO]BYPASSRLS'
description:
- 'PostgreSQL user attributes string in the format: CREATEDB,CREATEROLE,SUPERUSER.'
- Note that '[NO]CREATEUSER' is deprecated.
- To create a simple role for using it like a group, use C(NOLOGIN) flag.
type: str
login_unix_socket:
description:
- Path to a Unix domain socket for local connections.
type: str
no_password_changes:
default: false
description:
- If C(yes), does not inspect the database for password changes. If the user already
exists, skips all password related checks. Useful when C(pg_authid) is not accessible
(such as in AWS RDS). Otherwise, makes password changes as necessary.
type: bool
queries: description: List of executed queries. returned: always sample: - CREATE USER "alice" - GRANT CONNECT ON DATABASE "acme" TO "alice" type: list