Re: Proposal: knowing detail of config files via SQL

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> On 3/3/15 5:58 PM, Tom Lane wrote:
> > One aspect of this that merits some thought is that in some cases
> > access to some set of functions is best granted as a unit. That's
> > easy with role properties but much less so with plain GRANT.
> > Do we have enough such cases to make it an issue?
>
> You could have built-in roles, such as "backup" and ship the system with
> the "backup" role having permissions on some functions. And then users
> are granted those roles. Similar to how some Linux systems ship with
> groups such as "adm".

One thought I had for this was a contrib module which added an extension
to create and grant those roles. That approach would mean that we don't
need to worry about upgrade-path problems which we could get into if we
declared new roles like 'backup' which users might already have.

An alternative approach which might be better, now that I think about
it, would be to declare that the 'pg_' prefix applies to roles too and
then have a 'pg_backup' role which is granted the correct permissions.
Personally, I like that idea a lot..

We could then have pg_upgrade throw an error and pg_dump a warning (or
something along those lines) if they find any existing roles with that
prefix.

Thanks!

Stephen