whitelist no longer working

Question

whitelist no longer working

Closed this issue 9 years ago · 7 comments

I had to reinstall frontend files. rsyslog setup and sql data was not altered at it was already functional.

but : whitelisting does not work. I keep having messages which should be filtered out.

i tried to delete and recreatte whitelist rule : no luck
i export rules, truncat tables whitelist and whitelist_mem and reimport rules : no luck

any pointer ?

Answer 1 · 2015-12-18T14:44:47.000Z

Hi Thomas,

Please excuse the delayed reply, but things have been really busy this week.

We have had similar issues in other installations after the some recent changes.

After replacing the php files with the latest master, have you taken any action in the database, other than reimporting the whitelist tables?

Answer 2 · 2015-12-18T14:59:48.000Z

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Hello,

Don nothing on db

Have a nice weekend

Thomas Constans
Opendoor
+33 6 23 37 87 85

Sent from mobile phone.
Envoyé de mon téléphone, excusez la brièveté.

Le 18 décembre 2015 15:44:48 UTC+01:00, gadamo notifications@github.com a écrit :

Hi Thomas,

Please excuse the delayed reply, but things have been really busy this
week.

We have had similar issues in other installations after the some recent
changes.

After replacing the php files with the latest master, have you taken
any action in the database, other than reimporting the whitelist
tables?

Reply to this email directly or view it on GitHub:
#32 (comment)
-----BEGIN PGP SIGNATURE-----
Version: APG v1.1.1

iQFJBAEBCgAzBQJWdB9XLBxUaG9tYXMgQ29uc3RhbnMgKG5vbmUpIDx0aG9tYXNA
b3BlbmRvb3IuZnI+AAoJELoZdF+AQQVB75gIAIlXSwwa3GjwxJqibehQUHGkStT1
MwF6eoLvcfJx3vsrdrtukugtOCh7Md5QuYAkfzZpIKXD5gsuXsmlHahBqbY/2Fkj
o8DvgMLmyYEthe/4rYmSpi6wr8j6d0Wd2X/+EF+svqxurHwyL+rnUe/ENDhYkya7
d1w1ZR30rcnD3ypDL/LrY8tr7m3CLI46yggRJ6Nz6B4XqKYLj7E9szbtWsKtHVnM
Fh4sXHeF/q7DWkMGG8V7+1njH4OB7Ct02mJqAtALsIk4ZOKkuUBg89BpiafFo14l
soBUB9EASu1EVc1JGVp9OKDunA2ujKLQUth6Llxd741Bx/rUa1ZedzYO1+k=
=9Ns3
-----END PGP SIGNATURE-----

Answer 3 · 2015-12-21T10:19:23.000Z

Since i can not be sure how dated your old installation was, we should first check if you've already ran the code in schema/updates/v0.4-to-v0.5.sql. Within mysql issue the following query:

DESC syslog;

In case you see the column updated_at still there, we should take the appropriate steps to upgrade to current master. Let me know so we can proceed resolving this.

Answer 4 · 2015-12-22T15:42:49.000Z

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Hello Gadamo,

Pretty busy on my side too, sorry for high latency answer !

Le 21/12/2015 11:19, gadamo a écrit :

+-------------+---------------------+------+-----+---------+------------
- ----+
  | Field       | Type                | Null | Key | Default | Extra
    |
  +-------------+---------------------+------+-----+---------+------------
- ----+
  | id          | bigint(20) unsigned | NO   | PRI | NULL    |
  auto_increment |
  | host        | int(10) unsigned    | NO   | MUL | NULL    |
    |
  | facility    | tinyint(3) unsigned | YES  | MUL | 0       |
    |
  | priority    | tinyint(3) unsigned | YES  |     | 0       |
    |
  | level       | tinyint(3) unsigned | YES  | MUL | NULL    |
    |
  | program     | varchar(255)        | YES  | MUL | NULL    |
    |
  | pid         | int(11) unsigned    | YES  |     | NULL    |
    |
  | tag         | varchar(255)        | YES  |     | NULL    |
    |
  | msg         | text                | YES  | MUL | NULL    |
    |
  | received_ts | datetime            | YES  |     | NULL    |
    |
  | created_at  | datetime            | YES  |     | NULL    |
    |
  +-------------+---------------------+------+-----+---------+------------
- ----+

In case you see the column updated_at still there, we should take
the appropriate steps to upgrade to current master. Let me know so
we can proceed resolving this.

Thomas Constans
Services en informatique libre
T: 33(0)6 23 37 87 85
@: http://opendoor.fr
#: https://twitter.com/ThomasConstans
gpg: 0xBA19745F80410541
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQEcBAEBCAAGBQJWeW9VAAoJELoZdF+AQQVB0qEIAKyd/X6HpBXhsFxNSbNnuWGt
vG0/n/8/ufzAa0+3ztMGJ+vwIxZZde7rP50c1hNxEpMtb4YNpQkn01VyukwU76Lh
14dA9TDMtoEwHSNjmgnGY/n4pPPszNfNUPlpEpDzmsSU+MsH+Utyxr/WSRWtx0eq
aNNpVw6PF8jvXmHZjOHuiGEYymO/GU51PX8q3W3qiZZTRTO2Q7EkHMO1nD/d5n+5
uCRsAS5nmyk/qezB42a652xsOzmrHwS50oYPzrd9NyfwLn/0YmjDy2SEG/RHZNgw
ih8iRhCEsCn9BnS2CDHRb4qP/MNZk5objVDu72A9xCle9vDC/BYJYxMelNEZt0E=
=XPaT
-----END PGP SIGNATURE-----

Answer 5 · 2015-12-23T08:54:30.000Z

OK. since 'updated_at' is already removed from your setup version, all you should need to do is to take a few more upgrade steps.

This will also ensure you are running the latest fixes and the same version we do.

Disable mysql's event scheduler:

mysql -e "SET GLOBAL EVENT_SCHEDULER=off;"

Import the updated functions, triggers and events (will not affect your data in syslog/archive/whitelists):

mysql ETS_echofish < schema/echofish-functions.sql
mysql ETS_echofish < schema/echofish-procedures.sql
mysql ETS_echofish < schema/echofish-triggers.sql
mysql ETS_echofish < schema/echofish-events.sql

Reactivate the event scheduler:

mysql -e "SET GLOBAL EVENT_SCHEDULER=on;"

The database schema should now be in par with the frontend version you recently pulled. Check that the whitelist trigger works by "faking" a change on all whitelist entries:

UPDATE whitelist SET id=id;

If this process fails, find out the location of your mysql logfiles:

SHOW VARIABLES WHERE variable_name IN ('log_error','general_log_file');

Monitor the error log of mysql for any messages & let us know of the result.

Answer 6 · 2015-12-23T11:14:11.000Z

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

gadamo,

The syslog database had grown to a point (7g) where any operation on
it was impossible. (echofish is installed on a small vm)

I noticed that when issuing "UPDATE whitelist SET id=id;" i had a
whole bunch of "CREATE PROCEDURE" mysql process competing / waiting
for locks.

I had no other solution than dropping and recreating echofish
database, then importing my whitelist-backup.xml file.

Now whitelisting is once again working

I notice one difference between current installation and old one:

In configuration i have 6 settings whereas after upgrade i had only
one (archive_activated).

Best regards

Le 23/12/2015 09:54, gadamo a écrit :

1

Thomas Constans
Services en informatique libre
T: 33(0)6 23 37 87 85
@: http://opendoor.fr
#: https://twitter.com/ThomasConstans
gpg: 0xBA19745F80410541

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQEcBAEBCAAGBQJWeoGvAAoJELoZdF+AQQVBHJwH/12HAhNBaEHqnoXjtx9HNsO4
QaWhiAfB5BwaH3OHdRBMg//pfnmdCeoPz6u81XlxLsRHSp8KBSk8Cb3G0KcOHQHN
H2Ui+3VwIbMdJWoNsjNmlsTr4gdXu0L1yusExlWOqPn8J3d155i9su/NVRKa+g3K
P84DZPaD3rgCMK5Qdzuac210Zv6RKaD6QEiMil7wMw+ngq74lJEpOLPXQCjSPGDd
ljedz/E+7XCJUh3TEV0znnygGWKMaWewh1znWBIvVTB/KfAlgknfKP8Hfyuy9nH/
psc5F8FU9rys3wuiI3YI9fxoE/Ey1tAdt2j99iPF5cg5vhlie7h97NIPHG0zy3g=
=lgIp
-----END PGP SIGNATURE-----

Answer 7 · 2015-12-23T13:12:41.000Z

Your syslog table was way too large, we could attempt triggering the whitelisting in smaller batches.

Anyway, I am glad your installation is functional again.

Those new settings will help trim old entries in the archive in a less brutal way (until now, this was accomplished with TRUNCATE).