Dave Page <dpage@postgresql.org> writes: > Just to add a note to that - when running it in the same shell from > which I started the server with messages going to stdout, it seemed > clear that it trys to connect once using PGPASSWORD, then when that > fails, it prompts for the password instead, and then tries to connect > with that and fails a second time. Hmmm ... it seems the problem is that we've defined PQconnectionUsedPassword in such a way that it returns true (causing a prompt) regardless of whether the reason for the connection failure was a bad password or not. We might need to reconsider that API. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Mark Cave-Ayland <mark.cave-ayland@ilande.co.uk> writes: > On Fri, 2007-12-07 at 11:03 -0500, Tom Lane wrote: >> Hmmm ... it seems the problem is that we've defined >> PQconnectionUsedPassword in such a way that it returns true (causing a >> prompt) regardless of whether the reason for the connection failure was >> a bad password or not. We might need to reconsider that API. > Right. I think it depends on the interpretation of the > PQconnectionUsedPassword function. If it should simply return whether or > not the connection used a password or not (as it does now), then you > could argue that it should be psql which should incorporate an > additional check to determine whether the attempt was cancelled due to > an invalid database name. I think the problem is that we've tried to make PQconnectionUsedPassword serve two masters. What it was originally designed for was to provide a way for dblink to determine whether the user had provided an authentication token, or was trying to impersonate the postgres user on a remote connection. For that purpose its behavior is fine. However, we (I think this is my fault :-() also tried to use it to replace the ugly technique of checking for a specific error message string to decide whether to prompt for a password. This example shows that that doesn't work. I can see three ways to proceed: 1. Revert the changes that removed dependencies on PQnoPasswordSupplied. This is ugly but might be the safest solution for 8.3 --- we can always revisit the issue later. 2. Try to adjust PQconnectionUsedPassword so that what it reports after successful connection (which is what dblink cares about) isn't necessarily defined the same as what it says after a failed connection. This seems pretty ugly and nonintuitive. 3. Invent another libpq function, maybe PQconnectionNeedsPassword, that does the right thing for the password-checking tests. I don't think that leaving it as-is is acceptable --- if we do that, we'll be encouraging client apps to adopt a broken API. Thoughts? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > Tom Lane wrote: >> 3. Invent another libpq function, maybe PQconnectionNeedsPassword, >> that does the right thing for the password-checking tests. > My vote goes to (3), if the work can be done quickly, or (1) if it > can't. I don't think it's a big problem to do, as long as we are agreed on the behavior we want. In particular, consider: 1. If libpq obtains a password internally (ie, from PGPASSWORD or ~/.pgpass), and it's wrong, do we want a user password prompt? 2. If we prompt the user for a password, and it's wrong, do we want to try again? Our historical behavior on both points was "no", but at least the second one seems a bit questionable. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Mark Cave-Ayland <mark.cave-ayland@ilande.co.uk> writes: > I'd say that you definitely don't want a user password prompt if libpq's > password is wrong, since I can see this would break a lot of scripts > that weren't launched directly from the shell Agreed, we don't want the behavior to suddenly turn interactive when the user expected it not to be. > As for the second point, I'm not too worried about how many times you > are asked for the password - I personally am happy with the one attempt > as it stands at the moment. On reflection, this ought to be an application decision anyway; libpq isn't going to be a good place to do it since it can have no memory of how many times the "same" connection was tried. Also, on further reflection I realize that PQconnectionUsedPassword is broken for dblink's usage too! It's currently defined, in essence, as "the connection used a password-based auth method". But that fails to distinguish where it got the password from. In particular, as of CVS HEAD it's still possible for a non-superuser to impersonate postgres on a dblink connection, if the DBA has taken the not-unreasonable step of setting up a ~/.pgpass in postgres' home directory. Fortunately, existing release branches don't use that technique, or we'd have a live security problem here. But anyway, this seemingly trivial patch has turned out to be pretty snake-bit :-( So what I think we must do is split the function into two: PQconnectionNeedsPassword: true if server demanded a password and there was none to send (hence, can only be true for a failed connection) PQconnectionUsedPassword: true if server demanded a password and it was supplied from the connection function's argument list, *not* from environment or a password file. Offhand it looks like only dblink needs the second version --- all of our client-side code wants the first one. Barring objections I'll go code this up ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings