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.
454
votes
24 Answers
494
votes
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.
577
votes
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.
256
votes
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 *.*
43
votes
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.
29
votes
23
votes
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$$;
21
votes
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
21
votes
18
votes
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;
11
votes
6
votes
There is no such command in PostgreSQL. But you can work around it using method I described some time ago for GRANTs.
3
votes
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
$$;
2
votes
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
2
votes
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.
2
votes
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.
1
votes
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:
TABLESSEQUENCESFUNCTIONS
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:
BEGINa transaction to modify the privs- Change ownership of
DATABASESto a "DBA role" - Change ownership of
SCHEMASto the "DBA role" REVOKE ALLprivs on allTABLES,SEQUENCESandFUNCTIONSfrom all rolesGRANT SELECT, INSERT, UPDATE, DELETEon relevant/appropriate tables to the appropriate rolesCOMMITthe DCL transaction.
1
votes
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};"
1
votes
1
votes
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
0
votes
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
0
votes
0
votes
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
$$;
0
votes
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: "{{ postgres_admin_username }}"
login_unix_socket: "{{postgres_socket}}"
db: "db-name"
new_owner: "new-owner"
reassign_owned_by: "old-owner"
obj_type: "table"
Here is the documentation with some further information: https://docs.ansible.com/ansible/latest/collections/community/general/postgresql_owner_module.html
Hope that helps.
0
votes
From the others discussions, that don't agree for my own problem. As said, REASSIGN OWNED don't work if owner is postgres. So, I wrote this script, inspired by discussions before :
CREATE OR REPLACE FUNCTION public.alt_own_onall (v_new_owner text)
RETURNS void
LANGUAGE plpgsql
AS $alt_own_onall$
-- ALTer OWNer ON ALL objects
DECLARE
r RECORD;
v_sqlcmd TEXT; -- commande SQL
b_modif BOOLEAN DEFAULT false; -- si au moins une modif
BEGIN
v_new_owner := quote_ident (v_new_owner);
IF v_new_owner NOT IN
(SELECT role_name FROM information_schema.enabled_roles WHERE role_name <>'postgres')
THEN
RAISE WARNING '[%] est inconnu', v_new_owner;
RETURN;
END IF
; -- tables
RAISE INFO 'Le nouveau propriétaire des tables, vues, fonctions, schémas et de la base va être [%]', v_new_owner
;
FOR r IN
SELECT quote_ident(schemaname) || '.' || quote_ident(tablename) sch_tbl
FROM pg_catalog.pg_tables
WHERE schemaname !~'^(pg_|information)' AND tableowner <> v_new_owner
LOOP
v_sqlcmd := 'ALTER TABLE ' || r.sch_tbl || ' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
; -- views
FOR r IN
SELECT quote_ident(schemaname) || '.' || quote_ident(viewname) v_sch_nam
FROM pg_catalog.pg_views
WHERE schemaname !~'^(pg_|information)' AND viewowner <> v_new_owner
LOOP
v_sqlcmd := 'ALTER VIEW '|| r.v_sch_nam ||' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
; -- séquences
/*
ERROR: cannot change owner of sequence "*_seq"
DETAIL: Sequence "*_seq" is linked to table "*".
FOR r IN
SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) s_sch_nam
FROM information_schema.sequences
WHERE sequence_schema !~'^(pg_|information)'
LOOP
EXECUTE 'ALTER SEQUENCE ' || r.s_sch_nam || ' OWNER TO ' || v_new_owner || ';'
;
END LOOP
; -- fonctions */
FOR r IN
SELECT
quote_ident (n.nspname) || '.' || quote_ident (p.proname) || '(' ||
pg_get_function_identity_arguments (p.oid) || ')' AS nsp_pro_arg
FROM pg_proc AS p
JOIN pg_namespace AS n ON p.pronamespace = n.oid
JOIN pg_authid AS a ON p.proowner = a.oid
WHERE n.nspname !~'^(pg_|information)' AND quote_ident(a.rolname) <> v_new_owner
LOOP
v_sqlcmd := 'ALTER FUNCTION ' || r.nsp_pro_arg || ' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
; -- schémas
FOR r IN
SELECT quote_ident(schema_name) s_nam
FROM information_schema.schemata
WHERE schema_name !~'^(pg_|information)' AND quote_ident(schema_owner) <> v_new_owner
LOOP
v_sqlcmd := 'ALTER SCHEMA ' || r.s_nam || ' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
; -- base
FOR r IN
SELECT d.datname, a.rolname
FROM pg_database d JOIN pg_authid a ON d.datdba=a.oid
WHERE d.datname=current_database() AND a.rolname <> v_new_owner
LOOP
v_sqlcmd := 'ALTER DATABASE ' || quote_ident(current_database()) || ' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
;
IF NOT b_modif THEN RAISE INFO 'Aucune modification n''a été effectuée.'; END IF;
END
;$alt_own_onall$;
to use it under psql : SELECT alt_own_onall ('new_role_name'); You have to translate messages from french to your own language. Works on pg 9.5
