Aias00 opened a new issue, #7584:
URL: https://github.com/apache/shenyu/issues/7584
## Problem
The `data_permission` table has **no unique constraint on its logical
business key**. It is declared with only a surrogate primary key in every
dialect:
- `db/init/mysql/schema.sql:174-182` — `PRIMARY KEY (id) USING BTREE`
- `db/init/pg/create-table.sql:197` + `:2049` — `CONSTRAINT
"data_permission_pkey" PRIMARY KEY ("id")`
- `db/init/oracle/schema.sql:811` — primary key on `id` only
- `shenyu-admin/src/main/resources/sql-script/h2/schema.sql:312-320` —
`PRIMARY KEY (id)`
Yet the application already treats `(user_id, data_id, data_type)` as the
unique key:
- `data-permission-sqlmap.xml:39-44` — `deleteByUniqueKey` deletes by
exactly these three columns
- `data-permission-sqlmap.xml:103` — `findOneByUniqueKey` looks up by
exactly these three columns
Because the database does not enforce it, duplicate grant rows can be
created:
- `DataPermissionServiceImpl#grantMissingPermissions` (around line 331)
performs a **read-then-write dedup**: `selectUserIds(dataId, dataType)` →
filter out users already present → `insertBatch(...)`. Two concurrent requests
(or a retry after a partial failure) both observe the user as absent and both
insert, producing duplicate rows.
- The same exposure exists for the other `insertSelective` / `insertBatch`
paths.
## Impact
- Duplicate rows for the same `(user_id, data_id, data_type)`.
- Deletes keyed on the "unique" key remove more rows than the caller
intended in a single call.
- The dedup is a check-then-act race with no database-level backstop, so
correctness depends entirely on request timing.
## Suggested fix
1. Add a unique index to the init scripts for **all** dialects, e.g. `UNIQUE
KEY uk_user_data_type (user_id, data_id, data_type)` (MySQL), a `CREATE UNIQUE
INDEX` (PostgreSQL / openGauss), and the Oracle equivalent.
2. Add a matching entry under `db/upgrade/`. Note that MySQL has no `CREATE
INDEX IF NOT EXISTS`, so the upgrade statement must be idempotent-safe
(guarded, or documented as run-once).
3. Keep the application-side read-then-write filter — it avoids relying on
constraint violations for the happy path — but treat the unique index as the
real guarantee.
4. The admin H2 profile (`application-h2.yml` → `sql-script/h2/schema.sql`)
uses an in-memory database that is re-initialized on every start, so
unconditional index creation is safe there and needs no special handling.
## Related
#6677 ("Systemic missing secondary indexes across the admin schema") was
closed, but this table's unique constraint does not appear to have been added.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
