search

bluszcz / ejabberd-diaspora-auth

Ejabberd external authentication trough Diaspora* database.
3 stars 1 forks source link

Add MySQL back-end #2

Open lnjX opened 6 years ago

lnjX commented 6 years ago

This would be great! :smile: Otherwise I can't use it currently. :/

bluszcz commented 6 years ago

Hm, I don't have such setup next to me - do you have some replicable (docker or similar) environment to test?

lnjX commented 6 years ago

Unfortunately, not but I'm sure there's something on the docker hub. Also testing is not the problem; I could do that.

bluszcz commented 6 years ago

@LNJ2 I would need:

show create table users;

and 

select * from users where username = 'testusername';

from your diaspora instance and this should be enough. Don't provide real username there since it will output the password. And I need raw password output from database to test. So simply use test user or change password after that. You have been warned :)

Can you do it today?

lnjX commented 6 years ago

Only tomorrow (probably different timezone: UTC+2).

bluszcz commented 6 years ago

CEST here ;)

lnjX commented 6 years ago

Ups .. didn't see you're from Barcelona! :)

Here's the ouput:

MariaDB [diaspora_production]> show create table users;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `serialized_private_key` text COLLATE utf8mb4_bin,
  `getting_started` tinyint(1) NOT NULL DEFAULT '1',
  `disable_mail` tinyint(1) NOT NULL DEFAULT '0',
  `language` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `encrypted_password` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `reset_password_token` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `remember_created_at` datetime DEFAULT NULL,
  `sign_in_count` int(11) DEFAULT '0',
  `current_sign_in_at` datetime DEFAULT NULL,
  `last_sign_in_at` datetime DEFAULT NULL,
  `current_sign_in_ip` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `last_sign_in_ip` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `invited_by_id` int(11) DEFAULT NULL,
  `authentication_token` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `unconfirmed_email` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `confirm_email_token` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `locked_at` datetime DEFAULT NULL,
  `show_community_spotlight_in_stream` tinyint(1) NOT NULL DEFAULT '1',
  `auto_follow_back` tinyint(1) DEFAULT '0',
  `auto_follow_back_aspect_id` int(11) DEFAULT NULL,
  `hidden_shareables` text COLLATE utf8mb4_bin,
  `reset_password_sent_at` datetime DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `remove_after` datetime DEFAULT NULL,
  `export` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `exported_at` datetime DEFAULT NULL,
  `exporting` tinyint(1) DEFAULT '0',
  `strip_exif` tinyint(1) DEFAULT '1',
  `exported_photos_file` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `exported_photos_at` datetime DEFAULT NULL,
  `exporting_photos` tinyint(1) DEFAULT '0',
  `color_theme` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `post_default_public` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_users_on_username` (`username`(191)),
  UNIQUE KEY `index_users_on_email` (`email`(191)),
  UNIQUE KEY `index_users_on_authentication_token` (`authentication_token`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [diaspora_production]> select * from users where username = 'testuser';
+----+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------+----------+---------------+--------------------------------------------------------------+----------------------+---------------------+---------------+---------------------+---------------------+--------------------+-----------------+---------------------+---------------------+---------------+----------------------+-------------------+---------------------+-----------+------------------------------------+------------------+----------------------------+-------------------+------------------------+---------------------+--------------+--------+-------------+-----------+------------+----------------------+--------------------+------------------+-------------+---------------------+
| id | username | serialized_private_key                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | getting_started | disable_mail | language | email         | encrypted_password                                           | reset_password_token | remember_created_at | sign_in_count | current_sign_in_at  | last_sign_in_at     | current_sign_in_ip | last_sign_in_ip | created_at          | updated_at          | invited_by_id | authentication_token | unconfirmed_email | confirm_email_token | locked_at | show_community_spotlight_in_stream | auto_follow_back | auto_follow_back_aspect_id | hidden_shareables | reset_password_sent_at | last_seen           | remove_after | export | exported_at | exporting | strip_exif | exported_photos_file | exported_photos_at | exporting_photos | color_theme | post_default_public |
+----+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------+----------+---------------+--------------------------------------------------------------+----------------------+---------------------+---------------+---------------------+---------------------+--------------------+-----------------+---------------------+---------------------+---------------+----------------------+-------------------+---------------------+-----------+------------------------------------+------------------+----------------------------+-------------------+------------------------+---------------------+--------------+--------+-------------+-----------+------------+----------------------+--------------------+------------------+-------------+---------------------+
|  4 | testuser | -----BEGIN RSA PRIVATE KEY-----
MIIJKgIBAAKCAgEA0RXQw1iU7CwS0KwEOeZkBayxlU/Ki+WGSPpKNimeoBqRQ2lv
2p4iw+wEDeItxlJzhYxL2E+Fn46fgCJ5gd/vO+wluJxffA4Oj2F/0fusaap9o/XO
1Hs7zDL/puHqueU7XOQLAc0lSpuj7qP3ZQ6TnCOd4j1jCasjjS2lzK2pYAC48a0s
CDzURbWYHRBkCFAfm0z5hBWcelp8XQUDDm/icgnPWP6YItojWRB+WW+Si2AdZ7m1
kBuTdujJSNQY36YB6TfkgP+oxe+n4B8r1Q91CWq1JffSiZlV/MmM2/MvaddrXz/S
/nLQ9aFgqbh77QPeysHMW49TYfskv7rPdjhIZukh1TqJBW8TBpHFxR6Wf5OyF5XI
zqCIprfk09qPZ9AujlXAz1ER20rMZPEztJyRjniWwJej1Tz2BdE4pknWwJhDZ45e
tocQmx0BDMd+MSOuCHdQq1VPEhSQZps7UkM8mii94cna+Rfa5lLctJwYcg5GWEj1
OdKqYgR9lR14l3Dv/jK70lFMjcOZfrN/w33JMZDBgxa9VREAub0+cUfDC8ITz61s
/huMcpFswcn5p8bNHHgTrV46y3Y1swBH5QClP2iGTtZTQfBXi7DGb92x8Hj5G01S
jFVDBEm8iWNXyfa/L81U1NGMkgQtIauxsuGpY1/fI/FyrOIxchKiWjPG6gsCAwEA
AQKCAgEAjOEEHvuKrG8t5khhOJoR/vjTVuXCdeybRITQiBoW9Y2xHxjl8wnmJtja
CnjBmwVnLS7Q09kREedJdU7FTEsUqO1+pRtIAxB9owJw242WKERmD5nKWMUID5i1
RT6np8Xa78X/3KDbZZ/lHyB/yym9L9GXyh7Oom8brz54nqbMnxEdjEnAh3RlGus2
qS6oGCnUghGldegR3Scm8I419v5QN8KAYVh6LdGJS7OfTqesuBh097Tbr1PFUrOb
HGU8tUTnuLqeToT0tRm7pXmbUR3vH/80O3b19xzIIWG0dWcwJUKu6CjbA/Nc9IRd
2z2g0pxu+3MFPRQffL95jW5T5zYZIXYHlgERk5+Q9TVO4D+8D5+81IIGLno8i2Fb
YxFfwXacxAZ8KhXL+XEzTVQezp/CYcHjyK4pPsvdcvql6gAda+Lbykg/aWNJuAR0
CdMVrsLnnlU/LGgulUxEAAxqUtmDFPDQfJiOvd6mRKZdd4NopsQo06mug+AQnYle
2z77j7Jj07VzhOSa24IJidFBQo4uwickRFl+9txLXYAW+XD2WPeLpQOV8Q/46t24
nS6fesa7YCTZWKXlW5r+5pQCX9XxM9EQAm89J0h/TstUIB0nhcSCsScC9gRH8qN2
UkXDMSN7/eb283LgvITuz5G3RIg23n2R2EBHrQpFM2naoBOvEWECggEBAPKchQWs
gkK/Eb9iTIeUZ767sWg3ehtWhlJ3k5nAFJf07S67amM6Uvrv8db4FxWJwQNPC3QU
JeLdCcsg1lfRHNYDdmAIPBRCWhRoaR46xenDPxtkqnSRYAXKrBsZ8UvOg5Z8vnPZ
DQu7iRNDUGdd2b5rHGtKvwdh0B2y4T9uvItrxQMWYnFwA+wbTsdM2nJLZgyo1Rx+
1wHd4bNO+5g3ujc351MMULIzUEefHI9w4DYHKI2CqbpbxQeHlUVZD6LyYF4sWm0+
1Fjv7pjgIGxfRkZVjUwvPEWZm43l71VFRe0FetmBzYOBUiKZ1xpa4jfFTlXKKQUy
mHC4/OTZdpwEZVMCggEBANyfqAVrDdD4P8e7hiP8lt1plGmur2LA6AAA04Ntq19J
UzeN1nQJ+VSfS8PRCzvGXV7M820VeJJvWUSeQtBvefJSydzx0KO9mLBQTFWz+ELX
xo9KFcZ8G2/wtVakbTkdVAkajd+12Ljz5lFQtGzA769jQKy8MvAs0IBlgBAid3vP
ZDTtIwYdKrOrtX1ED15i1dX8zw8DpajPswUe7mYXxBBtPoqGZyLpCve0PHOWjr0Q                                                                                                                                                                                                               
caAA8+As4vk+7NdwWPPnA57JwQbo3RmWQHKieQmBfZe7DC4UFTjqfyBnx+lgzFPD                                                                                                                                                                                                               
RqtgIkxig9QDbr1/o4FTEBX84DI/hAIOLqpJ52rS2WkCggEBAJwVTJNY57jvWgip
whibxp7z++/TeTBrWyFlIsWQyO72OT2Of6R1GnussQcfNyK5LD7ntLOqXrF0iV9w
sdVviEC7m4Wu5pAWHAPzJGW5ZCi/XzNNcUhrWr5/JHiPGSkvJINFn/XCF12L+mU1
Ml4+B9804+S3Au1J5W2l7rozkQOVgvs4hH78lNlqvCUGcaeEkJPmbqzVgZgG2rTU
RJMqjxgFVEKesq/iyWw6llJGSUkZE+LuxClL7xeoB8XExYWMr6kGmeqcuFq7ieI6
l0GBjiGj2rK/1OPmLih9XJucZJ9XnjCfDMvlACWYmcWv4MgGnR4eYIOiiGZE7RaC
PYekYFcCggEAChseHTFsLphQg5upZ1iYqIe/jp+71PA1MFrZVXnekbzQHQlYO3SB
gUjStaOLui8586+7rtF+9mY3VpdRRC73DQyRxpclEXsL1Xe4J2zZAGbV55ZjLPZe
Y2BJTjC1+BnJ0ajvusGfrckuUac6vIiR8hy/x6nPQ4Up/9emaBbMFlXHp0z9f7gy
ToydAaHuekpYtTahXXSzT+pDrwZdsTuBdacraDkD3GuEXxhYdIr++tYX/lZM1YX4
16X0P2IcEQASAGIJf4GHLzbcuOOMW80craiZxDqTRkonjJp0Y9JVSbj2Mb9zksAo
fbuyhOel27SsVfWBZ4vVRirUJGTCPvhtkQKCAQEApKRHpRN1zr4AG1NlzrS2KnAR
4osHaBUVPjwOKYI4ZPdHtrnLh9SkQPifKhIUWna1aqCdqyfzlhaL6Cin2pZtfj1i
s1bqRQAtHrIa+IdqOCj+JoU1T00Oe7RUc0kMWf0IlTVFVsxdySiEr2pLnayB1bnl
ZGVMM9Kl7l4alorxJRsBFzL1/YbR1hPokzboBg9+k2JAgaM/ECF3FYDbv37SZiRA
eI2BFZwpzpJrlbF5hKyvPFiwT9epMH7BFh8Gg/tgOe1+1RVwME3H9pluOy6dV/1R
QXYgRcl1dMZobE0khSfd3XBdL3lfZcqMYr1GFIHpL+iw+Fn1AmUgARFqWv70aw==
-----END RSA PRIVATE KEY-----
 |               1 |            0 | en       | test@mail.com | $2a$10$6P5A7jFNtvVJHJ56/R9vAeQbxqPj0I3OaOzktch9a6wkKI4MnUUum | NULL                 | NULL                |             1 | 2018-04-08 12:24:48 | 2018-04-08 12:24:48 | 77.179.34.129      | 77.179.34.129   | 2018-04-08 12:24:47 | 2018-04-08 12:24:48 |             1 | NULL                 | NULL              | NULL                | NULL      |                                  1 |                0 |                       NULL | NULL              | NULL                   | 2018-04-08 12:24:48 | NULL         | NULL   | NULL        |         0 |          1 | NULL                 | NULL               |                0 | original    |                   0 |
+----+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------+----------+---------------+--------------------------------------------------------------+----------------------+---------------------+---------------+---------------------+---------------------+--------------------+-----------------+---------------------+---------------------+---------------+----------------------+-------------------+---------------------+-----------+------------------------------------+------------------+----------------------------+-------------------+------------------------+---------------------+--------------+--------+-------------+-----------+------------+----------------------+--------------------+------------------+-------------+---------------------+
1 row in set (0.00 sec)
Neustradamus commented 5 years ago

Any news since one year?

Neustradamus commented 4 years ago

@bluszcz: Any news since more two years?

bluszcz commented 4 years ago

Well, not using diaspora anymore. Can do some sponsored development or we can put it on hacktober fest...