Jak rozwiązać problemy z uprawnieniami podczas przywracania bazy danych PostgreSQL

Zrzuciłem czystą kopię zapasową bazy danych Postgres bez właściciela za pomocą polecenia

pg_dump sample_database -O -c -U

Później, kiedy przywracam bazę danych za pomocą

psql -d sample_database -U app_name

Jednak napotkałem kilka błędów, które uniemożliwiają mi przywrócenie danych:

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

Zagłębiłem się w pg_dumpgenerowane przez zwykły tekst SQL i stwierdziłem, że zawiera 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';

Myślę, że przyczyną jest to, że użytkownik app_namenie ma uprawnień do zmiany publicschematu i plpgsql.

Jak mogę rozwiązać ten problem?

Aby rozwiązać ten problem, musisz przypisać odpowiednie uprawnienia własności. Wypróbuj poniższe rozwiązania, które powinny rozwiązać wszystkie problemy związane z uprawnieniami dla określonych użytkowników, ale jak stwierdzono w komentarzach, nie należy tego używać w produkcji:

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=#

Połącz się więc z bazą danych na koncie Superuser sudo -u postgres psqli wykonaj ALTER ROLE <user-name> Superuser;instrukcję.

Pamiętaj, że nie jest to najlepsze rozwiązanie na serwerze obsługującym wiele witryn, więc zamiast tego przyjrzyj się przypisywaniu poszczególnych ról: https://www.postgresql.org/docs/current/static/sql-set-role.html i https : //www.postgresql.org/docs/current/static/sql-alterrole.html .

Użytkownicy AWS RDS, jeśli otrzymujesz to, to dlatego, że nie jesteś superużytkownikiem i zgodnie z dokumentacją aws nie możesz nim być. Odkryłem, że muszę zignorować te błędy.

W przypadku osób korzystających z Google Cloud Platform każdy błąd zatrzyma proces importowania. Osobiście napotkałem dwa różne błędy w zależności od wydanego przeze mnie polecenia pg_dump:

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

Występuje, gdy próbujesz zrzucić bazę danych w formacie innym niż zwykły tekst. To znaczy, gdy polecenie nie ma parametru -Fp lub --format = plain. Jeśli jednak dodasz go do polecenia, możesz napotkać następujący błąd:

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

Jest to problem z uprawnieniami, którego nie udało mi się naprawić za pomocą polecenia podanego w dokumentacji GCP , porad z tego bieżącego wątku lub zgodnie z poradami zespołu Google Postgres tutaj . Który zalecił wydanie następującego polecenia:

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

Jedyną rzeczą, która załatwiła sprawę w moim przypadku, była ręczna edycja pliku zrzutu i skomentowanie wszystkich poleceń związanych z plpgsql.

Mam nadzieję, że pomoże to duszom zależnym od GCP.

Aktualizacja :

Łatwiej jest zrzucić plik, komentując rozszerzenia, zwłaszcza, że ​​niektóre zrzuty mogą być ogromne: pg_dump ... | grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' > mydump.sql

Które można zawęzić do plpgsql: pg_dump ... | grep -v -E '(CREATE\ EXTENSION\ IF\ NOT\ EXISTS\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)' > mydump.sql

W takim przypadku prawdopodobnie możesz bezpiecznie zignorować komunikaty o błędach. Brak komentarza do schematu publicznego i instalacja plpgsql (który powinien być już zainstalowany) nie spowoduje żadnych poważnych problemów.

Jeśli jednak chcesz przeprowadzić pełną ponowną instalację, będziesz potrzebować użytkownika z odpowiednimi uprawnieniami. Oczywiście nie powinien to być użytkownik, który Twoja aplikacja uruchamia rutynowo.

Krótsza odpowiedź: zignoruj ​​to.

Ten moduł jest częścią Postgres, która przetwarza język SQL. Błąd często pojawia się podczas kopiowania zdalnej bazy danych, na przykład w przypadku „heroku pg: pull”. Nie zastępuje procesora SQL i ostrzega o tym.

Spróbuj użyć -Lflagi z pg_restore, określając plik wzięty zpg_dump -Fc

-L plik-listy --use-list = plik-listy

Przywróć tylko te elementy archiwum, które są wymienione w pliku listy i przywróć je w kolejności, w jakiej pojawiają się w pliku. Zwróć uwagę, że jeśli używane są przełączniki filtrujące, takie jak -n lub -t z -L, dodatkowo ograniczą one przywracane elementy.

plik-listy jest zwykle tworzony przez edycję danych wyjściowych poprzedniej operacji -l. Wiersze można przenosić lub usuwać, a także komentować, umieszczając średnik (;) na początku wiersza. Poniżej znajdują się przykłady.

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

Tutaj możesz zobaczyć, że odwrotność jest prawdą, wyświetlając tylko komentarz:

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
--

Dla osób korzystających z AWS , COMMENT ON EXTENSIONjest możliwe tylko jako administrator , a jak wiemy przez docs, RDS przypadki są zarządzane przez Amazon. W związku z tym, aby zapobiec zepsuciu rzeczy, takich jak replikacja, Twoi użytkownicy - nawet użytkownik root skonfigurowany podczas tworzenia instancji - nie będą mieli pełnych uprawnień superużytkownika:

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

Podczas tworzenia instancji bazy danych główne konto systemowe użytkownika, które tworzysz, jest przypisywane do roli rds_superuser. Rola rds_superuser to predefiniowana rola Amazon RDS podobna do roli superużytkownika PostgreSQL (zwykle nazywana postgres w lokalnych instancjach), ale z pewnymi ograniczeniami. Podobnie jak w przypadku roli superużytkownika PostgreSQL, rola rds_superuser ma najwięcej uprawnień do instancji bazy danych i nie należy przypisywać tej roli użytkownikom, chyba że potrzebują oni największego dostępu do instancji bazy danych.

Aby naprawić ten błąd, po prostu użyj --do zakomentowania wierszy kodu SQL, które zawierająCOMMENT ON EXTENSION

Użyj użytkownika postgres (admin), aby zrzucić schemat, ponownie go utworzyć i przyznać uprawnienia do użycia przed wykonaniem przywracania. Jednym poleceniem:

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

Dla mnie konfigurowałem bazę danych za pomocą pgAdmin i wydaje się, że ustawienie właściciela podczas tworzenia bazy danych nie wystarczyło. Musiałem przejść w dół do schematu „publicznego” i ustawić tam również właściciela (pierwotnie był to „postgres”).

Dla osób, które zawęziły problem do COMMENT ONstwierdzeń (zgodnie z różnymi odpowiedziami poniżej) i które mają dostęp superużytkownika do źródłowej bazy danych, z której tworzony jest plik zrzutu, najprostszym rozwiązaniem może być zapobieganie dołączaniu komentarzy do zrzutu plik w pierwszej kolejności, usuwając je z zrzucanej źródłowej bazy danych ...

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

Przyszłe zrzuty nie będą wtedy zawierać COMMENT ONinstrukcji.