Thread

Re: REASSIGN OWNED BY alters objects in other database.

Kirill Reshke <reshkekirill@gmail.com> — 2025-12-30T14:05:53Z

On Tue, 30 Dec 2025, 17:59 Kirill Reshke, <reshkekirill@gmail.com> wrote:

> Hi hackers.
>
> I experience following behaviour.
>
> ```
>
> postgres=# create role u1;
> CREATE ROLE
> postgres=# create role su;
> CREATE ROLE
> postgres=# create database d1 owner u1;
> CREATE DATABASE
> postgres=# grant pg_create_subscription to u1;
> GRANT ROLE
> postgres=# \c d1 u1
> connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:
> role "u1" is not permitted to log in
> Previous connection kept
> postgres=# \c d1
> You are now connected to database "d1" as user "reshke".
> d1=# set session^C
> d1=# set session authorization u1;
> SET
> d1=> create subscription s1 CONNECTION 'password=2' PUBLICATION pb1
> with (connect = false, enabled=false);
> WARNING:  subscription was created, but is not connected
> HINT:  To initiate replication, you must manually create the
> replication slot, enable the subscription, and alter the subscription
> to refresh publications.
> CREATE SUBSCRIPTION
>
> d1=# \c postgres
> postgres=# \c d1
> d1=# \dRs
>         List of subscriptions
>  Name | Owner | Enabled | Publication
> ------+-------+---------+-------------
>  s1   | u1    | f       | {pb1}
> (1 row)
>
> d1=# \c postgres
> You are now connected to database "postgres" as user "reshke".
> postgres=# reassign owned by u1 to su;
> REASSIGN OWNED
> postgres=# \c d1
> You are now connected to database "d1" as user "reshke".
> d1=# \dRs
>         List of subscriptions
>  Name | Owner | Enabled | Publication
> ------+-------+---------+-------------
>  s1   | su    | f       | {pb1}
> (1 row)
>
> d1=#
> ```
>
>
> So, REASSIGN OWNER executed in database postgres alters subscription
> owner, which is created in another database. I am not myself confident
> that this is actually wrong... Is this a bug?
>
> --
> Best regards,
> Kirill Reshke
>


Well, I do think this is a bug, but I do not think we can do privilege
escalation using it.

I am planning to post patch which will avoid altering obj from another db.
My current idea is that records in pg_shdepent are missing database oid for
subscriptions (they are inserted with invalid oid). So, maybe good fix will
be to use MyDatabaseOid

>