pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction with security definer
4+ messages / 3 participants
[nested] [flat]
* function with security definer
@ 2003-03-24 09:54 Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tomasz Myrta @ 2003-03-24 09:54 UTC (permalink / raw)
To: pgsql-sql
Hi
I have a lot of users for my database, but I don't want to create each
of them as postgres user, so I tried to do this:
1. Create only postgres 3 users: admin,user,nobody
2. Login into database _only_ as nobody and execute only 1 function -
make_login.
3. This function checks user login and password
4. If everything is ok, function switches from nobody to either user or
admin.
Here is a body of function:
CREATE OR REPLACE FUNCTION make_login(varchar,varchar) returns integer AS'
DECLARE
login_ ALIAS for $1;
passwd_ ALIAS for $2;
access_level integer;
BEGIN
select into access_level access_level from users
where login=login_ and passwd=passwd_;
if not found then
return -1;
end if;
if access_level=1 then
set session authorization ''user'';
end if;
if access_level=2 then
set session authorization ''admin'';
end if;
return 1;
END;
' LANGUAGE 'plpgsql' SECURITY DEFINER;
Well, it doesn't work :-(
ERROR: permission denied
Why? Everything looks ok. User nobody has acl to execute this function.
Function is created by database owner. What else do I need?
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function with security definer
@ 2003-03-24 11:16 Tomasz Myrta <jasiek@klaster.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tomasz Myrta @ 2003-03-24 11:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> Tomasz Myrta <jasiek@klaster.net> writes:
> > [ Can't do SET SESSION AUTHORIZATION in a postgres-owned function ]
>
> That's because SET SESSION AUTHORIZATION looks to the original login
> userid, not the current effective userid, to decide whether you're
> allowed to do it. If it didn't work that way, a superuser couldn't
> switch to any other identity after becoming a nonprivileged user.
Does it mean, that SET SESSION AUTHORIZATION works the same for function
created with "security definer" as with "security invoker"? Are there any
other cases, when it doesn't work properly?
> I don't really see why you think this kluge is better than creating
> multiple database users, anyway ...
Well, it was just an idea. It is easier to keep all information about users
in one table "users" than synchronizing this table with pg_shadow via
usesysid and granting/revoking each user one by one (or adding them to
groups). It lets me also to give each user as little rights as needed - even
depending on which application they currently run (administration panel or
web report)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function with security definer
@ 2003-03-24 15:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2003-03-24 15:02 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
Tomasz Myrta <jasiek@klaster.net> writes:
> [ Can't do SET SESSION AUTHORIZATION in a postgres-owned function ]
That's because SET SESSION AUTHORIZATION looks to the original login
userid, not the current effective userid, to decide whether you're
allowed to do it. If it didn't work that way, a superuser couldn't
switch to any other identity after becoming a nonprivileged user.
I don't really see why you think this kluge is better than creating
multiple database users, anyway ...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: function with security definer
@ 2003-03-24 18:36 Antti Haapala <antti.haapala@iki.fi>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Antti Haapala @ 2003-03-24 18:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
On Mon, 24 Mar 2003, Tom Lane wrote:
> Tomasz Myrta <jasiek@klaster.net> writes:
>
> > [ Can't do SET SESSION AUTHORIZATION in a postgres-owned function ]
>
> That's because SET SESSION AUTHORIZATION looks to the original login
> userid, not the current effective userid, to decide whether you're
> allowed to do it. If it didn't work that way, a superuser couldn't
> switch to any other identity after becoming a nonprivileged user.
So what if it went like this:
- set session authorization on "logged as superuser" acts
as before
- as a bonus allow superuser owned SECURITY DEFINER functions
to set session authorization when called by unpriv. user.
- it could also be possible for superuser to drop privs
permanently, with SET PERMANENT SESSION AUTHORIZATION.
(which just sets login ids to the new value)
--
Antti Haapala
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-24 18:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-24 09:54 function with security definer Tomasz Myrta <jasiek@klaster.net>
2003-03-24 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-24 11:16 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-24 18:36 ` Antti Haapala <antti.haapala@iki.fi>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox