Обсуждение: db security; user identification
I have switched identification method from 'trust' to 'md5' for all local requests, ie: - local all all trust + local all all md5 But that creates a problem: - when restarting postgresql it waits for password; how can I work it around? My target is that pgsql restarts (or starst with system init) properly without need of entering password but every connection should require it. If there is no password requirement within local system, than every user could do createdb -d dbname -U postgres and create a database.. If I am mistaken, please point it out. Regards, -- Marcin Gil :: marcin.gil@audax.com.pl
On Tuesday 27 January 2004 13:30, Marcin Gil wrote: > I have switched identification method from 'trust' to 'md5' > for all local requests, ie: > > - local all all trust > + local all all md5 try something like (untested) - local all all trust + local all postgres trust + local all all md5 > But that creates a problem: > - when restarting postgresql it waits for password; > how can I work it around? > > My target is that pgsql restarts (or starst with system init) > properly without need of entering password but every connection > should require it. the startup script should su to the user postgres and then start the postmaster. > If there is no password requirement within local system, > than every user could do createdb -d dbname -U postgres and > create a database.. > > If I am mistaken, please point it out. - Martin - -- "If we don't succeed, we run the risk of failure." --Bill Clinton, President
Martin Atukunda wrote: > - local all all trust > + local all postgres trust > + local all all md5 > Ok. but if user does: psql -d template1 -U postgres? then he won't be asked about password but should. Everyone who can access psql, can get into db as postgres user. Not safe I suppose. -- Marcin Gil :: marcin.gil@audax.com.pl
On Tuesday 27 January 2004 16:21, Marcin Gil wrote: > Martin Atukunda wrote: > > - local all all trust > > + local all postgres trust > > + local all all md5 > > Ok. but if user does: > psql -d template1 -U postgres? > > then he won't be asked about password but should. > Everyone who can access psql, can get into db as postgres user. > Not safe I suppose. Quite true. This is not a safe option, particularly if you don't trust local users. I suppose in this case you could maintain the following in pg_hba.conf: - local all all trust + local all all md5 and then have init the postmaster by using su. i.e. 235:respawn:/bin/su - postgres -c /usr/local/pgsql/bin/start.sh with start.sh having: #!/bin/sh DATADIR=/usr/local/pgsql/data OPTIONS="-i -N 256 -B 512" LOGFILE=/usr/local/pgsql/server.log postmaster -D $DATADIR $OPTIONS > $LOGFILE 2>&1 - Martin - -- "If we don't succeed, we run the risk of failure." --Bill Clinton, President
On Tue, Jan 27, 2004 at 14:21:27 +0100, Marcin Gil <marcin.gil@audax.com.pl> wrote: > Martin Atukunda wrote: > > >- local all all trust > >+ local all postgres trust > >+ local all all md5 > > > Ok. but if user does: > psql -d template1 -U postgres? > > then he won't be asked about password but should. > Everyone who can access psql, can get into db as postgres user. > Not safe I suppose. You definitely don't want: local all postgres trust You can probably use ident authentication (this doesn't work for local connections for all os's) to allow root to connect as the user postgres. If you do this, than anyone connecting as postgres will also need to use ident authentication and be listed in the map along with root. You might end up creating a second superuser account that uses md5 authentication.
Martin Atukunda wrote: > > - local all all trust > + local all all md5 > > and then have init the postmaster by using su. > I've written a startup script for my Slackware that does that. I'll reboot the server soon to check it out. Hope it won't ask for password. I also maintain my very own i686/athlon-xp packages of postgresql for slackware. It contains group/user creation, logrotate script, startup script and all chmodding :) Regards, -- Marcin Gil :: marcin.gil@audax.com.pl
Marcin Gil <marcin.gil@audax.com.pl> writes:
> I have switched identification method from 'trust' to 'md5'
> for all local requests, ie:
> - local all all trust
> + local all all md5
> But that creates a problem:
> - when restarting postgresql it waits for password;
> how can I work it around?
I think the other respondents have missed the point --- your immediate
problem is that you want to turn off the "wait for postmaster to start"
option in pg_ctl, because that's what's demanding a password. I think
you want "pg_ctl start -w" but check the man page to be sure. You
should be able to run fine with the above configuration otherwise.
I concur with the suggestion to investigate local IDENT auth, though.
If your system supports it, it's trustworthy and lots more convenient
than forcing a password to be supplied all the time.
Another possibility is to put the correct password into the postgres
account's ~/.pgpass file. If you stick with md5 local auth you are
going to end up doing that anyway, because it's the only reasonable way
to handle authentication for batch jobs (think about backup and periodic
vacuum tasks). I am not totally sure, but I think that would also fix
the pg_ctl start problem without needing -w.
regards, tom lane
Tom Lane wrote:
> I concur with the suggestion to investigate local IDENT auth, though.
> If your system supports it, it's trustworthy and lots more
> convenient than forcing a password to be supplied all the time.
Could you please, suggest any further reading on IDENT topic?
I've found myself lacking knowlegde on that.
> Another possibility is to put the correct password into the postgres
> account's ~/.pgpass file. If you stick with md5 local auth you are
> going to end up doing that anyway, because it's the only reasonable
> way to handle authentication for batch jobs (think about backup and
> periodic vacuum tasks). I am not totally sure, but I think that
> would also fix the pg_ctl start problem without needing -w.
>
pg_ctl has two options:
-w : wait for the start or shutdown to complete
-W : do not wait for the start or shutdown to complete
So I guess its '-W' you are writing about?
I've attached my startup script.
Thanks
--
Marcin Gil :: marcin.gil@audax.com.pl
#!/bin/sh
#
# /etc/rc.d/rc.postgres
#
# Start/stop/restart the PostgreSQL database server.
#
export PGDATA=/var/lib/pgsql
USE_TCP=1
if [ "$2" = "usetcp" ]; then
USE_TCP=1
elif [ "$2" = "notcp" ]; then
USE_TCP=0
fi
if [ "${USE_TCP}" == "1" ]; then
OPTIONS="-o -i"
fi
postgres_start() {
if [ -x /usr/bin/pg_ctl -a -x /usr/bin/postmaster ]; then
echo "Starting PostgreSQL..."
su postgres -c "/usr/bin/pg_ctl start -l /var/log/postgresql -D ${PGDATA} ${OPTIONS}"
fi
}
postgres_stop() {
echo "Stopping PostgreSQL..."
su postgres -c "/usr/bin/pg_ctl stop -w -m fast"
}
postgres_restart() {
echo "Restarting PostgreSQL..."
su postgres -c "/usr/bin/pg_ctl restart -w -m fast ${OPTIONS}"
}
postgres_reload() {
echo "Reloading PostgreSQL..."
su postgres -c "/usr/bin/pg_ctl reload"
}
postgres_status() {
su postgres -c "/usr/bin/pg_ctl status"
}
case "$1" in
'start')
postgres_start
;;
'stop')
postgres_stop
;;
'restart')
postgres_restart
;;
'reload')
postgres_reload
;;
'status')
postgres_status
;;
*)
echo "Usage: $0 start|stop|restart|reload|status"
;;
esac
Tom Lane wrote: > Another possibility is to put the correct password into the postgres > account's ~/.pgpass file. If you stick with md5 local auth you are I've read developer docs about .pgpass file. Does it support already md5 encoded passwords? There's nothing about it in the docs so I suppose it doesn't but prefer to ask :) Thanks -- Marcin Gil :: marcin.gil@audax.com.pl
Marcin Gil <marcin.gil@audax.com.pl> writes:
> I've read developer docs about .pgpass file. Does it support already
> md5 encoded passwords?
No. What for? If permission 600 on that file isn't safe enough, you
have worse problems than someone else getting into the database.
regards, tom lane
On Wed, Jan 28, 2004 at 09:14:39 +0100, Marcin Gil <marcin.gil@audax.com.pl> wrote: > Tom Lane wrote: > > >I concur with the suggestion to investigate local IDENT auth, though. > > If your system supports it, it's trustworthy and lots more > >convenient than forcing a password to be supplied all the time. > > Could you please, suggest any further reading on IDENT topic? > I've found myself lacking knowlegde on that. Look in the section for client authentication in the documentation.