Named Query Parameters

The Rdbhost protocol now uses named parameters in addition to positional parameters.

Where before you could submit a query like:

<input name="q"
value="SELECT * FROM personnel WHERE firstName = %s AND lastName = %s" />
<input name="arg000" value="John" />
<input name="arg001" value="Smith" />

Now you can make it more readable, like:

<input name="q" value="SELECT * FROM personnel WHERE firstName = %(first) AND lastName = %(last)" />
<input name="arg:first" value="John" />
<input name="arg:last" value="Smith" />

This addition has a couple of benefits. The first is the greater readability and greater ease in assuring the correct parameter values are matched to the correct query tokens.

The other is that each parameter can be used in the query multiple times. For example, in the following snippet, we want to ensure that the value is at least 0.

CASE WHEN (%(val))<=0 THEN trunc(%(val),6) ELSE 0.00 END

The query snippet could be written with positional parameters, but there would then be no way to ensure the same value is passed as both values, and a negative value could be returned. Using a named parameter ensures that parameter value is used both times.

Using your own SSL/TLS Certificate

Rdbhost now supports your using your own SSL/TLS certificate for your rdbhost subdomain, to encrypt and authenticate data transfer with your account at www.rdbhost.com.

Why Use Certificates

The SSL/TLS certificates have two purposes, encryption and authentication. Any certificate, including a free self-signed certificate, will enable the server to encrypt data transferred between server and client. This encryption prevents data from being observed by eavesdroppers who might have access to your data stream.

However, a connection encrypted with a non-authenticated certificate is vulnerable to a type of attack known as a 'man in the middle' attack. If someone can insert a proxy into the network path between the client and the server, they can conduct a 'man in the middle' attack, impersonating each end of the connection to the other end. When the client (browser) requests a certificate, the proxy sends its own, and then requests a cert from the server. The proxy would hold a certificate for each half of the connection, and thus decrypt and encrypt all data passing through, recording whatever part it is interested in intercepting. This attack can happen because with an un-authenticated certificate, the client has no way of knowing that it is getting a cert from the proxy rather than the server.

The way to prevent this sort of 'man in the middle' attack, if it concerns you, is to use an authenticated, or 'trusted', certificate. If you purchase a 'trusted' certificate from vendor, they supposedly verify that you are indeed the owner of the site, and issue you a certificate that is digitally signed by them using their own trusted certificate. The client can verify the issuers signature with the issuer directly, and thus be assured that the certificate you present is your own. The 'man in the middle' attacker can present a certificate, but presumably would not be able to convince a trusted certificate issuer that they owned *your* domain, and thus could not present a certificate for your domain with a trusted issuer certificate.

There are a few certificate issuers that are trusted by all browsers as released. If your vendor is not one of those, they will have a certificate signed by one of those, sign your certificate with theirs, and send a bundle of certificates that establish a chain of trust from a trusted issuer to you. Godaddy.com is a very popular issuer of certificates, and they issue a bundle file with multiple certificates to establish the chain of trust. The client browser can see who signed your certificate, read the issuers certificate and find who signed it, and repeat until an intrinsically trusted issuer is found.

User Experience

Even if you are not very worried about someone conducting such an attack, you might still want a signed certificate to avoid browser warnings. Browsers, especially Firefox, can be very assertive about warning users of perceived limitations in a site's certificate. Having a trusted certificate avoids warnings and gives the user a smoother user experience. The warnings are so common these days, that many users are blase about it, but it is your call as to how tolerant your users will be.

The Rdbhost Way

Buy your trusted certificate from a vendor of your choice. There are websites that will create a public key pair and a CSR Certificate Signing Request for you, and you should create the CSR using your domain and the subdomain you use for Rdbhost server access, for example rdbhost.mydomain.com. The certificate vendor will create a certificate based on the CSR and make it available for download.

If you have not registered a domain alias on the site, do so now, registering the domain and subdomain, that you use for Rdbhost database access, for example 'rdbhost.mydomain.com'. The domain alias page is linked from the profile page.

If you have not entered the subdomain into the DNS zone manager of your domain registrar, you need to do so.

Visit the Certificate Manager on www.rdbhost.com and paste the certificate into the form field. If your vendor provides a chain (or 'bundle') file, paste it in after your certificate. Paste the private key in at the beginning or the end. Each of these files is a simple text file, so it can be opened in a text editor for cutting and pasting.

Within a minute or two after submitting the form, your account will be accessible using that domain, secured by SSL/TLS and your authenticated certificate.

Limitations

This SSL/TLS service relies on SNI Server Name Indication, an extension to the HTTP protocol. SNI is supported by many modern browsers, but not all. On Windows, notably, SNI handling is left to the OS by most browser brands, and while Vista and later support it, XP does not.

If your user is accessing the database using a browser that does not support SNI, they will get the default Rdbhost certificate, in lieu of your trusted certificate.


As always, comments are welcome.

Wizardry

When we first went online, many months ago, with the MVP (minimum viable product), the profile page had three or four options, and no subordinate pages. As we have added features, the members area of Rdbhost.com has grown to include six detail pages in addition to the profile page itself, the Rdbadmin utility and the log display.

For any given user, some of those options are irrelevant, and it can be confusing to have to sort through them. We have just added a wizard, which steps the user through the process of configuring everything necessary for their application.

When a user first logs in, they are shown the wizard and it guides him or her through initial configuration. All configuration options are available from the members pages, outside the wizard, or the user can rerun the wizard at any time. Whatever suits each of you.

The wizard can be as brief as four screens, or as elaborate as eleven. Configuring for use from Python can be straightforward, and use from JavaScript is necessarily more elaborate, as the browser security constraints have to be navigated around. Where we cannot do everything required (subdomain configuration, for example) for you, the wizard gives you guidance on how to do the necessary tasks.

Rdbhost, of course, is at: http://www.rdbhost.com
and the wizard is at: http://www.rdbhost.com/mbr/wizard

Home, home on the Ra..

Our server move is done, and Rdbhost.com now runs on a later version of PostgreSQL, a later OS version, and is on a new server company. We were looking to upgrade the software versions, and Slicehost announced their planned termination, so off we went.

The new server hosting is with Rackspace. They seem to have a good reputation, so hopefully this will work out for us.

Rdbhost.com now runs on PostgreSQL 9.0.4. This is an upgrade from 8.3, and provides us some useful new features.

We have, in the past, setup roles as an automated website operation, but had to handoff permission setting to the user, requiring you to use SQL to set permissions for each table, index, or other resource, for each role. PostgreSQL 9.0 supports setting permissions globally (within the schema) for a given role, and permits setting default permissions for resources not yet created. For now, that is useful to you in manually setting privileges, but we will be providing an automated setup to set permissions broadly for each role.

Rdbadmin has been upgraded to accommodate system table changes in the PostgreSQL upgrade.

David

Comments are welcome.

Not one, but *two* new videos

It's been months since RDBHost added CORS support enabling cross-site AJAX requests, and even longer since we put the JavaScript module online for general use.

A few days ago, we put online a new video showing the complete process of creating a JavaScript app.  As online videos go, it is a tad long at 11 minutes plus, but it does cover the whole process.


If you think 11:25 is just too long for a video, we put up another shorter video.  It shows a few features of the Rdbhost database administration utility, RdbAdmin.



Link to JavaScript Video
Link to RdbAdmin Video
Link to Rdbhost.com

Host your database-backed site on GitHub, for free!

We could say, "Host your github page's database on Rdbhost, for free", and be equally correct.  But Github has a greater profile than we do, so they get first billing.

A database backed website is mostly in the database, but it cannot be done without some static content.  The styling and the code are in plain files, which you have to host somewhere behind an accessible url.   Rdbhost clients can server static content from the database, using plain urls, but you can reasonably want to host static content elsewhere.  Github.com is a very well known git repository host that can also serve web pages.  The web site is under your domain name, and the git repository hosting doubles as a handy deployment system for the site.

Github hosts the static pages, and Rdbhost hosts the database, for a database-backed web application at no cost¹.  We describe how to set this up, in some detail, here.

We have a demonstration online, at http://gh.rdbhost.com/threefaves , of a database-backed site hosted on github.  Three Faves is a little taste-association app, the simplest demonstration app I could think of that might be fun. The site content itself is in a public repository at https://github.com/rdbhost/rdbhost.github.com .   All coding is in the repo, in one file, there for you to read and copy.  The Rdbhost account white-lists SQL queries, so undesirable queries cannot be run against the threefaves account.

Links:
Rdbhost SQL Database Host
Threefaves Site
Threefaves Repo


¹ We say free, but that is qualified: Github will host non-private content for free, at apparently no volume limit; Rdbhost will host private or public data for free, but only at low volumes.

Minor gains

The Rdbadmin utility has been upgraded again.  I improve it from time to time, and sometimes I write aobut the changes.

A bug-fix corrected the problem that dropped schema names from function identifiers.  This proved to be a problem in managing the log trimming functions for our sql logging facility.

We also added comment displays to all the items of the table structure display, including indexes and constraints.  Only the table structure edit tool facilitates adding comments, but comments can be added to anything using SQL, like:

COMMENT ON INDEX idx_name IS 'this index is the primary key';

We have been adding comments to tables and other resources that Rdbhost creates in user accounts; When you go exploring existing tables using Rdbadmin, the comments displayed will help you, hopefully, understand how each resource was intended to serve.

SQL Logging, how mundane

When you're sending requests to a database, or to any online API, it can be useful to be able to tell what requests are getting through, and whether results are being sent back to your client.

Rdbhost accounts now have SQL query logging.  Each SQL query is stored in a table in your account, with arguments and contextual information.  The log table is in a separate schema (called 'monitor') to minimize name collisions with your data tables, but you can run queries against the log table just like any other table.  The page displaying recent queries is linked from the account profile page.

The logs are trimmed automatically, and you can redefine the trimming function to set your own criteria.

https://www.rdbhost.com/sqllogging.html

If you are unacquainted with Rdbhost, each account has one database, which can be accessed via a choice of client-side APIs, or from an administration program, RdbAdmin, that we host.

A post about Authentication

At Rdbhost, there are passwords, and there are 'authcodes'.  What is the difference?

Passwords are about 30 characters or less, memorable, and submitted with an email address as a login.  The email/password login process is back-stopped by failure counters, that inhibit a brute force attack on the password by requiring increasing delays between tries.

Authcodes are 50 characters, base-64 character set, that are randomly generated and assigned to the account.  Machine requests to the database are submitted with a role name and an authcode.  There is a failure counting here, too, but for performance reasons there are multiple counters distributed around and they don't max out simultaneously, and the tolerated failure count for each is fairly high.  The authcodes are fairly resistant to 'social engineering' type attacks, as nobody chose them and there is little reason to remember them.  They get embedded in code, and forgotten.

Two roles have authcodes, and two do not.  Other than the super role, only the auth role has an authcode.

The auth role is different from the super role in that it can only run white-listed queries, and a given white-listed query can be restricted to only run for that particular role.  Since the auth role requires authentication, you can restrict its use to certain prequalified users, and give the role more Postgres privileges and its white-listed queries more power.

There has always been a way to change passwords, and now there is a way to reset authcodes, meaning to replace one with a new random authcode.

https://www.rdbhost.com/roles.html

More Backup Options

This week, Rdbhost has upgraded its bulk data transfer tools to be able to save the database dump to Amazon's S3 service, and to restore from same.

The additions are found on the Bulk Transfer page, which is in the left-hand menu bar on most membership pages.  There is an S3 configuration page, which askes for the access-key and secret-key, and the name of the bucket to store to.  The transfer to S3 page itself will save to an object named for the account, something like 'rdbhost0000000002' for account 2.  The S3 to rdbhost transfer asks for the object name.

The intended purpose for the tool is safe-keeping of snapshots of your database, for restoration in the case of data loss, but it is also useful for transferring databases quickly from one account to another.

The bulk transfer server uses streaming data flows (for all bulk transfers, not just S3), so no temporary copies of the dump are made anywhere, and the largest databases can be transferred.  The server will open up to five simultaneous transfers to accelerate the data flow to S3, so the S3 backup will presumably be faster than saving to the browser, though we have not measured the improvement.

You do need an Amazon Web Services Account (see aws.amazon.com), before you can use this streaming to S3 service, and you should create a bucket to receive the object.  We only create the object and put the data in it; the bucket must pre-exist. 

https://www.rdbhost.com/bulktransfer.html

Update for RdbAdmin

The RdbAdmin utility has been updated.  This is the 3rd major revision, and adds quite a few nice features.

New features:

• Supports use of back and forward buttons within the app.  This was implemented using the sammy.js library.
• Breadcrumb display to assist navigation.
• New Function editor
• New Trigger editor, linked from the table structure page.
• Record Editing forms now include table and column comments; when adding or editing a record, you can see how the table owner described each field.
• Record editing now supports a null checkbox for making value NULL.
• Most forms (all except SQL-command) support dynamic display of SQL generated.  As you fill in the form, you can see what SQL RdbAdmin will generate to change the database when you submit the form.
• Most forms have an edit option, so that dynamically generated SQL can be edited in the SQL-command form.  So if you need your query to include options not provided by the form, you can 'rough-in' the necessary query using the form, and fine-tune it by editing in the SQL-command form prior to submitting.
• Most forms have an 'add query to lookup.queries' link that will display the record-insert form, with the dynamic SQL from the previous form pre-loaded into the record form.  When you have a query perfected and tested, you can add it to the lookup.queries table in one step.  The lookup.queries table enables your script to execute queries by keyword, and invoke queries using roles that cannot execute free-form queries directly.
• In various forms interface now enables and disables buttons depending on relevance.
• The '** keep same **' option in record-edit type lists is now gone, replaced by relevant custom type name.
• In select panel, records are editable whenever a suitable index is available to uniquely identify unique records.
• You cannot see this, but most classes have been refactored to make private attributes genuinely private, and minimize the number of global variables.

Todo:

• Restore syntax highlighting; Previously, it previously used CodeMirror, but I had intermittent problems with code dependencies not getting defined.
• Add type editing tool, for creating and modifying custom types.

Links:
Github repository
Sample Account

.

Welcome to the new world. HTTP Databases and JSON Storage. Quirkey

Aaron Quint (Quirkey) blogged (more than a year ago, but still very relevant) about the transforming of web architecture.

Welcome to the new world. HTTP Databases and JSON Storage. The simple act of making the database and the browser more powerful on either end has destroyed the need for the middle tier. In the new architecture, Our database (JSON/HTTP based ...) serves data as JSON directly to the browser. On the browser side we create a much smaller/tighter ‘controller’ layer with JavaScript. This handles the directing of the user to the right place, the displaying of the data to the user, and the conversion of user interaction into state + data. This middle piece is jQuery + Sammy.js.

His blog post uses CouchDB and CloudKit as the server-side elements of the architecture, but his commentary on the appeal of omitting the whole custom server-side layer is just as relevant to Rdbhost as to either of those.

Rdbhost differs from CouchDB, in that the latter is a document database, and the former uses a relational database.  For any particular application, one will be preferable to the other.  I cannot speak to the authorization aspects of CouchDB as I have no experience with it, but several commenters on Quirkey's post had cautionary statements regarding the authentication/authorization.

Rdbhost supports multiple role-permission levels, and the ability to easily control what exact queries each role can execute.  See training your server, and roles pages.

Upgrade

I sent an email to all of our account holders about three weeks ago, informing them of a planned upgrade of our back-end server from PostgreSQL 8.3 to 9.0.1. We projected that conversion to happen the week after Christmas; that is now last week.

It didn't happen, because other stuff took longer than planned. We are now planning for this to happen the last week of January. That should allow time to get all the prerequisites done, and the conversion automated.