Sample Header Ad - 728x90

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