community.general.postgresql_privs – Grant or revoke privileges on PostgreSQL database objects
community.general.postgresql_privs – Grant or revoke privileges on PostgreSQL database objects
Note
This plugin is part of the community.general collection (version 1.3.2).
To install it use: ansible-galaxy collection install community.general
.
To use it in a playbook, specify: community.general.postgresql_privs
.
Synopsis
- 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 privs ON type objs TO/FROM roles).
Requirements
The below requirements are needed on the host that executes this module.
- psycopg2
Parameters
Parameter | Choices/Defaults | Comments |
---|---|---|
ca_cert string |
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.
| |
database string / required |
Name of database to connect to.
| |
fail_on_role boolean |
|
If |
grant_option boolean |
|
Whether Set to grant_option only has an effect if state is
|
host string |
Database host address. If unspecified, connect via Unix socket.
| |
login string |
Default: "postgres" |
The username to authenticate with.
|
login_host string |
Host running the database. | |
login_password string |
The password used to authenticate with. | |
login_unix_socket string |
Path to a Unix domain socket for local connections. | |
login_user string |
Default: "postgres" |
The username used to authenticate with. |
objs string |
Comma separated list of database objects to set privileges on. If type is
If type is If type is function or procedure, colons (":") in object names will be replaced with commas (needed to specify signatures, see examples).
| |
password string |
The password to authenticate with.
| |
port integer |
Default: 5432 |
Database port to connect to.
|
privs string |
Comma separated list of privileges to grant/revoke.
| |
roles string / required |
Comma separated list of role (user/group) names to set permissions for. The special value
| |
schema string |
Schema that contains the database objects specified via objs. May only be provided if type is Pay attention, for embedded types when type=type schema can be | |
session_role string |
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. | |
ssl_mode string |
|
Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server. See https://www.postgresql.org/docs/current/static/libpq-ssl.html for more information on the modes. Default of |
state string |
|
If |
target_roles string |
A list of existing role (user/group) names to set as the default permissions for database objects subsequently created by them. Parameter target_roles is only available with | |
trust_input boolean added in 0.2.0 of community.general |
|
If It makes sense to use |
type string |
|
Type of database object to set privileges on. The The The The |
unix_socket string |
Path to a Unix domain socket for local connections.
| |
usage_on_types boolean added in 1.2.0 of community.general |
|
When adding default privileges, the module always implicitly adds ``USAGE ON TYPES``. To avoid this behavior, set usage_on_types to Added to save backwards compatibility. Used only when adding default privileges, ignored otherwise. |
Notes
Note
- Parameters that accept comma separated lists (privs, objs, roles) have singular alias names (priv, obj, role).
- To revoke only
GRANT OPTION
for a specific object, set state topresent
and grant_option tono
(see examples). - Note that when revoking privileges from a role R, this role may still have access via privileges granted to any role R is a member of including
PUBLIC
. - Note that when you use
PUBLIC
role, the module always reports that the state has been changed. - Note that when revoking privileges from a role R, you do so as the user specified via login. If R has been granted the same privileges by another user also, R can still access database objects via these privileges.
- When revoking privileges,
RESTRICT
is assumed (see PostgreSQL docs). - The default authentication assumes that you are either logging in as or sudo’ing to the
postgres
account on the host. - To avoid “Peer authentication failed for user postgres” error, use postgres user as a become_user.
- This module uses psycopg2, a Python PostgreSQL database adapter. You must ensure that psycopg2 is installed on the host before using this module.
- If the remote host is the PostgreSQL server (which is the default case), then PostgreSQL must also be installed on the remote host.
- For Ubuntu-based systems, install the postgresql, libpq-dev, and python-psycopg2 packages on the remote host before using this module.
- The ca_cert parameter requires at least Postgres version 8.4 and psycopg2 version 2.4.3.
See Also
See also
- community.general.postgresql_user
- The official documentation on the community.general.postgresql_user module.
- community.general.postgresql_owner
- The official documentation on the community.general.postgresql_owner module.
- community.general.postgresql_membership
- The official documentation on the community.general.postgresql_membership module.
- PostgreSQL privileges
- General information about PostgreSQL privileges.
- PostgreSQL GRANT command reference
- Complete reference of the PostgreSQL GRANT command documentation.
- PostgreSQL REVOKE command reference
- Complete reference of the PostgreSQL REVOKE command documentation.
Examples
# 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.general.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
community.general.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
community.general.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
community.general.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.general.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.general.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.general.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
community.general.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.general.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.general.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
community.general.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.general.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.general.postgresql_privs:
db: test
objs: fdw
privs: ALL
type: foreign_data_wrapper
role: reader
# Available since community.general 0.2.0
- name: GRANT ALL PRIVILEGES ON TYPE customtype TO reader
community.general.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.general.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.general.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.general 1.3.0 or higher
- name: GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA common TO caller
community.general.postgresql_privs:
type: prucedure
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.general.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.general.postgresql_privs:
db: library
state: absent
schema: library
objs: TABLES
privs: SELECT
type: default_privs
role: reader
target_roles: librarian
# Available since community.general 0.2.0
- name: Grant type privileges for pg_catalog.numeric type to alice
community.general.postgresql_privs:
type: type
roles: alice
privs: ALL
objs: numeric
schema: pg_catalog
db: acme
Return Values
Common return values are documented here, the following are the fields unique to this module:
Key | Returned | Description |
---|---|---|
queries list / elements=string |
always |
List of executed queries.
Sample: ['REVOKE GRANT OPTION FOR INSERT ON TABLE "books" FROM "reader";'] |
Authors
- Bernhard Weitzhofer (@b6d)
- Tobias Birkefeld (@tcraxs)
© 2012–2018 Michael DeHaan
© 2018–2019 Red Hat, Inc.
Licensed under the GNU General Public License version 3.
https://docs.ansible.com/ansible/2.10/collections/community/general/postgresql_privs_module.html