在Amazon RDS上设置PostGis

Question

好的，我对RDS和AWS还比较新，但是我无论如何都不能把我在笔记本电脑上创建的数据库移动到Amazon RDS上。我已经将它移动到我的测试VPS和桌面机上，完全没有任何问题，以下是我迄今为止尝试过的：

create extension postgis;
create extension fuzzystrmatch;
create extension postgis_tiger_geocoder;
create extension postgis_topology;
alter schema tiger owner to rds_superuser;
alter schema topology owner to rds_superuser;

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN     $1; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;        

GRANT ALL PRIVILEGES ON DATABASE testgetwork TO luke;

然后我连接到数据库并尝试加载我本地的新数据库副本...

luke@l-xps:~$ psql --host=myhost.dns.us-west-2.rds.amazonaws.com --port=5432 --username=luke --password --dbname=testgetwork < testgetwork.sql 
Password for user luke: 
SET
SET
SET
SET
SET
ERROR:  schema "topology" already exists
ALTER SCHEMA
CREATE EXTENSION
ERROR:  must be owner of extension plpgsql
CREATE EXTENSION
ERROR:  must be owner of extension postgis
CREATE EXTENSION
ERROR:  must be owner of extension postgis_topology

<more output, removed to save space>

ERROR:  permission denied for relation spatial_ref_sys
invalid command \.
 setval 
-------- 
      1
(1 row)

 setval 

<more output, removed to save space>

我的问题是，我该如何从这里继续前进？所有的功能似乎都被权限问题阻塞了。用户卢克是rds_superuser组的一部分，有没有任何熟悉PostGis和RDS的人知道我该如何纠正这个问题？

testgetwork=> \du
                                  List of roles
   Role name   |                   Attributes                   |    Member of    
---------------+------------------------------------------------+-----------------
 luke          | Create role, Create DB                         | {rds_superuser}
 rds_superuser | Cannot login                                   | {}
 rdsadmin      | Superuser, Create role, Create DB, Replication | {}

- Crooksey

---

我对RDS还不是很熟悉，但从我所见的来看，它非常注重Owner规则。那么，testgetwork数据库的所有者是谁？很可能是rdsadmin。即使不是，您运行命令时最好使用rdsadmin账户，这样成功的几率会更大。我知道这并不理想，但为了让您能够开始工作，这可能是一个成功的方法。 - ProgrammerDan

Answer 1

我认为这里的问题在于用户luke在数据库testgetwork上的权限。请检查luke的权限。应该先执行以下命令：

GRANT ALL PRIVILEGES ON DATABASE testgetwork TO luke;

然后在执行create extension命令之前，应重新连接会话以使用用户luke。根据doc，正确的步骤如下：

• 当您创建AWS RDS实例时，如果您没有修改它，RDS会创建一个名为postgres的用户。密码是您在创建时输入的。不应将用户postgres（或其他名称）用于一般DBA或应用程序任务，因为它具有附加特权，您不希望在未来执行危险命令时破坏数据库。请使用此用户连接到您的数据库。

psql --host=<rds_host> --port=5432 --username=postgres --password

相反，应创建一个单独的用户并授予其所需的权限。我认为这就是 OP 所做的。OP 创建了一个名为 luke 的用户。

CREATE ROLE luke LOGIN PASSWORD 'pass';

AWS RDS posgtres 实例已经创建了一个名为 rds_superuser 的角色，具有受限制的超级用户访问权限。应将 luke 授予 rds_superuser 特权。

GRANT rds_superuser TO gis_admin;

创建数据库。由于数据库 testgetwork 已经创建，我们将跳过此步骤。

授予 luke 在 testgetwork 数据库上的所有权限。

GRANT ALL PRIVILEGES ON DATABASE testgetwork TO luke;

退出会话并使用用户名为luke重新连接到您的AWS RDS PostgreSQL实例。

psql --host=<rds_host> --port=5432 --username=luke --password

现在运行所有扩展命令：

create extension postgis;
create extension fuzzystrmatch;
create extension postgis_tiger_geocoder;
create extension postgis_topology;
alter schema tiger owner to rds_superuser;
alter schema topology owner to rds_superuser;

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN     $1; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;

- Rahul Sharma