Firebird Support

[jakefeasel]

Need to add support for firebird

[the-Arioch]

what kind of help can be needed from FB community ? i mean not people knowing JS and other frameworks of your server ,but those who had soem experience with FB ?

[jakefeasel]

What I really need to support Firebird is some database scripts. Basically, I need a script that will create a new database user that has no system privileges. Then, the script needs to create a database for that user, for which that user has full access. This must be the only database which the user has access to, and there can be no operating-system level functions that the user can execute. Finally, I need a script which will drop the database and then drop the user. If you can provide advice for general hardening techniques, and limiting consumption of resources from run-away queries, that would be helpful too.

Thanks very much!

[annoynimouse]

i assume u use Unix. Generally in FB 3 (i think would not be released for year or two) many things gonna change, but as of now FB server process runs under his own user account, that you should limit by usual measures like chroot and other jails. Then FB servers comes in 3 flavours: Classic, Super and SuperClassic. SC is not to be discussed, and S diffres from C in the following: Classic forks new process for each user, S spawns the thread within the only process. That means Classic puts more burden onto system, yet users have more interference upon each other (if server crashes - all the users are affected) and it also uses the only CPU core. There is also Firebird Embedded flavour. That is DLL (lib SO) which you can link to your own executable, after reducing privilege to safe minimum a la DJB fork (http://cr.yp.to/) pattern. While probably redundant, it can provide for ebsolute separation if needed (different TCP ports, differently jailed OS-level users and such) http://www.firebirdsql.org/manual/qsg25-appx-architectures.html

[annoynimouse]

Generally you'd to read firbebird.conf text file and analyze the options descryptions. They sometimes are better described in documentation upgrade text fiels or release notes but i don't know of unified and up to date index. Dangerous things you might want to completely disabled (unless securely limiting by OS jailing) would be:

• External tables: accessing any binary file in ISAM "array of records" mode.
• User-Defined functions: DLLs (lib SOs) providing custom functions to SQL That is very flexible but also rather dangerous. By default ET's and UDF's only allowed in different folders to avoid uploading UDF as array-of-bytes ET. Since no user of ur service should be able to install his UDFs anyway, it can outright be disabled.

http://www.firebirdsql.org/manual/qsg2-config.html Authentication is to be reduced to native-only to avoid mixing FB-users and OS-users

[annoynimouse]

http://www.firebirdsql.org/manual/fbmetasecur.html http://www.firebirdsql.org/manual/qsg25-config.html

You'd filter out user attempts at CREATE/ALTER/DROP DATABASE/SCHEME/SHADOW

http://firebirdsql.su/doku.php?id=create_database http://firebirdsql.su/doku.php?id=alter_database http://firebirdsql.su/doku.php?id=drop_database http://firebirdsql.su/doku.php?id=create_shadow http://firebirdsql.su/doku.php?id=drop_shadow

[annoynimouse]

Database should be created with "SQL Dialect 3", dialect 1 is legacy of no practical use. Dialect 2 is of no use except testing at all. You'd probably allow user to select database charset - ot assume UTF-8 everywhere yet that can restricts significantly maximim VARCHAR length. This charset should be specified for database when creating AND for connection itself when issuing actual commands. c:\Program Files\Firebird\Firebird_2_5\doc\README.intl.txt Specifying no charset may lead to heisenbugs.

You can monitor and cancel long requests either by API or in most recent servers with monitoring features. c:\Program Files\Firebird\Firebird_2_5\doc\README.monitoring_tables.txt c:\Program Files\Firebird\Firebird_2_5\doc\README.fb_cancel_operation.txt However in latter case you'd better use most recent builds, there were problems with tracing and monitoring before 2.5.2

your engine can issue commands into server using isql CLI tool - but i dunno how reliably that would work non-Latin characters. Or you can use some language-specific API binding.

[annoynimouse]

SYSDBA is specific user, that has every access to databases. You should not connect as SYSDBA except for administrative tasks like deleting obsoleted databases.

any user can create any new objects (tables, procedures, etc) in the database he connected too.

So perhaps the overall design should be like

1. has special database tracking all www-connections, FB-users and FB-databases
2. on new connection make via SYSDBA new user like "u1234" and new database like "/var/tmp/FB-test/u1234/db1234.fdb" 2.1) when creating database specify Dialect 3, charset UTF-8 or user-specified, and new user as DB owner 2.2) then connect to that database using that new user

When issuing commands set some watchdog timer, if requests takes too long, then connect to db1234 as SYSDBA and kill the request.

Track c:\Program Files\Firebird\Firebird_2_5\firebird.log - if some request crashes the server that can at least give some last info.

3. after session over remove user and database via SYSDBA connection

I don't remember if you can enforce per-database temp file quota other than using custom applications with fb-embedded or fb-classic local mode

or maybe xinet.d can do it, if it is used to manage FB Classic on unix, dunno

[jakefeasel]

Thanks very much for your help; I'll post updates here when I have more questions / progress to report.

[the-Arioch]

http://www.progdigy.com/?p=220

May or may not be of help.

Henry usually reluctantly wastes his time on communication, but his code usually rocks and he knows Firebird/Interbase API well.