Any way to have CREATEUSER privs without having all privs?

[ezra epstein]

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

[Tom Lane]

"ezra epstein" <ee************ ***@prajnait.co m> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bruno Wolff III]

On Fri, Jan 02, 2004 at 07:18:45 -0800,
ezra epstein <ee************ ***@prajnait.co m> 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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Ezra Epstein]

"Tom Lane" <tg*@sss.pgh.pa .us> wrote in message
news:65******** *****@sss.pgh.p a.us...

"ezra epstein" <ee************ ***@prajnait.co m> 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

[Bruno Wolff III]

On Sat, Dec 06, 2003 at 22:33:00 -0800,
Ezra Epstein <sf******@prajn ait.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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly