301

I need to change the owner of PostgreSql database.

How to change owner of PostgreSql database in phppgadmin?

vvvvv
  • 25,404
  • 19
  • 49
  • 81
Jayashri
  • 4,073
  • 6
  • 25
  • 18

3 Answers3

512
ALTER DATABASE name OWNER TO new_owner;

See the Postgresql manual's entry on this for more details.

ctford
  • 7,189
  • 4
  • 34
  • 51
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 17
    @mArtinko5MB: That's impossible, ALTER doesn't DROP a database. – Frank Heikens Oct 18 '13 at 14:37
  • @FrankHeikens Do tell, it was probably caused by fact, that table wasn't empty. So after every alter on that table, table was dropped (especially with alter own permission). Thanks for reply – mArtinko5MB Oct 24 '13 at 13:19
  • 17
    @mArtinko5MB: Also impossible, ALTER TABLE doesn't DROP the table. Show us your SQL, something is badly broken in your statements. – Frank Heikens Oct 24 '13 at 13:24
  • 27
    Note, all tables and sequences inside the database will still be assigned to the original owner. – Cerin Nov 02 '16 at 00:52
  • 3
    `ERROR: must be member of role ...` = [DOES NOT WORK WITH RDS](https://stackoverflow.com/questions/26684643/error-must-be-member-of-role-when-creating-schema-in-postgresql/37331821) – Michael M Jul 25 '18 at 07:44
  • For more clarity; the term 'name' is the name of the database whose attributes are to be altered. – geobudex Feb 11 '19 at 05:27
74

Frank Heikens answer will only update database ownership. Often, you also want to update ownership of contained objects (including tables). Starting with Postgres 8.2, REASSIGN OWNED is available to simplify this task.

IMPORTANT EDIT!

Never use REASSIGN OWNED when the original role is postgres, this could damage your entire DB instance. The command will update all objects with a new owner, including system resources (postgres0, postgres1, etc.)

First, connect to admin database and update DB ownership:

psql
postgres=# REASSIGN OWNED BY old_name TO new_name;

This is a global equivalent of ALTER DATABASE command provided in Frank's answer, but instead of updating a particular DB, it change ownership of all DBs owned by 'old_name'.

The next step is to update tables ownership for each database:

psql old_name_db
old_name_db=# REASSIGN OWNED BY old_name TO new_name;

This must be performed on each DB owned by 'old_name'. The command will update ownership of all tables in the DB.

Antwane
  • 20,760
  • 7
  • 51
  • 84
  • 16
    Nice! ...unless the owner is `postgres`, himself... Learned that the hard way. – Chris Mar 18 '18 at 22:15
  • 3
    The problem is that it does not change the owner of a single database, but it replaces the owner everywhere by the new one. – Michael003 Oct 16 '18 at 12:35
  • 1
    The above REASSIGN OWNED is going to change all the database (if there are multiple databases) in a same instance to the new role. – varun7447 Mar 09 '20 at 23:48
  • 2
    For an alternative to `REASSIGN OWNED` (typically because your owner is `postgres`), see the snippets in https://stackoverflow.com/a/2686185/1839209. – Michael Herrmann Apr 15 '20 at 07:46
0

A slight change to answer proposed by Frank. While the query is correct, one may execute the query without making any changes to the ownership.

This happens when we login as the olduser and run psql in the terminal and do not specify the database.

Instead , after logging with older user.. type the dbname with psql

Terminal prompt will change. After running the command, you will see the output as "ALTER DATABASE". You can verify the ownership with '\list' command

$ psql <<dbname>>
dbname=# ALTER DATABASE name OWNER TO new_owner;

ALTER DATABASE
Aditya Wagholikar
  • 133
  • 6