Обсуждение: password authentication with external passwd file

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hello all.

I am currently running postgresql-7.2, and I have my pg_hba.conf
configured to use password authentication with external passwd files.
eg:

     pg_hba.conf:
     local db1 all password db1.pwd
     host db1 all 127.0.0.1 255.255.255.255 password db2.pwd
     local db2 all password db1.pwd
     host db2 all 127.0.0.1 255.255.255.255 password db2.pwd

     db1.pwd:
     postgres:bwoMq9dRTqHlI
     user1:WaC3rSwu.1V2A

     db1.pwd:
     postgres:MT0RIEZ2rN.IY
     user1:1U6pT5d0T/fcY


I would like to upgrade to postgresql-8.0, but as far as I can tell, this
feature is not present in 8.0 - indeed, it looks like 7.2 may be the last
version with that feature.

I would like to know first, if I'm mistaken, or if external passwd files
are now specified in some other way, or must be enabled at compile time or
something like that; ie, am I doing something wrong?


If this feature is indeed no longer supported, is there some other way
that I can allow the same username to have different passwords on
different databases?

The background is that this is a hosting environment, and each customer is
given one database to use however they want; different human users should
be able to create database users with the same username without
accidentally granting each other rights on their own databases.  We
currently do this by maintaining a password file per user/database, and
letting users put whatever usernames and passwords they want into the
password file for their database.


  -Jason
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (FreeBSD)
Comment: See https://private.idealab.com/public/jason/jason.gpg

iD8DBQFDECTdswXMWWtptckRAhcgAKCC6X3r+tTyhR4beK8JHqEc1KUAzACffPxG
CZWF0SSSWyRFV71D4DcOeTY=
=SMsw
-----END PGP SIGNATURE-----

On Sat, Aug 27, 2005 at 01:31:25AM -0700, Jason Stone wrote:
> I would like to know first, if I'm mistaken, or if external passwd files
> are now specified in some other way, or must be enabled at compile time or
> something like that; ie, am I doing something wrong?

The 7.3 Release Notes have the following items:

* Remove secondary password file capability and pg_password utility (Bruce)

* Add variable db_user_namespace for database-local user names (Bruce)

> If this feature is indeed no longer supported, is there some other way
> that I can allow the same username to have different passwords on
> different databases?

See the documentation for db_user_namespace:

http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG-CONNECTION-SECURITY

> The background is that this is a hosting environment, and each customer is
> given one database to use however they want; different human users should
> be able to create database users with the same username without
> accidentally granting each other rights on their own databases.  We
> currently do this by maintaining a password file per user/database, and
> letting users put whatever usernames and passwords they want into the
> password file for their database.

If you use db_user_namespace then you might have to provide an
interface for users to manage the user names in their databases.
One possibility would be to write a set of SECURITY DEFINER functions
that execute the correct CREATE USER, DROP USER, etc. commands.

--
Michael Fuhr

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


>> I would like to know first, if I'm mistaken, or if external passwd files
>> are now specified in some other way, or must be enabled at compile time or
>> something like that; ie, am I doing something wrong?
>
> The 7.3 Release Notes have the following items:
>
> * Remove secondary password file capability and pg_password utility (Bruce)
> * Add variable db_user_namespace for database-local user names (Bruce)
> [...]
> See the documentation for db_user_namespace:

hm.  well, I can probably hack this to work, but I notice that the
documentations also says:

     Note:  This feature is intended as a temporary measure until a
     complete solution is found. At that time, this option will be
     removed.

so... how temporary is temporary?  is it expected that the complete
solution will be at all similar?  I don't want to spend a lot of time
building on this if the next upgrade will make it all obsolete again....

out of curiosity, why was the secondary password file capability removed?
I really liked the simplicity of being able to manage users completely
outside of the database, and to be able to do maintainence on the user
accounts without the database's even running.

anyway, thanks for the pointer - I'll start considering how I can use
db_user_namespace.


  -Jason
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (FreeBSD)
Comment: See https://private.idealab.com/public/jason/jason.gpg

iD8DBQFDE60bswXMWWtptckRAnGkAKDg96CVnZ55Oe0VANp2gDl1bojs5gCgltE4
2t985/6iyokjujrL9OUtHts=
=U30b
-----END PGP SIGNATURE-----

Jason Stone <pgsql-admin@dfmm.org> writes:
> out of curiosity, why was the secondary password file capability removed?

You'd have to look into the pghackers archives for details, but my
recollection is that it didn't play nicely with other features that we
wanted to add.

> I really liked the simplicity of being able to manage users completely
> outside of the database, and to be able to do maintainence on the user
> accounts without the database's even running.

This is rather an overstatement.  You were able to change their passwords
externally, perhaps, but that's a pretty limited form of "management".

            regards, tom lane