Live database table access with row-level permissioning

[Jimmy Jia]

Hi,

I'm looking to build out something with broadly the same goals a CRUD API, but with real-time elements. Specifically:

• I have a table in my database
• I want to allow web clients to be able to maintain a live view into that table
• Each client will be authenticated as a particular user, and should only see rows corresponding to that user
• Clients should be able to upsert and delete from the table (essentially I want the API on the client side to look a little bit like a dictionary keyed by the table's primary key, so the primitive operations look more like upsert/delete rather than insert/update/delete)
  

Naively, it seems like I would want to build this with PubSub for dispatching table updates to clients (I'm use PostgreSQL, so with TRIGGER -> NOTIFY), along with RPC for requesting initial snapshots and updating things on the database side.

I think I have an idea of how to handle authorization on the RPC side, but I'm not sure about on the PubSub side. It looks like I could either use subscriber whitelisting, or else have a separate topic per-user, and use authorization to control who can subscribe to which topic. Which is more idiomatic? Neither seems entirely ideal to me - whitelisting seems to require my publisher to keep track of session IDs, which seems like something I ought to delegate to the router, while per-user topics seems to imply that my publisher needs to manage a list of subscribed users to know which topics to publish to.

Also, are there some sorts of primitives available such that I don't have to write myself? For comparison, I'm looking for something a little bit like Meteor collections with allow/deny.

Thanks,

Jimmy Jia

[Jimmy Jia]

To clarify, I'm looking to do this with Crossbar.

[Jimmy Jia]

A couple more questions - I see that a bunch of table-related functionality is described or stubbed in PostgreSQLDatabasePublisher. There are a few subtleties here that aren't obvious, though, and I was hoping for clarification:

1) The syntax involved uses new functions (json_build_array, json_build_object) that are only in PostgreSQL 9.4 - will Crossbar be compatible with earlier versions of PostgreSQL?

2) The publisher uses txpostgres, which is not installed by the "postgres" extra option for crossbar

3) The described cb_publish function (https://github.com/crossbario/crossbar/blob/master/crossbar/crossbar/adapter/postgres/publisher.py#L62) does not appear to be defined anywhere. I can define it myself as follows, but is this correct?

CREATE OR REPLACE FUNCTION cb_publish(
  topic  VARCHAR,
  args   JSON DEFAULT '[]',
  kwargs JSON DEFAULT '{}'
)
  RETURNS VOID
AS
  $$
  BEGIN
    PERFORM
      pg_notify(
          'crossbar_pubsub_event',
          row_to_json(x) :: VARCHAR
      )
    FROM
      (SELECT
         'direct' AS "type",
         topic    AS topic,
         args     AS args,
         kwargs   AS kwargs
      ) x;
  END;
  $$
LANGUAGE plpgsql;

4) The code shows potential support for "direct" and "table" message types. I see from the roadmap that full-fledged database connectivity won't be present until Crossbar 2. However, is there any current documentation for how table messages are expected to work, such that anything I build now will involve as little pain as possible to port to whatever gets written?

On Tuesday, November 18, 2014 12:30:13 PM UTC-5, Jimmy Jia wrote:

[Greg Fausak]

With Autobahn V1 I opted for a pubsub model where the subscribers shared the same topic name.  This was for a monitoring application, so, my client might subscribe to 'http://domain.com/tenant/temp/alarm'.   My publisher didn't do a direct publish to this topic, instead it called an rpc which in turn updated a postgres database table.  The table contained the tenant id (along with the temp information, etc).  That action caused a trigger to push a notify which my Autobahn router received. The router then used the notify's tenant id and published to all sessions with that tenant id (whether or not that tenant was subscribed to the topic). Using this technique I was sure that the subscriber would only receive events that were qualified by the tenant id. 

This of course meant that I needed to track all sessions, and the tenants that they are related to.

With v2 I've opted to have a topic per tenant.  This does mean that the publisher has to know which topic to publish to.  But, that isn't that hard.  In my case I've pushed the tenant information into the topic, like:

com.tenant.1.temp.alarm

My client subscribes to that, and my publisher simply publishes to the topic regardless of whether or not it is subscribed.  The trick here is authorizing both the subscriber and publisher to that topic. I feel that this offers better symmetry.  the publisher need not know subscriber information to make this work.

-g

[Jimmy Jia]

That makes sense - I was a little worried about publishing to a very large number of topics for some reason, but upon closer inspection I'm think I probably don't need to worry about that, especially if e.g. each entry is only permissioned to a small number of users.

It seems like this doesn't really give me a great way to let clients additionally ask for only a subset of data from the server and have the filtering happen on the server side, but I think as long as I'm not dealing with too many modifications to the table, it's probably fine to apply relevant filters on the client side anyway.

[Tobias Oberstein]

Hi Jimmy,

Am 19.11.2014 03:23, schrieb Jimmy Jia:
> A couple more questions - I see that a bunch of table-related
> functionality is described or stubbed in PostgreSQLDatabasePublisher.
> There are a few subtleties here that aren't obvious, though, and I was
> hoping for clarification:

Please note that this isn't even announced nor released;) How did you
find it?

>
> 1) The syntax involved uses new functions (json_build_array,
> json_build_object) that are only in PostgreSQL 9.4 - will Crossbar be
> compatible with earlier versions of PostgreSQL?

Not yet decided .. but:

We will add a PG side API providing a PL/pgSQL function to publish().
That function will take a JSON or JSONB argument(s). So it should be
possible to make it work with <9.4, means you will have less convenience
for creating your JSON value to be published, but it should work.

>
> 2) The publisher uses txpostgres, which is not installed by the
> "postgres" extra option for crossbar

Ah, good catch. Fixed:
https://github.com/crossbario/crossbar/commit/5e20e17d4ad0a95f983b14abae89b0a2c24f07cb

>
> 3) The described cb_publish function
> (https://github.com/crossbario/crossbar/blob/master/crossbar/crossbar/adapter/postgres/publisher.py#L62)
> does not appear to be defined anywhere. I can define it myself as
> follows, but is this correct?

Yeah, PG-side API stuff not yet there .. essentially will be what you
wrote below ..

"table" => payload is too long for pushing over NOTIFY/LISTEN, hence
will go over a regular DB table instead.

It will be fully transparent from PG app code.

If payload is small, push over NOTIFY, if not, push over table.

>
> On Tuesday, November 18, 2014 12:30:13 PM UTC-5, Jimmy Jia wrote:
>
> Hi,
>
> I'm looking to build out something with broadly the same goals a
> CRUD API, but with real-time elements. Specifically:
>

> * I have a table in my database
> * I want to allow web clients to be able to maintain a live view
> into that table
> * Each client will be authenticated as a particular user, and

> should only see rows corresponding to that user

> * Clients should be able to upsert and delete from the table

> (essentially I want the API on the client side to look a little
> bit like a dictionary keyed by the table's primary key, so the
> primitive operations look more like upsert/delete rather than
> insert/update/delete)
>
> Naively, it seems like I would want to build this with PubSub for
> dispatching table updates to clients (I'm use PostgreSQL, so with
> TRIGGER -> NOTIFY), along with RPC for requesting initial snapshots
> and updating things on the database side.
>
> I think I have an idea of how to handle authorization on the RPC
> side, but I'm not sure about on the PubSub side. It looks like I
> could either use subscriber whitelisting, or else have a separate
> topic per-user, and use authorization to control who can subscribe
> to which topic. Which is more idiomatic? Neither seems entirely
> ideal to me - whitelisting seems to require my publisher to keep
> track of session IDs, which seems like something I ought to delegate
> to the router, while per-user topics seems to imply that my
> publisher needs to manage a list of subscribed users to know which
> topics to publish to.
>
> Also, are there some sorts of primitives available such that I don't
> have to write myself? For comparison, I'm looking for something a
> little bit like Meteor collections with allow/deny.
>
> Thanks,
> Jimmy Jia
>

> --
> You received this message because you are subscribed to the Google
> Groups "Autobahn" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to autobahnws+...@googlegroups.com
> <mailto:autobahnws+...@googlegroups.com>.
> To post to this group, send email to autob...@googlegroups.com
> <mailto:autob...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/autobahnws/f1acf962-e0b6-4d63-883b-eb12516c78df%40googlegroups.com
> <https://groups.google.com/d/msgid/autobahnws/f1acf962-e0b6-4d63-883b-eb12516c78df%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

[Jimmy Jia]

If the code is PyPI package, I consider it released (:

The Postgres < 9.4 thing isn't so bad - my main use case is setting up a trigger to send a notification after create/insert/delete for each row on a table, in which case I can just call row_to_json on the NEW/OLD rows.

Otherwise for building the object, row_to_json and array_to_json aren't quite as nice as the build functions in 9.4, but they're not terrible.

> <mailto:autobahnws+unsub...@googlegroups.com>.

[mthu...@gmail.com]

Jimmy, why wouldn't you just use Meteor?  Livequery does exactly this.  https://www.meteor.com/livequery

[Jimmy Jia]

A couple of reasons, but they change around. It mostly boils down to

1. I'd rather not use MongoDB - prefer to use a database that lets me express my data relationally and do joins. I know the Meteor guys eventually want to support SQL, but they're not there yet.
   
2. I'd prefer to be language-agnostic in server implementation. I see DDP clients in plenty of different languages, but no other DDP servers.
   
3. Prefer to use solutions that are less monolithic where appropriate, while Meteor is extremely monolithic.
4. Scalability past hundreds of users and a single machine

If I were building an MVP for a pet project, Meteor would be a strong candidate. I'm working on something a little bit larger right now and Meteor doesn't fit my needs.