Re: Proposal: knowing detail of config files via SQL

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > 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.

I tend to agree with this approach and it's what I was advocating for in
my overall analysis of superuser checks that gave rise to the additional
role attributes idea. If it can be used to become superuser then it
doesn't make sense to have it be independent from superuser.

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

The functions identified for the new role attributes were specifically
those that, I believe, could be used without also giving the user
superuser rights. Those are:

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.

> 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?

That's what roles are for, in my mind. If we're fine with using the
grant system to control executing these functions then I would suggest
we address this by providing some pre-configured roles or even just
documentation which list what a given role would need. That's
certainly what a lot of people coming from other databases expect. The
problem with the role attribute approach is that they aren't inheirted
the way GRANTs are, which means you can't have a "backup" role that is
then granted out to users, you'd have to set a "BACKUP" role attribute
for every role added.

Thanks,

Stephen