Importing encrypted patron passwords
I'm in the process of upgrading a library's Debian 7/Koha 3.16 setup. This installation is so old that an in-place upgrade can't be done. My plan is to first create a test VM with Debian 10 and the latest stable koha-common. Then after manually setting up various admin things (library, item types, ccode, loc, patron types), I'll import the catalog and the patron list. Finally, I'll move the resulting database to the real (live) server. The snag here is the patron passwords. I'll use a report from the SQL library to export the patron list from the old installation, but the passwords will be encrypted. My understanding is that when importing the patron list, the passwords must be unencrypted. So I'll need to remove the password column from the CSV file, and as a result, every patron password will have to be reset manually. Is there any workaround for this? I would just like to move the encrypted passwords from the old installation to the new one. I am comfortable doing some hand-crafted SQL updates to accomplish this. But before I make the attempt, is there any reason why this wouldn't work or why it's a bad idea? -- Single-handedly, I have fought my way into this hopeless mess. --Ashleigh Brilliant
You should do all you mention, but instead of creating things, you should just paste the old db and trigger the upgrade process. El sáb., 5 feb. 2022 10:31, Mark Alexander <marka@pobox.com> escribió:
I'm in the process of upgrading a library's Debian 7/Koha 3.16 setup. This installation is so old that an in-place upgrade can't be done. My plan is to first create a test VM with Debian 10 and the latest stable koha-common. Then after manually setting up various admin things (library, item types, ccode, loc, patron types), I'll import the catalog and the patron list. Finally, I'll move the resulting database to the real (live) server.
The snag here is the patron passwords. I'll use a report from the SQL library to export the patron list from the old installation, but the passwords will be encrypted. My understanding is that when importing the patron list, the passwords must be unencrypted. So I'll need to remove the password column from the CSV file, and as a result, every patron password will have to be reset manually.
Is there any workaround for this? I would just like to move the encrypted passwords from the old installation to the new one. I am comfortable doing some hand-crafted SQL updates to accomplish this. But before I make the attempt, is there any reason why this wouldn't work or why it's a bad idea?
-- Single-handedly, I have fought my way into this hopeless mess. --Ashleigh Brilliant _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
There is no way to export unencrypted passwords since the db only has a hash of it. Not the password itself. You can just do a full backup of your koha db and move it to the new server. Then run the DB upgrade process. I'll however suggest you do the backup load/upgrade before configuring the server since some of the data uses DB indexes to be identified. Regards, Alvaro |----------------------------------------------------------------------------------------| Stay safe / Cuídate/ Reste sécurisé *7* Switch off as you go / Apaga lo que no usas / Débranchez au fur et à mesure. *q *Recycle always / Recicla siempre / Recyclez toujours P Print only if absolutely necessary / Imprime solo si es necesario / Imprimez seulement si nécessaire Le sam. 5 févr. 2022 à 08:31, Mark Alexander <marka@pobox.com> a écrit :
I'm in the process of upgrading a library's Debian 7/Koha 3.16 setup. This installation is so old that an in-place upgrade can't be done. My plan is to first create a test VM with Debian 10 and the latest stable koha-common. Then after manually setting up various admin things (library, item types, ccode, loc, patron types), I'll import the catalog and the patron list. Finally, I'll move the resulting database to the real (live) server.
The snag here is the patron passwords. I'll use a report from the SQL library to export the patron list from the old installation, but the passwords will be encrypted. My understanding is that when importing the patron list, the passwords must be unencrypted. So I'll need to remove the password column from the CSV file, and as a result, every patron password will have to be reset manually.
Is there any workaround for this? I would just like to move the encrypted passwords from the old installation to the new one. I am comfortable doing some hand-crafted SQL updates to accomplish this. But before I make the attempt, is there any reason why this wouldn't work or why it's a bad idea?
-- Single-handedly, I have fought my way into this hopeless mess. --Ashleigh Brilliant _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Mark As Alvaro wrote there is no way to find out the actual password of any user when just knowing the hash (which since Koha 3.14 is (encrypted by Crypt::Eksblowfish::Bcrypt). But if for whatever reason you need to export and reimport the current passwords you can just unload the hashed passwords and insert them using SQL. If for example you have a hash like $2a$08$dsPH1AD9y9llddwE45sae.Wkfplp0a3P3GEllf0XKodDU3RR2Ue5e you may insert it as follows (don't forget to escape the dollar signs): UPDATE borrowers SET password='\$2a\$08\$dsPH1AD9y9llddwE45sae.Wkfplp0a3P3GEllf0XKodDU3RR2Ue5e' WHERE borrowernumber=... NB. In my demo installation I'm doing this every minute since in the past some pointy-headed users used to change the password of the super user so they would have the demo installation just for themselves (they thought) until the next reset of the database. Best wishes: Michael -- Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz T 0041 (0)61 261 55 61 · E mik@adminkuhn.ch · W www.adminkuhn.ch Am 06.02.22 um 01:40 schrieb Alvaro Cornejo:
There is no way to export unencrypted passwords since the db only has a hash of it. Not the password itself.
You can just do a full backup of your koha db and move it to the new server. Then run the DB upgrade process.
I'll however suggest you do the backup load/upgrade before configuring the server since some of the data uses DB indexes to be identified.
Regards,
Alvaro
|----------------------------------------------------------------------------------------| Stay safe / Cuídate/ Reste sécurisé *7* Switch off as you go / Apaga lo que no usas / Débranchez au fur et à mesure. *q *Recycle always / Recicla siempre / Recyclez toujours P Print only if absolutely necessary / Imprime solo si es necesario / Imprimez seulement si nécessaire
Le sam. 5 févr. 2022 à 08:31, Mark Alexander <marka@pobox.com> a écrit :
I'm in the process of upgrading a library's Debian 7/Koha 3.16 setup. This installation is so old that an in-place upgrade can't be done. My plan is to first create a test VM with Debian 10 and the latest stable koha-common. Then after manually setting up various admin things (library, item types, ccode, loc, patron types), I'll import the catalog and the patron list. Finally, I'll move the resulting database to the real (live) server.
The snag here is the patron passwords. I'll use a report from the SQL library to export the patron list from the old installation, but the passwords will be encrypted. My understanding is that when importing the patron list, the passwords must be unencrypted. So I'll need to remove the password column from the CSV file, and as a result, every patron password will have to be reset manually.
Is there any workaround for this? I would just like to move the encrypted passwords from the old installation to the new one. I am comfortable doing some hand-crafted SQL updates to accomplish this. But before I make the attempt, is there any reason why this wouldn't work or why it's a bad idea?
-- Single-handedly, I have fought my way into this hopeless mess. --Ashleigh Brilliant _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Excerpts from Alvaro Cornejo's message of 2022-02-05 19:40:08 -0500:
I'll however suggest you do the backup load/upgrade before configuring the server since some of the data uses DB indexes to be identified.
Let me see if I understand this. Is this the order of steps you're recommending? 0. install Debian 10 on new server 1. apt-get install koha-common 2. copy database from the old server (mysqldump args >dump.sql) and load it on new server (mysql args <dump.sql). 3. run updatedatabase.pl 4. run Koha's web installer I worry about step 3 because I don't understand how updatedatabase.pl knows how to access the old database. In the 3.16 installation I'm working on, the database is called "koha", but in the latest Koha, the database is normally called "koha_INSTANCE". Is this a problem, and if so, what do I do about? -- I work very hard. Please don't expect me to think as well. --Ashleigh Brilliant
You will create a new instance, say 'name' like this: $ koha-create --create-db name You will then load your backup on the empty db that was created: $ koha-mysql name < backup.sql About step 3, you just need to try. It should work, and we can help with the data errors. Looking forward to your comments. El sáb., 5 feb. 2022 22:13, Mark Alexander <marka@pobox.com> escribió:
I'll however suggest you do the backup load/upgrade before configuring
Excerpts from Alvaro Cornejo's message of 2022-02-05 19:40:08 -0500: the
server since some of the data uses DB indexes to be identified.
Let me see if I understand this. Is this the order of steps you're recommending?
0. install Debian 10 on new server 1. apt-get install koha-common 2. copy database from the old server (mysqldump args >dump.sql) and load it on new server (mysql args <dump.sql). 3. run updatedatabase.pl 4. run Koha's web installer
I worry about step 3 because I don't understand how updatedatabase.pl knows how to access the old database. In the 3.16 installation I'm working on, the database is called "koha", but in the latest Koha, the database is normally called "koha_INSTANCE". Is this a problem, and if so, what do I do about?
-- I work very hard. Please don't expect me to think as well. --Ashleigh Brilliant _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
https://libpowertech.blogspot.com/2022/02/installing-multiple-instances-of-k... On Sun, 6 Feb 2022, 7:26 am Tomas Cohen Arazi, <tomascohen@gmail.com> wrote:
You will create a new instance, say 'name' like this:
$ koha-create --create-db name
You will then load your backup on the empty db that was created:
$ koha-mysql name < backup.sql
About step 3, you just need to try. It should work, and we can help with the data errors.
Looking forward to your comments.
El sáb., 5 feb. 2022 22:13, Mark Alexander <marka@pobox.com> escribió:
I'll however suggest you do the backup load/upgrade before configuring
Excerpts from Alvaro Cornejo's message of 2022-02-05 19:40:08 -0500: the
server since some of the data uses DB indexes to be identified.
Let me see if I understand this. Is this the order of steps you're recommending?
0. install Debian 10 on new server 1. apt-get install koha-common 2. copy database from the old server (mysqldump args >dump.sql) and load it on new server (mysql args <dump.sql). 3. run updatedatabase.pl 4. run Koha's web installer
I worry about step 3 because I don't understand how updatedatabase.pl knows how to access the old database. In the 3.16 installation I'm working on, the database is called "koha", but in the latest Koha, the database is normally called "koha_INSTANCE". Is this a problem, and if so, what do I do about?
-- I work very hard. Please don't expect me to think as well. --Ashleigh Brilliant _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Thanks for the help. The koha-upgrade-schema step went well until this: Upgrade to 3.19.00.037 done (Bug 13810: Change collate for tagsubfield (utf8_bin)) {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Incorrect datetime value: '0000-00-00 00:00:00' for column `koha_cfl`.`virtualshelves`.`created_on` at row 1 at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x55f9a91d9528), "DBI Exception: DBD::mysql::db do failed: Incorrect datetime v"...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x55f9ae477430), "DBI Exception: DBD::mysql::db do failed: Incorrect datetime v"...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1501 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Incorrect datetime value: '0000-00-"..., DBI::db=HASH(0x55f9ae93d640), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 10391 Here are the relevant lines from updatedatabase.pl: $DBversion = "3.19.00.038"; if ( CheckVersion($DBversion) ) { $dbh->do(q| ALTER TABLE virtualshelves ADD COLUMN created_on DATETIME NOT NULL AFTER lastmodified |); I tried running this ALTER statement manually, and it succeeded. So the problem does not seem to be MariaDB. -- If you're careful enough, nothing bad or good will ever happen to you. --Ashleigh Brilliant
That dates are problematic. Solve that and re run the script. It is designed to continue where it left. El dom., 6 feb. 2022 16:06, Mark Alexander <marka@pobox.com> escribió:
Thanks for the help. The koha-upgrade-schema step went well until this:
Upgrade to 3.19.00.037 done (Bug 13810: Change collate for tagsubfield (utf8_bin)) {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Incorrect datetime value: '0000-00-00 00:00:00' for column `koha_cfl`.`virtualshelves`.`created_on` at row 1 at /usr/share/perl5/DBIx/Class/Schema.pm line 1118.
DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x55f9a91d9528), "DBI Exception: DBD::mysql::db do failed: Incorrect datetime v"...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113
DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x55f9ae477430), "DBI Exception: DBD::mysql::db do failed: Incorrect datetime v"...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1501 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Incorrect datetime value: '0000-00-"..., DBI::db=HASH(0x55f9ae93d640), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/ updatedatabase.pl line 10391
Here are the relevant lines from updatedatabase.pl:
$DBversion = "3.19.00.038"; if ( CheckVersion($DBversion) ) { $dbh->do(q| ALTER TABLE virtualshelves ADD COLUMN created_on DATETIME NOT NULL AFTER lastmodified |);
I tried running this ALTER statement manually, and it succeeded. So the problem does not seem to be MariaDB.
-- If you're careful enough, nothing bad or good will ever happen to you. --Ashleigh Brilliant _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
I've managed to get the database upgrade to succeed, but it failed at four different places: * 3.19.00.037 * 3.21.00.027 * 3.23.00.041 * 16.06.00.050 The fixes involved manual updates to the database, or commenting out lines in updatedatabase.pl, or both. Thanks to all those who offered help. -- By doing just a little every day, I can gradually let the task completely overwhelm me. --Ashleigh Brilliant
Glad to hear!!!!! El dom., 6 feb. 2022 17:40, Mark Alexander <marka@pobox.com> escribió:
I've managed to get the database upgrade to succeed, but it failed at four different places:
* 3.19.00.037 * 3.21.00.027 * 3.23.00.041 * 16.06.00.050
The fixes involved manual updates to the database, or commenting out lines in updatedatabase.pl, or both.
Thanks to all those who offered help.
-- By doing just a little every day, I can gradually let the task completely overwhelm me. --Ashleigh Brilliant _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
participants (5)
-
Alvaro Cornejo -
Mark Alexander -
MASTeR Library -
Michael Kuhn -
Tomas Cohen Arazi