Postgres Pro
Downloads
Home   >   mailing lists

Thread: Selective authentication?

Selective authentication?

From
Date:

I'm setting up PostgreSQL for a small academic lab, where I work.  The
host OS is Linux (Debian).  I want to allow most users to connect to
the database without having to supply a password, but I want to have a
special user, called dbadmin, with superuser privileges.  The idea is
that I (or whoever gets entrusted with this responsibility) would
connect as dbadmin *only* when the task at hand required superuser
privileges.

I'd be very grateful if someone could explain to me how to set this
up.

(If there were a simple way to obviate the need to type in a password
every time (e.g. through a suitable command in a configuration file,
such as one can do with ${HOME}/.my.cnf), then I could just require a
password from everyone, but I have not found out how to do this in
PostgreSQL.)

Many thanks in advance!

kj

Re: Selective authentication?

From
"Charley L. Tiggs"
Date:
For information on setting up a .pgpass file:

http://www.postgresql.org/docs/8.0/static/libpq-pgpass.html

Charley

On Aug 25, 2005, at 2:31 PM, <kynn@panix.com> <kynn@panix.com> wrote:

>
>
> I'm setting up PostgreSQL for a small academic lab, where I work.  The
> host OS is Linux (Debian).  I want to allow most users to connect to
> the database without having to supply a password, but I want to have a
> special user, called dbadmin, with superuser privileges.  The idea is
> that I (or whoever gets entrusted with this responsibility) would
> connect as dbadmin *only* when the task at hand required superuser
> privileges.
>
> I'd be very grateful if someone could explain to me how to set this
> up.
>
> (If there were a simple way to obviate the need to type in a password
> every time (e.g. through a suitable command in a configuration file,
> such as one can do with ${HOME}/.my.cnf), then I could just require a
> password from everyone, but I have not found out how to do this in
> PostgreSQL.)
>
> Many thanks in advance!
>
> kj
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

Re: Selective authentication?

From
Tom Lane
Date:
"Charley L. Tiggs" <ctiggs@xpressdocs.com> writes:
> For information on setting up a .pgpass file:
> http://www.postgresql.org/docs/8.0/static/libpq-pgpass.html

.pgpass for everyone is one way, but there are others.  If this is
all going to be local connections, you could also think about
setting up your pg_hba.conf file along the lines of

    local    any    dbadmin        md5
    local    any    any        ident

This would have the effect of requiring a password from anyone trying to
log in as dbadmin.  For every other database user name, there'd be a
check that they were logged in under the same Unix user name, and if so
they'd be let in without a password.

Ident is not too trustworthy across the network, but for local
connections it should be fine.

            regards, tom lane