Comment résoudre les problèmes de privilèges lors de la restauration d'une base de données PostgreSQL ?

Question

J'ai fait une sauvegarde propre, sans propriétaire, de la base de données Postgres avec la commande

pg_dump sample_database -O -c -U

Plus tard, lorsque je restaure la base de données avec

psql -d sample_database -U app_name

Cependant, j'ai rencontré plusieurs erreurs qui m'empêchent de restaurer les données :

ERROR:  must be owner of extension plpgsql
ERROR:  must be owner of schema public
ERROR:  schema "public" already exists
ERROR:  must be owner of schema public
CREATE EXTENSION
ERROR:  must be owner of extension plpgsql

J'ai creusé dans le texte clair de SQL pg_dump génère et j'ai trouvé qu'il contient du SQL

CREATE SCHEMA public;
COMMENT ON SCHEMA public IS 'standard public schema';
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

Je pense que les causes sont que l'utilisateur app_name n'a pas les privilèges nécessaires pour modifier l'adresse de l'utilisateur. public et plpgsql .

Comment pourrais-je résoudre ce problème ?

Answer 1

Réponse plus courte : ignorez-la.

Ce module est la partie de Postgres qui traite le langage SQL. L'erreur apparaîtra souvent dans le cadre de la copie d'une base de données distante, comme avec un 'heroku pg:pull'. Il n'écrase pas votre processeur SQL et vous en avertit.

Answer 2

Pour les personnes utilisant AWS le COMMENT ON EXTENSION n'est possible que si superutilisateur et comme nous le savons par les documents, les instances RDS sont gérées par Amazon. En tant que tel, pour vous empêcher de casser des choses comme la réplication, vos utilisateurs - même l'utilisateur Root que vous avez configuré lors de la création de l'instance - n'auront pas les privilèges complets de superutilisateur :

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html

Lorsque vous créez une instance de DB, le compte système de l'utilisateur principal que vous créé est affecté au rôle rds_superuser. Le rôle rds_superuser est un rôle Amazon RDS prédéfini similaire au rôle de superutilisateur PostgreSQL PostgreSQL (habituellement nommé postgres dans les instances locales), mais avec quelques restrictions. restrictions. Comme pour le rôle de superutilisateur PostgreSQL, le rôle rds_superuser a le plus de privilèges sur votre instance de base de données et vous ne devriez pas attribuer ce rôle aux utilisateurs à moins qu'ils n'en aient besoin. Vous ne devriez pas attribuer ce rôle aux utilisateurs à moins qu'ils n'aient besoin d'accéder instance.

Afin de corriger cette erreur, il suffit d'utiliser -- pour commenter les lignes de SQL qui contiennent COMMENT ON EXTENSION

Answer 3

Pour les personnes qui ont réduit la question à la COMMENT ON (voir les différentes réponses ci-dessous) et qui ont un accès de super-utilisateur à la base de données source à partir de laquelle le fichier de vidage est créé, la solution la plus simple pourrait être d'empêcher les commentaires d'être inclus dans le fichier de vidage en premier lieu, en les supprimant de la base de données source qui est vidée...

COMMENT ON EXTENSION postgis IS NULL;
COMMENT ON EXTENSION plpgsql IS NULL;
COMMENT ON SCHEMA public IS NULL;

Les vidages futurs n'incluront donc pas le COMMENT ON déclarations.

Answer 4

Utilisez l'utilisateur postgres (admin) pour vider le schéma, le recréer et accorder des privilèges d'utilisation avant de procéder à la restauration. En une seule commande :

sudo -u postgres psql -c "DROP SCHEMA public CASCADE;
create SCHEMA public;
grant usage on schema public to public;
grant create on schema public to public;" myDBName

Answer 5

Pour moi, je mettais en place une base de données avec pgAdmin et il semble que le fait de définir le propriétaire lors de la création de la base de données n'était pas suffisant. J'ai dû naviguer jusqu'au Schéma "public et définir les propriétaire (qui était à l'origine 'postgres').