Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-14654

Database creation on Azure Database for PostgreSQL fails with - ERROR: must be member of role "openidm"

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 6.5.0.3
    • Fix Version/s: 7.0.0
    • Environment:
      Azure Database for PostgreSQL - underlying PosgreSQL version 10
    • Target Version/s:
    • 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=>
      

       

        Attachments

          Activity

            People

            • Assignee:
              cgdrake Chris Drake
              Reporter:
              bradley.tarisznyas Brad Tarisznyas
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: