Bug 30592

Summary: Move borrowers.secret to a new generic credentials table
Product: Koha Reporter: Marcel de Rooy <m.de.rooy>
Component: Architecture, internals, and plumbingAssignee: Tomás Cohen Arazi (tcohen) <tomascohen>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: dcook, evelyn, jonathan.druart, kyle, martin.renvoize, nick, tomascohen
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29924
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:
Bug Depends on: 28998    
Bug Blocks:    

Description Marcel de Rooy 2022-04-22 06:04:59 UTC
This generic credentials table should allow for storing other secrets, passwords, etc. too.
Comment 1 Tomás Cohen Arazi (tcohen) 2024-08-26 13:16:35 UTC
Throwing this to start the conversation:

```sql
CREATE TABLE `patron_credentials` (
  `patron_credential_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key, Koha assigned ID number for patron credentials',
  `patron_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Foreign key to the patron the credential belongs to',
  `type` ENUM('password','two-factor') NOT NULL COMMENT 'Credential type',
  `created_on` timestamp NULL DEFAULT NULL COMMENT 'Time and date the credential was created',
  `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Time and date of the latest change on the credential',
  `value` mediumtext DEFAULT NULL COMMENT 'Credential value',
  PRIMARY KEY (`patron_credential_id`),
  CONSTRAINT `patron_id_ibfk_1` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

Thoughts?
Comment 2 Marcel de Rooy 2024-08-26 13:20:04 UTC
Type for access_token, uuid ?
Comment 3 Tomás Cohen Arazi (tcohen) 2024-08-26 13:45:37 UTC
@Marcel: That's interesting. Both `oauth_access_tokens` and `borrower_password_recovery` tables follow the same pattern. Maybe 2FA should be moved into such table; and yeah, they could converge into this table. aving `valid_until` would make sense to me.
Comment 4 David Cook 2024-08-27 02:15:18 UTC
(In reply to Marcel de Rooy from comment #2)
> Type for access_token, uuid ?

I don't think you'd need "uuid" as that's a data type, but rather something  something like "self-registration-token".

So I don't think using an ENUM for "type" is a good idea. Better to manage that in the software I think, especially as you might have Koha plugins adding credentials.