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.
This question is related to
postgresql
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
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};"
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:
TABLESSEQUENCESFUNCTIONSPostgreSQL'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 privsDATABASES to a "DBA role"SCHEMAS to the "DBA role"REVOKE ALL privs on all TABLES, SEQUENCES and FUNCTIONS from all rolesGRANT SELECT, INSERT, UPDATE, DELETE on relevant/appropriate tables to the appropriate rolesCOMMIT the DCL transaction.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.
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
$$;
very simple, try it...
select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';
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.
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.
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$$;
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 *.*
You can use the REASSIGN OWNED command.
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.
There is no such command in PostgreSQL. But you can work around it using method I described some time ago for GRANTs.
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$$;
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.
For tables you must loop that:
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do psql -U postgres -c "alter table \"$tbl\" owner to NEW_USER" YOUR_DB ; done
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
$$;
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
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:
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;
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;
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;
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
is very simple
su - postgrespsqlREASSIGN OWNED BY [old_user] TO [new_user];\c [your database]REASSIGN OWNED BY [old_user] TO [new_user];done.
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.
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
Source: Stackoverflow.com