PostgreSQL Modify OWNER on all tables simultaneously in PostgreSQL

Question

How do I modify the owner of all tables in a PostgreSQL database   I tried ALTER TABLE   OWNER TO new owner but it doesn t support the asterisk syntax

User · Answer

Docker  Modify Owner of all Tables   Sequences  export user  your new owner  export dbname  your db name   cat  lt  lt EOF   docker run -i --rm --link postgres postgres postgres sh -c  psql -h   POSTGRES PORT 5432 TCP ADDR -p   POSTGRES PORT 5432 TCP PORT -U postgres -d  dbname    grep ALTER   docker run -i --rm --link postgres postgres postgres sh -c  psql -h   POSTGRES PORT 5432 TCP ADDR -p   POSTGRES PORT 5432 TCP PORT -U postgres -d  dbname  SELECT  ALTER TABLE    schemaname       tablename    OWNER TO  user   FROM pg tables WHERE schemaname    public   SELECT  ALTER SEQUENCE    relname    OWNER TO  user   FROM pg class WHERE relkind    S   EOF

User · Answer

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  lt  gt   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

User · Answer

You can try the following in PostgreSQL 9  DO   DECLARE r record  BEGIN     FOR r IN SELECT tablename FROM pg tables WHERE schemaname    public      LOOP         EXECUTE  alter table     r tablename     owner to newowner        END LOOP  END

User · Answer

This  http   archives postgresql org pgsql-bugs 2007-10 msg00234 php is also a nice and fast solution  and works for multiple schemas in one database  Tables SELECT  ALTER TABLE     schemaname       quot      tablename     quot  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       quot      sequence name     quot  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       quot      table name     quot  OWNER TO my new owner   FROM information schema views WHERE NOT table schema IN   pg catalog    information schema   ORDER BY table schema  table name   Materialized Views Based on this answer SELECT  ALTER TABLE     oid  regclass  text     OWNER TO my new owner   FROM pg class WHERE relkind    m  ORDER BY oid   This generates all the required ALTER TABLE   ALTER SEQUENCE   ALTER VIEW statements  copy these and paste them back into plsql to run them  Check your work in psql by doing   dt      ds      dv

User · Answer

For tables you must loop that  for tbl in  psql -qAt -c  quot select tablename from pg tables where schemaname    public   quot  YOUR DB    do  psql -U postgres -c  quot alter table   quot  tbl  quot  owner to NEW USER quot  YOUR DB   done

User · Answer

very simple  try it      select  ALTER TABLE      table name      OWNER TO myuser   from information schema tables where table schema    public

User · Answer

Same as  AlexSoto s approach for functions   IFS    n    for fnc in  psql -qAt -c  SELECT          p proname                 pg catalog pg get function identity arguments p oid         FROM pg catalog pg namespace n JOIN pg catalog pg proc p ON p pronamespace   n oid WHERE n nspname    public    YOUR DB    do  psql -c  alter function  fnc owner to NEW OWNER  YOUR DB  done

User · Answer

I like this one since it modifies tables  views  sequences and functions owner of a certain schema in one go  in one sql statement   without creating a function and you can use it directly in PgAdmin III and psql    Tested in PostgreSql v9 2   DO   DECLARE r record  DECLARE     v schema varchar     public       v new owner varchar      lt NEW OWNER gt    BEGIN     FOR r IN          select  ALTER TABLE       table schema             table name       OWNER TO      v new owner        as a from information schema tables where table schema   v schema         union all         select  ALTER TABLE       sequence schema             sequence name       OWNER TO      v new owner        as a from information schema sequences where sequence schema   v schema         union all         select  ALTER TABLE       table schema             table name       OWNER TO      v new owner        as a from information schema views where table schema   v schema         union all         select  ALTER FUNCTION     nsp nspname         p proname        pg get function identity arguments p oid      OWNER TO      v new owner        as a from pg proc p join pg namespace nsp ON p pronamespace   nsp oid where nsp nspname   v schema     LOOP         EXECUTE r a      END LOOP  END      Based on answers provided by  rkj   AlannaRose   SharoonThomas   user3560574 and this answer by  a horse with no name  Thank s a lot     Better yet  Also change database and schema owner   DO   DECLARE r record  DECLARE     v schema varchar     public       v new owner varchar     admin ctes   BEGIN     FOR r IN          select  ALTER TABLE       table schema             table name       OWNER TO      v new owner        as a from information schema tables where table schema   v schema         union all         select  ALTER TABLE       sequence schema             sequence name       OWNER TO      v new owner        as a from information schema sequences where sequence schema   v schema         union all         select  ALTER TABLE       table schema             table name       OWNER TO      v new owner        as a from information schema views where table schema   v schema         union all         select  ALTER FUNCTION     nsp nspname         p proname        pg get function identity arguments p oid      OWNER TO      v new owner        as a from pg proc p join pg namespace nsp ON p pronamespace   nsp oid where nsp nspname   v schema         union all         select  ALTER SCHEMA       v schema       OWNER TO      v new owner          union all         select  ALTER DATABASE       current database         OWNER TO      v new owner      LOOP         EXECUTE r a      END LOOP  END

User · Answer

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

User · Answer

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

User · Answer

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  lt  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

User · Answer

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

User · Answer

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    BEGIN a transaction to modify the privs Change ownership of DATABASES to a  DBA role  Change ownership of SCHEMAS to the  DBA role  REVOKE ALL privs on all TABLES  SEQUENCES and FUNCTIONS from all roles GRANT SELECT  INSERT  UPDATE  DELETE on relevant appropriate tables to the appropriate roles COMMIT the DCL transaction

User · Answer

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

User · Answer

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

User · Answer

See REASSIGN OWNED command  Note  As  trygvis mentions in the answer below  the REASSIGN OWNED command is available since at least version 8 2  and is a much easier method     Since you re changing the ownership for all tables  you likely want views and sequences too   Here s what I did   Tables   for tbl in  psql -qAt -c  select tablename from pg tables where schemaname    public    YOUR DB    do  psql -c  alter table    tbl   owner to NEW OWNER  YOUR DB   done   Sequences   for tbl in  psql -qAt -c  select sequence name from information schema sequences where sequence schema    public    YOUR DB    do  psql -c  alter sequence    tbl   owner to NEW OWNER  YOUR DB   done   Views   for tbl in  psql -qAt -c  select table name from information schema views where table schema    public    YOUR DB    do  psql -c  alter view    tbl   owner to NEW OWNER  YOUR DB   done   You could probably DRY that up a bit since the alter statements are identical for all three

User · Answer

I   ve created a convenient script for that  pg change db owner sh  This script change ownership for all tables  views  sequences and functions in a database schema and also owner of the schema itself   Please note that if you wanna just change the ownership of all objects  in a particular database  owned by a particular database role  then you can simply use command REASSIGN OWNED instead

User · Answer

is very simple  su - postgres psql REASSIGN OWNED BY  old user  TO  new user    c  your database  REASSIGN OWNED BY  old user  TO  new user    done

User · Answer

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

User · Answer

The following simpler shell script worked for me      bin bash for i in   psql -U  1  -qt -c   select tablename from pg tables where schemaname   2    do psql -U  1 -c   alter table  2  i set schema  3  done   Where input   1 - username  database    2   existing schema  3   to new schema

User · Answer

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   quot    postgres admin username    quot      login unix socket   quot   postgres socket   quot      db   quot db-name quot      new owner   quot new-owner quot      reassign owned by   quot old-owner quot      obj type   quot table quot   Here is the documentation with some further information  https   docs ansible com ansible latest collections community general postgresql owner module html Hope that helps

User · Answer

Based on the answer by elysch  here is a solution for multiple schemas   DO    DECLARE    r record    i int    v schema text        public schema1 schema2 schema3      v new owner varchar     my new owner   BEGIN     FOR r IN          select  ALTER TABLE       table schema             table name       OWNER TO      v new owner        as a from information schema tables where table schema   ANY  v schema          union all         select  ALTER TABLE       sequence schema             sequence name       OWNER TO      v new owner        as a from information schema sequences where sequence schema   ANY  v schema          union all         select  ALTER TABLE       table schema             table name       OWNER TO      v new owner        as a from information schema views where table schema   ANY  v schema          union all         select  ALTER FUNCTION     nsp nspname         p proname        pg get function identity arguments p oid      OWNER TO      v new owner        as a from pg proc p join pg namespace nsp ON p pronamespace   nsp oid where nsp nspname   ANY  v schema          union all         select  ALTER DATABASE       current database         OWNER TO      v new owner      LOOP         EXECUTE r a      END LOOP      FOR i IN array lower v schema 1     array upper v schema 1      LOOP         EXECUTE  ALTER SCHEMA       v schema i        OWNER TO      v new owner       END LOOP  END

User · Answer

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

[postgresql] PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL

Docker: Modify Owner of all Tables + Sequences

Examples related to postgresql