Live database table access with row-level permissioning

#1

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

0 Likes

#2

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

···

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

0 Likes

#3

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;

``

  1. 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:

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

0 Likes

#4

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

···

On Tuesday, November 18, 2014 11:30:13 AM UTC-6, 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

0 Likes

#5

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.

···

On Tuesday, November 18, 2014 11:34:19 PM UTC-5, Greg Fausak wrote:

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

On Tuesday, November 18, 2014 11:30:13 AM UTC-6, 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

0 Likes

#6

Hi Jimmy,

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 ..

>
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?

"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.

···

Am 19.11.2014 03:23, schrieb Jimmy Jia:

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.

0 Likes

#7

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.

···

On Thursday, November 20, 2014 5:29:51 AM UTC-5, Tobias Oberstein wrote:

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.

  1. 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

  1. 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 …

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;

  1. 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?

“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+unsub...@googlegroups.com.

To post to this group, send email to auto...@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.

0 Likes

#8

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

···

On Tuesday, November 18, 2014 at 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

0 Likes

#9

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.
···

On Wednesday, February 4, 2015 at 10:47:29 PM UTC-5, mth...@gmail.com wrote:

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

On Tuesday, November 18, 2014 at 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

0 Likes