Charmed PostgreSQL VM

Channel Revision Published Runs on
latest/stable 345 09 Nov 2023
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04 Ubuntu 14.04
latest/stable 239 09 Feb 2022
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04 Ubuntu 14.04
latest/stable 226 01 Apr 2021
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04 Ubuntu 14.04
14/stable 468 11 Sep 2024
Ubuntu 22.04
14/stable 467 11 Sep 2024
Ubuntu 22.04
14/candidate 468 02 Sep 2024
Ubuntu 22.04
14/candidate 467 02 Sep 2024
Ubuntu 22.04
14/beta 502 23 Oct 2024
Ubuntu 22.04
14/beta 503 23 Oct 2024
Ubuntu 22.04
14/edge 508 27 Oct 2024
Ubuntu 22.04
14/edge 507 27 Oct 2024
Ubuntu 22.04
juju deploy postgresql --channel 14/stable
Show information

Platform:

Ubuntu
22.04 20.04 18.04 16.04 14.04

Charm Users explanations

There are two types of users in PostgreSQL:

  • Internal users (used by charm operator)
  • Relation/integration users (used by related applications)
    • Extra user roles (if default permissions are not enough)

Internal users explanations:

The operator uses the following internal DB users:

  • postgres - the initial/default PostgreSQL user. Used for very initial bootstrap only.
  • operator - the user that charm.py uses to manage database/cluster.
  • replication - the user performs replication between database PostgreSQL cluster members.
  • rewind - the internal user for synchronizing a PostgreSQL cluster with another copy of the same cluster.
  • monitoring - the user for COS integration.
  • backups - the user to perform/list/restore backups.

The full list of internal users is available in charm source code. The full dump of internal users (on the newly installed charm):

postgres=# \du
                                      List of roles
  Role name  |                         Attributes                         |  Member of   
-------------+------------------------------------------------------------+--------------
 backup      | Superuser                                                  | {}
 monitoring  |                                                            | {pg_monitor}
 operator    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres    | Superuser                                                  | {}
 replication | Replication                                                | {}
 rewind      |                                                            | {}

Note: it is forbidden to use/manage described above users! They are dedicated to the operators logic! Please use data-integrator charm to generate/manage/remove an external credentials.

It is allowed to rotate passwords for internal users using action ‘set-password’:

> juju show-action postgresql set-password
Change the system user's password, which is used by charm. It is for internal charm users and SHOULD NOT be used by applications.

Arguments
password:
  type: string
  description: The password will be auto-generated if this option is not specified.
username:
  type: string
  description: The username, the default value 'operator'. Possible values - operator, replication, rewind.

For example, to generate a new random password for internal user:

> juju run-action --wait postgresql/leader set-password username=operator

unit-postgresql-1:
  UnitId: postgresql/1
  id: "2"
  results:
    password: k4qqnWSZJZrcMt4B
  status: completed

To set a predefined password for the specific user, run:

> juju run-action --wait postgresql/leader set-password username=operator password=newpassword

unit-postgresql-1:
  UnitId: postgresql/1
  id: "4"
  results:
    password: newpassword
  status: completed

Note: the action set-password must be executed on juju leader unit (to update peer relation data with new value).

Relation/integration users explanations:

The operator created a dedicated user for every application related/integrated with database. Those users are removed on the juju relation/integration removal request. However, DB data stays in place and can be reused on re-created relations (using new user credentials):

postgres=# \du
                                      List of roles
  Role name  |                         Attributes                         |  Member of   
-------------+------------------------------------------------------------+--------------
 ..
 relation-6  |                                                            | {}
 relation-8  |                                                            | {}
 ...

Note: If password rotation is needed for users used in relations, it is needed to remove the relation and create it again:

> juju remove-relation postgresql myclientapp
> juju wait-for application postgresql
> juju relate postgresql myclientapp

Extra user roles

When an application charm requests a new user through the relation/integration it can specify that the user should have the admin role in the extra-user-roles field. The admin role enables the new user to read and write to all databases (for the postgres system database it can only read data) and also to create and delete non-system databases.

Note: extra-user-roles is supported by modern interface postgresql_client only and missing for legacy pgsql interface. Read more about the supported charm interfaces here.


Help improve this document in the forum (guidelines). Last updated 10 months ago.