PostgreSQL Database Server Research Articles

Technology for using special encryption functions in PostgreSQL

Cryptographic encryption mechanisms in the open source PostgreSQL server DBMS are discussed. Special high-level and low-level functions of symmetric encryption of the Pgcrypto module for ensuring information security of data are analyzed. The analysis of special functions of the third-party GPG program is performed, which allow performing asymmetric encryption in the PostgreSQL database server. A combined data encryption technology in database tables is proposed, using a hierarchy of keys (asymmetric and symmetric encryption) with a passphrase, which is also encrypted to ensure better protection of encrypted data. This technology is being tested on the example of developing a function for encrypting data, which is triggered by a trigger when inserting rows into a database table, and a data decryption function when reading encrypted data from a table.

Uso de Técnicas de Minería de Datos para la Detección de Ataques de Inyección de SQL en Sistemas de Bases de Datos

In any business organization, database infrastructures are subject to various structured query language (SQL) injection attacks, such as tautologies, alternative coding, stored procedures, use of the union operator, piggyback, among others. This article describes a data mining project developed to mitigate the problem of identifying SQL injection attacks on databases. The project was conducted using an adaptation of the cross-industry standard process for data mining (CRISP-DM) methodology. A total of 12 python libraries was used for cleaning, transformation, and modeling. The anomaly detection model was carried out using clustering by the k – nearest neighbors (kNN) algorithm. The query text was analyzed for the groups with anomalies to identify sentences presenting attack traces. A web interface was implemented to display the daily summary of the attacks found. The information source was obtained from the transactions log of a PostgreSQL database server. Our results allowed the identification of different attacks by injection of SQL code above 80%. The execution time for processing half a million transaction log was approximately 60 minutes using a computer with the following characteristics: Intel® Core i7 processor 7th generation, 12GB RAM and 500GB SSD.

Methodological Proposal for Automated Detection of the Wildland–Urban Interface: Application to the Metropolitan Regions of Madrid and Barcelona

Official information on Land Use Land Cover is essential for mapping wildland–urban interface (WUI) zones. However, these resources do not always provide the geometrical or thematic accuracy required to delimit buildings that are easily exposed to risk of wildfire at the appropriate scale. This research shows that the integration of active remote sensing and official Land Use Land Cover (LULC) databases, such as the Spanish Land Use Land Cover information system (SIOSE), creates the synergy capable of achieving this. An automated method was developed to detect WUI zones by the massive geoprocessing of data from official and open repositories of the Spanish national plan for territory observation (PNOT) of the Spanish national geographic institute (IGN), and it was tested in the most important metropolitan zones in Spain: Barcelona and Madrid. The processing of trillions of LiDAR data and their integration with thousands of SIOSE polygons were managed in a Linux environment, with libraries for geographic processing and a PostgreSQL database server. All this allowed the buildings that are exposed to wildfire risk with a high level of accuracy to be obtained with a methodology that can be applied anywhere in the Spanish territory.

Analysis and Neural Networks Modeling of Web Server Performances Using MySQL and PostgreSQL

The purpose of this study is to analyze and then model, using neural network models, the performance of the Web server in order to improve them. In our experiments, the parameters taken into account are the number of instances of clients simultaneously requesting the same Web page that contains the same SQL queries, the number of tables queried by the SQL, the number of records to be displayed on the requested Web pages, and the type of used database server. This work demonstrates the influences of these parameters on the results of Web server performance analyzes. For the MySQL database server, it has been observed that the mean response time of the Web server tends to become increasingly slow as the number of client connection occurrences as well as the number of records to display increases. For the PostgreSQL database server, the mean response time of the Web server does not change much, although there is an increase in the number of clients and/or size of information to be displayed on Web pages. Although it has been observed that the mean response time of the Web server is generally a little faster for the MySQL database server, it has been noted that this mean response time of the Web server is more stable for PostgreSQL database server.

An ontology-based framework for extracting spatio-temporal influenza data using Twitter

ABSTRACTEarly detection of influenza outbreaks is one of the key priorities on a national level for preparedness and planning. This study presents the design and implementation of Fluwitter, which is a spatio-temporal web-based prototype framework for pseudo real-time detection of influenza outbreaks from Twitter. Specifically, the framework integrates PostgreSQL database server with PostGIS spatial extension, Twitter streaming client, pre-processor, tagger and similarity calculator for semantic information extraction (IE). The IE of tagged terms is supported by Natural Language Processing (NLP) techniques, DBpediaSpotlight and WordNet Similarity for Java (WS4J), while data analytics, visualization, and mapping are supported by GeoServer and other GIS Free Open Source Software (FOSS). The prototype was calibrated to maximize detection of influenza using rules developed from ontology-based semantic similarity scores. The Twitter-generated influenza cases were validated by weekly hospitalization records issued by Ohio Department of Health (ODH). The optimized rule produced a final F-measure value of 0.72 and accuracy (ACC) value of 94.4%. The validation suggested the existence of moderate correlations for the beginning of the time period Southeast region (r = 0.52), the Northwestern region (r = 0.38), and the Central region (r = 0.33) and weak correlations for the entire time period. The potential strengths and benefits of the prototype are shown through spatio-temporal assessment and visualization of influenza potential in Ohio.

GEMSFIT: a generic fitting tool for geochemical activity models

GEMSFIT, a parallelized open-source tool for fitting thermodynamic activity models has been developed. It is the first open-source implementation of a generic geochemical-thermodynamic fitting tool coupled to a chemical equilibrium solver which uses the direct Gibbs energy minimization (GEM) approach. This enables speciation-based fitting of complex solution systems such as solid solutions and mixed solvents. The extendable framework of GEMSFIT provides a generic interface for fitting geochemical activity models at varying system compositions, temperatures and pressures. GEMSFIT provides the most common tools for statistical analysis which allow thorough evaluation of the fitted parameters. The program can receive input of measured data from a PostgreSQL database server or exported spreadsheets. The fitting tool allows for bound, linear, and nonlinear (in)equality-constrained minimization of weighted squared residuals of highly nonlinear systems over a wide temperature and pressure interval only limited by user-supplied thermodynamic data. Results from parameter regression as well as from statistical analysis can be visualized and directly printed to various graphical formats. Efficient use of the code is facilitated by a graphical user interface which assists in setting up GEMSFIT input files. The usage and resulting output of GEMSFIT is demonstrated by results from parameter regression of the extended universal quasichemical aqueous activity model for geothermal brines.

Reasoning about nondeterminism in programs

Branching-time temporal logics (e.g. CTL, CTL*, modal mu-calculus) allow us to ask sophisticated questions about the nondeterminism that appears in systems. Applications of this type of reasoning include planning, games, security analysis, disproving, precondition synthesis, environment synthesis, etc. Unfortunately, existing automatic branching-time verification tools have limitations that have traditionally restricted their applicability (e.g. push-down systems only, universal path quantifiers only, etc). In this paper we introduce an automation strategy that lifts many of these previous restrictions. Our method works reliably for properties with non-trivial mixtures of universal and existential modal operators. Furthermore, our approach is designed to support (possibly infinite-state) programs. The basis of our approach is the observation that existential reasoning can be reduced to universal reasoning if the system's state-space is appropriately restricted. This restriction on the state-space must meet a constraint derived from recent work on proving non-termination. The observation leads to a new route for implementation based on existing tools. To demonstrate the practical viability of our approach, we report on the results applying our preliminary implementation to a set of benchmarks drawn from the Windows operating system, the PostgreSQL database server, SoftUpdates patching system, as well as other hand-crafted examples.

Comparative GO: A Web Application for Comparative Gene Ontology and Gene Ontology-Based Gene Selection in Bacteria

The primary means of classifying new functions for genes and proteins relies on Gene Ontology (GO), which defines genes/proteins using a controlled vocabulary in terms of their Molecular Function, Biological Process and Cellular Component. The challenge is to present this information to researchers to compare and discover patterns in multiple datasets using visually comprehensible and user-friendly statistical reports. Importantly, while there are many GO resources available for eukaryotes, there are none suitable for simultaneous, graphical and statistical comparison between multiple datasets. In addition, none of them supports comprehensive resources for bacteria. By using Streptococcus pneumoniae as a model, we identified and collected GO resources including genes, proteins, taxonomy and GO relationships from NCBI, UniProt and GO organisations. Then, we designed database tables in PostgreSQL database server and developed a Java application to extract data from source files and loaded into database automatically. We developed a PHP web application based on Model-View-Control architecture, used a specific data structure as well as current and novel algorithms to estimate GO graphs parameters. We designed different navigation and visualization methods on the graphs and integrated these into graphical reports. This tool is particularly significant when comparing GO groups between multiple samples (including those of pathogenic bacteria) from different sources simultaneously. Comparing GO protein distribution among up- or down-regulated genes from different samples can improve understanding of biological pathways, and mechanism(s) of infection. It can also aid in the discovery of genes associated with specific function(s) for investigation as a novel vaccine or therapeutic targets.Availability http://turing.ersa.edu.au/BacteriaGO.

Temporal property verification as a program analysis task

We describe a reduction from temporal property verification to a program analysis problem. First we present a proof system that, unlike the standard formulation, is more amenable to reasoning about infinite-state systems: disjunction is treated by partitioning, rather than enumerating, the state space and temporal operators are characterized with special sets of states called frontiers. We then describe a transformation that, with the use of procedures and nondeterminism, enables off-the-shelf program analysis tools to naturally perform the reasoning necessary for proving temporal properties (e.g. backtracking, eventuality checking, tree counterexamples for branching-time properties, abstraction refinement, etc.). Using examples drawn from the PostgreSQL database server, Apache web server, and Windows OS kernel, we demonstrate the practical viability of our work.

Making prophecies with decision predicates

We describe a new algorithm for proving temporal properties expressed in LTL of infinite-state programs. Our approach takes advantage of the fact that LTL properties can often be proved more efficiently using techniques usually associated with the branching-time logic CTL than they can with native LTL algorithms. The caveat is that, in certain instances, nondeterminism in the system's transition relation can cause CTL methods to report counter examples that are spurious with respect to the original LTL formula. To address this problem we describe an algorithm that, as it attempts to apply CTL proof methods, finds and then removes problematic nondeterminism via an analysis on the potentially spurious counterexamples. Problematic nondeterminism is characterized using decision predicates , and removed using a partial, symbolic determinization procedure which introduces new prophecy variables to predict the future outcome of these choices. We demonstrate---using examples taken from the PostgreSQL database server, Apache web server, and Windows OS kernel---that our method can yield enormous performance improvements in comparison to known tools, allowing us to automatically prove properties of programs where we could not prove them before.

Average Delay Guarantee in Server Systems Using Admission Control

Abstract Although server technology provides a mean to support a wide range of online services and applications, their ad-hoc configuration poses significant challenges to the performance, availability and economical costs of applications. One of the main concerns is that under a heavy load, the delay introduced by a server in the transaction process may grow unbounded. This paper precisely addresses this issue. First, we present the design of a server model as a non-linear continuous-time model. Second, we develop an admission control algorithm that allow to ensure a maximum average delay on the server. Model and control algorithm were implemented and applied on the standard PostgreSQL database server running the TPC-C warehouse application. The experiments show that the proposed method provides significant benefits for database servers management.

9A13 Sustainable Patient Information Network for Primary Care Health Center in Thousand Islands Country

Sustainable Patient Information Network for primary care health center is needed for Indonesia. There are many of remote, isolated and urban area, which causes transportation problems and telecommunication problems, for example : necessity of wireless infrastructure. We develop low speed communication system to access the Internet using Internet radio packet and used PostgreSQL database server on Linux machines. This system connects every health center to each other and avoid central data storage which common use in this day. It is a good opportunity for developing country such as Indonesia to use open free software. And radio packet communication uses radio media is no connection charge like telephone line for data communication. The study opens perspective for a further data storage, distribution, synchronizing, maintenance and security.

STL3/433: IATROTEK On-line: The Hellenic medical literature retrieval system

IntroductionIATROTEK is a bilingual (Greek, English) database containing the references of about 15.000 articles (abstracts included), which have been published in 75 Greek biomedical journals since 1982. Until recently, the input in this database was performed by manual typing and the retrieval was available to the users only off line. Input automation was started before six months and on-line use of the database was officially inaugurated on May 8th, 1999 (www.mednet.gr/iatrotek).MethodsThe on-line version of IATROTEK is implemented on a Linux-based server running PostgreSQL database server and Apache web server. All the software used is freeware.The user interface is comprised of several HTML forms, which are accessible by any WWW browser, and it is supported by certain CGI (Common Gateway Interface) scripts written in Perl. Searching can be performed providing single terms or terms combined by boolean operators, on the following fields: title, authors, key-words, abstract in both Greek and English. Matching records include the title and reference of the articles as well as links to the Greek and the English abstracts. Links providing further searching on authors' names and key-words are available on the page of abstracts. Concerning the input in the database, it is performed by the use of appropriate filters (format transformers) on the data provided by the publishers. Under construction is the automation of key-word assigning procedure and a stemming algorithm for Greek words.ResultsDuring the first fifteen days of its on-line operation, the database is queried with a mean rate of 48 requests/day (20% of them from abroad). Bearing in mind the small number of Greek doctors having access to the Web (2,500-3,000 out of 35,000), this is a fact signifying their warm interest on the on-line use of the IATROTEK.DiscussionCompared to the availability of the international medical literature through the Web, the literature written in national languages is not equally favored by the Internet explosion. Efforts, like that of IATROTEK on-line, towards the dissemination of locally produced medical information through the Web will overcome the serious obstacles confronted by the national journals.