From: Tomasz Myrta <jasiek@klaster.net>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: function with security definer
Date: Mon, 24 Mar 2003 10:54:54 +0100
Message-ID: <3E7ED5EE.9090104@klaster.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net
Subject: Re: function with security definer
In-Reply-To: <3E7ED5EE.9090104@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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