postgresql - 无法在亚马逊RDS上创建postgres基本类型(具有hstore/pgcrypto扩展名)

Question

我们正在从EC2实例上的本地postgres安装迁移到amazonrdsforpostgres。数据迁移是通过转储和还原完成的，如下所示：

 pg_dump -U postgres test1_main> test1_main.dump
 psql -U postgres -h <rds end point> -p 5432 test1_main < test1_main.dump

在恢复过程中，我们看到失败的说法是：

CREATE SCHEMA
ALTER SCHEMA
ERROR:  must be owner of language plpgsql
ALTER LANGUAGE
SET
CREATE TYPE
ALTER TYPE
ERROR:  must be superuser to create a base type
ERROR:  permission denied for language c
ALTER FUNCTION
ERROR:  permission denied for language c
ALTER FUNCTION
ERROR:  must be superuser to create a base type

.....


ERROR:  permission denied for language c
ERROR:  function pgcrypto.armor(bytea) does not exist
ERROR:  permission denied for language c

等等！
我们已经将hstore和pgcryptop扩展添加到Amazon RDS实例[1]上的template1数据库中，因此我们拥有这些扩展，但我们无法创建类型，因为在postgres 9.3中创建基类型需要超级用户[2]。postgres用户不允许这样做，amazon RDS文档说：
当您创建一个数据库实例时，您的主用户系统帐户
create被分配给rds\u超级用户角色。rds_超级用户角色
类似于PostgreSQL超级用户角色（通常命名为
但有一些限制
rdsadmin是超级用户，但我无法使用rdsadmin登录。

oncall@ip-10.144.xx.xxx:~> psql -U rdsadmin -h <rds end-point> -p 5432
psql.bin: FATAL:  pg_hba.conf rejects connection for host "10.144.xx.xxx", user "rdsadmin", database "rdsadmin", SSL on
FATAL:  pg_hba.conf rejects connection for host "10.144.xx.xxx", user "rdsadmin", database "rdsadmin", SSL off

请帮忙。
[1] http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#SQLServer.Concepts.General.FeatureSupport
[2] http://www.postgresql.org/docs/9.3/static/sql-createtype.html

Answer 1

RDS实例由Amazon管理。因此，为了防止破坏复制之类的操作，“您的用户（甚至是创建实例时设置的根用户）将没有完全的超级用户权限
rds_superuser角色可以执行以下操作：
一。添加可用于Amazon RDS的扩展。有关详细信息，请参阅受支持的PostgreSQL功能和PostgreSQL文档。
2。管理表空间，包括创建和删除它们。有关详细信息，请参阅PostgreSQL文档中的“表空间”部分。
三。使用pg_stat_activity命令查看未分配rds_superuser角色的所有用户，并使用pg_terminate_backend和pg_cancel_backend命令终止其连接。
四。为所有不是rds_超级用户角色的角色授予和撤消rds_复制角色。有关更多信息，请参阅PostgreSQL文档中的授权部分。
下面的示例演示如何创建用户，然后授予
使用rds\u超级用户角色。用户定义的角色，例如
必须授予rds_超级用户。

postgres=> \du
                                                                List of roles
    Role name     |                         Attributes                         |                          Member of
------------------+------------------------------------------------------------+---------------------------------------------
 masteruser       | Create role, Create DB                                    +| {rds_superuser}
                  | Password valid until infinity                              |
 rds_ad           | Cannot login                                               | {}
 rds_superuser    | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin         | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                  | Password valid until infinity                              |
 rdsrepladmin     | No inheritance, Cannot login, Replication                  | {}
 testuser         | Create DB                                                  | {}
------------------+------------------------------------------------------------+---------------------------------------------

create role testuser with password 'testuser' login;   
CREATE ROLE   
grant rds_superuser to testuser;   
GRANT ROLE   

    Role name     |                         Attributes                         |                          Member of
------------------+------------------------------------------------------------+---------------------------------------------
 masteruser       | Create role, Create DB                                    +| {rds_superuser}
                  | Password valid until infinity                              |
 rds_ad           | Cannot login                                               | {}
 rds_superuser    | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin         | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                  | Password valid until infinity                              |
 rdsrepladmin     | No inheritance, Cannot login, Replication                  | {}
 testuser         | Create DB                                                  | {rds_superuser}
------------------+------------------------------------------------------------+---------------------------------------------

postgres=> CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION

当您创建一个数据库实例时，您的主用户系统帐户
create被分配给rds\u超级用户角色。rds_超级用户角色
是一个预定义的Amazon RDS角色，类似于PostgreSQL超级用户
角色（在本地实例中习惯上称为postgres），但是
限制。与PostgreSQL超级用户角色一样，rds\u超级用户
角色对您的数据库实例拥有最多权限，您不应该
将此角色分配给用户，除非他们最需要访问数据库
实例。