MyTetra Share
Делитесь знаниями!
PostgreSQL Change owner of all tables under a specific schema
Время создания: 13.07.2018 15:30
Текстовые метки: postgresql
Раздел: Postgresql

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?


postgresql permissions

shareimprove this question

edited Dec 6 '17 at 16:47


Evan Carroll

24.8k542162

asked Oct 25 '13 at 9:40


Twisted Fate

56113

add a comment

6 Answers

active oldest votes

up vote

7

down vote

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.


shareimprove this answer

edited Jun 13 '16 at 14:09

answered Nov 24 '14 at 20:15


jrial

7913

add a comment

up vote

3

down vote

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


shareimprove this answer

answered Dec 6 '17 at 16:44


Evan Carroll

24.8k542162

add a comment

up vote

2

down vote

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


shareimprove this answer

answered Dec 6 '17 at 16:32


Ali Saeed

1213

add a comment

up vote

1

down vote

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


shareimprove this answer

edited Dec 19 '17 at 15:43


sp_BlitzErik

16.5k85192

answered Dec 19 '17 at 14:45


yatabani

112

add a comment

up vote

0

down vote

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


shareimprove this answer

edited Jun 13 '16 at 14:37


dezso

20.2k95186

answered Oct 15 '15 at 19:18


riley

1011

add a comment

up vote

0

down vote

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;

Так же в этом разделе:
 
MyTetra Share v.0.53
Яндекс индекс цитирования