恢复PostgreSQL数据库时如何解决特权问题 [英] How to solve privileges issues when restore PostgreSQL Database

问题描述

我已经使用以下命令为Postgres数据库转储了干净的无所有者备份

I have dumped a clean, no owner backup for Postgres Database with the command

pg_dump sample_database -O -c -U

稍后，当我使用

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

$的所有者b $ b

我研究了纯文本SQL pg_dump 生成的内容，发现其中包含SQL

I digged into the plain-text SQL pg_dump generates and I found it contains 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 。

I think the causes are that the user app_name doesn't have the privileges to alter the public schema and plpgsql.

如何解决此问题？

推荐答案

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

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

因此以超级用户身份连接到数据库帐户 sudo -u postgres psql 并执行 ALTER ROLE< user-name>超级用户； 语句。

So connect to the database under a Superuser account sudo -u postgres psql and execute a ALTER ROLE <user-name> Superuser; statement.

请紧记，这不是多站点托管服务器上的最佳解决方案，因此请看一下分配个人角色： https://www.postgresql.org/docs/current/static/sql- set-role.html 和 https：//www.postgresql .org / docs / current / static / sql-alterrole.html 。

Keep in mind this 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.html and https://www.postgresql.org/docs/current/static/sql-alterrole.html.

这篇关于恢复PostgreSQL数据库时如何解决特权问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！