db_user_namespace, md5 and changing passwords

From: "Fernando Moreno" <azazel(dot)7(at)gmail(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: db_user_namespace, md5 and changing passwords
Date: 2008-10-04 19:12:15
Message-ID: b1c45530810041212n229bcf30t975d7efeb3a2efc3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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?

Thanks in advance.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Broersma 2008-10-04 19:42:37 PG Windows Installer Feature Request
Previous Message Dave Page 2008-10-04 15:04:33 Re: failed to install posgrest

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2008-10-04 20:44:16 Obsolete functions istrue, isfalse, etc.
Previous Message David Fetter 2008-10-04 15:33:21 CYCLE and SEARCH [was Re: Common Table Expressions (WITH RECURSIVE) patch]