db table ownership flakiness?

[Robert Bruce Park]

I'm developing a service called 'bileto' that uses postgres as a db backend, and I recently ran into the following scenario.

1. added an association between my bileto instance and an already-deployed postgres instance.

2. connected to postgres from bileto and created some tables/rows

3. destroyed the whole deployment and redeployed everything

4. found that bileto could no longer access the tables it had created. charm relation told bileto to connect as user "db_7_bileto" but tables were owned by "db_5_bileto" and so the tables that bileto created for itself previously could no longer be accessed by itself.

I had to manually ssh into postgres instance and change ownership of the tables to make it work again, but I'm concerned that when I go live in production this will be an issue again, and "cowboying" the fix is quite a hassle.

is there any way to figure out why the connection credentials changed and anything I can do about it to fix that? (eg is it possible in my charm relation to connect into postgres and ensure tables have proper ownership?)

Thanks.

[Stuart Bishop (stub)]

The relation gets a user based on the relation id. When you create objects, by default they are owned by the user creating them. If you recreate the relation, you will get a new relation id and a new user. It will have no access to the previously created objects unless you use the roles feature.

To properly do this, your charm needs to specify a role on the relation. PostgreSQL will grant that role to the users it creates. Your charm also needs to ensure that any objects it creates are owned by that role or has suitable permissions granted to that role. This way, when you destroy and recreate the relation the new user will have access to the previously created objects.

Eventually, there will be an action to futz the ownership of all objects in a database and changing the owner, as a quick way to resolve this for people who don't care about their database level security.

I have considered allowing the client charm to specify the username to use. I'm fairly sure this is a security hole though, as if the client unit is compromised it could set the username on the relation to whatever it wants and access any data it wants.

I'm also considering having the default username be the remote service name (which would also involve a similar update to the pgbouncer charm). That won't help people upgrading from earlier versions on the charm though.

For now, you need to use roles.

[Stuart Bishop (stub)]

TODO:

Drop the roles feature, as it is a security hole the same as allowing a client to specify the username.

Make the default username be based on the remote service name, not the relation id.

For backwards compatibility, keep things working as they are on the existing db and db-admin relations but deprecate them. Implement v2 relations, with better security, fewer surprises and better HA and proxy support.

Action to reset ownership of all objects in a given database to a given user.

[Stuart Bishop (stub)]

lp:~stub/charms/trusty/postgresql/rewrite is up for review and sorts the most common use cases by using the remove service name for the username, rather than the more volatile relation id. upgrade-charm will migrate services to the new scheme of things.

[Robert Bruce Park (robru)]

> Make the default username be based on the remote service name, not the relation id.

Yes, please.

For reference to future googlers, here is the solution I came up with for setting the roles correctly so that I can access my own tables:

http://bazaar.launchpad.net/~cupstream2distro-maintainers/bileto/charm/view/head:/hooks/db-relation-joined
http://bazaar.launchpad.net/~cupstream2distro-maintainers/bileto/charm/view/head:/hooks/db-relation-changed

Basically, define roles=bileto and then in the db-admin relation, "grant all on [table] to bileto"

[Robert Bruce Park (robru)]

> Make the default username be based on the remote service name, not the relation id.

Or at least, if not the remote service name, anything that is stable and reproducible across deployments would be excellent.