https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43407 Bug ID: 43407 Summary: Database permissions, different users and "schemas" Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Database Assignee: koha-bugs@lists.koha-community.org Reporter: mark@hofstetter.at QA Contact: testopia@bugs.koha-community.org Target Milestone: --- A standard Koha instance defines one database account in koha-conf.xml. Koha::Database uses this account for normal DBI and DBIx::Class connections. The Debian installation creates this account with ALL PRIVILEGES on the complete instance database. Consequently, the same database identity is used by the OPAC, staff interface, background workers, command-line tools, database updates, reports, and plugins. It can read and modify operational data, but can also create, alter, and drop database objects. The same logical database/schema contains data with very different ownership and lifecycle requirements: * primary transactional and catalogue data; * configuration and mapping data; * denormalised or generated data; * transient state such as sessions, saved report results, tmp_holdsqueue, and zebraqueue; * plugin-owned tables and indexes. The problem is not merely that Koha uses one database. The problem is the assumption that one highly privileged service account and one schema should be used for every workload. Separate database identities could materially improve security by applying the principle of least privilege. For example: * An OPAC database account could have SELECT access to catalogue, availability, and public configuration data, no access to unrelated tables such as acquisitions or administrative plugin data, and narrowly scoped INSERT or UPDATE permissions only for supported patron operations such as holds, renewals, suggestions, tags, and account changes. It would have no DDL privileges. * A reports account could be strictly read-only. Preferably it would query an allowlisted of tables/views instead of receiving SELECT access to every table. This would also permit sensitive patron, authentication, or financial columns to be excluded or masked. * A staff runtime account could receive the DML permissions required for normal staff operations but no CREATE, ALTER, or DROP privileges. * A dedicated migration account could hold DDL privileges and only be made available while installing or upgrading Koha. Web applications and background workers would not have access to these credentials. * Background workers, search components, and plugins could use separate accounts with permissions limited to their queues, indexes, caches, or plugin-owned schemas. This would reduce the impact of a compromised component. For example, an OPAC vulnerability would not automatically provide access to every Koha table or permit schema changes. A reporting error or malicious report could not modify production records. Separate identities would also improve auditing, credential rotation, and attribution of database activity. Database permissions would not replace Koha's application-level authorization. Some OPAC features require writes, and permissions would need to reflect enabled features. The initial implementation could therefore be optional and use documented privilege profiles rather than attempting an immediate mandatory split. A possible incremental improvement would be support for named database connections in koha-conf.xml. Individual interfaces and components could select an appropriate connection, while the current single-account configuration remains the compatibility default. Such support would need defined behaviour for installation, upgrades, transactions, connection selection, backups/restores, testing, reporting, and deployments where cross-schema foreign keys are unavailable or undesirable. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.