Error must be member of role postgres - Исправление ошибок и поиск оптимальных решений проблем

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

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

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

Had this problem with RDS too.

To solve it:

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

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

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

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

Источник

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

Выйти

Войти как 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

Источник