The admin extra-user-role via data-integrator doesn't allow creating roles

[hloeung]

Steps to reproduce

1. juju deploy postgresql --channel 14/stable
2. juju deploy data-integrator
3. juju config data-integrator database-name='mydatabase' extra-user-roles='admin'
4. juju integrate data-integrator postgresql
5. juju run data-integrator/leader get-credentials
6. juju ssh --container postgresql postgresql/leader bash
7. psql --host=10.146.64.52 --username=operator --password postgres
8. \du

Expected behavior

postgres=# \du
                                                  List of roles
  Role name  |                         Attributes                         |              Member of
-------------+------------------------------------------------------------+--------------------------------------
...
 relation-27 | Create DB, Create role                                     | {admin}

Actual behavior

postgres=# \du
                                                  List of roles
  Role name  |                         Attributes                         |              Member of
-------------+------------------------------------------------------------+--------------------------------------
...
 relation-27 | Create DB                                                  | {admin}

Versions

Operating system: ubuntu@22.04

Juju CLI: 3.5.3

Juju agent: 3.5.3

Charm revision: postgresql 14.12 active 3 postgresql 14/stable 468 yes

Log output

Additional context

Creating an admin user, one would expect it to have access to create roles in addition to creating databases. Unfortunately, that is not the case and it seems restricted to only being allowed to create dateabases.

To work around this, I've had to run:

ALTER USER "relation-27" CREATEROLE;

[syncronize-issues-to-jira]

Thank you for reporting us your feedback!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-6182.

This message was autogenerated

[marceloneppel]

Hi, @hloeung! I'll discuss this in this week's Data Platform Office Hours meeting.

[hloeung]

@marceloneppel any updates on this?

[hloeung]

Any updates here? Even if it's an additional role would be helpful for us, especially since we're getting more and more requests for this from the release of the DBaaS product offering.

Something like extra-user-roles='admin,createrole,createuser' or similar.

[marceloneppel]

Hi, @hloeung! Apologies. After discussing with the team, unfortunately, the Data Platform charms won't have a special role that allows the user to create new roles.

One alternative is to deploy a data-integrator charm for each new user you need.

Another option is to use the identity platform to create new users. In this cycle, we're implementing the LDAP integration in the PostgreSQL charm.