unable to create schema on amazon rds for postgres

Question

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

Answer 1

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.

Answer 2

solution for rds postgres 15+

if you get errors like this:

dbname=> CREATE TABLE sk_project (
        project_name VARCHAR(50), 
        id SERIAL NOT NULL, 
        date_created TIMESTAMP WITHOUT TIME ZONE, 
        date_modified TIMESTAMP WITHOUT TIME ZONE, 
        PRIMARY KEY (id)
)
;
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE sk_project (

then in new versions of RDS Postgres (15+):

login into psql with your master rds user, then:

CREATE USER dbuser WITH PASSWORD 'RANDOM-PASSWORD';
CREATE DATABASE dbname;
\c dbname;

GRANT ALL ON dbname to dbuser;
ALTER DATABASE dbname OWNER TO dbuser;

export PASSWORD="RANDOM-PASSWORD"
psql postgresql://dbuser:${PASSWORD}@db-postgres.chipb2whnj74.us-east-1.rds.amazonaws.com:5432/dbname

and you will be able to run commands under that dbuser