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

Pour résoudre ce problème, vous devez attribuer les autorisations de propriété appropriées. Essayez la méthode ci-dessous qui devrait résoudre tous les problèmes liés aux autorisations pour certains utilisateurs, mais comme indiqué dans les commentaires, elle ne doit pas être utilisée en production :

root@server:/var/log/postgresql# sudo -u postgres psql
psql (8.4.4)
Type "help" for help.

postgres=# \du
               List of roles
    Role name    | Attributes  | Member of
-----------------+-------------+-----------
 <user-name>    | Superuser   | {}
                 : Create DB
 postgres       | Superuser   | {}
                 : Create role
                 : Create DB

postgres=# alter role <user-name> superuser;
ALTER ROLE
postgres=#

Donc, connectez-vous à la base de données sous un compte Superuser sudo -u postgres psql et exécuter un ALTER ROLE <user-name> Superuser; déclaration.

Gardez à l'esprit Ce n'est pas la meilleure solution sur un serveur d'hébergement multisite. Vous pouvez donc envisager d'attribuer des rôles individuels à la place : https://www.postgresql.org/docs/current/static/sql-set-role.html y https://www.postgresql.org/docs/current/static/sql-alterrole.html .

Answer 2

Utilisateurs d'AWS RDS, si vous obtenez ce message, c'est parce que vous n'êtes pas un superutilisateur et, selon la documentation d'AWS, vous ne pouvez pas l'être. J'ai découvert que je devais ignorer ces erreurs.

Answer 3

Pour les personnes utilisant Google Cloud Platform, toute erreur arrêtera le processus d'importation. Personnellement, j'ai rencontré deux erreurs différentes en fonction de la commande pg_dump que j'ai lancée :

1- The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database.

Cela se produit lorsque vous avez essayé de vider votre base de données dans un format autre que du texte brut. C'est-à-dire lorsque la commande n'a pas le paramètre -Fp ou --format=plain. Cependant, si vous l'ajoutez à votre commande, vous pouvez alors rencontrer l'erreur suivante :

2- SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql

Il s'agit d'un problème d'autorisation que je n'ai pas réussi à résoudre en utilisant la commande fournie dans la page d'accueil du site. Documents sur les BPC , les conseils de ce fil de discussion ou les conseils de l'équipe Postgres de Google. aquí . Qui recommande d'émettre la commande suivante :

pg_dump -Fp --no-acl --no-owner -U myusername myDBName > mydump.sql

La seule chose qui a fonctionné dans mon cas a été d'éditer manuellement le fichier dump et de commenter toutes les commandes relatives à plpgsql.

J'espère que cela aidera les âmes qui dépendent des BPC.

Mise à jour :

Il est plus facile de vider le fichier en commentant les extensions, d'autant plus que certains vidages peuvent être énormes : pg_dump ... | grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' > mydump.sql

Ce qui peut être réduit à plpgsql : pg_dump ... | grep -v -E '(CREATE\ EXTENSION\ IF\ NOT\ EXISTS\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)' > mydump.sql

Answer 4

Essayez d'utiliser le -L avec pg_restore en spécifiant le fichier extrait de pg_dump -Fc

-L fichier de liste --use-list=list-file

Restaurer uniquement les éléments d'archives qui sont listés dans le fichier liste, et les restaurer dans l'ordre où ils apparaissent dans le fichier. Notez que si des options de filtrage telles que -n ou -t sont utilisées avec -L, elles restreindront davantage les éléments restaurés.

Le fichier de liste est normalement créé en éditant la sortie d'une opération -l précédente. Les lignes peuvent être déplacées ou supprimées, et peuvent également être commentées en plaçant un point-virgule ( ;) au début de la ligne. Voir ci-dessous pour des exemples.

https://www.postgresql.org/docs/9.5/app-pgrestore.html

pg_dump -Fc -f pg.dump db_name
pg_restore -l pg.dump | grep -v 'COMMENT - EXTENSION' > pg_restore.list
pg_restore -L pg_restore.list pg.dump

Ici, vous pouvez voir le Inverse est vrai en ne produisant que le commentaire :

pg_dump -Fc -f pg.dump db_name
pg_restore -l pg.dump | grep 'COMMENT - EXTENSION' > pg_restore_inverse.list
pg_restore -L pg_restore_inverse.list pg.dump
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.4.15
-- Dumped by pg_dump version 9.5.14

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--
-- PostgreSQL database dump complete
--

Answer 5

Vous pouvez probablement ignorer les messages d'erreur dans ce cas. Le fait de ne pas ajouter un commentaire au schéma public et l'installation de plpgsql (qui devrait déjà être installé) ne vont pas causer de réels problèmes.

Cependant, si vous souhaitez effectuer une réinstallation complète, vous aurez besoin d'un utilisateur disposant des autorisations appropriées. Il ne doit pas s'agir de l'utilisateur sous lequel votre application s'exécute habituellement, bien entendu.