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