PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL

I had to change the ownership of tables, views and sequences and found the great solution posted by @rjk is working fine - despite one detail: If the object names are of mixed case (e.g. "TableName"), this will fail with an "not found"-error.
To circumvent this, wrap the object names with ' " ' like this:

Tables

SELECT 'ALTER TABLE \"'|| schemaname || '.' || tablename ||'\" OWNER TO my_new_owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

Sequences

SELECT 'ALTER SEQUENCE \"'|| sequence_schema || '.' || sequence_name ||'\" OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

Views

SELECT 'ALTER VIEW \"'|| table_schema || '.' || table_name ||'\" OWNER TO my_new_owner;'
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

If current owner is not postgres you can use this:

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

But if the current owner is postgres you definitely get error so you have to use @dvanrensburg answer but if you want to execute commands in same sql use these command based on need:

Database

ALTER DATABASE target_database OWNER TO new_onwer;

Tables

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH temp as (
    SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO newuser' as command
    FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
    ORDER BY schemaname, tablename )
    SELECT command from temp
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

Sequences

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH temp as (
    SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO newuser;' as command
    FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
    ORDER BY sequence_schema, sequence_name)
    select command from temp
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

Views

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH temp as (
    SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO newuser;' as command
    FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
    ORDER BY table_schema, table_name)
    select command from temp
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

Schemas

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH schema_names as(
    SELECT distinct(schemaname) FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
    ORDER BY schemaname)
    SELECT 'ALTER SCHEMA '|| schemaname ||' OWNER TO newuser;' as command
    FROM schema_names
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

Also note the functions and other components of the database that may need to change memberships

Functions and trigger functions

    DO
    LANGUAGE plpgsql
    $$
    DECLARE
      stmt text;
    BEGIN
      FOR stmt IN
        WITH temp as(
        SELECT 'alter function '||nsp.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to newuser;' as command
        FROM pg_proc p
        JOIN pg_namespace nsp ON p.pronamespace = nsp.oid
        WHERE NOT  nsp.nspname IN ('pg_catalog', 'information_schema'))
        SELECT command FROM temp
      LOOP
        EXECUTE stmt;
      END LOOP;
    END;
    $$;

For tables you must loop that:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -U postgres -c "alter table \"$tbl\" owner to NEW_USER" YOUR_DB ; done

Reassign owned didn't work for me as I was wanted to change tables owned by postgres.

I ended up using Alex's method, however I wanted to do this from within psql. The following was sufficient for me.

DO $$
DECLARE
    rec record;
BEGIN
    FOR rec in 
        SELECT *
        FROM pg_tables
        where schemaname = 'public'
LOOP
    EXECUTE 'alter table ' || quote_ident(rec.tablename) || ' owner to new_owner';
    END LOOP;
END
$$;

This is super easy with ansible. You may also skip obj_type to modify ownership of any object types.

- name: Reassigner owner of all objects
  postgresql_owner:
    login_user: "{{ postgres_admin_username }}"
    login_unix_socket: "{{postgres_socket}}"
    db: "db-name"
    new_owner: "new-owner"
    reassign_owned_by: "old-owner"
    obj_type: "table"

Here is the documentation with some further information: https://docs.ansible.com/ansible/latest/collections/community/general/postgresql_owner_module.html

Hope that helps.

From the others discussions, that don't agree for my own problem. As said, REASSIGN OWNED don't work if owner is postgres. So, I wrote this script, inspired by discussions before :

CREATE OR REPLACE FUNCTION public.alt_own_onall (v_new_owner text)
    RETURNS void
    LANGUAGE plpgsql
AS $alt_own_onall$
-- ALTer OWNer ON ALL objects
DECLARE
    r           RECORD;
    v_sqlcmd    TEXT; -- commande SQL
    b_modif     BOOLEAN DEFAULT false;    -- si au moins une modif
BEGIN
    v_new_owner := quote_ident (v_new_owner);
    IF v_new_owner NOT IN
        (SELECT role_name FROM information_schema.enabled_roles WHERE role_name <>'postgres')
        THEN
            RAISE WARNING '[%] est inconnu', v_new_owner;
            RETURN;
    END IF
    ; -- tables
    RAISE INFO 'Le nouveau propriétaire des tables, vues, fonctions, schémas et de la base va être [%]', v_new_owner
    ;
    FOR r IN
        SELECT quote_ident(schemaname) || '.' || quote_ident(tablename) sch_tbl
        FROM pg_catalog.pg_tables
        WHERE schemaname !~'^(pg_|information)' AND tableowner <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER TABLE ' || r.sch_tbl || ' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ; -- views
    FOR r IN
        SELECT quote_ident(schemaname) || '.' || quote_ident(viewname) v_sch_nam
        FROM pg_catalog.pg_views
        WHERE schemaname !~'^(pg_|information)' AND viewowner <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER VIEW '|| r.v_sch_nam ||' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ; -- séquences
/*
    ERROR:  cannot change owner of sequence "*_seq"
    DETAIL:  Sequence "*_seq" is linked to table "*".

    FOR r IN
        SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) s_sch_nam
        FROM information_schema.sequences
        WHERE sequence_schema !~'^(pg_|information)'
    LOOP
        EXECUTE 'ALTER SEQUENCE ' || r.s_sch_nam || ' OWNER TO ' || v_new_owner || ';'
        ;
    END LOOP
    ; -- fonctions */
    FOR r IN
        SELECT
            quote_ident (n.nspname) || '.' || quote_ident (p.proname) || '(' ||
            pg_get_function_identity_arguments (p.oid) || ')' AS nsp_pro_arg
        FROM pg_proc AS p
            JOIN pg_namespace   AS n ON p.pronamespace    = n.oid
            JOIN pg_authid      AS a ON p.proowner        = a.oid
        WHERE n.nspname !~'^(pg_|information)' AND quote_ident(a.rolname) <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER FUNCTION ' || r.nsp_pro_arg || ' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ; -- schémas
    FOR r IN
        SELECT quote_ident(schema_name) s_nam
        FROM information_schema.schemata
        WHERE schema_name !~'^(pg_|information)' AND quote_ident(schema_owner) <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER SCHEMA ' || r.s_nam || ' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ; -- base
    FOR r IN
        SELECT d.datname, a.rolname
        FROM pg_database d JOIN pg_authid a ON d.datdba=a.oid
        WHERE d.datname=current_database() AND a.rolname <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER DATABASE ' || quote_ident(current_database()) || ' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ;
    IF NOT b_modif THEN RAISE INFO 'Aucune modification n''a été effectuée.'; END IF;
END
;$alt_own_onall$;

to use it under psql : SELECT alt_own_onall ('new_role_name'); You have to translate messages from french to your own language. Works on pg 9.5

Althought the following does not change the owner, but changes the role, it was what I needed to do, and when searching google I ended up on this queston, so for completeness sake I will enter this here:

For me none of the above solutions worked, I kept getting: must be owner of relation xxx. the final solution was:

GRANT olduser TO newuser;