New collection creation is giving error

Hello, we are facing issue while creating a metabase collection. The error we are seeing

insert or update on table \"permissions\" violates foreign key constraint \"fk_permissions_group_id\"\n Detail: Key (group_id)=(3) is not present in table \"permissions_group\"

please help look into the issue.

The RCA we did:
Metabase is assigning permissions to the new collection — deciding who can view, curate, or manage it. These permissions are tied to permission groups, which are entries in the permissions_group table. When we are creating a new collection, Metabase is attempting to-> Create permission rows in the permissions table referencing multiple groups, including group_id=3.
Metabase expects group=3 to be present by default. It seems blind for missing groups.

We checked the permission groups table(postgres) and:

metabase_b2c=> SELECT * FROM permissions_group ORDER BY id;
 id |            name             | entity_id 
----+-----------------------------+-----------
  1 | All Users                   | 
  2 | Administrators              | 
  4 | View only access            |

and in permission table we are also seeing

metabase_b2c=> SELECT * FROM permissions WHERE group_id IN (3, 9);
 id  |        object         | group_id |   perm_value   |        perm_type        | collection_id 
-----+-----------------------+----------+----------------+-------------------------+---------------
  35 | /collection/root/     |        3 |                |                         |              
 422 | /collection/550/      |        3 | read-and-write | perms/collection-access |           550
 428 | /collection/558/      |        3 | read-and-write | perms/collection-access |           558

which looks like few collection prev had the permission corresponding to group_id=3.
Please help resolve the issue.
Plus: Let me know whether manual insertion(of groupid=3 in permission group) can/should be done to fix the issue.

Please post the Diagnostic info from your install to give us some context.

Include your diagnostic info. Go to Admin settings > Troubleshooting > Diagnostic info.

Also, that group shouldn't just disappear on its own. I have no group id 3 in my install from 0.54.x, so it doesn't seem to be installed "by default" on the open-source version at least.

Can you check backups of your app database and see if group id 3 appears in one of them?

Hey, heres the diagnostic report

{
  "browser-info": {
    "language": "en-GB",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/137.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "metabase-info": {
    "databases": [
      "redshift",
      "athena",
      "h2",
      "mysql"
    ],
    "run-mode": "prod",
    "plan-alias": "",
    "version": {
      "date": "2025-03-04",
      "tag": "v0.53.5",
      "hash": "2097d00"
    },
    "settings": {
      "report-timezone": "Asia/Calcutta"
    },
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "15.7"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.7.4"
      }
    }
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "21.0.6+7-LTS",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "21.0.6",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "21.0.6+7-LTS",
    "os.name": "Linux",
    "os.version": "5.10.237-230.949.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  }
}

Also, in permission table i am seeing

metabase_b2c=> SELECT * FROM permissions WHERE group_id IN (3, 9);
 id  |        object         | group_id |   perm_value   |        perm_type        | collection_id 
-----+-----------------------+----------+----------------+-------------------------+---------------
  35 | /collection/root/     |        3 |                |                         |              
 422 | /collection/550/      |        3 | read-and-write | perms/collection-access |           550
 428 | /collection/558/      |        3 | read-and-write | perms/collection-access |           558

which looks like few collection prev had the permission corresponding to group_id=3.
Is it possible that older version of metabase had group_id=3 by default, and in the current version its not used, if so will upgrading the metabase to 0.54.x help ?

Is it possible that older version of metabase had group_id=3 by default, and in the current version its not used, if so will upgrading the metabase to 0.54.x help ?

It seems unlikely that it was a deleted group. At least in 0.55.x, the FKs are set DELETE CASCADE so deleting the group would delete permissions related to it, which might have broken user configs.

It is more likely your database suffered some sort of data corruption or loss. You seem to be running Metabase in AWS (Amazon Linux 2); is the app database server RDS, Aurora, or is it a self-hosted PostgreSQL instance?

I went through release notes for the PostgreSQL 15.x series and didn't see any foreign-key related issues that might result in broken FK constraints.

In the meantime, recreating group id 3 by inserting a row with that ID into permissions_group will at least address the error and allow you to create collections again. Back up your database before you do this, of course.

Other places to look for that group_id, based on the referenced-by FKs:

    TABLE "connection_impersonations" CONSTRAINT "fk_conn_impersonation_group_id" FOREIGN KEY (group_id) REFERENCES permissions_group(id) ON DELETE CASCADE DEFERRABLE
    TABLE "data_permissions" CONSTRAINT "fk_data_permissions_ref_permissions_group" FOREIGN KEY (group_id) REFERENCES permissions_group(id) ON DELETE CASCADE DEFERRABLE
    TABLE "sandboxes" CONSTRAINT "fk_gtap_group_id" FOREIGN KEY (group_id) REFERENCES permissions_group(id) ON DELETE CASCADE DEFERRABLE
    TABLE "notification_recipient" CONSTRAINT "fk_notification_recipient_permissions_group_id" FOREIGN KEY (permissions_group_id) REFERENCES permissions_group(id) ON DELETE CASCADE DEFERRABLE
    TABLE "permissions_group_membership" CONSTRAINT "fk_permissions_group_group_id" FOREIGN KEY (group_id) REFERENCES permissions_group(id) ON DELETE CASCADE DEFERRABLE
    TABLE "permissions" CONSTRAINT "fk_permissions_group_id" FOREIGN KEY (group_id) REFERENCES permissions_group(id) ON DELETE CASCADE DEFERRABLE
1 Like

Unless there's a lot of work gone into that server, I'd be very tempted to just start from scratch. If it is due to a database corruption, I'd be concerned that something else is corrupted too. You may not find out what for some time, then lose loads more work or have the server refuse to start.

I dug around the Metabase GitHub repo and found a migration that deletes a group.

Back in 2022, Metabase used to ship with a Slack bot (MetaBot) that was deprecated. In v0.43 the supporting group (called 'MetaBot') was deleted. If somehow a user had a Metabase install prior to 0.43 running on a app database with broken/disabled foreign keys and upgraded to 0.43 or later, that group would be deleted and would leave a dangling reference. That would explain why a particular group ID would be missing.

It still would have required a broken database or disabled foreign key checking to leave behind any artifacts, best I can tell... and a very, very old Metabase install.

I'm still leaning towards a corrupted database for this user's issue, though.