如何解决在还原PostgreSQL数据库时出现的权限问题

Question

我使用以下命令生成了一个干净无所有者的Postgres数据库备份：

pg_dump sample_database -O -c -U

后来，当我使用以下命令恢复数据库时：

psql -d sample_database -U app_name

然而，我遇到了一些错误，这些错误阻止我恢复数据：

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

我深入研究了纯文本SQL pg_dump 生成的内容，发现它包含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';

我认为问题的原因是用户 app_name 没有修改 public 模式和 plpgsql 的权限。

我该如何解决这个问题？

- steveyang

---

如果你不需要 plpgsql，在执行 pg_dump 前先运行 DROP EXTENSION plpgsql。这比将你的应用程序设置为超级用户更安全，也比忽略错误更方便（如果使用 --single-transaction 或 -v ON_ERROR_STOP=1 会导致失败）。这是一个已知的问题，在Postgres开发人员的讨论中详细讨论过，但截至9.3版本仍未修复。 - Mark E. Haase

Answer 1

要解决此问题，您必须分配适当的所有权权限。尝试以下操作，这应该可以解决特定用户的所有权限相关问题，但正如评论中所述，不应在生产环境中使用：

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

因此，使用超级用户帐户连接到数据库sudo -u postgres psql并执行ALTER ROLE <user-name> Superuser;语句。

请记住：这不是多站点托管服务器上的最佳解决方案，请考虑分配单独的角色：https://www.postgresql.org/docs/current/static/sql-set-role.html 和 https://www.postgresql.org/docs/current/static/sql-alterrole.html。

- Daniel Sokolowski

---

不是超级用户，有没有其他方法可以做到这一点？ - Travis Webb

“必须分配适当的所有权权限”和“alter role <user-name> superuser”不一致。适当的所有权意味着app_user不是超级用户。 - Mark E. Haase

@mehaase请更新答案的措辞，而不是进行负面投票。 - Daniel Sokolowski

在我看来，这不是解决方案，而是在生产中应该避免的权宜之计。 - Dmytriy Voloshyn

我如何通过shell脚本来实现这个？ - user269867

将普通用户升级为超级用户是不明智的建议。 - Evren Yurtesen

Answer 2

AWS RDS用户如果出现此问题，可能是因为您不是超级用户，根据AWS文档，您无法成为超级用户。我发现我必须忽略这些错误。

- Jim Zucker

---

这个错误正在阻止我完成恢复（AWS RDS pg_restore）。有忽略这些错误的提示吗？ - avjaarsveld

我没有在pg_restore命令中使用-e或--exit-on-error选项。 - avjaarsveld

我发现在RDS上的问题在于COMMENT ON EXTENSION，而不是CREATE EXTENSION。删除注释即可解决问题。 - pkoch

@pkoch 同 Google Cloud 存储一样。问题出在不需要的扩展上。 - Jaybeecave

使用以下命令过滤掉注释： pg_restore --list $DUMP_DIR | sed '/COMMENT - EXTENSION/d' > $DUMP_ROOT/restore.list 然后使用以下命令进行恢复： pg_restore --dbname $CONN --verbose --exit-on-error --use-list $DUMP_ROOT/restore.list --no-owner --role=$DBUSER --clean --if-exists $DUMP_DIR - Alf47

AWS RDS不允许您创建超级用户，但是您可以将新用户添加到rds_superuser角色中：CREATE USER new_user WITH NOSUPERUSER NOCREATEDB IN ROLE "rds_superuser" ...; 在这种情况下，该用户将能够创建扩展和注释。 - Maxim

Answer 3

对于使用Google Cloud平台的用户来说，任何错误都会停止导入过程。就我个人而言，根据我发出的pg_dump命令，遇到了两种不同的错误:

1- 输入是PostgreSQL自定义格式转储。使用pg_restore命令行客户端将此转储还原到数据库中。

当您尝试以非纯文本格式转储DB时会发生这种情况。例如，当命令缺少-Fp或--format=plain参数时。但是，如果将其添加到您的命令中，则可能会遇到以下错误：

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

这是一个权限问题，我无法使用GCP文档提供的命令、本线程的提示或者谷歌Postgres团队的建议（在此处）解决。它建议发出以下命令：

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

在我的情况下，唯一有效的方法是手动编辑转储文件并注释掉与plpgsql相关的所有命令。

我希望这可以帮助依赖GCP的用户。

更新：

注释扩展名更容易导出文件，特别是某些转储可能非常大:

pg_dump ... | grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' > mydump.sql

可以缩小到plpgsql：

pg_dump ... | grep -v -E '(CREATE\ EXTENSION\ IF\ NOT\ EXISTS\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)' > mydump.sql

- Standaa - Remember Monica

---

现在，GCP的文档中提供了完整的pg_dump命令用于导出PostgreSQL数据库：pg_dump -U [USERNAME] --format=plain --no-owner --no-acl [DATABASE_NAME] \ | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > [SQL_FILE].sql。请注意不要更改原始意思。 - Rush

我必须在grep命令中使用双引号，例如"plpgsql"，才能使其正常工作。 - soltex

Answer 4

尝试使用-L标志与pg_dump -Fc获取的文件一起使用pg_restore。

-L list-file --use-list=list-file

仅还原在list-file中列出的归档元素，并按它们在文件中出现的顺序还原它们。请注意，如果使用-n或-t等过滤开关与-L一起使用，则它们将进一步限制恢复的项目。

list-file通常是通过编辑先前-l操作的输出创建的。可以移动或删除行，也可以在行首放置分号（;）来注释行。有关示例，请参见下文。

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

在这里，您可以看到只输出评论时反转(Inverse)是正确的:

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

- akahunahi

---

我认为上述内容是正确的，不包括插件注释不会影响您应用程序的功能。 - Andreas

这绝对是最好的答案，不确定为什么它在另外两个建议忽略它的答案下面... - Dylan

Answer 5

在这种情况下，您可能可以安全地忽略错误消息。未向公共模式添加注释和安装plpgsql（应已安装）不会导致任何真正的问题。

但是，如果您想要进行完全重新安装，则需要具有适当权限的用户。当然，这不应该是您的应用程序经常运行的用户。

- Richard Huxton

Answer 6

简短回答：忽略它。

这个模块是 PostgreSQL 处理 SQL 语言的一部分。当复制远程数据库时（例如使用 'heroku pg:pull'），通常会弹出这个错误。它不会覆盖您的SQL处理器并对此发出警告。

- Charles Merriam

Answer 7

对于使用AWS的人来说，COMMENT ON EXTENSION只有作为超级用户才能实现。我们知道根据文档，RDS实例由亚马逊管理。因此，为了防止您破坏复制等功能，即使是创建实例时设置的根用户，您的用户也不会拥有完整的超级用户权限：

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

创建DB实例时，您创建的主用户系统帐户被分配给rds_superuser角色。rds_superuser角色是预定义的Amazon RDS角色，类似于PostgreSQL超级用户角色（在本地实例中通常命名为postgres），但具有一些限制。与PostgreSQL超级用户角色一样，rds_superuser角色在DB实例上拥有最高特权，除非用户需要访问DB实例的最高权限，否则不应将此角色分配给用户。

为了修复此错误，只需使用--来注释包含COMMENT ON EXTENSION的SQL行。

编辑1：如Dmitrii I.所建议的那样，在转储时也可以省略注释：pg_dump --no-comments。

- Petar Nikov

---

仅返回翻译文本：或在转储时省略注释：“pg_dump --no-comments”。 - Dmitrii I.

Answer 8

如果已经将问题缩小到 COMMENT ON 语句 （根据下面的各种答案）并且拥有源数据库的超级用户访问权限，那么最简单的解决方案可能是首先从源数据库中删除它们，以防止这些注释被包含到转储文件中...

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

未来的转储将不包括COMMENT ON语句。

- Mark Schneider

---

在Rails本地开发中（每次运行模式迁移时会自动创建新的转储文件），这个解决方案使我能够简单地针对AWS RDS postgresql实例运行rails db:reset，而无需每次运行模式迁移时删除转储文件中的COMMENT ON行。 - Mark Schneider

Answer 9

使用postgres（admin）用户转储模式，重新创建并授予权限以供恢复使用。 一条命令：

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

- Pascal_dher

Answer 10

有些答案已经提供了与删除创建扩展和注释扩展相关的各种方法。对我来说，以下命令行似乎是最简单的解决问题的方法：

cat /tmp/backup.sql.gz | gunzip - | \
  grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' |  \
    psql --set ON_ERROR_STOP=on -U db_user -h localhost my_db

一些注释

• 第一行只是解压我的备份，您可能需要相应地进行调整。
• 第二行使用grep来消除有问题的行。
• 第三行是我的psql命令；您可能需要根据自己的习惯调整。

- oxer