Why does PostgreSQL not check permissions of the table referencing (with ON DELETE CASCADE) a table being deleted from?
0
votes
1
answer
39
views
On PostgreSQL 15, I have the following objects:
appuser_locked
table is referencing appuser
table with ON DELETE CASCADE
. application_role
is granted DELETE
on appuser
table but not on appuser_locked
table.
Why can application_role
delete rows in appuser_locked
by leveraging the ON DELETE CASCADE
when deleting from the appuser
table despite not having DELETE
granted on appuser_locked
?
Aren't the permissions on appuser_locked
table suppose to apply and prevent deletion?
Reproducible case:
------------------
CREATE ROLE owner_role WITH INHERIT LOGIN;
CREATE ROLE application_role WITH INHERIT LOGIN;
CREATE DATABASE mydb
TEMPLATE = template0
ENCODING = 'UTF8'
OWNER = owner_role;
\connect mydb
SET ROLE owner_role;
CREATE TABLE appuser(
appuser_id integer NOT NULL,
CONSTRAINT appuser_pkey PRIMARY KEY (appuser_id)
);
GRANT SELECT, INSERT, DELETE ON appuser TO application_role;
CREATE TABLE appuser_locked(
appuser_id integer NOT NULL,
CONSTRAINT appuser_locked_pkey PRIMARY KEY (appuser_id),
CONSTRAINT appuser_locked_fkey FOREIGN KEY (appuser_id)
REFERENCES appuser (appuser_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
COMMENT ON TABLE appuser_locked IS 'Indication that a given user is locked for modification by owner_role.';
GRANT SELECT ON appuser_locked TO application_role;
\dp+ appuser
\dp+ appuser_locked
INSERT INTO appuser (appuser_id)
VALUES
(1),
(2);
INSERT INTO appuser_locked (appuser_id)
VALUES
(1);
SET ROLE application_role;
TABLE appuser;
TABLE appuser_locked;
DELETE FROM appuser_locked;
TABLE appuser;
TABLE appuser_locked;
DELETE FROM appuser;
TABLE appuser;
TABLE appuser_locked;
Case output:
------------------
CREATE ROLE owner_role WITH INHERIT LOGIN;
CREATE ROLE
CREATE ROLE application_role WITH INHERIT LOGIN;
CREATE ROLE
CREATE DATABASE mydb
TEMPLATE = template0
ENCODING = 'UTF8'
OWNER = owner_role;
CREATE DATABASE
\connect mydb
You are now connected to database "mydb" as user "postgres".
SET ROLE owner_role;
SET
CREATE TABLE appuser(
appuser_id integer NOT NULL,
CONSTRAINT appuser_pkey PRIMARY KEY (appuser_id)
);
CREATE TABLE
GRANT SELECT, INSERT, DELETE ON appuser TO application_role;
GRANT
CREATE TABLE appuser_locked(
appuser_id integer NOT NULL,
CONSTRAINT appuser_locked_pkey PRIMARY KEY (appuser_id),
CONSTRAINT appuser_locked_fkey FOREIGN KEY (appuser_id)
REFERENCES appuser (appuser_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE
COMMENT ON TABLE appuser_locked IS 'Indication that a given user is locked for modification by owner_role.';
COMMENT
GRANT SELECT ON appuser_locked TO application_role;
GRANT
\dp+ appuser
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+---------------------------------+-------------------+----------
public | appuser | table | owner_role=arwdDxt/owner_role +| |
| | | application_role=ard/owner_role | |
(1 row)
\dp+ appuser_locked
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------------+-------+-------------------------------+-------------------+----------
public | appuser_locked | table | owner_role=arwdDxt/owner_role+| |
| | | application_role=r/owner_role | |
(1 row)
INSERT INTO appuser (appuser_id)
VALUES
(1),
(2);
INSERT 0 2
INSERT INTO appuser_locked (appuser_id)
VALUES
(1);
INSERT 0 1
SET ROLE application_role;
SET
TABLE appuser;
appuser_id
------------
1
2
(2 rows)
TABLE appuser_locked;
appuser_id
------------
1
(1 row)
DELETE FROM appuser_locked;
ERROR: permission denied for table appuser_locked
TABLE appuser;
appuser_id
------------
1
2
(2 rows)
TABLE appuser_locked;
appuser_id
------------
1
(1 row)
DELETE FROM appuser;
DELETE 2
TABLE appuser;
appuser_id
------------
(0 rows)
TABLE appuser_locked;
appuser_id
------------
(0 rows)
Asked by soliz
(293 rep)
May 9, 2025, 10:21 AM
Last activity: May 9, 2025, 11:06 AM
Last activity: May 9, 2025, 11:06 AM