PostgreSQL – How to Create Schema on Amazon RDS

amazon-rdspgadminpostgresql

I'm trying to create a schema on a postgres instance located on Amazon rds.

That schema would be used to import data from an existing schema built on a local db.

Issue is I do not have permissions to create schemas on RDS.

The user dw_etl I'm using to connect to the postgres instance has the role rds_superuser, which is similar to the superuser role. dw_etl should be able to create new schemas.

Questions:

  • Is there a way to create schemas with RDS?
  • If I cannot create schemas on RDS, what would be the best way to "silos" the data? Will the public schema became some kind of dump?

Thanks

Best Answer

RDS PostgreSQL in this sense is no different from any other PostgreSQL installation. You can, of course, create schemas and whatever objects you want.

The problem here is missing privileges. By default, the owner of the DB (the role that created it) has full access (CREATE, CONNECT, TEMP) on the DB, and can grant these to other roles, too.

Connect to the DB with the role that created it, then you can try to create a schema to prove the above. To make other users able to create schemas inside this DB, do the following:

GRANT ALL ON DATABASE your_db TO andy_k;

After this, you can log in as andy_k, and create schemas.

Related Question