Обсуждение: sudo-like behavior

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

"A.M." <agentm@themactionfaction.com> writes:
> It seems I am stuck so please allow me to propose an extension:
> SET SESSION AUTHORIZATION user [WITH PASSWORD 'password];

This idea is extremely unlikely to be accepted, as the password would be
at risk of exposure in places like the pg_stat_activity view.

I think the correct way to do what you want is via a SECURITY DEFINER
function.

            regards, tom lane

On Thu, Apr 20, 2006 at 04:06:19PM -0400, A.M. wrote:

> The problem is that I wish to run arbitrary SQL as an unprivileged user

Would wrapping the SQL in a stored procedure with "security
definer" help any ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Thu, April 20, 2006 4:21 pm, Tom Lane wrote:
> "A.M." <agentm@themactionfaction.com> writes:
>
>> It seems I am stuck so please allow me to propose an extension:
>> SET SESSION AUTHORIZATION user [WITH PASSWORD 'password];
>>
>
> This idea is extremely unlikely to be accepted, as the password would be
> at risk of exposure in places like the pg_stat_activity view.
>
> 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. I interpret that you propose that I accept only function names and
allow users to create security definer functions which I then call as the
superuser (carefully checking for the security definer flag). What about
commands that can't be run from within transactions?

I guess there is no way to stream arbitrary SQL in a permissions sandbox
if the original login user isn't the one I want. The security definer
method is a good enough workaround. Thanks.

-M

"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

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
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬

Agent M <agentm@themactionfaction.com> writes:
> I really haven't provided enough details- my fault. What I want to
> accomplish is a general-purpose timer facility for postgresql.

I'm not really sure why you think it'd be a good idea for such a thing
to operate as an unprivileged user that gets around its lack of
privilege by storing copies of everyone else's passwords.  I can think
of several reasonable ways to design the privilege handling for a
cron-like facility, but giving it cleartext copies of everyone's
passwords is not one of them.

            regards, tom lane

Sorry, but you misunderstand- nowhere am I interested in the role's
password. My previous suggestion was to add a password to set session
authorization itself so that if the authorization were to be reset, it
would need to be done with that password; the password itself could be
machine-generated. It it would merely allow a secure sandbox to be
established between:

SET SESSION AUTHORIZATION somerole WITH PASSWORD 'abc';
--arbitrary SQL run as somerole
RESET SESSION AUTHORIZATION; --fails- requires password
RESET SESSION AUTHORIZATION WITH PASSWORD 'pass'; --fails
RESET SESSION AUTHORIZATION WITH PASSWORD 'abc'; --succeeds- we are
done with this role

The password ensures that the session authorization initiator is the
only one that can terminate it as well.

-M

On Apr 20, 2006, at 10:44 PM, Tom Lane wrote:

> Agent M <agentm@themactionfaction.com> writes:
>> I really haven't provided enough details- my fault. What I want to
>> accomplish is a general-purpose timer facility for postgresql.
>
> I'm not really sure why you think it'd be a good idea for such a thing
> to operate as an unprivileged user that gets around its lack of
> privilege by storing copies of everyone else's passwords.  I can think
> of several reasonable ways to design the privilege handling for a
> cron-like facility, but giving it cleartext copies of everyone's
> passwords is not one of them.
>
>             regards, tom lane
>
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@themactionfaction.com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬

Agent M wrote:
> Sorry, but you misunderstand- nowhere am I interested in the role's
> password. My previous suggestion was to add a password to set session
> authorization itself so that if the authorization were to be reset, it
> would need to be done with that password; the password itself could be
> machine-generated. It it would merely allow a secure sandbox to be
> established between:
>
> SET SESSION AUTHORIZATION somerole WITH PASSWORD 'abc';
> --arbitrary SQL run as somerole
> RESET SESSION AUTHORIZATION; --fails- requires password
> RESET SESSION AUTHORIZATION WITH PASSWORD 'pass'; --fails
> RESET SESSION AUTHORIZATION WITH PASSWORD 'abc'; --succeeds- we are done
> with this role

Why don't you just use "SET SESSION AUTHORIZATION somerole", and then scan
the to-be-executel sql scripts for any occurence of "reset session authorization",
and ignore the script it matches.

Of course you'd need to be a bit carefull to catch all syntactially valid
variations (like ReSeT SeSsIoN AuToRiZaTiOn), but that should be doable.

If you design your "matched" carefully, the only way to defeat that protection
would be to wrap the "reset session authorization" command in a function, which
I believe is not possible.

greetings, Florian Pflug

"Florian G. Pflug" <fgp@phlo.org> writes:
> Why don't you just use "SET SESSION AUTHORIZATION somerole", and then scan
> the to-be-executel sql scripts for any occurence of "reset session authorization",
> and ignore the script it matches.

What would probably be better is a way to do SET SESSION AUTHORIZATION
and then abandon the underlying superuser privilege, thereby absolutely
guaranteeing that the session can't do anything the selected userid
shouldn't be able to do.  You'd have to start a new session for each
cronjob, but that would be a Really Good Idea anyway, given the lack of
any way to fully restore a session to default state.

            regards, tom lane

Tom Lane wrote:
> "Florian G. Pflug" <fgp@phlo.org> writes:
>
>>Why don't you just use "SET SESSION AUTHORIZATION somerole", and then scan
>>the to-be-executel sql scripts for any occurence of "reset session authorization",
>>and ignore the script it matches.
>
> What would probably be better is a way to do SET SESSION AUTHORIZATION
> and then abandon the underlying superuser privilege, thereby absolutely
> guaranteeing that the session can't do anything the selected userid
> shouldn't be able to do.  You'd have to start a new session for each
> cronjob, but that would be a Really Good Idea anyway, given the lack of
> any way to fully restore a session to default state.

My "solution" (or hack ;-) ) was meant to work with current versions of postgres..
Of course, a command like "set session authorization <user> final" or such would be
a better way - maybe something for 8.2? ;-))

mfg, Florian Pflug

On Apr 22, 2006, at 1:13 PM, Florian G. Pflug wrote:
>
> Why don't you just use "SET SESSION AUTHORIZATION somerole", and then
> scan
> the to-be-executel sql scripts for any occurence of "reset session
> authorization",
> and ignore the script it matches.
>
> Of course you'd need to be a bit carefull to catch all syntactially
> valid
> variations (like ReSeT SeSsIoN AuToRiZaTiOn), but that should be
> doable.
>
> If you design your "matched" carefully, the only way to defeat that
> protection
> would be to wrap the "reset session authorization" command in a
> function, which
> I believe is not possible.

Unfortunately, it is possible:
agentm=# CREATE OR REPLACE FUNCTION testacl() RETURNS void AS $$ RESET
SESSION AUTHORIZATION; $$ LANGUAGE SQL;
CREATE FUNCTION
agentm=# select current_user;
  current_user
--------------
  agentm
(1 row)
agentm=# set session authorization test;
SET
agentm=> select current_user;
  current_user
--------------
  test
(1 row)
agentm=> select testacl();
  testacl
---------

(1 row)
agentm=# select current_user;
  current_user
--------------
  agentm
(1 row)

So, currently, there is a security limitation in postgresql which
effectively prohibits switching roles midstream unless you can control
the statements of that role, i.e. there is no sandbox feature
available. (Such a feature would also be great for pooled connections,
but that has already been discussed as well.)

-M

¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@themactionfaction.com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬