Try SpotterAdds, removes or changes procedural languages with a PostgreSQL database
Authors: Jens Depuydt (@jensdepuydt), Thomas O'Donnell (@andytom)
Install with ansible-galaxy collection install community.general:==1.3.14
collections:
- name: community.general
version: 1.3.14Adds, removes or changes procedural languages with a PostgreSQL database.
This module allows you to add a language, remote a language or change the trust relationship with a PostgreSQL database.
The module can be used on the machine where executed or on a remote host.
When removing a language from a database, it is possible that dependencies prevent the database from being removed. In that case, you can specify I(cascade=yes) to automatically drop objects that depend on the language (such as functions in the language).
In case the language can't be deleted because it is required by the database system, you can specify I(fail_on_drop=no) to ignore the error.
Be careful when marking a language as trusted since this could be a potential security breach. Untrusted languages allow only users with the PostgreSQL superuser privilege to use this language to create new functions.
- name: Add language pltclu to database testdb if it doesn't exist community.general.postgresql_lang: db=testdb lang=pltclu state=present
# Add language pltclu to database testdb if it doesn't exist and mark it as trusted.
# Marks the language as trusted if it exists but isn't trusted yet.
# force_trust makes sure that the language will be marked as trusted
- name: Add language pltclu to database testdb if it doesn't exist and mark it as trusted
community.general.postgresql_lang:
db: testdb
lang: pltclu
state: present
trust: yes
force_trust: yes
- name: Remove language pltclu from database testdb
community.general.postgresql_lang:
db: testdb
lang: pltclu
state: absent
- name: Remove language pltclu from database testdb and remove all dependencies
community.general.postgresql_lang:
db: testdb
lang: pltclu
state: absent
cascade: yes
- name: Remove language c from database testdb but ignore errors if something prevents the removal
community.general.postgresql_lang:
db: testdb
lang: pltclu
state: absent
fail_on_drop: no
- name: In testdb change owner of mylang to alice
community.general.postgresql_lang:
db: testdb
lang: mylang
owner: alice
db:
aliases:
- login_db
description:
- Name of database to connect to and where the language will be added, removed or
changed.
required: true
type: str
lang:
aliases:
- name
description:
- Name of the procedural language to add, remove or change.
required: true
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
owner:
description:
- Set an owner for the language.
- Ignored when I(state=absent).
type: str
version_added: 0.2.0
version_added_collection: community.general
state:
choices:
- absent
- present
default: present
description:
- The state of the language for the selected database.
type: str
trust:
default: 'no'
description:
- Make this language trusted for the selected db.
type: bool
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
cascade:
default: 'no'
description:
- When dropping a language, also delete object that depend on this language.
- Only used when I(state=absent).
type: bool
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:
description:
- Host running the database.
type: str
login_user:
default: postgres
description:
- The username used to authenticate with.
type: str
force_trust:
default: 'no'
description:
- Marks the language as trusted, even if it's marked as untrusted in pg_pltemplate.
- Use with care!
type: bool
trust_input:
default: true
description:
- If C(no), check whether values of parameters I(lang), I(session_role), I(owner)
are potentially dangerous.
- It makes sense to use C(no) only when SQL injections via the parameters are possible.
type: bool
version_added: 0.2.0
version_added_collection: community.general
fail_on_drop:
default: 'yes'
description:
- If C(yes), fail when removing a language. Otherwise just log and continue.
- In some cases, it is not possible to remove a language (used by the db-system).
- When dependencies block the removal, consider using I(cascade).
type: bool
session_role:
description:
- Switch to session_role after connecting.
- The specified I(session_role) must be a role that the current I(login_user) is a
member of.
- Permissions checking for SQL commands is carried out as though the I(session_role)
were the one that had logged in originally.
type: str
login_password:
description:
- The password used to authenticate with.
type: str
login_unix_socket:
description:
- Path to a Unix domain socket for local connections.
type: str
queries: description: List of executed queries. returned: always sample: - CREATE LANGUAGE "acme" type: list