Try SpotterChange an owner of PostgreSQL database object
Authors: Andrew Klychkov (@Andersson007), Daniele Giudice (@RealGreenDragon)
Install with ansible-galaxy collection install community.postgresql:==3.4.0
collections:
- name: community.postgresql
version: 3.4.0Change an owner of PostgreSQL database object.
Also allows to reassign the ownership of database objects owned by a database role to another role.
# Set owner as alice for function myfunc in database bar by ansible ad-hoc command:
# ansible -m postgresql_owner -a "db=bar new_owner=alice obj_name=myfunc obj_type=function"
- name: The same as above by playbook
community.postgresql.postgresql_owner:
db: bar
new_owner: alice
obj_name: myfunc
obj_type: function
- name: Set owner as bob for table acme in database bar
community.postgresql.postgresql_owner:
db: bar
new_owner: bob
obj_name: acme
obj_type: table
- name: Set owner as alice for view test_view in database bar
community.postgresql.postgresql_owner:
db: bar
new_owner: alice
obj_name: test_view
obj_type: view
- name: Set owner as bob for tablespace ssd in database foo
community.postgresql.postgresql_owner:
db: foo
new_owner: bob
obj_name: ssd
obj_type: tablespace
- name: Reassign all databases owned by bob to alice and all objects in database bar owned by bob to alice
community.postgresql.postgresql_owner:
db: bar
new_owner: alice
reassign_owned_by: bob
- name: Reassign all databases owned by bob or bill to alice and all objects in database bar owned by bob or bill to alice
community.postgresql.postgresql_owner:
db: bar
new_owner: alice
reassign_owned_by:
- bob
- bill
db:
aliases:
- login_db
description:
- Name of database to connect to.
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
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
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
obj_name:
description:
- Name of a database object to change ownership.
- Mutually exclusive with I(reassign_owned_by).
type: str
obj_type:
aliases:
- type
choices:
- aggregate
- collation
- conversion
- database
- domain
- event_trigger
- foreign_data_wrapper
- foreign_table
- function
- language
- large_object
- matview
- procedure
- publication
- routine
- schema
- sequence
- server
- statistics
- table
- tablespace
- text_search_configuration
- text_search_dictionary
- type
- view
description:
- Type of a database object.
- Mutually exclusive with I(reassign_owned_by).
- I(obj_type=matview) is available since PostgreSQL 9.3.
- I(obj_type=event_trigger), I(obj_type=procedure), I(obj_type=publication), I(obj_type=statistics),
and I(obj_type=routine) are available since PostgreSQL 11.
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 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
new_owner:
description:
- Role (user/group) to set as an I(obj_name) owner.
required: true
type: str
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), check whether values of parameters I(new_owner), I(obj_name), I(reassign_owned_by),
I(session_role) are potentially dangerous.
- It makes sense to use C(false) only when SQL injections via the parameters are possible.
type: bool
version_added: 0.2.0
version_added_collection: community.postgresql
fail_on_role:
default: true
description:
- If C(true), fail when I(reassign_owned_by) role does not exist. Otherwise just warn
and continue.
- Mutually exclusive with I(obj_name) and I(obj_type).
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
login_unix_socket:
aliases:
- unix_socket
default: ''
description:
- Path to a Unix domain socket for local connections.
type: str
reassign_owned_by:
description:
- Caution - the ownership of all the objects within the specified I(db), owned by
this role(s) will be reassigned to I(new_owner).
- REASSIGN OWNED is often used to prepare for the removal of one or more roles.
- REASSIGN OWNED does not affect objects within other databases.
- Execute this command in each database that contains objects owned by a role that
is to be removed.
- If role(s) exists, always returns changed True.
- Cannot reassign ownership of objects that are required by the database system.
- Mutually exclusive with C(obj_type).
elements: str
type: list
queries: description: List of executed queries. returned: success sample: - REASSIGN OWNED BY "bob" TO "alice" type: str