Modify OWNER on all tables simultaneously in PostgreSQL

There is no such command in PostgreSQL. But you can work around it using method I described some time ago for GRANTs.

I recently had to change the ownership of all objects in a database. Although tables, views, triggers and sequences were somewhat easily changed the above approach failed for functions as the signature is part of the function name. Granted, I have a MySQL background and am not that familiar with Postgres.

However, pg_dump allows you to dump just the schema and this contains the ALTER xxx OWNER TO yyy; statements you need. Here is my bit of shell magic on the topic

pg_dump -s YOUR_DB | grep -i 'owner to' | sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | psqL YOUR_DB

pg_dump as insert statements 
pg_dump -d -O database filename
-d ( data as inserts ) -O ( capital O is no owner )

Then pipe the backup file back in to PostgreSQL using:

psql -d database -U username -h hostname < filename

As there is no owner included then all of the created table, schema, etc, are created under the login user you specify.

I have read this could be a good approach for migrating between PostgreSQL versions as well.

This: http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php is also a nice and fast solution:

select 'alter table '|| tablename ||' owner to ;' from pg_tables where schemaname = 'public';

copy the alter table statements and run it.

If you want to do it in one sql statement, you need to define an exec() function as mentioned in http://wiki.postgresql.org/wiki/Dynamic_DDL

CREATE FUNCTION exec(text) returns text language plpgsql volatile
  AS $f$
    BEGIN
      EXECUTE $1;
      RETURN $1;
    END;
$f$;

Then you can execute this query, it will change the owner of tables, sequences and views:

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' ||
            quote_ident(s.relname) || ' OWNER TO $NEWUSER')
  FROM (SELECT nspname, relname
          FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
         WHERE nspname NOT LIKE E'pg\\_%' AND 
               nspname <> 'information_schema' AND 
               relkind IN ('r','S','v') ORDER BY relkind = 'S') s;

$NEWUSER is the postgresql new name of the new owner.

In most circumstances you need to be superuser to execute this. You can avoid that by changing the owner from your own user to a role group you are a member of.

Thanks to RhodiumToad on #postgresql for helping out with this.

Starting in PostgreSQL 9.0, you have the ability to GRANT [priv name] ON ALL [object type] IN SCHEMA where [priv name] is the typical SELECT, INSERT, UPDATE, DELETE, etc and [object type] can be one of:

• TABLES
• SEQUENCES
• FUNCTIONS

PostgreSQL's docs on GRANT and REVOKE go in to more detail regarding this. In some situations it's still required to use tricks involving the system catalogs (pg_catalog.pg_*) but it's not nearly as common. I frequently do the following:

1. BEGIN a transaction to modify the privs
2. Change ownership of DATABASES to a "DBA role"
3. Change ownership of SCHEMAS to the "DBA role"
4. REVOKE ALL privs on all TABLES, SEQUENCES and FUNCTIONS from all roles
5. GRANT SELECT, INSERT, UPDATE, DELETE on relevant/appropriate tables to the appropriate roles
6. COMMIT the DCL transaction.

The answer by @Alex Soto is the right one and the gist uploaded by @Yoav Aner also works provided there are no special characters in the table/view names (which are legal in postgres).

You need to escape them to work and I have uploaded a gist for that: https://gist.github.com/2911117

The accepted solution does not take care of function ownership following solution takes care of everything (while reviewing I noticed that it is similar to @magiconair above)

echo "Database: ${DB_NAME}"
echo "Schema: ${SCHEMA}"
echo "User: ${NEW_OWNER}"

pg_dump -s -c -U postgres ${DB_NAME} | egrep "${SCHEMA}\..*OWNER TO"| sed -e "s/OWNER TO.*;$/OWNER TO ${NEW_OWNER};/" | psql -U postgres -d ${DB_NAME}
# do following as last step to allow recovery
psql -U postgres -d postgres -c "ALTER DATABASE ${DB_NAME} OWNER TO ${NEW_OWNER};"

You can use the REASSIGN OWNED command.

Synopsis:

REASSIGN OWNED BY old_role [, ...] TO new_role

This changes all objects owned by old_role to the new role. You don't have to think about what kind of objects that the user has, they will all be changed. Note that it only applies to objects inside a single database. It does not alter the owner of the database itself either.

It is available back to at least 8.2. Their online documentation only goes that far back.

very simple, try it...

 select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';