Try SpotterGrant or revoke privileges on PostgreSQL database objects
Authors: Bernhard Weitzhofer (@b6d), Tobias Birkefeld (@tcraxs), Daniele Giudice (@RealGreenDragon)
Install with ansible-galaxy collection install community.postgresql:==3.4.0
collections:
- name: community.postgresql
version: 3.4.0Grant or revoke privileges on PostgreSQL database objects.
This module is basically a wrapper around most of the functionality of PostgreSQL's GRANT and REVOKE statements with detection of changes (GRANT/REVOKE I(privs) ON I(type) I(objs) TO/FROM I(roles)).
# On database "library":
# GRANT SELECT, INSERT, UPDATE ON TABLE public.books, public.authors
# TO librarian, reader WITH GRANT OPTION
- name: Grant privs to librarian and reader on database library
community.postgresql.postgresql_privs:
database: library
state: present
privs: SELECT,INSERT,UPDATE
type: table
objs: books,authors
schema: public
roles: librarian,reader
grant_option: true
- name: Same as above leveraging default values
community.postgresql.postgresql_privs:
db: library
privs: SELECT,INSERT,UPDATE
objs: books,authors
roles: librarian,reader
grant_option: true
# REVOKE GRANT OPTION FOR INSERT ON TABLE books FROM reader
# Note that role "reader" will be *granted* INSERT privilege itself if this
# isn't already the case (since state: present).
- name: Revoke privs from reader
community.postgresql.postgresql_privs:
db: library
state: present
priv: INSERT
obj: books
role: reader
grant_option: false
# "public" is the default schema. This also works for PostgreSQL 8.x.
- name: REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA public FROM reader
community.postgresql.postgresql_privs:
db: library
state: absent
privs: INSERT,UPDATE
objs: ALL_IN_SCHEMA
role: reader
- name: GRANT ALL PRIVILEGES ON SCHEMA public, math TO librarian
community.postgresql.postgresql_privs:
db: library
privs: ALL
type: schema
objs: public,math
role: librarian
# Note the separation of arguments with colons.
- name: GRANT ALL PRIVILEGES ON FUNCTION math.add(int, int) TO librarian, reader
community.postgresql.postgresql_privs:
db: library
privs: ALL
type: function
obj: add(int:int)
schema: math
roles: librarian,reader
# Note that group role memberships apply cluster-wide and therefore are not
# restricted to database "library" here.
- name: GRANT librarian, reader TO alice, bob WITH ADMIN OPTION
community.postgresql.postgresql_privs:
db: library
type: group
objs: librarian,reader
roles: alice,bob
admin_option: true
# Note that here "db: postgres" specifies the database to connect to, not the
# database to grant privileges on (which is specified via the "objs" param)
- name: GRANT ALL PRIVILEGES ON DATABASE library TO librarian
community.postgresql.postgresql_privs:
db: postgres
privs: ALL
type: database
obj: library
role: librarian
# If objs is omitted for type "database", it defaults to the database
# to which the connection is established
- name: GRANT ALL PRIVILEGES ON DATABASE library TO librarian
community.postgresql.postgresql_privs:
db: library
privs: ALL
type: database
role: librarian
# Available since version 2.7
# Objs must be set, ALL_DEFAULT to TABLES/SEQUENCES/TYPES/FUNCTIONS
# ALL_DEFAULT works only with privs=ALL
# For specific
- name: ALTER DEFAULT PRIVILEGES ON DATABASE library TO librarian
community.postgresql.postgresql_privs:
db: library
objs: ALL_DEFAULT
privs: ALL
type: default_privs
role: librarian
grant_option: true
# Available since version 2.7
# Objs must be set, ALL_DEFAULT to TABLES/SEQUENCES/TYPES/FUNCTIONS
# ALL_DEFAULT works only with privs=ALL
# For specific
- name: ALTER DEFAULT PRIVILEGES ON DATABASE library TO reader, step 1
community.postgresql.postgresql_privs:
db: library
objs: TABLES,SEQUENCES
privs: SELECT
type: default_privs
role: reader
- name: ALTER DEFAULT PRIVILEGES ON DATABASE library TO reader, step 2
community.postgresql.postgresql_privs:
db: library
objs: TYPES
privs: USAGE
type: default_privs
role: reader
# Available since version 2.8
- name: GRANT ALL PRIVILEGES ON FOREIGN DATA WRAPPER fdw TO reader
community.postgresql.postgresql_privs:
db: test
objs: fdw
privs: ALL
type: foreign_data_wrapper
role: reader
# Available since community.postgresql 0.2.0
- name: GRANT ALL PRIVILEGES ON TYPE customtype TO reader
community.postgresql.postgresql_privs:
db: test
objs: customtype
privs: ALL
type: type
role: reader
# Available since version 2.8
- name: GRANT ALL PRIVILEGES ON FOREIGN SERVER fdw_server TO reader
community.postgresql.postgresql_privs:
db: test
objs: fdw_server
privs: ALL
type: foreign_server
role: reader
# Available since version 2.8
# Grant 'execute' permissions on all functions in schema 'common' to role 'caller'
- name: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA common TO caller
community.postgresql.postgresql_privs:
type: function
state: present
privs: EXECUTE
roles: caller
objs: ALL_IN_SCHEMA
schema: common
# Available since collection version 1.3.0
# Grant 'execute' permissions on all procedures in schema 'common' to role 'caller'
# Needs PostreSQL 11 or higher and community.postgresql 1.3.0 or higher
- name: GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA common TO caller
community.postgresql.postgresql_privs:
type: procedure
state: present
privs: EXECUTE
roles: caller
objs: ALL_IN_SCHEMA
schema: common
# Available since version 2.8
# ALTER DEFAULT PRIVILEGES FOR ROLE librarian IN SCHEMA library GRANT SELECT ON TABLES TO reader
# GRANT SELECT privileges for new TABLES objects created by librarian as
# default to the role reader.
# For specific
- name: ALTER privs
community.postgresql.postgresql_privs:
db: library
schema: library
objs: TABLES
privs: SELECT
type: default_privs
role: reader
target_roles: librarian
# Available since version 2.8
# ALTER DEFAULT PRIVILEGES FOR ROLE librarian IN SCHEMA library REVOKE SELECT ON TABLES FROM reader
# REVOKE SELECT privileges for new TABLES objects created by librarian as
# default from the role reader.
# For specific
- name: ALTER privs
community.postgresql.postgresql_privs:
db: library
state: absent
schema: library
objs: TABLES
privs: SELECT
type: default_privs
role: reader
target_roles: librarian
# Available since community.postgresql 0.2.0
- name: Grant type privileges for pg_catalog.numeric type to alice
community.postgresql.postgresql_privs:
type: type
roles: alice
privs: ALL
objs: numeric
schema: pg_catalog
db: acme
- name: Alter default privileges grant usage on schemas to datascience
community.postgresql.postgresql_privs:
database: test
type: default_privs
privs: usage
objs: schemas
role: datascience
# Available since community.postgresql 3.1.0
# Needs PostgreSQL 15 or higher
- name: GRANT SET ON PARAMETER log_destination,log_line_prefix TO logtest
community.postgresql.postgresql_privs:
database: logtest
state: present
privs: SET
type: parameter
objs: log_destination,log_line_prefix
roles: logtest
- name: GRANT ALTER SYSTEM ON PARAMETER primary_conninfo,synchronous_standby_names TO replicamgr
community.postgresql.postgresql_privs:
database: replicamgr
state: present
privs: ALTER_SYSTEM
type: parameter
objs: primary_conninfo,synchronous_standby_names
roles: replicamgr
objs:
aliases:
- obj
description:
- Comma separated list of database objects to set privileges on.
- If I(type) is C(table), C(partition table), C(sequence), C(function) or C(procedure),
the special value C(ALL_IN_SCHEMA) can be provided instead to specify all database
objects of I(type) in the schema specified via I(schema). (This also works with
PostgreSQL < 9.0.) (C(ALL_IN_SCHEMA) is available for C(function) and C(partition
table) since Ansible 2.8).
- C(procedure) is supported since PostgreSQL 11 and community.postgresql collection
1.3.0.
- C(parameter) is supported since PostgreSQL 15 and community.postgresql collection
3.1.0.
- If I(type) is C(database), this parameter can be omitted, in which case privileges
are set for the database specified via I(database).
- If I(type) is C(function) or C(procedure), colons (":") in object names will be
replaced with commas (needed to specify signatures, see examples).
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
type:
choices:
- database
- default_privs
- foreign_data_wrapper
- foreign_server
- function
- group
- language
- table
- tablespace
- schema
- sequence
- type
- procedure
- parameter
default: table
description:
- Type of database object to set privileges on.
- The C(default_privs) choice is available starting at version 2.7.
- The C(foreign_data_wrapper) and C(foreign_server) object types are available since
Ansible version 2.8.
- The C(type) choice is available since Ansible version 2.10.
- The C(procedure) is supported since collection version 1.3.0 and PostgreSQL 11.
- The C(parameter) is supported since collection version 3.1.0 and PostgreSQL 15.
type: str
privs:
aliases:
- priv
description:
- Comma separated list of privileges to grant/revoke.
type: str
roles:
aliases:
- role
description:
- Comma separated list of role (user/group) names to set permissions for.
- Roles C(PUBLIC), C(CURRENT_ROLE), C(CURRENT_USER), C(SESSION_USER) are implicitly
defined in PostgreSQL.
- C(CURRENT_USER) and C(SESSION_USER) implicit roles are supported since collection
version 3.1.0 and PostgreSQL 9.5.
- C(CURRENT_ROLE) implicit role is supported since collection version 3.1.0 and PostgreSQL
14.
required: true
type: str
state:
choices:
- absent
- present
default: present
description:
- If C(present), the specified privileges are granted, if C(absent) they are revoked.
type: str
schema:
description:
- Schema that contains the database objects specified via I(objs).
- May only be provided if I(type) is C(table), C(sequence), C(function), C(procedure),
C(type), or C(default_privs). Defaults to C(public) in these cases.
- Pay attention, for embedded types when I(type=type) I(schema) can be C(pg_catalog)
or C(information_schema) respectively.
- If not specified, uses C(public). Not to pass any schema, use C(not-specified).
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
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
database:
aliases:
- db
- login_db
description:
- Name of database to connect to.
required: true
type: str
password:
default: ''
description:
- The password to authenticate with.
- This option has been B(deprecated) and will be removed in community.postgresql 4.0.0,
use the I(login_password) option instead.
- Mutually exclusive with I(login_password).
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
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(roles), I(target_roles), I(session_role),
I(schema) 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 target role (for whom privs need to be granted) does not exist.
Otherwise just warn and continue.
type: bool
grant_option:
aliases:
- admin_option
description:
- Whether C(role) may grant/revoke the specified privileges/group memberships to others.
- Set to C(false) to revoke GRANT OPTION, leave unspecified to make no changes.
- I(grant_option) only has an effect if I(state) is C(present).
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
target_roles:
description:
- A list of existing role (user/group) names to set as the default permissions for
database objects subsequently created by them.
- Parameter I(target_roles) is only available with C(type=default_privs).
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
queries: description: List of executed queries. returned: success sample: - REVOKE GRANT OPTION FOR INSERT ON TABLE "books" FROM "reader"; type: list