Re: improve predefined roles documentation

On Mon, Jun 24, 2024 at 2:53 PM Nathan Bossart <nathandbossart(at)gmail(dot)com>
wrote:

> On Mon, Jun 24, 2024 at 02:44:33PM -0400, Robert Haas wrote:
>
> > I don't know what to do about pg_database_owner. I almost wonder if
> > that should be moved out of the table and documented as a special
> > case. Or maybe some more wordsmithing would add clarity. Or maybe it's
> > fine as-is.
>
> I've left it alone for now. I thought about adding something like
> "pg_database_owner does not provide any special capabilities or access
> out-of-the-box" to the beginning of the entry, but I don't have time at the
> moment to properly wordsmith the rest. If anyone else wants to give it a
> try before I get to it (probably tomorrow), please be my guest.
>

This feels like a case where why is more important than what, so here's my
first draft suggestion.

pg_database_owner owns the initially created public schema and has an
implicit membership list of one - the role owning the connected-to database.
It exists to encourage and facilitate best practices regarding database
administration. The primary rule being to avoid using superuser to own or
do things. The bootstrap superuser thus should connect to the postgres
database and create a login role, with the createdb attribute, and then use
that role to create and administer additional databases. In that context,
this feature allows the creator of the new database to log into it and
immediately begin working in the public schema.

As a result, in version 14, PostgreSQL no longer initially grants create
and usage privileges, on the public schema, to the public pseudo-role.

For technical reasons, pg_database_owner may not participate in explicitly
granted role memberships. This is an easily mitigated limitation since the
role that owns the database may be a group and any inheriting members of
that group will be considered owners as well.

David J.