Use shared postgresql from multiple units

I’m trying to use a postgresql https://github.com/canonical/ops-lib-pgsql from multiple units. Using operator framework.

As soon as the relations is set up (juju relate myapp:db postgresql:db) there is a user created in the database juju_myapp.

At postgresql | Juju it says: The database may be shared with other Juju Applications, allowing data to be shared. Which makes sense, a new user will be created for each new relation to an app, juju_<app_name>.

However, I want to use the database from multiple units in the same app. What is the approach to do this? The units can’t use the same user juju_myapp, or not what I know of… Do I need to “manually” create separate users for each units? E.g. juju_myapp_<unit_nr>. If so, what is the best way to do this?

Let’s find out if I made myself clear :smiley:

1 Like

Multiple units in the same app will use the same connection string (event.master), and the same username. There is no point to using different usernames for each Unit of an Application, since they are all instances of the same Charm and it is the same security context. Even if you want to be able to tell connections apart for some reason such as auditing or monitoring, it would be better to adjust the application_name or fallback_application_name attribute on the connection string than to create and maintain separate users (and now that I think of it, maybe ops-lib-pgsql should do that for you).

(fun fact, several years ago each unit did get a unique user, and it caused people all sorts of headaches where DB objects created by one unit were not visible to other units, because most apps are completely oblivious to database security)

1 Like