6

I want to use the REASSIGN OWNED query to change all objects in 1 database from owner A to owner B.

Let say I have the following databases:

 postgres              | postgres        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0             | postgres        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                       |                 |          |             |             | postgres=CTc/postgres
 template1             | postgres        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                       |                 |          |             |             | postgres=CTc/postgres
 db1                   | user1           | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 db2                   | user1           | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 db3                   | user2           | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

I want to db1 and all objects inside so they are owned by user2. I run:

postgres=# \c db1
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
You are now connected to database "db1" as user "postgres".
db1=# REASSIGN OWNED BY user1 TO user2;
REASSIGN OWNED

The owner changed as it should for db1 and all of its objects. But the command also changed the owner of db2. Not the objects in db2, just the database (like and ALTER DATABASE statement):

 postgres              | postgres        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0             | postgres        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                       |                 |          |             |             | postgres=CTc/postgres
 template1             | postgres        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                       |                 |          |             |             | postgres=CTc/postgres
 db1                   | user2           | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 db2                   | user2           | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 db3                   | user2           | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Is this the normal behaviour? How can I run the REASSIGN OWNED without altering other databases?

Carlos Vásquez
  • 61
  • 1
  • 3
  • This looks like a bug. At least it's not implied at all by the [documentation](http://www.postgresql.org/docs/current/static/sql-reassign-owned.html). Do you feel like [submitting](http://www.postgresql.org/support/submitbug/) it as such? – Daniel Vérité Nov 19 '14 at 14:06
  • @DanielVérité thanks for your input. I just reported it as a bug (#12008). I will get back with any feedback from the Posgres guys. – Carlos Vásquez Nov 19 '14 at 18:40

1 Answers1

1

The documentation quoted in the comment by Daniel Vérité states:

old_role

The name of a role. The ownership of all the objects within the current database, and of all shared objects (databases, tablespaces), owned by this role will be reassigned to new_role.

so this is per spec. If this is not what you want, I think you need to state your use case more fully.

alvherre
  • 2,499
  • 20
  • 22
  • 2
    But the previous version 9.2, had [this](http://www.postgresql.org/docs/9.2/static/sql-reassign-owned.html): "_The REASSIGN OWNED command does not affect the ownership of any databases owned by the role. Use ALTER DATABASE to reassign that ownership_" which is **the opposite of the current behavior** (9.3). It's surprising, in that it comes as an unavoidable side-effect instead of as a new non-default option. – Daniel Vérité Nov 20 '14 at 18:31
  • 2
    This was changed in [this commit](http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ee22c55f5ad2e7b7032cd6c0243254d84d4496c7) as a result of [this bug report](http://www.postgresql.org/message-id/flat/E1ThhcE-0001nJ-1v@wrigleys.postgresql.org#E1ThhcE-0001nJ-1v@wrigleys.postgresql.org). – alvherre Jan 13 '15 at 20:10