Re: Proposal: knowing detail of config files via SQL

On 3/3/15 5:58 PM, Tom Lane wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> It's not a documented policy but it's certainly what a whole slew of
>> functions *do*. Including pg_start_backup, pg_stop_backup,
>> pg_switch_xlog, pg_rotate_logfile, pg_create_restore_point,
>> pg_xlog_replay_pause, lo_import, lo_export, and pg_xlog_replay_resume,
>> pg_read_file, pg_read_text_file, pg_read_binary_file, and pg_ls_dir.
>
>> Indeed, it's the larger portion of what the additional role attributes
>> are for. I'm not entirely thrilled to hear that nearly all of that work
>> might be moot, but if that's the case then so be it and let's just
>> remove all those superuser checks and instead revoke EXECUTE from public
>> and let the superuser grant out those rights.
>
> It does seem like that could be an easier and more flexible answer than
> inventing a pile of role attributes.

One issue is that privilege changes on built-in stuff (and extensions)
are not dumped. But that is fixable.

> 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".