Merge ~cjwatson/launchpad:doc-charm-database-roles into launchpad:master

[Colin Watson]

Document database role management for charms

When setting up charmed instances of the librarian recently, I had to invent some approaches to handling database roles. I expect much the same approaches to be used for a number of other charms, so I wanted to at least write down what I did so that other people don't need to reinvent it if they find themselves doing the same thing.

[Jürgen Gmach (jugmac00)]

Thanks for the documentation! I added some comments where I'd need some additional information to be able to do similar things.

I understand that we need to do these manual steps due to the way Juju (currently) works.

I would believe that our requirements are not too special. Is the Juju team aware of this situation? Were there discussions or is there an open bug report?

review: Approve

[Colin Watson (cjwatson)]

This isn't a matter for the Juju team (in much the same way that one wouldn't normally escalate bugs in a C program to the compiler team). It's also not an issue if you're using the `postgresql` charm by way of normal Juju relations; in that case the user is created automatically. It's arguably a limitation in the very simple proxy charm that we use to pass around PostgreSQL credentials in order that we don't have to have applications deployed in the same Juju model as PostgreSQL itself; we may eventually be able to use cross-model relations instead, but I'm not enthusiastic about introducing that extra complexity when we already have enough on our plate just with dealing with the migration to Juju.

For qastaging and staging, this is a transitional measure; at some point over the next few months I expect to migrate their databases into the same Juju model as the application code, at which point we can use normal relations for those environments instead.

diff --git a/doc/explanation/charms.rst b/doc/explanation/charms.rst
index 6fe52d3..28cb3ba 100644
--- a/doc/explanation/charms.rst
+++ b/doc/explanation/charms.rst
@@ -111,3 +111,100 @@ Managing secrets like this is more cumbersome than updating Mojo specs, so
 try to keep it to a minimum.  In some cases there may be automation
 available to help, such as the `autocert charm
 <https://charmhub.io/autocert>`_.
+
+Database roles
+==============
+
+PostgreSQL considers "users" and "roles" to be very nearly synonymous.  In
+this section, "user" means specifically a role that has login credentials.
+
+Launchpad uses lots of different database roles.  We used to deal with this
+by having each user on each machine that runs Launchpad code have a
+``.pgpass`` file with credentials for the particular set of users that it
+needs, and then it would log in as those users directly.  However, this
+approach doesn't work very well with Juju: the ``postgresql`` charm allows
+related charms to request access to a single user (per interface), and they
+can optionally request that that user be made a member of some other roles;
+SQL sessions can then use ``SET ROLE`` to switch to a different role.
+
+In our production, staging, and qastaging environments, we use a proxy charm
+to provide charms with database credentials rather than relating them to
+``postgresql`` directly (partly for historical reasons, and partly to avoid
+complications when the database is deployed in a different region from some
+of our applications).  As a result, we need to do some manual user
+management in these environments.  On staging and qastaging, developers can
+do most of this themselves when adding new charms to those existing
+deployment environments.
+
+Taking the librarian as an example: ``charm/launchpad-librarian/layer.yaml``
+lists the ``binaryfile-expire``, ``librarian``, ``librarianfeedswift``, and
+``librariangc`` roles as being required (this corresponds to the database
+users used by the services and jobs installed by that particular charm).  To
+create the corresponding user, we first generate a password (e.g. using
+``pwgen 30 1``), then log into the management environment (``ssh -t
+launchpad-bastion-ps5.internal sudo -iu stg-launchpad``), set up environment
+variables for qastaging (``. .mojorc.qastaging``), run ``juju ssh
+launchpad-admin/leader``, and run ``db-admin``.  In the resulting PostgreSQL
+session, replacing ``<secret>`` with the generated password:
+
+.. code-block:: psql
+
+    CREATE ROLE "juju_launchpad-librarian"
+    	WITH LOGIN PASSWORD '<secret>'
+        IN ROLE "binaryfile-expire", "librarian", "librarianfeedswift", "librariangc";
+
+The user name here should be ``juju_`` plus the name of the charm, since
+that matches what the ``postgresql`` charm would create.
+
+Having done that, we need to install the new credentials.  On
+``[e-mail]``, find the
+``db_connections`` option under the ``external-services`` application, and
+add an entry to
+``~/.local/share/mojo/LOCAL/mojo-lp/lp/qastaging/deploy-secrets`` that looks
+like this, again replacing ``<secret>`` with the generated password:
+
+.. code-block:: yaml
+
+    launchpad_qastaging_librarian:
+      master: "postgresql://juju_launchpad-librarian:<secret>@pamola.internal:6432/launchpad_qastaging?connect_timeout=10"
+      standbys: []
+
+In the connection string URL, the database host, port, and name (in this
+case, ``pamola.internal``, ``6432``, and ``launchpad_qastaging``
+respectively) should match those of other entries in ``db_connections``.
+
+The configuration for the ``pgbouncer`` connection pooler must also be
+updated to match, which currently requires help from IS.  On
+``pamola.internal``, IS should take the relevant username/password pair from
+the ``deploy-secrets`` file above and add it to
+``/etc/pgbouncer/userlist.txt``.
+
+Staging works similarly with the obvious substitutions of ``staging`` for
+``qastaging``.  The qastaging and staging environments currently share a
+``pgbouncer``; as a result, while the user still has to be created on both
+database clusters, the passwords for a given user on qastaging and staging
+must be identical.
+
+Production works similarly, except that IS needs to generate the user on the
+production database, add it to the production ``pgbouncer`` by editing
+``userlist.txt`` in ``[e-mail]`` and
+pushing it out using Mojo, and update the secrets file found in
+``~/.local/share/mojo/LOCAL/mojo-lp/lp/production/deploy-secrets`` on
+``[e-mail]``.  Developers should request this
+via RT, using this document to construct instructions for IS on what to do.
+
+Finally, the corresponding application in `launchpad-mojo-specs
+<https://git.launchpad.net/launchpad-mojo-specs>`_ needs to be configured
+with the appropriate database name (``launchpad_qastaging_librarian`` in the
+example above).  This normally looks something like this, where
+``librarian_database_name`` is an option whose value is set depending on the
+stage name:
+
+.. code-block:: yaml
+
+  launchpad-librarian:
+    ...
+    options: {{ base_options() }}
+      databases: |
+        db:
+          name: "{{ librarian_database_name }}"