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