Thread: User permission

Dear All,

I have a user_info table that has trigger which creates a user and switches
session
authorization to the new user, but it doesn't seem to work as I expect.

I created the table/functions as a super user.

Any ideas?

Thanks

Colin

mydb1=> select session_user;
session_user
--------------
admin
(1 row)
mydb1=>insert into user_info values ('user1','user1');
INSERT 18155 1

mydb1=> select session_user;
session_user
--------------
user1
(1 row)
That's what I wanted

mydb1=> select * from user_info ;
usename | password
---------+----------
user1   | ***
(1 row)
Shouldn't be able to do that!
mydb1=> set session authorization 'user1';
SET
mydb1=> select session_user;
session_user
--------------
user1
(1 row)mydb1=> select * from user_info ;
ERROR:  permission denied for relation user_info
Now it works.


Code I used to create the table.

DROP TABLE user_info;
create table user_info
(
    usename text,
    password text
);
DROP FUNCTION create_user() CASCADE;
CREATE FUNCTION create_user() RETURNS trigger AS'
--Creates a new user in the database and puts them into group2
    DECLARE
        set_qry1 text;
        set_qry2 text;
    BEGIN
        set_qry1 := ''CREATE USER '' || NEW.usename || '' WITH PASSWORD '''''' ||
NEW.password ||'''''' NOCREATEDB NOCREATEUSER'';
        EXECUTE set_qry1;
         NEW.password = ''***'';
        set_qry2 := ''SET SESSION AUTHORIZATION ''|| NEW.usename;
        EXECUTE set_qry2;

        RETURN NEW;
    END;
' LANGUAGE 'plpgsql' SECURITY DEFINER;

CREATE TRIGGER create_user BEFORE INSERT
    ON user_info FOR EACH ROW EXECUTE
        PROCEDURE create_user();

_________________________________________________________________
Tired of 56k? Get a FREE BT Broadband connection
http://www.msn.co.uk/specials/btbroadband

"C G" <csgcsg39@hotmail.com> writes:
> I have a user_info table that has trigger which creates a user and
> switches session authorization to the new user, but it doesn't seem to
> work as I expect.

> mydb1=> select * from user_info ;
> usename | password
> ---------+----------
> user1   | ***
> (1 row)
> Shouldn't be able to do that!

I think what is happening is that when control exits from the SECURITY
DEFINER function, the value of current_user is reset to what it was when
the function was entered.  So even though session_user says user1,
you are still effectively admin here.

I'm not sure there is any clean solution that would make this do what
you expect, partly because it's not real clear what you expect (consider
nested SECURITY DEFINER functions, error recovery, etc).  I'd be
inclined to put a band-aid on it by forbidding SET SESSION AUTHORIZATION
inside functions.

Can anyone propose a defensible and implementable behavior that would
allow CG to do what he wants?

            regards, tom lane

Tom Lane wrote:
> I'm not sure there is any clean solution that would make this do what
> you expect, partly because it's not real clear what you expect
> (consider nested SECURITY DEFINER functions, error recovery, etc).
> I'd be inclined to put a band-aid on it by forbidding SET SESSION
> AUTHORIZATION inside functions.

Well, we could also try to just not switch back to the old value when
exiting the function.  I'll have a look to see whether that is doable.