launchpad-dev team mailing list archive

[Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>]

On Thu, Sep 15, 2011 at 6:15 PM, Julian Edwards
<julian.edwards@xxxxxxxxxxxxx> wrote:

> One of the biggest skews we have that *consistently* hurts us is setting up DB
> users with the right permissions on tables.  This is largely because the
> security.cfg is not action-based, but user-based.  That is - we should be
> composing DB users' permissions out of a set of actions/roles (I think there's
> one example if this in the file but it's an exception), not setting up each
> user discretely.
>
> However, I want to re-examine the requirement to set up explicit permissions
> on each user at all.  Why do we need this?  What is it helping to protect?  Is
> any of this worth the hassle and rollout problems?

Its not a requirement, but just how we have been doing things. I
wouldn't want to do it across the board (there are some simple
services that can be given extremely limited access). There are
already things using a role type approach, eg. the three garbo_XXX
users are just clones of garbo, and we use the 'script' role to grant
access to the scriptactivity table (this used to be much more when
accounts where created on demand), and most of the rosetta scripts
using realms like 'poimport' and 'translations_approval'.

The granular grants gain us a little security, but not much. For
simpler systems, they do help give us a picture of what they affect.
For instance, we can easily see what targetnamecacheupdater is doing -
accessing data from a few sources, and updating two tables. This
sounds useful for helping narrow down concurrency issues, but turns
out that often it isn't the primary effects that cause issues but the
secondary ones (tables with foreign key references to the ones you are
updating being affected).


> This permissioning is also sometimes extremely hard to catch in testing. Quite
> often a particular set of permissions are only required in a tiny corner case
> that is not always relevant to the test intentions.
>
> We already know that having separate users is useful to identify rogue
> connections etc, but I fail to understand why they need permissions.  This is
> particularly interesting given that we only do this with scripts - the webapp
> has carte blanche.

It doesn't. It has a wide reach because it is wide scope. It probably
has too wide a reach because of cruft, so isn't that useful of an
example. But you will find holes and tables with restricted access
that are maintained by other mechanisms such as cronscripts, triggers,
services.

> I would like do away with the discrete permissions and make all the users
> inherit from "main".  This would also bring the benefits of quicker tests
> since they don't need to commit and switch db users.
>
> I'd love to hear opinions on this.

I'm all for defining realms. I'd like to see realms for the 'general'
stuff, like 'translations' or 'publication' with explicit listing of
unusual stuff so it sticks out and raises questions. It also helps
keep the door open on splitting the database.

I don't really want to see an effective 'GRANT ALL ON * TO PUBLIC'.
Maintaining security.cfg shouldn't be about sacrificing chickens. If a
permission is tripped up, it should indicate an issue that needs to be
dealt with. A corner case not being tested? Instead of poking at that
unrelated bit of data, should this blurry service be split up into
more specific services? Are these unexpected changes going to cause
data loss or concurrency problems?


-- 
Stuart Bishop <stuart@xxxxxxxxxxxxxxxx>
http://www.stuartbishop.net/