Thread
-
Re: [ADMIN] user auth & passwords
Oliver Elphick <olly@lfix.co.uk> — 1998-06-04T03:46:44Z
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 -
Re: [ADMIN] user auth & passwords
Bruce Momjian <maillist@candle.pha.pa.us> — 1999-09-28T19:45:47Z
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, Pennsylvania 19026
-
Re: [ADMIN] user auth & passwords
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-09-29T04:51:54Z
> 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 give users passwords... Not that I know of. There is a chapter on security (security.sgml) which would be a good place for it. Oliver, do you know anyone who would be willing to merge this in? - Thomas -- Thomas Lockhart lockhart@alumni.caltech.edu South Pasadena, California -
Re: [ADMIN] user auth & passwords
Oliver Elphick <olly@lfix.co.uk> — 1999-10-04T06:00:40Z
Thomas Lockhart wrote: >> 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 give users passwords... > >Not that I know of. There is a chapter on security (security.sgml) >which would be a good place for it. Oliver, do you know anyone who >would be willing to merge this in? I'll take a look -- Vote against SPAM: http://www.politik-digital.de/spam/ ======================================== Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "For the word of God is quick, and powerful, and sharper than any twoedged sword, piercing even to the dividing asunder of soul and spirit, and of the joints and marrow, and is a discerner of the thoughts and intents of the heart." Hebrews 4:12