135

I'm logged in with a superuser account and this is the process I'm doing:

1-> CREATE ROLE test WITH IN ROLE testroles PASSWORD 'testpasswd'
2-> CREATE SCHEMA AUTHORIZATION test

The role is correctly created but I'm getting this error when trying to create the Schema:

ERROR:  must be member of role "test"
David Jones
  • 4,766
  • 3
  • 32
  • 45
Ultranuke
  • 1,735
  • 2
  • 15
  • 21

9 Answers9

219

I ran into this issue when using CREATE DATABASE on Amazon RDS. I think it's essentially the same as using CREATE SCHEMA.

When using Amazon RDS, the user issuing the CREATE DATABASE must be a member of the role that will be the owner of the database. In my case, the superuser account I'm using is called root, and I'm going to create a role o which is going to own a database d:

postgres=> CREATE ROLE o;
CREATE ROLE

postgres=> CREATE DATABASE d OWNER = o;
ERROR:  must be member of role "o"

postgres=> GRANT o TO root;
GRANT ROLE

postgres=> CREATE DATABASE d OWNER = o;
CREATE DATABASE
AATHITH RAJENDRAN
  • 4,689
  • 8
  • 34
  • 58
David Jones
  • 4,766
  • 3
  • 32
  • 45
  • 7
    Same thing, but I found the new user didn't need to have the CREATEDB permission. I created a regular user, granted that user role to my admin user, then created the database using the admin user with the owner set to the regular user. – Nick Spacek Feb 01 '16 at 18:53
  • 2
    There's a short write up describing the cause of this effect on RDS here.. https://blog.2ndquadrant.com/the-rds_superuser-role-isnt-that-super – Molomby Feb 28 '18 at 01:26
  • 5
    I had this same error on Google Cloud SQL for PostgreSQL and this answer was the solution except I ran `GRANT o TO postgres;` before creating the database. – Simon Watson Nov 23 '19 at 08:13
  • 3
    **WARNING** Don't forget to remove the role from `postgres` user with `REVOKE` before you enable IAM authentication for that user. Otherwise you cannot connect to the database with user `postgres` anymore until you disable IAM authentication in RDS. – UltimaWeapon Jan 24 '22 at 12:07
76

I came across this same problem, it is complaining about the current(master) user you are logged in with is not a member of the user group you are trying to create the schema for. You should grant the role access to your master user and it will let you create the SCHEMA without error:

GRANT <role> TO <master_user>;
AATHITH RAJENDRAN
  • 4,689
  • 8
  • 34
  • 58
James Ching
  • 761
  • 5
  • 2
24

Are you using RDS? Because I get the same issue when I log in as the "superuser" that they create for you. The way that I was able to fix this was to create a new group role that included my super user and the user who owned the schema. So for you this would mean adding your super user and test user to a new roles group:

CREATE ROLE users NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT megausers TO testroles;
GRANT test TO testroles;

Now you should be able to create your schmea

bionicseraph
  • 812
  • 1
  • 7
  • 10
  • 1
    This is a bit confused, but I did use this to eventually work out what was going on. See my response: http://stackoverflow.com/a/34898033/242457 – David Jones Jan 20 '16 at 10:54
22

Had this problem with RDS too.

To solve it:

Login as superuser

psql --host=xxxxxxx.rds.amazonaws.com --port=5432 --username=RDS_SUPERUSER_NAME --password --dbname=postgres

Create the User

CREATE USER newuser WITH CREATEDB PASSWORD 'password';

Logout

\q

Login as newuser

psql --host=xxxxxxx.rds.amazonaws.com --port=5432 --username=newuser --password --dbname=postgres

Create your DB/Schema

CREATE SCHEMA/DATABASE ....
Rafael Oliveira
  • 2,823
  • 4
  • 33
  • 50
10

Don't we just have to grant the admin user membership to the service role?

create role service_role with password 'some_password';
create database service_db with owner service_role;
ERROR:  must be member of role "service_role"
grant admin_user service_role;
GRANT ROLE
create database service_db with owner service_role;
CREATE DATABASE
jorfus
  • 2,804
  • 27
  • 23
4

I have encountered this issue on RDS as well. I logged in as the root user, created a role named myappuser and then tried creating a schema called superduper whose owner is myappuser.

I found a solution which works. Create the myappuser role, and make sure that this role at least has the permission to create databases (the privilege is called CREATEDB). After creating the myappuser role, I logged into the database as myappuser and created the superduper schema whose user is myappuser. This worked without any problems.

mattp
  • 66
  • 8
Krystian Cybulski
  • 10,789
  • 12
  • 67
  • 98
2

I fixed it by logging in with postgres user (and then apply my changes, which was changing ownership in my case):

sudo -u postgres psql
ALTER DATABASE my_database OWNER TO new_user;
DevonDahon
  • 7,460
  • 6
  • 69
  • 114
1

I just ran into this using Amazon RDS.

A lot of the answers are already correct, but you can also just alter the role.

Here was my implementation

psql -h ${PGHOST} -p ${PGPORT:-5432} -U ${PGUSER:-postgres} -c "ALTER USER renderer WITH CREATEDB PASSWORD '$RENDERPASSWORD'"

So while changing the password, I am also adding the CREATEDB role permission to the role.

sprut
  • 101
  • 1
  • 4
0

I was facing the same issue. To resolve this, I logged in with the newly created role and created the database. Somehow, grant does not work in RDS Postgres.

Sid
  • 36
  • 1