Re: Proposal: knowing detail of config files via SQL

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Sat, Feb 28, 2015 at 12:27 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > While this generally "works", the usual expectation is that functions
> > that should be superuser-only have a check in the function rather than
> > depending on the execute privilege.  I'm certainly happy to debate the
> > merits of that approach, but for the purposes of this patch, I'd suggest
> > you stick an if (!superuser()) ereport("must be superuser") into the
> > function itself and not work about setting the correct permissions on
> > it.
>
> -1.  If that policy exists at all, it's a BAD policy, because it
> prevents users from changing the permissions using DDL.  I think the
> superuser check should be inside the function, when, for example, it
> masks some of the output data depending on the user's permissions.
> But I see little virtue in handicapping an attempt by a superuser to
> grant SELECT rights on pg_file_settings.

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.

As for pg_stat_get_wal_senders, pg_stat_get_activity, and proably some
others, column-level privileges and/or RLS policies might be able to
provide what we want there (or possibly not), but it's something to
consider if we want to go this route.

For pg_signal_backend, we could revoke direct EXECUTE permissions on it
and then keep just the check that says only superusers can signal
superuser initiated processes, and then a superuser could grant EXECUTE
rights on it directly for users they want to have that access.  We could
possibly also create new helper functions for pg_terminate and
pg_cancel.

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.
Thanks,
    Stephen