How to share a postgresql user between 2 different charms?

charm
1

I am starting to use postgresql as a central repository of information… relating it to apache superset and meltano

I start a DB of the same name in each of my db-relation-joined charms… but is there a way to share users?? or is that not recommended?

I ask because… I’m just trying to use Meltano ELT to pour data in postgresql… and likewise get apache superset to visualize that same db… but it seems the users cannot see eachothers DB… or it’s not clear how I can more closely tie them together beyond just calling for the same DB

#!/bin/bash

juju-log -l 'WARNING' 'db-relation-created'
status-set maintenance "Preparing superset to create connection to new DB $(date +"%H:%M")"

relation-set database="my_fresh_sb"

juju-log -l 'WARNING' "database is set to my_fresh_sb on joined"

is there something like relation-set username ?

this is my bundle so far

series: focal 
applications:
  postgresql:
    charm: cs:postgresql
    num_units: 1
    to:
    - "1"
    options:
      extra_pg_auth: host all all 0.0.0.0/0 md5
  superset:
    charm: /path/to/superset-charm
    num_units: 1
    to:
    - "2"
  meltano:
    charm: /path/to/meltano-charm
    num_units: 1
    to:
    - "3"
    options:
      git_private_key_proj: |
        -----YOUR RSA PRIVATE KEY-----
machines:
  "1": {}
  "2": {}
  "3": {}
relations:
- - superset:db
  - postgresql:db
- - meltano:db
  - postgresql:db
2

It appears that there are roles that can be configured to share access between applications as documented under “Database Permissions and Disaster Recovery” on the postgresql charm’s README on charmhub.io.

It appears that you can set the “roles” attribute on the relation to add/consume roles with the addition of the user/database when relating to postgres.

  • roles - A comma separated list of PostgreSQL roles to grant this relation’s user. Roles will be created if they do not already exist.

You may need to “grant” permissions to the role created manually, I’ve not used the feature.

2 Likes
3

for now I manually grant on the user access to the tables but this is great insight, will see how i can smooth this out even more!

1 Like