Re: [ADMIN] user auth & passwords - Mailing list pgsql-docs

Thomas, is this already in the docs?


> Jason Dillon wrote:
>   >For the life of me I can not find any substancial documentation on how to gi
>       >ve
>   >users passwords.  I have found the -a argument to many of the command line
>   >tools, but I can't seem to figure out what they do.  I have also played with
>   >pg_hba.conf, but this exercise has proved to be just as frustrating. I also
>   >tried to use "create user" via psql, but it too did little.
>   >
>   >I am trying to setup a database that will only allow users with the correct
>   >encrypted passwords to access the system.  It does not appear to me that the
>   >`createuser' tool has the ability to set user passwords.  I tryed creating
>   >users with different settings for -a <system> but this did not really do
>   >anything differently.
> 
> The man page says "this option no longer has any effect".
>   >
>   >When I changed the auth setting in pg_hba.conf from trust to crypt or
>   >password, I was unable to login with user postgres.  I had to set
>   >local back to trust to be able to do anything.
> Every account _requires_ a password.
>   >
>   >I would really apreciate it if someone who knows how to create passwords for
>   >users would drop some knowledge.  The lack of documentation is driving me nu
>       >ts.
> 
> I put this together a few days back:
> 
> =========================================================================
> 
> 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().
> [How can one know, with libpq, whether this is necessary?]
> 
> 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.
> 
> <Debian-specific>
> If you turn on passwords for local, the default do.maintenance cron job
> will stop working, because it will not supply a username or password.
> In this case, you must alter /etc/cron.d/postgresql to supply the
> user and password for the postgres superuser, with the -u and -p options.
> It will then be necessary to change the permissions on /etc/cron.d/postgresql
> to make it readable by root only.
> </Debian-specific>
> 
> 
> 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. pgaccess has no mechanism for specifying username and password. It cannot
>    be used if password/crypt authentication is turned on for host
>    connections from localhost.
> 
> 3. 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.
> 
> 4. 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.
> 
> 5. 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.
> =========================================================================
> 
> -- 
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
>                PGP key from public servers; key ID 32B8FAA1
>                  ========================================
>      "But without faith it is impossible to please him; for 
>       he that cometh to God must believe that he is, and 
>       that he is a rewarder of them that diligently seek 
>       him."       Hebrews 11:6 
> 
> 
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026