Thread: Bug in createlang?
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
"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? > 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 >
"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
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
> 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
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
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?
"Matt Block" wrote: >Believe Debian sid does. Not any more. It uses Unix socket authentication by default. Originally Debian installed as "local all trust"; later I switched to using TCP/IP and ident, but people objected because of the perceived insecurity of identd, which they did not want running on their machines. So then I switched to Unix socket authentication. ...> >-----Original Message----- >From: pgsql-general-owner@postgresql.org >[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane >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? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Behold, I stand at the door, and knock; if any man hear my voice, and open the door, I will come in to him, and will sup with him, and he with me." Revelation 3:20
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