============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name		: Michael Fuhr
Your email address	: mfuhr@fuhr.org


System Configuration
---------------------
  Architecture (example: Intel Pentium)  	: several

  Operating System (example: Linux 2.0.26 ELF) 	: several

  PostgreSQL version (example: PostgreSQL-7.4):   PostgreSQL-7.4

  Compiler used (example:  gcc 2.95.2)		: several


Please enter a FULL description of your problem:
------------------------------------------------

When a client connects to the database server using a service name,
the dbname parameter in pg_service.conf is ignored.  In the absence
of an explicitly-named database in the connection string, the service
name is used as the database name regardless of that service's
dbname setting.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible: 
----------------------------------------------------------------------

1. Create/edit pg_service.conf in whatever directory it's supposed
to be in on your system (location varies).  Add the following (change
the database and user names as appropriate for your system):

[foobar]
dbname=template1
user=postgres

2. Connect to the database server using the "foobar" service.  The
following example should work on most Unix-like systems (you may
or may not be prompted for a password, depending on your configuration):

% env PGSERVICE=foobar psql
Password: ********
psql: FATAL:  database "foobar" does not exist


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

The problem appears to be in the parseServiceInfo() function in
src/interfaces/libpq/fe-connect.c.  Here's an excerpt from that
function:

        /*
         * If not already set, set the database name to the
         * name of the service
         */
        for (i = 0; options[i].keyword; i++)
        {
            if (strcmp(options[i].keyword, "dbname") == 0)
            {
                if (options[i].val == NULL)
                    options[i].val = strdup(service);
                break;
            }
        }

        /*
         * Set the parameter --- but don't override any
         * previous explicit setting.
         */
        found_keyword = 0;
        for (i = 0; options[i].keyword; i++)
        {
            if (strcmp(options[i].keyword, key) == 0)
            {
                if (options[i].val == NULL)
                    options[i].val = strdup(val);
                found_keyword = 1;
                break;
            }
        }

Since the database name is already set to be either the service
name or an explicitly named database from the connection string,
the "Set the parameter" section of code skips the dbname parameter.
I haven't yet examined the rest of the code closely enough to come
up with the correct patch, but it seems that the "set the database
name to the name of the service" code should be deferred until
after all of the service's parameters have been read.

-- 
Michael Fuhr

Mon, 15 Dec 2003 20:59:11 -0700
Michael Fuhr <mfuhr@fuhr.org>
pg_service.conf ignores dbname parameter
Michael Fuhr <mfuhr@fuhr.org> writes:
> When a client connects to the database server using a service name,
> the dbname parameter in pg_service.conf is ignored.  In the absence
> of an explicitly-named database in the connection string, the service
> name is used as the database name regardless of that service's
> dbname setting.
> [snip]
> I haven't yet examined the rest of the code closely enough to come
> up with the correct patch, but it seems that the "set the database
> name to the name of the service" code should be deferred until
> after all of the service's parameters have been read.

Hm.  I'm of the opinion that the real problem here is the code's
assumption that it is reasonable to force dbname = servicename when
the service file doesn't say any such thing.  For all other parameters,
omitting the parameter from pg_service.conf causes the standard default
to be adopted.  Why should dbname work differently?  It saves only a
minimal amount of typing to do it this way, and it might prevent some
useful setups (namely, a service that specifies connection params but
allows the usual default of dbname = username to apply).

Since pg_service was completely undocumented before 7.4, I think it is
safe to say that its usage in the field is nil except for the original
author, and therefore "backwards compatibility" is not really a relevant
argument just yet.  We ought to concentrate on "principle of least
astonishment" instead.

Accordingly, I'd rather just delete the offending code instead of move
it.  (BTW, I notice Bruce has fooled with this code before, so it's
already in the "known source of problems" category.)

Comments anyone?

			regards, tom lane

Wed, 17 Dec 2003 12:39:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_service.conf ignores dbname parameter 
Tom Lane wrote:
> Michael Fuhr <mfuhr@fuhr.org> writes:
> > When a client connects to the database server using a service name,
> > the dbname parameter in pg_service.conf is ignored.  In the absence
> > of an explicitly-named database in the connection string, the service
> > name is used as the database name regardless of that service's
> > dbname setting.
> > [snip]
> > I haven't yet examined the rest of the code closely enough to come
> > up with the correct patch, but it seems that the "set the database
> > name to the name of the service" code should be deferred until
> > after all of the service's parameters have been read.
> 
> Hm.  I'm of the opinion that the real problem here is the code's
> assumption that it is reasonable to force dbname = servicename when
> the service file doesn't say any such thing.  For all other parameters,
> omitting the parameter from pg_service.conf causes the standard default
> to be adopted.  Why should dbname work differently?  It saves only a
> minimal amount of typing to do it this way, and it might prevent some
> useful setups (namely, a service that specifies connection params but
> allows the usual default of dbname = username to apply).
> 
> Since pg_service was completely undocumented before 7.4, I think it is
> safe to say that its usage in the field is nil except for the original
> author, and therefore "backwards compatibility" is not really a relevant
> argument just yet.  We ought to concentrate on "principle of least
> astonishment" instead.
> 
> Accordingly, I'd rather just delete the offending code instead of move
> it.  (BTW, I notice Bruce has fooled with this code before, so it's
> already in the "known source of problems" category.)

Agreed.  Just make them supply the dbname in the service file.  I can
make the changes and update the documentation in pg_service.conf:

# included in this file.  If no database name is specified, it is assumed 
# to match the service name.  Lines beginning with '#' are comments.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 17 Dec 2003 12:51:24 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] pg_service.conf ignores dbname parameter
Tom Lane wrote:
> Hm.  I'm of the opinion that the real problem here is the code's
> assumption that it is reasonable to force dbname = servicename when
> the service file doesn't say any such thing.  For all other
> parameters, omitting the parameter from pg_service.conf causes the
> standard default to be adopted.  Why should dbname work differently? 

Regardless of this particular issue, I think it would be useful if, 
under some conditions to be identified, some service were taken as 
default if nothing is specied in libpq.  That would eliminate the need 
to set environment variables, which is undesirable in many situations.


Thu, 18 Dec 2003 18:32:25 +0100
Peter Eisentraut <peter_e@gmx.net>
Re: [BUGS] pg_service.conf ignores dbname parameter
Peter Eisentraut <peter_e@gmx.net> writes:
> Regardless of this particular issue, I think it would be useful if, 
> under some conditions to be identified, some service were taken as 
> default if nothing is specied in libpq.  That would eliminate the need 
> to set environment variables, which is undesirable in many situations.

That's a thought.  Maybe if pg_service.conf exists and contains a
section named "default", we use whatever settings are present there?
(Obviously we'd not want the dbname to be forced by this, but I think
we've already agreed to get rid of that behavior.)

About the only downside I can see to this is that every connection
would incur the overhead of an attempted file opening.  That might be
thought to be too much overhead, at least by people who have no use
for the feature.  But in comparison to what will happen on the server
side during backend startup, it's probably pretty negligible.

BTW, why is it that pg_service.conf is system-wide?  Personally I'd
think it more useful to seek settings in ~/.pg_service.conf.

			regards, tom lane

Thu, 18 Dec 2003 12:44:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [BUGS] pg_service.conf ignores dbname parameter 
I have applied the attached patch to remove the default
dbname=serive-name code, changed the comment at the top of the file, and
changed a few ints to booleans.

---------------------------------------------------------------------------

Michael Fuhr wrote:
> ============================================================================
>                         POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
> 
> Your name		: Michael Fuhr
> Your email address	: mfuhr@fuhr.org
> 
> 
> System Configuration
> ---------------------
>   Architecture (example: Intel Pentium)  	: several
> 
>   Operating System (example: Linux 2.0.26 ELF) 	: several
> 
>   PostgreSQL version (example: PostgreSQL-7.4):   PostgreSQL-7.4
> 
>   Compiler used (example:  gcc 2.95.2)		: several
> 
> 
> Please enter a FULL description of your problem:
> ------------------------------------------------
> 
> When a client connects to the database server using a service name,
> the dbname parameter in pg_service.conf is ignored.  In the absence
> of an explicitly-named database in the connection string, the service
> name is used as the database name regardless of that service's
> dbname setting.
> 
> 
> Please describe a way to repeat the problem.   Please try to provide a
> concise reproducible example, if at all possible: 
> ----------------------------------------------------------------------
> 
> 1. Create/edit pg_service.conf in whatever directory it's supposed
> to be in on your system (location varies).  Add the following (change
> the database and user names as appropriate for your system):
> 
> [foobar]
> dbname=template1
> user=postgres
> 
> 2. Connect to the database server using the "foobar" service.  The
> following example should work on most Unix-like systems (you may
> or may not be prompted for a password, depending on your configuration):
> 
> % env PGSERVICE=foobar psql
> Password: ********
> psql: FATAL:  database "foobar" does not exist
> 
> 
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
> 
> The problem appears to be in the parseServiceInfo() function in
> src/interfaces/libpq/fe-connect.c.  Here's an excerpt from that
> function:
> 
>         /*
>          * If not already set, set the database name to the
>          * name of the service
>          */
>         for (i = 0; options[i].keyword; i++)
>         {
>             if (strcmp(options[i].keyword, "dbname") == 0)
>             {
>                 if (options[i].val == NULL)
>                     options[i].val = strdup(service);
>                 break;
>             }
>         }
> 
>         /*
>          * Set the parameter --- but don't override any
>          * previous explicit setting.
>          */
>         found_keyword = 0;
>         for (i = 0; options[i].keyword; i++)
>         {
>             if (strcmp(options[i].keyword, key) == 0)
>             {
>                 if (options[i].val == NULL)
>                     options[i].val = strdup(val);
>                 found_keyword = 1;
>                 break;
>             }
>         }
> 
> Since the database name is already set to be either the service
> name or an explicitly named database from the connection string,
> the "Set the parameter" section of code skips the dbname parameter.
> I haven't yet examined the rest of the code closely enough to come
> up with the correct patch, but it seems that the "set the database
> name to the name of the service" code should be deferred until
> after all of the service's parameters have been read.
> 
> -- 
> Michael Fuhr
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Index: src/interfaces/libpq/fe-connect.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.264
diff -c -c -r1.264 fe-connect.c
*** src/interfaces/libpq/fe-connect.c	29 Nov 2003 19:52:11 -0000	1.264
--- src/interfaces/libpq/fe-connect.c	19 Dec 2003 21:18:13 -0000
***************
*** 2367,2373 ****
  {
  	char	   *service = conninfo_getval(options, "service");
  	char	   *serviceFile = SYSCONFDIR "/pg_service.conf";
! 	int			group_found = 0;
  	int			linenr = 0,
  				i;
  
--- 2367,2373 ----
  {
  	char	   *service = conninfo_getval(options, "service");
  	char	   *serviceFile = SYSCONFDIR "/pg_service.conf";
! 	bool		group_found = false;
  	int			linenr = 0,
  				i;
  
***************
*** 2431,2439 ****
  
  				if (strncmp(line + 1, service, strlen(service)) == 0 &&
  					line[strlen(service) + 1] == ']')
! 					group_found = 1;
  				else
! 					group_found = 0;
  			}
  			else
  			{
--- 2431,2439 ----
  
  				if (strncmp(line + 1, service, strlen(service)) == 0 &&
  					line[strlen(service) + 1] == ']')
! 					group_found = true;
  				else
! 					group_found = false;
  			}
  			else
  			{
***************
*** 2445,2451 ****
  					 */
  					char	   *key,
  							   *val;
! 					int			found_keyword;
  
  					key = line;
  					val = strchr(line, '=');
--- 2445,2451 ----
  					 */
  					char	   *key,
  							   *val;
! 					bool		found_keyword;
  
  					key = line;
  					val = strchr(line, '=');
***************
*** 2461,2491 ****
  					*val++ = '\0';
  
  					/*
- 					 * If not already set, set the database name to the
- 					 * name of the service
- 					 */
- 					for (i = 0; options[i].keyword; i++)
- 					{
- 						if (strcmp(options[i].keyword, "dbname") == 0)
- 						{
- 							if (options[i].val == NULL)
- 								options[i].val = strdup(service);
- 							break;
- 						}
- 					}
- 
- 					/*
  					 * Set the parameter --- but don't override any
  					 * previous explicit setting.
  					 */
! 					found_keyword = 0;
  					for (i = 0; options[i].keyword; i++)
  					{
  						if (strcmp(options[i].keyword, key) == 0)
  						{
  							if (options[i].val == NULL)
  								options[i].val = strdup(val);
! 							found_keyword = 1;
  							break;
  						}
  					}
--- 2461,2477 ----
  					*val++ = '\0';
  
  					/*
  					 * Set the parameter --- but don't override any
  					 * previous explicit setting.
  					 */
! 					found_keyword = false;
  					for (i = 0; options[i].keyword; i++)
  					{
  						if (strcmp(options[i].keyword, key) == 0)
  						{
  							if (options[i].val == NULL)
  								options[i].val = strdup(val);
! 							found_keyword = true;
  							break;
  						}
  					}
Index: src/interfaces/libpq/pg_service.conf.sample
===================================================================
RCS file: /cvsroot/pgsql-server/src/interfaces/libpq/pg_service.conf.sample,v
retrieving revision 1.1
diff -c -c -r1.1 pg_service.conf.sample
*** src/interfaces/libpq/pg_service.conf.sample	3 Feb 2003 14:24:07 -0000	1.1
--- src/interfaces/libpq/pg_service.conf.sample	19 Dec 2003 21:18:13 -0000
***************
*** 5,16 ****
  # multiple services in this file.  Each starts with a service name in  
  # brackets.  Subsequent lines have connection configuration parameters of
  # the pattern  "param=value".  A sample configuration for template1 is 
! # included in this file.  If no database name is specified, it is assumed 
! # to match the service name.  Lines beginning with '#' are comments.
  #
  # Copy this to your sysconf directory (typically /usr/local/pgsql/etc) and
  # rename it pg_service.conf.
  #
  #
  #[template1]
  #user=postgres
--- 5,16 ----
  # multiple services in this file.  Each starts with a service name in  
  # brackets.  Subsequent lines have connection configuration parameters of
  # the pattern  "param=value".  A sample configuration for template1 is 
! # included in this file.  Lines beginning with '#' are comments.
  #
  # Copy this to your sysconf directory (typically /usr/local/pgsql/etc) and
  # rename it pg_service.conf.
  #
  #
  #[template1]
+ #dbname=template1
  #user=postgres
Fri, 19 Dec 2003 16:50:31 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_service.conf ignores dbname parameter
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Regardless of this particular issue, I think it would be useful if, 
> > under some conditions to be identified, some service were taken as 
> > default if nothing is specied in libpq.  That would eliminate the need 
> > to set environment variables, which is undesirable in many situations.
> 
> That's a thought.  Maybe if pg_service.conf exists and contains a
> section named "default", we use whatever settings are present there?
> (Obviously we'd not want the dbname to be forced by this, but I think
> we've already agreed to get rid of that behavior.)
> 
> About the only downside I can see to this is that every connection
> would incur the overhead of an attempted file opening.  That might be
> thought to be too much overhead, at least by people who have no use
> for the feature.  But in comparison to what will happen on the server
> side during backend startup, it's probably pretty negligible.
> 
> BTW, why is it that pg_service.conf is system-wide?  Personally I'd
> think it more useful to seek settings in ~/.pg_service.conf.

Perhaps the solution is to allow an environment variable to point to the
services file.  That way, you only look for the file if that variable
exists.  This would also have to be defined for any service file usage,
so maybe this is bad.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 19 Dec 2003 16:52:27 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] pg_service.conf ignores dbname parameter
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> BTW, why is it that pg_service.conf is system-wide?  Personally I'd
>> think it more useful to seek settings in ~/.pg_service.conf.

> Perhaps the solution is to allow an environment variable to point to the
> services file.

Peter was after a no-environment-variable solution, so I don't think
he'll like that one.

			regards, tom lane

Fri, 19 Dec 2003 18:11:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [BUGS] pg_service.conf ignores dbname parameter 
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have applied the attached patch to remove the default
> dbname=serive-name code, changed the comment at the top of the file, and
> changed a few ints to booleans.

Looks good, but you should apply to 7.4 branch as well.  The argument
that we can change this without a backwards-compatibility penalty falls
down if the change does not appear until 7.5.

			regards, tom lane

Fri, 19 Dec 2003 18:24:21 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_service.conf ignores dbname parameter 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> BTW, why is it that pg_service.conf is system-wide?  Personally I'd
> >> think it more useful to seek settings in ~/.pg_service.conf.
> 
> > Perhaps the solution is to allow an environment variable to point to the
> > services file.
> 
> Peter was after a no-environment-variable solution, so I don't think
> he'll like that one.

I thought he was more concerned about removing envirnment variables that
have to be tuned for each user.  Let's see how he responds.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 19 Dec 2003 18:30:26 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] pg_service.conf ignores dbname parameter
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have applied the attached patch to remove the default
> > dbname=serive-name code, changed the comment at the top of the file, and
> > changed a few ints to booleans.
> 
> Looks good, but you should apply to 7.4 branch as well.  The argument
> that we can change this without a backwards-compatibility penalty falls
> down if the change does not appear until 7.5.

Done.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 19 Dec 2003 18:32:31 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_service.conf ignores dbname parameter
Bruce Momjian wrote:
> I thought he was more concerned about removing envirnment variables
> that have to be tuned for each user.  Let's see how he responds.

Think about a web server talking to a database server.  Where do you set 
the environment variables for that?  Or where is the "home directory" 
for that?  And then remember issues of "su" vs. "su -".  Environment 
variables are error prone when they contain information that is 
essential for running the program correctly.


Sat, 20 Dec 2003 09:05:36 +0100
Peter Eisentraut <peter_e@gmx.net>
Re: [BUGS] pg_service.conf ignores dbname parameter