542507 - [PostgreSQL] The db_user sometimes cannot connect to the "template1" database

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[Christoph Göhre]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; de; rv:1.9.1.6) Gecko/20091216 Firefox/2.0.0.3 (like Firefox/3.5.6; Debian-3.5.6-1)
Build Identifier: [Postgresql]: database creation in checksetup.pl didn't work

Running checksetup.pl with Postgresql as database backend and resticted database user will run into an error. checksetup.pl want to use database template1.

Reproducible: Always

Steps to Reproduce:
1. add a new postgresql user and a empty database
2. adjust ACL to only access these database
3. run checksetup.pl initial and adapt localconfig for your PSQL setup
4. run checksetup.pl again to initial database
Actual Results:  
There was an error connecting to PostgreSQL:

    FATAL:  no pg_hba.conf entry for host "[IP]", user "bugzilla", database "template1", SSL off

Expected Results:  
Should import database schema into database 'bugzilla', but it use database 'template1'. $db_name in localconfig is set to 'bugzilla'

Comment 1

[Christoph Göhre]

Attachment: use $lc->{db_name} to specify database name

Comment 2

[Max Kanat-Alexander]

Comment on attachment 423794 [details] [diff] [review]
use $lc->{db_name} to specify database name

Thanks for the patch! The code that you're modifying affects all databases, not just PostgreSQL. The code that you should instead modify would be in Bugzilla::DB::Pg. I think that there has been some discussion about this issue on another bug already, I'll see if I can find it.

Comment 3

[Max Kanat-Alexander]

Well, I can't find the other bug, so I'll confirm this one for now.

The problem is that in order to create a database, we have to connect to some database, and in PostgreSQL, "template1" is that database. It's true that once the database exists, we can connect to it, but we can't determine if the database exists without connecting to some database.

Comment 4

[GitHub Autolander Bot]

Attachment: [bugzilla] dylanwh:bug-542507 > bugzilla:master

Comment 5

[Dylan Hardison [:dylan] (he/him)]

Comment on attachment 8767682 [details] [review]
[bugzilla] dylanwh:bug-542507 > bugzilla:master

This is another bug that bites you when trying to use heroku. It's pretty common to not be allowed to access template1, honestly,
and it isn't clear there is a reason to force the db_name to '' in _connect. so let's not do either of those things.

Comment 6

[David Lawrence [:dkl]]

Comment on attachment 8767682 [details] [review]
[bugzilla] dylanwh:bug-542507 > bugzilla:master

r=dkl but with one caveat: we will need to update the installation docs to not that the database will need to be created manually first before running checksetup.pl. Before it defaulted to template1 so checksetup would run without issue. Now it will error saying the database 'bugs' (whatever is configured in localconfig) does not exist. Maybe the docs should be fixed as part of this bug.

Comment 7

[Dylan Hardison [:dylan] (he/him)]

I'll fix the docs in this bug too. Good call.

Comment 8

[Dylan Hardison [:dylan] (he/him)]

Does this suffice? 
https://github.com/bugzilla/bugzilla/pull/10/commits/22501ee8bdb332705f8ca5297618ca09681c035f

Comment 9

[David Lawrence [:dkl]]

(In reply to Dylan William Hardison [:dylan] from comment #8)
> Does this suffice? 
> https://github.com/bugzilla/bugzilla/pull/10/commits/
> 22501ee8bdb332705f8ca5297618ca09681c035f

LGTM. Thanks!

Comment 10

[Dylan Hardison [:dylan] (he/him)]

To [e-mail]:dylanwh/bugzilla.git
 + 22501ee...bd54cb4  master -> master

Comment 11

[Frédéric Buclin]

This patch breaks all DB servers!! Now MySQL (and probably Oracle too) refuses to create a new database!

Reading ./localconfig...
There was an error connecting to MySQL:

    Unknown database 'bugs_tmp2'


MySQL has no problems to create new databases itself, so there is no reason to break this feature for MySQL installations. Even with PostgreSQL, there is no reason to force *all admins* to create the DB first when Bugzilla is able to do it itself. For many of us, Bugzilla is able to access template1, else you would have seen *tons* of such bug reports about PostgreSQL installations, or at least a lot of people in the CC list and "me too" comments.

The right fix is to try to access template1, and if it's not accessible, try to access $db_name directly. That's why _connect() is in an eval {}.

Please backout this patch!

Comment 12

[Dylan Hardison [:dylan] (he/him)]

I will not back out this patch. I will accept (or probably even write) a patch that gracefully fails a test write to template1 or a no-db-name connection to mysql. The previous behavior prevented the installation of bugzilla on an environment where the db user wasn't an admin.

In fact, I will even file such a bug.

Comment 13

[Frédéric Buclin]

:command:`createdb -U postgress -O bugs bugs`

typo: s/postgress/postgres/ Could you fix that, please?


For MySQL, you didn't need to be an admin. You had to give explicit write access to a given DB, see http://bugzilla.readthedocs.io/en/latest/installing/mysql.html#add-a-user. In all cases must the new DB be created, so you cannot bypass the intervention of an admin.

Comment 14

[Dylan Hardison [:dylan] (he/him)]

typo fixed. I also have a patch for bug 1311394 soon, which also fixes the error message when you cannot connect to a db:
it differentiates "missing db driver modules" from "invalid driver name" and "errors during object instantiation".