Security - PostgreSQL 9 Administration - page 128

It does exactly what its says—assigns ownership of all database objects currently owned by
role bob to role bobs_replacement;
However, you need to have privileges on both the old and new role to do that and you need to
do it in all databases where bob owns any objects, as the REASSIGN OWNED works only on the
current database.
Reassigning ownership in older databases
REASSIGN OWNED was added to PostgreSQL in Version 8.2. If you need to change ownership
in older databases, then this can be done with Unix command-line utilities magic.
First extract the ownership assignments from schema dump:
dbuser:~$ pg_dump -s mydatabase | grep -i "alter.*owner to bob"
ALTER FUNCTION public.somefunction() OWNER TO bob;
ALTER TABLE public.directory OWNER TO bob;
ALTER TABLE public.directory_seq OWNER TO bob;
ALTER TABLE public.document_id_seq OWNER TO bob;
ALTER TABLE public.documents OWNER TO bob;
Then just replace Bob in this output with new users name and feed the commands back
to database:
dbuser:~$ pg_dump -s mydb | grep -i "owner to bob" > tmp.sql
dbuser:~$ sed -e 's/TO bob/TO bobs_replacement/' < tmp.sql | psql
mydb
Of course it is a good idea to look at the changed data first.
dbuser:~$ pg_dump -s mydb | grep -i "owner to bob" > tmp.sql
dbuser:~$ sed -e 's/TO bob/TO bobs_replacement/' < tmp.sql >tmp2.sql
dbuser:~$ less tmp2.sql
dbuser:~$ psql mydb < tmp2.sql
Again, this works on one database at a time, so you have to repeat it in all databases that
have objects owned by the role you want to delete.
Checking all users have a secure password
PostgreSQL has no built-in facilities to make sure that you are using strong passwords.
The best you can do is to make sure that all users' passwords are encrypted, and that your
pg_hba file does not allow logins with a plain password. That is, always use MD5 as login
method for users.