Try SpotterCreate, alter, or remove a user (role) from a PostgreSQL server instance
Authors: Ansible Core Team
Install with ansible-galaxy collection install community.postgresql:==3.4.0
collections:
- name: community.postgresql
version: 3.4.0Creates, 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(false) 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.
B(WARNING) The I(priv) option has been B(deprecated) and will be removed in community.postgresql 4.0.0. Please use the M(community.postgresql.postgresql_privs) module instead.
# This example uses the 'priv' argument which is deprecated.
# You should use the 'postgresql_privs' module instead.
- name: Connect to acme database, create django user, and grant access to database and products table
community.postgresql.postgresql_user:
db: acme
name: django
password: ceec4eif7ya
priv: "CONNECT/products:ALL"
expires: "Jan 31 2020"
- name: Add a comment on django user
community.postgresql.postgresql_user:
db: acme
name: django
comment: This is a test user
# Connect to default database, create rails user, set its password (MD5- or SHA256-hashed),
# and grant privilege to create other databases and demote rails from super user status if user exists
# the hash from the corresponding pg_authid entry.
- name: Create rails user, set MD5-hashed password, grant privs
community.postgresql.postgresql_user:
name: rails
password: md59543f1d82624df2b31672ec0f7050460
# password: SCRAM-SHA-256$4096:zFuajwIVdli9mK=NJkcv1Q++$JC4gWIrEHmF6sqRbEiZw5FFW45HUPrpVzNdoM72o730+;fqA4vLN3mCZGbhcbQyvNYY7anCrUTsem1eCh/4YA94=
role_attr_flags: CREATEDB,NOSUPERUSER
# When using sha256-hashed password:
#environment:
# PGOPTIONS: "-c password_encryption=scram-sha-256"
# This example uses the 'priv' argument which is deprecated.
# You should use the 'postgresql_privs' module instead.
- name: Connect to acme database and remove test user privileges from there
community.postgresql.postgresql_user:
db: acme
name: test
priv: "ALL/products:ALL"
state: absent
fail_on_user: false
# This example uses the 'priv' argument which is deprecated.
# You should use the 'postgresql_privs' module instead.
- name: Connect to test database, remove test user from cluster
community.postgresql.postgresql_user:
db: test
name: test
priv: ALL
state: absent
# This example uses the 'priv' argument which is deprecated.
# You should use the 'postgresql_privs' module instead.
- name: Connect to acme database and set user's password with no expire date
community.postgresql.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.postgresql.postgresql_user:
db: test
user: test
password: ""
# 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.postgresql.postgresql_user:
name: appclient
password: "secret123"
environment:
PGOPTIONS: "-c password_encryption=scram-sha-256"
# This example uses the 'priv' argument which is deprecated.
# You should use the 'postgresql_privs' module instead.
- name: Create a user, grant SELECT on pg_catalog.pg_stat_database
community.postgresql.postgresql_user:
name: monitoring
priv: 'pg_catalog.pg_stat_database:SELECT'
db:
aliases:
- login_db
default: ''
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:
- This option has been B(deprecated) and will be removed in community.postgresql 4.0.0.
Please use the M(community.postgresql.postgresql_privs) module to GRANT/REVOKE permissions
instead.
- '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).'
- When I(priv) contains tables, the module uses the schema C(public) by default. If
you need to specify a different schema, use the C(schema_name.table_name) notation,
for example, C(pg_catalog.pg_stat_database:SELECT).
type: str
state:
choices:
- absent
- present
default: present
description:
- The user (role) state.
type: str
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).
- To reset the comment, pass an empty string.
type: str
version_added: 0.2.0
version_added_collection: community.postgresql
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
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
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_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 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=true) 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(false) by default.
type: bool
conn_limit:
description:
- Specifies the user (role) connection limit.
type: int
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), checks whether values of options I(name), I(password), I(privs), I(expires),
I(role_attr_flags), I(comment), I(session_role) are potentially dangerous.
- It makes sense to use C(false) only when SQL injections through the options are
possible.
type: bool
version_added: 0.2.0
version_added_collection: community.postgresql
fail_on_user:
aliases:
- fail_on_role
default: true
description:
- If C(true), 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
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
role_attr_flags:
default: ''
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.
- See the full list of supported flags in documentation for your PostgreSQL version.
type: str
login_unix_socket:
aliases:
- unix_socket
default: ''
description:
- Path to a Unix domain socket for local connections.
type: str
no_password_changes:
default: false
description:
- If C(true), 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: success sample: - CREATE USER "alice" - GRANT CONNECT ON DATABASE "acme" TO "alice" type: list