[sql] use separate grant-all scripts for postgresql and mysql

Mysql doesn't support granting to multiple tables, and requires a
different username format.

Would be nice to code the permissions somewhere, then generate the
grant statements as required.

Signed-off-by: Jeremy Kerr <jk@ozlabs.org>

diff --git a/docs/INSTALL b/docs/INSTALL
index 8f3aab93f6fe48a57b69b482734ac74f35c81882..05fea7c13983f0273616336d634a9bd4a584f88b 100644 (file)
--- a/docs/INSTALL
+++ b/docs/INSTALL
@@ -29,16 +29,21 @@ in brackets):
     user that your mail server runs as). On Ubuntu these are
     www-data and nobody, respectively.
 
-      PostgreSQL:
-        createdb patchwork
-        createuser www-data
-        createuser nobody
+    For PostgreSQL
 
-      MySQL:
+        $ createdb patchwork
+        $ createuser www-data
+        $ createuser nobody
+
+       - postgres uses the standard UNIX authentication, so these users
+         will only be accessible for processes running as the same username.
+         This means that no passwords need to be set.
+
+     For MySQL:
         $ mysql
        > CREATE DATABASE 'patchwork';
-        > INSERT INTO user (Host, User) VALUES ('localhost', 'www-data');
-        > INSERT INTO user (Host, User) VALUES ('localhost', 'nobody');
+        > CREATE USER 'www-data'@'localhost' IDENTIFIED BY '<password>';
+        > CREATE USER 'nobody'@'localhost' IDENTIFIED BY '<password>';
 
 2. Django setup
 
@@ -99,8 +104,10 @@ in brackets):
        And add privileges for your mail and web users:
 
        Postgresql:
-         psql -f lib/sql/grant-all.sql patchwork
+         psql -f lib/sql/grant-all.postgres.sql patchwork
 
+       MySQL:
+         mysql patchwork < lib/sql/grant-all.mysql.sql
 
 
 3. Apache setup

diff --git a/lib/sql/grant-all.mysql.sql b/lib/sql/grant-all.mysql.sql
new file mode 100644 (file)
index 0000000..4dd6efb
--- /dev/null
+++ b/lib/sql/grant-all.mysql.sql
@@ -0,0 +1,35 @@
+BEGIN;
+-- give necessary permissions to the web server. Becuase the admin is all
+-- web-based, these need to be quite permissive
+GRANT SELECT, UPDATE, INSERT, DELETE ON        auth_message TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON django_session TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON django_site TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON django_admin_log TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON django_content_type TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON auth_group_permissions TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON auth_user TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON auth_user_groups TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON auth_group TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON auth_user_user_permissions TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON auth_permission TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_userpersonconfirmation TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_state TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_comment TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_person TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_userprofile TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_userprofile_maintainer_projects TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_project TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_bundle TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_bundle_patches TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON patchwork_patch TO 'www-data'@localhost;
+GRANT SELECT, UPDATE, INSERT, DELETE ON registration_registrationprofile TO 'www-data'@localhost;
+
+-- allow the mail user (in this case, 'nobody') to add patches
+GRANT INSERT, SELECT ON patchwork_patch TO 'nobody'@localhost;
+GRANT INSERT, SELECT ON patchwork_comment TO 'nobody'@localhost;
+GRANT INSERT, SELECT ON patchwork_person TO 'nobody'@localhost;
+GRANT SELECT ON        patchwork_project TO 'nobody'@localhost;
+GRANT SELECT ON patchwork_state TO 'nobody'@localhost;
+
+COMMIT;
+

diff --git a/lib/sql/grant-all.postgres.sql b/lib/sql/grant-all.postgres.sql
new file mode 100644 (file)
index 0000000..72e1f31
--- /dev/null
+++ b/lib/sql/grant-all.postgres.sql
@@ -0,0 +1,69 @@
+BEGIN;
+-- give necessary permissions to the web server. Becuase the admin is all
+-- web-based, these need to be quite permissive
+GRANT SELECT, UPDATE, INSERT, DELETE ON
+       auth_message,
+       django_session,
+       django_site,
+       django_admin_log,
+       django_content_type,
+       auth_group_permissions,
+       auth_user,
+       auth_user_groups,
+       auth_group,
+       auth_user_user_permissions,
+       auth_permission,
+       patchwork_userpersonconfirmation,
+       patchwork_state,
+       patchwork_comment,
+       patchwork_person,
+       patchwork_userprofile,
+       patchwork_userprofile_maintainer_projects,
+       patchwork_project,
+       patchwork_bundle,
+       patchwork_bundle_patches,
+       patchwork_patch,
+       registration_registrationprofile
+TO "www-data";
+GRANT SELECT, UPDATE ON
+       auth_group_id_seq,
+       auth_group_permissions_id_seq,
+       auth_message_id_seq,
+       auth_permission_id_seq,
+       auth_user_groups_id_seq,
+       auth_user_id_seq,
+       auth_user_user_permissions_id_seq,
+       django_admin_log_id_seq,
+       django_content_type_id_seq,
+       django_site_id_seq,
+       patchwork_bundle_id_seq,
+       patchwork_bundle_patches_id_seq,
+       patchwork_comment_id_seq,
+       patchwork_patch_id_seq,
+       patchwork_person_id_seq,
+       patchwork_project_id_seq,
+       patchwork_state_id_seq,
+       patchwork_userpersonconfirmation_id_seq,
+       patchwork_userprofile_id_seq,
+       patchwork_userprofile_maintainer_projects_id_seq,
+       registration_registrationprofile_id_seq
+TO "www-data";
+
+-- allow the mail user (in this case, 'nobody') to add patches
+GRANT INSERT, SELECT ON
+       patchwork_patch,
+       patchwork_comment,
+       patchwork_person
+TO "nobody";
+GRANT SELECT ON
+       patchwork_project,
+       patchwork_state
+TO "nobody";
+GRANT UPDATE, SELECT ON
+       patchwork_patch_id_seq,
+       patchwork_person_id_seq,
+       patchwork_comment_id_seq
+TO "nobody";
+
+COMMIT;
+

diff --git a/lib/sql/grant-all.sql b/lib/sql/grant-all.sql
deleted file mode 100644 (file)
index 72e1f31..0000000
--- a/lib/sql/grant-all.sql
+++ /dev/null
@@ -1,69 +0,0 @@
-BEGIN;
--- give necessary permissions to the web server. Becuase the admin is all
--- web-based, these need to be quite permissive
-GRANT SELECT, UPDATE, INSERT, DELETE ON
-       auth_message,
-       django_session,
-       django_site,
-       django_admin_log,
-       django_content_type,
-       auth_group_permissions,
-       auth_user,
-       auth_user_groups,
-       auth_group,
-       auth_user_user_permissions,
-       auth_permission,
-       patchwork_userpersonconfirmation,
-       patchwork_state,
-       patchwork_comment,
-       patchwork_person,
-       patchwork_userprofile,
-       patchwork_userprofile_maintainer_projects,
-       patchwork_project,
-       patchwork_bundle,
-       patchwork_bundle_patches,
-       patchwork_patch,
-       registration_registrationprofile
-TO "www-data";
-GRANT SELECT, UPDATE ON
-       auth_group_id_seq,
-       auth_group_permissions_id_seq,
-       auth_message_id_seq,
-       auth_permission_id_seq,
-       auth_user_groups_id_seq,
-       auth_user_id_seq,
-       auth_user_user_permissions_id_seq,
-       django_admin_log_id_seq,
-       django_content_type_id_seq,
-       django_site_id_seq,
-       patchwork_bundle_id_seq,
-       patchwork_bundle_patches_id_seq,
-       patchwork_comment_id_seq,
-       patchwork_patch_id_seq,
-       patchwork_person_id_seq,
-       patchwork_project_id_seq,
-       patchwork_state_id_seq,
-       patchwork_userpersonconfirmation_id_seq,
-       patchwork_userprofile_id_seq,
-       patchwork_userprofile_maintainer_projects_id_seq,
-       registration_registrationprofile_id_seq
-TO "www-data";
-
--- allow the mail user (in this case, 'nobody') to add patches
-GRANT INSERT, SELECT ON
-       patchwork_patch,
-       patchwork_comment,
-       patchwork_person
-TO "nobody";
-GRANT SELECT ON
-       patchwork_project,
-       patchwork_state
-TO "nobody";
-GRANT UPDATE, SELECT ON
-       patchwork_patch_id_seq,
-       patchwork_person_id_seq,
-       patchwork_comment_id_seq
-TO "nobody";
-
-COMMIT;
-