Re: Proposal: knowing detail of config files via SQL

On Tue, Mar 3, 2015 at 10:29 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> -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.

And the same issue comes up for the pg_hba_settings patch.

Fwiw it's not entirely true that users can't change the permissions on
these functions via DDL -- it's just a lot harder. They have to create
a security-definer wrapper function and then grant access to that
function to who they want.

I'm generally of the opinion we should use the GRANT system and not
hard-wire things but I also see the concern that it's really easy to
grant access to something without realizing all the consequences. It's
a lot harder to audit your system to be sure nothing inappropriate has
been granted which can be escalated to super-user access. It's not
clear how to determine what the set of things are that could do that.

--
greg