I really haven't provided enough details- my fault. What I want to
accomplish is a general-purpose timer facility for postgresql. Ideally,
arbitrary roles provide statements to run at certain intervals. The
benefit here is that the user connections can go away and only a single
timer connection is maintained (waiting on notifications to update).
Examples of where this could be useful:
1) simulated materialized views
2) daily tasks such as cache cleanup/refresh/updates
3) expensive tasks which run regularly
Arbitrary statements could be executed on a timed basis without needing
local access for crontab or persistent remote access.
Anyway, here is the table:
CREATE TABLE pgtimer._timer
(
id SERIAL PRIMARY KEY,
repeats INTEGER NOT NULL, --repeats X times as countdown
lastfired TIMESTAMP,
waitinterval INTERVAL, --OR
specialeventid INTEGER REFERENCES pgtimer.specialevent, --various
special events such as startup, autovacuum, or notifications
detail TEXT, --stores notification event name if applicable
statement TEXT NOT NULL,
asrole TEXT NOT NULL
);
A separate view with rules handles insert/update capabilities and
throws a notification so that the daemon is notified to refresh its
countdown to the next event. The actual statement execution is all I
have left to do. I could force users to define security definer
functions but then vacuuming capability is lost (autovacuum can't
handle everything).
If there is an architecture change I could make to rectify this, I am
all ears. Thanks!
-M
On Apr 20, 2006, at 5:03 PM, Tom Lane wrote:
"A.M." <agentm@xxxxxxxxxxxxxxxxxxxxx> writes:
On Thu, April 20, 2006 4:21 pm, Tom Lane wrote:
I think the correct way to do what you want is via a SECURITY DEFINER
function.
Perhaps I can't wrap my head around it- I have the SQL as a string in
a
table.
Well, the simplest thing would be
create function exec(text) returns void as $$
begin
execute $1;
end$$ language plpgsql strict security definer;
revoke execute on exec(text) from public;
grant execute on exec(text) to whoever-you-trust;
although personally I'd try to restrict what the function can be used
for a bit more than that. If the allowed commands are in a table, you
could perhaps pass the table's key to exec() and let it pull the string
from the table for itself.
What about commands that can't be run from within transactions?
There aren't that many of those. Do you really need this for them?
For that matter, do you really need this at all? Have you considered
granting role membership as an alternative solution path? The SQL
permissions mechanism is quite powerful as of 8.1, and if it won't
do what you want, maybe you have not thought hard enough.
regards, tom lane
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@xxxxxxxxxxxxxxxxxxxxx
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
