Predefined role(s) to use in PostgreSQL in order to create a login role with privileges to automate database development

Question

I am in the process of setting up an AWS Aurora (PostgreSQL) 15.x instance for a Java-based application. I only have access to the instance through the rds_superuser account — not psql.

I am configuring three login roles initially:

1. A read-only user_ro login role — based on pg_read_all_data.
2. A read/write user_rw login role — based on pg_read_all_data and pg_write_all_data.
3. A user_liquibase login role with privileges to automate database development by doing schema changes, seed data.

However, I'm unsure of what predefined role I can use for user_liquibase that allows me to create and/or drop database objects. I would prefer to configure user_liquibase like user_rw by granting it pg_read_all_data and pg_write_all_data privileges, but I'm missing the rest of the privileges necessary for these tasks.

Can anyone suggest a predefined role to accomplish this?

I am not familiar with these tasks as I am more on the development side, but this time I need to set up the instance as well.

Answer 1

pg_write_all_data allows you to INSERT, UPDATE and DELETE on all existing tables.

To create an object, you need to have the CREATE privilege on the schema where you create the object.

To drop an object, you must be the owner of the object or a member of the owning role.