Thread: Any way to have CREATEUSER privs without having all privs?
I've got a user with CREATEUSER privs. I've not granted that user and DB specific privs but it can do what it will with non-public schemas... Is there a user that can do SET SESSION AUTHORIZATION but does not have privs otherwise? Basically I want a login user that can then set session auth... to any other user but otherwise has no privs. (Having createuser is acceptable.) I'm looking into a way to give connection pooled access to a web site (connections must have the same user/pw info to be pooled) but to then enforce DB-level security. I do not want the account that the web container uses to access the db to have any db-level privs. (I.e., rather than the Unix "root" account, something more like VMS (now Windows NT) user privs. VMS users had a "set priv" privilege which, of course, could indirectly give the holder of that priv any other priv. But only indirectly. It has some benefits.) Thanks, == Ezra Epstien
"ezra epstein" <ee_newsgroup_post@prajnait.com> writes:
> Basically I want a login user that can then set session auth... to any other
> user but otherwise has no privs.
You have not thought this through.
If user X can become any other user Y, then he can do anything that is
doable within the system. Pretending that he is not superuser is
pointless.
regards, tom lane
On Fri, Jan 02, 2004 at 07:18:45 -0800, ezra epstein <ee_newsgroup_post@prajnait.com> wrote: > I've got a user with CREATEUSER privs. I've not granted that user and DB > specific privs but it can do what it will with non-public schemas... Is > there a user that can do SET SESSION AUTHORIZATION but does not have privs > otherwise? > > Basically I want a login user that can then set session auth... to any other > user but otherwise has no privs. (Having createuser is acceptable.) I'm > looking into a way to give connection pooled access to a web site > (connections must have the same user/pw info to be pooled) but to then > enforce DB-level security. I do not want the account that the web container > uses to access the db to have any db-level privs. If the web server and the DB server are the same machine or the web server machine runs an identd service that you can trust, then you can do what you want using ident authentication. The ident map file doesn't have an "all" keyword, so you will need to update it as you add users. > (I.e., rather than the Unix "root" account, something more like VMS (now > Windows NT) user privs. VMS users had a "set priv" privilege which, of > course, could indirectly give the holder of that priv any other priv. But > only indirectly. It has some benefits.) VMS' set priv feature wasn't well designed (at least in early versions of VMS). You didn't need to reauthenticate to elevate your privileges, so you still had to be VERY careful when running other people's programs as they would have access to your elevated privileges.
On Sat, Dec 06, 2003 at 22:33:00 -0800, Ezra Epstein <sf-lists@prajnait.com> wrote: > Thank you Bruno for the informative reply. > > I'm not sure how ident solves this. I would like the session to run as the > actual user (via set session authorization) so that that user's actual privs > are enforced. But I want the connection to be shared: so it cannot be per > login (username/pw combo). I'm not up on ident enough to see the fit. Any > pointers would be most welcome. I was wrong about this being useful in your situation. SET SESSION AUTHORIZATION doesn't reauthenticate, it only allows you to switch to a new user if you originally were connected as a superuser. Ident authentication would only be useful if you could close and then reopen the connection. This could be useful if the overhead of doing this wasn't a concern.
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:6596.1073173257@sss.pgh.pa.us... > "ezra epstein" <ee_newsgroup_post@prajnait.com> writes: > > Basically I want a login user that can then set session auth... to any other > > user but otherwise has no privs. > > You have not thought this through. > > If user X can become any other user Y, then he can do anything that is > doable within the system. Pretending that he is not superuser is > pointless. > > regards, tom lane > I know, I know.... It's like I want something that just isn't possible. I want good DB-level security in the app without requiring the overhead of per-userid login: so connection pools can work. The app could be careful with super user... but it is probably better to just go the ordinary route of an app account with enough privs to do everything and then have the app/servlet container manage security. Thanks, == EE