Rules section¶

Odyssey allows you to define client routing rules by specifying database, user, and storage sections.

When a client connects, the appropriate route is assigned by matching database and user sections. All requests are then forwarded to a storage (which is referenced from the user section).

database <name> | default { users }

Defines database name requested by client. Each database section structure consists of user subsections.

A special database default is used when no database is matched.

user <name> | default { options }

Defines authentication, pooling and storage settings for requested route.

A special user default is used when no user is matched.

Configuration Parameters Reference¶

authentication¶

string

Set route authentication method. Supported:

"none"          - authentication turned off
"block"         - block this user
"clear_text"    - PostgreSQL clear text authentication
"md5"           - PostgreSQL md5 authentication
"scram-sha-256" - PostgreSQL scram-sha-256 authentication
"cert"          - Compare client certificate Common Name against auth_common_name's

authentication "none"

password¶

string

Set route authentication password. Depending on selected method, password can be in plain text, md5 hash or SCRAM secret.

To generate SCRAM secret you can use this tool.

password "test"

auth_common_name¶

default|string

Specify common names to check for "cert" authentication method. If more than one common name is defined, all of them will be checked until a match is found.

Set 'default' to check for the current user.

auth_common_name default
auth_common_name "test"

auth_query¶

string

Enable remote route authentication. Use another route to authenticate clients following this logic:

Use the selected 'auth_query_db' and 'auth_query_user' to match a route. Use the matched route server to send 'auth_query' to get the username and password needed to authenticate a client.

auth_query "SELECT usename, passwd FROM pg_shadow WHERE usename=$1"
auth_query_db ""
auth_query_user ""

Disabled by default.

auth_pam_service¶

Enables PAM (Pluggable Authentication Modules) as the authentication mechanism. It is incompatible with the auth query method. The password must be passed in plain text form, as standard PostgreSQL requires.

auth_pam_service "name desired pam service"

client_max¶

integer

Set client connections limit for this route.

Comment out 'client_max' to disable the limit. When the client limit is reached, Odyssey will reply with 'too many connections'.

client_max 100

storage¶

string

Set remote server to use.

By default, route database and user names are used as connection parameters to the remote server. It is possible to override these values by specifying storage_db and storage_user. The remote server password can be set using the storage_password field.

storage "postgres_server"
storage_db "database"
storage_user "test"
storage_password "test"

ldap_storage_credentials¶

This subsection must be located within the user subsection and is used to route clients to a remote PostgreSQL server with special credentials (storage_user and storage_password), depending on the client account attributes stored on the LDAP server (based on OpenLDAP, Active Directory or others). This routing method allows to grant access with different privileges to different databases located on the same host.

This routing method may only be used if the variables ldap_endpoint_name and ldap_storage_credentials_attr are set. For example:

storage "test_server" {
     type "remote"
     port 5432
     host "postgres_server"
}
ldap_endpoint "ldap1" {
     ldapscheme "ldap"
     ldapbasedn "dc=example,dc=org"
     ldapbinddn "cn=admin,dc=example,dc=org"
     ldapbindpasswd "admin"
     ldapsearchfilter "(memberOf=cn=localhost,ou=groups,dc=example,dc=org)"
     ldapsearchattribute "gecos"
     ldapserver "192.168.233.16"
     ldapport 389
}
database default {
    user default {
          authentication "clear_text"
          storage "test_server"

          ldap_endpoint_name "ldap1"
          ldap_storage_credentials_attr "memberof"
          ldap_storage_credentials "group_ro" {
               ldap_storage_username "ldap_ro"
               ldap_storage_password "password1"
          }
          ldap_storage_credentials "group_rw" {
               ldap_storage_username "ldap_rw"
               ldap_storage_password "password2"
          }

          #other required regular parameters are hidden from this example
     }
}

dn: uid=user4,dc=example,dc=org
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: user4
uid: user4
memberof: cn=localhost,ou=groups,dc=example,dc=org
memberof: cn=localhost_ldap_db1_group_ro,ou=groups,dc=example,dc=org
memberof: cn=localhost_ldap_db2_group_rw,ou=groups,dc=example,dc=org
uidNumber: 16860
gidNumber: 101
homeDirectory: /home/user4
loginShell: /bin/bash
gecos: user4
userPassword: default
shadowLastChange: 0
shadowMax: 0
shadowWarning: 0

ldap_storage_credentials_attr¶

string

Sets the account attribute name from the LDAP server, whose values will be used to determine the route and parameters for connecting the client to the PostgreSQL server.

ldap_endpoint_name¶

string

Specifies the name of the ldap_endpoint to be used to connect to the LDAP server.

ldap_endpoint¶

The ldap_endpoint section is used to configure parameters for connecting to the LDAP server. For example:

ldap_endpoint "ldap1" {
    ldapscheme "ldap"
    ldapbasedn "dc=example,dc=org"
    ldapbinddn "cn=admin,dc=example,dc=org"
    ldapbindpasswd "admin"
    ldapsearchattribute "gecos"
    ldapserver "192.168.233.16"
    ldapport 389
}

ldap_pool_size¶

integer

LDAP server pool size

Keep the number of servers in the pool up to 'pool_size'. Clients are put in a wait queue when all servers are busy.

Set to zero to disable.

ldap_pool_size 10

ldap_pool_timeout¶

integer

LDAP server pool timeout

Time to wait in milliseconds for an available server. Disconnect the client when the timeout is reached.

Set to zero to disable.

ldap_pool_timeout 1000

ldap_pool_ttl¶

integer

LDAP server pool idle timeout.

Close the LDAP server connection when it becomes idle for 'ldap_pool_ttl' seconds.

Set to zero to disable.

ldap_pool_ttl 60

password_passthrough¶

bool

By default, Odyssey authenticates users itself, but if a side authentication application is used, such as a LDAP server, PAM module, or custom auth module, sometimes instead of configuring storage_password, it is more convenient to reuse the client-provided password to perform backend authentication. If you set this option to "yes", Odyssey will store the client token and use it when a new server connection is opened. However, if you configure storage_password for the route, password_passthrough is essentially ignored.

pool¶

string

Set route server pool mode.

Supported modes:

"session"     - assign server connection to a client until it disconnects
"transaction" - assign server connection to a client for a transaction processing

pool "transaction"

pool_size¶

integer

Server pool size.

Keep the number of servers in the pool up to 'pool_size'. Clients are put in a wait queue when all servers are busy.

Set to zero to disable the limit.

pool_size 100

min_pool_size¶

integer

Minimum server pool size.

Keep the number of servers in the pool at a minimum of 'min_pool_size'. Helps to handle unexpected high load. Default: 0

min_pool_size 50

pool_timeout¶

integer

Server pool wait timeout.

Time to wait in milliseconds for an available server. Disconnect the client when the timeout is reached.

Set to zero to disable.

pool_timeout 4000

pool_ttl¶

integer

Server pool idle timeout.

Close a server connection when it becomes idle for 'pool_ttl' seconds.

Set to zero to disable.

pool_ttl 60

pool_discard¶

yes|no

Server pool parameters discard.

Execute DISCARD ALL and reset client parameters before using a server from the pool.

pool_discard no

pool_smart_discard¶

yes|no

When this parameter is enabled, Odyssey sends a smart discard query instead of the default DISCARD ALL when it returns a connection to the pool. Its default value may be overwritten by the pool_discard_string setting.

pool_discard_string¶

string

When resetting a database connection, a pre-defined query string is sent to the server. This query string consists of a set of SQL statements that will be executed during a DISCARD ALL command, except for DEALLOCATE ALL. The default query string includes the following statements:

SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD SEQUENCES;DISCARD TEMP;

This sequence of statements is designed to reset the connection to a clean state, without affecting the authentication credentials of the session. By executing these queries, any open transactions will be closed, locks will be released, and any cached execution plans and sequences will be discarded.

pool_cancel¶

yes|no

Server pool auto-cancel.

Start an additional Cancel connection if the server is left with an executing query. Close the connection otherwise.

pool_cancel no

pool_rollback¶

yes|no

Server pool auto-rollback.

Execute 'ROLLBACK' if the server is left in an active transaction. Close the connection otherwise.

pool_rollback yes

client_fwd_error¶

yes|no

Forward PostgreSQL errors during remote server connection.

client_fwd_error no

application_name_add_host¶

yes|no

Add the client host name to the application_name parameter

application_name_add_host yes

reserve_session_server_connection¶

yes|no

Connect a new client to the server immediately or wait for the first query

reserve_session_server_connection yes

server_lifetime¶

integer

Server lifetime - maximum number of seconds for a server connection to live. Prevents cache bloat. Server connection is deallocated only in the idle state. Defaults to 3600 (1 hour). Use 0 to disable.

server_lifetime 3600

server_drop_on_cached_plan_error¶

yes/no

Drop server connection when prepared statements support are enabled and ERROR 0A000 cached plan must not change result type are met. Useful when driver cant do Close+Prepare by itself.

Default: no

server_drop_on_cached_plan_error yes

pool_client_idle_timeout¶

integer

Client pool idle timeout.

Drop stale client connections after this many seconds of idleness when not in a transaction.

Set to zero to disable.

pool_client_idle_timeout 0

pool_idle_in_transaction_timeout¶

integer

Client pool idle in transaction timeout.

Drop client connections in a transaction after this many seconds of idleness.

Set to zero to disable.

pool_idle_in_transaction_timeout 0

pool_notice_after_waiting_ms¶

integer

Sent NOTICE to client after specified amount of milliseconds spent int waiting for free backend connection.

Negative values disables the feature.

db1=> select 42;
NOTICE:  waiting for free backend connection
NOTICE:  waiting took 12319 ms
 ?column? 
----------
       42
(1 row)

pool_notice_after_waiting_ms 5000

pool_attach_check**¶

yes/no

Check server connection before attaching to client. Default: yes

pool_attach_check no

pool_acquire_fail_fast¶

yes/no

Do not wait for free connection on host if pools are busy, immediately try to get connection from next host Default: no

pool_acquire_fail_fast yes

pool_reserve_prepared_statement¶

yes|no

Enable support of prepared statements in transactional pooling.

pool_reserve_prepared_statement yes

pool_pin_on_listen¶

yes/no Experimental

Enable pinning client connection to server, after LISTEN command execute.

Default: no

pool_pin_on_listen yes

log_debug¶

yes|no

Enable verbose mode for a specific route only.

log_debug no

group_checker_interval¶

integer

Soft interval between group checks (in ms) 7000 by default

group_checker_interval 7000

maintain_params¶

yes|no

User parameters maintenance

By default, Odyssey saves parameter values defined by the user and deploys them on server attach if they are different from the server's. This option disables the feature.

maintain_params no

target_session_attrs¶

string

Target session attributes feature. Odyssey will look up primary/standby for connections of this user, depending on the value set. Possible values are:

• read-write - always select host, available for write
• read-only - never select host, available for write
• prefer-standby - try to select host available for write last of all
• any (the default) - select host randomly

target_session_attrs "read-write"

quantiles¶

string

Compute quantiles of query and transaction times

quantiles "0.99,0.95,0.5"

catchup_timeout¶

integer

Specify maximum replication lag in seconds for the user See catchup-timeout.md for more details

catchup_timeout 10

catchup_checks (deprecated)¶

integer

Deprecated. Accepted for backwards compatibility but ignored — a deprecation warning is emitted.

role¶

string

Set the route role. Supported values:

"admin"  - grants access to the Odyssey admin console
"client" - regular client route (default when not set)

role "admin"

client_show_id¶

yes|no

Include the internal Odyssey client ID in log messages produced for connections on this route. Useful when correlating logs for a specific database/user pair.

client_show_id yes

pool_routing¶

string

Internal routing mode for the connection. Use "internal" for system routes such as the watchdog sub-section. Normal client routes should leave this unset.

pool_routing "internal"

pool_reset_timeout_ms¶

integer

Timeout in milliseconds for executing the reset/discard query when a server connection is returned to the pool. If the query does not complete within this time the connection is dropped instead. Set to 0 to disable the timeout.

pool_reset_timeout_ms 3000

auth_module¶

string

Name of the external authentication module to use for this route. The module must be registered via external_auth_socket_path in the global section.

auth_module "my_auth_module"

enable_mdb_iamproxy_auth¶

yes|no

Enable Yandex MDB IAM proxy authentication for this route. When set to yes, Odyssey will delegate token validation to the MDB IAM proxy service specified by mdb_iamproxy_socket_path.

enable_mdb_iamproxy_auth yes

mdb_iamproxy_socket_path¶

string

Path to the UNIX socket of the MDB IAM proxy service. Used when enable_mdb_iamproxy_auth yes.

mdb_iamproxy_socket_path "/var/run/mdb-iamproxy.sock"

log_query (per-route)¶

yes|no

Enable query logging for this specific route only, without enabling it globally. Overrides the global log_query setting for connections matched by this route.

log_query yes

options¶

Key-value block that sets PostgreSQL GUC parameters sent to the server via the startup message (equivalent to PGOPTIONS). Each entry is a pair of quoted strings.

options {
    "search_path" "myschema,public"
    "TimeZone"    "UTC"
}

backend_startup_options¶

Key-value block for additional parameters sent to the backend during the startup handshake. Similar to options but intended for parameters that must arrive in the startup packet rather than as SET commands.

backend_startup_options {
    "application_name" "my_app"
}

group¶

A group sub-section inside a database block defines a named set of route settings that is selected based on the client's group membership (looked up via a SQL query). A database block can contain multiple group entries as well as user entries.

database "mydb" {
    group "power_users" {
        group_query      "SELECT group FROM user_groups WHERE usename=$1"
        group_query_user "monitor"
        group_query_db   "postgres"

        authentication "md5"
        storage        "postgres_server"
        pool           "transaction"
        pool_size      20
    }

    user default {
        authentication "md5"
        storage        "postgres_server"
        pool           "transaction"
        pool_size      5
    }
}

group_query¶

string

SQL query executed to determine the group of the connecting client. The query must return a single string value (the group name). The client username is passed as $1.

group_query "SELECT grp FROM user_groups WHERE usename=$1"

group_query_user¶

string

Username used to connect to the database when executing group_query.

group_query_user "monitor"

group_query_db¶

string

Database used when executing group_query.

group_query_db "postgres"

Deprecated parameters¶

The following per-route parameters are accepted for backwards compatibility but are ignored — a deprecation warning is emitted in the log.

example (remote)¶

database default {
    user default {
        authentication "none"
#       password ""
#       auth_common_name default
#       auth_common_name "test"
#       auth_query "SELECT usename, passwd FROM pg_shadow WHERE usename=$1"
#       auth_query_db ""
#       auth_query_user ""
#       client_max 100

        storage "postgres_server"
#       storage_db "database"
#       storage_user "test"
#       storage_password "test"

        pool "transaction"
        pool_size 0
        pool_timeout 0
        pool_ttl 60
        pool_cancel no
        pool_rollback yes

        client_fwd_error no
        log_debug no
    }
}

example (admin console)¶

storage "local" {
    type "local"
}

database "console" {
    user default {
        authentication "none"
        pool "session"
        storage "local"
    }
}