Database¶

The database is mostly used by the local API but also by cscli for some tasks.

Currently, 3 databases are supported:

sqlite (default database)
mysql
postgresql

Warning

It is recommanded to use mysql or postgresql if you expect to have a lot of traffic on the API.

The database configuration can be found in the crowdsec configuration file (default: /etc/crowdsec/config.yaml).

Its located under the db_config block.

Configuration Examples¶

SQLite

db_config:
  type: sqlite
  db_path: /var/lib/crowdsec/data/crowdsec.db
  flush:
    max_items: 5000
    max_age: 7d

MySQL

db_config:
  type: mysql
  user: crowdsec
  password: crowdsecpassword
  db_name: crowdsec
  host: "127.0.0.1"
  port: 3306
  flush:
    max_items: 5000
    max_age: 7d

PostgreSQL

db_config:
  type: postgresql
  user: crowdsec
  password: crowdsecpassword
  db_name: crowdsec
  host: "127.0.0.1"
  port: 3306
  flush:
    max_items: 5000
    max_age: 7d

Configuration Format¶

`db_config`¶

Contains the configuration of the database

db_config:
  type:     <db_type>

  db_path:  <path_to_database_file>  # for sqlite

  user:     <db_user>      # for mysql/pgsql
  password: <db_password>  # for mysql/pgsql
  db_name:  <db_name>      # for mysql/pgsql
  host:     <db_host_ip>   # for mysql/pgsql
  port:     <db_host_port> # for mysql/pgsql
  flush:
    max_items: <max_alerts_in_db>
    max_age: <max_age_of_alerts_in_db>

Configuration Directives¶

`type`¶

db_config:
  type: sqlite

The type of database to use. It can be:

sqlite
mysql
postgresql

`db_path`¶

db_config:
  type: sqlite
  db_path: "/var/lib/crowdsec/data/crowdsec.db

The path to the database file (only if the type of database is sqlite)

`user`¶

db_config:
  type: mysql|postgresql

  user: foo

The username to connect to the database (only if the type of database is mysql or postgresql)

`password`¶

db_config:
  type: mysql|postgresql

  password: foobar

The password to connect to the database (only if the type of database is mysql or postgresql)

`db_name`¶

db_config:
  type: mysql|postgresql

  db_name: crowdsec

The database name to connect to (only if the type of database is mysql or postgresql)

`db_host`¶

db_config:
  type: mysql|postgresql

  user: foo

The host to connect to (only if the type of database is mysql or postgresql)

`db_port`¶

db_config:
  type: mysql|postgresql

  user: foo

The port to connect to (only if the type of database is mysql or postgresql)

`flush`¶

flush:
  max_items: <nb_max_alerts_in_database>
  max_age: <max_alerts_age_in_database>

`max_items`¶

int

Number max of alerts in database.

`max_age`¶

string

Alerts retention time.

Supported units:

s: seconds
m: minutes
h: hours
d: days

Database schema¶

crowdsec-agent uses the ent framework to manage the database.

This is the schema of the database (as seen by entc describe)

Alert:
    +-----------------+-----------+--------+----------+----------+---------+---------------+-----------+----------------------------------+------------+
    |      Field      |   Type    | Unique | Optional | Nillable | Default | UpdateDefault | Immutable |            StructTag             | Validators |
    +-----------------+-----------+--------+----------+----------+---------+---------------+-----------+----------------------------------+------------+
    | id              | int       | false  | false    | false    | false   | false         | false     | json:"id,omitempty"              |          0 |
    | created_at      | time.Time | false  | false    | false    | true    | false         | false     | json:"created_at,omitempty"      |          0 |
    | updated_at      | time.Time | false  | false    | false    | true    | false         | false     | json:"updated_at,omitempty"      |          0 |
    | scenario        | string    | false  | false    | false    | false   | false         | false     | json:"scenario,omitempty"        |          0 |
    | bucketId        | string    | false  | true     | false    | true    | false         | false     | json:"bucketId,omitempty"        |          0 |
    | message         | string    | false  | true     | false    | true    | false         | false     | json:"message,omitempty"         |          0 |
    | eventsCount     | int32     | false  | true     | false    | true    | false         | false     | json:"eventsCount,omitempty"     |          0 |
    | startedAt       | time.Time | false  | true     | false    | true    | false         | false     | json:"startedAt,omitempty"       |          0 |
    | stoppedAt       | time.Time | false  | true     | false    | true    | false         | false     | json:"stoppedAt,omitempty"       |          0 |
    | sourceIp        | string    | false  | true     | false    | false   | false         | false     | json:"sourceIp,omitempty"        |          0 |
    | sourceRange     | string    | false  | true     | false    | false   | false         | false     | json:"sourceRange,omitempty"     |          0 |
    | sourceAsNumber  | string    | false  | true     | false    | false   | false         | false     | json:"sourceAsNumber,omitempty"  |          0 |
    | sourceAsName    | string    | false  | true     | false    | false   | false         | false     | json:"sourceAsName,omitempty"    |          0 |
    | sourceCountry   | string    | false  | true     | false    | false   | false         | false     | json:"sourceCountry,omitempty"   |          0 |
    | sourceLatitude  | float32   | false  | true     | false    | false   | false         | false     | json:"sourceLatitude,omitempty"  |          0 |
    | sourceLongitude | float32   | false  | true     | false    | false   | false         | false     | json:"sourceLongitude,omitempty" |          0 |
    | sourceScope     | string    | false  | true     | false    | false   | false         | false     | json:"sourceScope,omitempty"     |          0 |
    | sourceValue     | string    | false  | true     | false    | false   | false         | false     | json:"sourceValue,omitempty"     |          0 |
    | capacity        | int32     | false  | true     | false    | false   | false         | false     | json:"capacity,omitempty"        |          0 |
    | leakSpeed       | string    | false  | true     | false    | false   | false         | false     | json:"leakSpeed,omitempty"       |          0 |
    | scenarioVersion | string    | false  | true     | false    | false   | false         | false     | json:"scenarioVersion,omitempty" |          0 |
    | scenarioHash    | string    | false  | true     | false    | false   | false         | false     | json:"scenarioHash,omitempty"    |          0 |
    | simulated       | bool      | false  | false    | false    | true    | false         | false     | json:"simulated,omitempty"       |          0 |
    +-----------------+-----------+--------+----------+----------+---------+---------------+-----------+----------------------------------+------------+
    +-----------+----------+---------+---------+----------+--------+----------+
    |   Edge    |   Type   | Inverse | BackRef | Relation | Unique | Optional |
    +-----------+----------+---------+---------+----------+--------+----------+
    | owner     | Machine  | true    | alerts  | M2O      | true   | true     |
    | decisions | Decision | false   |         | O2M      | false  | true     |
    | events    | Event    | false   |         | O2M      | false  | true     |
    | metas     | Meta     | false   |         | O2M      | false  | true     |
    +-----------+----------+---------+---------+----------+--------+----------+

Bouncer:
    +------------+-----------+--------+----------+----------+---------+---------------+-----------+-----------------------------+------------+
    |   Field    |   Type    | Unique | Optional | Nillable | Default | UpdateDefault | Immutable |          StructTag          | Validators |
    +------------+-----------+--------+----------+----------+---------+---------------+-----------+-----------------------------+------------+
    | id         | int       | false  | false    | false    | false   | false         | false     | json:"id,omitempty"         |          0 |
    | created_at | time.Time | false  | false    | false    | true    | false         | false     | json:"created_at,omitempty" |          0 |
    | updated_at | time.Time | false  | false    | false    | true    | false         | false     | json:"updated_at,omitempty" |          0 |
    | name       | string    | true   | false    | false    | false   | false         | false     | json:"name,omitempty"       |          0 |
    | api_key    | string    | false  | false    | false    | false   | false         | false     | json:"api_key,omitempty"    |          0 |
    | revoked    | bool      | false  | false    | false    | false   | false         | false     | json:"revoked,omitempty"    |          0 |
    | ip_address | string    | false  | true     | false    | true    | false         | false     | json:"ip_address,omitempty" |          0 |
    | type       | string    | false  | true     | false    | false   | false         | false     | json:"type,omitempty"       |          0 |
    | version    | string    | false  | true     | false    | false   | false         | false     | json:"version,omitempty"    |          0 |
    | until      | time.Time | false  | true     | false    | true    | false         | false     | json:"until,omitempty"      |          0 |
    | last_pull  | time.Time | false  | false    | false    | true    | false         | false     | json:"last_pull,omitempty"  |          0 |
    +------------+-----------+--------+----------+----------+---------+---------------+-----------+-----------------------------+------------+

Decision:
    +--------------+-----------+--------+----------+----------+---------+---------------+-----------+-------------------------------+------------+
    |    Field     |   Type    | Unique | Optional | Nillable | Default | UpdateDefault | Immutable |           StructTag           | Validators |
    +--------------+-----------+--------+----------+----------+---------+---------------+-----------+-------------------------------+------------+
    | id           | int       | false  | false    | false    | false   | false         | false     | json:"id,omitempty"           |          0 |
    | created_at   | time.Time | false  | false    | false    | true    | false         | false     | json:"created_at,omitempty"   |          0 |
    | updated_at   | time.Time | false  | false    | false    | true    | false         | false     | json:"updated_at,omitempty"   |          0 |
    | until        | time.Time | false  | false    | false    | false   | false         | false     | json:"until,omitempty"        |          0 |
    | scenario     | string    | false  | false    | false    | false   | false         | false     | json:"scenario,omitempty"     |          0 |
    | type         | string    | false  | false    | false    | false   | false         | false     | json:"type,omitempty"         |          0 |
    | start_ip     | int64     | false  | true     | false    | false   | false         | false     | json:"start_ip,omitempty"     |          0 |
    | end_ip       | int64     | false  | true     | false    | false   | false         | false     | json:"end_ip,omitempty"       |          0 |
    | start_suffix | int64     | false  | true     | false    | false   | false         | false     | json:"start_suffix,omitempty" |          0 |
    | end_suffix   | int64     | false  | true     | false    | false   | false         | false     | json:"end_suffix,omitempty"   |          0 |
    | ip_size      | int64     | false  | true     | false    | false   | false         | false     | json:"ip_size,omitempty"      |          0 |
    | scope        | string    | false  | false    | false    | false   | false         | false     | json:"scope,omitempty"        |          0 |
    | value        | string    | false  | false    | false    | false   | false         | false     | json:"value,omitempty"        |          0 |
    | origin       | string    | false  | false    | false    | false   | false         | false     | json:"origin,omitempty"       |          0 |
    | simulated    | bool      | false  | false    | false    | true    | false         | false     | json:"simulated,omitempty"    |          0 |
    +--------------+-----------+--------+----------+----------+---------+---------------+-----------+-------------------------------+------------+
    +-------+-------+---------+-----------+----------+--------+----------+
    | Edge  | Type  | Inverse |  BackRef  | Relation | Unique | Optional |
    +-------+-------+---------+-----------+----------+--------+----------+
    | owner | Alert | true    | decisions | M2O      | true   | true     |
    +-------+-------+---------+-----------+----------+--------+----------+

Event:
    +------------+-----------+--------+----------+----------+---------+---------------+-----------+-----------------------------+------------+
    |   Field    |   Type    | Unique | Optional | Nillable | Default | UpdateDefault | Immutable |          StructTag          | Validators |
    +------------+-----------+--------+----------+----------+---------+---------------+-----------+-----------------------------+------------+
    | id         | int       | false  | false    | false    | false   | false         | false     | json:"id,omitempty"         |          0 |
    | created_at | time.Time | false  | false    | false    | true    | false         | false     | json:"created_at,omitempty" |          0 |
    | updated_at | time.Time | false  | false    | false    | true    | false         | false     | json:"updated_at,omitempty" |          0 |
    | time       | time.Time | false  | false    | false    | false   | false         | false     | json:"time,omitempty"       |          0 |
    | serialized | string    | false  | false    | false    | false   | false         | false     | json:"serialized,omitempty" |          1 |
    +------------+-----------+--------+----------+----------+---------+---------------+-----------+-----------------------------+------------+
    +-------+-------+---------+---------+----------+--------+----------+
    | Edge  | Type  | Inverse | BackRef | Relation | Unique | Optional |
    +-------+-------+---------+---------+----------+--------+----------+
    | owner | Alert | true    | events  | M2O      | true   | true     |
    +-------+-------+---------+---------+----------+--------+----------+

Machine:
    +-------------+-----------+--------+----------+----------+---------+---------------+-----------+------------------------------+------------+
    |    Field    |   Type    | Unique | Optional | Nillable | Default | UpdateDefault | Immutable |          StructTag           | Validators |
    +-------------+-----------+--------+----------+----------+---------+---------------+-----------+------------------------------+------------+
    | id          | int       | false  | false    | false    | false   | false         | false     | json:"id,omitempty"          |          0 |
    | created_at  | time.Time | false  | false    | false    | true    | false         | false     | json:"created_at,omitempty"  |          0 |
    | updated_at  | time.Time | false  | false    | false    | true    | false         | false     | json:"updated_at,omitempty"  |          0 |
    | machineId   | string    | true   | false    | false    | false   | false         | false     | json:"machineId,omitempty"   |          0 |
    | password    | string    | false  | false    | false    | false   | false         | false     | json:"password,omitempty"    |          0 |
    | ipAddress   | string    | false  | false    | false    | false   | false         | false     | json:"ipAddress,omitempty"   |          0 |
    | scenarios   | string    | false  | true     | false    | false   | false         | false     | json:"scenarios,omitempty"   |          1 |
    | version     | string    | false  | true     | false    | false   | false         | false     | json:"version,omitempty"     |          0 |
    | isValidated | bool      | false  | false    | false    | true    | false         | false     | json:"isValidated,omitempty" |          0 |
    | status      | string    | false  | true     | false    | false   | false         | false     | json:"status,omitempty"      |          0 |
    +-------------+-----------+--------+----------+----------+---------+---------------+-----------+------------------------------+------------+
    +--------+-------+---------+---------+----------+--------+----------+
    |  Edge  | Type  | Inverse | BackRef | Relation | Unique | Optional |
    +--------+-------+---------+---------+----------+--------+----------+
    | alerts | Alert | false   |         | O2M      | false  | true     |
    +--------+-------+---------+---------+----------+--------+----------+

Meta:
    +------------+-----------+--------+----------+----------+---------+---------------+-----------+-----------------------------+------------+
    |   Field    |   Type    | Unique | Optional | Nillable | Default | UpdateDefault | Immutable |          StructTag          | Validators |
    +------------+-----------+--------+----------+----------+---------+---------------+-----------+-----------------------------+------------+
    | id         | int       | false  | false    | false    | false   | false         | false     | json:"id,omitempty"         |          0 |
    | created_at | time.Time | false  | false    | false    | true    | false         | false     | json:"created_at,omitempty" |          0 |
    | updated_at | time.Time | false  | false    | false    | true    | false         | false     | json:"updated_at,omitempty" |          0 |
    | key        | string    | false  | false    | false    | false   | false         | false     | json:"key,omitempty"        |          0 |
    | value      | string    | false  | false    | false    | false   | false         | false     | json:"value,omitempty"      |          1 |
    +------------+-----------+--------+----------+----------+---------+---------------+-----------+-----------------------------+------------+
    +-------+-------+---------+---------+----------+--------+----------+
    | Edge  | Type  | Inverse | BackRef | Relation | Unique | Optional |
    +-------+-------+---------+---------+----------+--------+----------+
    | owner | Alert | true    | metas   | M2O      | true   | true     |
    +-------+-------+---------+---------+----------+--------+----------+

Database¶

Configuration Examples¶

Configuration Format¶

db_config¶

Configuration Directives¶

type¶

db_path¶

user¶

password¶

db_name¶

db_host¶

db_port¶

flush¶

max_items¶

max_age¶