db security; user identification

#1

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 (AT) audax (DOT) com.pl

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#2

On Tuesday 27 January 2004 13:30, Marcin Gil wrote:

Quote:

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

Quote:

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.

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#3

Martin Atukunda wrote:

Quote:

- 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 (AT) audax (DOT) com.pl

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#4

On Tuesday 27 January 2004 16:21, Marcin Gil wrote:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#5

On Tue, Jan 27, 2004 at 14:21:27 +0100,
Marcin Gil <marcin.gil (AT) audax (DOT) com.pl> wrote:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#6

Martin Atukunda wrote:

Quote:

- 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 (AT) audax (DOT) com.pl

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#7

Marcin Gil <marcin.gil (AT) audax (DOT) com.pl> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#8

Tom Lane wrote:

Quote:

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.

Quote:

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 (AT) audax (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#9

Tom Lane wrote:

Quote:

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 (AT) audax (DOT) com.pl

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#10

Marcin Gil <marcin.gil (AT) audax (DOT) com.pl> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)