Re: [HACKERS] WIP: URI connection string support for libpq
On 03/07/2012 08:03 PM, Peter Eisentraut wrote: On ons, 2012-03-07 at 18:31 +0200, Alex Shulgin wrote: I figured that adding this right into src/interfaces/libpq is polluting the source dir, so I've used src/test instead. I would prefer src/interfaces/libpq/test, to keep it close to the code. Hm, actually that makes more sense and is not unprecedented (I see ecpg has it's own 'test' subdir.) Apparently I was under false impression that all regression tests are concentrated under $(topdir)/src/test. I'll post an updated patch shortly (unless someone like to argue to keep the tests where they are now.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: URI connection string support for libpq
On 03/07/2012 09:16 PM, Alexander Shulgin wrote: I would prefer src/interfaces/libpq/test, to keep it close to the code. Hm, actually that makes more sense and is not unprecedented (I see ecpg has it's own 'test' subdir.) Apparently I was under false impression that all regression tests are concentrated under $(topdir)/src/test. I'll post an updated patch shortly (unless someone like to argue to keep the tests where they are now.) And here it is attached (v7.) The test code now lives under libpq/test. A colleague of mine also pointed out that expanded PGUSER/PGPORT vars slipped into the expected.out file in the previous version, so that was not really useful for testing. The new version addresses the above issue by expanding shell vars in a separate step. The test lines moved to separate file 'regress.in,' since we are expanding the variables manually now (no need to use heredoc.) After moving the test lines to separate file I've noticed that it was identical to the expected output file. So I've thought it would be nice to add some failing URIs as well (improves code coverage.) I did that and one test highlighted a minor bug, which I've also fixed. For that, I decided to move previously extracted parts of code back to the main parser routine, as it was getting too ugly to pass all the required local vars to them. I hope this won't confuse the ones who had a chance to review previous version too much. -- Regards, Alex *** a/src/interfaces/libpq/Makefile --- b/src/interfaces/libpq/Makefile *** *** 121,126 install: all installdirs install-lib --- 121,129 $(INSTALL_DATA) $(srcdir)/pqexpbuffer.h '$(DESTDIR)$(includedir_internal)' $(INSTALL_DATA) $(srcdir)/pg_service.conf.sample '$(DESTDIR)$(datadir)/pg_service.conf.sample' + check installcheck: + $(MAKE) -C test $@ + installdirs: installdirs-lib $(MKDIR_P) '$(DESTDIR)$(includedir)' '$(DESTDIR)$(includedir_internal)' *** *** 132,137 uninstall: uninstall-lib --- 135,141 rm -f '$(DESTDIR)$(datadir)/pg_service.conf.sample' clean distclean: clean-lib + $(MAKE) -C test $@ rm -f $(OBJS) pthread.h libpq.rc # Might be left over from a Win32 client-only build rm -f pg_config_paths.h *** *** 142,145 clean distclean: clean-lib --- 146,150 rm -f encnames.c wchar.c maintainer-clean: distclean maintainer-clean-lib + $(MAKE) -C test $@ rm -f libpq-dist.rc *** a/src/interfaces/libpq/fe-connect.c --- b/src/interfaces/libpq/fe-connect.c *** *** 282,287 static const PQEnvironmentOption EnvironmentOptions[] = --- 282,290 } }; + /* The connection URI must start with either of the following designators: */ + static const char uri_designator[] = postgresql://; + static const char short_uri_designator[] = postgres://; static bool connectOptions1(PGconn *conn, const char *conninfo); static bool connectOptions2(PGconn *conn); *** *** 297,303 static PQconninfoOption *conninfo_parse(const char *conninfo, static PQconninfoOption *conninfo_array_parse(const char *const * keywords, const char *const * values, PQExpBuffer errorMessage, bool use_defaults, int expand_dbname); ! static char *conninfo_getval(PQconninfoOption *connOptions, const char *keyword); static void defaultNoticeReceiver(void *arg, const PGresult *res); static void defaultNoticeProcessor(void *arg, const char *message); --- 300,333 static PQconninfoOption *conninfo_array_parse(const char *const * keywords, const char *const * values, PQExpBuffer errorMessage, bool use_defaults, int expand_dbname); ! static PQconninfoOption *conninfo_uri_parse(const char *uri, ! PQExpBuffer errorMessage); ! static bool conninfo_uri_parse_options(PQconninfoOption *options, ! const char *uri, char *buf, ! PQExpBuffer errorMessage); ! static char *conninfo_uri_parse_local_socket_dir(PQconninfoOption *options, ! const char *uri, ! char *buf, char *lastc, ! PQExpBuffer errorMessage); ! static char *conninfo_uri_parse_remote_host(PQconninfoOption *options, ! const char *uri, ! char *buf, char *lastc, ! PQExpBuffer errorMessage); ! static bool conninfo_uri_parse_params(char *params, ! PQconninfoOption *connOptions, ! PQExpBuffer errorMessage); ! static char *conninfo_uri_decode(const char *str, PQExpBuffer errorMessage); ! static bool get_hexdigit(char digit, int *value); ! static const char *conninfo_getval(PQconninfoOption *connOptions, ! const char *keyword); ! static PQconninfoOption *conninfo_storeval(PQconninfoOption *connOptions, ! const char *keyword, const char *value, ! PQExpBuffer errorMessage, bool ignoreMissing); ! static PQconninfoOption *conninfo_store_uri_encoded_value( ! PQconninfoOption *connOptions, ! const char *keyword, const char *encoded_value, ! PQExpBuffer errorMessage, bool ignoreMissing); ! static
Re: [HACKERS] WIP: URI connection string support for libpq
On 03/06/2012 01:09 AM, Peter Eisentraut wrote: On ons, 2012-02-22 at 12:26 -0500, Greg Smith wrote: I started collecting up all the variants that do work as an initial shell script regression test, so that changes don't break something that already works. Here are all the variations that already work, setup so that a series of 1 outputs is passing: Let's please add something like this to the patch. Otherwise, I foresee a lot of potential to break corner cases in the future. I've included a (separate) test shell script based on Greg's cases in one of the updates. What would be preferred place to plug it in? Override installcheck in libpq Makefile? -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: URI connection string support for libpq
On 02/24/2012 03:18 PM, Florian Weimer wrote:
* Alex Shulgin:
It's ugly, but it's standard practice, and seems better than a separate
-d parameter (which sort of defeats the purpose of URIs).
Hm, do you see anything what's wrong with ?dbname=other if you don't
like a separate -d?
It's not nice URI syntax, but it's better than an out-of-band mechanism.
Attached is v5 of the patch, adding support for local Unix socket
directory specification w/o the need to percent-encode path separators.
The path to directory must start with forward slash, like so:
postgres:///path/to/socket/dir
To specify non-default dbname use URI query parameters:
postgres:///path/to/socket/dir?dbname=other
Username/password should be also specified on query parameters in this
case, as opposed to user:pw@host syntax supported by host URIs.
--
Alex
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
***
*** 282,287 static const PQEnvironmentOption EnvironmentOptions[] =
--- 282,290
}
};
+ /* The connection URI must start with either of the following designators: */
+ static const char uri_designator[] = postgresql://;
+ static const char short_uri_designator[] = postgres://;
static bool connectOptions1(PGconn *conn, const char *conninfo);
static bool connectOptions2(PGconn *conn);
***
*** 297,303 static PQconninfoOption *conninfo_parse(const char *conninfo,
static PQconninfoOption *conninfo_array_parse(const char *const * keywords,
const char *const * values, PQExpBuffer errorMessage,
bool use_defaults, int expand_dbname);
! static char *conninfo_getval(PQconninfoOption *connOptions,
const char *keyword);
static void defaultNoticeReceiver(void *arg, const PGresult *res);
static void defaultNoticeProcessor(void *arg, const char *message);
--- 300,333
static PQconninfoOption *conninfo_array_parse(const char *const * keywords,
const char *const * values, PQExpBuffer errorMessage,
bool use_defaults, int expand_dbname);
! static PQconninfoOption *conninfo_uri_parse(const char *uri,
! PQExpBuffer errorMessage);
! static bool conninfo_uri_parse_options(PQconninfoOption *options,
! const char *uri, char *buf,
! PQExpBuffer errorMessage);
! static char *conninfo_uri_parse_local_socket_dir(PQconninfoOption *options,
! const char *uri,
! char *buf, char *lastc,
! PQExpBuffer errorMessage);
! static char *conninfo_uri_parse_remote_host(PQconninfoOption *options,
! const char *uri,
! char *buf, char *lastc,
! PQExpBuffer errorMessage);
! static bool conninfo_uri_parse_params(char *params,
! PQconninfoOption *connOptions,
! PQExpBuffer errorMessage);
! static char *conninfo_uri_decode(const char *str, PQExpBuffer errorMessage);
! static bool get_hexdigit(char digit, int *value);
! static const char *conninfo_getval(PQconninfoOption *connOptions,
! const char *keyword);
! static PQconninfoOption *conninfo_storeval(PQconninfoOption *connOptions,
! const char *keyword, const char *value,
! PQExpBuffer errorMessage, bool ignoreMissing);
! static PQconninfoOption *conninfo_store_uri_encoded_value(
! PQconninfoOption *connOptions,
! const char *keyword, const char *encoded_value,
! PQExpBuffer errorMessage, bool ignoreMissing);
! static PQconninfoOption *conninfo_find(PQconninfoOption *connOptions,
const char *keyword);
static void defaultNoticeReceiver(void *arg, const PGresult *res);
static void defaultNoticeProcessor(void *arg, const char *message);
***
*** 580,586 PQconnectStart(const char *conninfo)
static void
fillPGconn(PGconn *conn, PQconninfoOption *connOptions)
{
! char *tmp;
/*
* Move option values into conn structure
--- 610,616
static void
fillPGconn(PGconn *conn, PQconninfoOption *connOptions)
{
! const char *tmp;
/*
* Move option values into conn structure
***
*** 3739,3745 ldapServiceLookup(const char *purl, PQconninfoOption *options,
static int
parseServiceInfo(PQconninfoOption *options, PQExpBuffer errorMessage)
{
! char *service = conninfo_getval(options, service);
char serviceFile[MAXPGPATH];
char *env;
bool group_found = false;
--- 3769,3775
static int
parseServiceInfo(PQconninfoOption *options, PQExpBuffer errorMessage)
{
! const char *service = conninfo_getval(options, service);
char serviceFile[MAXPGPATH];
char *env;
bool group_found = false;
***
*** 4129,4161 conninfo_parse(const char *conninfo, PQExpBuffer errorMessage,
}
/*
! * Now we have the name and the value. Search for the param record.
! */
! for (option = options; option-keyword != NULL; option++)
! {
! if (strcmp(option-keyword, pname) == 0)
! break;
! }
! if (option-keyword == NULL)
! {
! printfPQExpBuffer(errorMessage,
! libpq_gettext(invalid connection
Re: [HACKERS] WIP: URI connection string support for libpq
On 02/25/2012 09:37 PM, Cédric Villemain wrote: I've not followed all the mails about this feature but I don't find it is a nice syntax too. ?dbname=other looks like dbname is an argument, but dbname is a requirement for postgresql connexion. Ugh, not really. AFAIK, dbname is a connection option which defaults to $USER, unless overridden on command line or in the environment (or via a service file.) -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: URI connection string support for libpq
Excerpts from Greg Smith's message of Wed Dec 14 02:54:14 +0200 2011: Initial quick review of your patch: you suggested this as the general form: psql -d postgresql://user@pw:host:port/dbname?param1=value1param2=value2... That's presumably supposed to be: psql -d postgresql://user:pw@host:port/dbname?param1=value1param2=value2... Yes, that was clearly a typo, so user:pw@host:port. If we had to pick one URI prefix, it should be postgres. But given the general name dysfunction around this project, I can't see how anyone would complain if we squat on postgresql too. That'd be true if we've started afresh in the absence of any existing URI implementations. IMO, what makes a connection URI useful is: a) it keeps all the connection parameters in a single string, so you can easily send it to other people to use, and b) it works everywhere, so the people who've got the URI can use it and expect to get the same results as you do. (Well, not without some quirks, like effects of locally-set environment variables or presence of .pgpass or service files, or different nameserver opinions about which hostname resolves to which IP address, but that is pretty much the case with any sort of URIs.) This is not in objection to what you say, but rather an important thing to keep in mind for the purpose of this discussion. Whatever decision we make here, the libpq-binding connectors are going to be compatible with each other automatically if they just pass the URI to libpq. However, should we stick to using postgresql:// URI prefix exclusively, these might need to massage the URI a bit before passing further (like replacing postgres:// with postgresql://, also accepting the latter should be reasonable.) With proper recommendations from our side, the new client code will use the longer prefix, thus achieving compatibility with the only(?) driver not based on libpq (that is, JDBC) in the long run. Attached patch modifies yours to prove we can trivially support both, in hopes of detonating this argument before it rages on further. Tested like this: $ psql -d postgres://gsmith@localhost:5432/gsmith And that works too now. I doubt either of us like what I did to the handoff between conninfo_uri_parse and conninfo_uri_parse_options to achieve that, but this feature is still young. Yes, the caller could just do the pointer arithmetics itself, since the exact URI prefix is known at the time, then pass it to conninfo_uri_parse. After this bit of tinkering with the code, it feels to me like this really wants a split() function to break out the two sides of a string across a delimiter, eating it in the process. Adding the level of paranoia I'd like around every bit of code I see that does that type of operation right now would take a while. Refactoring in terms of split and perhaps a few similarly higher-level string parsing operations, targeted for this job, might make it easier to focus on fortifying those library routines instead. For example, instead of the gunk I just added that moves past either type of protocol prefix, I'd like to just say split(buf,://,left,right) and then move on with processing the right side. A search with cscope over my repo clone doesn't give any results for split, so I assume you're talking about a new function with a signature similar to the following: split(char *buf, const char *delim, char **left, char **right) Note, there should be no need for parameter left, since that will be pointing to the start of buf. Also, we might just return right as a function's value instead of using out-parameter, with NULL meaning delimiter was not found in the buffer. Now, if you look carefully at the patch's code, there are numerous places where it accepts either of two delimiting characters and needs to examine one before zeroing it out, so it'll need something more like this: char *need_a_good_name_for_this(char *buf, const char *possible_delims, char *actual_delim) where it will store a copy of encountered delimiting char in *actual_delim before modifying the buffer. I agree with your comment that we need to add some sort of regression tests for this. Given how the parsing is done right now, we'd want to come up with some interesting invalid strings too. Making sure this fails gracefully (and not in a buffer overflow way) might even use something like fuzz testing too. Around here we've just been building some Python scripting to do that sort of thing, tests that aren't practical to do with pg_regress. I'd appreciate if you could point me to any specific example of such existing tests to take some inspiration from. -- Regards, Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: URI connection string support for libpq
Excerpts from Robert Haas's message of Tue Dec 13 23:31:32 +0200 2011: On Mon, Dec 12, 2011 at 6:55 PM, Peter van Hardenberg p...@pvh.ca wrote: I'd like to make the controversial proposal that the URL prefix should be postgres: instead of postgresql:. Postgres is a widely accepted nickname for the project, and is eminently more pronounceable. Once the url is established it will be essentially impossible to change later, but right now only a nearly insurmountable mailing list thread prevents it. That, and the fact the JDBC is already doing it the other way. A reasonable compromise might be to accept either one. AIUI, part of what Alexander was aiming for here was to unite the clans, so to speak, and it would seem a bit unfriendly (and certainly counter-productive as regards that goal) to pull the rug out from him by refusing to support that syntax over what is basically a supermassive bikeshed. However, being generous in what we accept won't cost anything, so why not? (oops, misfired... now sending to the list) I was going to put a remark about adding to the soup here, but realized that if this is actually committed, the soup is gonna be like this: libpq-supported syntax vs. everything else (think JDBC, or is there any other driver in the wild not using libpq?) This is in the ideal world, where every binding is updated to embrace the new syntax and users have updated all of their systems, etc. Before that, why don't also accept psql://, pgsql://, postgre:// and anything else? Or wait, aren't we adding to the soup again (or rather putting the soup right into libpq?) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WIP: URI connection string support for libpq
Hello Hackers, Attached is a work-in-progress patch for URI connection string syntax support in libpq. The recent discussion (also pointing to the original one) is here: http://archives.postgresql.org/message-id/132180-sup-1235@moon The patch adds support for the following syntax in psql, by adding special handling of dbname parameter, when it starts with postgresql://, e.g: psql -d postgresql://user@pw:host:port/dbname?param1=value1param2=value2... Virtually every component of the above syntax is optional, ultimately allowing for, e.g: psql -d postgresql:/// to specify local connection via Unix socket, with default port, user name, dbname, etc. URI percent-encoding is handled, in particular, allowing to include special symbols in the embedded password string, or to specify non-standard Unix socket location, like the following: psql -d postgresql://%2Fvar%2Fpgsql%2Ftmp/mydb The patch applies cleanly against the master branch and compiles w/o errors or warnings. No tests were broken by this patch on my box, as far as I can tell. The patch follows design initially proposed and tries to address feedback gathered from the recent discussion. Special provision was made to improve compatibility with JDBC's connection URIs, by treating ssl=true parameter as equivalent of sslmode=require. The patch intentionally omits documentation changes, to focus on the desired behavior and new code design. I've put reasonable effort into testing the new code by feeding various parameters to psql -d. However, if there's a facility for writing formal regression tests against psql, I'd be happy to use that. I'm also adding this to the next open CommitFest: 2012-01. -- Regards, Alex libpq-uri-v3.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Daniel Farina's message of Mon Dec 05 11:56:19 +0200 2011: 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. Daniel, The JDBC driver is special in that it intentionally does not use libpq. Given every other binding (think Ruby, Python, Perl, Tcl, etc.) does use libpq, it makes perfect sense to me to make the syntax compatible with JDBC. I see this as a two-fold effort: add URI syntax to libpq *and* improve JDBC's syntax to support the usual user:pw@ notation. This way, not only the above language's bindings URI syntaxes would become compatible with each other (eventually, with release of new libpq and new drivers' versions,) but they would also be interchangeable with JDBC's new syntax (also, eventually.) 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. Since the idea is to parse any supported URI query parameters, this is likely going to Just Work(tm) if you add proper sslcert=sslkey= query parameters to the connection URI. 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. See above. The hope is that URIs will be compatible sans the driver-specific extra query parameters which might be not recognized by either party. -- Regards, Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Daniel Farina's message of Fri Dec 09 23:04:26 +0200 2011: I guess if I move the parenthetical grouping of logic around, what you are probably intending to say is everyone except this one ecosystem does the normal thing, so we have an opportunity to Unite The Clans, by absorbing a unique aspect of one of them Yes, what I meant is something more or less like that. i:am:feeling:like:postgresql://(etc) jdbc:postgresql://(etc) psycopg2:postgresql://(etc) Which would reduce to the same thing as: postgresql://(etc) Well, it wasn't suggested that JDBC people paste their URIs to psql, while keeping the jdbc: prefix, that would be really weird thing to do. However, I have just noticed they *do* require that part themselves, like in: String url = jdbc:postgresql://localhost/test; Connection conn = DriverManager.getConnection(url); It is really weird, since as far as I can see from the docs, the jdbc: part is always discarded by the driver manager. That must be some true Java way of doing things. :-p What I can't get excited about is: postgresql:ssl://user:pw@host:port/dbname?sslmode=... Since this is not actually a URL, and the scheme using the above rule would be ssl. If you really want to have SSL be part of the scheme (given ssl=require exists, I'd prefer One Way that involves no scheme alterations to denote the transport), then you can use an RFC-compatible notation like +: postgresql+ssl:// For which the scheme would be postgresql+ssl. Again, I'm not terribly excited about having a scheme that denotes the transport (in spite of it being semi-commonly done as in svn+ssh), especially if redundant with query string options. Yeah, I was also considering +ssl, but don't recall if I ever suggested that on the list. My primary motivation behind making SSL stand out in the URI is that it feels wrong when that is pushed to the query parameters. In a real-world URI that would be impossible, since it's the server which is supposed to parse the parameters, not the client, but that can only happen after the connection has been established. However, since we're parsing all of the query parameters locally in a client, this becomes less of a problem, so I would agree that we don't need a special scheme for SSL connections. Especially, since the default SSL mode is prefer and to override that you still need to add a sslmode= query parameter. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Alexander Shulgin's message of Sat Nov 26 21:46:32 +0200 2011: I would also think that if one is to specify the password in the URI, and the password happen to contain the @-sign (e.g. !@#$%^,) it should be percent-encoded, like: postgresql://user:!%40#$%^@/ Actually, like: postgresql://user:!%40#$%25^@/ since the %-sign has to be encoded itself. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Alexander Shulgin's message of Sat Nov 26 22:07:21 +0200 2011: So how about this: postgresql:ssl://user:pw@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. 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. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Greg Smith's message of Mon Nov 28 10:08:42 +0200 2011: On 11/24/2011 05:21 AM, Alvaro Herrera wrote: A coworker also suggested using a different designator: postgresqli:///path/to/socket:5433/database postgresqli://:5433/database This is not unprecedented. An example is how CUPS handles this problem when connecting printers using URIs: http://www.cups.org/documentation.php/network.html where you might see this for the usual port: lpd://ip-address-or-hostname/queue And this for AppSocket AKA JetDirect: socket://ip-address-or-hostname I am certainly not going to defend printing setup with CUPS as a model worth emulating, just noting the similarity here. I think we'll save miles of user headaches if there's only one designator. I'm not a big fan of using different designator for local socket connections either, especially if they differ so little (the added 'i' might be too hard to spot, moreso if the displayed using proportional font.) Not to mention that printers and compatibility don't go together that often, in my (probably way too limited) experience. ;-) As it was suggested downthread, postgresql://[:port]/[mydb] should work perfectly for this purpose, since it's just a matter of allowing empty host/addr in the URI. So, using the default port: postgresql:///mydb (notice the similarity with the local-filesystem URI scheme: file:///) Speaking of JDBC, the postgresql:///mydb notation may be abbreviated as postgresql:mydb, which is not unreasonable to support in psql too. -- Regards, Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Peter Eisentraut's message of Thu Nov 24 22:05:09 +0200 2011: On tor, 2011-11-24 at 15:43 +0200, Alexander Shulgin wrote: Huh? The service definitions are read from a local pg_service.conf, and are specified by setting PGSERVICE (and PGSERVICEFILE) environment variables, no? What would you do with such URI if you need to other people to connect to the same service? Send them URI along with the pg_service.conf? A full URI would also rely on host names or IP addresses being the same everywhere. It's all a matter of degree ... True, but it is much more reasonable to expect that hostnames will resolve to the same addresses most of the time (save for zone changes propagation time.) Still I can imagine where this may be useful, like local networks with shared pg_service.conf files. And since we don't need to do anything special to support the behavior (i.e. postgresql:///?service=foo is going to work out of the box,) this seems to be a non-problem. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 15:59:08 +0200 2011: I think we could do something like: postgresql://user:pw@host:port/database?param1=val1param2=val2param3=val3... I wonder if this should be allowed syntax (i.e. specify a user, but connect locally, so leave 'host' to be an empty string): postgresql://user@/ Furthermore, if we want to connect locally, but to a non-default port: postgresql://user@:5433/ I would also think that if one is to specify the password in the URI, and the password happen to contain the @-sign (e.g. !@#$%^,) it should be percent-encoded, like: postgresql://user:!%40#$%^@/ Reasonable? -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 13:57:17 +0200 2011: I think it would be really weird not to support user:pw@host:port. You can presumably also support the JDBC style for backward compatibility, but I don't think we should adopt that syntax as project standard. By the way, if we're already considering this, what about special syntax for SSL, instead of the JDBC's ssl=true thingy? Given that the default sslmode is prefer I assume libpq tries SSL first, then falls back to plain text if that's not available. To me, it looks much more natural if the fact that SSL is/should be used is stated early in the URI syntax, like: https://;, svn+ssh://, etc., rather than in the query parameters (if the parameters were to be passed to remote service to process, like it's done with HTTP[S], this would not make any sense at all.) But given that sslmode can currently be either of: disable, allow, prefer, require, verify-ca or verify-full (and who knows if any new allowed mode could show up later,) allowing sslmode=whatever makes sense. Note, that this is not the same as ssl=whatever. So how about this: postgresql:ssl://user:pw@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. If we can decide on this, we should also put reasonable effort into making JDBC support the same syntax. Thoughts? -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Martijn van Oosterhout's message of Thu Nov 24 09:40:42 +0200 2011: On Thu, Nov 24, 2011 at 08:59:56AM +0200, Alexander Shulgin wrote: How would you specifiy a local port/UNIX domain socket? Missed that in my previous reply. If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. Uh, no it doesn't. -h localhost uses TCP/IP (try it). This is one piece of mysql magic we don't copy. If you want to use the socket you need to specify -h /tmp or wherever you keep it. Leaving out the -h parameter also uses UNIX domain sockets. Oh, you're right -- I was under wrong impression (hacking in the wrong local install, you know.) Which does raise the valid question of how to represent that in URI syntax. SQLAlchemy (for example) doesn't try with it's URL syntax, to connect to a non-default UNIX socket, you need to create the URL object directly. Well, whatever syntax we're going to invent here: it is not supported by the JDBC driver. Because Java does not support using unix sockets the PostgreSQL™ server must be configured to allow TCP/IP connections. http://jdbc.postgresql.org/documentation/head/prepare.html Or, this has to be done not in the URI syntax itself, but with the use of some external option. Or maybe we can just add unixsocket=... and hope that JDBC simply ignores that? I think I will try the last option to see if that's the case. (Looking at libpq code, I think we will also need to verify that host/hostaddr parameter is pointing to the local host and reset it to NULL, to actually make libpq consider UNIX sockets.) How about the service option, that's a nice way of handling non-default socket options. The service handling isn't going to be affected with the proposed approach. So, if PGSERVICE is given, the options from the service file are applied after the URI is parsed, filling any parameters not set using previous methods. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Making TEXT NUL-transparent
Excerpts from Florian Weimer's message of Thu Nov 24 11:27:51 +0200 2011: and why you don't use bytea ? Text should be correct literal. It's actually UTF-8 text, and some PostgreSQL functions are only available for TEXT, but not BYTEA, e.g.: bfk_int= SELECT '\x006500'::bytea ~ 'A'; ERROR: operator does not exist: bytea ~ unknown And how will those TEXT functions behave on a value with an embedded NUL? Or is it not only about being able to *store* NULs in a text field? -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Making TEXT NUL-transparent
Excerpts from Florian Weimer's message of Thu Nov 24 12:59:09 +0200 2011: I have a different question. What is reason for embedded NULs inside strings? The source system does not enforce that constraint, so from time to time, such data slips through. I don't know why it's there in the first place, and I have no control over the original data source. Usually, it's okay to silently strip NUL bytes; it's just a bit awkward. Strip? Like this: loss\0less = lossless? That would be awkward for sure. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 13:57:17 +0200 2011: I think it would be really weird not to support user:pw@host:port. You can presumably also support the JDBC style for backward compatibility, but I don't think we should adopt that syntax as project standard. Well, I don't believe JDBC syntax is ideal either, but I don't recall any better option proposed in the original discussion: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01945.php Do you suggest that we should reconsider? -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Alvaro Herrera's message of Thu Nov 24 15:21:49 +0200 2011: I think the question is allowing the URI to specify a service. Huh? The service definitions are read from a local pg_service.conf, and are specified by setting PGSERVICE (and PGSERVICEFILE) environment variables, no? What would you do with such URI if you need to other people to connect to the same service? Send them URI along with the pg_service.conf? Or are we talking about different things completely? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 15:35:36 +0200 2011: Do you suggest that we should reconsider? I guess my feeling is that if we're going to have URLs, we ought to try to adhere to the same conventions that are used for pretty much every other service that supports URLs. user:pw@host:port is widely supported by multiple protocols, so I think we would need a very good reason to decide to go off in a completely different direction. It would be nice to be compatible with whatever JDBC does (link?) but I'm not prepared to put that ahead of general good design. What JDBC supports is rather weird and far from being ideal: http://jdbc.postgresql.org/documentation/head/connect.html The problem with supporting multiple syntaxes, IMO is that it makes libpq compatible in only one direction: from particular foreign syntax to libpq, but not from libqp to any other particular foreign syntax. So when you see psql -d URL you wouldn't know if you can copy that URL to JDBC or any other connection interface parameter, unless you check the docs thoroughly. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 15:59:08 +0200 2011: Well, based on that document, I think that trying to be bug-compatible with the JDBC syntax is a, erm, doomed effort. I mean, what are you going to do with things like loglevel or logUnclosedConnections that change the behavior of JDBC, not PostgreSQL? The proposition was to ignore keywords not known to libpq (see top of this thread.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Alexey Klyukin's message of Thu Nov 24 10:22:21 +0200 2011: Another idea is to use local:/dir/name for UNIX domain socket instead of hostname:port, like it's displayed in the psql prompt. So the whole thing would look like this: postgresql://local:/dir/name/dbname?param1=val1... Where /dir/name is the absolute path to the directory containing the socket file. If one wants to use the default directory the following syntax may serve the need: postgresql://local:/dbname -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 16:02:38 +0200 2011: So, in that light, do we still think that letting the user specify a service name in the URI makes sense? (My personal opinion is yes). service is just a connection parameter, so if we choose a URL format that allows any connection parameter to be specified, this falls out naturally, without any additional work. And if we don't choose such a URL format, we are, in my humble opinion, crazy. The patch draft I have uses that format, yes: so any keyword libqp recognizes can be given in form of param=value URI query parameter. e.g. if we used the format suggested in my previous email, this would just boil down to: postgresql:///?service=foo Oh, well, that would make sense. It also appeared to me that we should deny overriding host, port and dbname by the query parameters to prevent confusion, e.g: postgresql://host:port/dbname?host=otherhostport=otherportdbname=otherdb -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Florian Weimer's message of Thu Nov 24 16:31:29 +0200 2011: I plan to add UNIX Domain socket support to the JDBC driver. Eventually, the JDK will expose UNIX Domain sockets to Java code, too (they are already used internally for management functions). Do you maybe plan to support user:pw@host syntax too? :-) Apparently, that would make people happier, also JDBC and libpq URIs will become fully compatible (eventually.) -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 17:02:13 +0200 2011: On Thu, Nov 24, 2011 at 9:40 AM, Alexander Shulgin a...@commandprompt.com wrote: Another idea is to use local:/dir/name for UNIX domain socket instead of hostname:port, like it's displayed in the psql prompt. So the whole thing would look like this: postgresql://local:/dir/name/dbname?param1=val1... Where /dir/name is the absolute path to the directory containing the socket file. If one wants to use the default directory the following syntax may serve the need: postgresql://local:/dbname I think this is just weird. libpq treats any hostname that starts with a slash as hostname. And there's a standard way of URL-encoding characters that would otherwise be treated as terminators: you write a percent sign followed by two hex digits. So if you want the host to be /tmp, you just should just write: postgresql://%2Ftmp/fred Which is the equivalent of the connection string: host=/tmp dbname=fred Yeah, that should work, but it's giving the pathname a really weird look. Given that this is going to be used only rarely, this is less of a problem, though. This may appear to be slightly inconvenient notation, but there is little reason to reinvent syntax that the URL gods have already devised, and in practice specifying an explicit pathname in a connection string is quite rare. One normally specifies a local socket connection by omitting to specify a hostname at all, and that can work here, too. That is, postgresql:///fred should be equivalent to the connection string: dbname=fred ...which means it will use the default socket directory on UNIX, and a loopback connection on Windows. And postgresql:/// should be equivalent to an empty connection string, defaulting everything. Hm... that's neat. Didn't appear to me due to a bit too restrictive parser rules in my draft patch. Now that I allow host to be empty string, the above works like a charm! -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Florian Weimer's message of Wed Nov 23 13:04:47 +0200 2011: * Alexander Shulgin: This, in my opinion, is very similar to what we would like to achieve with the URI syntax, so the above could also be specified using a URI parameter like this: psql -d postgresql://example.net:5433/mydb How would you specifiy a local port/UNIX domain socket? Would it be possible to add something like psql -d postgresql+ssh://fweimer@db5/var/run/postgresql/.s.PGSQL.5432 similar to what Subversion supports? (This might have security implications when used from untrusted PHP scripts.) While it is really tempting to provide support for all that fancy stuff (or at least support user:password@host part instead of the ugly ?user=password=) this will make psql URIs backward-incompatible with the JDBC syntax, which is exactly what we want to avoid. The primary reason people even considering adding the syntax, IMO is compatibility and thus, it has to be compatible in both directions. If we support something that's more than JDBC provides, we're just adding to the soup of incompatible URI syntaxes out there. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Florian Weimer's message of Wed Nov 23 13:04:47 +0200 2011: * Alexander Shulgin: This, in my opinion, is very similar to what we would like to achieve with the URI syntax, so the above could also be specified using a URI parameter like this: psql -d postgresql://example.net:5433/mydb How would you specifiy a local port/UNIX domain socket? Missed that in my previous reply. If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Dmitriy Igrishin's message of Thu Nov 24 09:19:02 +0200 2011: If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. But what if the user wants to connect exactly via socket or TCP/IP ? And what if the user needs to specify a socket file name extension? How do you achieve that with the current psql set of command line options (and, possibly environment variables?) I would think the same method will work with URI, as with the proposed approach the URI is just decomposed into host, port and dbname parts and the rest of the code works like if you've had specified -h example.net -p 5433 -d mydb instead of the URI parameter. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Notes on implementing URI syntax for libpq
Hello, It was proposed a while ago for libpq to support URI syntax for specifying the connection information: http://archives.postgresql.org/message-id/1302114698.23164.17.camel@jd-desktop http://archives.postgresql.org/pgsql-hackers/2011-07/msg01144.php It appears to me that the consensus was that: 1) this feature is indeed going to be useful, and 2) that we would go by implementing a simple URI parser ourselves instead of adding dependency on any fancy external library. Now we're going to actually implement this. It is known that libpq (and, thus every utility using it to connect a database: psql, pg_dump, etc.) supports a way to specify some of the connection parameters (or all of them) via a single conninfo string, e.g: psql -d dbname=mydb host=example.net port=5433 This, in my opinion, is very similar to what we would like to achieve with the URI syntax, so the above could also be specified using a URI parameter like this: psql -d postgresql://example.net:5433/mydb We can also support specifying extra parameters via the usual ?keyword=valuekeyword2=other syntax. As it was noted in the original discussion, sticking to what JDBC provides makes the most sense: http://jdbc.postgresql.org/documentation/head/connect.html So we should support 'user', 'password' and 'ssl' parameters (and probably just ignore the rest, at least for start.) Upon libpq code inspection I come to think that the best place to plug this seems to be conninfo_array_parse function (where dbname keyword is currently being checked for '=' symbol and expanded): http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/interfaces/libpq/fe-connect.c;h=ed9dce941e1d57cce51f2c21bf29769dfe2ee542;hb=HEAD#l4262 We could similarly check for postgresql: designator and if present, extract the connection options from the dbname keyword thought to be a connection URI. The check should obviously go before the current check for '=', if we're going support the extra parameters, as outlined above. I am going to sketch a work-in-progress patch in the background of a discussion here. Your thoughts on this are very welcome! -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postmaster holding unlinked files for pg_largeobject table
Hello Hackers, There is some strange behavior we're experiencing with one of the customer's DBs (8.4) We've noticed that free disk space went down heavily on a system, and after a short analysis determined that the reason was that postmaster was holding lots of unlinked files open. A sample of lsof output was something like this: postmaste 15484 postgres 57u REG 253,0 1073741824 41125093 /srv/pgsql/data/base/352483309/2613.2 (deleted) postmaste 15484 postgres 58u REG 253,0 1073741824 41125094 /srv/pgsql/data/base/352483309/2613.3 (deleted) postmaste 15484 postgres 59u REG 253,0 1073741824 41125095 /srv/pgsql/data/base/352483309/2613.4 (deleted) There were about 450 such (or similar) files, all of them having /2613 in the filename. Since 2613 is a regclass of pg_largeobject and we are indeed working with quite a few large objects in that DB so this is where our problem lies we suspect. Restarting PostgreSQL obviously helps the issue and the disk space occupied by those unlinked files (about 63GB actually) is reclaimed. So what happens on that host is that we drop/restore a fresh version of the DB from the production host, followed by a migration script which among other things loads around 16GB of data files as large objects. This happens nightly. But if we go and run the whole drop/restore and migration manually, the problem doesn't manifest itself right after migration is successfully finished. Our next thought was that it must be dropdb part of the nightly script that removes the pg_largeobject's files (still we don't know what makes it keep them opened,) but dropping the DB doesn't manifest the problem either. I'm currently running a VACUUM pg_largeobject on the problematic DB, to see if it triggers the problem, but this didn't happen so far. At this point it would be nice to hear what are your thoughts. What could cause such behavior? -- Regards, Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
