Thread: configuring pg_hba.conf...

configuring pg_hba.conf...

From
Postgres GOD
Date:
i'm an actual newbie when it comes to postgres.

how do i set the pg_hba.conf for a password authentication? how do i set
the ps_pwd and other needed configuration?

and lastly, where can i find a detailed document on configuring postgres?

thanks.

samil buri.


Re: [ADMIN] configuring pg_hba.conf...

From
"Oliver Elphick"
Date:
Postgres GOD wrote:
  >how do i set the pg_hba.conf for a password authentication? how do i set
  >the ps_pwd and other needed configuration?

Here is a document I put together on the subject. (If anything in it has
become inaccurate, please let me know; I believe it is correct for 6.4.2.)

               +++++++++++++++++++++++++++++++++++++
How to use clear or encrypted passwords for PostgreSQL access:
=============================================================

Use lines such as

  local        all                password
  host        192.137.23    255.255.255.0    crypt

in /etc/postgresql/pg_hba.conf; then you can use

   CREATE USER user WITH PASSWORD password...

to create a new user with the specified password, or

   ALTER USER user WITH PASSWORD password...

to change the password of an existing user.  ANY USER with create-user
privilege can ALTER a password for any user, *INCLUDING* the postgres
super-user.

If connecting with psql, use the -u option; the user is prompted for username
and password.  If you don't use -u, the connection fails.

If using your own program with libpq, it is up to you to collect the user name
and password from the user and send them to the backend with PQsetdbLogin().

Passwords are stored in pg_shadow in clear, but if `crypt' authentication is
specified, the frontend encrypts the password with a random salt and
the backend uses the same salt to encrypt the password in the database.
If the two encrypted passwords match, the user is allowed access. If the
authentication method is `password', the password is transmitted and
compared in clear.

If passwords are turned on, it becomes impossible to connect as
a user, if no password is defined for that user, neither can you use
\connect to change user within psql.


Problems with password authentication
=====================================

1. There is no easy and secure way to automate access when passwords are
   in use.  It would be good if the postgres super-user (as identified by
   Unix on a Unix sockets connection) could bypass the authentication.

2. In general, passwords are insecure, because they are held in clear
   in pg_shadow.  Anyone with create-user privilege can not only alter but
   also read them.  They ought to be stored with one-way encryption, as
   with the Unix password system.

3. The postgres super-user's password can be changed by anyone with
   create-user privilege.  It ought to be the case that people can
   only change their own passwords and that only the super-user can change
   other peoples' passwords.

4. If passwords are turned on, the -u option must be supplied to psql. If
   it is not, psql merely says "Connection to database 'xxxx' failed.".  A
   more helpful error message would be desirable.
               +++++++++++++++++++++++++++++++++++++

  >and lastly, where can i find a detailed document on configuring postgres?

Have you read the Administrator's Guide? (doc/admin.ps.gz)  There are also
extensive comments in pg_hba.conf.

Also: `man create_user' and `man alter_user'

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Wash me thoroughly from mine iniquity, and cleanse me
      from my sin. For I acknowledge my transgressions; and
      my sin is ever before me. Against thee, thee only,
      have I sinned, and done this evil in thy sight..."
                                   Psalms 51:2-4



Re: [ADMIN] configuring pg_hba.conf...

From
Postgres GOD
Date:

On Tue, 26 Jan 1999, Oliver Elphick wrote:

> Postgres GOD wrote:
>   >how do i set the pg_hba.conf for a password authentication? how do i set
>   >the ps_pwd and other needed configuration?
>
> Here is a document I put together on the subject. (If anything in it has
> become inaccurate, please let me know; I believe it is correct for 6.4.2.)
>
>                +++++++++++++++++++++++++++++++++++++
> How to use clear or encrypted passwords for PostgreSQL access:
> =============================================================
>
> Use lines such as
>
>   local        all                password
>   host        192.137.23    255.255.255.0    crypt
>
> in /etc/postgresql/pg_hba.conf; then you can use
>
>    CREATE USER user WITH PASSWORD password...
>

thanks for your help. i followed your instructions but can't still connect
to my database. btw, i'm using 6.3.2 version. i copied exactly the two
lines you've mentioned above in my pg_hba.conf and i even tried modifying
using my server's ip but still user authentication failed.

what should be the problem with this?


samil buri.
-----------
'winning w/o fighting is best.' --suntzu.


Re: [ADMIN] configuring pg_hba.conf...

From
"Oliver Elphick"
Date:
Postgres GOD wrote:
  >> Use lines such as
  >>
  >>   local        all                password
  >>   host        192.137.23    255.255.255.0    crypt
  >>
  >> in /etc/postgresql/pg_hba.conf; then you can use
  >>
  >>    CREATE USER user WITH PASSWORD password...
  >>
  >
  >thanks for your help. i followed your instructions but can't still connect
  >to my database. btw, i'm using 6.3.2 version. i copied exactly the two
  >lines you've mentioned above in my pg_hba.conf and i even tried modifying
  >using my server's ip but still user authentication failed.
  >
  >what should be the problem with this?

Have you restarted the postmaster after editing pg_hba.conf?
Have you created a password for yourself before trying to connect
using password authentication?

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Wash me thoroughly from mine iniquity, and cleanse me
      from my sin. For I acknowledge my transgressions; and
      my sin is ever before me. Against thee, thee only,
      have I sinned, and done this evil in thy sight..."
                                   Psalms 51:2-4



Re: [ADMIN] configuring pg_hba.conf...

From
"Carl Zmola"
Date:
"Oliver Elphick" wrote:
> Postgres GOD wrote:
>   >> Use lines such as
>   >>
>   >>   local        all                password
>   >>   host        192.137.23    255.255.255.0    crypt
>   >>
>   >> in /etc/postgresql/pg_hba.conf; then you can use
>   >>
>   >>    CREATE USER user WITH PASSWORD password...
>   >>
>   >
>   >thanks for your help. i followed your instructions but can't still connect
>   >to my database. btw, i'm using 6.3.2 version. i copied exactly the two
>   >lines you've mentioned above in my pg_hba.conf and i even tried modifying
>   >using my server's ip but still user authentication failed.
>   >
>   >what should be the problem with this?
>
> Have you restarted the postmaster after editing pg_hba.conf?
> Have you created a password for yourself before trying to connect
> using password authentication?

That shouldn't be necessary. At least I havn't had to.

Can you connect remotly without crypt?
I would try the following.

local        all                password
host        127.0.0.1    255.255.255.0   password
host        192.137.23    255.255.255.0    crypt

and try to connect from your local machine but using localhost.

psql -u -h localhost

That exercises the network interface.  if that works, Try

host        127.0.0.1    255.255.255.0   crypt


I've had the best luck with getting things working using the local
clients first.

Carl


Carl
zmola@campbellsci.com

vacuum problem...

From
Postgres GOD
Date:
i know this one is easy one to those postgres guru.

how can i deal with this vacuum problem?

NOTICE:  CreatePortal: portal <vacuum> already exists
ERROR:  can't create lock file -- another vacuum cleaner running?

thanks.

samil.
'winning w/o fighting is best!'-suntzu.