Hello everybody, The recently committed LDAP module contains support for a standalone configuration file where the administrator can configure the mapping of database operations within SER module to LDAP searches.
As I personally like this concept, it makes the module really powerful and adjustable without the need to recompile it (which is a crucial thing when you need to connect SER to an already existing LDAP directory), I have been thinking about how to extend it to other database drivers (mysql, postgres, ldap, generic radius module to come soon). As I am currently in early planning stage, I thought I would post what I have in mind to the list to collect input from other developers and/or people who are interested in this stuff. Do not hesitate to voice your opinion or send me your suggestions (even and especially negative ones). 1. Motivation ============= 1) The database layer in SER has undergone a major overhaul and internally it supports some new features that currently cannot be used because there is no way to configure them, such as database failover and load balancing configuration. 2) There is no way to configure more advanced database connection parameters in modules like mysql or postgres. 3) It is not possible to tune individual queries (except for changing column names if the ser module supports it) without recompiling ser. 4) The way how SQL queries will look like is hardwired in the C code without the possibility to change it, for example when you need to adapt SER to a custom database schema. 5) Non-SQL modules, such as LDAP typically need more configuration than just connection URIs and table/column names, so when we already have the config file implementation for them, why not extend it to other modules too. 6) It would be nice to have the possibility to separate all database configuration from the routing logic (i.e. the main configuration file of SER). 2. Database Configuration File ============================== Instead of per-module configuration files such as the one for LDAP, we could have one configuration file for the whole database subsystem, which could contain the following: * Description of all database connections. * Sections describing database commands * Sections describing "slave" (i.e. bound to a particular connection or database type) commands, see below. A high-level overview of the configuration file could look like this: ### [connection:mysql_server_a] driver=mysql [command:get_password] slave = mysql_get_password [slave:mysql_get_password] connection=mysql_server_a ### This is the most basic configuration file, defining a single connection to a mysql database with id "mysql_server_a" and only one database command with id "get_password" which is executed on the connection "mysql_server_a" through the slave command "mysql_get_password". The difference between "commands" and "slave" commands becomes obvious if we extend the configuration file a bit more. Let's suppose we wanted to implement load balancing across two database servers, one of them being a MySQL server and the other a PostgresSQL server. The MySQL server should receive 25% of all queries and the PostgreSQL server 75%. The configuration file for such a setup would look like this: ### [connection:my_server] driver=mysql [connection:pg_server] driver=postgres [command:get_password] slave=my_get_password 10 25 slave=pg_get_password 10 75 [slave:my_get_password] connection=my_server [slave:pg_get_password] connection=pg_server ### We now have one "command" section and two "slave" sections. The two numbers in the "command" section after every slave option are the priority and weight values as defined in the DNS SRV RFC and they are handled exactly the same way a DNS resolver library would handle them. In this particular example both slave commands have the same priority but different weights so one of them receives a higher number of database commands than the other. The following 3 sections describe what is configured in various sections and how. 2.1 Database Connections ------------------------ Every section of the configuration file that starts with [connection:xxx] where (xxx is an unique identifier) is a definition of a connection to a database server. The first option in every connection section must be "driver" which contains the name of the database driver module that will be used to create and maintain the connection. All other options in the section are then driver-specific and they are passed to individual database driver modules to parse and interpret. Connection sections would typically contain all parameters and options that are needed to create and maintain database connections. So far we have been using URLs of form mysql://ser:[EMAIL PROTECTED]/ser to create database connections. In this configuration the options would be broken down, because they can contain much more configuration options and it is not practical to embed all of them in a single URL. An example configuration file listing many options that could be supported by MySQL connections is attached to this email (file my.txt). 2.2 Slave Commands ------------------ Slave command sections start with [slave:xxx]. In short a slave command is an instance of a database command for one particular database server/connection. A simple slave command section might look like this: ### [slave:my_get_password] connection=myserver1 sql="select uid, password, flags from credentials...." field_type=(int) flags [slave:ldap_get_password] connection=central_ldap base="ou=Digest Credentials,dc=iptel,dc=org" scope=subtree filter="(objectClass=digestAuthCredentials)" field_map=password:(Binary) digestPassword field_map=realm:digestRealm field_map=auth_username:digestUsername field_map=uid:serUID field_map=flags:(BitString) serFlags ### Both slave sections above define a command which retrieves user's uid and password for digest authentication. The first one defines an SQL query (shortened) for MySQL, the second one defines search options for LDAP. 2.3 Commands ------------ The third section type marked with [command:xxx] defines high-level database commands. This is the place where multiple slave commands are harnessed together to implement more advanced stuff, such as high availability and load balancing, here is a a few examples: Example 2.3.1: Get password, balance load across two servers: [command:get_password] slave=my_get_pw1 10 50 # Same priorities, trafic split 50/50 slave=my_get_pw2 10 50 Example 2.3.2: Get password from one server, try another if first fails: [command:get_password] slave = my_get_pw1 10 0 # Lower priority, tried first slave = my_get_pw2 20 0 Example 2.3.3: Collect contacts from two registrars: [command:get_contact] slave = my_get_contact2 + my_get_contact2 More options in commands sections to be done. The attached file cfg.txt contains an example of how a full database configuration file could look like, it contains many options that I did not describe in the email, some of them probably even won't be implemented (it is just a very first try). 3. Futher steps =============== Based on what I described above, here is a partial list of further steps that I was thinking about: 1) Move the configuration parser from ldap module into the database layer and make it generic so that all other database modules can use it. The parser will only contain generic parsing primitives, such as the lexical scanner and the parsers for basic option types, other parsing functions and definitions of available options will be implemented in database driver modules. 2) Implement the possibility to define connections and connection parameters in the configuration file. So, for example, instead of mysql://ser:[EMAIL PROTECTED]/ser the admin would use an identifier of the connection, such as "mydb1", and all attributes for the connection will be read from the configuration file. The attached file my.txt contains an example configuration snippet for mysql, listing many options of the libmysql library that can be configured. 3) Implement support for the configuration file in all database drivers and add support for connections options that could be commonly needed, such as SSL configuration stuff, timeouts, authentication methods, etc. The 3 steps above are relatively simple to do and needed for sure. Whether or not I will proceed with the rest of the stuff depends on the feedback, if there is any :-). regards, Jan.
# # Database Connections # [connection:mydb1] driver = mysql # Name of the database driver to be used for the database. host = my_server1 # Hostname or IP address of the server port = 1234 # Port number, default mysql port will be used if omitted # This option is used if and only if the host option above contains string # "localhost", in that case you can specify the UNIX domain socket name here. unix_socket = "/var/run/mysql.sock" # Default database name. Database name can be overriden in database commands, # this value will be used for columns that do not have a database prefix. database = ser username = ser # Authentication username. password = heslo # Plain-text password for authentication, optional. connect_attempts = 3 # Number of reconnect attemps before the library gives up. compress = yes # Enable/disable compression on the connection. connect_timeout = 5 # Connect timeout in seconds. # The timeout in seconds for attempts to read from the server. Each attempt # uses this timeout value and there are retries if necessary, so the total # effective timeout value is three times the option value. You can set the # value so that a lost connection can be detected earlier than the default # TCP/IP timeout. read_timeout = 5 # The timeout in seconds for attempts to write to the server. Each attempt # uses this timeout value and there are net_retry_count retries if necessary, # so the total effective timeout value is net_retry_count times the option # value. This option works only for TCP/IP connections, and only for Windows # prior to MySQL 5.0.25. write_timeout = 20 # Statement to execute when connecting to the MySQL server. Automatically # re-executed if reconnection occurs. init_command = "flush privileges" # Read options from the named option file instead of from my.cnf. read_default_file = "/etc/mysql/my.cnf" # Read options from the named group from my.cnf or the file specified with # read_default_file option. read_default_group = "ser" # SSL/TLS private key of the client. ssl_key = "/etc/ssl/private/ser.key" # SSL/TLS client certificate to be presented to the server. ssl_certificate = "/etc/ssl/certs/ser.pem" # The pathname to the certificate authority file. ssl_ca = "/etc/ssl/certs/ser-ca.pem" # the pathname to a directory that contains trusted SSL CA certificates in pem # format. ssl_ca_path = "/etc/ssl/certs" # A list of allowable ciphers to use for SSL encryption. ssl_cipher_list = "AES256-SHA" # Enable or disable verification of the server's Common Name value in its # certificate against the hostname used when connecting to the server. The # connection is rejected if there is a mismatch. This feature can be used to # prevent man-in-the-middle attacks. Verification is disabled by # default. Added in MySQL 5.0.23. ssl_verify_certificate = yes
# # Database Connections # [connection:mydb1] driver = mysql # Name of the database driver to be used for the database. host = my_server1 # Hostname or IP address of the server port = 1234 # Port number, default mysql port will be used if omitted # Default database name. Database name can be overriden in database commands, # this value will be used for columns that do not have a database prefix. database = ser username = ser # Authentication username. password = heslo # Plain-text password for authentication, optional. connect_attempts = 3 # Number of reconnect attemps before the library gives up. compress = yes # Enable/disable compression on the connection. connect_timeout = 5 # Connect timeout in seconds. [connection:pgdb1] driver = postgres host = pg_server database = pg_ser username = ser password = heslo [connection:central_directory] uri=ldap://some.ldap.server bind_dn="cn=admin,dc=iptel,dc=org" bind_pw=heslo pool=no # Enable/disable connection pool # Internal memory cache [connection:cache] [connection:radius] config=/usr/local/etc/radiusclient/radiusclient.conf # # Database Commands # [command:get_password] result= (str) uid, (str) password, (int) flags parameters = (str) auth_username, (str) realm slave = auth1 10 20, auth2 10 80 # Load balancing 20% of traffic to auth1 and 80% to auth2 #slave = auth1 10, auth2 20 # Fail over, try ath1 first, then auth2 #slave = auth1 + auth2 # Merge results from auth1 and auth2 [slave:auth1] connection=my_server1, my_server2 sql="select uid, password, flags from credentials where auth_username=$ and realm=$" field_type = (int) flags # Optional specification of desired type sql_parameters = auth_username, realm [slave:auth2] connection=my_server2 sql="select uuid, password, flags from subscribeer where username=$ and realm=$" sql_parameters = auth_username, realm [slave:get_password] connection=central_ldap base="ou=Digest Credentials,dc=iptel,dc=org" scope=subtree filter="(objectClass=digestAuthCredentials)" field_map=password:(Binary) digestPassword field_map=realm:digestRealm field_map=auth_username:digestUsername field_map=uid:serUID field_map=flags:(BitString) serFlags
_______________________________________________ Serdev mailing list [email protected] http://lists.iptel.org/mailman/listinfo/serdev
