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.
dw_etl I’m using to connect to the postgres instance has the role
rds_superuser, which is similar to the
dw_etl should be able to create new schemas.
- 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
publicschema became some kind of dump?
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.