469,106 Members | 2,262 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

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

Nov 23 '05 #1
2 1847
"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

Nov 23 '05 #2
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

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Lou | last post: by
10 posts views Thread by Anthony Best | last post: by
5 posts views Thread by Segfahlt | last post: by
3 posts views Thread by Asaf | last post: by
3 posts views Thread by shapper | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.