From time to time it might be required to move objects (tables, indexes, whatever) from one user to another user in a database system. You could do that by dumping all the objects with pg_dump and then load it to the target user. But there are other solutions which are faster than that. Lets go.
Obviously we need two users when we want to move objects from one user to another (actually it is not really moving the objects but more about changing the ownership):
postgres=# create role a login password 'a'; CREATE ROLE postgres=# create role b login password 'b'; CREATE ROLE postgres=# create schema a authorization a; CREATE SCHEMA postgres=# create schema b authorization b; CREATE SCHEMA postgres=# alter role a set search_path=a; ALTER ROLE postgres=# alter role b set search_path=b; ALTER ROLE postgres=#
Lets create some objects in schema “a” owned by user “a”:
postgres=# c postgres a You are now connected to database "postgres" as user "a". postgres=> ! cat a.sql create table a ( a int ); create table b ( a int ); create table c ( a int ); create table d ( a int ); create index i1 on a (a); create index i2 on b (a); create index i3 on c (a); create index i4 on d (a); postgres=> i a.sql CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX
By joining pg_class and pg_roles we can verify who is actually the owner of the objects:
postgres=> select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'a'; relname | rolname ---------+--------- a | a b | a c | a d | a i1 | a i2 | a i3 | a i4 | a (8 rows)
The easiest way to make these objects owned by another user (call it “c”) would be:
postgres=# alter user a rename to c; NOTICE: MD5 password cleared because of role rename ALTER ROLE postgres=# select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'c'; relname | rolname ---------+--------- a | c b | c c | c d | c i1 | c i2 | c i3 | c i4 | c (8 rows)
Not a good idea though as the schema still is named “a” and this at least will create some confusion with the naming. Of course we could rename the schema as well:
postgres=# alter schema a rename to c; ALTER SCHEMA postgres=# c postgres c You are now connected to database "postgres" as user "c". postgres=> select count(*) from a; 2017-07-28 15:51:25.499 CEST [3415] ERROR: relation "a" does not exist at character 22 2017-07-28 15:51:25.499 CEST [3415] STATEMENT: select count(*) from a; ERROR: relation "a" does not exist LINE 1: select count(*) from a;
… but now we have another mess. Because the search_path is still set to “a” we can not see the objects by default but we will need to use the fully qualified name:
postgres=> select count(*) from c.a; count ------- 0 (1 row)
Finally we would need to adjust the search_path to get back the previous behavior:
postgres=> set search_path=c; SET postgres=> select count(*) from a; count ------- 0 (1 row)
A lot of steps to follow. Easier is:
postgres=# reassign owned by c to b; REASSIGN OWNED postgres=# alter user b set search_path=c,b; ALTER ROLE postgres=# c postgres b You are now connected to database "postgres" as user "b". postgres=> select count(*) from a; count ------- 0 (1 row) postgres=> select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'b'; relname | rolname ---------+--------- d | b i4 | b c | b i3 | b b | b i2 | b a | b i1 | b (8 rows)
Cool 🙂 There is also a command to drop all objects of a user:
postgres=> drop owned by b; DROP OWNED postgres=> select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'b'; relname | rolname ---------+--------- (0 rows)
Nice …