Обсуждение: Help with access control settings in pg_hba.conf -- AAAARGH!
Hi,
I am trying to set up a database server with multiple DB
clusters, so that in each cluster a number of users have their own
database each, with passwordless access (we can trust the network
security in our installation). The following is what seems like it
*should* work:
host all all 127.0.0.1 255.255.255.255 password
host sameuser all xxx.xxx.xxx.0 255.255.255.128 ident sameuser
host all @fac xxx.xxx.xxx.0 255.255.255.128 trust
The second line ("host sameuser") is the problem. It doesn't
work -- when tryign to connect, I keep getting error messages:
$ whoami
testuser
$ psql -h db-edlab -p 7666 testuser testuser
psql: FATAL: IDENT authentication failed for user "testuser"
If I replace 'ident sameuser' with 'trust' there, it works fine
-- but then any user can access anyone else's database, providing they
request the same password.
The idea is that each user should be able to access only their
database, only as themselves, without password -- but I can't figure out
what I am doing wrong. Any help? if what I am trying to do is
impossible, is there any other way to achieve such a goal -- i.e.
passwordless access that allows each user to access only their own
database over the network?
BTW, as long as I am writing, a somewhat related question, which
is not nearly as important as the previous one.
I launch multiple postmatser processes, each servicing a
dedicated DB cluster on a dedicated port. The problem is that I only
ever see *one* local UNIX socket (/tmp/.s.PGSQL.<portnumber>) file.
There is a .lock file created corresponding to each server/port combo,
but it looks like each subsequent instance of the postmaster kills the
previous instance's UNIX socket. Is this how it should be -- and if so,
are there any pg_ctl options I can pass in to make it simply not create
the UNIX sockets altogether, so that only network operations are
supported? AT the moment, I am doing admin access though the loopback
device, so it's not a big issue.
--
| Victor Danilchenko | Give a man a match, and he will be warm |
| danilche@cs.umass.edu | for a moment; but set him on fire, and |
| CSCF | 5-4231 | he will be warm for the rest of his life. |
On Thu, 27 Jan 2005, Victor Danilchenko wrote:
> Hi,
>
> I am trying to set up a database server with multiple DB
>clusters, so that in each cluster a number of users have their own
>database each, with passwordless access (we can trust the network
>security in our installation). The following is what seems like it
>*should* work:
>
>host all all 127.0.0.1 255.255.255.255 password
>host sameuser all xxx.xxx.xxx.0 255.255.255.128 ident sameuser
>host all @fac xxx.xxx.xxx.0 255.255.255.128 trust
>
> The second line ("host sameuser") is the problem. It doesn't
>work -- when tryign to connect, I keep getting error messages:
>
>$ whoami
>testuser
>$ psql -h db-edlab -p 7666 testuser testuser
>psql: FATAL: IDENT authentication failed for user "testuser"
I forgot to mention that yes, I do have identd daemon running on
the connecting system -- from the RHL pidentd RPM.
> If I replace 'ident sameuser' with 'trust' there, it works fine
>-- but then any user can access anyone else's database, providing they
>request the same password.
>
> The idea is that each user should be able to access only their
>database, only as themselves, without password -- but I can't figure out
>what I am doing wrong. Any help? if what I am trying to do is
>impossible, is there any other way to achieve such a goal -- i.e.
>passwordless access that allows each user to access only their own
>database over the network?
>
>
> BTW, as long as I am writing, a somewhat related question, which
>is not nearly as important as the previous one.
>
> I launch multiple postmatser processes, each servicing a
>dedicated DB cluster on a dedicated port. The problem is that I only
>ever see *one* local UNIX socket (/tmp/.s.PGSQL.<portnumber>) file.
>There is a .lock file created corresponding to each server/port combo,
>but it looks like each subsequent instance of the postmaster kills the
>previous instance's UNIX socket. Is this how it should be -- and if so,
>are there any pg_ctl options I can pass in to make it simply not create
>the UNIX sockets altogether, so that only network operations are
>supported? AT the moment, I am doing admin access though the loopback
>device, so it's not a big issue.
>
>
--
| Victor Danilchenko +------------------------------------+
| danilche@cs.umass.edu | I don't have to outrun the bear -- |
| CSCF | 5-4231 | I just have to outrun YOU! |
Pre-scriptum:
I solved the problem, but the text message I originally composed is left
intact in case someone else needs it to help debug IDENT problems.
the solution was in disabling the 'result:encrypt' option
(setting it to 'no') in the /etc/identd.conf file. Once I did that,
IDENT started returning plaintext names instead of encrypted strings,
and clearly PostgreSQL ident client doesn't know how to handle encrypted
IDENT responses. Something to fix in the future release perhaps? or
maybe it's fixed already...
------------------------------------------------------------------------
Old mesage:
I have been doing some more investigation, and it looks like the
problem is with the malfunctioning identd server. it never returns the
username -- always replies 'USERID : OTHER', even though on clientside,
I can see it deriving the correct userid. I have tested this on multiple
systems and Linux releases, so I think this is something i am doing
wrong...
On client side:
# identd -b -d
[Pidentd, version 3.0.16 (compiled for Linux 2.4.21-4.ELsmp) - Apr 14 2004 12:14:17]
kernel_thread() started
kernel_thread() started
timeout_thread() started
entering server main loop
request_thread: fd#7: start
timeout_create(120, ...) -> 09f9cb70
handle_request: fd#7: '1926,7666'
remote = xxx.xxx.xxx.xxx:7666
local = xxx.xxx.xxx.xxx:1926
ka_lookup(), attempt = 1, status = 1
kernel_query, status = 1
euid = 1028
send_result(7) - ruid = -1, euid = 1028
timeout_reset(09f9cb70, 120)
On server side:
[root@elsrv4 init.d]# telnet clienthost 113
Escape character is '^]'.
1926,7666
1926, 7666 : USERID : OTHER :[0RPlNgpZPzlJNeS5JzOol2J1fsNGUMd8]
As I understand it, it's supposed to return the actual userid
instead of 'OTHER'. Any ideas about what's going wrong?..
On Thu, 27 Jan 2005, Victor Danilchenko wrote:
>On Thu, 27 Jan 2005, Victor Danilchenko wrote:
>
>> Hi,
>>
>> I am trying to set up a database server with multiple DB
>>clusters, so that in each cluster a number of users have their own
>>database each, with passwordless access (we can trust the network
>>security in our installation). The following is what seems like it
>>*should* work:
>>
>>host all all 127.0.0.1 255.255.255.255 password
>>host sameuser all xxx.xxx.xxx.0 255.255.255.128 ident sameuser
>>host all @fac xxx.xxx.xxx.0 255.255.255.128 trust
>>
>> The second line ("host sameuser") is the problem. It doesn't
>>work -- when tryign to connect, I keep getting error messages:
>>
>>$ whoami
>>testuser
>>$ psql -h db-edlab -p 7666 testuser testuser
>>psql: FATAL: IDENT authentication failed for user "testuser"
>
> I forgot to mention that yes, I do have identd daemon running on
>the connecting system -- from the RHL pidentd RPM.
>
>> If I replace 'ident sameuser' with 'trust' there, it works fine
>>-- but then any user can access anyone else's database, providing they
>>request the same password.
>>
>> The idea is that each user should be able to access only their
>>database, only as themselves, without password -- but I can't figure out
>>what I am doing wrong. Any help? if what I am trying to do is
>>impossible, is there any other way to achieve such a goal -- i.e.
>>passwordless access that allows each user to access only their own
>>database over the network?
>>
>>
>> BTW, as long as I am writing, a somewhat related question, which
>>is not nearly as important as the previous one.
>>
>> I launch multiple postmatser processes, each servicing a
>>dedicated DB cluster on a dedicated port. The problem is that I only
>>ever see *one* local UNIX socket (/tmp/.s.PGSQL.<portnumber>) file.
>>There is a .lock file created corresponding to each server/port combo,
>>but it looks like each subsequent instance of the postmaster kills the
>>previous instance's UNIX socket. Is this how it should be -- and if so,
>>are there any pg_ctl options I can pass in to make it simply not create
>>the UNIX sockets altogether, so that only network operations are
>>supported? AT the moment, I am doing admin access though the loopback
>>device, so it's not a big issue.
>>
>>
>
>
--
| Victor Danilchenko | If you can keep your head when all about |
| danilche@cs.umass.edu | you people are losing theirs, then you |
| CSCF | 5-4231 | clearly don't understand the situation |
On Thu, Jan 27, 2005 at 12:22:06 -0500, Victor Danilchenko <danilche@cs.umass.edu> wrote: > > the solution was in disabling the 'result:encrypt' option > (setting it to 'no') in the /etc/identd.conf file. Once I did that, > IDENT started returning plaintext names instead of encrypted strings, > and clearly PostgreSQL ident client doesn't know how to handle encrypted > IDENT responses. Something to fix in the future release perhaps? or > maybe it's fixed already... When you encrypt names for ident, the other host isn't supposed to be able to figure out who is making the request. If the remote site has a problem they can give the string back to the connecting site's admins and then they can figure out who is causing problems. If you are actually using ident for authentication, you don't want to use the encrypted mode unless you are willing to modify applications so that they can decrypt the ident strings.
On Thu, 27 Jan 2005, Bruno Wolff III wrote:
>On Thu, Jan 27, 2005 at 12:22:06 -0500,
> Victor Danilchenko <danilche@cs.umass.edu> wrote:
>>
>> the solution was in disabling the 'result:encrypt' option
>> (setting it to 'no') in the /etc/identd.conf file. Once I did that,
>> IDENT started returning plaintext names instead of encrypted strings,
>> and clearly PostgreSQL ident client doesn't know how to handle encrypted
>> IDENT responses. Something to fix in the future release perhaps? or
>> maybe it's fixed already...
>
>When you encrypt names for ident, the other host isn't supposed to be
>able to figure out who is making the request. If the remote site has
>a problem they can give the string back to the connecting site's admins
>and then they can figure out who is causing problems.
>
>If you are actually using ident for authentication, you don't want to use
>the encrypted mode unless you are willing to modify applications so that
>they can decrypt the ident strings.
Aha. Gotcha. Thanks.
--
| Victor Danilchenko | When in danger or in doubt, |
| danilche@cs.umass.edu | run in circles, scream, and shout. |
| CSCF | 5-4231 | Robert Heinlein |
Bruno Wolff III <bruno@wolff.to> writes:
> Victor Danilchenko <danilche@cs.umass.edu> wrote:
>> the solution was in disabling the 'result:encrypt' option
>> (setting it to 'no') in the /etc/identd.conf file.
> When you encrypt names for ident, the other host isn't supposed to be
> able to figure out who is making the request. If the remote site has
> a problem they can give the string back to the connecting site's admins
> and then they can figure out who is causing problems.
I have added a note to our SGML docs pointing out that this doesn't work.
In one sense it *should* not work, by design, since the whole point of
an encrypting ident server is to not let the remote side figure out the
real name of its user. However, we already point out that
[ident is] only appropriate for closed networks where each client
machine is under tight control and where the database and system
administrators operate in close contact.
You could argue that in this scenario it is appropriate for the database
to have a copy of the encryption key that the client's ident server is
using, and then it would be just a small matter of programming to make
PostgreSQL accept encrypted responses. I'm not sure it's worth the
trouble though. One problem is that so far as I've been able to find,
there is not a recognized standard for the encryption transformation,
and so you might need different code for each ident server
implementation you deal with. Also, at least some ident servers can
be configured to deliver encrypted responses to some questioners and
unencrypted responses to others --- so the simplest solution is to use
one of those and configure it to give cleartext responses to your
database server.
regards, tom lane
Victor Danilchenko wrote:
> Hi,
>
> I am trying to set up a database server with multiple DB
> clusters, so that in each cluster a number of users have their own
> database each, with passwordless access (we can trust the network
> security in our installation). The following is what seems like it
> *should* work:
>
> host all all 127.0.0.1 255.255.255.255 password
> host sameuser all xxx.xxx.xxx.0 255.255.255.128 ident sameuser
> host all @fac xxx.xxx.xxx.0 255.255.255.128 trust
>
> The second line ("host sameuser") is the problem. It doesn't
> work -- when tryign to connect, I keep getting error messages:
>
> $ whoami
> testuser
> $ psql -h db-edlab -p 7666 testuser testuser
> psql: FATAL: IDENT authentication failed for user "testuser"
>
> If I replace 'ident sameuser' with 'trust' there, it works fine
> -- but then any user can access anyone else's database, providing they
> request the same password.
you need to read the manual to understand what same user does/does not.
>
> The idea is that each user should be able to access only their
> database, only as themselves, without password -- but I can't figure out
> what I am doing wrong. Any help? if what I am trying to do is
> impossible, is there any other way to achieve such a goal -- i.e.
> passwordless access that allows each user to access only their own
> database over the network?
>
have not had the need for this, but i guess that the sql-commands GRANT and/or
REVOKE can be of help, look in the manual.
>
> BTW, as long as I am writing, a somewhat related question, which
> is not nearly as important as the previous one.
>
> I launch multiple postmatser processes, each servicing a
> dedicated DB cluster on a dedicated port. The problem is that I only
> ever see *one* local UNIX socket (/tmp/.s.PGSQL.<portnumber>) file.
> There is a .lock file created corresponding to each server/port combo,
> but it looks like each subsequent instance of the postmaster kills the
> previous instance's UNIX socket. Is this how it should be -- and if so,
> are there any pg_ctl options I can pass in to make it simply not create
> the UNIX sockets altogether, so that only network operations are
> supported? AT the moment, I am doing admin access though the loopback
> device, so it's not a big issue.
>