rr.interface+auth=ouch [Was: Re: updates from CADC publishing registry]

[Markus Demleitner]

Dear Registry,

On Fri, Apr 05, 2019 at 02:28:53PM -0700, Patrick Dowler wrote:
> ** and the interesting change:
> 
> - updated ivo://cadc.nrc.ca/sia and ivo://cadc.nrc.ca/tap records to
> include multiple securityMethod(s) in the accessURL (for sia I only did
> this in the SIA-2.0 capability); this seems to be schema-valid given that
> the records refer to VOResource-1.0 but I don't know if/how this kight
> effect recipients of such records
> 
> Let me  know if this works OK or if I need to make any further tweaks,

As far as I can see, this is fine as such, but it has uncovered an
issue this poses with RegTAP (and it shows once again we shouldn't
make anything a REC without implementation. Good thing we caught it
now).

In RegTAP 1.0, rr.interface is supposed to have a primary key (ivoid,
intf_index), where intf_index is something like the sibling index of
or anything else uniquely identifying the index element.

In the current RegTAP 1.1 draft, the plan is to produce one
rr.interface row per securityMethod child of an interface; this we
want so clients can quickly weed out interfaces they can't use by
saying something like

  WHERE security_method_id IS NULL

(which is now suggested as a default for "native" clients).

But this denormalisation means that (ivoid, intf_index) is no longer
a primary key in rr.interface.  That, in turn, is (something of) a
problem, because interfaces can have parameters in VOResource.  In
RegTAP, these are in the rr.intf_param table, and they are linked
through a foreign key into interface.

So: What's to happen with this foreign key relationship?

Here's what I've worked out so far:


Non-solution
------------

A half-natural solution would be to include security_method_id into
the primary key of rr:interface.  But that won't do, because
security_method_id right now usually is NULL, which self-respecting
RDBMSes will reject as components of primary keys.

We could of course map non-authenticated services to a non-NULL
value, perhaps an empty string.  But then ingestors would, in
addition to generating one row per securityMethod in interface, also
have to generate n rows in intf_param rows per securityMethod.
That's ugly in implementation and not particularly pretty in use.
Unless someone proposes very strong arguments in favor of this, I'd
immediately discard it.


Solution (a)
------------

We could drop the foreign key relationship from intf_param to
interface. Apart from that, we'd not touch intf_param, so the link to
interface would be through ivoid and intf_index.  A natural join with
rr.interface thus would do the right thing (sticking the paramters to
the interface they belong to).

If we make intf_param.ivoid a foreign key into rr.resource, such
params would still be dropped when a resource is updated by dropping
and ingesting again, so operationally it wouldn't be too bad.

As is to be expected when de-normalising things, it still feels a bit
precarious.  And, of course if someone says:

select name, ucd, access_url
from rr.interface
natural join rr.intf_param
where ivoid='...'

there'll still be a whole set of params in the result *per
securiy_method_id*.  Yes, that's what denormalisation is about, but
it's just another trap.


Solution (b)
------------

We could also forget about the de-normalisation and drop the
security_method_id column in rr.interface; in the proposed sample
queries, it was only used to filter for "unauth access possible"
anyway.  For that, we could add a column "open_access" (or a bit more
provicative, fair_interface:-) to rr.interface, which is true if
there's an empty securityMethod or none at all, false otherwise.

And then we wait what kind of discovery problems actually arise in
the context of authentication and will probably add
rr.security_method table in RegTAP 1.2.


Me, I'm now leaning towards Solution (b), and I'll implement and
specify that if nobody protests.  But for now I'm absolutely open to
alternative suggestions.

         -- Markus