Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (id int primary key);
CREATE TABLE "groups" (id int primary key);
CREATE TABLE pages (id int primary key);
CREATE TABLE group_roles (
  id int PRIMARY KEY,
  group_id int REFERENCES "groups"(id));
CREATE TABLE user_memberships (
  user_id int REFERENCES users(id),
  group_role_id int REFERENCES group_roles(id)
);
insert into users values (1),(2);
insert into "groups" values (1),(2);
insert into group_roles values (1,1),(2,2);
insert into user_memberships values (1,1),(2,2);

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 2

INSERT 0 2

INSERT 0 2

INSERT 0 2

BEGIN;

DELETE FROM user_memberships
WHERE user_id = 1
AND group_role_id IN 
  (    SELECT id 
       FROM group_roles 
       WHERE group_id = 1 )
RETURNING *;

ROLLBACK;

BEGIN

user_id
group_role_id

1
1

DELETE 1

ROLLBACK

DELETE FROM user_memberships
USING generate_series(1,array_length(ARRAY[[1,1],[2,2]],1))as a(n)
WHERE user_id = (ARRAY[[1,1],[2,2]])[n][1]
AND group_role_id IN 
  (    SELECT id 
       FROM group_roles 
       WHERE group_id = (ARRAY[[1,1],[2,2]])[n][2] )
RETURNING *;

user_id
group_role_id
n

1
1
1

2
2
2

DELETE 2