Schlagwort-Archive: SQL

Absicherung für Cron in RapidMiner Server

(English version)

Seit der Version 7.2 ist der RapidMiner Server in einer eingeschränkten Version frei erhältlich, und es lassen sich sehr nützliche Dinge damit machen.

Eine Kernfunktion ist das geplante Ausführen von Prozessen. Das passiert mit Hilfe von Cron-Ausdrücken, wobei die Implementierung in RM Server die Cron-Syntax vorne um ein Sekunden-Feld erweitert. Das erhöht natürlich die Flexibilität, aber birgt eine Gefahr in sich: wenn man nicht aufpaßt, legt man ganz schnell einen Cron-Job an, der jede Sekunde einen Prozess startet. Das kann auch den besten Server schnell in die Knie zwingen.

Leider ist die Benutzeroberfläche so gestaltet, daß die Standardeinstellungen genau dieses Problem verursachen:

Cron Editor in RapidMiner Studio with default settings
Cron Editor in RapidMiner Studio mit den Standardeinstellungen

Wer intensiv an einem Server arbeitet, wird vielleicht früher oder später die Standardeinstellungen übernehmen. Oder es passiert jemandem in einem Training dieser Fehler. Glücklicherweise gibt es einen Weg, die Datenbank gegen diese Cron-Jobs abzusichern.

Mein RapidMiner Server verwendet eine PostgreSQL-Datenbank im Hintergrund. Die Tabelle qrtz_cron_triggers enthält die definierten Cron-Jobs. Es ist möglich, einen Trigger auf diese Tabelle zu legen, der unerwünschte Eingaben ablehnt oder, noch eleganter, korrigiert.

In PostgreSQL bestehen Trigger aus zwei Teilen: einer Triggerfunktion und dem eigentlichen Trigger. (Dadurch läßt sich eine Funktion für mehrere ähnliche Trigger verwenden, man muß sie nicht jedes Mal neu schreiben.)

Meine Triggerfunktion schaut so aus:


create or replace function check_second_cron() returns trigger
as $func$
begin
  new.cron_expression = regexp_replace(new.cron_expression, 
                                       '^\s*(\*|\d+/\d)\s', 
                                       '0 ');
  return new;
end;
$func$
language plpgsql;

Die Funktion ersetzt im hereinkommenden Cron-Ausdruck unerwünschte Sekundenangaben gegen 0. Eine unerwünschte Angabe ist * (jede Sekunde), die andere hat die Form X/Y (beginnend bei der Sekunde X, alle Y Sekunden). Hier vermeiden wir einstellige Sekundenangaben. Damit kann man einen Prozess alle 10 Sekunden oder seltener laufen lassen, aber nicht häufiger; das reduziert die Gefahr schon wesentlich.

Der reguläre Ausdruck schaut komplex aus, ist aber nicht so schlimm:

^\s* – Am Anfang darf Whitespace (Space oder Tabulator) vorkommen

(\*|\d+/\d) – Entweder ein Stern ODER Ziffer(n) gefolgt von einer Ziffer

\s – Wieder Whitespace

Der Trigger wird dann auf die Tabelle angewendet, und zwar sowohl bei Insert als auch bei Update, und auch nur, wenn der Cron-Ausdruck den unerwünschten Inhalt hat:


drop trigger if exists prevent_second_cron on qrtz_cron_triggers;
create trigger prevent_second_cron
before insert or update 
on qrtz_cron_triggers
for each row
when (new.cron_expression ~ '^\s*(\*|\d+/\d)\s')
execute procedure check_second_cron();

Damit wurde der Server gegen die unabsichtliche oder bösartige Eingabe von zu häufig ausgeführten Cron-Prozessen abgesichert. (Natürlich könnte ein Angreifer auch anders viele Prozesse gleichzeitig starten; dagegen helfen nur Queues.)

 

 

Securing Cron in RapidMiner Server

A slightly limited version of RapidMiner Server is freely available since release 7.2. It is a very useful piece of software.

Scheduled process execution is a core functionality. The execution time and frequency are specified using Cron expressions; however, the implementation in RM Server extends the usual Cron syntax by a leading Seconds field. This gives us more flexibility but is also dangerous: If we aren’t careful, we can easily schedule a process to run every second. This can cause a huge usage even on the fastest server.

Unfortunately, the default settings in the user interface expose exactly this problem:

Cron Editor in RapidMiner Studio with default settings
Cron Editor in RapidMiner Studio with default settings

People working a lot on a server will probably accept the default values, or someone in a training makes a mistake. Fortunately, there’s a way to secure the database against this kind of Cron schedule.

My RapidMiner Server uses a PostgreSQL backend database. The table qrtz_cron_triggers contains the defined Cron jobs. It is easy to create a trigger on this table that rejects undesired input, or fixes it, which I consider more elegant.

Triggers consist of two parts in PostgreSQL: a trigger function and the actual trigger. (This allows us to use the same function in many triggers instead of writing it many times.)

This is my trigger function:


create or replace function check_second_cron() returns trigger
as $func$
begin
  new.cron_expression = regexp_replace(new.cron_expression, 
                                       '^\s*(\*|\d+/\d)\s', 
                                       '0 ');
  return new;
end;
$func$
language plpgsql;

The function replaces unwanted specification of seconds by zero. One kind of unwanted entry is * (every second), the other variant has the form X/Y (start at X seconds, repeat every Y seconds). Here we deny one-digit second entries. This allows us to schedule a process every 10 or more seconds, but not more frequently. This reduces the problem by a large margin.

The regular expression seems complex but it’s not too bad:

^\s* – The beginning of the expression can contain whitespace

(\*|\d+/\d) – A star OR digit(s) followed by / and one digit

\s – Whitespace again

The trigger is placed on the table both for Insert and Update and is executed when the expression has unwanted contents:


drop trigger if exists prevent_second_cron on qrtz_cron_triggers;
create trigger prevent_second_cron
before insert or update 
on qrtz_cron_triggers
for each row
when (new.cron_expression ~ '^\s*(\*|\d+/\d)\s')
execute procedure check_second_cron();

Thus the server was secured against unintentional or malicious creation of Cron processes that run too often. (An attacker could start many processes at a time with other means, of course. This can be avoided with Queues.)

PostgreSQL: Zugriff auf fremde Datenbanken

(English version)
Komplexe Lösungen bestehen häufig aus mehr als einer Datenbank, manchmal sogar unterschiedlichen Typs. Im Fall von Sco2t haben wir eine „geerbte“ MySQL-Datenbank, während neue Daten wegen der erweiterten GIS-Funktionalität in PostgreSQL liegen. Natürlich müssen gewisse Daten zwischen beiden Systemen synchronisiert werden.

Pentaho Data Integration ist dafür eine sehr gute Lösung und wird auch intensiv verwendet. Allerdings möchte ich in einigen Fällen sofort auf Änderungen reagieren und nicht erst eine Minute später. (Und auch nicht ständig Java-Prozesse starten, die die meiste Zeit keine Änderungen finden.) Dafür bieten sich PostgreSQL-seitig Trigger an, die Änderungen an einer Tabelle in der MySQL-Datenbank nachvollziehen.

PostgreSQL 9.1 hat die erste Version der Foreign Data Wrappers genannten Funktionalität bekommen, seit 9.3 können diese sogar in fremde Datenbanken schreiben. Ein FDW-Modul für den Zugriff auf andere PostgreSQL-Server ist in 9.4 im PostgreSQL-Contrib-Bereich dabei, Module für viele andere Datenbanken (auch NoSQL und Hadoop!) sind verfügbar.

(Linked Server sind schon seit Längerem in vielen Datenbanksystemen verfügbar, PostgreSQL hatte dafür „dblink“. Diese Funktionalität hat sich aber großteils auf Verbindungen zum eigenen Datenbanktyp beschränkt.)

EnterpriseDB hat ein FDW-Modul für MySQL realisiert. Derzeit muß man es selbst kompilieren, da in den üblichen Repositories keine fertigen Pakete enthalten sind. Das ist aber nicht schwierig.

Installation auf einem Debian-System:


git clone https://github.com/EnterpriseDB/mysql_fdw.git

apt-get install make gcc libmysqlclient-dev postgresql-server-dev-9.4

cd mysql_fdw

make USE_PGXS=1

make USE_PGXS=1 install

Damit ist das Modul übersetzt und als PostgreSQL-Extension installiert. Der Rest passiert als PostgreSQL-User mit Superuser-Rechten in SQL:


CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

CREATE USER MAPPING FOR username SERVER mysql_server

OPTIONS (username 'myuser', password 'mypassword');

CREATE FOREIGN TABLE mysqltabelle (

-- Felder aus der MySQL-Tabelle mit ihrem Datentyp

)

SERVER mysql_server

OPTIONS (dbname='datenbankname', table_name='OriginalTabelleName');

Damit ist die Fremdtabelle „mysqltabelle“ in der PostgreSQL-Datenbank angelegt und kann wie eine normale Tabelle angesprochen, gelesen und beschrieben werden.

CREATE EXTENSION registriert das Modul in der aktuellen Datenbank. (Verwendet man mehrere Datenbanken auf einem Server, muß CREATE EXTENSION in jeder, die die Funktionalität braucht, ausgeführt werden.)

CREATE SERVER erzeugt die Verbindung zum fremden Datenbankserver und gibt die Verbindungsparameter an.

CREATE USER MAPPING ist notwendig, um PostgreSQL-seitige User mit einem User in der fremden Datenbank zu verknüpfen. Es können mehrere Mappings für unterschiedliche User angelegt werden.

CREATE FOREIGN TABLE definiert dann die Fremdtabelle und gibt ihren Namen in der fremden Datenbank an. Die Datentypen der Felder müssen in PostgreSQL existieren. Es ist möglich, Constraints wie NOT NULL oder Default-Werte schon PostgreSQL-seitig anzugeben.

Foreign Tables eröffnen ganz neue Möglichkeiten der Datenintegration: Daten müssen nicht mehr hin und her kopiert werden, stattdessen sind sie in der Master-Datenbank und die anderen Datenbanken arbeiten immer mit der aktuellen Kopie. Datenübernahmeprozesse können direkt in der Datenbank, ohne Zwischenschaltung einer ETL-Software ausgeführt werden. Ich bin gespannt, welche Anwendungsmöglichkeiten mir noch einfallen.

Accessing foreign databases in PostgreSQL

Many complex solutions use more than one database, sometimes even different database systems. At Sco2t we „inherited“ a MySQL database, but new data are stored in PostgreSQL because of the better GIS functionality. So some data need to be kept in sync between the two systems.

Pentaho Data Integration is a great solution for this and it’s already in heavy use. In some situations, however, I’d like to react to changes instantly instead of waiting a minute. (Also, I dont’t want to start Java processes frequently that don’t have to process any changes most of the time.) In PostgreSQL, we can use triggers for updating data in other tables, now even in the MySQL database.

PostgreSQL 9.1 was the first release with Foreign Data Wrappers functionality. With 9.3, even writing into foreign databases is supported. In 9.4 there is a FDW module for accessing other PostgreSQL servers in the Contrib area. Modules for accessing other database types (even NoSQL and Hadoop!) available.

(Linked server functionality has been available in many database systems for a long time. PostgreSQL had „dblink“. However, linked servers usually had to be of the same type.)

There is a FDW module for MySQL from EnterpriseDB. Currently we need to compile it ourselves, as there are no binary packages in the usual repositories. But it’s not hard.

This is the installation on a Debian system:


git clone https://github.com/EnterpriseDB/mysql_fdw.git

apt-get install make gcc libmysqlclient-dev postgresql-server-dev-9.4

cd mysql_fdw

make USE_PGXS=1

make USE_PGXS=1 install

After this, the module is compiled and registered as a PostgreSQL extension. The rest of the procedure has to be executed (with database superuser rights) in SQL:


CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

CREATE USER MAPPING FOR username SERVER mysql_server

OPTIONS (username 'myuser', password 'mypassword');

CREATE FOREIGN TABLE mysqltable (

-- Fields of the MySQL table, including their type

)

SERVER mysql_server

OPTIONS (dbname='databasename', table_name='OriginalTableName');

The result of this script is a foreign table „mysqltable“ in the PostgreSQL database. This table can be used like any normal table, you can read and write it.

CREATE EXTENSION registers the compiled module in the current database. (When using multiple databases on a server, CREATE EXTENSION must be executed in each database that will contain foreign tables.)

CREATE SERVER creates the link to the foreign database server and defines connection parameters.

CREATE USER MAPPING is necessary to link a PostgreSQL user to a user in the foreign database. Multiple mappings can be created for different users.

CREATE FOREIGN TABLE defines the foreign table and its name in the foreign database. The field types must exist in PostgreSQL. It is possible to define constraints like NOT NULL or give default values on the PostgreSQL side.

Foreign tables open entire new worlds of possibilities for data integration. Master data don’t need to be copied to multiple databases: other databases can work directly on the original which is always current. Data integration processes run in the database, without a separate ETL software. It’ll be interesting to explore further ideas and usage scenarios.

JSON-Array in PostgreSQL verarbeiten

(English version)

Neulich mußte ich eine JSON-Datenstruktur aus einer Web-Quelle holen und in die Datenbank einfügen. Das Besondere an diesen Daten ist, daß sie einfach in einem zweidimensionalen Array liegen, die Webseite interpretiert die Daten selbst. Es sind also keine Feldnamen im JSON.

In R ging es recht einfach:

require(rjson)

# JSON-Array laden, List daraus machen
global2k_lst <- fromJSON(file="http://www.forbes.com/ajax/load_list/?type=organization&uri=global2000&year=2014");
# Die Liste in einen eindimensionalen Vektor konvertieren
global2k <- unlist(global2k_lst)
# Durch die Angabe der Dimensionen wird eine Matrix daraus
dim(global2k) <- c(12,2000)
# Aus der Matrix dann ein Data Frame machen und die Felder benennen
global2k <- as.data.frame(t(global2k))
names(global2k) <- c("rank", "urlname", "name", "logoname", "sort",
                     "sales", "profits", "assets", "marketvalue",
                     "country", "industry", "us_state")

write.csv2(global2k, "/tmp/forbes2000.csv", row.names=FALSE)

Andererseits hat PostgreSQL seit Version 9.3 viele JSON-Funktionen. Wie funktioniert es also direkt in der Datenbank?

Erst mal habe ich den ganzen JSON-String in eine Tabelle ins Feld „forbes2k_json“ eingelesen. (PostgreSQL hat ja auch mit 244 kByte Daten in einem Feld kein Problem.) Der Inhalt des Arrays läßt sich dann mit PostgreSQL-Mitteln mit etwas Aufwand als Tabelle ausgeben:

-- JSON-Array in 2000 Zeilen aufspalten
with lines as (
    select json_array_elements(forbes2k_json::json) as company
    from forbes2k_json
),
-- Die Elemente der Zeilen in jeweils 12 Zeilen aufspalten, die Original-Zeilen nummerieren
entries as (
    select json_array_elements(company)::text as entry,
        row_number() over () as entrynr
    from lines
),
-- Die Felder nummerieren
fields as (
    select *,
        row_number() over (partition by entrynr) as fieldnr
    from entries
)
-- Mit 12 self joins die Tabellenstruktur aufbauen und die Felder konvertieren
select f1.entry::integer as rank,
    regexp_replace(f2.entry::text, '^"(.*)"$', '\1') as urlname,
    regexp_replace(f3.entry::text, '^"(.*)"$', '\1') as company,
    regexp_replace(f4.entry::text, '^"(.*)"$', '\1') as logoname,
    f5.entry::integer as sort,
    regexp_replace(regexp_replace(f6.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as sales,
    regexp_replace(regexp_replace(f7.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as profits,
    regexp_replace(regexp_replace(f8.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as assets,
    regexp_replace(regexp_replace(f9.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as marketvalue,
    regexp_replace(f10.entry::text, '^"(.*)"$', '\1') as country,
    regexp_replace(f11.entry::text, '^"(.*)"$', '\1') as industry,
    regexp_replace(f12.entry::text, '^"(.*)"$', '\1') as us_state
from fields f1
inner join fields f2 on f2.entrynr = f1.entrynr and f2.fieldnr = 2
inner join fields f3 on f3.entrynr = f1.entrynr and f3.fieldnr = 3
inner join fields f4 on f4.entrynr = f1.entrynr and f4.fieldnr = 4
inner join fields f5 on f5.entrynr = f1.entrynr and f5.fieldnr = 5
inner join fields f6 on f6.entrynr = f1.entrynr and f6.fieldnr = 6
inner join fields f7 on f7.entrynr = f1.entrynr and f7.fieldnr = 7
inner join fields f8 on f8.entrynr = f1.entrynr and f8.fieldnr = 8
inner join fields f9 on f9.entrynr = f1.entrynr and f9.fieldnr = 9
inner join fields f10 on f10.entrynr = f1.entrynr and f10.fieldnr = 10
inner join fields f11 on f11.entrynr = f1.entrynr and f11.fieldnr = 11
inner join fields f12 on f12.entrynr = f1.entrynr and f12.fieldnr = 12
where f1.fieldnr = 1
order by rank, sort

Kompliziert ist es nicht, nur viel zu tippen (und copy/paste-en). Da die Daten selbst keine Feld-Metadaten wie Name und Typ enthalten, muß man sie selbst festlegen.

Processing a JSON array in PostgreSQL

I needed to get a JSON data structure from the web and put its contents into a database. These data were a bit special in the sense that the JSON only contained only a two dimensional array interpreted by the website, without any field names.

In R it was quite easy:

require(rjson)

# Load JSON array and convert to a list
global2k_lst <- fromJSON(file="http://www.forbes.com/ajax/load_list/?type=organization&uri=global2000&year=2014");
# Convert to a one-dimensional vector
global2k <- unlist(global2k_lst)
# Change to a matrix by specifying the dimensions
dim(global2k) <- c(12,2000)
# Convert the matrix to a data frame and specify the field names
global2k <- as.data.frame(t(global2k))
names(global2k) <- c("rank", "urlname", "name", "logoname", "sort",
                     "sales", "profits", "assets", "marketvalue",
                     "country", "industry", "us_state")

write.csv2(global2k, "/tmp/forbes2000.csv", row.names=FALSE)

On the other hand, PostgreSQL has lots of JSON functions since release 9.3. So how to do it directly in the database?

The first step is reading the entire JSON string into a table in a field called „forbes2k_json“. (PostgreSQL can easily store 244 kBytes of data in a single field.) A not too complex query converts the array contents into a normal table:

-- JSON array into 2000 lines
with lines as (
    select json_array_elements(forbes2k_json::json) as company
    from forbes2k_json
),
-- Existing lines converted into 12 lines each, with an additional row number for 
-- the original lines
entries as (
    select json_array_elements(company)::text as entry,
        row_number() over () as entrynr
    from lines
),
-- Row numbers of the entry lines
fields as (
    select *,
        row_number() over (partition by entrynr) as fieldnr
    from entries
)
-- 12 self joins for building the table structure; convert field types
select f1.entry::integer as rank,
    regexp_replace(f2.entry::text, '^"(.*)"$', '\1') as urlname,
    regexp_replace(f3.entry::text, '^"(.*)"$', '\1') as company,
    regexp_replace(f4.entry::text, '^"(.*)"$', '\1') as logoname,
    f5.entry::integer as sort,
    regexp_replace(regexp_replace(f6.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as sales,
    regexp_replace(regexp_replace(f7.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as profits,
    regexp_replace(regexp_replace(f8.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as assets,
    regexp_replace(regexp_replace(f9.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as marketvalue,
    regexp_replace(f10.entry::text, '^"(.*)"$', '\1') as country,
    regexp_replace(f11.entry::text, '^"(.*)"$', '\1') as industry,
    regexp_replace(f12.entry::text, '^"(.*)"$', '\1') as us_state
from fields f1
inner join fields f2 on f2.entrynr = f1.entrynr and f2.fieldnr = 2
inner join fields f3 on f3.entrynr = f1.entrynr and f3.fieldnr = 3
inner join fields f4 on f4.entrynr = f1.entrynr and f4.fieldnr = 4
inner join fields f5 on f5.entrynr = f1.entrynr and f5.fieldnr = 5
inner join fields f6 on f6.entrynr = f1.entrynr and f6.fieldnr = 6
inner join fields f7 on f7.entrynr = f1.entrynr and f7.fieldnr = 7
inner join fields f8 on f8.entrynr = f1.entrynr and f8.fieldnr = 8
inner join fields f9 on f9.entrynr = f1.entrynr and f9.fieldnr = 9
inner join fields f10 on f10.entrynr = f1.entrynr and f10.fieldnr = 10
inner join fields f11 on f11.entrynr = f1.entrynr and f11.fieldnr = 11
inner join fields f12 on f12.entrynr = f1.entrynr and f12.fieldnr = 12
where f1.fieldnr = 1
order by rank, sort