DA Postgresql plugin

S

shvaber

Verified User
Joined
Aug 9, 2019
Messages
37
Hello,

I hope this plugin will be useful to start some support of PostgreSQL databases with DA.
It is a completely free plug-in for DirectAdmin written by Poralix.com (thanks a lot Alex!) that can be as a start point to continue development and become a part of DA.
All source codes here: da-postgresql on GitHub.

As it is non-commercial product, I'm looking for some one or a team of developers to continue work over it. For right now it is already possible to use this plugin on production servers.
 
shvaber said:
Hello,

I hope this plugin will be useful to start some support of PostgreSQL databases with DA.
It is a completely free plug-in for DirectAdmin written by Poralix.com (thanks a lot Alex!) that can be as a start point to continue development and become a part of DA.
All source codes here: da-postgresql on GitHub.

As it is non-commercial product, I'm looking for some one or a team of developers to continue work over it. For right now it is already possible to use this plugin on production servers.
Click to expand...

I can confirm. Poralix has worked on the plugin development, but we don't control the repository where the source code was published and did not review it. So we can not give any warranty regarding it.

Should anybody have questions on the code, installation, or usage, I'm ready to answer and help.
 
Hello,

I'm interest this plugin and i want of your help.

The plugin is lack of installation instructions and programs is required to run the plugin.

- How to install postgres server and if need special instrutions to integrate in DA
- How to install phpPgAdmin and integrate in DA
- Etc.

Thanks.
 
The plugin should be installed with a help of plugin manager in DirectAdmin. There are no other instructions. But some manual work is still required.

Earlier I was emailed about the plugin, it shows a white screen in Directadmin. At least one reason comes to my mind, the plugin is not compatible with PHP 8+. I never tried the plugin on my private or customers servers since the moment when the development of the plugin stopped. Anyway, here is what I found in my notes:


The plugin was developed in 2020 for PHP 7.3.13 + pgsql and PostgreSQL-server 9.6.10, phpPgAdmin 7.12 and 7.13.

The plugin does not install PostgreSQL-server, and it does NOT add pgsql support in PHP. That's fully up to you to complete installation of these components.

​

Requirements:​

  • PosgreSQL pre-installed
  • PHP functions: exec
  • PHP extension pgsql
  • unzip installed

Scripts for a server administrator and used by DirectAdmin:​

  • da-postgresql/exec/dbbackupuser.sh - a script to backup PostgreSQL DBs and users, used by DirectAdmin
  • da-postgresql/exec/dbdump.sh - a script to dump a single PostgreSQL DB
  • da-postgresql/exec/dbrestore.sh - a script to restore a single PostgreSQL DB
  • da-postgresql/exec/dbsize.sh - a script to get size of all DBs created by user in DirectAdmin
  • da-postgresql/exec/dbusage.sh - a script to get count of all DBs created by user in DirectAdmin

Scripts for a server administrator:​


The folder da-postgresql/scripts/setup/ contains scripts:
  • da-postgresql/scripts/setup/phppgadmin.sh - for installation of phpPgAdmin
  • da-postgresql/scripts/setup/create_admin.sh - create users/admins required fort the plugin to work with PostgreSQL
  • da-postgresql/scripts/setup/custom_backups.sh - enable/disable DirectAdmin to include PostgreSQL dumps into backups
  • da-postgresql/scripts/setup/custom_quotas.sh - enable/disable DirectAdmin to include PostgreSQL used space into user quotas
  • da-postgresql/scripts/setup/custom_restore.sh - enable/disable DirectAdmin to include PostgreSQL restoration of dumps from backups
Kurogane said:
- How to install postgres server and if need special instrutions to integrate in DA
Click to expand...
 
Bash: 
create_admin.sh

[OK] USER ROLE diradmin CREATED FINE!
[OK] Writing configs for PostgreSQL superuser access.
[OK] Setting a password based authentication in PostgreSQL
[OK] Restarting PostgreSQL server
[OK] The pgsql module is loaded
[OK] pg_connect() is available
PHP Warning:  pg_connect(): Unable to connect to PostgreSQL server: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "diradmin"
connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "diradmin" in /usr/local/directadmin/plugins/postgresql/exec/test_php.php on line 41
[ERROR] Failed to connect to PostgreSQL server as diradmin...
PHP Fatal error:  Uncaught TypeError: pg_close(): Argument #1 ($connection) must be of type ?PgSql\Connection, false given in /usr/local/directadmin/plugins/postgresql/exec/test_php.php:50
Stack trace:
#0 /usr/local/directadmin/plugins/postgresql/exec/test_php.php(50): pg_close()
#1 {main}
  thrown in /usr/local/directadmin/plugins/postgresql/exec/test_php.php on line 50

Yes, postgress is installed and up.

Any ideas?
 
After a successful user creation the following files should be created:

Bash: 
PSQL_CONF="/usr/local/directadmin/plugins/postgresql/pgpass.conf";
PSQL_USER_CONF="/usr/local/directadmin/.pgpass";

Check their content and try to connect to the PGSQL from a CLI.
 
Same problem

psql -U diradmin or psql -U diradmin -W

Code: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "diradmin"
 
What version of PostgreSQL is that?

And do you have PGSQL running?

Code: 
ls -la /var/run/postgresql/.s.PGSQL.5432
?
 
PostgreSQL 15.

But i find the problem and because of this.

  • needs to change /var/lib/pgsql/14/data/postgresql.conf: from: password_encryption = scram-sha-256 to: password_encryption = md5
Click to expand...
I revert back and now create_admin.sh or CLI work fine. This option has to be changed or not.

But still see white screen in Directadmin.
 
Kurogane said:
But i find the problem and because of this.
Click to expand...

Yes, it is all written in README file as well as on the main page of the plugin page at GitHub.

It is REQUIRE​

  • to re-save User Package or the user's account and set up how much databases it can handle. Including the user "admin"
  • plugin will work with phpPgAdmin-7.13.0
  • needs to change /var/lib/pgsql/14/data/postgresql.conf: from: password_encryption = scram-sha-256 to: password_encryption = md5
  • needs to install pgsql module for PHP to run phpPgAdmin and plugin using standart DA way.

Kurogane said:
But still see white screen in Directadmin.
Click to expand...

What PHP version do you use?
 
The plugin was developed in 2020 for PHP 7.3.13 + pgsql and PostgreSQL-server 9.6.10, phpPgAdmin 7.12 and 7.13.

I never tested the plugin under current DirectAdmin and PHP 8.x

You might try and identify what does it complain about. You might enable error_reporting and display_errors in PHP/plugin
 
Give me a lot errors

PHP: 
Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/functions.inc.php on line 123 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Fatal error: Uncaught Error: Call to undefined function get_magic_quotes_gpc() in /usr/local/directadmin/plugins/postgresql/exec/functions.inc.php:68 Stack trace: #0 /usr/local/directadmin/plugins/postgresql/exec/admin.inc.php(51): parse_input() #1 /usr/local/directadmin/plugins/postgresql/admin/index.html(23): require_once('/usr/local/dire...') #2 {main} thrown in /usr/local/directadmin/plugins/postgresql/exec/functions.inc.php on line 68

In this error seems function get_magic_quotes_gpc() is removed in PHP 8 so, i remove that function (hope i do right)

Then

PHP: 
Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/functions.inc.php on line 102 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Fatal error: Uncaught Error: Call to undefined function pg_connect() in /usr/local/directadmin/plugins/postgresql/exec/class.postgresql.inc.php:874 Stack trace: #0 /usr/local/directadmin/plugins/postgresql/exec/class.postgresql.inc.php(329): postgresql->_connect() #1 /usr/local/directadmin/plugins/postgresql/exec/actions/html/admin/admin.php(56): postgresql->getDatabasesList() #2 /usr/local/directadmin/plugins/postgresql/exec/admin.inc.php(73): require_once('/usr/local/dire...') #3 /usr/local/directadmin/plugins/postgresql/admin/index.html(23): require_once('/usr/local/dire...') #4 {main} thrown in /usr/local/directadmin/plugins/postgresql/exec/class.postgresql.inc.php on line 874

Here i don't know the function pg_connect() is not detected? problem to connect? i try to find what user is try to connect but don't find anything
 
The core error is:

Code: 
Call to undefined function pg_connect()

How did you install the PGSQL extension in PHP? Did you compile PHP with --with-pgsql[=DIR], or it is loaded using extension directive in php.ini?

If the second, then you will need to update da-postgresql/php.ini too and add instructions to load the shared module pgsql.
 
I was thinking it was that the extension was and was trying to verify it and saw that it was indeed not loading.

I really don't understand how php works in directadmin, when I use CLI or website the function works fine, when used in directadmin it doesn't detect it, it's like directadmin has its own isolated php internally.

I installed the function with Custombuild, and it loads it in the php.conf.d folder.
 
Let's stick to the topic. Is PGSQL enabled in PHP or not?

The plugin does not use a system php.ini, it uses its own copy of php.ini.
 
Extension is installed with custombuild, enabled and work fine. But the only place in not working is in the plugin.
 
The plugin does not use system php.ini as well as it does not use any extra ini files from CustomBuild, it uses its own copy of php.ini.

You will need to update da-postgresql/php.ini and add instructions to load the shared module pgsql in the file.

Let me know when you get it enabled in da-postgresql/php.ini if you need any further assistance of course.
 
I have manually added the extension and I think it works now, at least I don't get the white screen anymore.

Now i need to solve this.

PHP: 
Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/functions.inc.php on line 102 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /usr/local/directadmin/plugins/postgresql/exec/class.inc.php on line 127 Deprecated: Creation of dynamic property postgresql::$query is deprecated in /usr/local/directadmin/plugins/postgresql/exec/class.postgresql.inc.php on line 794 Deprecated: Automatic conversion of false to array is deprecated in /usr/local/directadmin/plugins/postgresql/exec/class.postgresql.inc.php on line 346

How to solve this?👇

PHP: 
Deprecated: Creation of dynamic property postgresql::$query is deprecated in /usr/local/directadmin/plugins/postgresql/exec/class.postgresql.inc.php on line 794
Deprecated: Automatic conversion of false to array is deprecated in /usr/local/directadmin/plugins/postgresql/exec/class.postgresql.inc.php on line 346
 
I was happy to help you with the plugin installation. Rewriting and/or updating PHP scripts is not something I want to do on the forums. Probably somebody else will help you with updating PHP scripts and making them compatible with PHP 8.x.
 
You must log in or register to reply here.
Back
Top