Re: db_user_namespace, md5 and changing passwords

Fernando Moreno wrote:
> Hi there, I'm going to use the db_user_namespace parameter to get a strong
> relationship between roles and databases, multiple databases -users
> included- residing in the same server without conflicts is my objective too.
>
>
> Right now I'm working on the backup process, which ideally would let me
> mirror a database and all of its users, keeping their passwords.
> user01(at)database01 must not collide with user01(at)database02, this is why I
> need db_user_namespace enabled.
>
> Just before executing pg_dump, I will create a table to store roles
> information: name and options like login, encrypted password (from
> pg_authid) and connection limit. When restoring, I'll add the
> current_database() value to the stored role names, in order to create them
> correctly. The problem is that md5sums in postgresql passwords are not
> created from "password", but "passworduser", and "user" is not likely to be
> the same because it depends directly on the database name; therefore,
> authentication will always fail even when trying with the same password.
>
> Is there a way to avoid this problem without having to reset all passwords
> or storing them in plain text?

I don't know of a way to make MD5 and db_user_namespace work cleanly so
we are considering removing db_user_namespace in 8.4.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +