# Two expert bots to lookup contact information in a database and apply notification rules
Part of the [intelmq-cb-mailgen solution](https://github.com/Intevation/intelmq-mailgen-release).
An overview of the setup can be gained from the [IntelMQ Mailgen Docs](https://github.com/Intevation/intelmq-mailgen/tree/master/docs).
## Contact DB
### Automatic versus manual contacts
Two types of contacts are supported.
Both are modeled with a set of tables that differ
in the table name and in some columns.
#### 'Automatic' for externally maintained infos
The tables ending with `_automatic` shall hold contact
information that are maintained externally to the system.
They are designed so they can be updated or reimported
in a straight forward way. Several import sources are possible.
Therefore they have columns for `import_source` and `import_time`
in order to later decide which information to use. And there are
no fields for additional information as those informatiom may potentially
get lost or be incorrect if the contents of the database changes
during an update or re-import.
The example for an external import source is contact data from RIPE,
please see [README-ripe-import.md](README-ripe-import.md).
#### 'Manual' to hold special cases
If the automatic contacts contain mistakes or a special case is needed,
the "manual" tables (that have no additional name suffix) can be used.
The information in the manual tables is to be considered the primary
source and maintained within this contact database.
Those special information shall be entered by regular users of the system
and should not interfere with the original external data. To keep the
system robust and consistent, there are no direct links
to the automatic tables.
To add special information to an already existing contact in the automatic
tables, all relevant tables entries of this specific automatic contact
have to be copied into the manual tables. The manual contact tables lack
columns about import source, but have comment and annotations
possibilities.
The comments are free-text fields that can be used to record additional
information that cannot not be expressed with the existing structured fields.
This allows for rare entries or to see what additional info people
would like to see in the database in future version of the database schema.
The annotations can be used as general "tags" to steer behaviour of
the system that can be configured on the level of administration rights,
but selected on the user level. The "simple" tags are designed to best be used
to denote a group of properties for sending behaviour that can be combined.
It is recommended to keep the numer of tags low.
The tags have to be tuned after gaining experience in production use
and they will be easy to use if good tags are chosen over time.
The `inhibition`-annotations expose a generic way to prevent
sending out information based on field contents of the intelmq event
to users. Which is important if no administrator is available on short notice
or the behaviour is an exception that does not indicate a change in the general
behaviour groups. The `inhibition`-annotations are powerful
at the expense of being harder to use.
#### How to update external sources?
The manual entries have additional information to the automatic ones,
but also contain copied parts of the automatic entries.
If an automatic import source is updated, the additional
and copied information may be out of date and in need of change.
Example 1: the manual entry corrects a contact email address.
With the next update, the email address in the RIPE database is
updated to the correct one. Ideally the manual entry is deleted
as it is not needed anymore. (Though it does not harm if is kept
for a while.)
Example 2: again the manual entry corrects a contact email address.
Now a network not is not served anymore by the same organisation coming
with the RIPE database update. In this case the CIDR has to be removed
from the manuel entry, because otherwise notifications will be send that
should not go to the organisation anymore.
As there is no algorithmic way to know which info is better after an update
and thus which kind of changes have to be made,
a human has to involved.
At time of writing, this has to be done by an administrator, e.g once
a week or every two weeks for each import source, like:
1. Check diffs if you would import for necessary changes.
2. Stop the lookup expert-bot.
3. Do the manual changes (e.g. with fody).
4. Completly replace the automatic tables of this source with the new version.
5. Restart the lookup bot.
For RIPE there is a script that will show the difference
between old and new automatic entries
and which manual entries are affected by theses changes (see link
to the RIPE documentation above).
### Database Setup
Requires the python module psycopg2 (which is already installed if
you also use the postgresql-output bot).
Requires at least Postgresql v>=9.5 (compare to intelmq-fody-backend).
The following commands assume that PostgreSQL is running and listening on the
default port. They create a database called "contactdb" which matches the
default configuration of the bot.
```
su - postgres
createdb --encoding=UTF8 --template=template0 contactdb
psql -f /usr/share/doc/intelmq-certbund-contact/sql/initdb.sql contactdb
```
A database user with the right to select the data in the Contact DB
must be created. This is the account, which will be used in the bot's
configuration for accessing the database.
```
createuser intelmq --pwprompt
psql -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO intelmq;" contactdb
```
#### Adding default email tags
The names, values and default values for tags directly associated with
an email address have to be saved in the database.
(Maybe future version of fody-backend and fody will allow editing them, but
as of 2021-02-25 they don't.)
The following example adds two tag names with a few values:
```sql
INSERT INTO tag_name (tag_name, tag_name_order) VALUES ('Format', 1);
-- -> id 1
INSERT INTO tag_name (tag_name, tag_name_order) VALUES ('Constituency', 2);
-- -> id 2
COPY tag (tag_name_id, tag_value, tag_description, is_default) FROM STDIN
WITH CSV;
1,CSV_inline,CSV inline,true
1,CSV_attachment,CSV attachment,false
2,network_operators,Network Operators,true
2,government,Government,false
2,CNI,Critical National Infrastructure,false
2,CNI_energy,CNI Energy,false
\.
```
(Tags for emails have been added with release 0.9.4 in 2019-05.)
### Schema Updates
When upgrading to new version take a look at
[UPDATE.mg](UPDATE.md).
### Adding New Contacts
Contacts can be added to the database directly using SQL.
Though most users are expected to use a frontend application like Fody.
When put into the tables for manual contacts, the entries
will take precedence over contacts which
were imported automatically, i.e. by `ripe_import.py`.
Connect to the database:
```sh
su - postgres
psql contactdb
```
Add a contact:
```sql
-- 1. Prepare contact information
\set asn 3320
-- unique name of the organization:
\set org_name 'org1'
\set org_comment 'Example comment on organization.'
\set contact_email 'test@example.com'
\set contact_comment 'Test comment on contact.'
-- set the notification interval in seconds
-- an interval of -1 means no notifications will be generated
\set notification_interval 0
-- 2. Add new contact
BEGIN TRANSACTION;
INSERT INTO autonomous_system (number) VALUES (:asn);
WITH new_org AS (
INSERT INTO organisation (name,comment)
VALUES (:'org_name',:'org_comment')
RETURNING id
),
new_contact AS (
INSERT INTO contact (email,format_id,comment)
VALUES (:'contact_email', 2, :'contact_comment')
RETURNING id
),
new_ota AS (
INSERT INTO organisation_to_asn (organisation_id,asn_id,notification_interval)
VALUES (
(SELECT id from new_org), :asn, :notification_interval
)
)
INSERT INTO role (organisation_id,contact_id) VALUES (
(SELECT id from new_org),
(SELECT id from new_contact)
)
;
COMMIT TRANSACTION;
```
### Example change tags
If we call the simple text annotations tags, how could we change a tag
that has already be used?
Of course the notification rules may have to be changed
(see mailgen documentation) and possibly the list of known tags
that the fody-backend sends to fody.
For example the following sql command on database `contactdb`
change the tag `whitelist-malware` to `Whitelist:Malware`:
```sql
BEGIN;
UPDATE autonomous_system_annotation
SET annotation = '{"tag": "Whitelist:Malware"}'
WHERE annotation ->> 'tag' = 'whitelist-malware';
UPDATE organisation_annotation
SET annotation = '{"tag": "Whitelist:Malware"}'
WHERE annotation ->> 'tag' = 'whitelist-malware';
UPDATE network_annotation
SET annotation = '{"tag": "Whitelist:Malware"}'
WHERE annotation ->> 'tag' = 'whitelist-malware';
UPDATE fqdn_annotation
SET annotation = '{"tag": "Whitelist:Malware"}'
WHERE annotation ->> 'tag' = 'whitelist-malware';
COMMIT;
```
## Suppress notification of contacts based upon certain criteria:
It ist possible to suppress the notification of contacts based upon certain
criteria. Such can be: AS-number, IP addresses, FQDN, or Organisations.
To suppress notifications for such an Object, one has to create an annotation to
such an Object.
Remember: It's up to the `Rule Expert` to determine if such an annotation is
evaluated or not.
# Generating a graphic which visualizes the schema of the ContactDB
You can use `postgresql-autodoc` to do this. PG autodoc is available on both
debian and ubuntu via apt.
Raw data
{
"_id": null,
"home_page": "https://github.com/Intevation/intelmq-certbund-contact",
"name": "intelmq-certbund-contact",
"maintainer": "Intevation GmbH",
"docs_url": null,
"requires_python": ">=3.4",
"maintainer_email": "sebastian.wagner@intevation.de",
"keywords": "",
"author": "Intevation GmbH",
"author_email": "",
"download_url": "https://files.pythonhosted.org/packages/ef/2e/1564eead66decb0c13f69deceb932ec28ecebcc6eb5da960d66fd60f5cda/intelmq_certbund_contact-0.9.8.tar.gz",
"platform": null,
"description": "# Two expert bots to lookup contact information in a database and apply notification rules\n\nPart of the [intelmq-cb-mailgen solution](https://github.com/Intevation/intelmq-mailgen-release).\n\nAn overview of the setup can be gained from the [IntelMQ Mailgen Docs](https://github.com/Intevation/intelmq-mailgen/tree/master/docs).\n\n## Contact DB\n\n### Automatic versus manual contacts\n\nTwo types of contacts are supported.\nBoth are modeled with a set of tables that differ\nin the table name and in some columns.\n\n#### 'Automatic' for externally maintained infos\n\nThe tables ending with `_automatic` shall hold contact\ninformation that are maintained externally to the system.\nThey are designed so they can be updated or reimported\nin a straight forward way. Several import sources are possible.\n\nTherefore they have columns for `import_source` and `import_time`\nin order to later decide which information to use. And there are\nno fields for additional information as those informatiom may potentially\nget lost or be incorrect if the contents of the database changes\nduring an update or re-import.\n\nThe example for an external import source is contact data from RIPE,\nplease see [README-ripe-import.md](README-ripe-import.md).\n\n#### 'Manual' to hold special cases\n\nIf the automatic contacts contain mistakes or a special case is needed,\nthe \"manual\" tables (that have no additional name suffix) can be used.\nThe information in the manual tables is to be considered the primary\nsource and maintained within this contact database.\n\nThose special information shall be entered by regular users of the system\nand should not interfere with the original external data. To keep the\nsystem robust and consistent, there are no direct links\nto the automatic tables.\n\nTo add special information to an already existing contact in the automatic\ntables, all relevant tables entries of this specific automatic contact\nhave to be copied into the manual tables. The manual contact tables lack\ncolumns about import source, but have comment and annotations\npossibilities.\n\nThe comments are free-text fields that can be used to record additional\ninformation that cannot not be expressed with the existing structured fields.\nThis allows for rare entries or to see what additional info people\nwould like to see in the database in future version of the database schema.\n\nThe annotations can be used as general \"tags\" to steer behaviour of\nthe system that can be configured on the level of administration rights,\nbut selected on the user level. The \"simple\" tags are designed to best be used\nto denote a group of properties for sending behaviour that can be combined.\nIt is recommended to keep the numer of tags low.\nThe tags have to be tuned after gaining experience in production use\nand they will be easy to use if good tags are chosen over time.\n\nThe `inhibition`-annotations expose a generic way to prevent\nsending out information based on field contents of the intelmq event\nto users. Which is important if no administrator is available on short notice\nor the behaviour is an exception that does not indicate a change in the general\nbehaviour groups. The `inhibition`-annotations are powerful\nat the expense of being harder to use.\n\n\n#### How to update external sources?\nThe manual entries have additional information to the automatic ones,\nbut also contain copied parts of the automatic entries.\n\nIf an automatic import source is updated, the additional\nand copied information may be out of date and in need of change.\n\nExample 1: the manual entry corrects a contact email address.\n With the next update, the email address in the RIPE database is\n updated to the correct one. Ideally the manual entry is deleted\n as it is not needed anymore. (Though it does not harm if is kept\n for a while.)\n\nExample 2: again the manual entry corrects a contact email address.\n Now a network not is not served anymore by the same organisation coming\n with the RIPE database update. In this case the CIDR has to be removed\n from the manuel entry, because otherwise notifications will be send that\n should not go to the organisation anymore.\n\nAs there is no algorithmic way to know which info is better after an update\nand thus which kind of changes have to be made,\na human has to involved.\n\nAt time of writing, this has to be done by an administrator, e.g once\na week or every two weeks for each import source, like:\n 1. Check diffs if you would import for necessary changes.\n 2. Stop the lookup expert-bot.\n 3. Do the manual changes (e.g. with fody).\n 4. Completly replace the automatic tables of this source with the new version.\n 5. Restart the lookup bot.\n\nFor RIPE there is a script that will show the difference\nbetween old and new automatic entries\nand which manual entries are affected by theses changes (see link\nto the RIPE documentation above).\n\n\n### Database Setup\nRequires the python module psycopg2 (which is already installed if\nyou also use the postgresql-output bot).\n\nRequires at least Postgresql v>=9.5 (compare to intelmq-fody-backend).\n\nThe following commands assume that PostgreSQL is running and listening on the\ndefault port. They create a database called \"contactdb\" which matches the\ndefault configuration of the bot.\n\n```\n su - postgres\n\n createdb --encoding=UTF8 --template=template0 contactdb\n psql -f /usr/share/doc/intelmq-certbund-contact/sql/initdb.sql contactdb\n```\n\nA database user with the right to select the data in the Contact DB\nmust be created. This is the account, which will be used in the bot's\nconfiguration for accessing the database.\n\n```\n createuser intelmq --pwprompt\n psql -c \"GRANT SELECT ON ALL TABLES IN SCHEMA public TO intelmq;\" contactdb\n\n```\n\n#### Adding default email tags\n\nThe names, values and default values for tags directly associated with\nan email address have to be saved in the database.\n(Maybe future version of fody-backend and fody will allow editing them, but\nas of 2021-02-25 they don't.)\n\nThe following example adds two tag names with a few values:\n\n```sql\nINSERT INTO tag_name (tag_name, tag_name_order) VALUES ('Format', 1);\n-- -> id 1\nINSERT INTO tag_name (tag_name, tag_name_order) VALUES ('Constituency', 2);\n-- -> id 2\n\nCOPY tag (tag_name_id, tag_value, tag_description, is_default) FROM STDIN\n WITH CSV;\n1,CSV_inline,CSV inline,true\n1,CSV_attachment,CSV attachment,false\n2,network_operators,Network Operators,true\n2,government,Government,false\n2,CNI,Critical National Infrastructure,false\n2,CNI_energy,CNI Energy,false\n\\.\n```\n\n(Tags for emails have been added with release 0.9.4 in 2019-05.)\n\n\n### Schema Updates\n\nWhen upgrading to new version take a look at\n[UPDATE.mg](UPDATE.md).\n\n### Adding New Contacts\n\nContacts can be added to the database directly using SQL.\nThough most users are expected to use a frontend application like Fody.\n\nWhen put into the tables for manual contacts, the entries\nwill take precedence over contacts which\nwere imported automatically, i.e. by `ripe_import.py`.\n\nConnect to the database:\n\n```sh\n su - postgres\n psql contactdb\n\n```\nAdd a contact:\n\n```sql\n\n-- 1. Prepare contact information\n\n \\set asn 3320\n -- unique name of the organization:\n \\set org_name 'org1'\n \\set org_comment 'Example comment on organization.'\n \\set contact_email 'test@example.com'\n \\set contact_comment 'Test comment on contact.'\n -- set the notification interval in seconds\n -- an interval of -1 means no notifications will be generated\n \\set notification_interval 0\n\n-- 2. Add new contact\n\n BEGIN TRANSACTION;\n INSERT INTO autonomous_system (number) VALUES (:asn);\n WITH new_org AS (\n INSERT INTO organisation (name,comment)\n VALUES (:'org_name',:'org_comment')\n RETURNING id\n ),\n new_contact AS (\n INSERT INTO contact (email,format_id,comment)\n VALUES (:'contact_email', 2, :'contact_comment')\n RETURNING id\n ),\n new_ota AS (\n INSERT INTO organisation_to_asn (organisation_id,asn_id,notification_interval)\n VALUES (\n (SELECT id from new_org), :asn, :notification_interval\n )\n )\n INSERT INTO role (organisation_id,contact_id) VALUES (\n (SELECT id from new_org),\n (SELECT id from new_contact)\n )\n ;\n COMMIT TRANSACTION;\n\n```\n\n### Example change tags\n\nIf we call the simple text annotations tags, how could we change a tag\nthat has already be used?\n\nOf course the notification rules may have to be changed\n(see mailgen documentation) and possibly the list of known tags\nthat the fody-backend sends to fody.\n\nFor example the following sql command on database `contactdb`\nchange the tag `whitelist-malware` to `Whitelist:Malware`:\n\n```sql\nBEGIN;\n\nUPDATE autonomous_system_annotation\n SET annotation = '{\"tag\": \"Whitelist:Malware\"}'\n WHERE annotation ->> 'tag' = 'whitelist-malware';\n\nUPDATE organisation_annotation\n SET annotation = '{\"tag\": \"Whitelist:Malware\"}'\n WHERE annotation ->> 'tag' = 'whitelist-malware';\n\nUPDATE network_annotation\n SET annotation = '{\"tag\": \"Whitelist:Malware\"}'\n WHERE annotation ->> 'tag' = 'whitelist-malware';\n\nUPDATE fqdn_annotation\n SET annotation = '{\"tag\": \"Whitelist:Malware\"}'\n WHERE annotation ->> 'tag' = 'whitelist-malware';\n\nCOMMIT;\n```\n\n\n## Suppress notification of contacts based upon certain criteria:\n\nIt ist possible to suppress the notification of contacts based upon certain\ncriteria. Such can be: AS-number, IP addresses, FQDN, or Organisations.\n\nTo suppress notifications for such an Object, one has to create an annotation to\nsuch an Object.\n\nRemember: It's up to the `Rule Expert` to determine if such an annotation is\nevaluated or not.\n\n# Generating a graphic which visualizes the schema of the ContactDB\n\nYou can use `postgresql-autodoc` to do this. PG autodoc is available on both\ndebian and ubuntu via apt.\n",
"bugtrack_url": null,
"license": "AGPL-3.0-or-later",
"summary": "IntelMQ Contacts is a contact database for IntelMQ with related expert bots",
"version": "0.9.8",
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "9a7ecfd9766363e5bb514b9e92221fb8b358b1f8f46e6c29757190728e5f0fdf",
"md5": "448e9f54737cbf614122440c0e9bd1a1",
"sha256": "9f25f44a20648dc8315ee3e1b632a891e3f325fed9b6cb9b30e74d4ae40d3a2b"
},
"downloads": -1,
"filename": "intelmq_certbund_contact-0.9.8-py3-none-any.whl",
"has_sig": true,
"md5_digest": "448e9f54737cbf614122440c0e9bd1a1",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.4",
"size": 54418,
"upload_time": "2023-03-14T09:38:10",
"upload_time_iso_8601": "2023-03-14T09:38:10.229545Z",
"url": "https://files.pythonhosted.org/packages/9a/7e/cfd9766363e5bb514b9e92221fb8b358b1f8f46e6c29757190728e5f0fdf/intelmq_certbund_contact-0.9.8-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "ef2e1564eead66decb0c13f69deceb932ec28ecebcc6eb5da960d66fd60f5cda",
"md5": "b7a0913407cd3963534fbda56b523476",
"sha256": "a4800e4ab395f43e9cbf36e688b671883f3baca8410e8106ad0e8fef505e0b93"
},
"downloads": -1,
"filename": "intelmq_certbund_contact-0.9.8.tar.gz",
"has_sig": true,
"md5_digest": "b7a0913407cd3963534fbda56b523476",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.4",
"size": 48369,
"upload_time": "2023-03-14T09:38:13",
"upload_time_iso_8601": "2023-03-14T09:38:13.092519Z",
"url": "https://files.pythonhosted.org/packages/ef/2e/1564eead66decb0c13f69deceb932ec28ecebcc6eb5da960d66fd60f5cda/intelmq_certbund_contact-0.9.8.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-03-14 09:38:13",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "Intevation",
"github_project": "intelmq-certbund-contact",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "intelmq-certbund-contact"
}
JSON
