######################################################################################### # aceql-server.properties # # Default properties file for AceQL HTTP Web Server ######################################################################################### ######################################################################################### # Tomcat JDBC Connection Pool Section # # Properties for creating an embedded Tomcat JDBC Pool. # See http://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html # # (If you don't want to use Tomcat JDBC Pool and implement another # connection pool in your own DatabaseConfigurator.getConnection() # implementation, just comment the driverClassName property). # # 1) Add to the "databases" property the database names separated by # commas. # # 2) Fill the mandatory properties if you want to use Tomcat JDBC Pool: # -> driverClassName: The fully qualified Java class name of # the JDBC driver to be used. # -> url: The connection URL to be passed to # our JDBC driver to establish a connection # -> username: The connection username to be passed to # our JDBC driver to establish a connection # -> password: The connection password to be passed to # our JDBC driver to establish a connection # # You may specify other properties as defined in: # http://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html#Common_Attributes # and in # http://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html#Tomcat_JDBC_Enhanced_Attributes # # Each property must be prefixed by the database name and a dot separator. # # Examples: # # databases = my_database, my_database_2 # # my_database.driverClassName = org.postgresql.Driver # my_database.url= jdbc:postgresql://localhost:5432/my_database # my_database.username= user1 # my_database.password= password1 # # my_database_2.driverClassName = org.gjt.mm.mysql.Driver # my_database_2.url = jdbc:postgresql://localhost:5432/my_database_2 # my_database_2.username = user1 # my_database_2.password = password1 ######################################################################################### # Database names separated by commas databases = sampledb # Mandatory JDBC properties: # PostgreSQL example sampledb.driverClassName = org.postgresql.Driver sampledb.url= jdbc:postgresql://localhost:5432/sampledb sampledb.username= user1 sampledb.password= password1 # MySQL example #sampledb.driverClassName = com.mysql.cj.jdbc.Driver #sampledb.url= jdbc:mysql://localhost:3306/sampledb?characterEncoding=latin1\ # &useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC #serverTimezone=UTC #sampledb.username= user1 #sampledb.password= password1 # SQL Server example #sampledb.driverClassName = com.microsoft.sqlserver.jdbc.SQLServerDriver #sampledb.url= jdbc:sqlserver://localhost:1433;database=sampledb #sampledb.username= user1 #sampledb.password= password1 # Oracle example #sampledb.driverClassName = oracle.jdbc.driver.OracleDriver #sampledb.url= jdbc:oracle:thin:sys@//localhost:1521/XE #sampledb.username= sys as sysdba #sampledb.password= password1 # Define pool size sampledb.initialSize = 10 sampledb.minIdle = 10 sampledb.maxIdle = 125 sampledb.maxActive = 125 # Default sampledb.defaultAutoCommit=true # Make sure returned connections are not in the middle of a transaction sampledb.rollbackOnReturn=true # testOnBorrow & validationQuery will ensure that the JDBC Connection Pool # is still accessible after the restart of the SQL engine in use. sampledb.testOnBorrow=true sampledb.validationQuery=SELECT 1 # The removeAbandoned & removeAbandonedTimeout Properties allow # to remove automatically the abandoned connections so that they # are recycled in the pool and will be available for other client users. # removeAbandonedTimeout defines the timeout in seconds before the removal. # These settings are useful in order to avoid connection pool exhaustion # when client user programs in C#, Java, or Python don't close explicitly # their connection. # Just set removeAbandoned=true for your database and abandoned connections # will thus be recycled in the connection pool after removeAbandonedTimeout # seconds. # See http://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html#Common_Attributes # for more info. sampledb.removeAbandoned=true sampledb.removeAbandonedTimeout=120 ######################################################################################### # User Authentication Section (optional) # # Allows to define the UserAuthenticator to use for authenticating # remote client users before allowing them to execute SQL Requests. # # UserAuthenticator is optional. If no class is defined, # the DefaultUserAuthenticator will be loaded that grants free database # access to all users. # The class names must include package names when defining # your own classes (package.subpackage.className syntax). # # So, an UserAuthenticator implementation should be defined if you want to # secure the login: select among the 4 built-in & ready to use # implementations or develop your own implementation. # # These are the predefined and ready to use UserAuthenticator # implementations of the org.kawanfw.sql.api.server package: # - 1) JdbcUserAuthenticator: authenticates users with a JDBC query against a SQL table # - 2) LdapUserAuthenticator: authenticates users against a LDAP server. # - 3) SshUserAuthenticator: authenticates users against a SSH server. # - 4) WebServiceUserAuthenticator: authenticates users against a Web # Service developed and deployed by your organization. # - 5) WindowsUserAuthenticator: authenticates users against a Windows # machine on which the AceQL instance is running. # # In order to define the runtime behavior: each UserAuthenticator # implementation has it's own properties prefixed by the class name ######################################################################################### # Uncomment to activate one of the built-in concrete UserAuthenticator: #userAuthenticatorClassName=JdbcUserAuthenticator #userAuthenticatorClassName=LdapUserAuthenticator #userAuthenticatorClassName=SshUserAuthenticator #userAuthenticatorClassName=WebServiceUserAuthenticator #userAuthenticatorClassName=WindowsUserAuthenticator ####################################### # JDBC Authentication properties ####################################### # The database to use. If not set, the first value in the "databases" # property at top of file will be used. jdbcUserAuthenticator.database= # The query that will be executed in order to authenticate the user. # Defaults to "SELECT encrypted_password FROM aceql_user WHERE username = ?" if not set. jdbcUserAuthenticator.authenticationQuery=\ SELECT encrypted_password FROM aceql_user WHERE username = ? # The algorithm to use to hash passwords. Defaults to SHA-256 if no set. jdbcUserAuthenticator.hashAlgorithm=SHA-256 # The number of hashing iterations. Defaults to 1 if no set. jdbcUserAuthenticator.hashIterations=1 # The salt string to use. If not set, no salt will be used. jdbcUserAuthenticator.salt= ####################################### # LDAP Authentication properties ####################################### # LdapUserAuthenticator property: URL of the LDAP server ldapUserAuthenticator.url=ldap://ldap.forumsys.com:389 # If set: the security level to use: none, simple, strong #ldapUserAuthenticator.securityAuthentication= # If set: the security protocol to use Possible values= ssl, tls, etc. #ldapUserAuthenticator.securityProtocol= ####################################### # SSH Authentication properties ####################################### # SshUserAuthenticator properties: host or IP and port (defaults to 22) sshUserAuthenticator.host=10.10.10.10 sshUserAuthenticator.port=22 ####################################### # Web Service Authentication properties ####################################### # The Authentication Web Service must be developed and deployed by # your organization. # It must accept 2 POST parameters "username" and "password" and must return # either: # - the JSON string {"status"="OK"} if the authentication succeeds. # - the JSON string {"status"="FAIL"} if the authentication fails. # URL of the Authentication Web Service to call to authenticate client users. webServiceUserAuthenticator.url=https://www.acme.com/aceql-auth-ws # Defines how long to wait for the response. 0 means wait indefinitely. webServiceUserAuthenticator.timeoutSeconds=5 # Set to true to trace http client calls on stdout (for debug) webServiceUserAuthenticator.httpTrace=false ####################################### # Windows Authentication properties ####################################### # WindowsUserAuthenticator property: Windows domain # May be null/empty, or "." or mydomain.com... windowsUserAuthenticator.domain=. # Or you may define you own UserAuthenticator implementation, # your class must be then in the CLASSPATH. Full name with package is required: #userAuthenticatorClassName=com.acme.MyUserAuthenticator ######################################################################################### # SSL Configuration Section (optional) # # Configure this section if you want to use SSL. This is done with the # Default Tomcat HTTP Connector # # See Tomcat 9.0 doc: # http://tomcat.apache.org/tomcat-9.0-doc/config/http.html#SSL_Support # # Each SSL attribute must be prefixed by "sslConnector." ######################################################################################### # Set to true to activate SSL on Default HTTP Connector sslConnector.SSLEnabled=false # Default values for SSL attributes - should not be changed. sslConnector.scheme=https sslConnector.protocol=org.apache.coyote.http11.Http11Protocol sslConnector.sslProtocol=TLS sslConnector.secure=true # Values for the SSL Certificate are stored in a Java Keystore. # See Tomcat 9.0 Doc: # http://tomcat.apache.org/tomcat-9.0-doc/ssl-howto.html#Prepare_the_Certificate_Keystore # See Java doc: http://docs.oracle.com/javase/8/docs/technotes/tools/#security sslConnector.keystoreFile=c:\\.keystore sslConnector.keystorePass=changeit sslConnector.keyPass=changeit sslConnector.keyAlias=tomcat ######################################################################################### # SQL Firewall Managers Section (optional) # # Defines SQL firewall rulesets to use for each database. # # Classes must be in the CLASSPATH prior to AceQL Web Server start. # # SQL Firewall Managers are optional. # # Each property must be prefixed by the database name and a dot # separator. # The class names must include package names when defining # your own classes (package.subpackage.className syntax). # # SqlFirewallManager may be chained in property value by separating # class names by a comma. # When SqlFirewallManager classes are chained, an AND condition is # applied to all the SqlFirewallManager execution conditions in order # to compute final allow. # For example, the allowExecuteUpdate() of each chained SqlFirewallManager # instance must return true in order to allow updates of the database. # # The org.kawanfw.sql.api.server.firewall package contains several # ready to use SqlFirewallManager that can be used - and chained - without # any coding or changes: # - CsvRulesManager: manager that apply rules written in a CSV file. # - CsvRulesManagerNoReload: same as CsvRulesManager, but dynamic reload of # rules is prohibited if the CSV file is updated. # - DenyDatabaseWriteManager: manager that denies any update of the database. # - DenyDclManager: manager that denies any DCL (Data ControlLanguage) call. # - DenyDdlManager: manager that denies any DDL (Data DefinitionLanguage) call. # - DenyExceptOnWhitelistManager: manager that allows only statementst hat are # listed in a whitelist text file. # - DenyMetadataQueryManager: manager that denies the use of theAceQL Metadata Query API. # - DenyOnBlacklistManager: manager that denies statements that are listed in a # blacklist text file. # - DenySqlInjectionManager: firewall manager that allows detectingSQL injection # attacks, using Cloudmersive (https://www.cloudmersive.com) third-party API. # - DenySqlInjectionManagerAsync: same as DenySqlInjectionManager, but detection # is done asynchronously. # - DenyStatementClassManager: manager that denies any call of the raw Statement Java # class.(Calling Statements without parameters is forbidden). # # You may use the same SqlFirewallManager classes for all databases. # See Javadoc for more info at https://docs.aceql.com/rest/soft/11/javadoc. ######################################################################################### # An example that defines two built in firewalls to chain: # - First DenyDatabaseWriteManager will deny write access to database, # - Second DenyDdlManager will deny to pass DDL statements such as # DROP, CREATE TABLE, etc. #sampledb.sqlFirewallManagerClassNames=\ # DenyDatabaseWriteManager,\ # DenyDdlManager # The Operational Mode for firewalling a database. # The possible values are: # - detecting: Intrusions & attacks are detected using the SQL Firewall # Manager's rules, but are not blocked. SQL Firewall Triggers will be # applied. # - learning: Recording mode is on, building a white list whose file # name is _deny_except_whitelist.txt and located in the same # directory as the current file. "learning" is meaningful only if a # DenyExceptOnWhitelistManager SQL Firewall Manager is defined for the # database. # - protecting: The SQL Firewall Manager's rules are fully enabled # for the database. # Defaults to protecting. #sampledb.operationalMode=protecting ######################################################################################### # SQL Firewall Triggers Section (Optional # # Allows to define per database a trigger if a # SqlFirewallManager.allowSqlRunAfterAnalysis() call returns false, meaning an attack is # detected. A trigger is the Java code executed in the implementation of the # SqlFirewallTrigger.runIfStatementRefused() method. # # Classes must be in the CLASSPATH prior to AceQL Web Server start. # # SQL firewalls triggers are optional. # # Each property must be prefixed by the database name and a dot # separator. # The class names must include package names when defining # your own classes (package.subpackage.className syntax). # # SqlFirewallTrigger may be chained in property value by separating # class names by a comma. # When SqlFirewallTrigger classes are chained, all of them are successively # executed in the declared order. # # The org.kawanfw.sql.api.server.firewall.trigger package contains several # ready to use SqlFirewallTrigger that can be used - and chained - without # any coding or changes: # - BanUserSqlFirewallTrigger: a trigger that inserts the username and other info into a # SQL table. The SQL table is scanned/controlled at each request, so the banned user # cannot access any more the AceQL server. # - BeeperSqlFirewallTrigger: a trigger that simply beeps on the terminal if an attack # is detected by a SqlFirewallManager. # - JdbcLoggerSqlFirewallTrigger: a trigger that logs into a SQL table all info about # the denied SQL request. # - JsonLoggerSqlFirewallTrigger: a trigger that logs in JSON format all info about # the denied SQL request. # # You may use the same SqlFirewallTrigger classes for all databases. # See Javadoc for more info at https://docs.aceql.com/rest/soft/12/javadoc. ######################################################################################### #sampledb.sqlFirewallTriggerClassNames=JsonLoggerSqlFirewallTrigger ######################################################################################### # Update Listeners Section (optional) # # Allows defining Java code to execute after a successful SQL database update is done. # Update Listeners can be viewed as a kind of Java "trigger" executed on the completion # of SQL updates. # # Classes must be in the CLASSPATH prior to AceQL Web Server start. # # Update Listeners are optional. # # Each property must be prefixed by the database name and a dot # separator. # The class names must include package names when defining # your own classes (package.subpackage.className syntax). # # UpdateListener may be chained in property value by separating # class names by a comma. # When UpdateListener classes are chained, all of them are successively # executed in the declared order. # # The org.kawanfw.sql.api.server.listener package contains the # ready to use JsonLoggerUpdateListener that can be used to log using JSON format all # successful SQL updates. ######################################################################################### #sampledb.updateListenerClassNames=JsonLoggerUpdateListener ######################################################################################### # AceQL Manager Servlet Name Section (optional) # # Allows to define the call name of the AceQL Manager servlet. # # This is the name the client side will use to call AceQL: # http(s)://host:port/ ######################################################################################### aceQLManagerServletCallName=aceql ######################################################################################### # Servlets Section (optional) # # Allows to define your servlets that can interact with AceQL # Web Server. # (In order to query info about JDBC pools in use, or modify a # pool size, etc.) # # 1) Add to the "servlets" property the servlets names separated by # commas. # # 2) For each servlet name: # - Add a property with "servlet name.class" to define the servlet # class name. # - Add a property with "servlet name.url-pattern" to define # the servlet url-pattern, i.e. the path to the servlet in the # AceQL URL. # # Each servlet class must be in the CLASSPATH prior to AceQL Web # Server start. # # The provided defaultPoolsInfo servlet allows to gather current info # from JDBC pools created in the Tomcat JDBC Connection Pool Section. # See org.kawanfw.sql.api.server.DefaultPoolsInfo Javadoc & source code. ######################################################################################### # Servlets names separated by commas servlets = defaultPoolsInfo # This servlet allows displaying for each database the pool info. # See "Tomcat JDBC Connection Pool Section" at beginning of file. # defaultPoolsInfo.class = org.kawanfw.sql.api.server.DefaultPoolsInfo defaultPoolsInfo.url-pattern = /default_pools_info # Add if necessary you own servlet(s): ######################################################################################### # # Stateless/Stateful Mode Section (optional) # ######################################################################################### # Boolean allows saying if the AceQL Manager servlet/app is stateless. # Defaults to false (the app is thus stateful per default). # In Stateful mode: the JDBC Connection is extracted from the Connection pool at user # login. The Connection is closed (released to the pool) at end of the user session # when the client-side calls for a Connection.close(). # Each client JDBC Connection is stored in memory during the whole client session. # This allows to support SQL transactions and to use the same JDBC Connection through # the whole user session. # Note that it is required in stateful mode that the client always accesses # the same AceQL server during the whole session life. # In stateless mode: The JDBC Connection is extracted from the Connection pool at each # client SQL request. It is also closed and released in the pool at the end of each # client SQL request. # The server does not hold any session info. Different client requests can be # processed by different servers. This enables resiliency and elasticity. # Note that SQL transactions are not supported in stateless mode. statelessMode=false # Servlet will be tested at server startup from running AceQL if true. # Set to false to defer the test (case the Network firewall # does allow out HTTP calls from the machine itself, etc.) loadAceQLManagerServletOnStartup=true ######################################################################################### # Session Configurator Section (optional) # # Allows to chose between the default and the JWT implementation. # Note that the default implementation (DefaultSessionConfigurator class) allows easy # command line calls, with curl for example, but requires stateful running mode. # # The JWT implementation (JwtSessionConfigurator class) is compatible with both stateless # and stateful running modes, but the command line calls are much less user friendly. ######################################################################################### sessionConfiguratorClassName=DefaultSessionConfigurator # Comment previous line and uncomment following line if you want to # use JWT tokens: #sessionConfiguratorClassName=JwtSessionConfigurator # Secret value needed for JWT generation. Uncomment & change the value: #jwtSessionConfiguratorSecret=changeit # The duration in minutes of the AceQL sessions of client users. # 0 for infinite sessions. session.timelifeMinutes=0 ######################################################################################### # HTTP Connector Attributes Section (optional) # # You may define all attributes defined in Tomcat 9.0 Doc: # http://tomcat.apache.org/tomcat-9.0-doc/config/http.html#Attributes # except SSL attributes that must be defined in previous section # # Each Connector attribute must be prefixed by "connector." ######################################################################################### # Example: Change default connectionTimeout from 60000ms to 80000ms #connector.connectionTimeout=80 ######################################################################################### # System Properties Section (optional) # # Allows to set or clear System Properties before server start. # # Syntax: # systemSetProperty.propertyName=propertyValue # systemClearProperty.propertyName ######################################################################################### # AceQL Web server does not use SecureRandom on Unix/Linux because of # slow performances. See http://goo.gl/80X8sT # So we use: -Djava.security.egd=file:/dev/./urandom # Uncomment to force AceQL Web server to use default # SecureRandom on Unix/Linux: #systemClearProperty.java.security.egd ######################################################################################### # Internal Properties Section (optional) # # Allows to set or clear some AceQL internal Properties before server start. ######################################################################################### # Allows to modify our default Tomcat logging (SEVERE) on console. #tomcatLoggingLevel=SEVERE # Says if each Result Row row must be flushed by the Json Generator. Defaults to false. #flushEachResultSetRow=false ######################################################################################### # Database Configurators Section (optional) # # Database Configurator to use for each database. # It is not required nor recommended to implement your own class. # # Configure only if you want to use your own JDBC connection pool, # or if you want to manage you own Connection.close() method. # See Server Guide or Javadoc for more info. # # Class must be in the CLASSPATH prior to AceQL Web Server start. # (Default configurator DefaultDatabaseConfigurator is already in # CLASSPATH.) # The class names must include package names when defining # your own classes (package.subpackage.className syntax). # # Database Configurator is optional. # AceQL uses the default Configurator DefaultDatabaseConfigurator # for all databases if not specified. # # Each property must be prefixed by the database name and a dot # separator. # # You may use the same DatabaseConfigurator for all databases. # ######################################################################################### # An example #sampledb.databaseConfiguratorClassName=org.kawanfw.test.api.server.config.TestDatabaseConfigurator # DefaultDatabaseConfigurator property. Allows to define the maximum rows # per request to be returned to the client. If this limit is exceeded, # the excess rows are silently dropped. 0 means there is no limit. defaultDatabaseConfigurator.maxRows=0 ######################################################################################### # Headers Authentication Section (optional) # # Allows authenticating a client user using the request headers set # & sent from the client side. # See RequestHeadersAuthenticator class and Javadoc. # No headers authentication is done per default. ######################################################################################### # Uncomment to activate your own headers authentication implementation. # Your class must be then in the CLASSPATH. Full name will package is required: #requestHeadersAuthenticatorClassName=com.acme.MyRequestHeadersAuthenticator ######################################################################################### # ThreadPoolExecutor Section (optional) # # Allows to define the parameters of the # java.util.concurrent.ThreadPoolExecutor instance used # to execute all servlet requests in async mode. # # The below default parameters are passed to the first # ThreadPoolExecutor constructor. See https://bit.ly/2QkMg5S. # # See ThreadPoolExecutor Javadoc for more info: https://bit.ly/2MBYQrd. # Default values should be appropriate for most AceQL configurations. ######################################################################################### # The number of threads to keep in the pool, even if they are idle corePoolSize=10 # The maximum number of threads to allow in the pool maximumPoolSize=125 #the time unit for the keepAliveTime argument unit=SECONDS # When the number of threads is greater than the core, this is # the maximum time that excess idle threads will wait for new tasks # before terminating keepAliveTime=10 # The BlockingQueue class to use in ThreadPoolExecutor constructor workQueueClassName=java.util.concurrent.ArrayBlockingQueue # The initial capacity of the BloquingQueue # (0 for no or default initial capacity.) capacity=100 ######################################################################################### # HTTP2 Configuration Section (optional) # ######################################################################################### # If set to true, protocol will be updated to HTTP/2 updateToHttp2Protocol=false ######################################################################################### # Upload & Download Configurators (optional) # # It is not required nor recommended to implement your own class. # # if blobDownloadConfiguratorClassName and/or # blobDownloadConfiguratorClassName are not specified, the default # class sql.api.server.blob.DefaultBlobDownloadConfigurator & # org.kawanfw.sql.api.server.blob.DefaultBlobUploadConfigurator # are loaded. # # See org.kawanfw.sql.api.server.blob package for more info. ######################################################################################### blobDownloadConfiguratorClassName=DefaultBlobDownloadConfigurator blobUploadConfiguratorClassName=DefaultBlobUploadConfigurator ######################################################################################### # Properties Password Manager Section (optional) # # Allows to define the concrete class that implements the # org.kawanfw.sql.api.server.auth.crypto.PropertiesPasswordManager interface and # will provide the password to decrypt the encrypted property values. # # The encrypted values are created using the command line: # java -jar /lib-server/properties-encryptor-7.0.jar # # The provided DefaultPropertiesPassworManager gets the password value from the # content of the "password" property of the # user.home/.kawansoft/properties_password_manager.properties file ######################################################################################### # Full class name with package is required: propertiesPasswordManagerClassName=\ org.kawanfw.sql.api.server.auth.crypto.DefaultPropertiesPasswordManager