CODEWITHSUNDEEP
postgresqlamazon-rdsroles
Ultranuke
Ultranuke

Reputation: 1885

"ERROR: must be member of role" When creating schema in PostgreSQL

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"

Upvotes: 150

Views: 200373

Answers (10)

user2959589
user2959589

Reputation: 617

The responses above are correct, you basically need to first create the user / owner, and then do:

grant newusername to postgres;

and then postgres can create a new database with owner of newusername.

This is a quirk of RDS.

If you want to do this operation in Ansible, you need to do three steps:

- name: Create new database owner
  community.postgresql.postgresql_user:
    name: "{{ database_username }}"
    password: "{{ database_password }}"
    host: "{{ database_host }}"
    login_password: "{{ database_master_password }}"

- name: Grant role of database owner to Postgres
  community.postgresql.postgresql_membership:
    groups: "{{ database_username }}"
    target_role: postgres
    state: present
    host: "{{ database_host }}"
    login_password: "{{ database_master_password }}"

- name: Create database with the new owner
  community.postgresql.postgresql_db:
    name: "{{ database_name }}"
    owner: "{{ database_username }}"
    host: "{{ database_host }}"
    login_password: "{{ database_master_password }}"

Definitely took me some effort to figure this out, hope this helps others.

Upvotes: 0

James Ching
James Ching

Reputation: 891

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>;

Upvotes: 89

David Jones
David Jones

Reputation: 5139

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

Upvotes: 233

DevonDahon
DevonDahon

Reputation: 8350

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;

Upvotes: 3

sprut
sprut

Reputation: 111

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.

Upvotes: 1

Sid
Sid

Reputation: 36

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.

Upvotes: 0

Krystian Cybulski
Krystian Cybulski

Reputation: 11108

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.

Upvotes: 4

jorfus
jorfus

Reputation: 3088

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

Upvotes: 10

Rafael Oliveira
Rafael Oliveira

Reputation: 2923

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

Upvotes: 24

bionicseraph
bionicseraph

Reputation: 832

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

Upvotes: 25

Related Questions