Thread: local security
Hi, I have a very simple security setup wish. I only want to allow local connections where each user can only log into postgres as himself, but there isnt a "ident sameuser" option for local connections. Is there any way around this? Can anyone explain to me why such an option doesnt exist for local connections? Thanks, David Kaplan
"David M. Kaplan" <dkaplan@genes.bio.puc.cl> writes:
> I have a very simple security setup wish. I only want to allow local
> connections where each user can only log into postgres as himself, but
> there isnt a "ident sameuser" option for local connections. Is there
> any way around this? Can anyone explain to me why such an option doesnt
> exist for local connections?
Because IDENT is a TCP protocol and only applies to connections made via
TCP. (Some platforms have ways to get similar info for Unix
connections, but AFAIK they're not standardized.)
If you set environment variable PGHOST to "localhost" then things will
work fairly transparently over TCP ...
regards, tom lane
David M. Kaplan writes: > I have a very simple security setup wish. I only want to allow local > connections where each user can only log into postgres as himself, but > there isnt a "ident sameuser" option for local connections. Is there > any way around this? Can anyone explain to me why such an option doesnt > exist for local connections? Because ident works based on TCP ports. There is a similar mechanism for Unix domain sockets implemented in some kernels, but it's not portable and therefore there hasn't been wide-spread enthusiasm for supporting it. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Hi, Thanks for all of the responses. Setting the pghost variable works, but I now realize that the situation is a bit more complex. If you do that, you can no longer do things like restores from backups without editing the configuration file because postgresql wont let you change users. This is of course not a great problem, but it is a bit annoying. What I really want is that normal users can only logon as themselves, but superusers can logon as anyone. Even better would be that postgres uses the standard unix security and that on connecting it asks for the appropriate unix password unless you are a superuser in which case it just connects. How hard would this be to implement and is it worth doing? I could try to implement it if there was a need. Thanks, David Tom Lane wrote: > "David M. Kaplan" <dkaplan@genes.bio.puc.cl> writes: > > I have a very simple security setup wish. I only want to allow local > > connections where each user can only log into postgres as himself, but > > there isnt a "ident sameuser" option for local connections. Is there > > any way around this? Can anyone explain to me why such an option doesnt > > exist for local connections? > > Because IDENT is a TCP protocol and only applies to connections made via > TCP. (Some platforms have ways to get similar info for Unix > connections, but AFAIK they're not standardized.) > > If you set environment variable PGHOST to "localhost" then things will > work fairly transparently over TCP ... > > regards, tom lane
"David M. Kaplan" <dkaplan@bio.puc.cl> writes:
> Even better would be that postgres uses the standard unix security
> and that on connecting it asks for the appropriate unix password unless you
> are a superuser in which case it just connects.
This would tie Postgres usernames to usernames of the surrounding
system, which is something that we've explicitly avoided doing.
There are many scenarios where you don't want to have to grant every
database user a shell account on the database host machine.
Another serious problem is how do you know what Unix userid is at the
other end of the connection? If it's a localhost TCP connection then
maybe you can trust IDENT protocol to find out, but AFAIK there's no
portable equivalent for Unix-socket connections.
Finally, I don't much care for the assumption that superuserness on the
Unix side should automatically translate to superuserness in Postgres
land. We've worked hard to ensure that routine Postgres administration
does not require system root privileges, and I don't think that the
access-control scheme should encourage people to break that
compartmentalization. If you're doing Postgres-related work as root,
you're too likely to slip up and give something root privs that
shouldn't have 'em.
regards, tom lane
"David M. Kaplan" <dkaplan@bio.puc.cl> writes:
> ... If you do that, you
> can no longer do things like restores from backups without editing the
> configuration file because postgresql wont let you change users. This is of
> course not a great problem, but it is a bit annoying. What I really want is
> that normal users can only logon as themselves, but superusers can logon as
> anyone.
BTW, this has already been discussed (look in the pghackers archives),
and I believe the consensus was that the most useful approach is to make
Postgres distinguish between "real" userid (what you logged in as) and
"effective" userid (what's used for object creation and permission
checks). For unprivileged users these would be equal and unchanging
throughout a session, but if your real ID is a Postgres superuser then
you would be allowed to SET the effective-userid variable to different
values. Then, for example, a pg_dump script would use "SET
effective_userid" instead of "\connect" commands to change user
identity, and a whole lot of the problems with executing pg_dump and
pg_dumpall scripts under secure authentication models would go away.
We need a notion of current effective userid anyway to allow rules and
functions to execute as though they are "setuid" programs.
I think Peter E. has laid some of the groundwork for this mechanism in
7.1, but work is still needed.
regards, tom lane
The scenario you mention here is basically what I was thinking. I was thinking instead of using a SET that one would simply use the normal connect, but the system would decide on whether or not to connect based first on your system id, next on the current \connect id. If neither of these work (i.e. if neither of these are superuser), the system would ask for your unix password to allow the change of \connect id. This is basically the "su" mechanism. I was never thinking of having unix superusers be postgres superusers, but rather that if you are unix logged on as postgres, you would be able to connect to psql as any user. Also, a user would only be allowed to connect if there was also a postgresql user with the same username (or there could be a system of aliases as used in the current ident security option). I will look into postgres 7.1 and see what is there. Perhaps I will get a moment to develop some of this. Thanks, David Tom Lane wrote: > "David M. Kaplan" <dkaplan@bio.puc.cl> writes: > > ... If you do that, you > > can no longer do things like restores from backups without editing the > > configuration file because postgresql wont let you change users. This is of > > course not a great problem, but it is a bit annoying. What I really want is > > that normal users can only logon as themselves, but superusers can logon as > > anyone. > > BTW, this has already been discussed (look in the pghackers archives), > and I believe the consensus was that the most useful approach is to make > Postgres distinguish between "real" userid (what you logged in as) and > "effective" userid (what's used for object creation and permission > checks). For unprivileged users these would be equal and unchanging > throughout a session, but if your real ID is a Postgres superuser then > you would be allowed to SET the effective-userid variable to different > values. Then, for example, a pg_dump script would use "SET > effective_userid" instead of "\connect" commands to change user > identity, and a whole lot of the problems with executing pg_dump and > pg_dumpall scripts under secure authentication models would go away. > > We need a notion of current effective userid anyway to allow rules and > functions to execute as though they are "setuid" programs. > > I think Peter E. has laid some of the groundwork for this mechanism in > 7.1, but work is still needed. > > regards, tom lane