Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

CREATE SCHEMA AUTHORIZATION fails with must be member of role

ErrorERROR: 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, Amazon RDS for PostgreSQL
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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"
  2. 2Match what you see, then jump to its fix

    1. 1The error names the role you put after AUTHORIZATION or OWNERThe admin user is not a member of the target owner roleFix
    2. 2GRANT shows the role as a member, and the same error remainsMembership exists but without the SET optionFix
    3. 3The GRANT itself fails with a permission errorYou lack the right to grant the roleFix

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+ reporting

Confirmed by postgresql.org

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.