User permission

[C G]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"C G" <cs******@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Peter Eisentraut]

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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend