community / community.postgresql / 3.4.0 / module / postgresql_privs Grant or revoke privileges on PostgreSQL database objects Authors: Bernhard Weitzhofer (@b6d), Tobias Birkefeld (@tcraxs), Daniele Giudice (@RealGreenDragon)community.postgresql.postgresql_privs (3.4.0) — module
Install with ansible-galaxy collection install community.postgresql:==3.4.0
collections: - name: community.postgresql version: 3.4.0
Grant 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