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@database01 must not collide with user01@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.