Re: improve predefined roles documentation - Mailing list pgsql-hackers

From David G. Johnston
Subject Re: improve predefined roles documentation
Date
Msg-id CAKFQuwbR4trZ2QgjJUcxYegRNngVS0Bp5sGznPppBNazrU+A8w@mail.gmail.com
Whole thread Raw
In response to Re: improve predefined roles documentation  (Nathan Bossart <nathandbossart@gmail.com>)
Responses Re: improve predefined roles documentation
List pgsql-hackers
On Mon, Jun 24, 2024 at 2:53 PM Nathan Bossart <nathandbossart@gmail.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.

pgsql-hackers by date:

Previous
From: Stan Hu
Date:
Subject: [PATCH v3] Fix type redefinition build errors with macOS SDK 15.0
Next
From: Michael Paquier
Date:
Subject: Re: Apparent bug in WAL summarizer process (hung state)