Thread: db security; user identification

db security; user identification

From

Marcin Gil

Date:

27 January 2004, 09:32:02

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

Re: db security; user identification

From

Martin Atukunda

Date:

27 January 2004, 10:51:24

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

Re: db security; user identification

From

Marcin Gil

Date:

27 January 2004, 12:22:31

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

Re: db security; user identification

From

Martin Atukunda

Date:

27 January 2004, 12:55:02

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

Re: db security; user identification

From

Bruno Wolff III

Date:

27 January 2004, 12:57:56

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.

Re: db security; user identification

From

Marcin Gil

Date:

27 January 2004, 13:26:44

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

Re: db security; user identification

From

Tom Lane

Date:

27 January 2004, 15:51:17

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

Re: db security; user identification

From

Marcin Gil

Date:

28 January 2004, 07:15:38

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

Re: db security; user identification

From

Marcin Gil

Date:

28 January 2004, 07:37:39

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

Re: db security; user identification

From

Tom Lane

Date:

28 January 2004, 14:21:20

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

Re: db security; user identification

From

Bruno Wolff III

Date:

28 January 2004, 18:01:05

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.