User permission

["C G" <csgcsg39@hotmail.com>]

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