canonical / canonical/postgresql-operator
extra-user-roles=admin does not grant "CREATE USER" privileges
- Dominant language
- Python
- Stars
- 20
- Forks
- 36
- Avg merge
- 4d 15h
- Merged PRs (30d)
- 30
Description
## Steps to reproduce
1. Following the tutorial, deploy a PostgreSQL <> Data-Integrator <> PGBouncer setup
2. Set `extra-user-roles` config on `data-integrator` to `admin`.
3. Get a PSQL shell using the credentials returned by `get-credentials` action on `data-integrator`.
4. Try running
```
CREATE ROLE myuser LOGIN PASSWORD 'strong_password';
````
which will error out with:
```
launchpad=> CREATE ROLE myuser LOGIN PASSWORD 'strong_password';
ERROR: permission denied to create role
DETAIL: Only roles with the CREATEROLE attribute may create roles.
```
## Expected behavior
User gets created successfuly and `admin` role has all the necessary privileges to GRANT necessary permissions for users.
1. CONNECT privilege
2. SCHEMA privileges
3. TABLE access related privileges
## Actual behavior
```
psql -U relation_id_9
launchpad=> CREATE database mydb;
CREATE DATABASE
launchpad=> CREATE ROLE myuser LOGIN PASSWORD 'strong_password';
ERROR: permission denied to create role
DETAIL: Only roles with the CREATEROLE attribute may create roles.
launchpad=> \du+
List of roles
Role name | Attributes | Member of | Description
----------------------------+------------------------------------------------------------+--------------------------------------+-------------
admin | Cannot login | {pg_read_all_data,pg_write_all_data} |
pgbouncer_auth_relation_10 | Superuser | {} |
relation_id_9 | Create DB | {admin} |
launchpad=>
```
## Versions
Operating system:
Ubuntu 24.04.2 LTS
Juju CLI: 3.6.5-genericlinux-amd64
Juju agent: 3.6.5
```
App Version Status Scale Charm Channel Rev Exposed Message
data-integrator active 1 data-integrator latest/edge 151 no
pgbouncer 1.21.0 active 1 pgbouncer 1/edge 744 no
postgresql 16.8 active 3 postgresql 16/edge 610 no
```
LXD: 5.21.3 LTS
## Log output
Juju debug log:
## Additional context
Contributor guide
Research direction
Start by reproducing the PostgreSQL and Data Integrator setup, then trace the implementation of the `extra-user-roles` configuration and the credentials returned by `get-credentials`. Done means the configured `admin` role permits CREATE ROLE and the listed CONNECT, schema, and table privileges, with coverage for the reported setup.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100