Thread: pg_hba.conf and crypt
I'm having trouble understanding how to get the crypt option working in the pg_hba.conf file. I can get cleartext passwords to be passed and they work just fine, but I cannot figure out how in the heck to get the crypt option working. I've looked through the FAQ and found only a reference to the pg_hba.conf man page which isn't all that clear to me. Any help would be greatly appreciated. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Louis Zirkel III (lzirkel@cleverly.com) System Admin/Programmer "We're living on the Edge of the Century" -- Styx
Louis Zirkel III wrote: >I'm having trouble understanding how to get the crypt option working in >the pg_hba.conf file. I can get cleartext passwords to be passed and they >work just fine, but I cannot figure out how in the heck to get the crypt >option working. I've looked through the FAQ and found only a reference to >the pg_hba.conf man page which isn't all that clear to me. Any help would >be greatly appreciated. This is a document I wrote some time ago on this subject. If any parts of it are no longer accurate, please let me know. ============================================================================= 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. [Debian-specific section deleted] 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. ============================================================================= -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "If it is possible, as much as it depends on you, live peaceably with all men." Romans 12:18
On Thu, 6 May 1999, Oliver Elphick wrote: > This is a document I wrote some time ago on this subject. If any parts of > it are no longer accurate, please let me know. Thank you very much. This helped to clear up quite a bit and I've now got crypt'ed passwords working. Normally I would prefer not to have to utilize such passwords, but I'm trying to setup a basic distributed "push" database where a bunch of client databases get their information from a central repository and I want the databases as secure as possible so that only the central server can update the clients. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Louis Zirkel III (lzirkel@cleverly.com) System Admin/Programmer "We're living on the Edge of the Century" -- Styx
Oliver Elphick wrote: > This is a document I wrote some time ago on this subject. If any parts of > it are no longer accurate, please let me know. > > ==================================== > 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. Hi, Oliver, I follow your instructions to do on my local machine, edit the file pg_hba.conf: local all password host all 127.0.0.1 255.255.255.255 crypt then I connect with psql, use the -u option, I got the following errors: % psql -u Username: postgres Password: User authentication failed Connection to database 'postgres' failed. User authentication failed If I don't edit the file pg_hba.conf, I can connect template1 with no problem. I have run createuser and have created the user 'postgres'. Would you like to tell me how to solve this problem, thanks. -Kevin.
Kevin Lo wrote: >Hi, Oliver, > >I follow your instructions to do on my local machine, edit the file >pg_hba.conf: > >local all password >host all 127.0.0.1 255.255.255.255 crypt > >then I connect with psql, use the -u option, I got the following errors: > >% psql -u >Username: postgres >Password: > >User authentication failed >Connection to database 'postgres' failed. >User authentication failed You must set a password for postgres before you change pg_hba.conf, using `ALTER USER postgres WITH PASSWORD xxxxx'. It sounds as if you haven't done this. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "If it is possible, as much as it depends on you, live peaceably with all men." Romans 12:18