CREATE ROLE role_a;
CREATE ROLE role_b;

SET SESSION AUTHORIZATION role_a;

CREATE TABLE pk_1 (
    id serial primary key,
    val text
);

CREATE TABLE fk_1 (
    id serial primary key,
    val text,
    pk_1_id int references pk_1 (id) ON DELETE RESTRICT
);

CREATE TABLE pk_2 (
    id serial primary key,
    val text
);

CREATE TABLE fk_2 (
    id serial primary key,
    val text,
    pk_2_id int references pk_2 (id) ON DELETE CASCADE
);

INSERT INTO pk_1 VALUES (1, 'a'), (2,'b');
INSERT INTO fk_1 (pk_1_id, val) VALUES (1, 'c'), (1, 'd'), (2, 'e'), (2, 'f');

INSERT INTO pk_2 VALUES (1, 'a'), (2,'b');
INSERT INTO fk_2 (pk_2_id, val) VALUES (1, 'c'), (1, 'd'), (2, 'e'), (2, 'f');

GRANT SELECT, DELETE ON TABLE pk_1, pk_2 TO role_b;
GRANT SELECT ON TABLE fk_1, fk_2 TO role_b;
REVOKE DELETE ON TABLE fk_1, fk_2 FROM role_b;

SET SESSION AUTHORIZATION role_b;

TABLE pk_1;
TABLE fk_1;

TABLE pk_2;
TABLE fk_2;

-- These should both fail, because role_b does not have DELETE permissions
DELETE FROM fk_1 WHERE id = 1;
DELETE FROM fk_2 WHERE id = 1;

TABLE pk_1;
TABLE fk_1;

TABLE pk_2;
TABLE fk_2;

-- Failure/success here will be the basic constraints definition defaults, including permissions, etc
DELETE FROM pk_1 WHERE id = 1;
DELETE FROM pk_2 WHERE id = 1;

-- DELETE CASCADE should treat existing constraints as CASCADE
DELETE CASCADE FROM pk_1 WHERE id = 2;
DELETE CASCADE FROM pk_2 WHERE id = 2;

TABLE pk_1;
TABLE fk_1;

TABLE pk_2;
TABLE fk_2;

SET SESSION AUTHORIZATION postgres;

DROP TABLE fk_1;
DROP TABLE fk_2;
DROP TABLE pk_1;
DROP TABLE pk_2;

DROP ROLE role_a;
DROP ROLE role_b;
