I want to address a failing in my prior post on my GOTO postgres configuration because it fails for RDS postgres databases. Luckily, this change will work for RDS postgres and self-managed installations. Woohoo!
RDS's most privileged user is
rds_superuser is akin to a regular user that has privileges to create postgres roles. And, unlike a postgres
rds_superuser does not have privileges to create and manage objects for another postgres role. This is the crux of the problem with my previous configuration, and the reason you see errors like
ERROR: must be a member of the role "owner".
Since we cannot create objects for other users with
rds_superuser, we separate role management and database object management. To manage roles, we rely on
rds_superuser. To create database objects and control access, we rely on the
owner. So the configuration from my previous post needs a few changes. Here's the gist: