Thread: pg_service.conf ?
Folks, It doesn't appear that the JDBC driver knows about pg_service.conf. If that's so, would it be a big challenge to add that feature? Thanks in advance for any hints, tips, pointers, &c. :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 415 235 3778 Remember to vote!
david@fetter.org (David Fetter) wrote: > Folks, > > It doesn't appear that the JDBC driver knows about pg_service.conf. > If that's so, would it be a big challenge to add that feature? > > Thanks in advance for any hints, tips, pointers, &c. :) Yeah, that would be a pretty slick thing. We're starting to look at this; using it initially so DBA accounts only need one PG value set to get psql to work. It's *tempting* to use pg_service.conf to control Slony-I. There's a fragility to it in that pg_service.conf has to be kept consistent on all the relevant servers, although that would normally be just one server per site... .pgpass support is under way, right? That's better than pg_service.conf :-). -- "cbbrowne","@","gmail.com" http://linuxfinances.info/info/finances.html "It don't mean a thing, if it ain't got that swing..."
David, Can you give us the use case? How would you expect this to work ? Dave On 13-Mar-06, at 10:23 PM, David Fetter wrote: > Folks, > > It doesn't appear that the JDBC driver knows about pg_service.conf. > If that's so, would it be a big challenge to add that feature? > > Thanks in advance for any hints, tips, pointers, &c. :) > > Cheers, > D > -- > David Fetter david@fetter.org http://fetter.org/ > phone: +1 415 235 3778 > > Remember to vote! > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
On Mon, Mar 13, 2006 at 11:11:43PM -0500, Dave Cramer wrote: > David, > > Can you give us the use case? How would you expect this to work ? Let's imagine you have a pg_service.conf that says: [foo] host=foo.example.com dbname=foo port=5555 user=dcramer [bar] host=bar.example.com dbname=bar port=5554 user=dfetter [baz] host=baz.sample.com dbname=baz port=5553 user=brass_monkey You'd use a connect string that looked something like jdbc:postgresql://service=foo/ to get to the db called foo running foo.example.com on port 5555 as ROLE (aka user) dcramer. The pg_service.conf could then be distributed around and available to every kind of application--even ones not written in java. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 415 235 3778 Remember to vote!
On Mon, Mar 13, 2006 at 10:57:03PM -0500, Christopher Browne wrote: > david@fetter.org (David Fetter) wrote: > > Folks, > > > > It doesn't appear that the JDBC driver knows about > > pg_service.conf. If that's so, would it be a big challenge to add > > that feature? > > > > Thanks in advance for any hints, tips, pointers, &c. :) > > Yeah, that would be a pretty slick thing. > > We're starting to look at this; using it initially so DBA accounts > only need one PG value set to get psql to work. That would be a Very Good Thing(TM) > It's *tempting* to use pg_service.conf to control Slony-I. There's > a fragility to it in that pg_service.conf has to be kept consistent > on all the relevant servers, although that would normally be just > one server per site... That's true. > .pgpass support is under way, right? That's better than > pg_service.conf :-). They shouldn't be mutually exclusive :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 415 235 3778 Remember to vote!
David. OK, so as I understand it the client would be responsible for finding, parsing this file and reading the appropriate section. Assuming the PGSYSCONFDIR environment variable is setup properly and the driver has access to the file this would work. This would be a lot easier if the server managed this file. Dave On 14-Mar-06, at 12:30 AM, David Fetter wrote: > On Mon, Mar 13, 2006 at 11:11:43PM -0500, Dave Cramer wrote: >> David, >> >> Can you give us the use case? How would you expect this to work ? > > Let's imagine you have a pg_service.conf that says: > > [foo] > host=foo.example.com > dbname=foo > port=5555 > user=dcramer > > [bar] > host=bar.example.com > dbname=bar > port=5554 > user=dfetter > > [baz] > host=baz.sample.com > dbname=baz > port=5553 > user=brass_monkey > > You'd use a connect string that looked something like > > jdbc:postgresql://service=foo/ > > to get to the db called foo running foo.example.com on port 5555 as > ROLE (aka user) dcramer. The pg_service.conf could then be > distributed around and available to every kind of application--even > ones not written in java. > > Cheers, > D > -- > David Fetter david@fetter.org http://fetter.org/ > phone: +1 415 235 3778 > > Remember to vote! >
In the last exciting episode, pg@fastcrypt.com (Dave Cramer) wrote: > David. > > OK, so as I understand it the client would be responsible for > finding, parsing this file and reading the appropriate section. > > Assuming the PGSYSCONFDIR environment variable is setup properly and > the driver has access to the file this would work. > > This would be a lot easier if the server managed this file. Unfortunately, that represents a recursive need. pg_service.conf is used to determine where the server might be, so you need to read it before you can know where the server is... Note that libpq takes the perspective that pg_service.conf should live in the ../etc directory for the PostgreSQL build. Thus, if psql is living as /opt/dbs/pgsql825/bin/psql, then this would be in /opt/dbs/pgsql825/etc/pg_service.conf. -- let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; http://linuxdatabases.info/info/rdbms.html linux: the choice of a GNU generation (ksh@cis.ufl.edu put this on Tshirts in '93)
Ever wanted to retract an email after you sent it ? I realized the complete stupidity of that statement 30 seconds after I hit send Dave On 14-Mar-06, at 7:51 AM, Christopher Browne wrote: > In the last exciting episode, pg@fastcrypt.com (Dave Cramer) wrote: >> David. >> >> OK, so as I understand it the client would be responsible for >> finding, parsing this file and reading the appropriate section. >> >> Assuming the PGSYSCONFDIR environment variable is setup properly and >> the driver has access to the file this would work. >> >> This would be a lot easier if the server managed this file. > > Unfortunately, that represents a recursive need. pg_service.conf is > used to determine where the server might be, so you need to read it > before you can know where the server is... > > Note that libpq takes the perspective that pg_service.conf should live > in the ../etc directory for the PostgreSQL build. Thus, if psql is > living as /opt/dbs/pgsql825/bin/psql, then this would be in > /opt/dbs/pgsql825/etc/pg_service.conf. > -- > let name="cbbrowne" and tld="cbbrowne.com" in String.concat > "@" [name;tld];; > http://linuxdatabases.info/info/rdbms.html > linux: the choice of a GNU generation > (ksh@cis.ufl.edu put this on Tshirts in '93) > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly >
Christopher Browne wrote: > Note that libpq takes the perspective that pg_service.conf should live > in the ../etc directory for the PostgreSQL build. Thus, if psql is > living as /opt/dbs/pgsql825/bin/psql, then this would be in > /opt/dbs/pgsql825/etc/pg_service.conf. The big thing here is "how do we find the config file?". The JDBC driver has no concept of an "installation" .. it's just classes that the JVM manages to load somehow. -O
On Tue, Mar 14, 2006 at 08:42:26AM -0500, Dave Cramer wrote: > Ever wanted to retract an email after you sent it? On a good day, I only want to do this for 1/10 emails ;) Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
In an attempt to throw the authorities off his trail, pg@fastcrypt.com (Dave Cramer) transmitted:
> Ever wanted to retract an email after you sent it ? I realized the
> complete stupidity of that statement 30 seconds after I hit send
Well, this is a common enough problem that there are occasions where
people build server-based mechanisms for it...
Oracle has something whose name I misremember where you head to a
server to get the *real* server configuration.
CORBA had this notion with the Naming Service; you start by getting a
connection to a Naming Service object, and then ask where the *real*
services are...
How to do it "right" for JDBC is a good question. Searching for
.pgpass in $HOME is pretty obvious. It is less obvious where to
search for pg_service.conf if all that is installed is JDBC, and there
is no PostgreSQL instance around.
--
output = reverse("gro.mca" "@" "enworbbc")
http://linuxdatabases.info/info/internet.html
What do you mean "Why's it got to be built?" It is a bypass. You've got
to build bypasses.
According to the 8.1 documentation, libpq looks in $PGSYSCONFDIR. As a user
it would make sense to me if JDBC looked at the same environment variable.
Woody
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Christopher Browne
Sent: Tuesday, March 14, 2006 11:56 AM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] pg_service.conf ?
In an attempt to throw the authorities off his trail, pg@fastcrypt.com (Dave
Cramer) transmitted:
> Ever wanted to retract an email after you sent it ? I realized the
> complete stupidity of that statement 30 seconds after I hit send
Well, this is a common enough problem that there are occasions where people
build server-based mechanisms for it...
Oracle has something whose name I misremember where you head to a server to
get the *real* server configuration.
CORBA had this notion with the Naming Service; you start by getting a
connection to a Naming Service object, and then ask where the *real*
services are...
How to do it "right" for JDBC is a good question. Searching for .pgpass in
$HOME is pretty obvious. It is less obvious where to search for
pg_service.conf if all that is installed is JDBC, and there is no PostgreSQL
instance around.
--
output = reverse("gro.mca" "@" "enworbbc")
http://linuxdatabases.info/info/internet.html
What do you mean "Why's it got to be built?" It is a bypass. You've got to
build bypasses.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On 14-Mar-06, at 11:55 AM, Christopher Browne wrote:
> In an attempt to throw the authorities off his trail,
> pg@fastcrypt.com (Dave Cramer) transmitted:
>> Ever wanted to retract an email after you sent it ? I realized the
>> complete stupidity of that statement 30 seconds after I hit send
>
> Well, this is a common enough problem that there are occasions where
> people build server-based mechanisms for it...
>
> Oracle has something whose name I misremember where you head to a
> server to get the *real* server configuration.
>
> CORBA had this notion with the Naming Service; you start by getting a
> connection to a Naming Service object, and then ask where the *real*
> services are...
>
> How to do it "right" for JDBC is a good question. Searching for
> .pgpass in $HOME is pretty obvious. It is less obvious where to
> search for pg_service.conf if all that is installed is JDBC, and there
> is no PostgreSQL instance around.
This is useful if you have a $HOME dir, but in the case of a web
application this isn't guaranteed
> --
> output = reverse("gro.mca" "@" "enworbbc")
> http://linuxdatabases.info/info/internet.html
> What do you mean "Why's it got to be built?" It is a bypass.
> You've got
> to build bypasses.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
On 14-Mar-06, at 10:52 AM, Oliver Jowett wrote: > Christopher Browne wrote: > >> Note that libpq takes the perspective that pg_service.conf should >> live >> in the ../etc directory for the PostgreSQL build. Thus, if psql is >> living as /opt/dbs/pgsql825/bin/psql, then this would be in >> /opt/dbs/pgsql825/etc/pg_service.conf. > > The big thing here is "how do we find the config file?". The JDBC > driver has no concept of an "installation" .. it's just classes > that the JVM manages to load somehow. This is the biggest concern. In a case where a user runs a jdbc client manually from the command line this is straight forward; however it is quite problematic for drivers running in something like a web application, or some other "container" Practically speaking I don't have an issue implementing this, however I can see this being a challenge managing the support for it. There appears to be some ambiguity as to where the file is kept. > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
Hi, Oliver,
Oliver Jowett wrote:
>> Note that libpq takes the perspective that pg_service.conf should live
>> in the ../etc directory for the PostgreSQL build. Thus, if psql is
>> living as /opt/dbs/pgsql825/bin/psql, then this would be in
>> /opt/dbs/pgsql825/etc/pg_service.conf.
>
> The big thing here is "how do we find the config file?". The JDBC driver
> has no concept of an "installation" .. it's just classes that the JVM
> manages to load somehow.
I could imagine a specific search order in a defined subset of:
- ClassLoader.GetResource('pg_service.conf')
- System.getEnv("PG_JDBC_SERVICE_CONF")
- System.getEnv("HOME") + File.separator + ".pgpass"
- System.getEnv("PGSYSCONFDIR") + File.separator + "pg_service.conf"
- System.getProperty('pg_service.conf') // contains the path of the file
- new InitialContext().lookup('pg_service.conf')
- A driver URL parameter specifying the location of the file
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
On Friday 17 March 2006 11:26, Markus Schaber wrote:
> - System.getEnv("PG_JDBC_SERVICE_CONF")
> - System.getEnv("HOME") + File.separator + ".pgpass"
> - System.getEnv("PGSYSCONFDIR") + File.separator + "pg_service.conf"
Which would mean tying yourself to JDK1.5
jan
--
--------------------------------------------------------------
Jan de Visser jdevisser@digitalfairway.com
Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------
On Fri, Mar 17, 2006 at 11:52:23AM -0500, Jan de Visser wrote:
> On Friday 17 March 2006 11:26, Markus Schaber wrote:
> > - System.getEnv("PG_JDBC_SERVICE_CONF")
> > - System.getEnv("HOME") + File.separator + ".pgpass"
> > - System.getEnv("PGSYSCONFDIR") + File.separator + "pg_service.conf"
>
> Which would mean tying yourself to JDK1.5
What things wouldn't tie to JDK 1.5?
This brings up some interesting questions:
1. What versions of JDK does the PostgreSQL JDBC project support now?
2. Is there a policy about how many versions back the project will
continue to support?
3. If so, what is it? If not, what should it be? Support back to
more than about three versions of anything is not even an option
without a large and stable budget, and even then, there are
limits.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
On Tue, 21 Mar 2006, David Fetter wrote: > 1. What versions of JDK does the PostgreSQL JDBC project support now? 1.2 - 1.5 > 2. Is there a policy about how many versions back the project will > continue to support? Not really. We'll continue to support everything we can until it becomes a real burden. We phased out 1.1 support when we needed 1.2 features. I wouldn't say we need 1.5 features now. > Support back to more than about three versions of anything is not even > an option without a large and stable budget, and even then, there are > limits. Not really, Java really does maintain backwards compatibility. It just means we can't use new features everywhere. There is some conditional compilation and different directories for different JDK versions, but that doesn't really affect the core of the driver. Kris Jurka
Well, as one of the people who would be left out in the cold if newer
versions of the driver required JDK 1.5, perhaps the new 1.5
functionality could be leveraged if available, using something like the
following:
String getEnv(String name) {
try {
Method m;
m = System.class.getMethod("getEnv", new Class[]{String.class});
return (String)m.invoke(null, new Object[]{name});
}
catch(Exception ex) {
// Must not be running 1.5 or later
return null;
}
}
So looking in PG_JDBC_SERVICE_CONF and PGSYSCONFDIR would require 1.5,
although looking in the user's home dir is still possible with older
versions because you can inspect the user.dir System property.
-- Mark Lewis
On Tue, 2006-03-21 at 10:56 -0800, David Fetter wrote:
> On Fri, Mar 17, 2006 at 11:52:23AM -0500, Jan de Visser wrote:
> > On Friday 17 March 2006 11:26, Markus Schaber wrote:
> > > - System.getEnv("PG_JDBC_SERVICE_CONF")
> > > - System.getEnv("HOME") + File.separator + ".pgpass"
> > > - System.getEnv("PGSYSCONFDIR") + File.separator + "pg_service.conf"
> >
> > Which would mean tying yourself to JDK1.5
>
> What things wouldn't tie to JDK 1.5?
>
> This brings up some interesting questions:
>
> 1. What versions of JDK does the PostgreSQL JDBC project support now?
> 2. Is there a policy about how many versions back the project will
> continue to support?
> 3. If so, what is it? If not, what should it be? Support back to
> more than about three versions of anything is not even an option
> without a large and stable budget, and even then, there are
> limits.
>
> Cheers,
> D
On Tue, Mar 21, 2006 at 02:24:04PM -0500, Kris Jurka wrote: > On Tue, 21 Mar 2006, David Fetter wrote: > >1. What versions of JDK does the PostgreSQL JDBC project support now? > 1.2 - 1.5 > > >2. Is there a policy about how many versions back the project will > > continue to support? > > Not really. We'll continue to support everything we can until it becomes > a real burden. We phased out 1.1 support when we needed 1.2 features. I > wouldn't say we need 1.5 features now. Fantastic :) Circling back to the topic at hand, how can the JDBC driver be told to understand things like ~/.pgpass and pg_service.conf ? Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
One thought I've had is to make a proxy driver which has additional functionality. Essentially this driver would wrap the existing driver and leave the existing driver alone. Additional functionality might include: 1) Better Connection Pooling 2) Statement Caching 3) ~/.pgpass and pg_service.conf Thoughts ? Dave On 21-Mar-06, at 2:47 PM, David Fetter wrote: > On Tue, Mar 21, 2006 at 02:24:04PM -0500, Kris Jurka wrote: >> On Tue, 21 Mar 2006, David Fetter wrote: >>> 1. What versions of JDK does the PostgreSQL JDBC project support >>> now? >> 1.2 - 1.5 >> >>> 2. Is there a policy about how many versions back the project will >>> continue to support? >> >> Not really. We'll continue to support everything we can until it >> becomes >> a real burden. We phased out 1.1 support when we needed 1.2 >> features. I >> wouldn't say we need 1.5 features now. > > Fantastic :) > > Circling back to the topic at hand, how can the JDBC driver be told to > understand things like ~/.pgpass and pg_service.conf ? > > Cheers, > D > -- > David Fetter <david@fetter.org> http://fetter.org/ > phone: +1 415 235 3778 AIM: dfetter666 > Skype: davidfetter > > Remember to vote! > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
On Tue, Mar 21, 2006 at 03:27:10PM -0500, Dave Cramer wrote: > One thought I've had is to make a proxy driver which has additional > functionality. > > Essentially this driver would wrap the existing driver and leave the > existing driver alone. Would this be in interim solution, or...? > Additional functionality might include: > > 1) Better Connection Pooling > 2) Statement Caching > 3) ~/.pgpass and pg_service.conf > > Thoughts ? My first thought is, "would this make it happen sooner?" My second thought is, "will people rue the day we decided on a wrapper rather than an integrated solution?" Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
Hi, Mark,
Mark Lewis wrote:
> String getEnv(String name) {
> try {
> Method m;
> m = System.class.getMethod("getEnv", new Class[]{String.class});
> return (String)m.invoke(null, new Object[]{name});
> }
> catch(Exception ex) {
> // Must not be running 1.5 or later
> return null;
> }
> }
I don't think you need reflection there, because the method was present
since very old JDK libs, but deprecated (and thus throwing an exception)
in 1.3 and 1.4.
> So looking in PG_JDBC_SERVICE_CONF and PGSYSCONFDIR would require 1.5,
> although looking in the user's home dir is still possible with older
> versions because you can inspect the user.dir System property.
Ah, I knew there as a possibility to get the users home directory.
user.dir should work even under those Windows environments that really
support multiple user accounts.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org