canonical / canonical/postgresql-operator

extra-user-roles=admin does not grant "CREATE USER" privileges

Open
#864 2 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.