ERROR: permission denied for table transaction_log when deleting from table.

[nextstopsun]

ERROR: permission denied for table transaction_log when deleting from table.

[nextstopsun]

I've found out that this error only fires up when I try to delete from a table, which is referenced by another one by a FK constraint.
This is a constraint on a auth.lnk_users_user_roles table:

CONSTRAINT fk_users FOREIGN KEY (user_guid) REFERENCES auth.ref_users(guid) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED

Both auth.ref_users and auth.lnk_users_user_roles are being audited by pgmemento.
The error fires up when I'm trying to run

DELETE FROM auth.ref_users WHERE guid = '....'

Notice, that error is fired when inserting transaction for a referencing table.
Shouldn't only one record in transaction_log be inserted in this case?

[FxKu]

Yes, only one row will be inserted, but for a cascading delete another insert will be triggered and omitted (DO NOTHING).

Strange error, indeed. Not sure if it has to do with the deferrable setting. On the other Hand a 42501 error always reads to me as a lack of permissions. Is your superuser also the owner of pgmemento's tables and functions?

[nextstopsun]

I've double checked that and also ran this

GRANT ALL PRIVILEGES ON TABLE pgmemento.transaction_log TO postgres;
ALTER TABLE pgmemento.transaction_log OWNER TO postgres;

Still have this [42501] ERROR: permission denied for table transaction_log thing.

Some side questions:
Why aren't pgmemento functions marked as SECURITY DEFINER?
Why do triggers return NULL? I've got some other triggers on logged table, does returned NULL dismiss them?

[nextstopsun]

Ok, I have a reproducible example here:

DROP TABLE IF EXISTS parent CASCADE;
DROP TABLE IF EXISTS child;

DROP ROLE IF EXISTS tbl_owners;
CREATE ROLE tbl_owners;
GRANT tbl_owners TO current_user;

CREATE TABLE parent (
  id SERIAL PRIMARY KEY
);

ALTER TABLE parent OWNER TO tbl_owners;

CREATE TABLE child (
  id SERIAL PRIMARY KEY,
  parent_id INTEGER,
  CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
);

ALTER TABLE child OWNER TO tbl_owners; --THIS LINE CAUSES TROUBLE!!

SELECT pgmemento.create_table_audit('parent', 'public', TRUE);
SELECT pgmemento.create_table_audit('child', 'public', TRUE);

INSERT INTO parent (id) VALUES (1);
INSERT INTO child (parent_id) VALUES (1);

DELETE FROM parent; --BOOM!

If I remove ALTER TABLE child OWNER TO tbl_owners; then everything works OK.

@FxKu can you please point to a solution?

[FxKu]

Thanks for creating this example. That will help to narrow down the problem. Looks like the missing security definer is indeed the reason for this, but not sure.

I can fix it next week as I'm currently in vacation. Or you come up with a PR ;)

[nextstopsun]

Not sure if adding a security definer is a good move - this can be a security issue then, allowing users to modify audit tables.
I've found a source of this bug: actions of FK are executed by table owner, not current_user. So the DELETE CASCADE fired a DELETE command by tbl_owners user.
In my case table owner didn't have permissions to select and modify audit tables - so this resulted in permission error.

[FxKu]

Good to know. Thanks for investigating. Didn't find the time yet.