schema.sql 4.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107
  1. -- MySQL dump 10.16 Distrib 10.1.37-MariaDB, for Linux (x86_64)
  2. --
  3. -- Host: localhost Database: dovecot
  4. -- ------------------------------------------------------
  5. -- Server version 10.1.37-MariaDB
  6. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  7. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  8. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  9. /*!40101 SET NAMES utf8 */;
  10. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  11. /*!40103 SET TIME_ZONE='+00:00' */;
  12. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  13. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  14. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  15. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  16. --
  17. -- Table structure for table `aliases`
  18. --
  19. USE dovecot;
  20. DROP TABLE IF EXISTS `aliases`;
  21. /*!40101 SET @saved_cs_client = @@character_set_client */;
  22. /*!40101 SET character_set_client = utf8 */;
  23. CREATE TABLE `aliases` (
  24. `action` varchar(16) NOT NULL,
  25. `config` varchar(255) NOT NULL,
  26. `email` varchar(129) NOT NULL,
  27. `relay` tinyint(1) DEFAULT '0',
  28. PRIMARY KEY (`email`)
  29. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  30. /*!40101 SET character_set_client = @saved_cs_client */;
  31. --
  32. -- Table structure for table `users`
  33. --
  34. DROP TABLE IF EXISTS `users`;
  35. /*!40101 SET @saved_cs_client = @@character_set_client */;
  36. /*!40101 SET character_set_client = utf8 */;
  37. CREATE TABLE `users` (
  38. `username` varchar(64) NOT NULL,
  39. `domain` varchar(64) NOT NULL,
  40. `salt` varchar(8) NOT NULL,
  41. `hash` char(64) NOT NULL,
  42. PRIMARY KEY (`username`,`domain`)
  43. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  44. /*!40101 SET character_set_client = @saved_cs_client */;
  45. --
  46. -- Dumping routines for database 'dovecot'
  47. --
  48. /*!50003 DROP PROCEDURE IF EXISTS `add_user` */;
  49. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  50. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  51. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  52. /*!50003 SET character_set_client = utf8 */ ;
  53. /*!50003 SET character_set_results = utf8 */ ;
  54. /*!50003 SET collation_connection = utf8_general_ci */ ;
  55. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  56. /*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  57. DELIMITER ;;
  58. CREATE DEFINER=`root`@`localhost` PROCEDURE `add_user`(IN uname VARCHAR(64), IN dname VARCHAR(64), IN password TEXT)
  59. BEGIN
  60. DECLARE newsalt VARCHAR(8);
  61. SET newsalt = LEFT(UUID(), 8);
  62. INSERT INTO users (username, domain, salt, hash) VALUES (uname, dname, newsalt, SHA2(CONCAT(newsalt,password), 256));
  63. END ;;
  64. DELIMITER ;
  65. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  66. /*!50003 SET character_set_client = @saved_cs_client */ ;
  67. /*!50003 SET character_set_results = @saved_cs_results */ ;
  68. /*!50003 SET collation_connection = @saved_col_connection */ ;
  69. /*!50003 DROP PROCEDURE IF EXISTS `set_user_password` */;
  70. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  71. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  72. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  73. /*!50003 SET character_set_client = utf8 */ ;
  74. /*!50003 SET character_set_results = utf8 */ ;
  75. /*!50003 SET collation_connection = utf8_general_ci */ ;
  76. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  77. /*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  78. DELIMITER ;;
  79. CREATE DEFINER=`root`@`localhost` PROCEDURE `set_user_password`(IN uname VARCHAR(64), IN dname VARCHAR(64), IN password TEXT)
  80. BEGIN
  81. DECLARE newsalt VARCHAR(8);
  82. SET newsalt = LEFT(UUID(), 8);
  83. UPDATE users SET salt=newsalt, hash=SHA2(CONCAT(newsalt,password), 256) WHERE username=uname AND domain=dname;
  84. END ;;
  85. DELIMITER ;
  86. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  87. /*!50003 SET character_set_client = @saved_cs_client */ ;
  88. /*!50003 SET character_set_results = @saved_cs_results */ ;
  89. /*!50003 SET collation_connection = @saved_col_connection */ ;
  90. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
  91. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  92. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  93. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  94. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  95. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  96. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  97. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
  98. -- Dump completed on 2019-04-02 8:26:40