Message-ID: <3E7ED5EE.9090104@klaster.net>
Date: Mon, 24 Mar 2003 10:54:54 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: function with security definer
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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