Hello,
I have written a crontab-like daemon which accepts jobs from users through
a table and executes SQL statements after certain events or intervals.
This daemon maintains a persistent connection to the database as a
superuser.
The problem is that I wish to run arbitrary SQL as an unprivileged user
but SET SESSION AUTHORIZATION is easily reversed via RESET SESSION
AUTHORIZATION. Since I don't have the role's password, I cannot connect as
him through a secondary connection.
It seems I am stuck so please allow me to propose an extension:
SET SESSION AUTHORIZATION user [WITH PASSWORD 'password];
If a password is specified, then any call to RESET SESSION AUTHORIZATION
would also need to include the WITH PASSWORD clause (and the correct
password) to be successful. This would allow for blocks of foreign code to
be executed as an arbitrary user. I am not sure this would work for SET
ROLE because of role inheritance.
Does anyone have a better idea?
Thanks,
-M