Thread: Using both ident and password in pg_hba.conf
Here's my situation. I have a mix of users. Some are running PHP sites and some are not. PHP runs as the web server owner, "nobody." Everyone else runs as their own user. Since the PHP sites run as nobody I want to require password but accept ident (from the server I control) for the rest. There does not appear to be a way to specif that. Here was one attempt: host all nobody 192.168.151.75/32 password host all all 192.168.151.75/32 ident But that doesn't work. The actual user according to ident is nobody but the request is for a specific user. As a result it isn't recognized by the first line so it tries ident anyway and fails. Is there any way to accomplish what I want? Any help appreciated. Cheers. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net
On 05/09/2016 12:44 PM, D'Arcy J.M. Cain wrote: > Here's my situation. I have a mix of users. Some are running PHP > sites and some are not. PHP runs as the web server owner, "nobody." > Everyone else runs as their own user. > > Since the PHP sites run as nobody I want to require password but accept > ident (from the server I control) for the rest. There does not appear > to be a way to specif that. Here was one attempt: > > host all nobody 192.168.151.75/32 password > host all all 192.168.151.75/32 ident > > But that doesn't work. The actual user according to ident is nobody > but the request is for a specific user. As a result it isn't > recognized by the first line so it tries ident anyway and fails. > > Is there any way to accomplish what I want? Any help appreciated. So define PHP runs as 'nobody'? Is that the script's user permissions? Or is that the database user the script is connecting as? Is 'nobody' defined as a database user? > > Cheers. > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, 9 May 2016 13:02:53 -0700 Adrian Klaver <adrian.klaver@aklaver.com> wrote: > So define PHP runs as 'nobody'? Because of the way PHP and Apache works PHP script have to run as the Apache user which, in my case anyway, is "nobody" so every PHP script runs as nobody. Meanwhile non-PHP scripts run as the user who owns the site. > Is that the script's user permissions? Sometimes. The user has the choice to have everything owned by nobody (which requires that they contact us for changes) or else as themself but with world readable permissions on the files so that nobody can serve them. > Or is that the database user the script is connecting as? Yes. > Is 'nobody' defined as a database user? Yes but each user has their own database with their own user and password. When they run PHP scripts they connect as nobody but they attempt to login as themself. Basically I think that pg_hba.conf is missing a feature. We can specify the database, the user and the address but we can't specify the authenticated user. When it sees this; provided user name (x) and authenticated user name (nobody) do not match I would like it to connect with user x but drop to password authentication. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net
On 5/9/2016 1:18 PM, D'Arcy J.M. Cain wrote:
Basically I think that pg_hba.conf is missing a feature. We can specify the database, the user and the address but we can't specify the authenticated user. When it sees this; provided user name (x) and authenticated user name (nobody) do not match I would like it to connect with user x but drop to password authentication.
'ident' is only secure over local 'domain' sockets, not over tcp/ip.
that said, you can use an ident user map to do what you want, this would say '"nobody" can log on as A, B, or C'
-- john r pierce, recycling bits in santa cruz
On 05/09/2016 01:18 PM, D'Arcy J.M. Cain wrote: > On Mon, 9 May 2016 13:02:53 -0700 > Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> So define PHP runs as 'nobody'? > > Because of the way PHP and Apache works PHP script have to run as the > Apache user which, in my case anyway, is "nobody" so every PHP script > runs as nobody. Meanwhile non-PHP scripts run as the user who owns the > site. > >> Is that the script's user permissions? > > Sometimes. The user has the choice to have everything owned by nobody > (which requires that they contact us for changes) or else as themself > but with world readable permissions on the files so that nobody can > serve them. > >> Or is that the database user the script is connecting as? > > Yes. > >> Is 'nobody' defined as a database user? > > Yes but each user has their own database with their own user and > password. When they run PHP scripts they connect as nobody but they > attempt to login as themself. The above does not make sense to me. Maybe I am not understanding if you mean connect and login as the same thing or not? I could see connecting as 'nobody' and then doing SET ROLE as user. Or connect as 'nobody' for the PHP script and have a separate connection as the database user. Otherwise you are going to have to explain more about what you are doing. > > Basically I think that pg_hba.conf is missing a feature. We can > specify the database, the user and the address but we can't specify the > authenticated user. When it sees this; > > provided user name (x) and authenticated user name (nobody) do not match > > I would like it to connect with user x but drop to password > authentication. Again this seems to assume a given connection can have two user names at the same time. As John pointed out there is mapping but it still resolves to only one name for the actual connection. > -- Adrian Klaver adrian.klaver@aklaver.com
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> Here's my situation. I have a mix of users. Some are running PHP
> sites and some are not. PHP runs as the web server owner, "nobody."
> Everyone else runs as their own user.
> Since the PHP sites run as nobody I want to require password but accept
> ident (from the server I control) for the rest. There does not appear
> to be a way to specif that. Here was one attempt:
> host all nobody 192.168.151.75/32 password
> host all all 192.168.151.75/32 ident
> But that doesn't work. The actual user according to ident is nobody
> but the request is for a specific user. As a result it isn't
> recognized by the first line so it tries ident anyway and fails.
> Is there any way to accomplish what I want? Any help appreciated.
If the same user id + database combinations might be valid in both cases
(from both PHP and manual connections) I think your only other option
for distinguishing which auth method to use is to make them come in on
different addresses. Can you set up a secondary IP interface that only
the PHP server uses, for example?
There's no provision for saying "try this auth method, but if it fails,
try subsequent hba lines". It might be interesting to have that,
particularly for methods like ident that don't involve any client
interaction. (Otherwise, you're assuming that the client can cope
with multiple challenges, which seems like a large assumption.)
I don't have much of a feeling for how hard it would be to do in the
server.
regards, tom lane
On Mon, May 09, 2016 at 13:39:48 -0700, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > >The above does not make sense to me. Maybe I am not understanding if >you mean connect and login as the same thing or not? I could see >connecting as 'nobody' and then doing SET ROLE as user. Or connect as >'nobody' for the PHP script and have a separate connection as the >database user. Otherwise you are going to have to explain more about >what you are doing. The mapping is between system and postgres users. So that the system user nobody is allowed to login as any of the postgres users a, b or c.
On Mon, 09 May 2016 17:12:22 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > If the same user id + database combinations might be valid in both > cases (from both PHP and manual connections) I think your only other > option for distinguishing which auth method to use is to make them > come in on different addresses. Can you set up a secondary IP > interface that only the PHP server uses, for example? I did think of that but how do I define that in pg_hba? The host field only specifies the remote IP, not the local one. > There's no provision for saying "try this auth method, but if it > fails, try subsequent hba lines". It might be interesting to have > that, particularly for methods like ident that don't involve any > client interaction. (Otherwise, you're assuming that the client can > cope with multiple challenges, which seems like a large assumption.) > I don't have much of a feeling for how hard it would be to do in the > server. I had an idea that that wouldn't be so easy else we would have had it by now. However, I am not sure that that is what is needed. I was thinking of something like this: host all joe@nobody 192.168.151.75/32 password host all all 192.168.151.75/32 ident The "all@nobody" field is meant to specify that the remote user is nobody but that they are connecting as user joe. You would be able to use "all" as well. You don't even need to do an ident check unless the auth method is "trust" which would be silly anyway. In fact "password" is the only method that even makes any sense at all. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net
On Mon, May 9, 2016 at 5:42 PM, D'Arcy J.M. Cain <darcy@druid.net> wrote:
On Mon, 09 May 2016 17:12:22 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> If the same user id + database combinations might be valid in both
> cases (from both PHP and manual connections) I think your only other
> option for distinguishing which auth method to use is to make them
> come in on different addresses. Can you set up a secondary IP
> interface that only the PHP server uses, for example?
I did think of that but how do I define that in pg_hba? The host field
only specifies the remote IP, not the local one.
> There's no provision for saying "try this auth method, but if it
> fails, try subsequent hba lines". It might be interesting to have
> that, particularly for methods like ident that don't involve any
> client interaction. (Otherwise, you're assuming that the client can
> cope with multiple challenges, which seems like a large assumption.)
> I don't have much of a feeling for how hard it would be to do in the
> server.
I had an idea that that wouldn't be so easy else we would have had it
by now. However, I am not sure that that is what is needed. I was
thinking of something like this:
host all joe@nobody 192.168.151.75/32 password
host all all 192.168.151.75/32 ident
The "all@nobody" field is meant to specify that the remote user is
nobody but that they are connecting as user joe. You would be able to
use "all" as well. You don't even need to do an ident check unless the
auth method is "trust" which would be silly anyway. In fact "password"
is the only method that even makes any sense at all.
So, at a high-level, you want:
- Users deploying php scripts in apache to require a password ( btw -- use md5, not password)
- Users running php scripts from their shell accounts to connect with no password to the database
Is that correct?
Why not just require that everyone use an (again: md5) to connect? It would be significantly more secure. Is their a requirement that shell account users be able to connect without providing a password?
(NB: http://www.postgresql.org/docs/9.4/static/auth-methods.html#AUTH-PASSWORD password will send the password in cleartext, md5 will tell libpq to hash the password for you. No client-level change).
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 5/9/2016 2:42 PM, D'Arcy J.M. Cain wrote:
I had an idea that that wouldn't be so easy else we would have had it by now. However, I am not sure that that is what is needed. I was thinking of something like this: host all joe@nobody 192.168.151.75/32 password host all all 192.168.151.75/32 ident The "all@nobody" field is meant to specify that the remote user is nobody but that they are connecting as user joe. You would be able to use "all" as well. You don't even need to do an ident check unless the auth method is "trust" which would be silly anyway. In fact "password" is the only method that even makes any sense at all.
over a tcp socket, there's no way of knowing *WHAT* the system user is short of querying the unreliable service 'authd' (113/tcp) and hoping that it A) exists and B) returns something meaningful. authd/ident services can return virtually anything they want to.
when pg_hba.conf is searched, all thats known is the socket type (host or local), the database name, the requested(!) username, and if its 'host', the source IP address. this is used to select the desired authentication method for that combination.
-- john r pierce, recycling bits in santa cruz
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> On Mon, 09 May 2016 17:12:22 -0400
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If the same user id + database combinations might be valid in both
>> cases (from both PHP and manual connections) I think your only other
>> option for distinguishing which auth method to use is to make them
>> come in on different addresses. Can you set up a secondary IP
>> interface that only the PHP server uses, for example?
> I did think of that but how do I define that in pg_hba? The host field
> only specifies the remote IP, not the local one.
Right, but you'd be using it essentially as a loopback interface.
Say you set it up as 192.168.0.42 --- you'd tell PHP to connect to
Postgres on 192.168.0.42, and Postgres would also see the PHP connections
as coming in from 192.168.0.42.
I think on most modern OSes you can set up this sort of thing entirely in
software, not even needing a spare NIC card. I haven't done it that way
though.
> I had an idea that that wouldn't be so easy else we would have had it
> by now. However, I am not sure that that is what is needed. I was
> thinking of something like this:
> host all joe@nobody 192.168.151.75/32 password
> host all all 192.168.151.75/32 ident
> The "all@nobody" field is meant to specify that the remote user is
> nobody but that they are connecting as user joe.
As John noted, we don't have any idea what the "remote username" is
at the time we're scanning pg_hba.conf.
regards, tom lane
On Mon, 9 May 2016 17:50:52 -0400 Scott Mead <scottm@openscg.com> wrote: > > was thinking of something like this: > > > > host all joe@nobody 192.168.151.75/32 password > > host all all 192.168.151.75/32 ident > > > > The "all@nobody" field is meant to specify that the remote user is > > nobody but that they are connecting as user joe. You would be able > > to use "all" as well. You don't even need to do an ident check > > unless the auth method is "trust" which would be silly anyway. In > > fact "password" is the only method that even makes any sense at all. > > So, at a high-level, you want: > > - Users deploying php scripts in apache to require a password ( btw > -- use md5, not password) I was using "password" in the generic sense. > - Users running php scripts from their shell accounts to connect with > no password to the database > > Is that correct? Absolutely not. I am allowing ident authentication for users because I trust the client machine but require password (md5, whatever) when they want to connect to their database but I can't confirm who they are. > Why not just require that everyone use an (again: *md5*) to > connect? It would be significantly more secure. Is their a > requirement that shell account users be able to connect without > providing a password? They aren't actually shell account users. I have a shell machine too but this is from the web server. If I require passwords then they have to store them in their scripts. The scripts can be secured from other users but not admins and since we don't otherwise know their raw passwords I wouldn't like to expose them, even to us. Of course PHP scripts have to run as nobody so I have no choice other than to have them store passwords in various config.php files but PHP users are used to that. I would like to fix that but that's a war for another day. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net
On Mon, 9 May 2016 14:56:14 -0700 John R Pierce <pierce@hogranch.com> wrote: > over a tcp socket, there's no way of knowing *WHAT* the system user > is short of querying the unreliable service 'authd' (113/tcp) and > hoping that it A) exists and B) returns something meaningful. > authd/ident services can return virtually anything they want to. I run both the client web server and the database server. Outside machines require passwords. > when pg_hba.conf is searched, all thats known is the socket type > (host or local), the database name, the requested(!) username, and if > its 'host', the source IP address. this is used to select the > desired authentication method for that combination. Yes, it is missing that one piece I suggested - the ability to select based on the authenticated name. That's what I am trying to work around. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net
On Mon, 09 May 2016 18:15:16 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > > I did think of that but how do I define that in pg_hba? The host > > field only specifies the remote IP, not the local one. > > Right, but you'd be using it essentially as a loopback interface. > Say you set it up as 192.168.0.42 --- you'd tell PHP to connect to > Postgres on 192.168.0.42, and Postgres would also see the PHP > connections as coming in from 192.168.0.42. Can you expand on this? I can't seem to get my head around it. How does the client make it look like it is coming from this ersatz loopback IP? In fact, I don't even need to add this to pg_hba since anything outside of my trusted IPs requires a password I did consider creating another private network (I already have one for internal communications) so the web server would alias 192.168.100.75 to the interface that 192.168.151.75 is on and the database would do the same for it's IP. Now I can trigger on the host 192.168.100.75. In fact, I don't even need to add this to pg_hba since anything outside of my trusted IPs already requires a password I was hoping for a way that did not involve changing every PHP user's web site but I guess there is no way around it. > I think on most modern OSes you can set up this sort of thing > entirely in software, not even needing a spare NIC card. I haven't > done it that way though. I do things like that all the time. > > The "all@nobody" field is meant to specify that the remote user is > > nobody but that they are connecting as user joe. > > As John noted, we don't have any idea what the "remote username" is > at the time we're scanning pg_hba.conf. So how do you do ident then? -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net
On Tue, 10 May 2016 09:50:10 -0400 "D'Arcy J.M. Cain" <darcy@druid.net> wrote: > Can you expand on this? I can't seem to get my head around it. How > does the client make it look like it is coming from this ersatz > loopback IP? In fact, I don't even need to add this to pg_hba since > anything outside of my trusted IPs requires a password Ignore that last sentence. It was meant for the following paragraph. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net
On Tue, 10 May 2016 09:50:10 -0400, "D'Arcy J.M. Cain" <darcy@druid.net> wrote: >On Mon, 09 May 2016 18:15:16 -0400 >Tom Lane <tgl@sss.pgh.pa.us> wrote: >> > I did think of that but how do I define that in pg_hba? The host >> > field only specifies the remote IP, not the local one. >> >> Right, but you'd be using it essentially as a loopback interface. >> Say you set it up as 192.168.0.42 --- you'd tell PHP to connect to >> Postgres on 192.168.0.42, and Postgres would also see the PHP >> connections as coming in from 192.168.0.42. > >Can you expand on this? I can't seem to get my head around it. How >does the client make it look like it is coming from this ersatz >loopback IP? In fact, I don't even need to add this to pg_hba since >anything outside of my trusted IPs requires a password On Linux (or Unix) you'd set up a forwarding record in iptables that redirects a second port to Postgresql. http://www.cyberciti.biz/faq/linux-port-redirection-with-iptables/ I don't know offhand a way to do that on Windows, but I presume that it is possible. George
On Tue, 10 May 2016 10:46:39 -0400 George Neuner <gneuner2@comcast.net> wrote: > On Linux (or Unix) you'd set up a forwarding record in iptables that > redirects a second port to Postgresql. Forwarding to a different host is bad enough without explaining different ports. I think my idea will do the job cleanly, or at least as cleanly as I can expect. > I don't know offhand a way to do that on Windows, but I presume that > it is possible. Luckily I could care less about Windows or Linux for that matter. Unix is all I run on my servers. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net
On Mon, May 09, 2016 at 22:43:53 -0400, "D'Arcy J.M. Cain" <darcy@druid.net> wrote: > >Of course PHP scripts have to run as nobody so I have no choice other >than to have them store passwords in various config.php files but PHP >users are used to that. I would like to fix that but that's a war for >another day. You can use peer authentication if the php scripts run on the same machine as the database, though you'd probably want to use a different local user than 'nobody' to run under.
On Tue, 10 May 2016 11:20:05 -0400 "D'Arcy J.M. Cain" <darcy@druid.net> wrote: > On Tue, 10 May 2016 10:46:39 -0400 > George Neuner <gneuner2@comcast.net> wrote: > > On Linux (or Unix) you'd set up a forwarding record in iptables that > > redirects a second port to Postgresql. > > Forwarding to a different host is bad enough without explaining > different ports. I think my idea will do the job cleanly, or at least > as cleanly as I can expect. > > > I don't know offhand a way to do that on Windows, but I presume that > > it is possible. > > Luckily I could care less about Windows or Linux for that matter. > Unix is all I run on my servers. Just to finish off this topic, I went with the alias method. Both the client and server now have aliases on a new private network and the PHP scripts will have to connect to the new alias. Since that causes the remote machine to also be seen as the aliased IP it does not match the real IP which uses ident so a password (md5) is required. Works great. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net
On 2016-05-09 16:18:39 -0400, D'Arcy J.M. Cain wrote: > On Mon, 9 May 2016 13:02:53 -0700 > Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > So define PHP runs as 'nobody'? > > Because of the way PHP and Apache works PHP script have to run as the > Apache user which, in my case anyway, is "nobody" so every PHP script > runs as nobody. This is not really true. You can use FastCGI to run PHP for each site as a different user. For Apache there is also an MPM (http://mpm-itk.sesse.net/) which lets you run apache processes (and therefore also any embedded mod_php) under different uids. So while running everything as nobody is the default, it is possible to use different users, and I would strongly recommend doing this if you have multiple customers. hp -- _ | Peter J. Holzer | I want to forget all about both belts and |_|_) | | suspenders; instead, I want to buy pants | | | hjp@hjp.at | that actually fit. __/ | http://www.hjp.at/ | -- http://noncombatant.org/