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 transferred to new owners who lack CREATE privilege on the namespace. But is it rational to follow that pattern here? I would expect it to follow more closely the behavior of objects which do not exist within namespaces, like AlterSchemaOwner or AlterForeignServerOwner which don't require this. (There are other examples to look at, but those require the 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 privilege on 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.patch confirm this. After the feature was first committed in 665d1fad99, Peter updated it in commit 4cfc9484d4, but the reasoning for bringing back this requirement is not clear, as the commit message just 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 I can find. To investigate, I edited all 22 scripts in src/test/subscription/t/ assigning ownership of all publications to nonsuperuser roles which lack CREATE before the rest of the test is run. Nothing changes. Either the tests are not checking the sort of thing this breaks, or this breaks nothing. I also edited src/backend/commands/publicationcmds.c circa line 693 to only raise a warning when the assignee lacks CREATE rather than an error and then ran check-world with TAP tests 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 be less 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 a separate 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
