"safe" way to access PSA database from PHP

[faris]

I've written a php script that processes Plesk mailbox usage and quota and alerts mailbox users in an intelligent and highly customisable way when they reach configurable capacity band levels.

I had previously been relying on importing data from a bash script that laboriously steps through all the mailnames and quota files.

I've now finally worked out the sql statement needed to get the same info directly from the PSA database.

In order to access this data, the php script obviously needs access to the PSA database.
And in order to do that, it needs to know the Plesk admin password.

Can someone advise me on a relatively safe way to go about this?

I've thought of having the php script in /root, owned by root and only readable by root and running it from the command line.

I guess that's as safe as you'll easily get. But I would have preferred to be able to put it in a password-protected (Plesk's protected directory system) directory in an outside-accessible webspace.

That would be dangerous if the script contained the Plesk admin password, so I was thinking of creating, via the command line, a mysql user with read-only privs to only the specific tables in the PSA database that are needed for the script to work.

Does that sound reasonable? The only thing is that I'm scared of fiddling with mysql users outside of plesk in case it screws something up. Does anyone have any experience with this?

[BruceLee]

what about this:

Code: Select all

mysql -uadmin -p$(cat /etc/psa/.psa.shadow) psa -e "SELECT domains.name AS domain, mail_name, password FROM domains LEFT JOIN mail ON (domains.id=mail.dom_id) LEFT JOIN accounts ON (accounts.id=mail.account_id) ORDER BY domain;"

this script gets the password out of the psa.shadow file and than checks the db for the correct plesk user account password.
in this case you don't need to have the password saved somewhere in your script.

[faris]

Yes....that's good, but it would have to be run though exec() (or whatever) via php, and the user running that script would need access to the mysql client binary?

But much better than having the password in the file. Much, much better!

But I'm leaning towards using MySQL's column and command access control at the moment....if I can get past my fear of creating a user with access to the PSA database outside of Plesk. Of course the tortix user and its databases are created outside plesk with no problems, although it doesn't have access to the PSA database directly. Hmm....

I'm reasonably sure that MySQL access control for a user is held in records for that user, and do not make any changes to any databases. i.e. the PSA database isn't changed to say "user X can do this", but rather user X's records are changed to say "you have access to this, that and the other". In which case it would be safe do create a user with SELECT privs on certain columns only.

[breun]

Why do you want to put this script in an Apache document root? I'd just run this script from cron as root using the contents of /etc/psa/.psa.shadow and keep Apache out of the mix.

[breun]

Yes....that's good, but it would have to be run though exec() (or whatever) via php, and the user running that script would need access to the mysql client binary?

The user running that command would need permission to read /etc/psa/.psa.shadow, which would be either psaadm or root.

[scott]

You could also make a new mysql user that only has read access to the psa db, or perhaps specific tables in the psa db.

[faris]

I want to put it in an internet-accessible area so that potentially it can do its thing, and then display on-screen what it intends to do (e.g. who it will email and why), then I can decide if I want it to do so or not. More for debugging than anything else. Another way would be for it to not be accessible and just have a debug mode where it emails me with what it intends to do, then I can look it over, take it out of debug mode, and get it to send alerts for real.

However, this idea of creating a mysql user with specific select-only permissions on specific columns of specific tables in the PSA database is interesting to me in general from a security point of view. I'm going to go that way on a non-critical system and see what happens.

[breun]

An even fancier way of going about this might be to split the data gathering process and the do-you-want-to-send-these-messages web interface. You could for instance run the job on the psa database via cron and have it output to some file or database for the mail sending step.

[faris]

A most excellent idea breun.

I'd need to add some code in the "root" script that generated a timestamp, and something to check that timestamp in the "http" script to make sure I'm working with up-to-date data, but that would definitly work.

Thanks for everybody's input on this. The finished product will, I hope, be a must-have utility on every Plesk server (until it gets built into Plesk by Parallels

[faris]

For posterity:

Here's the sql statement to get usage info:

Code: Select all

SELECT mail.mail_name, domains.name, 
mn_param.val, mail.mbox_quota FROM mail, mn_param, domains WHERE mail.id=mn_param.mn_id AND 
mail.dom_id=domains.id AND mn_param.param='box_usage';

Here's how to create a mysql user that only has SELECT privs on the *specific columns* on the *specific tables* in the psa database that are needed to execute the above sql statement:

Step 1: Create user and grant SELECT on mail table columns required

Code: Select all

GRANT SELECT (id, dom_id, mail_name, mbox_quota) ON psa.mail TO 'my-lovely-user'@'localhost' 
IDENTIFIED BY 'my-lovely-password';

NOTE: we need to grant select on id and dom_id as these are used in the WHERE part of the sql statement even though they are not in the SELECT part


Step 2: Grant SELECT on particular columns of mn_param table

Code: Select all

GRANT SELECT (mn_id, val, param) ON psa.mn_param TO 'my-lovely-user'@'localhost';

Note: again we need to grant select on mn_id and param as they are used in WHERE part of sql statement


Step 3: Grant SELECT on particular columns of domains table

Code: Select all

GRANT SELECT (id, name) ON psa.domains TO 'my-lovely-user'@'localhost';

Note: Once again we need to grant SELECT on id as it is used in WHERE part


At this point my-lovely-user will be able to execute the statement to get mail quota info, and pretty much nothing else.

If this restricted user is compromised in some way, an attacker could obtain all mailbox names on the server. This is not a good thing. But no further damage could be done. Errr.. Maybe the mn_param.param needs to be locked down to row 'box_usage' to make it properly secure. Not sure where passwords are stored.