annotate doc/mysql.txt @ 8411:ef1ea918b07a reauth-confirm_id

feat(security): Add user confirmation/reauth for sensitive changes Auditors can raise Reauth(reason) exception to require the user to enter a token (e.g. account password) to verify the user is performing the change. Naming is subject to change. actions.py: New ReauthAction class handler and verifyPassword() method for overriding if needed. client.py: Handle Reauth exception by calling Client:reauth() method. Default client:reauth method. Add 'reauth' action declaration. exceptions.py: Define and document Reauth exception as a subclass of RoundupCGIException. templating.py: Define method utils.embed_form_fields(). The original form making a change to the database has a lot of form fields. These need to be resubmitted to Roundup as part of the form submission that verifies the user's password. This method turns all non file form fields into type=hidden inputs. It escapes the names and values to prevent XSS. For file form fields, it base64 encodes the contents and puts them in hidden pre blocks. The pre blocks have data attributes for the filename, filetype and the original field name. (Note the original field name is not used.) This stops the file content data (maybe binary e.g. jpegs) from breaking the html page. The reauth template runs JavaScript that turns the encoded data inside the pre tags back into a file. Then it adds a multiple file input control to the page and attaches all the files to it. This file input is submitted with the rest of the fields. _generic.reauth.html (multiple tracker templates): Generates a form with id=reauth_form to: display any message from the Reauth exception to the user (e.g. why user is asked to auth). get the user's password submit the form embed all the form data that triggered the reauth recreate any file data that was submitted as part of the form and generate a new file input to push the data to the back end It has the JavaScript routine (as an IIFE) that regenerates a file input without user intervention. All the TAL based tracker templates use the same form. There is also one for the jinja2 template. The JavaScript for both is the same. reference.txt: document embed_form_fields utility method. upgrading.txt: initial upgrading docs. TODO: Finalize naming. I am leaning toward ConfirmID rather than Reauth. Still looking for a standard name for this workflow. Externalize the javascript in _generic.reauth.html to a seperate file and use utils.readfile() to embed it or change the script to load it from a @@file url. Clean up upgrading.txt with just steps to implement and less feature detail/internals. Document internals/troubleshooting in reference.txt. Add tests using live server.
author John Rouillard <rouilj@ieee.org>
date Mon, 11 Aug 2025 14:01:12 -0400
parents 3d7292d222d1
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
6167
81ae33038ec5 more index entries.
John Rouillard <rouilj@ieee.org>
parents: 6163
diff changeset
1 .. index:: mysql; deployment notes
6163
c2fd254c9257 Doc updates/indexing.
John Rouillard <rouilj@ieee.org>
parents: 5756
diff changeset
2
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
3 =============
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
4 MySQL Backend
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
5 =============
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
6
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
7 This notes detail the MySQL backend for the Roundup issue tracker.
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
8
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
9
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
10 Prerequisites
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
11 =============
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
12
1706
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
13 To use MySQL as the backend for storing roundup data, you also need
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
14 to install:
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
15
8221
3d7292d222d1 doc: update version in doc/mysql.txt; remove bdb support for mysql.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
16 1. MySQL RDBMS 8.0.11 or higher - https://www.mysql.com/. Your MySQL
3d7292d222d1 doc: update version in doc/mysql.txt; remove bdb support for mysql.
John Rouillard <rouilj@ieee.org>
parents: 7719
diff changeset
17 installation MUST support InnoDB tables.
5510
e2978ed3b550 update link to new mysqlclient module and recommend update in upgrading.txt
Christof Meerwald <cmeerw@cmeerw.org>
parents: 5137
diff changeset
18 2. Python MySQL interface - https://pypi.org/project/mysqlclient/
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
19
5137
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
20
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
21 Preparing the Database
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
22 ======================
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
23
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
24 The Roundup user expects to be able to create and drop its database
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
25 when using ``roundup_admin init``.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
26
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
27 In the examples below, replace ``roundupuser``, ``rounduppw`` and
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
28 ``roundupdb`` with suitable values.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
29
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
30 This assumes you are running MySQL on the same host as you are
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
31 running Roundup. If this is not the case, setting up remote
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
32 credentials, SSL/TLS etc. is beyond the scope of this documentation.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
33 However examples are welcome on the wiki or mailing list.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
34
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
35 These references may be helpful:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
36 https://dev.mysql.com/doc/refman/8.0/en/create-user.html and
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
37 https://dev.mysql.com/doc/refman/8.0/en/grant.html.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
38
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
39 Creating a Role/User
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
40 --------------------
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
41
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
42 The following command will create a ``roundupuser`` with the ability
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
43 to create the database::
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
44
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
45 mysql -u root -e 'CREATE USER "roundupuser"@"localhost" IDENTIFIED WITH mysql_native_password BY "rounduppw"; GRANT ALL on roundupuser.* TO "roundupuser"@"localhost";'
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
46
5137
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
47 Other Configuration
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
48 ===================
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
49
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
50 If you are indexing large documents (e.g attached file contents)
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
51 using MySQL, you may need to increase the max_allowed_packet size.
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
52 If you don't you can see the error::
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
53
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
54 'MySql Server has gone away (2006)'
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
55
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
56 To do this edit /etc/my.conf and change::
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
57
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
58 [mysqld]
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
59 max_allowed_packet = 1M
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
60
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
61 the 'max_allowed_packet' value from '1M' to '64M' or
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
62 larger.
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
63
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
64 Alternatively you can install an alternate indexer (whoosh, xapian
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
65 etc.) and force the tracker to use it by setting the ``indexer``
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
66 setting in the tracker's ``config.ini``.
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
67
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
68 This fix was supplied by telsch. See issue
5610
0df5f9eeefd4 Changed references to http://issues.roundup-tracker.org to https now
John Rouillard <rouilj@ieee.org>
parents: 5510
diff changeset
69 https://issues.roundup-tracker.org/issue2550743 for further info or if
5137
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
70 you are interested in developing a patch to roundup to help work
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
71 around this issue.
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
72
1706
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
73 Running the MySQL tests
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
74 =======================
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
75
1706
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
76 Roundup tests expect an empty MySQL database. Two alternate ways to provide
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
77 this:
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
78
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
79 1. If you have root permissions on the MySQL server, you can create
7312
fc9e16fe3991 typo fix.
John Rouillard <rouilj@ieee.org>
parents: 6167
diff changeset
80 the necessary database entries using the following SQL sequence. Use
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
81 ``mysql`` on the command line to enter::
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
82
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
83 CREATE DATABASE rounduptest;
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
84 USE rounduptest;
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
85 GRANT ALL PRIVILEGES ON rounduptest.* TO rounduptest@localhost
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
86 IDENTIFIED BY 'rounduptest';
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
87 FLUSH PRIVILEGES;
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
88
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
89 2. If your administrator has provided you with database connection info,
4513
6a32a2fb95b4 Docs update for mysql and postgresl: Mentioned test/db_test_base.py because
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 3929
diff changeset
90 see the config values in 'test/db_test_base.py'
6a32a2fb95b4 Docs update for mysql and postgresl: Mentioned test/db_test_base.py because
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 3929
diff changeset
91 about which database connection, name and user will be used.
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
92
2409
Richard Jones <richard@users.sourceforge.net>
parents: 2067
diff changeset
93 The MySQL database should not contain any tables. Tests will not
1706
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
94 drop the database with existing data.
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
95
7409
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 7312
diff changeset
96 Note that ``rounduptest`` is a well known account. You should delete
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 7312
diff changeset
97 it and the ``rounduptest`` database and create a new user/database for
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 7312
diff changeset
98 production use.
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
99
2067
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
100 Showing MySQL who's boss
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
101 ========================
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
102
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
103 If things ever get to the point where that test database is totally hosed,
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
104 just::
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
105
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
106 $ su -
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
107 # /etc/init.d/mysql stop
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
108 # rm -rf /var/lib/mysql/rounduptest
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
109 # /etc/init.d/mysql start
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
110
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
111 and all will be better (note that on some systems, ``mysql`` is spelt
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
112 ``mysqld``).
2921
ad4fb8a14a97 more doc updates, not so many TODOs any more
Richard Jones <richard@users.sourceforge.net>
parents: 2860
diff changeset
113

Roundup Issue Tracker: http://roundup-tracker.org/