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.

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

Postgresql data initial setup and mysql data initial setup are different #5355

Open
angarod opened this issue Oct 24, 2019 · 5 comments

Comments

@angarod
Copy link

@angarod angarod commented Oct 24, 2019

Work environment

Questions Answers
Type of issue Bug
OS version (server) Alpine Docker
OS version (client) Windows 10
PHP version 7.3.10
MISP version / git hash 2.4.117
Browser Firefox 70

Expected behavior

Connect to https://misp/users/login without error

Actual behavior

SQLSTATE[42703]: Undefined column: 7 ERROR: column Server.priority does not exist LINE 1: ...

Steps to reproduce the behavior

Just connect the latest misp to a postgresql version, load the structure of postgresql and load initial config in postgresql.

Logs, screenshots, configuration dump, ...

2019-10-24 12:42:08.193 UTC [797] STATEMENT: ALTER TABLE servers ADD priority int(11) NOT NULL DEFAULT 0;
2019-10-24 12:42:08.326 UTC [797] ERROR: column "priority" does not exist
2019-10-24 12:42:08.326 UTC [797] STATEMENT: CREATE INDEX idx_servers_priority ON servers (priority);
2019-10-24 12:42:08.471 UTC [797] ERROR: column Server.priority does not exist at character 939
2019-10-24 12:42:08.471 UTC [797] STATEMENT: SELECT "Server"."id" AS "Server__id", "Server"."name" AS "Server__name", "Server"."url" AS "Server__url", "Server"."authkey" AS "Server__authkey", "Server"."org_id" AS "Server__org_id", "Server"."push" AS "Server__push", "Server"."pull" AS "Server__pull", "Server"."lastpulledid" AS "Server__lastpulledid", "Server"."lastpushedid" AS "Server__lastpushedid", "Server"."organization" AS "Server__organization", "Server"."remote_org_id" AS "Server__remote_org_id", "Server"."publish_without_email" AS "Server__publish_without_email", "Server"."unpublish_event" AS "Server__unpublish_event", "Server"."self_signed" AS "Server__self_signed", "Server"."pull_rules" AS "Server__pull_rules", "Server"."push_rules" AS "Server__push_rules", "Server"."cert_file" AS "Server__cert_file", "Server"."client_cert_file" AS "Server__client_cert_file", "Server"."internal" AS "Server__internal" FROM "public"."servers" AS "Server" WHERE 1 = 1 ORDER BY "Server"."priority" ASC, "Server"."id" ASC

This issue is caused by the table servers in https://github.com/MISP/MISP/blob/2.4/INSTALL/POSTGRESQL-structure.sql#L1166 which not defined the priority field although the mysql default structure located in https://github.com/MISP/MISP/blob/2.4/INSTALL/MYSQL.sql did it

@srcr

This comment has been minimized.

Copy link

@srcr srcr commented Oct 24, 2019

I'm running into the same issue, but as a manual fix you could add the missing 3 columns with this query :

psql -U misp -d misp

alter table servers
add column skip_proxy SMALLINT NOT NULL DEFAULT 0,
add column caching_enabled SMALLINT NOT NULL DEFAULT 0,
add column priority BIGINT NOT NULL DEFAULT 0;
@angarod

This comment has been minimized.

Copy link
Author

@angarod angarod commented Oct 25, 2019

It adds some columns in appropriate tables, but MISP try to do some mysql commands to my database postgres with the file https://github.com/MISP/MISP/blob/2.4/app/Model/AppModel.php
More technically the file AppModel.php try to add the table user_settings to my database with the command

"CREATE` TABLE IF NOT EXISTS `user_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting` varchar(255) COLLATE utf8_bin NOT NULL, `value` text COLLATE utf8_bin NOT NULL, `user_id` int(11) NOT NULL, INDEX `setting` (`setting`), INDEX `user_id` (`user_id`), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
Which is definitely a mysql command located at line https://github.com/MISP/MISP/blob/2.4/app/Model/AppModel.php#L1076

@srcr

This comment has been minimized.

Copy link

@srcr srcr commented Oct 25, 2019

Yes I noticed. I have created a delta PostgreSQL file to create the full database, but still it fails due to other issues for some reason in MySQL you can add true + true + true to make 3.

MySQL:
CASE WHEN ("Role"."perm_add" + "Role"."perm_modify" + "Role"."perm_publish" = 3) THEN '3'
PostgreSQL could be:
CASE WHEN ("Role"."perm_add"::int + "Role"."perm_modify"::int + "Role"."perm_publish"::int = 3) THEN '3'
Or perhaps:
CASE WHEN ("Role"."perm_add" AND "Role"."perm_modify" AND "Role"."perm_publish") THEN '3'

This is at line 47 of Role.php

Delta PostgreSQL is here:
https://gist.github.com/srcr/84b81180b4337496757d8defa469d754

@angarod

This comment has been minimized.

Copy link
Author

@angarod angarod commented Oct 25, 2019

Thanks i have created the table user_settings but for some reasons it output an error
psql:./POSTGRESQL-structure.sql:1900: ERROR: relation "user_settings" does not exist

Will wait on a better postgresql support from the developers.

@iglocska

This comment has been minimized.

Copy link
Member

@iglocska iglocska commented Oct 25, 2019

Basically MISP, through the framework we use will cache the data model. This means that adding a table to the DB itself won't solve the issue outright - you still need to purge the caches. To do so, click on "clean caches" in the diagnostics tab or just run the following:

rm -f /var/www/MISP/app/tmp/cache/models/myapp*
rm -f /var/www/MISP/app/tmp/cache/persistent/myapp*
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.