I have another question for you.

Is it a known bug [in PostgreSQL 7.1.2] in the createlang command that it
asks for my password 4 times.  If I type any of them wrong, it partially
creates the language [plpgsql] for the given database, but fails.

[veldy@fuggle veldy]$ createlang plpgsql homebrew
Password:
Password:
Password:
Password:
[veldy@fuggle veldy]$

Why does it ask 4 times?

Tom Veldhouse
veldy@veldy.net


Tue, 26 Jun 2001 20:27:03 -0500
"Thomas T. Veldhouse" <veldy@veldy.net>
Bug in createlang?
"Thomas T. Veldhouse" wrote:
> 
> Is it a known bug [in PostgreSQL 7.1.2] in the createlang command that it
> asks for my password 4 times.  If I type any of them wrong, it partially
> creates the language [plpgsql] for the given database, but fails.
 
> Why does it ask 4 times?

createlang is just a script - it basically runs "/path/to/psql $QUERY" -
each query connects a separate time.

- Richard Huxton

Wed, 27 Jun 2001 08:00:01 +0100
Richard Huxton <dev@archonet.com>
Re: Bug in createlang?
Richard Huxton <dev@archonet.com> writes:
> "Thomas T. Veldhouse" wrote:
>> Why does it ask 4 times?

> createlang is just a script - it basically runs "/path/to/psql $QUERY" -
> each query connects a separate time.

Note that running a setup that requires password auth for the DBA will
also be a major pain in the rear when running pg_dumpall: one password
prompt per database, IIRC.  We have other scripts that make more than
one database connection, too.

I'd counsel using a setup that avoids passwords for local connections.
One way to do this is to run an ident daemon and use IDENT authorization
for connections from 127.0.0.1.  This allows "psql -h localhost" to work
without a password.  (IDENT authorization is quite properly discouraged
for remote connections, but it's trustworthy enough on your own machine,
if you control the ident daemon or trust the person who does.)

			regards, tom lane

Wed, 27 Jun 2001 10:24:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in createlang? 
Awesome.  That is what I am looking for.  I have been having a problem
restoring a database without changing the security options and restarting
the server.  Real hassle.  This could be what I am looking for.  phpPgAdmin
is running on the same machine, should I just tell it to use the "public"
address instead of localhost so that authentication is still required for it
(without trying to use ident)?

Thanks,

Tom Veldhouse
veldy@veldy.net

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Richard Huxton" <dev@archonet.com>
Cc: "Thomas T. Veldhouse" <veldy@veldy.net>; <pgsql-general@postgresql.org>
Sent: Wednesday, June 27, 2001 9:24 AM
Subject: Re: [GENERAL] Bug in createlang?


> Richard Huxton <dev@archonet.com> writes:
> > "Thomas T. Veldhouse" wrote:
> >> Why does it ask 4 times?
>
> > createlang is just a script - it basically runs "/path/to/psql $QUERY" -
> > each query connects a separate time.
>
> Note that running a setup that requires password auth for the DBA will
> also be a major pain in the rear when running pg_dumpall: one password
> prompt per database, IIRC.  We have other scripts that make more than
> one database connection, too.
>
> I'd counsel using a setup that avoids passwords for local connections.
> One way to do this is to run an ident daemon and use IDENT authorization
> for connections from 127.0.0.1.  This allows "psql -h localhost" to work
> without a password.  (IDENT authorization is quite properly discouraged
> for remote connections, but it's trustworthy enough on your own machine,
> if you control the ident daemon or trust the person who does.)
>
> regards, tom lane
>


Wed, 27 Jun 2001 09:32:42 -0500
"Thomas T. Veldhouse" <veldy@veldy.net>
Re: Bug in createlang? 
"Thomas T. Veldhouse" <veldy@veldy.net> writes:
> Real hassle.  This could be what I am looking for.  phpPgAdmin
> is running on the same machine, should I just tell it to use the "public"
> address instead of localhost so that authentication is still required for it
> (without trying to use ident)?

Sure, that would work, or use Unix-socket connection that way.  (BTW,
the reason ident doesn't work for Unix sockets is that standard IDENT
daemons only know how to get the info for IP-based connections.  Too
bad...)

			regards, tom lane

Wed, 27 Jun 2001 10:59:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in createlang? 
> Richard Huxton <dev@archonet.com> writes:
> > "Thomas T. Veldhouse" wrote:
> >> Why does it ask 4 times?
> 
> > createlang is just a script - it basically runs "/path/to/psql $QUERY" -
> > each query connects a separate time.
> 
> Note that running a setup that requires password auth for the DBA will
> also be a major pain in the rear when running pg_dumpall: one password
> prompt per database, IIRC.  We have other scripts that make more than
> one database connection, too.

This brings up an issue I am concerned about.  Right now, when we
install the database with initdb, we basically are wide-opened to any
local user who wants to connect to the database as superuser.  In fact,
someone could easily install a function in template1 that bypasses
database security so even after you put a password on the superuser and
others, they could bypass security.

Do people have a good solution for this problem?  Should be be
installing a password for the super-user at initdb time?  I see initdb
has this option:

       --pwprompt

       -W     Makes  initdb prompt for a password of the database
              superuser. If you  don't  plan  on  using  password
              authentication,  this  is not important.  Otherwise
              you won't be able to  use  password  authentication
              until you have a password set up.

Do people know they should be using this initdb option if they don't
trust their local users?  I see no mention of it in the INSTALL file.

I see it does:

# set up password
if [ "$PwPrompt" ]; then
    $ECHO_N "Enter new superuser password: "$ECHO_C
    stty -echo > /dev/null 2>&1
    read FirstPw
    stty echo > /dev/null 2>&1
    echo
    $ECHO_N "Enter it again: "$ECHO_C
    stty -echo > /dev/null 2>&1
    read SecondPw
    stty echo > /dev/null 2>&1
    echo
    if [ "$FirstPw" != "$SecondPw" ]; then
        echo "Passwords didn't match." 1>&2
        exit_nicely
    fi
    echo "ALTER USER \"$POSTGRES_SUPERUSERNAME\" WITH PASSWORD '$FirstPw'" \
        | "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
    if [ ! -f $PGDATA/global/pg_pwd ]; then
        echo "The password file wasn't generated. Please report this problem." 1>&2
        exit_nicely
    fi

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 11 Jul 2001 15:02:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in createlang?
> Richard Huxton <dev@archonet.com> writes:
> > "Thomas T. Veldhouse" wrote:
> >> Why does it ask 4 times?
> 
> > createlang is just a script - it basically runs "/path/to/psql $QUERY" -
> > each query connects a separate time.
> 
> Note that running a setup that requires password auth for the DBA will
> also be a major pain in the rear when running pg_dumpall: one password
> prompt per database, IIRC.  We have other scripts that make more than
> one database connection, too.
> 
> I'd counsel using a setup that avoids passwords for local connections.
> One way to do this is to run an ident daemon and use IDENT authorization
> for connections from 127.0.0.1.  This allows "psql -h localhost" to work
> without a password.  (IDENT authorization is quite properly discouraged
> for remote connections, but it's trustworthy enough on your own machine,
> if you control the ident daemon or trust the person who does.)

I just applied a diff to better document the use of ident for localhost.
I think it is a good idea, and in some ways a better use of ident than
for remote machines.  If I missed a spot that could be better
documented, let me know.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Index: doc/src/sgml/client-auth.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/client-auth.sgml,v
retrieving revision 1.11
diff -c -r1.11 client-auth.sgml
*** doc/src/sgml/client-auth.sgml	2001/05/12 22:51:34	1.11
--- doc/src/sgml/client-auth.sgml	2001/07/11 20:27:07
***************
*** 242,248 ****
            of the connecting user. <productname>Postgres</productname>
            then verifies whether the so identified operating system user
            is allowed to connect as the database user that is requested.
! 	  This is only available for TCP/IP connections.
            The <replaceable>authentication option</replaceable> following
            the <literal>ident</> keyword specifies the name of an
            <firstterm>ident map</firstterm> that specifies which operating
--- 242,251 ----
            of the connecting user. <productname>Postgres</productname>
            then verifies whether the so identified operating system user
            is allowed to connect as the database user that is requested.
! 	  This is only available for TCP/IP connections.  It can be used
! 	  on the local machine by specifying the localhost address 127.0.0.1.
!          </para>
!          <para>
            The <replaceable>authentication option</replaceable> following
            the <literal>ident</> keyword specifies the name of an
            <firstterm>ident map</firstterm> that specifies which operating
***************
*** 553,559 ****
       <attribution>RFC 1413</attribution>
       <para>
        The Identification Protocol is not intended as an authorization
!       or access control protocol.
       </para>
      </blockquote>
     </para>
--- 556,563 ----
       <attribution>RFC 1413</attribution>
       <para>
        The Identification Protocol is not intended as an authorization
!       or access control protocol.  You must trust the machine running the
!       ident server.
       </para>
      </blockquote>
     </para>
Index: src/backend/libpq/pg_hba.conf.sample
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/libpq/pg_hba.conf.sample,v
retrieving revision 1.19
diff -c -r1.19 pg_hba.conf.sample
*** src/backend/libpq/pg_hba.conf.sample	2001/07/11 19:36:36	1.19
--- src/backend/libpq/pg_hba.conf.sample	2001/07/11 20:27:08
***************
*** 1,5 ****
  # 
! #                PostgreSQL HOST-BASED ACCESS (HBA) CONTROL FILE
  # 
  # 
  # This file controls:
--- 1,5 ----
  # 
! #		  PostgreSQL HOST-BASED ACCESS (HBA) CONTROL FILE
  # 
  # 
  # This file controls:
***************
*** 101,109 ****
  # 		be use only for machines where all users are truested.
  # 
  #   password:	Authentication is done by matching a password supplied
! #   		in clear by the host. If no AUTH_ARGUMENT is used, the
! #   		password is compared with the user's entry in the
! #   		pg_shadow table.
  # 
  # 		If AUTH_ARGUMENT is specified, the username is looked up
  # 		in that file in the $PGDATA directory. If the username
--- 101,109 ----
  # 		be use only for machines where all users are truested.
  # 
  #   password:	Authentication is done by matching a password supplied
! #		in clear by the host. If no AUTH_ARGUMENT is used, the
! #		password is compared with the user's entry in the
! #		pg_shadow table.
  # 
  # 		If AUTH_ARGUMENT is specified, the username is looked up
  # 		in that file in the $PGDATA directory. If the username
***************
*** 118,147 ****
  # 		passwords.
  # 
  #   crypt:  	Same as "password", but authentication is done by
! #   		encrypting the password sent over the network. This is
! #   		always preferable to "password" except for old clients
! #   		that don't support "crypt". Also, crypt can use
! #   		usernames stored in secondary password files but not
! #   		secondary passwords.
! # 
! #   ident: Authentication is done by the ident server on the local 
! #   		or remote host. AUTH_ARGUMENT is required and maps names 
! #		found in the $PGDATA/pg_ident.conf file. The connection
! #   		is accepted if the file contains an entry for this map
! #   		name with the ident-supplied username and the requested
! #   		PostgreSQL username. The special map name "sameuser"
! #   		indicates an implied map (not in pg_ident.conf) that
! #   		maps each ident username to the identical PostgreSQL 
  #		username.
  # 
! #   krb4:   	Kerberos V4 authentication is used.
  # 
! #   krb5:   	Kerberos V5 authentication is used.
  # 
  #   reject: 	Reject the connection. This is used to reject certain hosts
! #   		that are part of a network specified later in the file.
! #   		To be effective, "reject" must appear before the later
! #   		entries.
  # 
  # Local UNIX-domain socket connections support only the AUTH_TYPEs of
  # "trust", "password", "crypt", and "reject".
--- 118,147 ----
  # 		passwords.
  # 
  #   crypt:  	Same as "password", but authentication is done by
! #		encrypting the password sent over the network. This is
! #		always preferable to "password" except for old clients
! #		that don't support "crypt". Also, crypt can use
! #		usernames stored in secondary password files but not
! #		secondary passwords.
! # 
! #   ident:	Authentication is done by the ident server on the local
! #		(127.0.0.1) or remote host. AUTH_ARGUMENT is required and
! #		maps names found in the $PGDATA/pg_ident.conf file. The 
! #		connection is accepted if the file contains an entry for
! #		this map name with the ident-supplied username and the 
! #		requested PostgreSQL username. The special map name 
! #		"sameuser" indicates an implied map (not in pg_ident.conf)
! #		that maps each ident username to the identical PostgreSQL 
  #		username.
  # 
! #   krb4:	Kerberos V4 authentication is used.
  # 
! #   krb5:	Kerberos V5 authentication is used.
  # 
  #   reject: 	Reject the connection. This is used to reject certain hosts
! #		that are part of a network specified later in the file.
! #		To be effective, "reject" must appear before the later
! #		entries.
  # 
  # Local UNIX-domain socket connections support only the AUTH_TYPEs of
  # "trust", "password", "crypt", and "reject".
Wed, 11 Jul 2001 16:33:37 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] Bug in createlang?
Bruce Momjian writes:

> I just applied a diff to better document the use of ident for localhost.
> I think it is a good idea, and in some ways a better use of ident than
> for remote machines.  If I missed a spot that could be better
> documented, let me know.

<blockquote> means it's a quote.  What you added there is not part of the
original source.  Also, *please* make the pg_hba.conf.sample file shorter,
not longer.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Wed, 11 Jul 2001 23:12:00 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Re: [GENERAL] Bug in createlang?
> Bruce Momjian writes:
> 
> > I just applied a diff to better document the use of ident for localhost.
> > I think it is a good idea, and in some ways a better use of ident than
> > for remote machines.  If I missed a spot that could be better
> > documented, let me know.
> 
> <blockquote> means it's a quote.  What you added there is not part of the

Moved out of blockquote.

> original source.  Also, *please* make the pg_hba.conf.sample file shorter,
> not longer.

I have the idea of having the postmaster load the non-comment lines from
pg_hba.conf in as a List of character strings and have the postmaster
read through the strings, reloading on sighup.  That way, we don't have
to read the file at all for each connection.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 11 Jul 2001 17:27:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: [GENERAL] Bug in createlang?

Does anyone have a comment on this?  I wrote it a month ago.

> > Richard Huxton <dev@archonet.com> writes:
> > > "Thomas T. Veldhouse" wrote:
> > >> Why does it ask 4 times?
> > 
> > > createlang is just a script - it basically runs "/path/to/psql $QUERY" -
> > > each query connects a separate time.
> > 
> > Note that running a setup that requires password auth for the DBA will
> > also be a major pain in the rear when running pg_dumpall: one password
> > prompt per database, IIRC.  We have other scripts that make more than
> > one database connection, too.
> 
> This brings up an issue I am concerned about.  Right now, when we
> install the database with initdb, we basically are wide-opened to any
> local user who wants to connect to the database as superuser.  In fact,
> someone could easily install a function in template1 that bypasses
> database security so even after you put a password on the superuser and
> others, they could bypass security.
> 
> Do people have a good solution for this problem?  Should be be
> installing a password for the super-user at initdb time?  I see initdb
> has this option:
> 
>        --pwprompt
> 
>        -W     Makes  initdb prompt for a password of the database
>               superuser. If you  don't  plan  on  using  password
>               authentication,  this  is not important.  Otherwise
>               you won't be able to  use  password  authentication
>               until you have a password set up.
> 
> Do people know they should be using this initdb option if they don't
> trust their local users?  I see no mention of it in the INSTALL file.
> 
> I see it does:
> 
> # set up password
> if [ "$PwPrompt" ]; then
>     $ECHO_N "Enter new superuser password: "$ECHO_C
>     stty -echo > /dev/null 2>&1
>     read FirstPw
>     stty echo > /dev/null 2>&1
>     echo
>     $ECHO_N "Enter it again: "$ECHO_C
>     stty -echo > /dev/null 2>&1
>     read SecondPw
>     stty echo > /dev/null 2>&1
>     echo
>     if [ "$FirstPw" != "$SecondPw" ]; then
>         echo "Passwords didn't match." 1>&2
>         exit_nicely
>     fi
>     echo "ALTER USER \"$POSTGRES_SUPERUSERNAME\" WITH PASSWORD '$FirstPw'" \
>         | "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
>     if [ ! -f $PGDATA/global/pg_pwd ]; then
>         echo "The password file wasn't generated. Please report this problem." 1>&2
>         exit_nicely
>     fi
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 5 Sep 2001 00:42:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in createlang?
Bruce Momjian writes:

> Does anyone have a comment on this?  I wrote it a month ago.

The fact that the database server is wide-open in the default installation
is surely not good, but the problem is that we don't have a universally
accepted way to lock it down.  We could make password authentication the
default, but that would annoy a whole lot of people.  Another option would
be to set the unix domain socket permissions to 0200 by default, so only
the user that's running the server can get in.  I could live with that;
not sure about others.


> > > Richard Huxton <dev@archonet.com> writes:
> > > > "Thomas T. Veldhouse" wrote:
> > > >> Why does it ask 4 times?
> > >
> > > > createlang is just a script - it basically runs "/path/to/psql $QUERY" -
> > > > each query connects a separate time.
> > >
> > > Note that running a setup that requires password auth for the DBA will
> > > also be a major pain in the rear when running pg_dumpall: one password
> > > prompt per database, IIRC.  We have other scripts that make more than
> > > one database connection, too.
> >
> > This brings up an issue I am concerned about.  Right now, when we
> > install the database with initdb, we basically are wide-opened to any
> > local user who wants to connect to the database as superuser.  In fact,
> > someone could easily install a function in template1 that bypasses
> > database security so even after you put a password on the superuser and
> > others, they could bypass security.
> >
> > Do people have a good solution for this problem?  Should be be
> > installing a password for the super-user at initdb time?  I see initdb
> > has this option:
> >
> >        --pwprompt
> >
> >        -W     Makes  initdb prompt for a password of the database
> >               superuser. If you  don't  plan  on  using  password
> >               authentication,  this  is not important.  Otherwise
> >               you won't be able to  use  password  authentication
> >               until you have a password set up.
> >
> > Do people know they should be using this initdb option if they don't
> > trust their local users?  I see no mention of it in the INSTALL file.
> >
> > I see it does:
> >
> > # set up password
> > if [ "$PwPrompt" ]; then
> >     $ECHO_N "Enter new superuser password: "$ECHO_C
> >     stty -echo > /dev/null 2>&1
> >     read FirstPw
> >     stty echo > /dev/null 2>&1
> >     echo
> >     $ECHO_N "Enter it again: "$ECHO_C
> >     stty -echo > /dev/null 2>&1
> >     read SecondPw
> >     stty echo > /dev/null 2>&1
> >     echo
> >     if [ "$FirstPw" != "$SecondPw" ]; then
> >         echo "Passwords didn't match." 1>&2
> >         exit_nicely
> >     fi
> >     echo "ALTER USER \"$POSTGRES_SUPERUSERNAME\" WITH PASSWORD '$FirstPw'" \
> >         | "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
> >     if [ ! -f $PGDATA/global/pg_pwd ]; then
> >         echo "The password file wasn't generated. Please report this problem." 1>&2
> >         exit_nicely
> >     fi
> >
> > --
> >   Bruce Momjian                        |  http://candle.pha.pa.us
> >   pgman@candle.pha.pa.us               |  (610) 853-3000
> >   +  If your life is a hard drive,     |  830 Blythe Avenue
> >   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
> >
>
>

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 6 Sep 2001 12:21:58 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Bug in createlang?
On Wed, 5 Sep 2001, Bruce Momjian wrote:

> Does anyone have a comment on this?  I wrote it a month ago.

I'm no authority on security, but it seems to me that PosgreSQL ought to
require the setting of an administrative password. IIRC, some of the
commercial database products have default passwords, but those are a
frequent security problem, since admins oft forget to change them. But I
don't mind setting the root password when I install my RedHat or Yellow
Dog system, so I don't think I'll mind setting it when I install Postgres.

Just MHO.

Regards,

David

-- 
David Wheeler                                         AIM: dwTheory
David@Wheeler.net                                     ICQ: 15726394
                                                   Yahoo!: dew7e
                                                   Jabber: Theory@jabber.org


Thu, 6 Sep 2001 06:22:22 -0700 (PDT)
David Wheeler <David@Wheeler.net>
Re: Bug in createlang?
Peter Eisentraut <peter_e@gmx.net> writes:
> The fact that the database server is wide-open in the default installation
> is surely not good, but the problem is that we don't have a universally
> accepted way to lock it down.  We could make password authentication the
> default, but that would annoy a whole lot of people.

Yes, particularly for pg_dumpall scripts...

> Another option would be to set the unix domain socket permissions to
> 0200 by default, so only the user that's running the server can get
> in.  I could live with that; not sure about others.

For my purposes this would be acceptable, but I wouldn't actually want
to use 0200.  So it'd be nicer if the default socket permission were
trivially configurable (ideally as a configure switch).  Given that,
I wouldn't mind if the default were 0200.

Note that locking down the unix socket is little help if one is using a
startup script that helpfully supplies -i by default.  I am not sure
what the score is with all the startup scripts that are in various RPMs
and other platform-specific distributions; does anyone know if there are
any that ship with -i enabled?

			regards, tom lane

Thu, 06 Sep 2001 11:44:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in createlang? 
> Bruce Momjian writes:
> 
> > Does anyone have a comment on this?  I wrote it a month ago.
> 
> The fact that the database server is wide-open in the default installation
> is surely not good, but the problem is that we don't have a universally
> accepted way to lock it down.  We could make password authentication the
> default, but that would annoy a whole lot of people.  Another option would
> be to set the unix domain socket permissions to 0200 by default, so only
> the user that's running the server can get in.  I could live with that;
> not sure about others.

Whatever you suggest.  We basically create a world-writeable
socket/database when we do initdb.  It is similar to a product
installing in a world-writable directory.

I realize you can lock it down later, but it seems people need to lock
it down _before_ doing initdb or somehow keep it locked down until they
set security.  Our new SO_PEERCRED/SCM_CREDS gives us a lockdown option
on Linux/BSD platforms, but not on the others.

If we do the socket permissions thing for initdb, when do we start
setting the socket permissions properly?

I realize there is no easy answer.  I just wanted people to know this is
a security hole.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 6 Sep 2001 11:49:58 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in createlang?
[redirect to -hackers]

Tom Lane writes:

> > The fact that the database server is wide-open in the default installation
> > is surely not good, but the problem is that we don't have a universally
> > accepted way to lock it down.

> > Another option would be to set the unix domain socket permissions to
> > 0200 by default, so only the user that's running the server can get in.

> For my purposes this would be acceptable, but I wouldn't actually want
> to use 0200.  So it'd be nicer if the default socket permission were
> trivially configurable (ideally as a configure switch).  Given that,
> I wouldn't mind if the default were 0200.

It is configurable already (unix_socket_permissions in postgresql.conf).
If we make this change then we just need to make it really clear in the
documentation somewhere, because the error message will say "Connection
refused", and the permission of the socket file is the last thing people
will think of.

> Note that locking down the unix socket is little help if one is using a
> startup script that helpfully supplies -i by default.  I am not sure
> what the score is with all the startup scripts that are in various RPMs
> and other platform-specific distributions; does anyone know if there are
> any that ship with -i enabled?

The last count is that none that I can see the source code for do.  In
general, I don't think this is our problem.  If people change the default
configuration in their packages without knowing better, they cannot be
helped.  They will just as quickly change the default unix socket
permissions back to 0777 if they want to.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 6 Sep 2001 19:43:23 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [GENERAL] Bug in createlang? 
Believe Debian sid does.

  -- Matt

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, September 06, 2001 11:44 AM
To: Peter Eisentraut
Cc: Bruce Momjian; Richard Huxton; Thomas T. Veldhouse;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Bug in createlang? 

Note that locking down the unix socket is little help if one is using a
startup script that helpfully supplies -i by default.  I am not sure
what the score is with all the startup scripts that are in various RPMs
and other platform-specific distributions; does anyone know if there are
any that ship with -i enabled?


Thu, 6 Sep 2001 14:33:24 -0400
"Matt Block" <matt@blockdev.net>
Re: Bug in createlang? 

To address this issue, I have added the following paragraph to the
installation instructions:

   However, while the directory contents are secure, the default
   <filename>pg_hba.conf</filename> authentication of
   <literal>trust</literal> allows any local user to become the
   superuser and connect to the database. If you don't trust your local
   users, we recommend you use the <command>initdb</command> option
   <option>-W</option> or <option>--pwprompt</option> to assign a
   password to the superuser and modify your
   <filename>pg_hba.conf</filename> accordingly. (Another option:
   Your operating system may support <literal>ident</literal> for
   local connections.)


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

> > Richard Huxton <dev@archonet.com> writes:
> > > "Thomas T. Veldhouse" wrote:
> > >> Why does it ask 4 times?
> > 
> > > createlang is just a script - it basically runs "/path/to/psql $QUERY" -
> > > each query connects a separate time.
> > 
> > Note that running a setup that requires password auth for the DBA will
> > also be a major pain in the rear when running pg_dumpall: one password
> > prompt per database, IIRC.  We have other scripts that make more than
> > one database connection, too.
> 
> This brings up an issue I am concerned about.  Right now, when we
> install the database with initdb, we basically are wide-opened to any
> local user who wants to connect to the database as superuser.  In fact,
> someone could easily install a function in template1 that bypasses
> database security so even after you put a password on the superuser and
> others, they could bypass security.
> 
> Do people have a good solution for this problem?  Should be be
> installing a password for the super-user at initdb time?  I see initdb
> has this option:
> 
>        --pwprompt
> 
>        -W     Makes  initdb prompt for a password of the database
>               superuser. If you  don't  plan  on  using  password
>               authentication,  this  is not important.  Otherwise
>               you won't be able to  use  password  authentication
>               until you have a password set up.
> 
> Do people know they should be using this initdb option if they don't
> trust their local users?  I see no mention of it in the INSTALL file.
> 
> I see it does:
> 
> # set up password
> if [ "$PwPrompt" ]; then
>     $ECHO_N "Enter new superuser password: "$ECHO_C
>     stty -echo > /dev/null 2>&1
>     read FirstPw
>     stty echo > /dev/null 2>&1
>     echo
>     $ECHO_N "Enter it again: "$ECHO_C
>     stty -echo > /dev/null 2>&1
>     read SecondPw
>     stty echo > /dev/null 2>&1
>     echo
>     if [ "$FirstPw" != "$SecondPw" ]; then
>         echo "Passwords didn't match." 1>&2
>         exit_nicely
>     fi
>     echo "ALTER USER \"$POSTGRES_SUPERUSERNAME\" WITH PASSWORD '$FirstPw'" \
>         | "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
>     if [ ! -f $PGDATA/global/pg_pwd ]; then
>         echo "The password file wasn't generated. Please report this problem." 1>&2
>         exit_nicely
>     fi
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 27 Nov 2001 16:01:55 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug in createlang?