Thread: Question regarding psql or libpq
Hi,
It seems psql(or libpq) connects to PostgreSQL twice when md5 auth is
required. Here is a strace log on my Linux machine. Is there any
reason for this? IMO frontend/backend protocol allows to send salt
after receiving AuthenticationMD5Password using the same socket. So
there's no reason to close the socket and make it again. It seems to
be just waste of resource.
:
:
socket(PF_FILE, SOCK_STREAM, 0) = 3 <-- create a socket
fcntl(3, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
fcntl(3, F_SETFD, FD_CLOEXEC) = 0
connect(3, {sa_family=AF_FILE, path="/tmp/.s.PGSQL.5432"...}, 110) = 0
getsockopt(3, SOL_SOCKET, SO_ERROR, [28580018057641984], [4]) = 0
getsockname(3, {sa_family=AF_FILE, path=@""}, [2]) = 0
poll([{fd=3, events=POLLOUT|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLOUT}])
sendto(3, "\0\0\0006\0\3\0\0user\0foo\0database\0test\0ap"..., 54, MSG_NOSIGNAL, NULL, 0) = 54
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "R\0\0\0\f\0\0\0\5\10\6N%"..., 16384, 0, NULL, NULL) = 13
close(3) <-- close the socket
open("/dev/tty", O_RDONLY) = 3
open("/dev/tty", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
ioctl(3, SNDCTL_TMR_TIMEBASE or TCGETS, {B38400 opost isig icanon echo ...}) = 0
ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B38400 opost isig icanon -echo ...}) = 0
fstat(4, {st_mode=S_IFCHR|0666, st_rdev=makedev(5, 0), ...}) = 0
ioctl(4, SNDCTL_TMR_TIMEBASE or TCGETS, {B38400 opost isig icanon -echo ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7feffbd21000
write(4, "Password for user foo: "..., 23Password for user foo: ) = 23
fstat(3, {st_mode=S_IFCHR|0666, st_rdev=makedev(5, 0), ...}) = 0
ioctl(3, SNDCTL_TMR_TIMEBASE or TCGETS, {B38400 opost isig icanon -echo ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7feffbd20000
read(3, "bar\n"..., 4096) = 4
ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B38400 opost isig icanon echo ...}) = 0
write(4, "\n"..., 1
) = 1
close(3) = 0
munmap(0x7feffbd20000, 4096) = 0
close(4) = 0
munmap(0x7feffbd21000, 4096) = 0
socket(PF_FILE, SOCK_STREAM, 0) = 3 <-- and create a socket again
fcntl(3, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
fcntl(3, F_SETFD, FD_CLOEXEC) = 0
connect(3, {sa_family=AF_FILE, path="/tmp/.s.PGSQL.5432"...}, 110) = 0
getsockopt(3, SOL_SOCKET, SO_ERROR, [28580018057641984], [4]) = 0
getsockname(3, {sa_family=AF_FILE, path=@""}, [2]) = 0
poll([{fd=3, events=POLLOUT|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLOUT}])
sendto(3, "\0\0\0006\0\3\0\0user\0foo\0database\0test\0ap"..., 54, MSG_NOSIGNAL, NULL, 0) = 54
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "R\0\0\0\f\0\0\0\5\264 \231\352"..., 16384, 0, NULL, NULL) = 13
sendto(3, "p\0\0\0(md5764161564364fab9083f39d97"..., 41, MSG_NOSIGNAL, NULL, 0) = 41
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "R\0\0\0\10\0\0\0\0S\0\0\0\32application_name\0ps"..., 16384, 0, NULL, NULL) = 325
:
:
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
Tatsuo Ishii <ishii@postgresql.org> writes:
> It seems psql(or libpq) connects to PostgreSQL twice when md5 auth is
> required. Here is a strace log on my Linux machine. Is there any
> reason for this? IMO frontend/backend protocol allows to send salt
> after receiving AuthenticationMD5Password using the same socket. So
> there's no reason to close the socket and make it again. It seems to
> be just waste of resource.
The sequence of events is
1. psql attempts connection
2. libpq receives md5 challenge, realizes it doesn't have password, fails
3. psql collects password from user, tries again
4. libpq successfully makes connection
AFAICS the only alternatives to two connections are
(A) Have psql demand a password from the user before it knows whether
one is needed. Nonstarter for obvious reasons.
(B) Complicate the libpq API to the point where a partially open
connection could be held onto while we ask for a password. Very
messy, and could fail anyway if user takes more than auth_timeout
to enter the password, or enters the wrong password first time.
Two connections are not really a problem IMO, so I would not be in favor
of kluging the API to the extent required by solution (B).
regards, tom lane
On Thu, Dec 16, 2010 at 16:22, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Tatsuo Ishii <ishii@postgresql.org> writes: >> It seems psql(or libpq) connects to PostgreSQL twice when md5 auth is >> required. Here is a strace log on my Linux machine. Is there any >> reason for this? IMO frontend/backend protocol allows to send salt >> after receiving AuthenticationMD5Password using the same socket. So >> there's no reason to close the socket and make it again. It seems to >> be just waste of resource. > > The sequence of events is > > 1. psql attempts connection > 2. libpq receives md5 challenge, realizes it doesn't have password, fails > 3. psql collects password from user, tries again > 4. libpq successfully makes connection > > AFAICS the only alternatives to two connections are > > (A) Have psql demand a password from the user before it knows whether > one is needed. Nonstarter for obvious reasons. > > (B) Complicate the libpq API to the point where a partially open > connection could be held onto while we ask for a password. Very > messy, and could fail anyway if user takes more than auth_timeout > to enter the password, or enters the wrong password first time. > > Two connections are not really a problem IMO, so I would not be in favor > of kluging the API to the extent required by solution (B). (B) could be as simple as a callback asking for it, though, couldn't it? That seems a lot simpler (at least in the API) than trying to support hanging on to half-connected connections. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
> On Thu, Dec 16, 2010 at 16:22, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Two connections are not really a problem IMO, so I would not be in favor
>> of kluging the API to the extent required by solution (B).
> (B) could be as simple as a callback asking for it, though, couldn't
> it? That seems a lot simpler (at least in the API) than trying to
> support hanging on to half-connected connections.
What's the point? You'll still need a retry loop around the connection
operation, in case of wrong response or too slow response.
I would also argue that holding a connection open for many seconds
while the user enters a password is not a net conservation of resources
compared to trying again, ie, the premise of Tatsuo-san's complaint is
dubious to begin with.
regards, tom lane