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.
