[Bug 16690] New: Installing DB on remote server fails without *.* permissions

classic Classic list List threaded Threaded
39 messages Options
12
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] New: Installing DB on remote server fails without *.* permissions

bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

            Bug ID: 16690
           Summary: Installing DB on remote server fails without *.*
                    permissions
 Change sponsored?: ---
           Product: Koha
           Version: master
          Hardware: All
                OS: All
            Status: NEW
          Severity: enhancement
          Priority: P5 - low
         Component: Installation and upgrade (command-line installer)
          Assignee: [hidden email]
          Reporter: [hidden email]
        QA Contact: [hidden email]

KohaDB: 192.168.71.250

> CREATE DATABASE koha_ils;
> GRANT ALL ON koha_ils.* TO 'kohauser'@'192.168.71.251' IDENTIFIED BY 'kohapass';
> FLUSH PRIVILEGES;
> QUIT;

KohaApp: 192.168.71.251
Do an install.
Hack the kohaconf username, hostname, passwords to match.
Try to run the web install steps.
You will get told you don't have enough permissions.
Why? Because 'hostname' is 192.168.71.250 to connect to the DB, but the app
server is 192.168.71.251, and you should be checking against those GRANTS in
installer/install.pl around line 116.

Hacked in a <webserver> tag to the kohaconf
and used that value instead in the script. Everything works.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|enhancement                 |minor

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |Needs Signoff
   Patch complexity|---                         |Medium patch

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

--- Comment #1 from M. Tompsett <[hidden email]> ---
Created attachment 52174
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=52174&action=edit
Bug 16690: SHOW GRANTS fails to detect proper permissions

If the DB is on a remote machine, the web server and the
db server are different, but the SHOW GRANTS code in
installer/install.pl is trying to use the SAME machine.
And even if the permissions were allowed accessing from
both the web and db servers, MySQL won't return the
SHOW GRANTS without access to the mysql.user table. To
install *.* permissions became easiest to get working.
This makes it so that you can specify the web and db server
separately, so the SHOW GRANT will match the machine the
user is connected from and return properly.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Frédéric Demians <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #2 from Frédéric Demians <[hidden email]> ---
Since you add a new parameter to:

  debian/templates/koha-conf-site.xml.in

shouldn't it also added to:

  etc/koha-conf.xml

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           See Also|                            |https://bugs.koha-community
                   |                            |.org/bugzilla3/show_bug.cgi
                   |                            |?id=15088

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

--- Comment #3 from M. Tompsett <[hidden email]> ---
Created attachment 53162
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=53162&action=edit
Bug 16690: Follow up etc/koha-conf.xml

As per Frédéric Demians feedback in comment #2,
made same changes to etc/koha-conf.xml as was made
to debian/templates/koha-conf-site.xml.in

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           See Also|                            |https://bugs.koha-community
                   |                            |.org/bugzilla3/show_bug.cgi
                   |                            |?id=15427

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]
             Status|Needs Signoff               |Signed Off

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Jonathan Druart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]-c
                   |                            |ommunity.org
             Status|Signed Off                  |Needs Signoff

--- Comment #4 from Jonathan Druart <[hidden email]> ---
Signed-off-by line is missing.
Dylan please adds it or at least let a comment to explicitly tell that you
tested the patch and confirmed it works as expected.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

--- Comment #5 from Jonathan Druart <[hidden email]> ---
Signed-off-by line is missing.
Dilan please adds it or at least let a comment to explicitly tell that you
tested the patch and confirmed it works as expected.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           See Also|                            |https://bugs.koha-community
                   |                            |.org/bugzilla3/show_bug.cgi
                   |                            |?id=17237

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           See Also|                            |https://bugs.koha-community
                   |                            |.org/bugzilla3/show_bug.cgi
                   |                            |?id=9409

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #52174|0                           |1
        is obsolete|                            |

--- Comment #6 from M. Tompsett <[hidden email]> ---
Created attachment 64847
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=64847&action=edit
Bug 16690: SHOW GRANTS fails to detect proper permissions

If the DB is on a remote machine, the web server and the
db server are different, but the SHOW GRANTS code in
installer/install.pl is trying to use the SAME machine.
And even if the permissions were allowed accessing from
both the web and db servers, MySQL won't return the
SHOW GRANTS without access to the mysql.user table. To
install *.* permissions became easiest to get working.
This makes it so that you can specify the web and db server
separately, so the SHOW GRANT will match the machine the
user is connected from and return properly.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #53162|0                           |1
        is obsolete|                            |

--- Comment #7 from M. Tompsett <[hidden email]> ---
Created attachment 64848
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=64848&action=edit
Bug 16690: Follow up etc/koha-conf.xml

As per Frédéric Demians feedback in comment #2,
made same changes to etc/koha-conf.xml as was made
to debian/templates/koha-conf-site.xml.in

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Katrin Fischer <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #8 from Katrin Fischer <[hidden email]> ---
Patches still apply. Mark, could you add a test plan please and say something
about the requirements in order to test this?

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without *.* permissions

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

--- Comment #9 from M. Tompsett <[hidden email]> ---
(In reply to Katrin Fischer from comment #8)
> Patches still apply. Mark, could you add a test plan please and say
> something about the requirements in order to test this?

This is about the koha-create --use-db method, if doing a manual install from
source. But I figure you can get the same result with a simple package install
and a couple tweaks.

As you can see in comment #1, I created a DB, but did not grant permissions for
'%'. This means when the web installer attempts to SHOW GRANTS it is going to
fail, because hostname on a regular install is the KohaApp VM, not the KohaDB
VM.

The 'Do an install' is a simple package installation.
'Hack the kohaconf...' is something like:
$ sudo vi /etc/koha/sites/library/koha-conf.xml
and making the appropriate adjustments to match the information used in the
GRANT ALL.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Installing DB on remote server fails without GRANT ALL, because wrong permissions are checked

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Installing DB on remote     |Installing DB on remote
                   |server fails without *.*    |server fails without GRANT
                   |permissions                 |ALL, because wrong
                   |                            |permissions are checked

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation fails

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Installing DB on remote     |Remote DB installation
                   |server fails without GRANT  |fails
                   |ALL, because wrong          |
                   |permissions are checked     |

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation fails

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|minor                       |major

--- Comment #10 from M. Tompsett <[hidden email]> ---
Increased importance, because I really feel the local install of the DB on the
same machine as the web server is not something larger enterprises wish to do.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation fails

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|Needs Signoff               |ASSIGNED

--- Comment #11 from M. Tompsett <[hidden email]> ---
I'm having difficulty retesting my own patch, so I've changed the status back
to ASSIGNED, while I looking into this. I blame plack. ;)

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation fails

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

--- Comment #12 from M. Tompsett <[hidden email]> ---
Oooooo... CURRENT_USER is ANSI 1999. Anyone object to simpler code? :)

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation fails

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

--- Comment #13 from M. Tompsett <[hidden email]> ---
Created attachment 74510
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=74510&action=edit
Bug 16690: Simplify and correct the SHOW GRANTS

MySQL / MariaDB allow the current connected user to
check their own grants with CURRENT_USER.
There is no need for the installer to know the
IP address of the webserver.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation fails

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

--- Comment #14 from M. Tompsett <[hidden email]> ---
Created attachment 74511
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=74511&action=edit
Bug 16690: Make DB_HOST environment variable override.

Actually use __WEBSERVER_HOST__ in rewrite-config.PL

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation fails

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Blocks|                            |17237


Referenced Bugs:

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17237
[Bug 17237] Stop koha-create from creating MySQL users without host restriction
--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation fails

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|ASSIGNED                    |Needs Signoff

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation fails

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #64847|0                           |1
        is obsolete|                            |
  Attachment #64848|0                           |1
        is obsolete|                            |
  Attachment #74510|0                           |1
        is obsolete|                            |
  Attachment #74511|0                           |1
        is obsolete|                            |

--- Comment #15 from M. Tompsett <[hidden email]> ---
Created attachment 74849
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=74849&action=edit
Bug 16690: Simplify SHOW GRANTS to work when connected

If the DB is on a remote machine, the web server and the
db server are different, but the SHOW GRANTS code in
installer/install.pl is trying to use the SAME machine.
And even if the permissions were allowed accessing from
both the web and db servers, MySQL won't return the
SHOW GRANTS without access to the mysql.user table. To
install *.* permissions became easiest to get working.
Unless the DB is set up with 'user'@'%', which is also a
potential security issue.

MySQL / MariaDB allow the current connected user to
check their own grants with CURRENT_USER.
There is no need for the installer to know the
IP address of the webserver.

This also removes the need to have permissions for
'koha_kohadev'@'%', because the only process to be
accessing the koha DB is from a known host/ip.
This tightens security too.

TEST PLAN
---------
Install 2 fresh VMs from a Debian ISO.
Make sure they are on the same network (192.168.50.x) as
the kohadevbox. You will need to remember one as DB_IPADDRESS.

On the DB VM & Third VM:
sudo apt-get install mariadb-server mariadb-client net-tools
-- the third vm just needs to be able to run mysql to access
   the DB VM.

On DB VM:
sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
-- make sure the bind-address line is commented out with a #
sudo service mariadb restart
-- congratulations, your DB server is listening to remote
   calls now.

sudo mysql -u root
CREATE DATABASE koha_kohadev;
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'localhost'
IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
-- now you have an empty DB ready to run a web install on.
   However, because only koha_kohadev from localhost is
   allowed, we expect failure when we try to run the web
   installation step when we get there.

Let's confirm that everything is working as expected
before trying.  It will also demonstrate the reason why
this patch is superior to the existing code.

On a kohadevbox:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should be denied

On DB VM:
DROP USER 'koha_kohadev'@'localhost';
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY
'password';
FLUSH PRIVILEGES;

On a kohadevbox:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show two lines based on 'koha_kohadev'@'%';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should give an access denied error.
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should show two lines based on 'koha_kohadev'@'%';
QUIT
-- This case requires the unless code currently in place,
   because we aren't checking CURRENT_USER.

On DB VM:
DROP USER 'koha_kohadev'@'%';
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10'
IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

On a kohadevbox:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show two lines based on 'koha_kohadev'@'%';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should show two lines based on 'koha_kohadev'@'192.168.50.10';
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should give an access denied error.
QUIT
-- This case demonstrates that we have two failure points:
       1) The GRANT command by the DB Admin and
       2) The koha-conf.xml setting.
   This is why CURRENT_USER is superior: only (2) is the
   failure point.

On DB VM:
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY
'password';
FLUSH PRIVILEGES;
SELECT host,user FROM mysql.user;
-- Should see both koha_kohadev for 192.168.50.10 and %.

On a kohadevbox:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show two lines based on 'koha_kohadev'@'192.168.50.10';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should show two lines based on 'koha_kohadev'@'192.168.50.10';
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should give an access denied error.
QUIT
-- This case doesn't need the unless. CURRENT_USER still
   just works.

On an third VM on the same network:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show two lines based on 'koha_kohadev'@'%';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should give an access denied error.
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should show two lines based on 'koha_kohadev'@'%';
QUIT
-- This case demonstrates that it may be more open than a DB
   administrator would prefer. And notice, CURRENT_USER still
   just works.

On DB VM:
DROP USER 'koha_kohadev'@'192.168.50.10';
DROP USER 'koha_kohadev'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
-- This basically give koha_kohadev free reign to do pretty
   dangerous stuff.

On an third VM on the same network:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show a line based on 'koha_kohadev'@'%';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should give a no such grant error.
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should show two lines based on 'koha_kohadev'@'%';
QUIT
-- This case demonstrates that it may be more open than a DB
   administrator would prefer. And notice, CURRENT_USER still
   just works.

In the old code, both cases were literally checked.
This tweak is an optimization which doesn't require
setting permissions to the mysql.user table. Without it,
the code says the user doesn't have permissions to check
the show grants.  This issue is not visible to the user,
because both cases are checked.

On DB VM:
SELECT host,user FROM mysql.user;
-- for each one do an appropriate DROP USER 'user'@'host';
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10'
IDENTIFIED BY 'password';

On kohadevbox:
-- Make sure the /etc/koha/sites/kohadev/koha-conf.xml
   points to the DB VM.
-- Make sure a web install runs correctly

On third VM:
-- Make sure unable to connect as koha_kohadev/password.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation is less secure.

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Remote DB installation      |Remote DB installation is
                   |fails                       |less secure.

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 16690] Remote DB installation is less secure.

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Martin Renvoize <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #74849|0                           |1
        is obsolete|                            |

--- Comment #16 from Martin Renvoize <[hidden email]> ---
Created attachment 79325
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=79325&action=edit
Bug 16690: Simplify SHOW GRANTS to work when connected

If the DB is on a remote machine, the web server and the
db server are different, but the SHOW GRANTS code in
installer/install.pl is trying to use the SAME machine.
And even if the permissions were allowed accessing from
both the web and db servers, MySQL won't return the
SHOW GRANTS without access to the mysql.user table. To
install *.* permissions became easiest to get working.
Unless the DB is set up with 'user'@'%', which is also a
potential security issue.

MySQL / MariaDB allow the current connected user to
check their own grants with CURRENT_USER.
There is no need for the installer to know the
IP address of the webserver.

This also removes the need to have permissions for
'koha_kohadev'@'%', because the only process to be
accessing the koha DB is from a known host/ip.
This tightens security too.

TEST PLAN
---------
Install 2 fresh VMs from a Debian ISO.
Make sure they are on the same network (192.168.50.x) as
the kohadevbox. You will need to remember one as DB_IPADDRESS.

On the DB VM & Third VM:
sudo apt-get install mariadb-server mariadb-client net-tools
-- the third vm just needs to be able to run mysql to access
   the DB VM.

On DB VM:
sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
-- make sure the bind-address line is commented out with a #
sudo service mariadb restart
-- congratulations, your DB server is listening to remote
   calls now.

sudo mysql -u root
CREATE DATABASE koha_kohadev;
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'localhost'
IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
-- now you have an empty DB ready to run a web install on.
   However, because only koha_kohadev from localhost is
   allowed, we expect failure when we try to run the web
   installation step when we get there.

Let's confirm that everything is working as expected
before trying.  It will also demonstrate the reason why
this patch is superior to the existing code.

On a kohadevbox:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should be denied

On DB VM:
DROP USER 'koha_kohadev'@'localhost';
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY
'password';
FLUSH PRIVILEGES;

On a kohadevbox:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show two lines based on 'koha_kohadev'@'%';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should give an access denied error.
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should show two lines based on 'koha_kohadev'@'%';
QUIT
-- This case requires the unless code currently in place,
   because we aren't checking CURRENT_USER.

On DB VM:
DROP USER 'koha_kohadev'@'%';
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10'
IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

On a kohadevbox:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show two lines based on 'koha_kohadev'@'%';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should show two lines based on 'koha_kohadev'@'192.168.50.10';
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should give an access denied error.
QUIT
-- This case demonstrates that we have two failure points:
       1) The GRANT command by the DB Admin and
       2) The koha-conf.xml setting.
   This is why CURRENT_USER is superior: only (2) is the
   failure point.

On DB VM:
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY
'password';
FLUSH PRIVILEGES;
SELECT host,user FROM mysql.user;
-- Should see both koha_kohadev for 192.168.50.10 and %.

On a kohadevbox:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show two lines based on 'koha_kohadev'@'192.168.50.10';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should show two lines based on 'koha_kohadev'@'192.168.50.10';
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should give an access denied error.
QUIT
-- This case doesn't need the unless. CURRENT_USER still
   just works.

On an third VM on the same network:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show two lines based on 'koha_kohadev'@'%';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should give an access denied error.
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should show two lines based on 'koha_kohadev'@'%';
QUIT
-- This case demonstrates that it may be more open than a DB
   administrator would prefer. And notice, CURRENT_USER still
   just works.

On DB VM:
DROP USER 'koha_kohadev'@'192.168.50.10';
DROP USER 'koha_kohadev'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
-- This basically give koha_kohadev free reign to do pretty
   dangerous stuff.

On an third VM on the same network:
mysql -u koha_kohadev -h DB_IPADDRESS -p
-- this should give you a SQL prompt
SHOW GRANTS FOR CURRENT_USER;
-- this should show a line based on 'koha_kohadev'@'%';
SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10';
-- this should give a no such grant error.
SHOW GRANTS FOR 'koha_kohadev'@'%';
-- this should show two lines based on 'koha_kohadev'@'%';
QUIT
-- This case demonstrates that it may be more open than a DB
   administrator would prefer. And notice, CURRENT_USER still
   just works.

In the old code, both cases were literally checked.
This tweak is an optimization which doesn't require
setting permissions to the mysql.user table. Without it,
the code says the user doesn't have permissions to check
the show grants.  This issue is not visible to the user,
because both cases are checked.

On DB VM:
SELECT host,user FROM mysql.user;
-- for each one do an appropriate DROP USER 'user'@'host';
GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10'
IDENTIFIED BY 'password';

On kohadevbox:
-- Make sure the /etc/koha/sites/kohadev/koha-conf.xml
   points to the DB VM.
-- Make sure a web install runs correctly

On third VM:
-- Make sure unable to connect as koha_kohadev/password.

Signed-off-by: Martin Renvoize <[hidden email]>

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
12