# postgresql\_privs equivalent for GRANT statement + solution

**URL:** <https://forum.ansible.com/t/postgresql-privs-equivalent-for-grant-statement-solution/38301>\
**Category:** Ansible Project\
**Created:** [August 4, 2023, 1:16pm UTC](https://forum.ansible.com/t/postgresql-privs-equivalent-for-grant-statement-solution/38301 "2023-08-04T13:16:07Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![dnmvisser](https://avatars.discourse-cdn.com/v4/letter/d/8e8cbc/32.png) [@dnmvisser](https://forum.ansible.com/u/dnmvisser)\
**Post date:** [August 4, 2023, 1:16pm UTC](https://forum.ansible.com/t/postgresql-privs-equivalent-for-grant-statement-solution/38301/1 "2023-08-04T13:16:07Z")

</div>

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:  
&nbsp;&nbsp;&nbsp;&nbsp;login\_host: pg.host.name  
&nbsp;&nbsp;&nbsp;&nbsp;login\_user: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;login\_password: hackme  
&nbsp;&nbsp;&nbsp;&nbsp;database: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;roles: role1  
&nbsp;&nbsp;&nbsp;&nbsp;privs: CREATE  
&nbsp;&nbsp;&nbsp;&nbsp;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](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:  
&nbsp;&nbsp;&nbsp;&nbsp;login\_host: pg.host.name  
&nbsp;&nbsp;&nbsp;&nbsp;login\_user: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;login\_password: hackme  
&nbsp;&nbsp;&nbsp;&nbsp;database: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;roles: role1  
&nbsp;&nbsp;&nbsp;&nbsp;privs: CREATE  
&nbsp;&nbsp;&nbsp;&nbsp;schema: public  
&nbsp;&nbsp;&nbsp;&nbsp;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:  
&nbsp;&nbsp;&nbsp;&nbsp;login\_host: pg.host.name  
&nbsp;&nbsp;&nbsp;&nbsp;login\_user: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;login\_password: hackme  
&nbsp;&nbsp;&nbsp;&nbsp;database: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;roles: role1  
&nbsp;&nbsp;&nbsp;&nbsp;privs: CREATE  
&nbsp;&nbsp;&nbsp;&nbsp;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](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:  
&nbsp;&nbsp;&nbsp;&nbsp;login\_host: pg.host.name  
&nbsp;&nbsp;&nbsp;&nbsp;login\_user: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;login\_password: hackme  
&nbsp;&nbsp;&nbsp;&nbsp;database: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;roles: role1  
&nbsp;&nbsp;&nbsp;&nbsp;privs: CREATE  
&nbsp;&nbsp;&nbsp;&nbsp;type: schema  
&nbsp;&nbsp;&nbsp;&nbsp;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:  
&nbsp;&nbsp;&nbsp;&nbsp;login\_host: pg.host.name  
&nbsp;&nbsp;&nbsp;&nbsp;login\_user: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;login\_password: hackme  
&nbsp;&nbsp;&nbsp;&nbsp;database: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;roles: role1  
&nbsp;&nbsp;&nbsp;&nbsp;privs: CREATE  
&nbsp;&nbsp;&nbsp;&nbsp;type: schema  
&nbsp;&nbsp;&nbsp;&nbsp;objs: public

changed: [dev\_foobar1] =\> changed=true  
&nbsp;&nbsp;ansible\_facts:  
&nbsp;&nbsp;&nbsp;&nbsp;discovered\_interpreter\_python: /usr/bin/python3  
&nbsp;&nbsp;queries:  
&nbsp;&nbsp;- 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](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  
&nbsp;&nbsp;community.postgresql.postgresql\_privs:  
&nbsp;&nbsp;&nbsp;&nbsp;db: library  
&nbsp;&nbsp;&nbsp;&nbsp;privs: ALL  
&nbsp;&nbsp;&nbsp;&nbsp;type: schema  
&nbsp;&nbsp;&nbsp;&nbsp;objs: public,math  
&nbsp;&nbsp;&nbsp;&nbsp;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