Thread: starting postgresql with pgsql password - workarounds?

hello,

I've been using postgresql for about a year now, and am pretty
comfortable with the basics, bu there has been something bugging me for
a while now:

I set the METHOD in pg_hba.conf to md5 so that a password is required
from all users, from all hosts.

The only problem is that if the server restarts, postgresql will not
start until somebody goes to the console and enters the password for the
pgsql account.

Is there a solution for this solution?

Thanks,
DW

This is not a PostgreSQL problem, it's the script you are using for startup that has some problem. The pg_hba method is for connection stablishment. PostgreSQL will start no matter what you put there.

Startup scripts are usually run as root, and postgresql script should su to the postgresql user to start the database. I don't know what your script is doing, but root should be able to su to any user without password.

Check your script, post it if you want. It would be usefull to know what system you are using also (linux/bsd/solaris/etc).

I am using the default startup script that is supplied with the FreeBSD
port (/usr/local/etc/rc.d/010.pgsql.sh) and enabling it in /etc/rc.d
with -o -i flags so listens on TCP/IP

Also, I should mention that the password I mentioned is NOT the password
for the local (Unix) pgsql account, but the password I set for the
postgresql database superuser, pgsql. That is the password I need to
enter to get postgresql to start.

Thanks,
DW

---------------------------------------------------------------------------------
#!/bin/sh

# $FreeBSD: ports/databases/postgresql74-server/files/pgsql.sh.tmpl,v
1.17 2005/03/19 03:51:45 girgen Exp $
#
# PROVIDE: postgresql
# REQUIRE: LOGIN
# KEYWORD: FreeBSD shutdown
#
# Add the following line to /etc/rc.conf to enable PostgreSQL:
#
#  postgresql_enable="YES"
#  # optional
#  postgresql_data="/usr/local/pgsql/data"
#  postgresql_flags="-w -s -m fast"
#
# This scripts takes one of the following commands:
#
#   start stop restart reload status initdb
#
# For postmaster startup options, edit ${postgresql_data}/postgresql.conf

prefix=/usr/local

. /etc/rc.subr

load_rc_config postgresql

# set defaults
postgresql_enable=${postgresql_enable:-"NO"}
postgresql_flags=${postgresql_flags:-"-w -s -m fast"}
postgresql_user=pgsql
eval postgresql_data=${postgresql_data:-"~${postgresql_user}/data"}
postgresql_class=${postgresql_class:-"default"}

name=postgresql
rcvar=`set_rcvar`
command=${prefix}/bin/pg_ctl
command_args="-D ${postgresql_data} ${postgresql_flags} $1"
extra_commands="reload initdb"
start_cmd="postgresql_command start"
stop_cmd="postgresql_command stop"
restart_cmd="postgresql_command restart"
reload_cmd="postgresql_command reload"
status_cmd="postgresql_command status"

initdb_cmd="postgresql_initdb"

postgresql_command()
{
    su -l ${postgresql_user} -c "exec ${command} ${command_args}"
}

postgresql_initdb()
{
    su -l -c ${postgresql_class} ${postgresql_user} -c "exec
${prefix}/bin/initdb -D ${postgresql_data}"
}

run_rc_command "$1"

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


Franco Bruno Borghesi wrote:

> This is not a PostgreSQL problem, it's the script you are using for
> startup that has some problem. The pg_hba method is for connection
> stablishment. PostgreSQL will start no matter what you put there.
>
> Startup scripts are usually run as root, and postgresql script should
> su to the postgresql user to start the database. I don't know what
> your script is doing, but root should be able to su to any user
> without password.
>
> Check your script, post it if you want. It would be usefull to know
> what system you are using also (linux/bsd/solaris/etc).
>
>
> 2005/5/20, Duane Winner <duanewinner@att.net
> <mailto:duanewinner@att.net>>:
>
>     hello,
>
>     I've been using postgresql for about a year now, and am pretty
>     comfortable with the basics, bu there has been something bugging
>     me for
>     a while now:
>
>     I set the METHOD in pg_hba.conf to md5 so that a password is required
>     from all users, from all hosts.
>
>     The only problem is that if the server restarts, postgresql will not
>     start until somebody goes to the console and enters the password
>     for the
>     pgsql account.
>
>     Is there a solution for this solution?
>
>     Thanks,
>     DW
>
>     ---------------------------(end of
>     broadcast)---------------------------
>     TIP 6: Have you searched our list archives?
>
>                    http://archives.postgresql.org
>     <http://archives.postgresql.org>
>
>

mmmhhh, I have never installed postgresql from the ports. I don´t know what the script is doing, probably it´s checking that Postgresql directory is initialized.

Anyway, here is my homemade script, you could replace yours with it (check it first, but it´s quite simple).
My script does not tell postgresql to listen on tcp sockets, but you can enable it in your postgresql.conf.

#!/bin/sh

case "$1" in
start)
        echo -n " postgresql"
        su -l pgsql -c '~/bin/pg_ctl start'
        ;;
stop)
        echo -n " postgresql"
        su -l pgsql -c '~/bin/pg_ctl stop -mf'
        ;;
restart)
        echo -n  "reloading postgresql"
        echo " stopping"
        $0 stop
        echo ""
        echo " starting"
        $0 start
        echo ""
        ;;
reload)
        echo " reloading postgreSQL"
        su -l pgsql -c '~/bin/pg_ctl reload -mf'
        echo ""
        ;;
status)
        echo -n " postgresql"
        su -l pgsql -c '~/bin/pg_ctl status'
        ;;
*)
        echo ""
        echo "Use: $0 [ start | stop | restart | reload | status ]"
        echo ""
        ;;
esac


exit 0


Hope it helps.

>> # set defaults
>> postgresql_enable=${postgresql_enable:-"NO"}
>> postgresql_flags=${postgresql_flags:-"-w -s -m fast"}

Try it without the "-w" ... that's probably causing it to try to connect
with psql.

Alternatively, set up a ~/.pgpass file for the postgres user (which
might be a reasonable thing anyway).

BTW, this script seems fairly brain-dead in assuming that the same
option flags should apply to all pg_ctl commands.

            regards, tom lane