I got the database "bikini-atoll" created using this PostgreSQL code:
CREATE TABLE TESTA (
name character varying NOT NULL,
age integer NOT NULL,
PRIMARY KEY(name)
);
CREATE DOMAIN RANK AS character varying
CHECK (
VALUE = 'bigmac' OR
VALUE = 'smallmac'
);
CREATE TABLE TESTB (
name character varying NOT NULL,
rank character varying NOT NULL,
PRIMARY KEY(name)
);
CREATE OR REPLACE VIEW TESTAB AS
SELECT * FROM (TESTA
JOIN
TESTB
ON TESTA.name = TESTB.name);
CREATE OR REPLACE RULE TESTAB_INSERT
AS ON INSERT TO TESTAB
DO INSTEAD (
INSERT INTO TESTA (name,age) VALUES (NEW.name,NEW.age);
INSERT INTO TESTB (name,rank) VALUES (NEW.name,NEW.rank);
)
CREATE OR REPLACE FUNCTION testRankToAge() RETURNS TRIGGER AS $$
DECLARE
ageRes integer;
BEGIN
SELECT AGE INTO ageRes
FROM TESTA
WHERE name = NEW.name;
IF (ageRes < 42) AND (NEW.rank = 'bigmac')
THEN
RAISE EXCEPTION 'YOU CANNOT BE BIGMAC AT THAT AGE';
END IF;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER rankToAgeTrigger
AFTER INSERT
ON TESTB
FOR EACH ROW
EXECUTE PROCEDURE testRankToAge();
DROP ROLE IF EXISTS testUser;
CREATE ROLE testUser WITH PASSWORD '123456' LOGIN;
GRANT INSERT ON TESTAB TO testUser;
Now I want my (Common-LISP) program to do a simple insert:
(clsql:file-enable-sql-reader-syntax)
(clsql-sys:with-database (db (list "localhost" "bikini-atoll" "testuser" "123456") :database-type :postgresql)
(clsql-sys:insert-records :into [TESTAB]
:attributes '([NAME] [AGE] [RANK])
:values '("sir" 44 "bigmac")
:database db))
which basically is an:
INSERT INTO TESTAB (NAME,AGE,RANK) VALUES ('sir',44,'bigmac');
as user testuser (I doubt that this is a program language specific problem, and more some sort of bad database/trigger design on my part).
But the (unexpected) result is:
Error 42501 / FEHLER: no permission for relation testa
CONTEXT: SQL-COMMAND „SELECT AGE FROM TESTA
WHERE name = NEW.name“
PL/pgSQL-Funktion testranktoage() LINE 5 AT SQL-Command
has occurred.
which would suggest that testuser would also need SELECT
permissions on any table a trigger might do stuff on, which seems a bit odd as I thought that one of the main reasons for views is to limit/filter the access of a user to tables, which would the opposite of granting any SELECT
needed by a consistency trigger.
How to I fix/prevent this dawning permission havoc?
which would suggest that testuser would also need SELECT permissions on any table a trigger might do stuff on
That's exactly correct. Except, there are ways around it. Like a SECURITY DEFINER
trigger function.
Here is a closely related answer with detailed instructions:
Is there a way to disable updates/deletes but still allow triggers to perform them?