definer-invoker-example.md
In this example, this table:
records(i INT, updated_date TIMESTAMP DEFAULT sysdate, updated_by VARCHAR(128) DEFAULT current_user())
Contains the following content:
=> SELECT * FROM records;
i | updated_date | updated_by
---+----------------------------+------------
1 | 2021-08-27 15:54:05.709044 | Bob
2 | 2021-08-27 15:54:07.051154 | Bob
3 | 2021-08-27 15:54:08.301704 | Bob
(3 rows)
Bob creates a procedure to update the table and uses the SECURITY DEFINER option and grants EXECUTE on the procedure to Alice. Alice can now use the procedure to update the table without any additional privileges:
=> GRANT EXECUTE ON PROCEDURE update_records(int,int) to Alice;
GRANT PRIVILEGE
=> \c - Alice
You are now connected as user "Alice".
=> CALL update_records(99,1);
update_records
---------------
0
(1 row)
Because calls to update_records()
effectively run the procedure as Bob, Bob is listed as the updater of the table rather than Alice:
=> SELECT * FROM records;
i | updated_date | updated_by
----+----------------------------+------------
99 | 2021-08-27 15:55:42.936404 | Bob
2 | 2021-08-27 15:54:07.051154 | Bob
3 | 2021-08-27 15:54:08.301704 | Bob
(3 rows)