postgresql 恢复PostgreSQL数据库时如何解决权限问题
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/13410631/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to solve privileges issues when restore PostgreSQL Database
提问by steveyang
I have dumped a clean, no owner backup for Postgres Database with the command
我已经使用命令为 Postgres 数据库转储了一个干净的、没有所有者的备份
pg_dump sample_database -O -c -U
pg_dump sample_database -O -c -U
Later, when I restore the database with
后来,当我用
psql -d sample_database -U app_name
psql -d sample_database -U app_name
However, I encountered several errors which prevents me from restoring the data:
但是,我遇到了几个阻止我恢复数据的错误:
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
I digged into the plain-text SQL pg_dump
generates and I found it contains SQL
我深入研究了pg_dump
生成的纯文本 SQL ,发现它包含 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';
I think the causes are that the user app_name
doesn't have the privileges to alter the public
schema and plpgsql
.
我认为原因是用户app_name
没有更改public
架构和plpgsql
.
How could I solve this issue?
我怎么能解决这个问题?
回答by Daniel Sokolowski
To solve the issue you must assign the proper ownership permissions. Try the below which should resolve all permission related issues for specific users but as stated in the comments this should not be used in 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=#
So connect to the database under a Superuser account sudo -u postgres psql
and execute a ALTER ROLE <user-name> Superuser;
statement.
所以用超级用户账号连接数据库sudo -u postgres psql
,执行一条ALTER ROLE <user-name> Superuser;
语句。
Keep in mindthis is not the best solution on multi-site hosting server so take a look at assigning individual roles instead: https://www.postgresql.org/docs/current/static/sql-set-role.htmland https://www.postgresql.org/docs/current/static/sql-alterrole.html.
请记住,这不是多站点托管服务器上的最佳解决方案,因此请查看分配单个角色:https: //www.postgresql.org/docs/current/static/sql-set-role.html和https ://www.postgresql.org/docs/current/static/sql-alterrole.html。
回答by Jim Zucker
AWS RDS users if you are getting this it is because you are not a superuser and according to aws documentation you cannot be one. I have found I have to ignore these errors.
AWS RDS 用户,如果你得到这个,那是因为你不是超级用户,根据 aws 文档你不能成为超级用户。我发现我必须忽略这些错误。
回答by Stanislasdrg Reinstate Monica
For people using Google Cloud Platform, any error will stop the import process. Personally I encountered two different errors depending on the pg_dump command I issued :
对于使用 Google Cloud Platform 的用户,任何错误都会停止导入过程。根据我发出的 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.
1- The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database.
Occurs when you've tried to dump your DB in a non plain text format. I.e when the command lacks the -Fp or --format=plain parameter. However, if you add it to your command, you may then encounter the following error :
当您尝试以非纯文本格式转储数据库时发生。即当命令缺少 -Fp 或 --format=plain 参数时。但是,如果将其添加到命令中,则可能会遇到以下错误:
2- SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql
2- SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql
This is a permission issue I have been unable to fix using the command provided in the GCP docs, the tips from this current thread, or following advice from Google Postgres team here. Which recommended to issue the following command :
这是一个权限问题,我无法使用GCP 文档中提供的命令、此当前线程中的提示或遵循此处的Google Postgres 团队的建议来修复。其中建议发出以下命令:
pg_dump -Fp --no-acl --no-owner -U myusername myDBName > mydump.sql
pg_dump -Fp --no-acl --no-owner -U myusername myDBName > mydump.sql
The only thing that did the trick in my case was manually editing the dump file and commenting out all commands relating to plpgsql.
在我的情况下,唯一能解决问题的是手动编辑转储文件并注释掉与 plpgsql 相关的所有命令。
I hope this helps GCP-reliant souls.
我希望这有助于依赖 GCP 的灵魂。
Update :
更新 :
It's easier to dump the file commenting out extensions, especially since some dumps can be huge :
pg_dump ... | grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' > mydump.sql
转储注释扩展名的文件更容易,特别是因为某些转储可能很大:
pg_dump ... | grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' > mydump.sql
Which can be narrowed down to plpgsql :
pg_dump ... | grep -v -E '(CREATE\ EXTENSION\ IF\ NOT\ EXISTS\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)' > mydump.sql
可以缩小到 plpgsql :
pg_dump ... | grep -v -E '(CREATE\ EXTENSION\ IF\ NOT\ EXISTS\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)' > mydump.sql
回答by Richard Huxton
You can probably safely ignore the error messages in this case. Failing to add a comment to the public schema and installing plpgsql (which should already be installed) aren't going to cause any real problems.
在这种情况下,您可能可以安全地忽略错误消息。未能向公共架构添加注释并安装 plpgsql(应该已经安装)不会导致任何实际问题。
However, if you want to do a complete re-install you'll need a user with appropriate permissions. That shouldn't be the user your application routinely runs as of course.
但是,如果您想进行完整的重新安装,则需要具有适当权限的用户。当然,这不应该是您的应用程序经常运行的用户。
回答by Charles Merriam
Shorter answer: ignore it.
简短的回答:忽略它。
This module is the part of Postgres that processes the SQL language. The error will often pop up as part of copying a remote database, such as with a 'heroku pg:pull'. It does not overwrite your SQL processor and warns you about that.
该模块是 Postgres 处理 SQL 语言的部分。该错误通常会作为复制远程数据库的一部分弹出,例如使用“heroku pg:pull”。它不会覆盖您的 SQL 处理器并警告您。
回答by Ligemer
Try using the -L
flag with pg_restore by specifying the file taken from pg_dump -Fc
尝试-L
通过指定从pg_dump -Fc
-L list-file --use-list=list-file
Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored.
list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples.
-L 列表文件 --use-list=list-file
仅恢复列表文件中列出的那些归档元素,并按照它们在文件中出现的顺序恢复它们。请注意,如果将 -n 或 -t 等过滤开关与 -L 一起使用,它们将进一步限制恢复的项目。
list-file 通常是通过编辑前一个 -l 操作的输出来创建的。可以移动或删除行,也可以通过在行的开头放置分号 (;) 来注释掉。有关示例,请参见下文。
https://www.postgresql.org/docs/9.5/app-pgrestore.html
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
Here you can see the Inverseis true by outputting only the comment:
在这里,您可以通过仅输出注释来看到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
--
回答by Artley
For people using AWS, the COMMENT ON EXTENSION
is possible only as superuser, and as we know by the docs, RDS instances are managed by Amazon. As such, to prevent you from breaking things like replication, your users - even the root user you set up when you create the instance - will not have full superuser privileges:
对于使用AWS 的人,COMMENT ON EXTENSION
只有作为超级用户才有可能,正如我们从文档中知道的那样,RDS 实例由 Amazon 管理。因此,为了防止您破坏复制等操作,您的用户 - 即使是您在创建实例时设置的 root 用户 - 将不具有完全的超级用户权限:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html
When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is a pre-defined Amazon RDS role similar to the PostgreSQL superuser role (customarily named postgres in local instances), but with some restrictions. As with the PostgreSQL superuser role, the rds_superuser role has the most privileges on your DB instance and you should not assign this role to users unless they need the most access to the DB instance.
创建数据库实例时,您创建的主用户系统帐户会分配给 rds_superuser 角色。rds_superuser 角色是一个预定义的 Amazon RDS 角色,类似于 PostgreSQL 超级用户角色(在本地实例中通常命名为 postgres),但有一些限制。与 PostgreSQL 超级用户角色一样,rds_superuser 角色对您的数据库实例拥有最多权限,除非他们需要对数据库实例的最大访问权限,否则您不应将此角色分配给用户。
In order to fix this error, just use --
to comment out the lines of SQL that contains COMMENT ON EXTENSION
为了修复这个错误,只需使用--
注释掉包含的 SQL 行COMMENT ON EXTENSION
回答by Pascal_dher
Use the postgres (admin) user to dump the schema, recreate it and grant priviledges for use before you do your restore. In one command:
在进行恢复之前,使用 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
回答by Peter L
For me, I was setting up a database with pgAdminand it seems setting the owner during database creation was not enough. I had to navigate down to the 'public' schemaand set the ownerthere as well (was originally 'postgres').
对我来说,我正在使用pgAdmin设置一个数据库,似乎在数据库创建期间设置所有者是不够的。我不得不向下导航到“公共”模式并将所有者也设置在那里(最初是“postgres”)。
回答by Mark Schneider
For people who have narrowed down the issue to the COMMENT ON
statements (as per various answers below) and who have superuser access to the source database from which the dump file is created, the simplest solution might be to prevent the comments from being included to the dump file in the first place, by removing them from the source database being dumped...
对于将问题缩小到COMMENT ON
语句的人(根据下面的各种答案)并且对创建转储文件的源数据库具有超级用户访问权限的人,最简单的解决方案可能是防止将注释包含在转储中首先,通过从正在转储的源数据库中删除它们...
COMMENT ON EXTENSION postgis IS NULL;
COMMENT ON EXTENSION plpgsql IS NULL;
COMMENT ON SCHEMA public IS NULL;
Future dumps then won't include the COMMENT ON
statements.
未来的转储将不包括这些COMMENT ON
语句。