19

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?

11

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

8

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.

7

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 :)

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

0

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

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.