in data science

Setting up PostGIS on Amazon RDS

Amazon provides step-by-step directions to install PostGIS on a Postgres RDS but does not tell the system admin how to grant access to a user. In this post I build on Amazon's work to provide a complete set of instructions.

Please note that, if you want to use PostGIS on multiple databases -- even if they're on the same server -- you will probably need to install PostGIS on each. A user can only access one database at a time natively (foreign data wrappers are changing that), so a PostGIS installation on one database will not be accessible from another database without a foreign data wrapper.

Here goes:

  1. Log into the database with the username that created the database
  2. Run the following 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;
  3. Test tiger with the following SQL statement:
    select na.address, na.streetname, na.streettypeabbrev, na.zip 
    from normalize_address('1 Devonshire Place, Boston, MA 02109') as na;

    You should see output like this:

  4. Screen Shot 2015-05-14 at 1.42.04 AMTest topology with the following SQL statement:
    select topology.createtopology('my_new_topo2',26986,0.5);

    You should see output like this:

  5. Screen Shot 2015-05-14 at 2.23.31 AMThis is where Amazon's instructions end. If you log out and log back in using the same account, step 3 (the test) will not work. Postgres knew to search the tiger schema for objects during the installation session, but it forgot as soon as you logged out. Tell Postgres to remember this search:
    alter role [user] set search_path=public, tiger;
  6. Other users will also need schema and table permissions for the PostGIS objects (you may need to alter the permissions to meet your specific needs):
    grant usage on schema tiger to [user];
    grant usage on schema topology to [user];
    grant select, insert, update, delete on all tables in schema tiger to [user];
    grant select, insert, update, delete on all tables in schema topology to [user];

The user can be a role, which would grant permissions to everyone with that role. If the role is "public," all users have access by default.

We're done!

Write a Comment

Comment