Re: Proposal: knowing detail of config files via SQL

Stephen Frost <sfrost@snowman.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.

> The discussion I'm having with Peter on another thread is a very similar
> case that should be looping in, which is if we should continue to have
> any superuser check on updating catalog tables.  He is advocating that
> we remove that also and let the superuser GRANT modification access on
> catalog tables to users.

> For my part, I understood that we specifically didn't want to allow that
> for the same reason that we didn't want to simply depend on the GRANT
> system for the above functions, but everyone else on these discussions
> so far is advocating for using the GRANT system.  My memory might be
> wrong, but I could have sworn that I had brought up exactly that
> suggestion in years past only to have it shot down.

I'm a bit less comfortable with this one, mainly because the ability to
modify the system catalogs directly implies the ability to crash, corrupt,
or hijack the database in any number of non-obvious ways.  I would go so
far as to argue that if you grant me modify permissions on many of the
catalogs, I would have the ability to become superuser outright, and
therefore any notion that such a grant is any weaker than granting full
superuserness is a dangerous lie.

It may be that the same type of permissions escalation is possible with
some of the functions you mention above; but anywhere that it isn't,
I should think GRANT on the function is a reasonable solution.

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?
        regards, tom lane