Thread: Why does the owner of a publication need CREATE privileges on the database?

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

On Tue, Jul 27, 2021 at 11:29 PM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:
>
> 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.
>

I am not aware of the original thought process behind this but current
behavior seems reasonable because if users need to have CREATE
privilege on the database while Create Publication, the same should be
true while we change the owner to a new owner. Basically, at any point
in time, the owner of the publication should have CREATE privilege on
the database which contains the publication.

-- 
With Regards,
Amit Kapila.

Amit Kapila <amit.kapila16@gmail.com> writes:
> On Tue, Jul 27, 2021 at 11:29 PM Mark Dilger
> <mark.dilger@enterprisedb.com> wrote:
>> 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.

> I am not aware of the original thought process behind this but current
> behavior seems reasonable because if users need to have CREATE
> privilege on the database while Create Publication, the same should be
> true while we change the owner to a new owner.

I think that for most (all?) forms of ALTER, we say that you need the same
privileges as you would need to drop the existing object and create a new
one with the new properties.  From the standpoint of the privilege
system, ALTER is just a shortcut for that.

            regards, tom lane