Blocking new connection requests in Derby

[Kristian Waagan-4]

Hello,

When working on the feature of dropping databases (in-memory primarily),
it became apparent that the possibility to temporarily block incoming
connection attempts would be very useful.

I have started on a spec for this feature. Today I was supposed to
continue working on it, but got lost in code trying to write a prototype
to verify some claims instead :)
I'm choosing to post it now anyway to see if I get any feedback on
whether this feature would be useful in other scenarios. If so, there
may be new requirements not included in the current spec.
Verification of what I have written about authentication and
authorization is also definitely required.

If I get positive feedback, I can create a Jira and post a prototype
shortly after. This should allow us to make progress after our initial
discussion here. If you want to have a quick look at the test I threw at
Derby (currently doesn't work because all the required code hasn't been
posted, specifically the drop database code), see the class
DropWhileConnectingTest in patch 1a attached to DERBY-4436.


As a side note, most of my work so far has been with the embedded
driver. I suspect getting the network client to behave as well as the
embedded driver may be harder.


Regards,
--
Kristian

Blocking access to databases temporarily
========================================
Revision: 1.0, 2009-11-06T14:00
Changes:
  1.0: Initial revision.


Table of contents
1.  Motivation
2.  Relevant standards
3.  Embedded vs client/server
4.  Blocking modes
5.  Public blocking mode API
6.  Authentication and authorization
7.  Usage example
A.1 Glossary


1.  Motivation

When performing certain database operations, it would be useful for the
user performing them to temorarily block access to the database.
Examples of such operations are dropping a database, shutting down a
database and operations requiring an intermediate shutdown.

The block access feature would allow for:
 - More graceful shutdowns.
   New connections are rejected, and one could also implement a feature
   for waiting until all existing connections are disconnected.

-  Better error reporting for the clients.
   Instead of getting potentially "random" exceptions, a more
   well-defined set of exceptions can be expected. For instance, getting
   messages about NullPointerExceptions, container X not found,
   and database boot failed should occur more seldom. Instead the
   clients will get one of the following messages:
    * Database not found.
    * Shutdown exception.
    * Access blocked due to reason X.

- Easy way for a DBO/DBA to block clients while doing maintenance.


2.  Relevant standards

There are no relevant standards for this feature. It is a Derby-specific
extension, which in some cases is applied transparently for the user
(internal usage) and which in other cases requires Derby-specific code
in the user application to be applied. In the latter case, the JDBC
connection URL has to be manipulated to use the feature.


3.  Embedded vs client/server
 
When using the client/server architecture, the DBA has more options to
block access to a database than when using the embedded driver. For
instance, she could choose to take down the network server or block
incoming TCP/IP connections. These are however likely to affect the
whole system, not just a single database.

When using the embedded driver, the environment tends to be more
isolated. In many cases the DBA should be able to ensure she has
exclusive access to the database. But even here a temporary block of a
specific database may be useful, especially a more well-defined behavior
on database shutdown and deletion.


4.  Blocking modes

The descriptions of the blocking modes below don't account for Derby
autentication and authorization, which also has to succeed for a user to
be granted access to a database (see "Authentication and authorization"
for more details).

There are three modes:
 * ALLOW_NONE
   Blocks all incoming connections. Used in cases where it is certain
   that the database will not be used again, for instance when dropping
   a database. Can be considered an internal mode, as users shouldn't be
   able to activate this mode.
 * ALLOW_DBA
   Allows connections from users with the DBA role, but blocks all other
   connections.
 * ALLOW_ALL
   Allows all connections. This is the default mode.


5.  Public blocking mode API

The blocking mode is controlled through a JDBC connection URL attribute:
    block=enable|disable
In this case, 'enable' would block everyone except the DBA(s) (mode
ALLOW_DBA), 'disable' would turn off the blocking (mode ALLOW_ALL).

Alternatively, one could use different values to allow for more freedom
in later version:
    block=non-dba|none
In this case, one could add more classes later. One possible extension
is to allow for blocking predefined groups. In an environment where you
have producers and consumers, you could for instance choose to block all
the consumers while a major update or event is being prepared by the
producers: block=consumers. However, this may be better controlled by
other mechanisms, like SQL authorization (especially with
roles). Further, this usage scenario would benefit from a more
expressive API as you may want to both block and unblock one or more
groups in one command.


6.  Authentication and authorization

Users connecting to a database will have to authenticate and be
authorized as before. The exact process depends on the system
configuration. Slightly different rules apply when the a blocking mode
has been enabled and depending on whether the database has been booted
or not. If a database isn't booted, it is either shut down or it doesn't
exist.
Possible cases:
 - ALLOW_NONE, booted
   No authentication nor authorization.
 - ALLOW_NONE, down
   Not applicable.
 - ALLOW_DBA, booted
   Currently the user must be authenticated as the DBO. In the future,
   the user may also have to be authorized according to system
   privileges.
 - ALLOW_DBA, down, existing (boot)
   System wide authentication if enabled.
   The user must be the DBO.
   In the future it may be possible that being authorized according to
   some system privilege will allow the user to boot the database.
 - ALLOW_DBA, non-existing (create)
   System wide authentication if enabled.
   After the database has been created, the user must be the DBO to be
   able to connect to the database. Again, in the future it may be
   possible to grant some kind of DBA system privilege to a non-DBO
   user.
   
The addition of system privileges to Derby may affect the processes
described above. In general, the change will be that the user must also
be authorized to boot or create the database. For instance, user A may
be allowed to create databases under "/home/databases/applicationA", but
not under "/home/databases/applicationB".


7.  Usage example

Work is being done to allow a user to delete a database by adding an
attribute to the JDBC connection URL: "jdbc:derby:memory:myDB;drop=true"
When this connection request is issued, the process may consist of the
following steps:
 1) Block access to the database for new connections.
 2) Allow in-flight connection attempts to complete.
    This step is important because Derby does not handle the underlying
    database being deleted very well at this point.
 3) Signal database shutdown.
    Doing this stops currently idle connections to perform more work.
 4) Wait to allow in-flight transactions / queries to detect the
    shutdown request.
 5) Shutdown and delete the database.

Steps 4 and 5 are the ones most likely to experience unexpected errors,
because the active transaction(s) can be almost "anywhere" in the code.
High level operations should fail gracefully, whereas code deep down in
Derby may fail in exotic ways. More investigation is needed to determine
how important, if at all, it is to improve this.

At step 5 we need to lock down the database across boots - not because
we are planning to boot it again, but because we need to shut it down
prior to deleting it. Deleting the database without shutting it down
causes trouble, for instace because Derby may try to write to log files
not existing any more if a checkpoint is triggered. If we don't lock
down the database, someone may be able to slip through and boot it up
again before we have had time to delete the database and remove it from
the list of services.


A.1 Glossary

DBA - database administrator
DBO - database owner

[Rick Hillegas-2]

Thanks for writing up this spec, Kristian. If I understand correctly,
you are proposing a mechanism for rejecting new connections to a
database to handle the following situations:

1) Eliminate race conditions and ugly, puzzling diagnostic messages
during database shutdown and database deletion.

2) Protect extended database maintenance from corrupting, concurrent access.

It seems to me that (1) is a very reasonable use case and probably
doesn't need any additional api: by default a database shutdown/deletion
should prevent other users from connecting and should, in fact, kick out
all other users.

Can you give some use-cases for situation (2)?

Thanks,
-Rick

Kristian Waagan wrote:

> Hello,
>
> When working on the feature of dropping databases (in-memory
> primarily), it became apparent that the possibility to temporarily
> block incoming connection attempts would be very useful.
>
> I have started on a spec for this feature. Today I was supposed to
> continue working on it, but got lost in code trying to write a
> prototype to verify some claims instead :)
> I'm choosing to post it now anyway to see if I get any feedback on
> whether this feature would be useful in other scenarios. If so, there
> may be new requirements not included in the current spec.
> Verification of what I have written about authentication and
> authorization is also definitely required.
>
> If I get positive feedback, I can create a Jira and post a prototype
> shortly after. This should allow us to make progress after our initial
> discussion here. If you want to have a quick look at the test I threw
> at Derby (currently doesn't work because all the required code hasn't
> been posted, specifically the drop database code), see the class
> DropWhileConnectingTest in patch 1a attached to DERBY-4436.
>
>
> As a side note, most of my work so far has been with the embedded
> driver. I suspect getting the network client to behave as well as the
> embedded driver may be harder.
>
>
> Regards,

[Kristian Waagan-4]

Rick Hillegas wrote:

> Thanks for writing up this spec, Kristian. If I understand correctly,
> you are proposing a mechanism for rejecting new connections to a
> database to handle the following situations:
>
> 1) Eliminate race conditions and ugly, puzzling diagnostic messages
> during database shutdown and database deletion.
>
> 2) Protect extended database maintenance from corrupting, concurrent
> access.
>
> It seems to me that (1) is a very reasonable use case and probably
> doesn't need any additional api: by default a database
> shutdown/deletion should prevent other users from connecting and
> should, in fact, kick out all other users.
>
> Can you give some use-cases for situation (2)?

Hi Rick,

Situation (2) arises when doing maintenance operations, and is basically
an alternative to shutting down an application or the database server.
The goal is to ensure no other connections are made to the database.
This include boot- and creation-attempts. Some use-cases:
 a) You simply want to block out all users without shutting down the JVM.
 b) You need to hard-upgrade the database.
    In this situation, you don't want anyone to boot the database before
you (after it has been shut down).
 c) You need to (re-)encrypt the database.
    Same as above.

The most problematic situation today may be when you have a network
server with several active databases, say one for each of your
customers. How do you currently block access to one of the databases
without affecting the others?
We don't have system privileges (yet), so I can only think of using the
Java security manager, moving the database away (might be very costly
for large databases) or possibly disabling users at the system-wide
level. None of these mechanisms are easy to use with the current level
of tooling.

Also, by throwing a known exception stating the database has been
temporarily blocked, properly written applications can detect this and
simply try to connect again after waiting for a while.

Finally, after thinking more about it, this feature would need a
different API to ensure  *exclusive* access to the database. The reason
is that Derby has to boot a database to determine if the user is the DBO
(or a DBA). The rejection should happen prior to booting the database. I
have some thoughts about this, but let us first discuss the need for
such a feature.
Just to be clear, the spec would need rewriting to be accurate for (2).


I agree (1) and (2) are different situations, and that (1) alone will be
enough to address many issues. I don't have any problems dropping (2) if
it isn't considered useful.


Thanks,
--
Kristian

>
> Thanks,
> -Rick
>
> Kristian Waagan wrote:
>> Hello,
>>
>> When working on the feature of dropping databases (in-memory
>> primarily), it became apparent that the possibility to temporarily
>> block incoming connection attempts would be very useful.
>>
>> I have started on a spec for this feature. Today I was supposed to
>> continue working on it, but got lost in code trying to write a
>> prototype to verify some claims instead :)
>> I'm choosing to post it now anyway to see if I get any feedback on
>> whether this feature would be useful in other scenarios. If so, there
>> may be new requirements not included in the current spec.
>> Verification of what I have written about authentication and
>> authorization is also definitely required.
>>
>> If I get positive feedback, I can create a Jira and post a prototype
>> shortly after. This should allow us to make progress after our
>> initial discussion here. If you want to have a quick look at the test
>> I threw at Derby (currently doesn't work because all the required
>> code hasn't been posted, specifically the drop database code), see
>> the class DropWhileConnectingTest in patch 1a attached to DERBY-4436.
>>
>>
>> As a side note, most of my work so far has been with the embedded
>> driver. I suspect getting the network client to behave as well as the
>> embedded driver may be harder.
>>
>>
>> Regards,
>
>

[Rick Hillegas-2]

Kristian Waagan wrote:

> Rick Hillegas wrote:
>> Thanks for writing up this spec, Kristian. If I understand correctly,
>> you are proposing a mechanism for rejecting new connections to a
>> database to handle the following situations:
>>
>> 1) Eliminate race conditions and ugly, puzzling diagnostic messages
>> during database shutdown and database deletion.
>>
>> 2) Protect extended database maintenance from corrupting, concurrent
>> access.
>>
>> It seems to me that (1) is a very reasonable use case and probably
>> doesn't need any additional api: by default a database
>> shutdown/deletion should prevent other users from connecting and
>> should, in fact, kick out all other users.
>>
>> Can you give some use-cases for situation (2)?
>
> Hi Rick,
>
> Situation (2) arises when doing maintenance operations, and is
> basically an alternative to shutting down an application or the
> database server. The goal is to ensure no other connections are made
> to the database. This include boot- and creation-attempts. Some
> use-cases:
> a) You simply want to block out all users without shutting down the JVM.
> b) You need to hard-upgrade the database.
>    In this situation, you don't want anyone to boot the database
> before you (after it has been shut down).
> c) You need to (re-)encrypt the database.
>    Same as above.
>
> The most problematic situation today may be when you have a network
> server with several active databases, say one for each of your
> customers. How do you currently block access to one of the databases
> without affecting the others?
> We don't have system privileges (yet), so I can only think of using
> the Java security manager, moving the database away (might be very
> costly for large databases) or possibly disabling users at the
> system-wide level. None of these mechanisms are easy to use with the
> current level of tooling.
>
> Also, by throwing a known exception stating the database has been
> temporarily blocked, properly written applications can detect this and
> simply try to connect again after waiting for a while.
>
> Finally, after thinking more about it, this feature would need a
> different API to ensure  *exclusive* access to the database. The
> reason is that Derby has to boot a database to determine if the user
> is the DBO (or a DBA). The rejection should happen prior to booting
> the database. I have some thoughts about this, but let us first
> discuss the need for such a feature.
> Just to be clear, the spec would need rewriting to be accurate for (2).
>
>
> I agree (1) and (2) are different situations, and that (1) alone will
> be enough to address many issues. I don't have any problems dropping
> (2) if it isn't considered useful.
>
>
> Thanks,

Thanks for the quick response, Kristian. It seems to me that (2b) and
(2c) are similar to (1) in that they should be the default behavior. My
gut feeling is that (1) + (2b) + (2c) are very useful: we should always
freeze out other users when performing these operations. (2a) is still
murky to me and I would be tempted to not design a user-invokable api
for this until we know more about who would use it. It makes sense to me
to log an enhancement JIRA for (2a) but not design a user-invokable api
for it until a user turns up who needs it. It may be that that user will
have experience with a good api from another database, which we could mimic.

Thanks,
-Rick

[Kristian Waagan-4]

Rick Hillegas wrote:

> Kristian Waagan wrote:
>> Rick Hillegas wrote:
>>> Thanks for writing up this spec, Kristian. If I understand
>>> correctly, you are proposing a mechanism for rejecting new
>>> connections to a database to handle the following situations:
>>>
>>> 1) Eliminate race conditions and ugly, puzzling diagnostic messages
>>> during database shutdown and database deletion.
>>>
>>> 2) Protect extended database maintenance from corrupting, concurrent
>>> access.
>>>
>>> It seems to me that (1) is a very reasonable use case and probably
>>> doesn't need any additional api: by default a database
>>> shutdown/deletion should prevent other users from connecting and
>>> should, in fact, kick out all other users.
>>>
>>> Can you give some use-cases for situation (2)?
>>
>> Hi Rick,
>>
>> Situation (2) arises when doing maintenance operations, and is
>> basically an alternative to shutting down an application or the
>> database server. The goal is to ensure no other connections are made
>> to the database. This include boot- and creation-attempts. Some
>> use-cases:
>> a) You simply want to block out all users without shutting down the JVM.
>> b) You need to hard-upgrade the database.
>>    In this situation, you don't want anyone to boot the database
>> before you (after it has been shut down).
>> c) You need to (re-)encrypt the database.
>>    Same as above.
>>
>> The most problematic situation today may be when you have a network
>> server with several active databases, say one for each of your
>> customers. How do you currently block access to one of the databases
>> without affecting the others?
>> We don't have system privileges (yet), so I can only think of using
>> the Java security manager, moving the database away (might be very
>> costly for large databases) or possibly disabling users at the
>> system-wide level. None of these mechanisms are easy to use with the
>> current level of tooling.
>>
>> Also, by throwing a known exception stating the database has been
>> temporarily blocked, properly written applications can detect this
>> and simply try to connect again after waiting for a while.
>>
>> Finally, after thinking more about it, this feature would need a
>> different API to ensure  *exclusive* access to the database. The
>> reason is that Derby has to boot a database to determine if the user
>> is the DBO (or a DBA). The rejection should happen prior to booting
>> the database. I have some thoughts about this, but let us first
>> discuss the need for such a feature.
>> Just to be clear, the spec would need rewriting to be accurate for (2).
>>
>>
>> I agree (1) and (2) are different situations, and that (1) alone will
>> be enough to address many issues. I don't have any problems dropping
>> (2) if it isn't considered useful.
>>
>>
>> Thanks,
> Thanks for the quick response, Kristian. It seems to me that (2b) and
> (2c) are similar to (1) in that they should be the default behavior.
> My gut feeling is that (1) + (2b) + (2c) are very useful: we should
> always freeze out other users when performing these operations. (2a)
> is still murky to me and I would be tempted to not design a
> user-invokable api for this until we know more about who would use it.
> It makes sense to me to log an enhancement JIRA for (2a) but not
> design a user-invokable api for it until a user turns up who needs it.
> It may be that that user will have experience with a good api from
> another database, which we could mimic.

Rick,

Your reservations sound reasonable.
I'll trim down the spec and attach it to a Jira (not yet created) for
further review.

I'm still not sure if we can cover (2b) with default  behavior, though.
My understanding is that the database has to be shut down before you
make the connection to hard-upgrade the database. This means that you
have to somehow ensure nobody else can connect to the database in the
time-window from when you shut down the database to the connection with
the upgrade attributes is accepted. This process has to happen as two
separate connections.

Is my understanding wrong?
You still have to load the new Derby jars to upgrade, and in most
systems I assume this requires you to take down the application /
server. Using OSGi may be an exception...

Let me investigate a bit more, I'll post my findings to the list. The
problem may be "of academic interest only" ;)


Regards,
--
Kristian

>
> Thanks,
> -Rick

[Kristian Waagan-4]

Kristian Waagan wrote:

> Rick Hillegas wrote:
>> Kristian Waagan wrote:
>>> Rick Hillegas wrote:
>>>> Thanks for writing up this spec, Kristian. If I understand
>>>> correctly, you are proposing a mechanism for rejecting new
>>>> connections to a database to handle the following situations:
>>>>
>>>> 1) Eliminate race conditions and ugly, puzzling diagnostic messages
>>>> during database shutdown and database deletion.
>>>>
>>>> 2) Protect extended database maintenance from corrupting,
>>>> concurrent access.
>>>>
>>>> It seems to me that (1) is a very reasonable use case and probably
>>>> doesn't need any additional api: by default a database
>>>> shutdown/deletion should prevent other users from connecting and
>>>> should, in fact, kick out all other users.
>>>>
>>>> Can you give some use-cases for situation (2)?
>>>
>>> Hi Rick,
>>>
>>> Situation (2) arises when doing maintenance operations, and is
>>> basically an alternative to shutting down an application or the
>>> database server. The goal is to ensure no other connections are made
>>> to the database. This include boot- and creation-attempts. Some
>>> use-cases:
>>> a) You simply want to block out all users without shutting down the
>>> JVM.
>>> b) You need to hard-upgrade the database.
>>>    In this situation, you don't want anyone to boot the database
>>> before you (after it has been shut down).
>>> c) You need to (re-)encrypt the database.
>>>    Same as above.
>>>
>>> The most problematic situation today may be when you have a network
>>> server with several active databases, say one for each of your
>>> customers. How do you currently block access to one of the databases
>>> without affecting the others?
>>> We don't have system privileges (yet), so I can only think of using
>>> the Java security manager, moving the database away (might be very
>>> costly for large databases) or possibly disabling users at the
>>> system-wide level. None of these mechanisms are easy to use with the
>>> current level of tooling.
>>>
>>> Also, by throwing a known exception stating the database has been
>>> temporarily blocked, properly written applications can detect this
>>> and simply try to connect again after waiting for a while.
>>>
>>> Finally, after thinking more about it, this feature would need a
>>> different API to ensure  *exclusive* access to the database. The
>>> reason is that Derby has to boot a database to determine if the user
>>> is the DBO (or a DBA). The rejection should happen prior to booting
>>> the database. I have some thoughts about this, but let us first
>>> discuss the need for such a feature.
>>> Just to be clear, the spec would need rewriting to be accurate for (2).
>>>
>>>
>>> I agree (1) and (2) are different situations, and that (1) alone
>>> will be enough to address many issues. I don't have any problems
>>> dropping (2) if it isn't considered useful.
>>>
>>>
>>> Thanks,
>> Thanks for the quick response, Kristian. It seems to me that (2b) and
>> (2c) are similar to (1) in that they should be the default behavior.
>> My gut feeling is that (1) + (2b) + (2c) are very useful: we should
>> always freeze out other users when performing these operations. (2a)
>> is still murky to me and I would be tempted to not design a
>> user-invokable api for this until we know more about who would use
>> it. It makes sense to me to log an enhancement JIRA for (2a) but not
>> design a user-invokable api for it until a user turns up who needs
>> it. It may be that that user will have experience with a good api
>> from another database, which we could mimic.
>
> Rick,
>
> Your reservations sound reasonable.
> I'll trim down the spec and attach it to a Jira (not yet created) for
> further review.
>
> I'm still not sure if we can cover (2b) with default  behavior, though.
> My understanding is that the database has to be shut down before you
> make the connection to hard-upgrade the database. This means that you
> have to somehow ensure nobody else can connect to the database in the
> time-window from when you shut down the database to the connection
> with the upgrade attributes is accepted. This process has to happen as
> two separate connections.
>
> Is my understanding wrong?
> You still have to load the new Derby jars to upgrade, and in most
> systems I assume this requires you to take down the application /
> server. Using OSGi may be an exception...
>
> Let me investigate a bit more, I'll post my findings to the list. The
> problem may be "of academic interest only" ;)

I created DERBY-4447 to track this, and I trimmed down the initial spec
and attached it to the Jira issue.

I also wrote a test that demonstrated that we are vulnerable to race
conditions during hard-upgrade. Per today, the user have to ensure
exclusive database access by using means outside of Derby itself. This
is a separate issue, and the drop database functionality can be
implemented without being held up by it.

As a side note, Derby blocked concurrent access from two different
classloaders, which is good. The remaining issue then is
applications/frameworks where you can hot-swap the version of the Derby
jars/classes. If you plan to upgrade both the jars and the database
format in such an environment, you must take care to make sure the
database actually gets upgraded. An upgrade request issued against a
booted database will fail silently, giving the user the impression that
it worked.


--
Kristian

>
>
> Regards,