Re: "pg_xxx" role name restriction not applied to bootstrap superuser?

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> So this seems like another reason why removing those checks was an
> improvement, but I'm left with a policy question: should initdb disallow
> bootstrap superuser names like "pg_xxx"? This doesn't seem quite
> open-and-shut. On the one hand, if we leave it as-is, then people might
> be blindsided by future additions of built-in roles. On the other,
> if we forbid the case, it seems noticeably more likely that we'll break
> existing setups, because "pg_something" doesn't seem like a terribly
> unlikely choice for the name of the Postgres OS user. (Certainly
> opossum's owner would have to fix it, so that's one example out of a
> not very large sample space of buildfarm users...) Allowing a potential
> conflict for the bootstrap superuser is a much narrower conflict risk
> than any-old-user, so maybe it's okay to leave it as is.

On the whole, I'd vote to treat the bootstrap user as a normal role and
therefore have the same restriction in place for that user also. As was
mentioned previously, it's already impossible to create schemas which
start with 'pg_', so you couldn't have a 'pg_buildfarmer' schema. I
realize that, for the buildfarm, that's not an issue, but that's a bit
of a special case.

> Also, the failure mode if you do get an actual, rather than hypothetical,
> conflict against a built-in role name isn't all that nice:
>
> $ initdb -U pg_signal_backend
> ...
> running bootstrap script ... FATAL: could not create unique index "pg_authid_rolname_index"
> DETAIL: Key (rolname)=(pg_signal_backend) is duplicated.
> ...
>
> While it's not hard to interpret this if you already know that
> "pg_signal_backend" is a reserved role name, an explicit failure message
> saying that the bootstrap superuser name can't begin with "pg_" would be
> more user-friendly. So that's a point in favor of having initdb reject
> the case.
>
> On the whole I lean to adding a restriction, but only weakly.

Agreed.

Thanks!

Stephen