create role gis_admin login password 'gis_password'; GRANT rds_superuser TO gis_admin; create database gis; grant all privileges on database gis to gis_admin;

psql -h qgis-prd-17.cyongorao4kh.us-east-1.rds.amazonaws.com –username=gis_admin –password –dbname=gis CREATE EXTENSION postgis; CREATE EXTENSION postgis_raster; CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION postgis_topology; CREATE EXTENSION address_standardizer_data_us;

Verify

SELECT n.nspname AS “Name”,

pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;

transfer ownership

ALTER SCHEMA tiger OWNER TO gis_admin; ALTER SCHEMA tiger_data OWNER TO gis_admin; ALTER SCHEMA topology OWNER TO gis_admin;

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;

– This alters the permissions

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')

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;

SET search_path=public,tiger;

– Test it out again – Should return: – address | streetname | streettypeabbrev | zip – ———+————+——————+——- – 1 | Devonshire | Pl | 02109

SELECT address, streetname, streettypeabbrev, zip FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;

– Should return 1 SELECT topology.createtopology('my_new_topo',26986,0.5);