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.

postgresql

shareimprove this question

edited Feb 5 '14 at 19:54

newUserNameHere

6,571113260

asked Aug 28 '09 at 16:32

Kai

4,68352128

add a comment

19 Answers

active oldest votes

up vote

378

down vote

accepted

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 table \"$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 table \"$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.

shareimprove this answer

edited Oct 2 '17 at 11:45

Janusz Skonieczny

6,52273349

answered Apr 21 '10 at 20:05

Alex Soto

4,4031147

9

+1 Thanks Alex. I've created a little bash script based on your answer, available at gist.github.com/2482969 – gingerlime Apr 24 '12 at 19:33

9

See the recent answer by @trygvis. Simplest answer by far: REASSIGN OWNED BY old_role [, ...] TO new_role – David Dec 4 '12 at 23:39

47

REASSIGN OWNED BY doesn't work for objects owned by postgres. – BrunoJCM Apr 1 '13 at 17:31

14

Also, REASSIGN OWNED actually affects the ownership of all databases owned by the old role (See: postgresql.org/docs/9.3/static/sql-reassign-owned.html). So if you only want to change the ownership of a single database, beware! – kitsune Oct 31 '14 at 14:24

2

Based on @gingerlime script, bspkrs (couldn't find his name) created one that also changes the functions: https://gist.github.com/bspkrs/b997ed7f1eb1268f3403 – elysch May 16 '16 at 15:15

show 6 more comments

up vote

453

down vote

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.

shareimprove this answer

edited Dec 5 '12 at 12:55

Konrad Garus

36.4k31126200

answered Nov 23 '12 at 20:09

Trygve Laugstøl

5,36322737

This works exactly as you imagine. Perfect and thanks. – Justin Van Horne Dec 21 '12 at 6:37

17

WAY better solution. This should be the accepted answer – vicTROLLA Feb 16 '13 at 22:42

27

This doesnt seem to work for user postgres, even though I am connected to a database that I created (i.e. not a system database), it says this: ERROR: cannot reassign ownership of objects owned by role postgres because they are required by the database system – thnee Mar 8 '13 at 23:19

12

As @thnee reported, REASSIGN affects all objects in the database and it doesn't discriminate between user defined and system objects, so it doesn't work for postgres if there are any extension having their own tables. Still I prefer (+1) this option for elegance, even though it didn't help me much (my database was previously owned by postgres). – Pavel V. Jul 3 '14 at 6:21

2

Just to be clear, this command works in the database that you are currently connected ONLY. If the old_role owns objects in multiple databases, you should connect and run this command in each one of those databases – mavroprovato Jul 7 '16 at 9:08

show 3 more comments

up vote

143

down vote

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 || '.' || 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;

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 *.*

shareimprove this answer

edited May 23 '17 at 11:55

Community♦

11

answered Jul 8 '11 at 12:27

rkj

4,85722030

3

A very nice solution! – jb. May 24 '12 at 17:10

1

Best in all. This worked a treat mate. – Makky Mar 25 '13 at 13:40

Great solution. My only issue was that I had export the scripts and then execute the exported scripts. I am SQL Server Guru but I am not sure what the shortcut is to execute. I clicked execute query and execute pgScript. What was I doing wrong? – Tyrone Moodley Apr 11 '15 at 12:46

I preferred this as it works from within plsql once logged in - the unix level scripts (currently favourite answer) require a "-U postgres" and password entry in my environment. – Dazed Apr 30 '15 at 8:57

2

I prefer this answer because (1) it can be done in psql or pgAdmin (2) it easily allows you to see the objects you'll be altering. I also used stackoverflow.com/questions/22803096/…, which is similar, but for functions. – AlannaRose Jun 29 '15 at 21:33

show 1 more comment

up vote

38

down vote

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 <> '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.

shareimprove this answer

edited Apr 2 '12 at 14:11

answered Jul 27 '11 at 23:33

Johan Dahlin

13.4k53050

1

This is much more useful since it changes ownership of the entire schema, including functions, indexes, sequences, etc.. Thank you! – liviucmg Oct 31 '11 at 20:18

It does not change schema owners. How to change schema owners also? – Andrus Jul 30 '12 at 14:46

@Andrus ALTER DATABASE $DB OWNER TO $OWNER; – Johan Dahlin Jul 31 '12 at 19:04

alter database changes whole database owner. I asked how to change schema owners. – Andrus Jun 20 '14 at 20:01

ALTER SCHEMA fred OWNER TO betty; – Eric Aldinger Apr 16 at 21:41

add a comment

up vote

17

down vote

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

shareimprove this answer

answered Aug 21 '10 at 1:29

magiconair

2,36132026

add a comment

up vote

15

down vote

very simple, try it...

select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';

shareimprove this answer

answered Feb 6 '13 at 13:02

mwendamseke

18915

4

You might add a note that the corresponding strings have to be copied and executed. Not that it's not obvious :p – Nightscape May 13 '13 at 13:55

Which includes removing all the quotes around the alter statements.. multi-cursors or replace helps in this case. – knownasilya Oct 13 '14 at 13:31

add a comment

up vote

12

down vote

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.

shareimprove this answer

answered Apr 11 '16 at 2:40

durenzo

12112

1

This probably does what the querent wanted. By far the easiest. – Geof Sawaya Feb 10 '17 at 10:55

You're only 4 years late to the party; scroll up: stackoverflow.com/a/13535184/1772379 – Ben Johnson Dec 5 '17 at 14:53

add a comment

up vote

12

down vote

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 := '<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 = 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$$;

shareimprove this answer

edited May 23 '17 at 11:47

Community♦

11

answered May 16 '16 at 17:22

elysch

93621430

AMAZING! Why postgres does not add this I do not know! – pip May 20 '16 at 12:51

Two questions: 1) It looks like the first and third "ALTER TABLE" lines are dupes. Is that intentional (e.g. do you have to do two passes over the tables to change ownership?). 2) We're finding that information_schema.sequences is empty even though SELECT c.* FROM pg_class c WHERE c.relkind = 'S'; lists sequences. Why might they not match? – GuyPaddock Sep 13 '17 at 18:27

Also, shouldn't the second ALTER query be an ALTER SEQUENCE? – GuyPaddock Sep 13 '17 at 18:34

add a comment

up vote

9

down vote

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$$;

shareimprove this answer

answered Apr 22 '14 at 13:07

user3560574

9111

add a comment

up vote

7

down vote

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

shareimprove this answer

answered Aug 28 '09 at 16:50

user80168

Thank you, very nice article. I will keep this as a future reference. Using pgAdmin, I ended up backing up the DB, dropping/deleting the DB, temporarily granting new_owner the necessary rights, and then re-creating and restoring DB as the new_owner, with the "no owner" option checked in the restore window. This produced the results I was looking for with new_owner as the owner of everything. – Kai Aug 28 '09 at 18:23

add a comment

up vote

3

down vote

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

$$;

shareimprove this answer

edited May 23 '17 at 12:18

Community♦

11

answered Jun 9 '16 at 9:05

JC Boggio

1105

add a comment

up vote

2

down vote

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

shareimprove this answer

answered Jun 15 '12 at 5:46

Sharoon Thomas

1,2111212

add a comment

up vote

1

down vote

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.

shareimprove this answer

answered Feb 9 '12 at 17:11

Sean

7,44632838

add a comment

up vote

1

down vote

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

shareimprove this answer

edited Sep 14 '12 at 10:48

Sicco

4,60933251

answered Dec 5 '10 at 20:02

atwsKris

115

add a comment

up vote

1

down vote

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};"

shareimprove this answer

answered Oct 7 '12 at 20:58

jsh

11110

add a comment

up vote

1

down vote

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.

shareimprove this answer

answered Aug 14 '14 at 20:42

Jakub Jirutka

5,45122530

Works great for me. This is what many would be looking for. – Chand Prakash Nov 13 '15 at 11:36

add a comment

up vote

1

down vote

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.

shareimprove this answer

edited Feb 19 '15 at 21:57

Brad Koch

9,5491271103

answered Dec 8 '14 at 10:46

sramay

18113

add a comment

up vote

1

down vote

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

shareimprove this answer

answered Sep 6 '17 at 9:49

Anton Smolkov

111

add a comment

up vote

0

down vote

Docker: Modify Owner of all Tables + Sequences

export user="your_new_owner"

export dbname="your_db_name"

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