Merge lp:~stub/charms/precise/postgresql/docs into lp:charms/postgresql

[Stuart Bishop]

After migrating some systems between OpenStack clouds, it became apparent
that database permissions need to be documented.

Document this. I'd like to provide a mechanism for the PostgreSQL
charm to automatically manage database permissions, which will be
suitable for most setups, but until this science fiction becomes fact
this documentation will have to do.

[Cory Johns (johnsca)]

Stuart,

Thanks for adding this documentation. It looks great.

You might want to check out the solution Matty came up with in [1] for keeping the passwords stable on relation changes without storing the actual password.

[1]: https://code.launchpad.net/~mattyw/charms/precise/mongodb/auth_experiment/+merge/162887

review: Approve

[Stuart Bishop (stub)]

Ta.

The fundamental problem remains the same no matter how we store passwords or keep them stable unfortunately. If we bring up a new service from backup, we need to publish passwords to client units. Thus the backup needs to include the passwords in plaintext, or we need to regenerate the passwords.

We might be able to get stable usernames though, which is the biggest problem, if we stop using the relationship id when generating them and instead use the database service name and client service name. Then, if we bring up a system using the same service names related in the same ways, then the usernames would be identical to the old system and match what is stored in the recovered database.

[Cory Johns (johnsca)]

Ah, you're right that the mongodb charm isn't dealing with a backup / restore scenario, so the particular trick isn't sufficient in this case. Not changing the password on every relation-change event still seems like it could be less confusing for the admin, though it might also encourage too much reliance on the password being stable and cause the problems you mentioned in the docs during a restore.

The mongodb charm also uses the unit number as part of the stable username, which would change during a restore operation. I assume that if a given service has multiple units for scaling, they would want to share the same database credentials, allowing your idea for stable usernames to work?

[Tim Van Steenburgh (tvansteenburgh)]

+1 LGTM, thanks stub.

review: Approve

=== modified file 'README.md'
--- README.md	2014-08-21 12:53:31 +0000
+++ README.md	2014-10-24 10:45:44 +0000
@@ -100,6 +100,61 @@
 automatically generated user for `db-admin` relations is a PostgreSQL
 superuser.
 
+## Database Permissions and Disaster Recovery
+
+⚠ These two topics are entwined, because failing to follow best
+  practice with your database permissions will make your life difficult
+  when you need to recover after failure.
+
+_Always_ set the 'roles' relationship setting when joining a
+relationship. _Always_ grant permissions to database roles for _all_
+database objects your charm creates. _Never_ rely on access permissions
+given directly to a user, either explicitly or implicitly (such as being
+the user who created a table). Consider the users you are provided by
+the PostgreSQL charm as ephemeral. Any rights granted directly to them
+will be lost if relations are recreated, as the generated usernames will
+be different. _If you don't follow this advice, you will need to
+manually repair permissions on all your database objects after any of
+the available recovery mechanisms._
+
+Learn about the SQL `GRANT` statement in the excellect [PostgreSQL
+reference guide][3].
+
+### block-storage-broker
+
+If you are using external storage provided by the block storage broker,
+recovery or a failed unit is simply a matter of ensuring the old unit
+is fully shut down, and then bringing up a fresh unit with the old
+external storage mounted. The charm will see the old database there
+and use it.
+
+If you are unable or do not wish to to simply remount the same
+filesystem, you can of course copy all the data from the old filesystem
+to the new one before bringing up the new unit.
+
+### dump/restore
+
+PostgreSQL dumps, such as those that can be scheduled in the charm, can
+be recovered on a new unit by using 'juju ssh' to connect to the new unit
+and using the standard PostgreSQL `pg_restore(1)` tool. This new unit must
+be standalone, or the master unit. Any hot standbys will replicate the
+recovered data from the master.
+
+You will need to use `pg_restore(1)` with the `--no-owner` option, as
+users that existed in the old service will not exist in the new
+service.
+
+### PITR
+
+If you had configured WAL-E, you can recover a WAL-E backup and replay
+to a point in time of your choosing using the `wal-e` tool. This
+will recover the whole database cluster, so all databases will be
+replaced.
+
+If there are any hot standby units, they will need to be destroyed
+and recreated after the PITR recovery.
+
+
 ## During db-relation-joined
 
 ### the client service provides:
@@ -304,3 +359,4 @@
 
   [1]: https://bugs.launchpad.net/charms/+source/postgresql/+bug/1258485
   [2]: https://github.com/wal-e/wal-e
+  [3]: http://www.postgresql.org/docs/9.3/static/sql-grant.html