More Postgres Magic


[REASSIGN OWNED BY old_role new_role] (http://www.postgresql.org/docs/9.0/static/sql-reassign-owned.html)

This command saved my bacon today. I had to apply some scripts that dropped a couple of database objects. The role I was using did not have ownership of the objects in question, so the script failed. I needed to change ownership so the scripts would run correctly.

REASSIGN OWNED BY does just what it says - log into the database you want to effect, and run the command. It transfers ownership from the old role to the new role for all objects in the database.

postgres=# create database ownage;
CREATE DATABASE
postgres=# grant all on database ownage to old_owner;
GRANT
postgres=# grant all on database ownage to owner_wannabe;
GRANT
postgres=# \q

psql ownage -U old_owner -h localhost

ownage=# create table users (id serial, username text, password text);
NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
CREATE TABLE
ownage=# create table things (id serial, user_id integer, name text);
NOTICE:  CREATE TABLE will create implicit sequence "things_id_seq" for serial column "things.id"
CREATE TABLE
ownage=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | things        | table    | old_owner
 public | things_id_seq | sequence | old_owner
 public | users         | table    | old_owner
 public | users_id_seq  | sequence | old_owner
(4 rows)

ownage=# reassign owned by old_owner to owner_wannabe;
REASSIGN OWNED
ownage=# \d
                 List of relations
 Schema |     Name      |   Type   |     Owner     
--------+---------------+----------+---------------
 public | things        | table    | owner_wannabe
 public | things_id_seq | sequence | owner_wannabe
 public | users         | table    | owner_wannabe
 public | users_id_seq  | sequence | owner_wannabe
(4 rows)

With that, we no longer have to iterate over objects one by one and alter owner.

And that, my friends is more Postgres magic.


comments powered by Disqus