Créer un utilisateur et sa base de données sous postgresql

CREATE USER <user> WITH PASSWORD '<password>';
CREATE DATABASE <dbname> TEMPLATE template0 ENCODING 'UTF-8';
ALTER DATABASE <dbname> OWNER TO <user>;
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <user>;

pg_dump -F c <dbname> > dbname.pgsql

\c <dbname>  
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

pg_restore -U <user> -d <dbname> -F c <file.pgsql>

Sur la base postgres (défaut) :

postgres=# REASSIGN OWNED BY <user> TO postgres;
postgres=# DROP OWNED BY <user>;

Sur la base à supprimer :

mydatabase=# REASSIGN OWNED BY <user> TO postgres;
mydatabase=# DROP OWNED BY <user>;
mydatabase=# DROP USER <user>;

Et enfin :

DROP DATABASE <dbname>;
DROP ROLE <user>;