Обсуждение: Move passwords between databases

Hi,

Using Postgresql 7.2.3 on Linux, is there an easy way to transfer user
passwords from one database to another?

I have an application that creates data (and users) centrally and then
distributes them to remote databases.  I can transfer everything else,
but do not know how to transfer MD5 passwords.

As a last resort the application could store the passwords in
plain-text in the central database and then use a remote "alter user
with encrypted password..." command to update the user's password, but
I'd rather avoid that if there is any alternative available.

I tried an "update pg_shadow set passwd=... where usename=..."

Regards,

-- Raju
--
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
       GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968 D0EF CC68 D17F
                      It is the mind that moves

Erhm, the last line of the earlier mail should read:

I tried an "update pg_shadow set passwd=... where usename=..." but
that doesn't do the trick: Postgresql does not authenticate the user.

Regards,

-- Raju
--
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
       GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968 D0EF CC68 D17F
                      It is the mind that moves

Raj Mathur <raju@linux-delhi.org> writes:
> Using Postgresql 7.2.3 on Linux, is there an easy way to transfer user
> passwords from one database to another?

You could look at what pg_dumpall --globals-only does.

> I have an application that creates data (and users) centrally and then
> distributes them to remote databases.  I can transfer everything else,
> but do not know how to transfer MD5 passwords.

AFAIK you can just do it.  The CREATE or ALTER USER command should
recognize the password as already encrypted and not munge it.  Are
you sure you are using the correct pg_hba.conf setup on the destination
system?

            regards, tom lane

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Raj Mathur <raju@linux-delhi.org> writes:
    >> Using Postgresql 7.2.3 on Linux, is there an easy way to
    >> transfer user passwords from one database to another?

    Tom> You could look at what pg_dumpall --globals-only does.

Tried that, doesn't seem to work (this is on a single system):

mdu=# alter user raju with encrypted password 'raju';
ALTER USER
mdu=# select passwd from pg_shadow where usename='raju';
               passwd
-------------------------------------
 md5ef70c430d5ed1ed52bd2ae960bb8ebe4
(1 row)

mdu=# create user xxx with password 'md5ef70c430d5ed1ed52bd2ae960bb8ebe4';
CREATE USER
mdu=# \q
[raju@mail raju]$ psql mdu xxx
Password: <enter 'raju' here>
psql: FATAL 1:  Password authentication failed for user "xxx"

    >> I have an application that creates data (and users) centrally
    >> and then distributes them to remote databases.  I can transfer
    >> everything else, but do not know how to transfer MD5 passwords.

    Tom> AFAIK you can just do it.  The CREATE or ALTER USER command
    Tom> should recognize the password as already encrypted and not
    Tom> munge it.  Are you sure you are using the correct pg_hba.conf
    Tom> setup on the destination system?

The entry is the same in both:

local     all                   md5

The application updating the passwords is running locally on both
systems.

Any clues?

Regards,

-- Raju
--
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
       GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968 D0EF CC68 D17F
                      It is the mind that moves

Raj Mathur <raju@linux-delhi.org> writes:
> mdu=# alter user raju with encrypted password 'raju';
> ALTER USER
> mdu=# select passwd from pg_shadow where usename='raju';
>                passwd
> -------------------------------------
>  md5ef70c430d5ed1ed52bd2ae960bb8ebe4
> (1 row)

> mdu=# create user xxx with password 'md5ef70c430d5ed1ed52bd2ae960bb8ebe4';
> CREATE USER

That's not going to work, because the user name is included into the
password encryption algorithm, so two different usernames with the same
cleartext password are going to have two different encrypted passwords.
(This is a feature, not a bug.)

You can transfer the same usernames with the same passwords from one
system to another using the method shown above.  You cannot assign one
user's password to another username this way.

            regards, tom lane

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Raj Mathur <raju@linux-delhi.org> writes:
    >> mdu=# alter user raju with encrypted password 'raju'; ALTER
    >> USER mdu=# select passwd from pg_shadow where usename='raju';
    >> passwd
    >> -------------------------------------
    >> md5ef70c430d5ed1ed52bd2ae960bb8ebe4 (1 row)

    >> mdu=# create user xxx with password
    >> 'md5ef70c430d5ed1ed52bd2ae960bb8ebe4'; CREATE USER

    Tom> That's not going to work, because the user name is included
    Tom> into the password encryption algorithm, so two different
    Tom> usernames with the same cleartext password are going to have
    Tom> two different encrypted passwords.  (This is a feature, not a
    Tom> bug.)

Oooh, that's what I was missing then.

    Tom> You can transfer the same usernames with the same passwords
    Tom> from one system to another using the method shown above.  You
    Tom> cannot assign one user's password to another username this
    Tom> way.

Great, it works now.

Thanks!  Both PostgreSQL and you guys rock!

Regards,

-- Raju
--
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
       GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968 D0EF CC68 D17F
                      It is the mind that moves