Small things, Big improvements – PostgreSQL

TL;DR: We've just added schema visibility to our PostgreSQL data browser and the ability to change your admin password.

We're always making small changes to the Compose platform's databases. A couple of those small changes just landed for PostgreSQL so now is a good time to talk about the hopefully big improvements they'll make for PostgreSQL users on Compose.

Schema visibility

Our PostgreSQL data browser is an online complement to the many PostgreSQL tools and applications out there; it lets you quickly browse a database without installing any software and over the web. Or, as it was, it let you browse the public schema which some users found a problem.

If you don't know about schemas, then let's quickly run through what they are. You can create databases in Compose which contain users and roles. So that developers and administrators can partition the space within the database, they can create schemas which, in turn, can contain tables, views, functions and more. The default schema is called public and things default to using that.

But you could have a schemas called appa and appb that use the same database. You can then create tables without worrying that their names would clash. If a table called fred was created in both then it would be referred to as appa.fred and appb.fred. It's really useful as another level of organizing your data but avoids walling off each set of data in its own silo; applications can query across schemas. That said, many people just work with the default schema – we developed the browser with that general case in mind.

This did mean we omitted to support users with multiple schemas but now we've remedied that omission by allowing the data browser to list tables in schemas other than the default schema. You can also select and browse those other schema's tables too.

What we haven't added is the ability to create and drop schemas. This can be easily done from most PostgreSQL tools and, of course, the psql command line tool. If you want to know more about schemas, including the syntax involved in creating them (spoiler: it's CREATE SCHEMA schemaname) then check out the PostgreSQL documentation. How you use schemas is up to you - there's many useful ways to divide up your database with them such as having a schema per application - but do remember they are a unique and useful feature of PostgreSQL and not part of SQL standards.

Admin passwords

The administration password for PostgreSQL on Compose has to be handled in a slightly different way from when you are running your own database. Specifically, it has to be securely stored yet be available to for the management of your database so that Compose's automation can work with the cluster to allow upgrades, backups and all the other things we do automatically. We do this using Amazon's KMS service to ensure that we cannot see the password; when it is needed it is automatically decrypted and presented to the capsules that make up your PostgreSQL deployment.

We've historically generated that password for you and presented it after you've authenticated a second time in the console. As with our handling of Schemas, our implementation has worked well for most cases but there have been requests to allow the password to be changed manually. Now, we've added that capability while maintaining the same level of security.

To practical matters, you'll find the Change Password controls are hidden until you use the show/change button next to the obscured password in your Compose PostgreSQL console. That button will require you to re-authenticate with the Compose application and once re-authenticated you'll see this in the Overview.

That message in yellow is important. Your PostgreSQL databases will be restarted when you enter a new password, click on Change Password and answer Yes to the popup dialog. This means there may be a minor interruption in service and you may wish to schedule password changes to periods of low activity.

Two new features, two big improvements in how you can manage your data and security with Compose PostgreSQL. As always, make sure you're getting the latest from Compose by subscribing to Compose Articles or following us on Twitter, Facebook or Google+ - the appropriate links are below.