Question Database information of the main plesk database

[Quinten]

Server operating system version

Ubuntu 22.04.1 LTS

Plesk version and microupdate number

Plesk Obsidian Version 18.0.49 Update #2

Hello,

I would like to connect my plesk database to our Grafana panel as a data source to read out some data.
Where do i find the database connection info of the main database of the Plesk server?

Any help would be appriciated.

Kind Regards,
Quinten

 

[Peter Debik]

user name:
admin

password:
# cat /etc/psa/.psa.shadow
(looks encrypted, but should work anyway)

database name:
psa

 

[Quinten]

Seems like i cant connect
Hostname i tried ip and the url version

Any ideas?

 

[Peter Debik]

Is your Grafana on the same host like the database that you want to access?

 

[Quinten]

Is your Grafana on the same host like the database that you want to access?

No it is not.
I enabled local connections from any host in the settings of plesk, am i missing something?

Thank you for your assistance.

 

[Peter Debik]

In that case, you need to do at least two things:
1) In the /etc/my.cnf file you need to bind the database server to :: instead of any local address. Reload the database service after that change.
2) The "admin" user has permissions for localhost access only. So in that case, add a new user record to the database with any new user of your choice and grant that user full access from % or - much more secue - the static IP of your other host where Grafana is hosted to the psa database. Then use that new user to access the database.

 

[Quinten]

In that case, you need to do at least two things:
1) In the /etc/my.cnf file you need to bind the database server to :: instead of any local address. Reload the database service after that change.
2) The "admin" user has permissions for localhost access only. So in that case, add a new user record to the database with any new user of your choice and grant that user full access from % or - much more secue - the static IP of your other host where Grafana is hosted to the psa database. Then use that new user to access the database.

Thank you for your reply, step 1 is done but could you explain step 2 abit more please? (Sorry)
Do i insert a user in the table db_users? and if so what do i write under role?

 

[Peter Debik]

Do i insert a user in the table db_users? and if so what do i write under role?

No, do not change anything in the psa database. Please, even if you have access to the psa database, never ever do manual updates or inserts to that. The relations in psa are so deep that a single missing or false entry can render the whole database useless.

Instead: The user record needs to be added to the MySQL/MariaDB users. Like
> GRANT ALL PRIVILEGES ON psa.* TO 'username'@'<ip of requesting server>';
or
> GRANT ALL PRIVILEGES ON psa.* TO 'username'@'%';
If you know that you only want to do selects, you could also do something like
> GRANT SELECT ON psa.* TO 'username'@'%';

 

[Quinten]

No, do not change anything in the psa database. Please, even if you have access to the psa database, never ever do manual updates or inserts to that. The relations in psa are so deep that a single missing or false entry can render the whole database useless.

Instead: The user record needs to be added to the MySQL/MariaDB users. Like
> GRANT ALL PRIVILEGES ON psa.* TO 'username'@'<ip of requesting server>';
or
> GRANT ALL PRIVILEGES ON psa.* TO 'username'@'%';
If you know that you only want to do selects, you could also do something like
> GRANT SELECT ON psa.* TO 'username'@'%';

Hello thank you for your answer.

I made a new user and then i gave all the privilages like you showed above.
But i still get the same error.

Is there anything else i am not doing right?

Kind Regards,
Quinten

 

[Peter Debik]

- Have you tried to access the database from the local machine using your new user account and password? Can you then run all the required SQL commands?
- Have you verified that your database /etc/my.cnf is configured with bind-address ::? And has the database server been restarted afterwards?
If both are working right, a firewall may be blocking the MySQL port either on the client or on the server or in between.