Re: [ADMIN] user auth & passwords

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(dot)Elphick(at)lfix(dot)co(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026