Setting up PostGis on Amazon RDS

Question

Ok, so I am fairly new to RDS and AWS, but I can't for the life of me, get my database that I created on my laptop, onto amazon RDS, I did move it to my test VPS and also my desktop machine, with no problems at all, here is what I have tried so far..

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;

Then I connect to the database and try and load my local copy of the new database...

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>

My question is, how can I move on from here? All of the features just seemed to be blocked by a permissions problem, by user luke is part of the rds_superuser group, does anyone who has experience with PostGis and RDS know how I can rectify this?

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

Answer 1

I think the problem here is with the privileges of user luke on database testgetwork. Please check luke's privileges. This command:

GRANT ALL PRIVILEGES ON DATABASE testgetwork TO luke;

should be executed beforehand. Then the session should be reconnected with user luke before executing create extension commands. Based on the doc, the proper steps would be:

• When you create an AWS RDS instance, RDS creates a user named postgres if you've not modified it. The password is what you entered at the time of creation. The user postgres (or named otherwise) shouldn't be used for general DBA or application tasks as it has added privileges and you don't want to mess up with your database (by executing a dangerous command in future). Connect to your database using this user.

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

• Instead, a separate user should be created granting it only the required permissions. I think this is what OP has done. OP has created a user named luke.

CREATE ROLE luke LOGIN PASSWORD 'pass';

• AWS RDS posgtres instance comes with an already created role name rds_superuser with restricted superuser access. luke should be granted rds_superuser privileges.

GRANT rds_superuser TO gis_admin;

• Create the database. Since database testgetwork is already created, we'll skip this step.

• Grant the luke all privileges on the testgetwork database.

GRANT ALL PRIVILEGES ON DATABASE testgetwork TO luke;

• Exit the session and reconnect with your AWS RDS PostgreSQL instance with user luke.

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

Now run all the extension commands:

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;