Minimal SQL database privileges

[baldurmen]

Hi!

The nextcloud documentation states that all privileges on the database should be granted to the nextcloud user:

GRANT ALL PRIVILEGES ON nextcloud.* TO 'username'@'localhost' IDENTIFIED BY 'password';

I was wondering what are the true minimal SQL privileges nextcloud needs to run normally.

I don't normally like to grant all privileges for security reasons and would really appreciate it if you could publish a minimal SQL database privileges list.

[tflidd]

Which permissions do you have in mind? To install apps and for upgrades, you probably need quite extensive permissions. You could ask for an additional mysql user to be set up for administrative tasks. If possible, I would use a database exclusively for Nextcloud and put other services on different DBs.

You could limit queries, updates and the number of connections but with that you risk to have some strange effect if you hit these limits so you must monitor your server carefully.

[baldurmen]

I guess I'm expecting something like Drupal does.

  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password';

At the database level, MySQL/MariaDB supports:

• ALTER
• CREATE
• CREATE ROUTINE
• CREATE TEMPORARY TABLES
• CREATE VIEW
• DELETE
• DELETE HISTORY
• DROP
• EVENT
• INDEX
• INSERT
• LOCK TABLES
• REFERENCES
• SELECT
• SHOW VIEW
• TRIGGER
• UPDATE

Does Nextcloud really need database level privileges like EVENT or CREATE ROUTINE? If not, why should I grant them?

I understand that different apps may require different privileges and that you don't have control on that. I'm thus only asking for the minimal required privileges for Nextcloud itself - minus all apps - to work correctly.

[tflidd]

Does Nextcloud really need database level privileges like EVENT or CREATE ROUTINE? If not, why should I grant them?

You are probably right that we have similar requirements for the database like drupal. @nickvergessen can you confirm that?

[baldurmen]

The nice folks over at the KanBoard project replied to a similar request I made: kanboard/kanboard#3699 (comment)

Maybe the Nextcloud permission set is the same?

[nickvergessen]

I think the drupal one looks good. But I didn't check the code if that fully covers everything we do.
If you want to, you could go through https://github.com/nextcloud/3rdparty/blob/eaf914b713e27625ec37a8fcc9e46227b8b0500b/doctrine/dbal/lib/Doctrine/DBAL/Platforms/AbstractPlatform.php and check for all the methods what privileges they require.

[baldurmen]

Good to know! My PHP level is not good enough for me to go through the code though.

I've set the Drupal permissions on my personal instance and I did not have any problems doing basic tasks and installing / removing apps.

I'll wait until the next Nextcloud update to see if the upgrade goes well, and if it's the case I'll close this issue and create a PR on the documentation.

[baldurmen]

Well, I updated to 13.0.1 last night without any problem using the "Drupal" permissions. I'll create a PR.