CREATE SCHEMA AUTHORIZATION fails with must be member of role
ERROR: must be member of role "app_owner"Short answer
To create an object owned by another role you must be able to SET ROLE to that role. On Amazon RDS the admin user is a member of rds_superuser, which does not by itself let it SET ROLE to an app's owner role. Grant the owner role to the admin user, then rerun the command.
Fix it fast
1Find out which cause you have
Check who you are connected as, and list the roles with their memberships.
psql -c "SELECT current_user;" psql -c "\du"2Match what you see, then jump to its fix
- 1
The error names the role you put after AUTHORIZATION or OWNERThe admin user is not a member of the target owner roleFix - 2
GRANT shows the role as a member, and the same error remainsMembership exists but without the SET optionFix - 3
The GRANT itself fails with a permission errorYou lack the right to grant the roleFix
- 1
What does the error look like?
ERROR: must be member of role "app_owner"CREATE SCHEMA ... AUTHORIZATION fails for the RDS admin user
Cause 1 of 3
The admin user is not a member of the target owner role
1Check
The manual says to create an object owned by another role you must be able to SET ROLE to it; CREATE SCHEMA says the same for AUTHORIZATION. A superuser bypasses the check; the first RDS user is a member of rds_superuser.
SET ROLE app_owner;2Fix
Make the admin user a member of the owner role, then create the schema.
GRANT app_owner TO postgres;
CREATE SCHEMA reporting AUTHORIZATION app_owner;3Verify
The schema exists and has the intended owner.
\dn+ reportingConfirmed by postgresql.org
Cause 2 of 3
Membership exists but without the SET option
1Check
Role membership has SET, INHERIT and ADMIN options. SET controls whether the member can change to the role with SET ROLE, and it defaults to TRUE. A grant made WITH SET FALSE does not allow it.
SET ROLE app_owner;2Fix
Regrant the membership with SET enabled. Omitted options keep their current values.
GRANT app_owner TO postgres WITH SET TRUE;3Verify
SET ROLE works and the original command succeeds.
SET ROLE app_owner;
RESET ROLE;Confirmed by postgresql.org
Cause 3 of 3
You lack the right to grant the role
1Check
Only a superuser, or a member holding ADMIN OPTION on the role, can grant it to someone else. Ordinary users cannot.
GRANT app_owner TO postgres;2Fix
Run the GRANT as the role that created app_owner, or ask whoever holds ADMIN OPTION on it to do it.
GRANT app_owner TO postgres WITH ADMIN OPTION;3Verify
The admin user can now grant and set the role.
SET ROLE app_owner;
RESET ROLE;Confirmed by postgresql.org
How do I stop it coming back?
- In provisioning scripts, grant each owner role to the admin user right after creating it.
- Keep app owner roles separate from the admin user, and document who may SET ROLE to them.
- Remember that on RDS the first user is a member of rds_superuser, and still needs membership of the owner role.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
ALTER SCHEMA ... OWNER TO never finishes in PostgreSQL
Another session shows pg_blocking_pids(pid) with a non-empty list for the waiting backend
A statement that hangs instead of failing is usually waiting for a lock that another session holds.
4 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix