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

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

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?

"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

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

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

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

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

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

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

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

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

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

[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

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?

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