Menu
To alter the owner, you must own the database and also be a direct or indirect member of the new owning role, and you must have the CREATEDB privilege. (Note that superusers have all these privileges automatically.) The fourth form changes the default tablespace of the database.
It is possible to set up multiple logins to act as the database owner: Create a "nologin" role to act as the owner: CREATE ROLE dbowner NOLOGIN. Change the owner of your database to this: ALTER DATABASE mydb OWNER TO dbowner. Grant all your logins to this new role: GRANT dbowner TO user1, user2.
Interestingly, the solution was quite simple: select tablename, tableowner from pg_catalog. pg_tables where schemaname = 'public' ; All of the schemas in our db are the default public , so to eliminate some of the tables Postgres provides, I included that filter.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With