community / community.general / 0.1.1 / module / postgresql_privs Grant or revoke privileges on PostgreSQL database objects Authors: Bernhard Weitzhofer (@b6d), Tobias Birkefeld (@tcraxs) stableinterface | supported by communitycommunity.general.postgresql_privs (0.1.1) — module
Install with ansible-galaxy collection install community.general:==0.1.1
collections: - name: community.general version: 0.1.1
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 postgresql_privs: database: library state: present privs: SELECT,INSERT,UPDATE type: table objs: books,authors schema: public roles: librarian,reader grant_option: yes
- name: Same as above leveraging default values postgresql_privs: db: library privs: SELECT,INSERT,UPDATE objs: books,authors roles: librarian,reader grant_option: yes
# 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 postgresql_privs: db: library state: present priv: INSERT obj: books role: reader grant_option: no
# "public" is the default schema. This also works for PostgreSQL 8.x. - name: REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA public FROM reader 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 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 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 postgresql_privs: db: library type: group objs: librarian,reader roles: alice,bob admin_option: yes
# 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 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 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 postgresql_privs: db: library objs: ALL_DEFAULT privs: ALL type: default_privs role: librarian grant_option: yes
# 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 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 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 postgresql_privs: db: test objs: fdw privs: ALL type: foreign_data_wrapper role: reader
# Available since version 2.10 - name: GRANT ALL PRIVILEGES ON TYPE customtype TO reader 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 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 postgresql_privs: type: function 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 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 postgresql_privs: db: library state: absent schema: library objs: TABLES privs: SELECT type: default_privs role: reader target_roles: librarian
# Available since version 2.10 - name: Grant type privileges for pg_catalog.numeric type to alice postgresql_privs: type: type roles: alice privs: ALL objs: numeric schema: pg_catalog db: acme
host: aliases: - login_host description: - Database host address. If unspecified, connect via Unix socket. type: str 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) or C(function), the special valueC(ALL_IN_SCHEMA) can be provided instead to specify all database objects of type 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) from version 2.8) - 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 I(function), colons (":") in object names will be replaced with commas (needed to specify function 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 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 from Ansible version '2.8'. - The C(type) choice is available from Ansible version '2.10'. type: str login: aliases: - login_user default: postgres description: - The username to authenticate with. 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. - The special value C(PUBLIC) can be provided instead to set permissions for the implicitly defined PUBLIC group. 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(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. 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 database: aliases: - db - login_db description: - Name of database to connect to. required: true type: str password: aliases: - login_password description: - The password to authenticate with. type: str 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 unix_socket: aliases: - login_unix_socket description: - Path to a Unix domain socket for local connections. type: str fail_on_role: default: true description: - If C(yes), 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(no) 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 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: - REVOKE GRANT OPTION FOR INSERT ON TABLE "books" FROM "reader"; type: list version_added: '2.8' version_added_collection: community.general