Re: Notes on implementing URI syntax for libpq

On Tue, Nov 29, 2011 at 12:02 PM, Alexander Shulgin
<ash(at)commandprompt(dot)com> wrote:
>
> Excerpts from Alexander Shulgin's message of Sat Nov 26 22:07:21 +0200 2011:
>>
>> So how about this:
>>
>>   postgresql:ssl://user:pw(at)host:port/dbname?sslmode=...
>>
>> The "postgresql:ssl://" designator would assume "sslmode=require", if not overriden in extra parameters and "postgresql://" would imply "sslmode=prefer".  And to disable SSL you would pick either designator and append "sslmode=disable".
>>
>> The JDBC's "ssl=true" will translate to "sslmode=require".
>
> Hey, I'm going to assume "no objections" equals "positive feedback" and continue hacking in this direction.

A couple of cents on this:

I think the current direction is fine, although as Robert Haas has
said, I am not really at all inclined to view JDBC compatibility as
any kind of a plus. JDBC URLs are weird, and do the drivers actually
link libpq anyway? That world is unto itself. Looking like a normal
URL to the greater body of URL-dom seems like a much more desirable
design trait to me, and after that decreasing the number of ways to
write the same thing. So a weak -1 from me on adding two ways to do
the same thing, of which the way to do it is weird by URL standards.
At best I'd try to avoid choosing any notations that clash or visually
confuse the URLs with their JDBC doppelgangers, and I think the more
URL-ish notation is polite to JDBC in that regard.

Here's a couple of URIs used by projects that do generally end up
delegating to libpq-based drivers. It is precisely this slight
fragmentation that I'd like to see put to rest by this feature in
libpq.

Sequel, for Ruby (all valid):
DB = Sequel.connect('postgres://user:password(at)localhost/blog') # Uses
the postgres adapter
DB = Sequel.connect('postgres://localhost/blog?user=user&password=password')
DB = Sequel.connect('postgres://localhost/blog' :user=>'user',
:password=>'password')

Amazingly, I don't find it trivial to find the format ActiveRecord
(part of Rails) spelled out, but here's one thing that works, at least
(same as Sequel, format one)
postgres://username:password(at)localhost/myrailsdb

Django: Doesn't use URLs at all, preferring dictionary structures, as
far as I can tell.

SQLAlchemy, Python:
dialect+driver://user:password(at)host/dbname[?key=value..]

I'm not familiar enough with the Perl and TCL worlds to comment, nor
some of the newcomers like Golang or Node.js.

Dialect would be 'postgresql', driver 'psycopg2', but at the libpq
level clearly that wouldn't make much sense, so it's basically Sequel
format one-compatible, except it goes by postgresql rather than
postgres for the dialect.

I do really like the attention paid to somehow making AF_UNIX sockets
work; they're great for development. I agree a different scheme would
be hard to swallow, but unfortunately the options to pack that
information into URL notation is at least a little ugly, as far as I
can tell. Using a different scheme is probably not worth the cost of
an ugly URL string, in my book.

Are there any provisions for choosing X.509/cert authentication? I
imagine not, but out-of-band presentation of that information is the
norm there, and I'm not sure if is any room for improvement within
reach.

>> If we can decide on this, we should also put reasonable effort into making JDBC support the same syntax.
>
> What would be our plan on this?  Since the syntax proposed here is strictly a superset of the existing JDBC syntax, I would think this qualifies as an improvement and it would be backwards compatible with any previous version of the JDBC connector.

I suppose that is nice, but is this designed, or coincidental? Is
there any fundamental reason why the JDBC driver will remain so
similar to libpq in the future? Will people realistically be able to
use one URL across their Java and libpq projects in most situations,
now and in the forseeable future, including the keyword options?
Because as soon as one encounters the need to maintain two URLs for
any reason, the otherwise real convenience regresses into bloat.

So there's my pile of opinions.

--
fdr