Create an Postgres user in AWS RDS with createDB permissions

Question

I have a db instance that contains multiples databases. I want to create one user to each database where the user can only access that database. I am using Prisma ORM, that requires that the user can create a database (known as shadow database) but I am struggling with this user creation.

I would like some tips about which SQL commands should I use.

Answer

*These commands are assuming you are using psql cli as such and can be executed via SQL gui also.*

To create a user you will need to execute the following command

`CREATE USER username with CREATEDB password 'qwerty12345';`

To grant SELECT access to the database created already you will need to grant access as follows assuming the schema is public.
Select the database to assign the access with with

`\c databasename;`

Then assign access to all future tables and views in public schema.

`ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO username;`

Then assign access to existing views and tables in the public schema as follows.

`GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;`

Replace username, databasename and the random password with your own values.

Create an Postgres user in AWS RDS with createDB permissions

Relevant content