community.postgresql.postgresql_privs - allow list of schemas

[danielleshoemake]

SUMMARY

Allow the schema parameter to accept a list of schemas

ISSUE TYPE

• Feature Idea

COMPONENT NAME

community.postgresql.postgresql_privs - schema

ADDITIONAL INFORMATION

My organization has about 100 databases on an instance of postgres, each with 1 to 3 schemas. We setup 6 group roles, each with a different set of permissions, universally on all databases and schemas. To execute GRANT SELECT,UPDATE ON ALL TABLES IN SCHEMA public TO role1;, this requires 100 to 300 iterations of the community.postgresql.postgresql_privs module to cover the 1 to 3 schemas in each database for 1 of the 6 roles. And of course we are granting more than table permissions, so the playbook to configure these 6 roles on all databases takes nearly two hours. Perhaps the loop that would be required in code would take the same amount of time, but we were thinking that if we could shave the number of iterations down by giving a list of schemas, this might reduce the overall time it takes to handle permission configuration.

[andreasscherbaum]

Makes sense.

[Andersson007]

@danielleshoemake hello, thanks for requesting the enhancement!
would you like to tackle it your self? If yes, please let us know.
our quick start dev guide can help volunteers who are new to development here
Putting the help wanted label

[dawagner]

Hello,

I find myself needing this as well :)

Looks like it would require changes at least at the following places (besides doc and tests):

• community.postgresql/plugins/modules/postgresql_privs.py

  Line 792 in f3789b7

  quoted_schema_qualifier = '"%s"' % schema_qualifier.replace('"', '""') if schema_qualifier else None

• community.postgresql/plugins/modules/postgresql_privs.py

  Line 822 in f3789b7

  set_what = '%s ON %s %s' % (','.join(privs).replace('_', ' '), orig_objs, quoted_schema_qualifier)

• and

  community.postgresql/plugins/modules/postgresql_privs.py

  Line 882 in f3789b7

  def for_schema(self, schema):

Maybe some day I'll give it a try but I'm a postgresql newbie and an ansible newbie...

[Andersson007]

@dawagner hello, thanks for your interest in helping with this!

Maybe some day I'll give it a try but I'm a postgresql newbie and an ansible newbie...

That's OK, as long as your changes covered with tests and the existing tests run with no failures, your contribution will be welcome. Check out this guide.