yii2
yii2 copied to clipboard
yiisoft
Fixture load yields "permission denied" error when disabling triggers in PostgreSQL
What steps will reproduce the problem?
When we try ./yii fixture/load, Yii disables triggers before load and enables them after. However, in PostgreSQL, the database user must be superuser so he can disable system triggers like foreign key constraints. If the user is not a superuser, we get an error message like this:
ERROR: permission denied: "RI_ConstraintTrigger_16509" is a system trigger
What is the expected result?
./yii fixture/load should works with any database user, but currently fails when the user is not a superuser.
What do you get instead?
Inability to load fixtures with a non-super user. I think that needing a DB superuser only to load fixtures is too overkill.
Additional info
According to [1], the best workaround is marking the triggers as deferred at first and mark them as immediate at last, using SET CONSTRAINTS ALL DEFERRED/IMMEDIATE. I've done some tests and seems to work OK, but I would like to see some other comments about that. I can create a pull request if needed.
Interestingly, this change will render PR #11775 unnecessary.
[1] http://kopongo.com/2008/7/25/postgres-ri_constrainttrigger-error
| Q | A |
|---|---|
| Yii version | 2.0.10 |
| PHP version | 7.0.8-0ubuntu0.16.04.3 |
| Operating system | Ubuntu 16.04.1 LTS 64-bit |
Problem still exists. It doesn't allows to run tests with PostgreSQL without using PostgreSQL superuser (which doesn't sounds very secure).
Suggested solution was rejected, see #13106 (I understand why, however this solution works for tests purpose).
Is there anything we can do about this? (besides using PostgreSQL superuser)
I'm not into this part of PostgreSQL much so I'm not sure #13106 is the only way to achieve it... do you have alternative solutions?
There is no way to exactly "disable constraints checks, insert invalid data, enable constraints checks" without superuser rights. So solutions depends on the goal.
(1) If the goal is to "insert valid data without caring about order of inserts", then solution is to use SET CONSTRAINTS ALL DEFERRED. This allows to insert data in any order but there is still constraints checks at the end of transactions.
(2) ActiveFixture supports depends. So we can select right order for inserts and there is no need to deffer constraints checks, right? Maybe it's just better to allow disabling usage of checkIntegrity() in InitDbFixture?
(3) If the goal is to "insert invalid data just for test purposes", then solution is to (but it looks a bit ugly):
- Drop the constraints
- Load data, use data in tests
- Clean data, re-create constraints
I think main goal is to allow run tests without using postgres superuser (as it's core feature).
P.S. This not resolves the problem:
- Use
ALTER TABLE table DISABLE TRIGGER ALL, requires superuser (current) - Use
set session_replication_role to replica;requires superuser - Disable only user triggers using
ALTER TABLE table DISABLE TRIGGER USER, but it doesn't disabling foreign key checks (so you still can't insert invalid data)
