community / community.general / 1.3.11 / module / postgresql_user Create, alter, or remove a user (role) from a PostgreSQL server instance Authors: Ansible Core Teamcommunity.general.postgresql_user (1.3.11) — module
Install with ansible-galaxy collection install community.general:==1.3.11
collections: - name: community.general version: 1.3.11
Creates, 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