PostgreSQL Use Case/Issues

[James Beedy]

On Wed, Feb 22, 2017 at 8:55 AM, Stuart Bishop <stuart.bishop at canonical.com>
wrote:

> On 22 February 2017 at 21:46, James Beedy <jamesbeedy at gmail.com> wrote:
>
> > Experiencing some varying results with the PostgreSQL charm, hoping to
> get
> > some validation on my use case.
>
> This all seems the pgsql interface. Expected use is at
> http://interface-pgsql.readthedocs.io/en/stable/
> requires.html#example-usage
> if you haven't seen it already. The protocol had a fairly large
> (backwards compatible) change recently, so sorry about the teething
> troubles.
>
> > 1. Sometimes (feeling like 1/5ish deploys) a database is created with the
> > name of the application, instead of the database name the application
> > requested.
>
> Unfortunately this seems possible.
>
> A client can 'accept the defaults' by not setting any properties on
> the db relation when it joins (dating back to the original protocol
> with pyjuju). When the PostgreSQL charm runs its relation-joined and
> relation-changed hooks, it has no way of telling if the client just
> wants to 'accept the defaults', or if the client has not yet run its
> relation-joined or relation-changed hooks yet. So if it sees an empty
> relation, it assumes 'accept the defaults' and provides a database
> named after the client service. If your client then runs its
> relation-joined hook, the 'db.connected' state will be set (because
> there is a relation), and the 'db.*.available' states will also be set
> because there is a database available (not the one you want, but you
> haven't had a change to say otherwise yet). At which point your
> handlers kick in on *.available and get valid connection strings to a
> different database to the one you want. And maybe the handler that
> calls set_database() runs too, but it is too late.
>
> I think I can fix this, but I'll need to make a corresponding
> adjustment in the PostgreSQL charm and the fix will only take effect
> with updated services.
>
> +1 for a fix. Thanks.



> pre-reactive, the burden was on the charm to wait until the database
> name provided matches the one requested. That is still what you can do
> here if you need an immediate work around, although the goal of the
> interface is to remove that sort of annoying implementation detail
> from your charm so I certainly should try and sort this out.
>
>
> > 2. Every ~ 1/5 deploys (odd how this keeps surfacing), I get a 'NoneType'
> > error when trying to access 'master.host', or 'master.uri' via relation
> to
> > the PostgreSQL charm on the firing of 'master.available'. See bug created
> > here -> https://bugs.launchpad.net/interface-pgsql/+bug/1666337
>
> Its related to the above issue. Your charm connects and gets the
> db.master.available state set. But you want to specify the database
> name, so a handler runs calling set_database(). At this point the
> .master and .standbys properties start correctly returning None, but
> set_database() neglected to remove the *.available states so handlers
> got kicked in that shouldn't have.
>
> Ok, so a fix coming for this too in that case? This one is borking on my
devs who are deploying my bundles, in turn causing me grief, but also
borking on me too, making me question my own sanity :(


> > 3. Users seem to have different access privs.
> >
> > On this specific deploy, everything seems to have initialized correctly,
> but
> > my applications don't have consistent access to the database across the
> > board. See -> http://paste.ubuntu.com/24046732/
>
> PostgreSQL tries to be secure by default, so users do not implicitly
> get access to each others tables. The charm that creates the tables
> needs to also grant permissions to them. Commonly people just 'GRANT
> ALL ON TABLE foo TO PUBLIC' to give full permissions to all other
> users on the system (all related services in the Juju case).


Applying this GRANT to just the other user got me around the permissions
issue as well (I was previously only granting to the database, didn't
realize I also needed to grant table explicitly), thanks!


> If you
> need more control, you can use the set_roles() method on the interface
> to have PostgreSQL grant some roles to your user, and then grant
> permissions explicitly to those roles. But this doesn't really help
> much from a security POV, so I've been toying with the idea of just
> having clients all connect as the same user for the common case where
> people don't want granular permissions (even if it does make security
> minded people wince).
>

Will the "common use case" be the only use case?



> --
> Stuart Bishop <stuart.bishop at canonical.com>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.ubuntu.com/archives/juju/attachments/20170222/d8d00855/attachment.html>