PostgreSQL Change owner of all tables under a specific schema

Question

I am trying to change the owner of all tables under the same schema in one command line. i.e: alter table schema_name.* owner to newowner. Is there a way to accomplish that?

score 31 · Answer 1 · answered Dec 06 '17 at 16:44

31

Reassigned Owned

There is a specific privilege command that does just this, RESASSIGN OWNED. This reassigns all objects, not just ones in a specific schema.

Schema-specific

You can generate the ALTER TABLE commands with the following,

SELECT format(
  'ALTER TABLE %I.%I.%I OWNER TO %I;',
  table_catalog,
  table_schema,
  table_name,
  current_user  -- or another just put it in quotes
)
FROM information_schema.tables
WHERE table_schema = 'mySchema';

In psql, you can run them by following it immediately with \gexec

answered Dec 06 '17 at 16:44

NO WAR WITH RUSSIA

54,954
34
200
411

To make it clear, `\gexec` should be in the line of the `WHERE` clause, right after the semi-colon. – Luís de Sousa Sep 22 '21 at 08:08
Is it wise to reassign the `postgres` user to `user` then? Because maybe `postgres` must be the only owner of some specific parts of the database, that you probably don't want `user` to be the owner... – s.k Jan 21 '22 at 14:33

score 15 · Answer 2 · answered Dec 06 '17 at 16:32

If you can query the tablenames in your schema, you can generate the queries to ALTER table ownership.

For example:

 select 'ALTER TABLE ' || t.tablename || ' OWNER TO new_owner;' 
 from  pg_tables t
 where t.tableowner != 'rdsadmin';

will return the query to change ownership of all tables:

ALTER TABLE schema_version OWNER TO ali;
ALTER TABLE users OWNER TO ali; 
ALTER TABLE company OWNER TO ali;
ALTER TABLE books OWNER TO ali;
...

then you can just run these :)

jrial · Answer 3 · 2016-06-13T14:09:18.997

I don't know of any way to accomplish this purely through psql, but using bash, you can list the tables in database $DB with:

psql -tc "select tablename from pg_tables where schemaname = '${SCHEMA}';" ${DB}

And the ownership can be transferred to $OWNER with:

psql -c "alter table ${SCHEMA}.${table} owner to ${OWNER}" ${DB}

Stringing this together gives you:

 $ for table in `psql -tc "select tablename from pg_tables where schemaname = '${SCHEMA}';" ${DB}` ; do  psql -c "alter table ${SCHEMA}.${table} owner to ${OWNER}" ${DB} ; done

$DB, $SCHEMA and $OWNER represent the database, schema (usually 'public') and the new owner's name respectively.

score 1 · Answer 4 · edited Dec 19 '17 at 15:43

1

This script will do the trick.

sh change_owner.sh -n new_owner -S schema_name

sh change_owner.sh -n user1 -S public

Summary:
    Tables/Sequences/Views : 16
    Functions              : 43
    Aggregates             : 1
    Type                   : 2

found here https://github.com/trrao/PostgreSQL_Scripts

edited Dec 19 '17 at 15:43

Erik Darling

32,622
13
110
249

answered Dec 19 '17 at 14:45

yatabani

11
2

score 1 · Answer 5 · answered Mar 02 '18 at 20:24

This is a function I use for changing table, view and function ownership in a schema. It is fast, clean and a good example of how to use cursors as well. Also, no command line required.

The following will change permissions through a plpgsql function:

CREATE OR REPLACE FUNCTION YOURSCHEMA.do_changeowner(
    newowner text,
    pschem text)
  RETURNS void AS
$BODY$
declare
  tblnames CURSOR FOR
    SELECT tablename FROM pg_tables
    WHERE schemaname = pschem;
  viewnames CURSOR FOR
    SELECT viewname FROM pg_views
    WHERE schemaname = pschem;
  funcnames CURSOR FOR
    SELECT p.proname AS name, pg_catalog.pg_get_function_identity_arguments(p.oid) as params
    FROM pg_proc p 
    JOIN pg_namespace n ON n.oid = p.pronamespace 
    WHERE n.nspname = pschem;

begin

  FOR stmt IN tblnames LOOP
    EXECUTE 'alter TABLE ' || pschem || '.' || stmt.tablename || ' owner to ' || newowner || ';';
  END LOOP;
  FOR stmt IN viewnames LOOP
    EXECUTE 'alter VIEW ' || pschem || '.' || stmt.viewname || ' owner to ' || newowner || ';';
  END LOOP;
  FOR stmt IN funcnames LOOP
    EXECUTE 'alter FUNCTION ' || pschem || '.' || stmt.name || '(' ||  stmt.params || ') owner to ' || newowner || ';';
  END LOOP;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

score 0 · Answer 6 · edited Jun 13 '16 at 14:37

Similar to above using bash but I had to output in a text file and then input into psql:

$ psql -qAt -d mydatabase -c "SELECT 'ALTER TABLE '||schemaname||'.'||tablename||' \
                                      OWNER TO new_owner;' \
                                      FROM pg_tables \
                                      WHERE schemaname = 'myschema'" > data.txt


$ psql < data.txt -d mydatabase

Based on this, but database added: http://penningpence.blogspot.ca/2014/09/changing-owner-of-multiple-database.html

PostgreSQL Change owner of all tables under a specific schema

6 Answers6

Reassigned Owned

Schema-specific

Linked