PostgreSQL : Modifier OWNER sur toutes les tables simultanément dans PostgreSQL

Question

Comment modifier le propriétaire de toutes les tables d'une base de données PostgreSQL ?

J'ai essayé ALTER TABLE * OWNER TO new_owner mais il ne prend pas en charge la syntaxe de l'astérisque.

Answer 1

Vous pouvez utiliser le REASSIGN OWNED commandement.

Synopsis :

REASSIGN OWNED BY old_role [, ...] TO new_role

Cela change tous les objets appartenant à old_role au nouveau rôle. Vous n'avez pas besoin de penser au type d'objets que possède l'utilisateur, ils seront tous modifiés. Notez que cela ne s'applique qu'aux objets d'une seule base de données. Elle ne modifie pas non plus le propriétaire de la base de données elle-même.

Il est disponible depuis au moins la version 8.2. Leur documentation en ligne ne remonte qu'à cette date.

Answer 2

Voir REASSIGN OWNED commande

Note : Comme @trygvis mentionne dans la réponse ci-dessous le REASSIGN OWNED est disponible depuis au moins la version 8.2, et constitue une méthode beaucoup plus simple.

---

Puisque vous changez la propriété de toutes les tables, vous voulez probablement les vues et les séquences aussi. Voici ce que j'ai fait :

Les tables :

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Séquences :

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter sequence \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Vues :

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter view \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Vous pourriez probablement SEC que les déclarations d'alter sont identiques pour les trois.

---

Answer 3

Ceci : http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php est également une solution agréable et rapide, et fonctionne pour plusieurs schémas dans une base de données :

Tableaux

SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO my_new_owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

Séquences

SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

Vues

SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO my_new_owner;'
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

Vues matérialisées

Sur la base de cette réponse

SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;'
FROM pg_class WHERE relkind = 'm'
ORDER BY oid;

Cela génère tous les ALTER TABLE / ALTER SEQUENCE / ALTER VIEW copiez-les et collez-les dans plsql pour les exécuter.

Vérifiez votre travail en psql en le faisant :

\dt *.*
\ds *.*
\dv *.*

Answer 4

Si vous voulez le faire en une seule instruction sql, vous devez définir une fonction exec() comme mentionné dans http://wiki.postgresql.org/wiki/Dynamic_DDL

CREATE FUNCTION exec(text) returns text language plpgsql volatile
  AS $f$
    BEGIN
      EXECUTE $1;
      RETURN $1;
    END;
$f$;

Ensuite vous pouvez exécuter cette requête, elle changera le propriétaire des tables, séquences et vues :

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' ||
            quote_ident(s.relname) || ' OWNER TO $NEWUSER')
  FROM (SELECT nspname, relname
          FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
         WHERE nspname NOT LIKE E'pg\\_%' AND 
               nspname <> 'information_schema' AND 
               relkind IN ('r','S','v') ORDER BY relkind = 'S') s;

$NEWUSER est le nouveau nom postgresql du nouveau propriétaire.

Dans la plupart des cas, vous devez être super-utilisateur pour exécuter cette opération. Vous pouvez éviter cela en changeant le propriétaire de votre propre utilisateur à un groupe de rôle dont vous êtes membre.

Merci à RhodiumToad sur #postgresql pour m'avoir aidé avec ça.

Answer 5

J'ai récemment dû changer la propriété de tous les objets d'une base de données. Bien que les tables, les vues, les déclencheurs et les séquences aient pu être modifiés assez facilement, l'approche ci-dessus a échoué pour les fonctions, car la signature fait partie du nom de la fonction. Il est vrai que j'ai une expérience de MySQL et que je ne suis pas très familier avec Postgres.

Cependant, pg_dump vous permet de vider uniquement le schéma, et celui-ci contient l'élément ALTER xxx OWNER TO yyy ; dont vous avez besoin. Voici mon petit tour de magie shell sur le sujet

pg_dump -s YOUR_DB | grep -i 'owner to' | sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | psqL YOUR_DB