Chicken Egg problem with users and databases

[breml]

In the beginning of this role, the databases have been created first and the users after. This allowed to use the db setting on the postgresql_users. Since 929fe7e#diff-2444ad0870f91f17ca6c2a5e96b26823 this has been changed, such that the users are created first and the databases second. This allows to set the owner on postgresql_databases but now the db on postgresql_users is no longer working.

I guess the only way to fix this is to first create the users and the databases and apply the db setting for the postgresql_users in a second step, when all databases have been created.

[hyclak]

I ran into this issue as well. I created a later task to use the postgresql_privs module to accomplish that:

postgresql_privs:
  db: postgres
  privs: ALL
  type: database
  obj: thedb
  role: theuser
become: yes
become_user: postgres

It would be much more convenient to be able to just db: thedb in the role!

[Migsi]

Would love to avoid writing a "me too" message, but yep, can confirm the chicken-egg problem and drives me nuts :/

Any updates on this?

[Pepan]

This should be really solved ... this #135 solution works.. Why it is not merged already?

[notpixxl]

I'm able to bypass this problem, invert line 22 and 23 (import_tasks) on main.yml

[Migsi]

@notpixxl by just swapping the database and user creation task, you are breaking functionality that requires the user to exist before database creation (e.g. setting the ownership). The PR mentioned by @Pepan seems fine and should be merged. I guess @geerlingguy (ping) just didn't have time yet to look into this and confirm the MR.

[notpixxl]

@Migsi yeah I realized it after, so i write a new role with postgres_privs module, now I use it after the db and users creations and i can fine tune the privs on objects.

[Migsi]

Why was the patch still not merged? I can't imagine it is intended to keep this misbehavior?

[geerlingguy]

The un-fun thing about this is that for years, many users of the role were begging me to change the order. Against my better judgement, I merged that change. And now the other half of the role's users are wanting it back in the other order.

Is there any way we can make it work both ways? I like it in the original order (not the way it currently is) but can change things to work either way on my end, so it's not a huge deal to me.

[Migsi]

As far as I understand this problem, it is actually not solvable by simply changing the order. The ability to set the postgresql_users for a certain db OR let a user own a db can't be performed both if the structure is kept as it is right now. Thats why the proposed fix isn't simply changing the order but adding a second call to the postgresql_user module after creating the databases. This actually allows to add users to the database using the db property of the role.

EDIT:

Is there any way we can make it work both ways?

The proposed fix should enable exactly that.