Re: sudo-like behavior - Mailing list pgsql-general

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@themactionfaction.com> 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@themactionfaction.com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬