Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add primary key to brutforces table #7522

Open
artemis061 opened this issue Jun 25, 2021 · 2 comments · May be fixed by #9090
Open

Add primary key to brutforces table #7522

artemis061 opened this issue Jun 25, 2021 · 2 comments · May be fixed by #9090
Labels
needs triage This issue has been automatically labelled and needs further triage

Comments

@artemis061
Copy link

artemis061 commented Jun 25, 2021 •
edited

Is your feature request related to a problem? Please describe.
I am currently working on creating a highly available MISP deployment with a distributed mysql database cluster. I was unable to login to the web ui after installing MISP due to the bruteforces table in the database not having a primary key.

Describe the solution you'd like
I would like an "official" primary key included in the MISP repository so that when I rebuild my docker images, they do not need additional SQL modifications.

Describe alternatives you've considered
Current code in MYSQL.sql:

-- --------------------------------------------------------

--
-- Table structure for table `bruteforces`
--

CREATE TABLE IF NOT EXISTS `bruteforces` (
  `ip` varchar(255) COLLATE utf8_bin NOT NULL,
  `username` varchar(255) COLLATE utf8_bin NOT NULL,
  `expire` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

My changes to get the mysql clustering working:

-- --------------------------------------------------------

--
-- Table structure for table `bruteforces`
--

CREATE TABLE IF NOT EXISTS `bruteforces` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(255) COLLATE utf8_bin NOT NULL,
  `username` varchar(255) COLLATE utf8_bin NOT NULL,
  `expire` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Additional context
My setup currently includes:
haproxy docker container load-balancing for 2 MISP servers running in docker (images custom built)
standalone redis server that both MISP servers communicate with (each MISP server is assigned a different database on the redis server)
1 mysql router directing traffic into the mysql cluster
3 mysql servers with HA clustering using innodb storage engine
The 2 MISP servers are configured to connect to the mysql cluster via the mysql router
@artemis061 artemis061 added the needs triage This issue has been automatically labelled and needs further triage label Jun 25, 2021
@c0dewhacker
Copy link

@artemis061 Also came across this issue using a MySQL HA cluster and mysql-router.

You can also alter the database once MISP has initialised by issuing -

ALTER TABLE bruteforces ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

I'll create a pull request that updates -

I don't see any other usages for when the bruteforces table is created except in the Postgresql table creation. I'm not sure if Postgresql would encounter the same issue, however, for consistency sake i should probably update INSTALL/POSTGRESQL-structure.sql and INSTALL/POSTGRESQL-data-initial.sql

Your update looks correct. INSTALL/MYSQL.sql would be -

CREATE TABLE IF NOT EXISTS `bruteforces` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(255) COLLATE utf8_bin NOT NULL,
  `username` varchar(255) COLLATE utf8_bin NOT NULL,
  `expire` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

also I think db_schema.json would need to be

"bruteforces": [
            {
                "column_name": "id",
                "is_nullable": "NO",
                "data_type": "int",
                "character_maximum_length": null,
                "numeric_precision": "10",
                "collation_name": null,
                "column_type": "int(10) unsigned",
                "column_default": null,
                "extra": "auto_increment"
            },
            {
                "column_name": "ip",
                "is_nullable": "NO",
                "data_type": "varchar",
                "character_maximum_length": "255",
                "numeric_precision": null,
                "collation_name": "utf8mb3_bin",
                "column_type": "varchar(255)",
                "column_default": null,
                "extra": ""
            },
            {
                "column_name": "username",
                "is_nullable": "NO",
                "data_type": "varchar",
                "character_maximum_length": "255",
                "numeric_precision": null,
                "collation_name": "utf8mb3_bin",
                "column_type": "varchar(255)",
                "column_default": null,
                "extra": ""
            },
            {
                "column_name": "expire",
                "is_nullable": "NO",
                "data_type": "datetime",
                "character_maximum_length": null,
                "numeric_precision": null,
                "collation_name": null,
                "column_type": "datetime",
                "column_default": null,
                "extra": ""
            }
        ]

In the Postgresql table creation INSTALL/POSTGRESQL-data-initial.sql would probably need to be -

COPY public.bruteforces (id, ip, username, expire) FROM stdin;

and INSTALL/POSTGRESQL-structure.sql

CREATE TABLE public.bruteforces (
    id serial PRIMARY KEY,
    ip character varying(255) NOT NULL,
    username character varying(255) NOT NULL,
    expire timestamp with time zone NOT NULL
);

Let me know if this looks correct and i'll submit a pull request.

@c0dewhacker c0dewhacker linked a pull request May 22, 2023 that will close this issue
Open
3 tasks
@artemis061
Copy link
Author

@c0dewhacker
Thank you for the reply, ultimately we decided to implement HA MISP within AWS using amazon aurora with multi-az option. This fit our requirements instead of running it on prem.

I concur with the changes for mysql, I am not much of a postgres developer and am not currently in a position to reattempt with your proposed changes. I'm good with it if you are!

Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
needs triage This issue has been automatically labelled and needs further triage
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix: [misp-core] MySQL HA DB Schema Fix for bruteforces table c0dewhacker/MISP
2 participants
@c0dewhacker @artemis061