Hii,
I can't seem to find the correct set of parameters for the
postgresql_privs module to do this as the postgres user:
GRANT CREATE ON SCHEMA public TO role1;
I tried the obvious:
- postgresql_privs:
login_host: pg.host.name
login_user: postgres
login_password: hackme
database: postgres
roles: role1
privs: CREATE
schema: public
But that fails with:
Argument "objs" is required for type "table".
I didn't intend to only specify 'table', so looking at the docs on
https://docs.ansible.com/ansible/5/collections/community/postgresql/postgresql_privs_module.html#parameter-type,
that appears to be the default for the 'type' parameter.
I *think* I just want type=schema there, so I add that:
- postgresql_privs:
login_host: pg.host.name
login_user: postgres
login_password: hackme
database: postgres
roles: role1
privs: CREATE
schema: public
type: schema
But then it fails with:
Argument "schema" is not allowed for type "schema".
Ehm, OK, so I then remove schema parameter:
- postgresql_privs:
login_host: pg.host.name
login_user: postgres
login_password: hackme
database: postgres
roles: role1
privs: CREATE
type: schema
This then fails with:
Argument "objs" is required for type "schema".
Right. Looking at the docs for that parameter
(https://docs.ansible.com/ansible/5/collections/community/postgresql/postgresql_privs_module.html#parameter-objs),
and trying to match the initial SQL statement, I don't think I should
be limiting things here, so the best I can come up with is
ALL_IN_SCHEMA:
- postgresql_privs:
login_host: pg.host.name
login_user: postgres
login_password: hackme
database: postgres
roles: role1
privs: CREATE
type: schema
objs: ALL_IN_SCHEMA
Now it fails with:
schema "ALL_IN_SCHEMA" does not exist
Which, looking back at the docs for the objs parameter, makes sense:
"If type is table, partition table, sequence, function or procedure,
the special value ALL_IN_SCHEMA can be provided instead to specify all
database objects of type in the schema specified via schema."
But now the coin dropped, this seems to do what I want:
- postgresql_privs:
login_host: pg.host.name
login_user: postgres
login_password: hackme
database: postgres
roles: role1
privs: CREATE
type: schema
objs: public
changed: [dev_foobar1] => changed=true
ansible_facts:
discovered_interpreter_python: /usr/bin/python3
queries:
- GRANT CREATE ON schema "public" TO "role1";
Posting this here for anyone else that might be confused by the
combination of parameters that are needed...
Looking back I see that this snippet in the examples
(https://docs.ansible.com/ansible/5/collections/community/postgresql/postgresql_privs_module.html#examples)
should have been good to start with:
- 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
Background: the upgrade to PostgreSQL v15 introduced some changes in
the way things are done, and the above reverts things to what they
were in v14 so we can work on properly doing the limited schema setup.
Dick