Try SpotterRun PostgreSQL queries
Authors: Felix Archambault (@archf), Andrew Klychkov (@Andersson007), Will Rouesnel (@wrouesnel)
Install with ansible-galaxy collection install community.general:==1.3.14
collections:
- name: community.general
version: 1.3.14Runs arbitrary PostgreSQL queries.
Can run queries from SQL script files.
Does not run against backup files. Use M(community.general.postgresql_db) with I(state=restore) to run queries on files made by pg_dump/pg_dumpall utilities.
- name: Simple select query to acme db
community.general.postgresql_query:
db: acme
query: SELECT version()
- name: Select query to db acme with positional arguments and non-default credentials
community.general.postgresql_query:
db: acme
login_user: django
login_password: mysecretpass
query: SELECT * FROM acme WHERE id = %s AND story = %s
positional_args:
- 1
- test
- name: Select query to test_db with named_args
community.general.postgresql_query:
db: test_db
query: SELECT * FROM test WHERE id = %(id_val)s AND story = %(story_val)s
named_args:
id_val: 1
story_val: test
- name: Insert query to test_table in db test_db
community.general.postgresql_query:
db: test_db
query: INSERT INTO test_table (id, story) VALUES (2, 'my_long_story')
- name: Run queries from SQL script using UTF-8 client encoding for session
community.general.postgresql_query:
db: test_db
path_to_script: /var/lib/pgsql/test.sql
positional_args:
- 1
encoding: UTF-8
- name: Example of using autocommit parameter
community.general.postgresql_query:
db: test_db
query: VACUUM
autocommit: yes
- name: >
Insert data to the column of array type using positional_args.
Note that we use quotes here, the same as for passing JSON, etc.
community.general.postgresql_query:
query: INSERT INTO test_table (array_column) VALUES (%s)
positional_args:
- '{1,2,3}'
# Pass list and string vars as positional_args
- name: Set vars
ansible.builtin.set_fact:
my_list:
- 1
- 2
- 3
my_arr: '{1, 2, 3}'
- name: Select from test table by passing positional_args as arrays
community.general.postgresql_query:
query: SELECT * FROM test_array_table WHERE arr_col1 = %s AND arr_col2 = %s
positional_args:
- '{{ my_list }}'
- '{{ my_arr|string }}'
# Select from test table looking into app1 schema first, then,
# if the schema doesn't exist or the table hasn't been found there,
# try to find it in the schema public
- name: Select from test using search_path
community.general.postgresql_query:
query: SELECT * FROM test_array_table
search_path:
- app1
- public
# If you use a variable in positional_args / named_args that can
# be undefined and you wish to set it as NULL, the constructions like
# "{{ my_var if (my_var is defined) else none | default(none) }}"
# will not work as expected substituting an empty string instead of NULL.
# If possible, we suggest to use Ansible's DEFAULT_JINJA2_NATIVE configuration
# (https://docs.ansible.com/ansible/latest/reference_appendices/config.html#default-jinja2-native).
# Enabling it fixes this problem. If you cannot enable it, the following workaround
# can be used.
# You should precheck such a value and define it as NULL when undefined.
# For example:
- name: When undefined, set to NULL
set_fact:
my_var: NULL
when: my_var is undefined
# Then:
- name: Insert a value using positional arguments
community.postgresql.postgresql_query:
query: INSERT INTO test_table (col1) VALUES (%s)
positional_args:
- '{{ my_var }}'
db:
aliases:
- login_db
description:
- Name of database to connect to and run queries against.
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
query:
description:
- SQL query to run. Variables can be escaped with psycopg2 syntax U(http://initd.org/psycopg/docs/usage.html).
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, the server's certificate will be verified to be signed by one
of these authorities.
type: str
encoding:
description:
- Set the client encoding for the current session (e.g. C(UTF-8)).
- The default is the encoding defined by the database.
type: str
version_added: 0.2.0
version_added_collection: community.general
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
autocommit:
default: false
description:
- Execute in autocommit mode when the query can't be run inside a transaction block
(e.g., VACUUM).
- Mutually exclusive with I(check_mode).
type: bool
login_host:
description:
- Host running the database.
type: str
login_user:
default: postgres
description:
- The username used to authenticate with.
type: str
named_args:
description:
- Dictionary of key-value arguments to pass to the query. When the value is a list,
it will be converted to PostgreSQL array.
- Mutually exclusive with I(positional_args).
type: dict
search_path:
description:
- List of schema names to look in.
elements: str
type: list
version_added: 1.0.0
version_added_collection: community.general
trust_input:
default: true
description:
- If C(no), check whether a value of I(session_role) is potentially dangerous.
- It makes sense to use C(no) only when SQL injections via I(session_role) are possible.
type: bool
version_added: 0.2.0
version_added_collection: community.general
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
path_to_script:
description:
- Path to a SQL script on the target machine.
- If the script contains several queries, they must be semicolon-separated.
- Mutually exclusive with I(query).
type: path
positional_args:
description:
- List of values to be passed as positional arguments to the query. When the value
is a list, it will be converted to PostgreSQL array.
- Mutually exclusive with I(named_args).
elements: raw
type: list
login_unix_socket:
description:
- Path to a Unix domain socket for local connections.
type: str
query:
description:
- Executed query.
- When reading several queries from a file, it contains only the last one.
returned: always
sample: SELECT * FROM bar
type: str
query_all_results:
description:
- List containing results of all queries executed (one sublist for every query).
Useful when reading several queries from a file.
elements: list
returned: always
sample:
- - Column: Value1
- Column: Value2
- - Column: Value1
- Column: Value2
type: list
query_list:
description:
- List of executed queries. Useful when reading several queries from a file.
elements: str
returned: always
sample:
- SELECT * FROM foo
- SELECT * FROM bar
type: list
query_result:
description:
- List of dictionaries in column:value form representing returned rows.
- When running queries from a file, returns result of the last query.
elements: dict
returned: always
sample:
- Column: Value1
- Column: Value2
type: list
rowcount:
description:
- Number of produced or affected rows.
- When using a script with multiple queries, it contains a total number of produced
or affected rows.
returned: changed
sample: 5
type: int
statusmessage:
description:
- Attribute containing the message returned by the command.
- When reading several queries from a file, it contains a message of the last one.
returned: always
sample: INSERT 0 1
type: str