Cant create postgres base type (with hstore/pgcrypto extensions) on amazon RDS

2.4k views Asked by At

We are moving from our own native postgres installation on an EC2 instance to amazon RDS for postgres. And the data migration is done via dump and restore like this:

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

On restore, we see failures saying:

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

And so on!

We have added the hstore and pgcryptop extensions to template1 database on our Amazon RDS instance[1] so we have these extensions but we cant create a type because creating a base type in postgres 9.3 needs superuser[2]. postgres user is disallowed to do this and the amazon RDS documentation says:

When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions

rdsadmin is the superuser but I cant log in with rdsadmin.

[email protected]:~> 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

Please help.

[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

0

There are 0 answers