> On 2/13/2006 9:57 AM, Christopher Browne wrote:
>
>> A way to do this that would be, oh, call it "forcibly robust" would be
>> for each SYNC to check to see if pg_shadow had changed, and update the
>> user table correspondingly.
>>
>> In practice, it's probably good enough to have some script that runs
>> every so often to look for changes.  I know we don't modify pg_shadow
>> on our systems so often as every week; polling for it once a day would
>> likely suffice, for us...
>
> A DB user per application user is IMHO broken design. That said, it
> could be done the other way around. Have a regular, replicated table
> with the same structure as pg_shadow. Use INSERT, UPDATE and DELETE on
> that table and have triggers that duplicate those actions into
> pg_shadow. The triggers need to be configured in slony as well so that
> replicating the table will have the same effect through triggers on the
> subscribers.
>
> Keep in mind that this whole thing will only work if the entire instance
> is for one single database. pg_shadow is a shared system catalog.
> Another reason why it isn't really good to have a user per application 
> user.
>

Thanks for the input all round. I wrote a script to dump the pg_shadow file 
and to
convert the output to SQL. I ran this through pgsql on my new server. I 
found
that inserting the encrypted password from the pg_shadow works on the new 
server.
My users are not created frequently, so I expect to use this approach
manually once per month.

In defense of my dbuser per application user design, I made a decision to 
use
the database security system as the application security system for two 
reasons:

1. I prefer to have a DBA responsible for user management than to create
a new process and assign responsibility to another person who has no 
appreciation
for the responsibility that comes with this. Clearly whether this approach 
is
applicable in another context will depend on the competence of the
target organisation.

2. Ultimately, I have to connect my user to the database. To do this 
securely
involves putting in place a middleware layer or hiding the real connection 
to
the database in some arbitrary way. The db user=application user mapping is
at least transparent.

Clearly these are design decisions and perhaps personal choices.