REASSIGN OWNED BY alters objects in other database.

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

On 2025-Dec-30, Kirill Reshke wrote:

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?

Subscriptions are shared objects (like tablespaces, roles etc), so I
think this is working as intended.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"I love the Postgres community. It's all about doing things _properly_. :-)"
(David Garamond)

On Tue, 30 Dec 2025, 19:30 Álvaro Herrera, <alvherre@kurilemu.de> wrote:

On 2025-Dec-30, Kirill Reshke wrote:

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?

Subscriptions are shared objects (like tablespaces, roles etc), so I
think this is working as intended.

--
Álvaro Herrera 48°01'N 7°57'E —
https://www.EnterpriseDB.com/
"I love the Postgres community. It's all about doing things _properly_.
:-)"
(David Garamond)

Yep, they are shared, but subscriptions are created in database context...
So, let me give some more context here

I want to delete user, which has subscription s1 in db1 and subscription s2
in db2. I want to REASSIGN all object from db1 to db1 owner and same for
db2.
I will do REASSIGN OWNED BY ... to <db owner> in each of these database,
and then drop user. I excpect that sql I do in db1 does not affect objects
in db2... Am I wrong in this assumption? Like, subscriptions have knowledge
of which database they are belong... maybe we should use this knowledge

On 2025-Dec-30, Kirill Reshke wrote:

Yep, they are shared, but subscriptions are created in database context...

True.

I want to delete user, which has subscription s1 in db1 and subscription s2
in db2. I want to REASSIGN all object from db1 to db1 owner and same for
db2.
I will do REASSIGN OWNED BY ... to <db owner> in each of these database,
and then drop user. I excpect that sql I do in db1 does not affect objects
in db2... Am I wrong in this assumption? Like, subscriptions have knowledge
of which database they are belong... maybe we should use this knowledge

Yeah, I can see that there is merit to this idea, and I think it's not
very difficult to implement -- POC attached. Does this solve your
issue?

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

On Thu, 1 Jan 2026 at 21:51, Álvaro Herrera <alvherre@kurilemu.de> wrote:

On 2025-Dec-30, Kirill Reshke wrote:

Yep, they are shared, but subscriptions are created in database context...

True.

I want to delete user, which has subscription s1 in db1 and subscription s2
in db2. I want to REASSIGN all object from db1 to db1 owner and same for
db2.
I will do REASSIGN OWNED BY ... to <db owner> in each of these database,
and then drop user. I excpect that sql I do in db1 does not affect objects
in db2... Am I wrong in this assumption? Like, subscriptions have knowledge
of which database they are belong... maybe we should use this knowledge

Yeah, I can see that there is merit to this idea, and I think it's not
very difficult to implement -- POC attached. Does this solve your
issue?

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Hi!
Thank you for your interest in this thread and thank you for your patch.
Yes, this patch achieves behaviour I want from REASSIGN OWNED. This is
something I had in mind when I started this thread.
My internal resistance to post a patch like yours was because of the following:

I can see that REASSIGN owned will behave the way I want if the `dbid`
column in pg_shdepend would be non-zero for record with deptype = 'o'
(owner).
This would automatically drop only subscriptions from the current
database. But we create this record with dbid = 0 because of
shdepAddDependency, which thinks that classId is a shared relation
then dependency should have dbid = 0. I wonder if this is correct (for
subscriptions case).

If it is, then your patch WFM LGTM.

--
Best regards,
Kirill Reshke

On 1 Jan 2026, at 21:51, Álvaro Herrera <alvherre@kurilemu.de> wrote:

<0001-REASSIGN-OWNED-ignore-subscriptions-in-other-databas.patch>

I've took a look into that patch and it seems correct to me.

On 2 Jan 2026, at 01:41, Kirill Reshke <reshkekirill@gmail.com> wrote:

I can see that REASSIGN owned will behave the way I want if the `dbid`
column in pg_shdepend would be non-zero for record with deptype = 'o'
(owner).
This would automatically drop only subscriptions from the current
database. But we create this record with dbid = 0 because of
shdepAddDependency, which thinks that classId is a shared relation
then dependency should have dbid = 0. I wonder if this is correct (for
subscriptions case).

If it is, then your patch WFM LGTM.

I would be nice to add a regression test.

Also, Álvaro correctly stated in commit message that same fix is needed for shdepDropOwned().
And, maybe, let's consider missing_ok parameter for API consistency in get_subscription_database()?

Best regards, Andrey Borodin.

On 2 Jan 2026, at 01:41, Kirill Reshke <reshkekirill@gmail.com> wrote:

I can see that REASSIGN owned will behave the way I want if the `dbid`
column in pg_shdepend would be non-zero for record with deptype = 'o'
(owner).
This would automatically drop only subscriptions from the current
database. But we create this record with dbid = 0 because of
shdepAddDependency, which thinks that classId is a shared relation
then dependency should have dbid = 0. I wonder if this is correct (for
subscriptions case).

If it is, then your patch WFM LGTM.

After considering your approach a bit more, I started to think that what you propose might be a better option for master branch.

While looking up into pg_subscription is much easier to backpatch, making dbid real in pg_shdepend might be more future proof.
But there might be some consequences that I do not understand now.

Best regards, Andrey Borodin.