Why does PostgreSQL reassign role command, change template database's owner

Question

Imagine a database existing called store_a with one table products. There is also a user called store_a_user. The database, table, data, objects, etc. are owned by store_a_user.

I now create a new user called store_b_user and a new database called store_b by templating the database store_a with this command CREATE DATABASE store_b WITH TEMPLATE store_a OWNER store_b_user. This creates the new database store_b as an exact copy of store_a. The only problem is the data/objects are still owned by store_a_user. In order to fix this I have to connect to the store_b database as the postgres user to reassign all of store_a_user's roles to store_b_user by running this command REASSIGN OWNED BY store_a_user TO store_b_user.

As the Postgres documentation shows for the reassign owned command. In order to reassign the role, you have to connect to the database in question as well as connect with a user who has privileges over both users (in our case store_a_user and store_b_user). Direct quote from the documentation:

Because REASSIGN OWNED does not affect objects within other databases, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.

Once REASSIGN OWNED BY store_a_user TO store_b_user is ran everything inside of store_b is now owned by store_b_owner which is exactly what I want. The only problem is running that command changes the owner of store_a to store_b_user, but only the owner of the table not all of the data inside of store_a.

The documentation also states that it will affect all "shared objects" owned by the old role and reassigned to the new, direct quote from documentation:

...and of all shared objects (databases, tablespaces), owned by this role will be reassigned to new_role

I have a couple of questions:

1. What is a shared object? (I can't seem to find documentation on this)
2. Why does the original store_a table change ownership under the reassign owned command when ran with a connection on store_b as the postgres user?
3. I don't want the behavior described above but if this behavior is suppose to happen or can't be avoided am I forced to manually reassign ownership to store_a?

Answer 1

You have analyzed the situation quite well.

Answer to 1

A shared object is one that does not belong to a single database, but to the whole database cluster. Examples are pg_authid, which contains the users that are common to all databases, and pg_database, which contains all databases.

Shared objects are in a special tablespace pg_global that corresponds to the global subdirectory in the data directory.

Answer to 2

Because, as the documentation you quote says, shared objects are also affected, and the database store_a is a shared object.

The objects in database store_a are not global objects and are not affected.

Answer to 3

You will have to change the ownership of database store_a back after you run REASSIGN OWNED. There is no better way to do it.

Be comforted: things were much harder before version 8.2, when REASSIGN OWNED was introduced.