Changer le propriétaire de toutes les tables d'une base postgres

A ma connaissance il n'existe pas de fonction qui permette de changer facilement le propriétaire de tous les contenus d'une base de donnée Postgresql.

En effet le ALTER DATABASE OWNER sur la base de donnée ne change que le propriétaire de la base mais pas des éléments qui sont contenus dans cette base

Voici donc une petite requête qui permet de lister tous les éléments de la base de données et de construire une requête de modification.

Une fois exécutée cette requête, il ne reste plus qu'à exporter son résultat dans un fichier .sql et à exécuter ce fichier

-- TYPES
SELECT 'ALTER TYPE ' || n.nspname || '.' || t.typname || ' OWNER TO nouveauproprietaire;' AS requete
FROM        pg_type t
LEFT JOIN   pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE       (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
AND     NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
AND     n.nspname NOT IN ('pg_catalog', 'information_schema')
UNION
-- TABLES
SELECT 'ALTER TABLE ' || schemaname || '.' || tablename || ' OWNER TO nouveauproprietaire;' AS requete
FROM pg_tables WHERE schemaname IN ('votreschema')
UNION
-- VIEWS
SELECT 'ALTER VIEW ' || table_schema || '.' || table_name || ' OWNER TO nouveauproprietaire;' AS requete
FROM INFORMATION_SCHEMA.views WHERE table_schema IN ('votreschema')
UNION
-- FUNCTION
SELECT 'ALTER FUNCTION ' || nspname || '.' || proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')' || ' OWNER TO nouveauproprietaire;' AS requete
FROM pg_proc pr JOIN pg_namespace n ON pr.pronamespace = n.oid
WHERE proisagg IS FALSE AND  n.nspname IN ('votreschema')
UNION
-- FUNCTION AGREGATS
SELECT 'ALTER AGGREGATE ' || nspname || '.' || proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')' || ' OWNER TO nouveauproprietaire;' AS requete
FROM pg_proc pr JOIN pg_namespace n ON pr.pronamespace = n.oid
WHERE proisagg IS TRUE AND  n.nspname IN ('votreschema')
UNION
-- SEQUENCES
SELECT 'ALTER SEQUENCE ' || sequence_schema || '.' || sequence_name || ' OWNER TO nouveauproprietaire;' AS requete
FROM information_schema.sequences
WHERE sequence_schema IN ('votreschema')
UNION
-- LARGE OBJECTS
SELECT DISTINCT  'ALTER LARGE OBJECT  ' ||  loid || ' OWNER TO nouveauproprietaire;'  AS requete
FROM pg_largeobject
ORDER BY requete;

On oubliera pas également de traiter les éventuels large Objects (blob) afin de leur redonner des permissions

REVOKE ALL ON pg_largeobject FROM PUBLIC;
GRANT SELECT (loid) ON pg_largeobject TO PUBLIC;
Par Christophe HIRON
13 mai 2014