How can I change ownership of a PostgreSQL database in phppgadmin?
Add a comment
|
3 Answers
ALTER DATABASE name OWNER TO new_owner;
See PostgreSQL manual's entry on this for more details.
9 Comments
Frank Heikens
@mArtinko5MB: That's impossible, ALTER doesn't DROP a database.
Frank Heikens
@mArtinko5MB: Also impossible, ALTER TABLE doesn't DROP the table. Show us your SQL, something is badly broken in your statements.
Cerin
Note, all tables and sequences inside the database will still be assigned to the original owner.
Michael M
ERROR: must be member of role ... = DOES NOT WORK WITH RDS
Rafs
I solved it, the issue was I needed to GRANT newrole TO ownerrole; as ownerrole had
ADMIN option but not SET option, so couldn't set the newrole |
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.
6 Comments
Chris
Nice! ...unless the owner is
postgres, himself... Learned that the hard way.
Michael003
The problem is that it does not change the owner of a single database, but it replaces the owner everywhere by the new one.
varun7447
The above REASSIGN OWNED is going to change all the database (if there are multiple databases) in a same instance to the new role.
Michael Herrmann
For an alternative to
REASSIGN OWNED (typically because your owner is postgres), see the snippets in stackoverflow.com/a/2686185/1839209.
user1133275
What about ALTER LARGE OBJECT large_object_oid OWNER TO new_owner ?
|
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
