How to change owner of PostgreSql database?
I need to change the owner of PostgreSql database.
开发者_C百科How to change owner of PostgreSql database in phppgadmin?
ALTER DATABASE name OWNER TO new_owner;
See the Postgresql manual's entry on this for more details.
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.
精彩评论