Administration Guide for BayEOS Server 1.9.6

Oliver Archner

Revision 1.9.6

Released 2015-07-27


Table of Contents

1. Preface
1.1. Audience
1.2. Accessing the database
2. User Management
2.1. Authentication
2.1.1. Database
2.1.2. LDAP
2.1.3. IP Authentication
2.2. Add Users
2.3. Delete User
2.4. Set User Password
2.5. Lock User
2.6. Rights
2.7. Roles
3. Time Series Management
3.1. Series Model
3.2. Time Zone Handling
3.3. Aggregation
A. Appendix
A.1. Core Physical Observation and Measurement Model
A.2. Frame Extension

1. Preface

1.1. Audience

This document is intended for database administrators, system administrators, and application developers. To use this document, you need to know the following:

  • Relational database concepts in general

  • Basic administration knowledge of the underlying operating system

1.2. Accessing the database

Please use the bayeos system account to access your local bayeos database instance for administration purposes. There is no need to change any setting in /etc/postgresql/<version>/pg_hba.conf or /etc/postgresql/<version>/postgresql.conf. A typical session looks like the following:

ssh -l root <hostname>
su bayeos
psql -d bayeos
select set_user('root');
...

2. User Management

This is probably the main task in every administrators job. Therefore the following paragraph describes how to manage this.

2.1. Authentication

BayEOS has build in capabilities for database, LDAP and IP authentication. The methods are configurated by entries in database tables. Please configure the authenticaton method before creating new users. The following subsections describe the authentication methods in more detail.

2.1.1. Database

This is the classic authentication approach using passwords stored in a database table named auth_db. A record for local authentication named as 'LOCAL' is provided by default. You can add additional records to this table to authenticate users against a different database or schema. Please be aware to provide the appropriate JDBC library to your application server.

Attributes in table bayeos.auth_db

name

Alias to access this record

URL

JDBC database connection definition, e.g.:"jdbc:postgresql://localhost:5432/bayeos"

username

Username for JDBC connection

password

Password for JDBC connection

query_stmt

Select statement to access user password using crypt function, e.g: "select crypt(?,substr(pw,1,2)) = pw from benutzer where login like ? and locked = false;"

update_stmt

Update statement to update user password in database e.g.: "update benutzer set pw = crypt(?,gen_salt('des')) where login like ?;"

2.1.2. LDAP

This type of authentication method is quite useful if your company is hosting a central LDAP repository. To use this method just add an entry providing the necessary information before creating new users.

Attributes in table bayeos.auth_ldap

name

Alias to access this record

host

LDAP server hostname

dn

Distinguished name to start the search. The username must be provided as an input parameter, e.g. "uid=:?,ou=people,dc=uni-bayreuth,dc=de"

ssl

Flag to specify whether LDAPS is used or not. For LDAPS a key store is necessary. You must import the certificate of your LDAP Server into the default store of the server JRE. The default keystore for your JRE can be found in /usr/lib/jvm/jre/lib/security. The key can be imported by the keytool utility:

keytool -import -alias myAlias -file myCertificate.crt -keystore cacerts -storepass changeit
Please restart the Tomcat Server after the import.
port

Port for ldap connection. Use 389 for default ldap or 636 for ldaps connections.

2.1.3. IP Authentication

Authentication for a specific user is controlled by his ip address. A specific IP filter can be used to assign users to access methods.

Attributes in table bayeos.auth_ip

network
Specifies the client machine addresses that this record matches. An IP address is specified in standard dotted decimal notation with an optional CIDR mask length.
login
User login name or * for all users.
access
Specifies the authentication method to use when a connection matches this record. Valid entries are: 'TRUST', 'DENY', 'PASSWORD'

Some valid examples entries in table auth_ip are shown in the following figure. When user 'import' is trying to login from 136.181.112.117 no password is needed.

network          | login  |  access
-----------------+--------+----------
136.181.112.117  | import | TRUST
136.0.0.0/8      | *      | PASSWORD
136.181.112.0/24 | *      | PASSWORD

2.2. Add Users

You can add users to the database by calling the create_user function:

create_user(login,password,name,auth_type,auth_name)

  • login: user name

  • password: user password, can be blank for LDAP authentication

  • name: user name

  • auth_type: [LDAP|DB]

  • auth_name: authentication name

A typical administrator session to create a new local database user 'bill' for Bill Gates will look like:

su bayeos
psql -d bayeos
select set_user('root');
select create_user('bill','PASSWORD','Bill Gates','DB','LOCAL');

2.3. Delete User

Users can be dropped by the drop_user function passing in the user name. A typical administrator session to drop a user 'bill' will look like:

su bayeos
psql -d bayeos
select set_user('root');
select drop_user('bill');

2.4. Set User Password

To set the password for a local database user just call the set_password(username,password) SQL function.

2.5. Lock User

User accounts can be locked by setting the locked flag in table benutzer.

2.6. Rights

BayEOS right system is based on the well known UNIX right system. Rights are bound to a specific object and can be granted to users or roles. The following rights are available:

  • Read: Right to read objects like series or locations

  • Write: Right to create and update objects

  • Execute: Gives full access in combination with the read and write right (rwx). Users with full access are allowed to delete objects and to add/delete references on objects

  • Inherit Flag: Forwards rights to all child objects

Commands to manage rights:

  • grant_right('rwxi',role|user,object)

  • revoke_right('rwxi',role|user,object)

2.7. Roles

Roles are a mechanism to grant rights to several users. The following functions are available to manage roles:

  • create_role(role_alias, role_name)

  • grant_role(username text, role_alias text)

  • revoke_role(username text, role_alias text)

The default role for all new users is called 'All Users'. The role has read access to all root nodes like folders or units.

3. Time Series Management

3.1. Series Model

The main objective of BayEOS is to manage timeseries of environmental observations. A timeseries is an ordered set of measurements at successive points in time. BayEOS handles the measurement series and the laboratory series type.

3.2. Time Zone Handling

Each observation series has a time zone property. The default timezone of BayEOS is GMT+1.

3.3. Aggregation

One main functionality of BayEOS is its capability to calculate aggregation values on base of orginal measurements. To speed up the calculation time we use a hierachy approach. Each coarser interval uses the calculation values of it's successor. Aggregation functions and intervals can be configurated in system tables aggr_intervall and aggr_funktion.

Attributes in table bayeos.aggr_intervall

name
Name of aggregation function including parantheses like avg(),count() etc. Each valid SQL group by function can be used.

Attributes in table bayeos.aggr_funktion

name
Name of aggregation interval as specified by the PostgreSQL interval definition: [@] quantity unit [quantity unit...] [direction]

The following housekeeping functions can be used to manage aggregates:

aggr_init()
Drops all aggregation tables, creates all aggr_tables and calculates the aggregation of all series using all combinations of aggregation function and aggregation interval.
aggr_calculate()
Incremental calculation of aggregates of all series using all combinations of aggregation functions and aggregation intervals. Uses the history tables and the value of variable "aggr_last_his_massendaten_id" in table sys_variablen. The aggregation interval for a series is calculated by means of the series resolution and its interval type. The type defines the aggrgation timestamp anchor. If the type is set to "END" all aggregates timestamp values are substracted by resolution. This function should be called periodically by a cron job.
arch_his()
Dumps the content of all history tables (his_ prefix) to a destination specified by sys_variablen setting arch_his and truncates the his tables. The function must be called as user postgres. Unix privileges are necessary to create and write files in arch_his.

A. Appendix

A.1. Core Physical Observation and Measurement Model

A.2. Frame Extension