[#30570] - [3.x-4.0] enable install in a dedicated schema on PostgreSQL databases
- Fixed in Code Base
- 14 Dec 2020
- Medium
- Build: staging
- # 30570
- Diff
- Skrol29:pgsql-support
- Success Download Prebuilt packages are available for download. Details
- Success continuous-integration/appveyor/pr AppVeyor build succeeded Details
- Success continuous-integration/drone/pr Build is passing Details
User tests: Successful: Unsuccessful:
Pull Request for Issue # .
Summary of Changes
The installation of Joomla in a PostgreSQL database is nowadays limited to the schema named "public".
This limitation is the due to only one line of code in the PostgreSQL (PDO) driver : this is in the function getTableColumns()
where a search for columns is made explicitly in the "public" schema.
The changes of this Pull Request make the function getTableColumns() to search columns in the default schema of the connection instead of the "public" schema.
This goes with a new private function named getDefaultSchema(), which could be used for future coding when we need to now the default schema in PostgreSQL.
The ability to install Joomla in a dedicated schema is more comfortable, opened, et secured.
Testing Instructions
Create a PostgreSQL schema dedicated to your Joomla application.
Also create a user dedicated for your Joomla application connection.
SQL instruction :
-- create a schema dedicated to your Joomla application
CREATE SCHEMA schema_joomla_app;
-- create a user dedicated to your Joomla application connection
CREATE ROLE user_joomla_app WITH
LOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
REPLICATION
ENCRYPTED PASSWORD 'xxxxxxxx';
-- limit the user permissions to the dedicated schema
GRANT ALL ON SCHEMA schema_joomla_app TO user_joomla_app WITH GRANT OPTION;
-- set the default schema for the user
ALTER ROLE user_joomla_app IN DATABASE my_test_db SET search_path TO schema_joomla_app;
- Start a fresh new Joomla installation, at the Database configuration step, choose the Database type : "PostgreSQL (PDO)", and continue the installation process using your user and database connection.
Actual result BEFORE applying this Pull Request
At the last step of the installation, the process never ends (test on Joomla 3.9.21) because a silent error as occurred: one Ajax response hold the error message : "No columns found for #__extensions table".
In the database, all Joomla tables seems to be correctly created.
If you restart the installation from the previous step, the the same error occurs.
Expected result AFTER applying this Pull Request
The installation ends without error.
Joomla is functional.
Documentation Changes Required
No
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Category | ⇒ | Libraries |
| Labels |
Added:
?
|
||
@wilsonge Should this be fixed upstream in the framework package, too?
Yes, but I cannot test it yet.
The code is nearly the same in https://github.com/joomla-framework/database/blob/master/src/Pgsql/PgsqlDriver.php
@Skrol29 Your PR here changes the PDO driver for PostgreSQL. But on 3.9.x we still have to support the native PostgreSQL driver, too.
Could you do the same changes in file https://github.com/Skrol29/fork-joomla-cms/blob/pgsql-support/libraries/joomla/database/driver/postgresql.php ?
Just edit that file in the branch of this PR and commit the changes so that change will be part of this PR here, too.
Let me know if you need advise with that.
Update 2020-10-12 by @richard67 : Has been solved meanwhile, see commit below.
After this PR will be tested with success and finally be merged into the staging branch here, I will take care of bringing up the change to the upstream repository in the framework and to bring it into the 2.0 branch of the framework database package so the correction will at the end also arrive in Joomla 4.
| Category | Libraries | ⇒ | Libraries Postgresql |
The continuous-integration/drone/pr has failed on the Codequality / Javascript test. This seems mis relevant. Can somebody help on this or restart the check ?
Restarted the check, maybe we are more lucky this time.
I have tested this item
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/30570.
Can someone else with permissions add a Human Test ? So the Pull Request could goes one...
What is the next step for a RTC (Ready To Commit) ? Does it need a second approval ?
If the Pull Request is accepted, do I need another one for Joomla 4 ?
What is the next step for a RTC (Ready To Commit) ? Does it need a second approval ?
No, but it needs a second two human tests.
If the Pull Request is accepted, do I need another one for Joomla 4 ?
No, when it has been merged into the staging branch, it will sooner or later arrive in the 3.10-dev branch, too. For 4.0-dev it would need to make a pull request for the 2.0-dev branch of the database package in the framework here: https://github.com/joomla-framework/database.
I have tested this item
i've tested both with pdo/postgresql with public/default schema and works as before
i've tested both with pdo/postgresql with dedicated schema and works
postgresql 11.7
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/30570.
I have tested this item
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/30570.
| Status | Pending | ⇒ | Ready to Commit |
RTC
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/30570.
@wilsonge PR for the framework is joomla-framework/database#217 .
| Labels |
Added:
?
|
||
Previous tests and RTC status are still valid. Last 2 commits by me only corrected 2 code comments. I've restored the test results so they are properly counted.
wilsonge
- | Status | Ready to Commit | ⇒ | Fixed in Code Base |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2020-12-14 18:39:34 |
| Closed_By | ⇒ | wilsonge |
@wilsonge Should this be fixed upstream in the framework package, too?