User permission - Mailing list pgsql-general

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