The documentation for ALTER PUBLICATION ... OWNER TO ... claims the new owner must have CREATE privilege on the
database,though superuser can change the ownership in spite of this restriction. No explanation is given for this
requirement. It seems to just mirror the requirement that many types of objects which exist within namespaces cannot be
transferredto new owners who lack CREATE privilege on the namespace. But is it rational to follow that pattern here?
Iwould expect it to follow more closely the behavior of objects which do not exist within namespaces, like
AlterSchemaOwneror AlterForeignServerOwner which don't require this. (There are other examples to look at, but those
requirethe new owner to be superuser, so they provide no guidance.)
During the development of the feature, Peter E. says in [1], "I think ALTER PUBLICATION does not need to require CREATE
privilegeon the database." Petr J. replies in [2], "Right, I removed the check." and the contents of the patch file
0002-Add-PUBLICATION-catalogs-and-DDL-v12.patchconfirm this. After the feature was first committed in 665d1fad99,
Peterupdated it in commit 4cfc9484d4, but the reasoning for bringing back this requirement is not clear, as the commit
messagejust says, "Previously, the new owner had to be a superuser. The new rules are more refined similar to other
objects." The commit appears not to have had a commitfest entry, nor does it have any associated email discussion that
Ican find.
To investigate, I edited all 22 scripts in src/test/subscription/t/ assigning ownership of all publications to
nonsuperuserroles which lack CREATE before the rest of the test is run. Nothing changes. Either the tests are not
checkingthe sort of thing this breaks, or this breaks nothing. I also edited src/backend/commands/publicationcmds.c
circaline 693 to only raise a warning when the assignee lacks CREATE rather than an error and then ran check-world with
TAPtests enabled. Everything passes. So no help there in understanding why this requirement exists.
Assuming the requirement makes sense, I'd like the error message generated when the assignee lacks CREATE privilege to
beless cryptic:
ALTER PUBLICATION testpub OWNER TO second_pub_owner;
ERROR: permission denied for database regression
But since similarly cryptic messages are produced for other object types that follow this pattern, maybe that should be
aseparate thread.
[1] https://www.postgresql.org/message-id/acbc4035-5be6-9efd-fb37-1d61b8c35ea5%402ndquadrant.com
[2] https://www.postgresql.org/message-id/ed24d725-1b8c-ed25-19c6-61410e6b1ec6%402ndquadrant.com
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
