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"
asked Oct 31, 2014 at 23:02
2
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
answered Jan 20, 2016 at 10:54
David JonesDavid Jones
4,5593 gold badges32 silver badges45 bronze badges
4
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>;
answered Feb 7, 2017 at 22:46
James ChingJames Ching
6715 silver badges2 bronze badges
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
answered Jan 9, 2015 at 15:45
bionicseraphbionicseraph
8121 gold badge7 silver badges10 bronze badges
1
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 ....
answered Oct 27, 2015 at 15:09
Rafael OliveiraRafael Oliveira
2,7834 gold badges32 silver badges50 bronze badges
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
answered May 19, 2016 at 18:53
jorfusjorfus
2,42624 silver badges22 bronze badges
1
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.
answered Jan 30, 2015 at 14:45
Krystian CybulskiKrystian Cybulski
10.6k11 gold badges67 silver badges97 bronze badges
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;
answered Sep 7, 2020 at 8:11
DevonDahonDevonDahon
6,8004 gold badges58 silver badges97 bronze badges
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.
answered Jul 31, 2018 at 18:12
sprutsprut
714 bronze badges
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.
answered Apr 23, 2018 at 17:53
I need to change owner of table.
I created table:
CREATE TABLE example (some columns);
Then I tried to change owner:
ALTER TABLE database.expample OWNER TO "secondary";
and them I got this error:
ERROR: must be member of role "secondary"
Can anybody help me?
Thanks in advance.
asked Jun 21, 2015 at 19:16
DawenearDawenear
811 gold badge1 silver badge4 bronze badges
1
See this from the Postgresql documentation:
http://www.postgresql.org/docs/current/static/sql-altertable.html
You must own the table to use ALTER TABLE. To change the schema of a
table, you must also have CREATE privilege on the new schema. To alter
the owner, you must also be a direct or indirect member of the new
owning role, and that role must have CREATE privilege on the table’s
schema. (These restrictions enforce that altering the owner doesn’t do
anything you couldn’t do by dropping and recreating the table.
However, a superuser can alter ownership of any table anyway.)
Craig Ringer
298k72 gold badges669 silver badges755 bronze badges
answered Jun 21, 2015 at 20:02
CoryatJohnCoryatJohn
3241 silver badge6 bronze badges
1
On Feb 7, 2018 12:35 AM, «marcos sr» <msr(dot)mailing(at)gmail(dot)com> wrote:
Hello David thanks for attention,
CREATE USER «user» NOCREATEDB NOCREATEUSER NOINHERIT ENCRYPTED PASSWORD
‘database’
CREATE DATABASE «database» WITH OWNER «user»
The user creating the db is not psql default. Is a user who creates dbs on
server and have the roles creatdb and create role. Can i give to this user
a superuser privilege?
Sorry for the noob questions, but i’m new on postgres and have this thing
to resolve. Is a legacy system on the company.
Sorry for the top-posting. now it’s correct?
2018-02-06 16:13 GMT-02:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:
> On Tuesday, February 6, 2018, marcos sr <msr(dot)mailing(at)gmail(dot)com> wrote:
>
>> Hello
>>
>> I’m new on postgres. I’m trying to create a database with a user who have
>> CREATEDB role. But I’m getting this error
>>
>
> You need to provide the code that you are running, not just the resultant
> error message.
>
> David J.
>
Hi macros,
The user issuing the CREATE DATABASE must be
a member of the role that will be the owner of the
database.
Compare the below example:
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
Regards,
Pavan,
Sent from my phone
Я вошел в систему с учетной записью суперпользователя, и вот что я делаю:
1-> CREATE ROLE test WITH IN ROLE testroles PASSWORD 'testpasswd'
2-> CREATE SCHEMA AUTHORIZATION test
Роль создана правильно, но я получаю эту ошибку при попытке создать схему:
ERROR: must be member of role "test"
Заранее спасибо!
Ответы:
Я столкнулся с этой проблемой при использовании CREATE DATABASE
Amazon RDS. Я думаю, что по сути это то же самое, что и использование CREATE SCHEMA
.
При использовании Amazon RDS пользователь, выдающий CREATE DATABASE
учетную запись, должен быть членом роли, которая будет владельцем базы данных. В моем случае вызывается учетная запись суперпользователя, которую я использую root
, и я собираюсь создать роль, o
которая будет владеть базой данных 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
Я столкнулся с той же проблемой: он жалуется на то, что текущий (главный) пользователь, с которым вы вошли в систему, не является членом группы пользователей, для которой вы пытаетесь создать схему. Вы должны предоставить доступ к роли вашему главному пользователю, и это позволит вам создать SCHEMA без ошибок:
GRANT <role> TO <master_user>;
Вы используете RDS? Потому что я получаю ту же проблему, когда вхожу в систему как «суперпользователь», которую они для вас создают. Способ, которым я смог это исправить, заключался в создании новой групповой роли, включающей моего суперпользователя и пользователя, владеющего схемой. Таким образом, для вас это будет означать добавление вашего суперпользователя и тестового пользователя в новую группу ролей:
CREATE ROLE users NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT megausers TO testroles;
GRANT test TO testroles;
Теперь вы сможете создать свою шмею
Была эта проблема и с RDS.
Чтобы решить эту проблему:
Войти как суперпользователь
psql --host=xxxxxxx.rds.amazonaws.com --port=5432 --username=RDS_SUPERUSER_NAME --password --dbname=postgres
Создать пользователя
CREATE USER newuser WITH CREATEDB PASSWORD 'password';
Выйти
q
Войти как newuser
psql --host=xxxxxxx.rds.amazonaws.com --port=5432 --username=newuser --password --dbname=postgres
Создайте свою БД / схему
CREATE SCHEMA/DATABASE ....
Разве мы не должны просто предоставить членство администратора в роли службы?
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
Я столкнулся с этой проблемой и в RDS. Я вошел в систему как root
пользователь, создал роль с именем, myappuser
а затем попытался создать схему с именем superduper
, владельцем которой является myappuser
.
Я нашел решение, которое работает. Создайте myappuser
роль и убедитесь, что у этой роли есть хотя бы разрешение на создание баз данных (эта привилегия называется CREATEDB
). После создания myappuser
роли я вошел в базу данных как myappuser
и создал superduper
схему, пользователем которой является myappuser
. Это сработало без проблем.
Я столкнулся с той же проблемой. Чтобы решить эту проблему, я вошел в систему с вновь созданной ролью и создал базу данных. Как-то не работает грант в RDS Postgres.
Я только что столкнулся с этим при использовании Amazon RDS.
Многие ответы уже верны, но вы также можете просто изменить роль.
Вот моя реализация
psql -h ${PGHOST} -p ${PGPORT:-5432} -U ${PGUSER:-postgres} -c "ALTER USER renderer WITH CREATEDB PASSWORD '$RENDERPASSWORD'"
Поэтому при изменении пароля я также добавляю к роли разрешение роли CREATEDB.
Я исправил это, войдя в систему с postgres
пользователем (а затем применил свои изменения, которые в моем случае меняли владельца):
sudo -u postgres psql
ALTER DATABASE my_database OWNER TO new_user;
Есть полный бекап базы данных со всеми таблицами итд
При загрузке на тестовых локальный сервер из PGadmin все ок
А при загрузке на удаленный сервер из под phpPgAdmin при импорте получаю кучу ошибок
Но база вроде работает так как нужно
Объясните плз что это за ошибки и можно ли их игнорировать раз работает вроде все без проблем ?
SET SET SET SET SET SET SET SET SET CREATE TABLE user3_new.backup:38: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:53: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:81: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:96: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:122: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:137: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:162: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:177: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:205: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:220: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:248: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:263: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:307: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:322: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:349: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:364: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:394: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:409: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:435: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:450: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:476: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:491: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:514: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:529: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:552: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:567: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:589: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:604: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:633: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:648: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:673: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:688: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:710: ERROR: must be member of role "postgres" CREATE TABLE user3_new.backup:723: ERROR: must be member of role "postgres" CREATE TABLE user3_new.backup:742: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:757: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:783: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:798: ERROR: must be member of role "postgres" ALTER SEQUENCE CREATE TABLE user3_new.backup:829: ERROR: must be member of role "postgres" CREATE SEQUENCE user3_new.backup:844: ERROR: must be member of role "postgres" ALTER SEQUENCE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE