Re: CATUPDATE confusion? - Mailing list pgsql-hackers

On Mon, Nov 24, 2014 at 04:28:46PM -0500, Adam Brightwell wrote:
> So, where I find this confusing is that the documentation supports this
> functionality and the check keeps superuser separate from CATUPDATE...
> however... comments and implementation in user.c state/do the opposite and
> couple them together.
> 
> Documentation:
> http://www.postgresql.org/docs/9.4/static/catalog-pg-authid.html - "Role
> can update system catalogs directly. (Even a superuser cannot do this
> unless this column is true)"
> 
> src/backend/commands/user.c
> 
> /* superuser gets catupdate right by default */
> new_record[Anum_pg_authid_rolcatupdate - 1] = BoolGetDatum(issuper);
> 
> and...
> 
> /*
>  * issuper/createrole/catupdate/etc
>  *
>  * XXX It's rather unclear how to handle catupdate.  It's probably best to
>  * keep it equal to the superuser status, otherwise you could end up with
>  * a situation where no existing superuser can alter the catalogs,
>  * including pg_authid!
>  */
> if (issuper >= 0)
> {
>   new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(issuper > 0);
>   new_record_repl[Anum_pg_authid_rolsuper - 1] = true;
> 
>   new_record[Anum_pg_authid_rolcatupdate - 1] = BoolGetDatum(issuper > 0);
>   new_record_repl[Anum_pg_authid_rolcatupdate - 1] = true;
> }

That documentation is correct as far it goes.  It does neglect to mention
that, as you have discovered, any CREATE ROLE or ALTER ROLE [NO]SUPERUSER will
change rolcatupdate to match.

> Perhaps this is not an issue but it seemed odd to me, especially after
> giving Peter's comment more thought.  So, I suppose the question is whether
> or not a superuser check should be added to 'has_rolcatupdate' or not?  I

I would not.  PostgreSQL has had this feature since day one (original name
"usecatupd").  It has two use cases, (1) giving effect to non-superuser
catalog grants and (2) preventing a narrow class of superuser mistakes.  We
wouldn't add such a thing today, but one can safely ignore its existence.
Making has_rolcatupdate() approve superusers unconditionally would exclude use
case (2).  Neither use case is valuable, but if I had to pick, (2) is more
valuable than (1).

> believe I understand the reasoning for coupling the two at role
> creation/alter time, however, is there really a case where a superuser
> wouldn't be allowed to bypass this check?  Based on the comments, there
> seems like there is potentially enough concern to allow it.  And if it is
> allowed, couldn't CATUPDATE then be treated like every other attribute and
> the coupling with superuser removed?  Thoughts?

A superuser always has _some_ way to bypass the check, if nothing else by
loading a C-language function to update pg_authid.  The user.c code you quoted
ensures that, if the DBA manages to remove rolcatupdate from every user, a
simple CREATE ROLE or ALTER ROLE can fix things.