[OPENIDM-14654] Database creation on Azure Database for PostgreSQL fails with - ERROR: must be member of role "openidm" Created: 29/Apr/20  Updated: 23/Sep/20  Resolved: 27/Jul/20

Status: Closed
Project: OpenIDM
Component/s: Module - Repository JDBC
Affects Version/s: 6.5.0.3
Fix Version/s: 7.0.0

Type: Bug Priority: Major
Reporter: Brad Tarisznyas Assignee: Chris Drake
Resolution: Fixed Votes: 0
Labels: CLARK, release-notes
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Azure Database for PostgreSQL - underlying PosgreSQL version 10


Target Version/s:
Verified Version/s:
QA Assignee: Alexander Dracka
Story Points: 1
Sprint: 2020.07 - IDM
Support Ticket IDs:

 Description   

When running the createuser.sql setup script for PostgreSQL on Azure Database for PostgreSQL using the admin user (in my case pgadmin) that is setup during database creation, the following error is encountered:

 

ERROR:  must be member of role "openidm"

Running the commands individually shows the create database command failing:

 

 

postgres=> create user openidm with password 'openidm';
CREATE ROLE
postgres=> create database openidm encoding 'utf8' owner openidm;
ERROR:  must be member of role "openidm"

The roles shows:

postgres=> \du
                                                            List of roles
      Role name       |                   Attributes                   |                          Member of
----------------------+------------------------------------------------+--------------------------------------------------------------
 azure_pg_admin       | Cannot login, Replication                      | {}
 azure_superuser      | Superuser, Create role, Create DB, Replication | {}
 openidm              |                                                | {}
 pg_monitor           | Cannot login                                   | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
 pg_read_all_settings | Cannot login                                   | {}
 pg_read_all_stats    | Cannot login                                   | {}
 pg_signal_backend    | Cannot login                                   | {}
 pg_stat_scan_tables  | Cannot login                                   | {}
 pgadmin              | Create role, Create DB, Replication            | {azure_pg_admin}

The following Stackoverflow post suggests that the user creating the database (pgadmin) must be a member of the openidm role.

https://stackoverflow.com/questions/26684643/error-must-be-member-of-role-when-creating-schema-in-postgresql

Adding the pgadmin user to the openidm role using: 

grant openidm to pgadmin

sees the database created as expected:

postgres=> grant openidm to pgadmin;
GRANT ROLE
postgres=> \du
                                                            List of roles
      Role name       |                   Attributes                   |                          Member of
----------------------+------------------------------------------------+--------------------------------------------------------------
 azure_pg_admin       | Cannot login, Replication                      | {}
 azure_superuser      | Superuser, Create role, Create DB, Replication | {}
 openidm              |                                                | {}
 pg_monitor           | Cannot login                                   | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
 pg_read_all_settings | Cannot login                                   | {}
 pg_read_all_stats    | Cannot login                                   | {}
 pg_signal_backend    | Cannot login                                   | {}
 pg_stat_scan_tables  | Cannot login                                   | {}
 pgadmin              | Create role, Create DB, Replication            | {azure_pg_admin,openidm}

postgres=> create database openidm encoding 'utf8' owner openidm;
CREATE DATABASE
postgres=>

 



 Comments   
Comment by Lana Frost [ 23/Jul/20 ]

Reopening to add to release notes

Comment by Alexander Dracka [ 23/Sep/20 ]

Fixed

Verified with the OpenIDM version "7.0.0" (build: 20200807153350, revision: 755f6ae) and local Postgres 11.3

Generated at Sun Sep 27 20:37:37 UTC 2020 using Jira 7.13.12#713012-sha1:6e07c38070d5191bbf7353952ed38f111754533a.