M-Labs
/
it-infra
8
0
Fork 4
Code Issues 8 Pull Requests Releases Wiki Activity

RT + PostgreSQL nonsense #32

New Issue
Closed
opened 2023-12-16 13:45:00 +08:00 by sb10q · 5 comments
sb10q commented 2023-12-16 13:45:00 +08:00
Owner
  • RT keeps showing "Your query exceeded the maximum run time and was stopped. Try modifying your query to improve the performance or contact your RT admin."
  • Database schema cannot be upgraded.
# rt-setup-database --action upgrade --prompt-for-dba-password --upgrade-from 5.0.3
In order to create or update your RT database, this script needs to connect to your  Pg instance on localhost (port '') as postgres
Please specify that user's database password below. If the user has no database
password, just press return.

Password: 
Working with:
Type:	Pg
Host:	localhost
Port:	
Name:	rt5
User:	rt_user
DBA:	postgres
Enter RT version you're upgrading from: 
Going to apply following upgrades:
* 5.0.4
* 5.0.5

Enter RT version if you want to stop upgrade at some point,
  or leave it blank if you want apply above upgrades: 

IT'S VERY IMPORTANT TO BACK UP BEFORE THIS STEP

Proceed [y/N]:y
[155586] [Sat Dec 16 05:42:31 2023] [warning]: DBI connect('dbname=rt5;host=localhost;client_encoding=UTF8','rt_user',...) failed: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "rt_user" at /nix/store/8yi5m8cp8fls15l7f0g2asvmn7d1ycv1-rt-perl-deps/lib/perl5/site_perl/5.38.0/DBIx/SearchBuilder/Handle.pm line 117. (/nix/store/afz9z2iczjx13i66ymwwinwp2zvgc1ck-perl-5.38.0/lib/perl5/5.38.0/Carp.pm:293)
[155586] [Sat Dec 16 05:42:31 2023] [critical]: Connect Failed connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "rt_user"
 at /nix/store/nfbvyalc0bb8pp7mxs8l0jwczlhxfd2j-rt-5.0.5/bin/../lib/RT.pm line 222. (/nix/store/nfbvyalc0bb8pp7mxs8l0jwczlhxfd2j-rt-5.0.5/bin/../lib/RT.pm:409)
Connect Failed connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "rt_user"
 at /nix/store/nfbvyalc0bb8pp7mxs8l0jwczlhxfd2j-rt-5.0.5/bin/../lib/RT.pm line 222.

Dec 16 13:42:31 nixbld postgres[155887]: [155887] FATAL:  password authentication failed for user "rt_user"
Dec 16 13:42:31 nixbld postgres[155887]: [155887] DETAIL:  Password does not match for user "rt_user".
Dec 16 13:42:31 nixbld postgres[155887]:         Connection matched pg_hba.conf line 7: "host  all all ::1/128      md5"

Obviously the password supplied to rt-setup-database is correct.

* RT keeps showing "Your query exceeded the maximum run time and was stopped. Try modifying your query to improve the performance or contact your RT admin." * Database schema cannot be upgraded. ``` # rt-setup-database --action upgrade --prompt-for-dba-password --upgrade-from 5.0.3 In order to create or update your RT database, this script needs to connect to your Pg instance on localhost (port '') as postgres Please specify that user's database password below. If the user has no database password, just press return. Password: Working with: Type: Pg Host: localhost Port: Name: rt5 User: rt_user DBA: postgres Enter RT version you're upgrading from: Going to apply following upgrades: * 5.0.4 * 5.0.5 Enter RT version if you want to stop upgrade at some point, or leave it blank if you want apply above upgrades: IT'S VERY IMPORTANT TO BACK UP BEFORE THIS STEP Proceed [y/N]:y [155586] [Sat Dec 16 05:42:31 2023] [warning]: DBI connect('dbname=rt5;host=localhost;client_encoding=UTF8','rt_user',...) failed: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "rt_user" at /nix/store/8yi5m8cp8fls15l7f0g2asvmn7d1ycv1-rt-perl-deps/lib/perl5/site_perl/5.38.0/DBIx/SearchBuilder/Handle.pm line 117. (/nix/store/afz9z2iczjx13i66ymwwinwp2zvgc1ck-perl-5.38.0/lib/perl5/5.38.0/Carp.pm:293) [155586] [Sat Dec 16 05:42:31 2023] [critical]: Connect Failed connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "rt_user" at /nix/store/nfbvyalc0bb8pp7mxs8l0jwczlhxfd2j-rt-5.0.5/bin/../lib/RT.pm line 222. (/nix/store/nfbvyalc0bb8pp7mxs8l0jwczlhxfd2j-rt-5.0.5/bin/../lib/RT.pm:409) Connect Failed connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "rt_user" at /nix/store/nfbvyalc0bb8pp7mxs8l0jwczlhxfd2j-rt-5.0.5/bin/../lib/RT.pm line 222. ``` ``` Dec 16 13:42:31 nixbld postgres[155887]: [155887] FATAL: password authentication failed for user "rt_user" Dec 16 13:42:31 nixbld postgres[155887]: [155887] DETAIL: Password does not match for user "rt_user". Dec 16 13:42:31 nixbld postgres[155887]: Connection matched pg_hba.conf line 7: "host all all ::1/128 md5" ``` Obviously the password supplied to rt-setup-database is correct.
esavkin was assigned by sb10q 2023-12-16 13:45:00 +08:00
esavkin commented 2024-01-17 12:50:46 +08:00
Owner

Appeared that user password was taken from config file, so without it it used the default rt_pass.
These steps worked:

git clone https://github.com/bestpractical/rt.git # needed for migration files
cd rt
git checkout rt-5.0.5 # desired release
nix-shell -p rt
nano RT_SiteConfig.pm

Insert following and save:

use utf8;
Set($DatabaseType, 'Pg');
Set($DatabaseHost, 'localhost');
Set($DatabaseUser, 'rt_user');
Set($DatabaseName, 'rt5');
Set($DatabasePassword, 'INSERT_YOUR_USER_PASSWORD_HERE');
1;

And then:

pg_dump -U rt_user -h localhost rt5 -W > rt_dump # backup https://www.postgresql.org/docs/current/backup-dump.html
export RT_SITE_CONFIG=/path/to/RT_SiteConfig.pm
rt-setup-database --action upgrade --upgrade-from 5.0.3 --dba <root_user> --prompt-for-dba-password

And then enter the password from <root_user>, which should have Create role, Create DB permissions (database administrator, not necessarily root or superuser). Users can be received as:

psql -U rt_user -h localhost rt5 -W 
...
rt5=> \du
Appeared that user password was taken from config file, so without it it used the default `rt_pass`. These steps worked: ```sh git clone https://github.com/bestpractical/rt.git # needed for migration files cd rt git checkout rt-5.0.5 # desired release nix-shell -p rt nano RT_SiteConfig.pm ``` Insert following and save: ```perl use utf8; Set($DatabaseType, 'Pg'); Set($DatabaseHost, 'localhost'); Set($DatabaseUser, 'rt_user'); Set($DatabaseName, 'rt5'); Set($DatabasePassword, 'INSERT_YOUR_USER_PASSWORD_HERE'); 1; ``` And then: ```sh pg_dump -U rt_user -h localhost rt5 -W > rt_dump # backup https://www.postgresql.org/docs/current/backup-dump.html export RT_SITE_CONFIG=/path/to/RT_SiteConfig.pm rt-setup-database --action upgrade --upgrade-from 5.0.3 --dba <root_user> --prompt-for-dba-password ``` And then enter the password from `<root_user>`, which should have `Create role, Create DB` permissions (database administrator, not necessarily root or superuser). Users can be received as: ```sh psql -U rt_user -h localhost rt5 -W ... rt5=> \du ```
sb10q commented 2024-01-17 13:48:39 +08:00
Poster
Owner

Appeared that user password was taken from config file, so without it it used the default rt_pass.

Sounds like a RT bug. Did you report it?

> Appeared that user password was taken from config file, so without it it used the default rt_pass. Sounds like a RT bug. Did you report it?
esavkin commented 2024-01-17 14:12:26 +08:00
Owner

Sounds like a RT bug.

Not really, the migration script is separate from rt service and no wonder why in user shell it could not find proper config, if there is no rt config path variable set.
Also the rt service itself doesn't have proper permissions to do the migration (and should not).
Was it different before?

> Sounds like a RT bug. Not really, the migration script is separate from rt service and no wonder why in user shell it could not find proper config, if there is no rt config path variable set. Also the rt service itself doesn't have proper permissions to do the migration (and should not). Was it different before?
sb10q commented 2024-01-17 14:13:02 +08:00
Poster
Owner

It asks for a password on the prompt and then ignores it?

It asks for a password on the prompt and then ignores it?
esavkin commented 2024-01-17 14:23:06 +08:00
Owner

It asks for a password on the prompt and then ignores it?

No, it asks for a password of DB admin (DBA) user (should able to create tables/roles), but it also needs rt user and password, which is stored in config.
I think they thought that we would do the migration from the same user/environment as starting the service, and enter a DBA password only on such occasions, and do not store it in configs.

Though actually interesting why do they need both user and admin accounts.

> It asks for a password on the prompt and then ignores it? No, it asks for a password of DB admin (DBA) user (should able to create tables/roles), but it also needs rt user and password, which is stored in config. I think they thought that we would do the migration from the same user/environment as starting the service, and enter a DBA password only on such occasions, and do not store it in configs. Though actually interesting why do they need both user and admin accounts.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
master
force-ssl-main-website
Labels
Clear labels
No items
No Label
Milestone
Clear milestone
No items
No Milestone
Assignees
Clear assignees
No Assignees
esavkin
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: M-Labs/it-infra#32
There is no content yet.
Delete Branch "%!s(<nil>)"

Deleting a branch is permanent. Although the deleted branch may exist for a short time before cleaning up, in most cases it CANNOT be undone. Continue?