users restricted to their records in a table/view

[rkoberg]

I think I know the answer is "use a function", but wanted to check. I have some tables/views (below, knex.js migration) around a shopping cart. I would like it so that users (or admin role'd users) can only access (CRUD) their respective records through non-RPC requests. I was also hoping not to have to proxy through my app server and have the user directly request through the api server.

Open to changing the below if it helps with the above.

    knex.schema.createTable('carts', function (table) {
      table.increments();
      table.integer('subtotal').notNullable().defaultTo(0);
      table.string('token').notNullable();
      table.integer('user_id').notNullable().references('users.id');
      table.timestamps(true, true);
    }),

    knex.schema.createTable('line_items', function (table) {
      table.increments();
      table.integer('cart_id').notNullable().references('carts.id');
      table.integer('quantity').notNullable().defaultTo(0);
      table.integer('wine_variant_id').notNullable().references('wine_variants.id');
      table.timestamps(true, true);
    }),

    knex.schema.createTable('cart_events', function (table) {
      table.increments();
      table.integer('cart_id').notNullable().references('carts.id');
      table.integer('line_item_id').notNullable().references('line_items.id');
      table.string('event').notNullable();
      table.integer('user_id').notNullable().references('users.id');
      table.timestamps(true, true);
    }),

[ruslantalpa]

What you arle looking for (concept) is called Row Level Security. You can do that using a new feature in postgresql 9.5 or you can do that using views like here https://github.com/ruslantalpa/postgrest-rls-example For now i would suggest views

[rkoberg]

Yes, that what I am looking for. Your postgrest-rls-example project has more "new to me" sql than I have ever looked at :) Installing vagrant now... Thank you.

[ruslantalpa]

no need for vagrant, that is just a quick way for someone to boot the system, but you can jsut look at the sql, the interesting part is here https://github.com/ruslantalpa/postgrest-rls-example/blob/master/sql/api_schema.sql

[rkoberg]

So I was able to get back to this and have it working, I think, following the example set in https://github.com/ruslantalpa/postgrest-rls-example and http://postgrest.com/examples/external_auth/ :

CREATE OR REPLACE FUNCTION app_user_id()
  RETURNS INTEGER
STABLE
LANGUAGE SQL
AS $$
SELECT nullif(current_setting('postgrest.claims.user_id'), '') :: INTEGER;
$$;

Not understanding why this works: nullif(current_setting('postgrest.claims.user_id'), '') but it does... Why does this work?

The I have a view like:

CREATE OR REPLACE VIEW view_customer AS
  SELECT
    t.title,
    t.subtotal,
    t.token,
    t.user_id,
    t.updated_at
  FROM customers t
  WHERE current_user = 'admin' OR t.user_id = app_user_id();

Then an api call like /view_customer will return only the authenticated user's customer entity.

[ruslantalpa]

Dont forget with local check option; that part is important for update/insert

[rkoberg]

Thank you for pointing that out. My view above is actually more complex (joining other tables -- below) than what I have above. If I add WITH local CHECK OPTION to the view as I currently have it, I get an error:

 - WITH CHECK OPTION is supported only on automatically updatable views

Is there some way to have this view still do the joins, but still allow updates/inserts (at least on the FROM table)? From looking at some docs/posts, it looks like there can be no joins, group by, aggregation. I suppose I could have this one view that does the joins for read only and then other views for the customers table and views for each joined table that will allow updates/inserts to their single underlying table. Do you think that would be the best approach? Here is the view as I have it currently. (This brings up another related issue for which I will create a new issue.)

CREATE OR REPLACE VIEW view_customer AS
  SELECT
    t.id,
    t.title,
    t.subtotal,
    t.user_id,
    t.updated_at,
    json_build_object(
        'id', c.id,
        'discount_code', c.discount_code,
        'subtotal', c.subtotal,
        'token', c.token,
        'customer_id', c.customer_id,
        'updated_at', c.updated_at
    ) as cart,
    json_agg(
        json_build_object(
            'id', li.id,
            'cart_id', li.cart_id,
            'quantity', li.quantity,
            'wine_variant_id', li.wine_variant_id,
            'updated_at', c.updated_at
        )
    ) AS lineItems
  FROM customers t
    LEFT JOIN carts c ON t.id = c.customer_id
    LEFT JOIN line_items li ON c.id = li.cart_id
  WHERE t.user_id = app_user_id()
  GROUP BY t.id, c.id
--   WITH local CHECK OPTION
;

[ruslantalpa]

i would say you don't need the joins at all since the things that you are doing with json_agg and build_object can be done with the select parameter.

[rkoberg]

I couldn't do it as I had it (at least with my limited knowledge), because of the hierarchical nature of how I had it (customer > cart > line_items). However, I was going to remove customers and just have users referenced by carts (so now it is cart > line_items), which works as you described:

CREATE OR REPLACE VIEW view_cart AS
  SELECT
    t.id,
    t.discount_code,
    t.subtotal,
    t.token,
    t.user_id,
    t.updated_at,

    (SELECT json_agg(
        json_build_object(
            'id', li.id,
            'cart_id', li.cart_id,
            'quantity', li.quantity,
            'wine_variant_id', li.wine_variant_id,
            'updated_at', li.updated_at
        )
    )
     FROM line_items li
     WHERE t.id = li.cart_id)    AS lineItems
  FROM carts t
  WHERE t.user_id = app_user_id()
  WITH local CHECK OPTION
;

[ruslantalpa]

you don't need that json_agg just have 2 views carts and items and then make the request carts?select=*,items{*} and you will get the same result as with this json_agg

[rkoberg]

Is that something that will work in the next postgREST? That does not work for me with 3.2.0. I get an error "could not find foreign keys between these entities". I assumed it was because views don't have the relationship, just the actual tables have those relationships. The request looks like:

/view_cart?select=*,view_line_items{*}

or

/view_cart?select=*,line_items{*}

(line_items is a table that users cannot read or write, but I get the same error regardless of the requests shown above) The views for this look like:

CREATE OR REPLACE VIEW view_cart AS
  SELECT
    t.id,
    t.discount_code,
    t.subtotal,
    t.token,
    t.user_id,
    t.updated_at
  FROM carts t
  WHERE t.user_id = app_user_id()
  WITH local CHECK OPTION;

CREATE OR REPLACE VIEW view_line_items AS
  SELECT
    t.id,
    t.cart_id,
    t.quantity,
    t.wine_variant_id,
    t.updated_at
  FROM line_items t
  WHERE t.user_id = app_user_id()
  WITH local CHECK OPTION;

The tables (created using a knex migration, but should be clear). I also added the user_id relationship.

knex.schema.createTable('carts', function (table) {
  table.increments();
  table.string('discount_code');
  table.integer('subtotal').notNullable().defaultTo(0);
  table.string('token');
  table.integer('user_id').references('id').inTable('basic_auth.users');
  table.timestamps(true, true);
  table.index('user_id', 'carts_user_id_idx');
}),

knex.schema.createTable('line_items', function (table) {
  table.increments();
  table.integer('cart_id').notNullable().references('carts.id');
  table.integer('quantity').notNullable().defaultTo(0);
  table.integer('user_id').references('id').inTable('basic_auth.users');
  table.integer('wine_variant_id').notNullable().references('wine_variants.id');
  table.timestamps(true, true);
  table.index('cart_id', 'line_items_cart_id_idx');
  table.index('user_id', 'line_items_user_id_idx');
}),

[ruslantalpa]

restart postgrest, views seem fine

[rkoberg]

I stop postgREST, rollback, migrate, seed, and then start postgREST. The error happens. (It also happens if the table is readable.) I just assumed it was a fact of life issue. But you are saying this should work. I don't see that. Is it possibly a bug in 3.2.0?

[ruslantalpa]

paste the exact definitions of the tables and views here (not the knex thing) as they are reported by postgresql and we'll go from there. i don't know what knex is generating, i need to look at the result

[rkoberg]

OK:

CREATE TABLE carts
(
  id INTEGER PRIMARY KEY NOT NULL,
  discount_code VARCHAR(255),
  subtotal INTEGER DEFAULT 0 NOT NULL,
  token VARCHAR(255),
  user_id INTEGER,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
  CONSTRAINT carts_user_id_foreign FOREIGN KEY (user_id) REFERENCES
);
CREATE INDEX carts_user_id_idx ON carts (user_id);

CREATE TABLE line_items
(
  id INTEGER PRIMARY KEY NOT NULL,
  cart_id INTEGER NOT NULL,
  quantity INTEGER DEFAULT 0 NOT NULL,
  user_id INTEGER,
  wine_variant_id INTEGER NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
  CONSTRAINT line_items_cart_id_foreign FOREIGN KEY (cart_id) REFERENCES carts (id),
  CONSTRAINT line_items_user_id_foreign FOREIGN KEY (user_id) REFERENCES ,
  CONSTRAINT line_items_wine_variant_id_foreign FOREIGN KEY (wine_variant_id) REFERENCES wine_variants (id)
);
CREATE INDEX line_items_cart_id_idx ON line_items (cart_id);
CREATE INDEX line_items_user_id_idx ON line_items (user_id);

[ruslantalpa]

a few things, i do not understand what this is for (or even if it's valid) CONSTRAINT carts_user_id_foreign FOREIGN KEY (user_id) REFERENCES, CONSTRAINT line_items_user_id_foreign FOREIGN KEY (user_id) REFERENCES ,

are these views is the same schema as the tables? if so, is it the schema that is being exposed with postgrest? what happens if you query the tables and not the views, like /cart?select=*,line_items{*}

[ruslantalpa]

then i also see that in one case you said it like this table.integer('user_id').references('id').inTable('basic_auth.users'); and in the cart case, you said it like this table.integer('cart_id').notNullable().references('carts.id'); see the diferences?

shouldn't it be references('id').inTable('carts')?

[rkoberg]

I used intellij IDEA to generate the DDL and copied the table definitions using the postgres dialect. One thing about that though: there is only a java driver for pg 9.4. I just used pg_dump --with-schema, but give a minute to try and put together the relevant parts or I could post or send the whole DDL? In the meantime:

are these views is the same schema as the tables?

Yes, the views are in the same schema as the tables, except for the basic_auth.users that gets a reference to the user's ID. All of the tables in the schema are only readable by admin. The views are mostly readable by anon users

if so, is it the schema that is being exposed with postgrest?

Don't know what you mean by this. postgREST is using the database that I am referring to. My view that uses json_agg works as expected.

what happens if you query the tables and not the views, like /cart?select=_,lineitems{}

The tables are only accessible to admin role'd users. I need to change some things in my current setup to try that query, but if I remember correctly, doing what you suggest does not work. Will report back in a bit, though.

table.integer('user_id').references('id').inTable('basic_auth.users'); versus table.integer('cart_id').notNullable().references('carts.id');

The latter one is the standard way to handle this type of thing in the latest version of knex (when the same schema is used). I had problems referencing the basic_auth.users table because it was in a different schema, and got it to work by using .inTable('basic_auth.users')

[ruslantalpa]

CREATE TABLE carts
(
  id INTEGER PRIMARY KEY NOT NULL,
  discount_code VARCHAR(255),
  subtotal INTEGER DEFAULT 0 NOT NULL,
  token VARCHAR(255),
  user_id INTEGER,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
  CONSTRAINT carts_user_id_foreign FOREIGN KEY (user_id) REFERENCES
);

returns

ERROR:  syntax error at or near ")"
LINE 11: );
         ^
Query failed
PostgreSQL said: syntax error at or near ")"

so there is something wrong with your table definitions

[rkoberg]

Here are some snippets from the ddl generate from pg_dump --with-schema:

--
-- Name: carts; Type: TABLE; Schema: public; Owner: rkoberg
--

CREATE TABLE carts (
    id integer NOT NULL,
    discount_code character varying(255),
    subtotal integer DEFAULT 0 NOT NULL,
    token character varying(255),
    user_id integer,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);

ALTER TABLE carts OWNER TO rkoberg;

--
-- Name: carts_id_seq; Type: SEQUENCE; Schema: public; Owner: rkoberg
--

CREATE SEQUENCE carts_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE carts_id_seq OWNER TO rkoberg;

--
-- Name: carts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: rkoberg
--

ALTER SEQUENCE carts_id_seq OWNED BY carts.id;

--
-- Name: line_items; Type: TABLE; Schema: public; Owner: rkoberg
--

CREATE TABLE line_items (
    id integer NOT NULL,
    cart_id integer NOT NULL,
    quantity integer DEFAULT 0 NOT NULL,
    user_id integer,
    wine_variant_id integer NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);

ALTER TABLE line_items OWNER TO rkoberg;

--
-- Name: line_items_id_seq; Type: SEQUENCE; Schema: public; Owner: rkoberg
--

CREATE SEQUENCE line_items_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE line_items_id_seq OWNER TO rkoberg;

--
-- Name: line_items_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: rkoberg
--

ALTER SEQUENCE line_items_id_seq OWNED BY line_items.id;

--
-- Name: view_cart; Type: VIEW; Schema: public; Owner: rkoberg
--

CREATE VIEW view_cart AS
 SELECT t.id,
    t.discount_code,
    t.subtotal,
    t.token,
    t.user_id,
    t.updated_at,
    ( SELECT json_agg(json_build_object('id', li.id, 'cart_id', li.cart_id, 'quantity', li.quantity, 'wine_variant_id', li.wine_variant_id, 'updated_at', li.updated_at)) AS json_agg
           FROM line_items li
          WHERE (t.id = li.cart_id)) AS lineitems
   FROM carts t
  WHERE (t.user_id = app_user_id())
  WITH LOCAL CHECK OPTION;

ALTER TABLE view_cart OWNER TO rkoberg;

--
-- Name: view_line_items; Type: VIEW; Schema: public; Owner: rkoberg
--

CREATE VIEW view_line_items AS
 SELECT t.id,
    t.cart_id,
    t.quantity,
    t.wine_variant_id,
    t.updated_at
   FROM line_items t
  WHERE (t.user_id = app_user_id())
  WITH LOCAL CHECK OPTION;

ALTER TABLE view_line_items OWNER TO rkoberg;

--
-- Name: id; Type: DEFAULT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY carts ALTER COLUMN id SET DEFAULT nextval('carts_id_seq'::regclass);

--
-- Name: id; Type: DEFAULT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY line_items ALTER COLUMN id SET DEFAULT nextval('line_items_id_seq'::regclass);

--
-- Name: carts_pkey; Type: CONSTRAINT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY carts
    ADD CONSTRAINT carts_pkey PRIMARY KEY (id);

--
-- Name: line_items_pkey; Type: CONSTRAINT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY line_items
    ADD CONSTRAINT line_items_pkey PRIMARY KEY (id);

--
-- Name: carts_user_id_idx; Type: INDEX; Schema: public; Owner: rkoberg
--

CREATE INDEX carts_user_id_idx ON carts USING btree (user_id);

--
-- Name: line_items_cart_id_idx; Type: INDEX; Schema: public; Owner: rkoberg
--

CREATE INDEX line_items_cart_id_idx ON line_items USING btree (cart_id);

--
-- Name: line_items_user_id_idx; Type: INDEX; Schema: public; Owner: rkoberg
--

CREATE INDEX line_items_user_id_idx ON line_items USING btree (user_id);

--
-- Name: carts_user_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY carts
    ADD CONSTRAINT carts_user_id_foreign FOREIGN KEY (user_id) REFERENCES basic_auth.users(id);

--
-- Name: line_items_cart_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY line_items
    ADD CONSTRAINT line_items_cart_id_foreign FOREIGN KEY (cart_id) REFERENCES carts(id);

--
-- Name: line_items_user_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY line_items
    ADD CONSTRAINT line_items_user_id_foreign FOREIGN KEY (user_id) REFERENCES basic_auth.users(id);

--
-- Name: line_items_wine_variant_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY line_items
    ADD CONSTRAINT line_items_wine_variant_id_foreign FOREIGN KEY (wine_variant_id) REFERENCES wine_variants(id);

--
-- Name: line_items_cart_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY line_items
    ADD CONSTRAINT line_items_cart_id_foreign FOREIGN KEY (cart_id) REFERENCES carts(id);

--
-- Name: line_items_user_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY line_items
    ADD CONSTRAINT line_items_user_id_foreign FOREIGN KEY (user_id) REFERENCES basic_auth.users(id);

--
-- Name: line_items_wine_variant_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: rkoberg
--

ALTER TABLE ONLY line_items
    ADD CONSTRAINT line_items_wine_variant_id_foreign FOREIGN KEY (wine_variant_id) REFERENCES wine_variants(id);

--
-- Name: carts; Type: ACL; Schema: public; Owner: rkoberg
--

REVOKE ALL ON TABLE carts FROM PUBLIC;
REVOKE ALL ON TABLE carts FROM rkoberg;
GRANT ALL ON TABLE carts TO rkoberg;
GRANT ALL ON TABLE carts TO admin;

--
-- Name: line_items; Type: ACL; Schema: public; Owner: rkoberg
--

REVOKE ALL ON TABLE line_items FROM PUBLIC;
REVOKE ALL ON TABLE line_items FROM rkoberg;
GRANT ALL ON TABLE line_items TO rkoberg;
GRANT ALL ON TABLE line_items TO admin;

--
-- Name: view_cart; Type: ACL; Schema: public; Owner: rkoberg
--

REVOKE ALL ON TABLE view_cart FROM PUBLIC;
REVOKE ALL ON TABLE view_cart FROM rkoberg;
GRANT ALL ON TABLE view_cart TO rkoberg;
GRANT ALL ON TABLE view_cart TO admin;
GRANT ALL ON TABLE view_cart TO pending_verification;
GRANT ALL ON TABLE view_cart TO verified_user;
GRANT ALL ON TABLE view_cart TO vip_user;

--
-- Name: view_line_items; Type: ACL; Schema: public; Owner: rkoberg
--

REVOKE ALL ON TABLE view_line_items FROM PUBLIC;
REVOKE ALL ON TABLE view_line_items FROM rkoberg;
GRANT ALL ON TABLE view_line_items TO rkoberg;
GRANT ALL ON TABLE view_line_items TO admin;
GRANT ALL ON TABLE view_line_items TO pending_verification;
GRANT ALL ON TABLE view_line_items TO verified_user;
GRANT ALL ON TABLE view_line_items TO vip_user;

[rkoberg]

Crap, I generated that from the DB that uses the json_agg, but apart from that it should all be the same. Basically, from what I have seen when working with postgREST is that the types of relationships you describe do not work. The only time I have seen postgREST be able to resolve references, when not explicitly defined as a one-to-one relationship, is when there is a dedicated lookup table like:

table "cart_line_items" with: cart_id | line_item_id

then postgrest will resolve, but when something like cart is referenced only from a non-lookup-type table it does not work (I have posted a few issues about this). Going the other direction does work. That is: ```/lineitems?select=,cartid{}

[ruslantalpa]

remove this part from the view definiton and it will work

( SELECT json_agg(json_build_object('id', li.id, 'cart_id', li.cart_id, 'quantity', li.quantity, 'wine_variant_id', li.wine_variant_id, 'updated_at', li.updated_at)) AS json_agg
           FROM line_items li
          WHERE (t.id = li.cart_id)) AS lineitems

no subselects in view definitions (except in the WHERE section)

[ruslantalpa]

i am quite certain that if you query the tables directly it will work with what you have not, the views fail because of the subselect (which you did not mention in your example, you showed a simple view definition)

[ruslantalpa]

everything works, there are tests for these things, you are missing something. I need you to make sure that you generate the dump from the database that you say does not work and that you restart postgrest. These relations do work, for tables always, for views they work as long as you do not have subselects

[ruslantalpa]

and since you just did the dump form the wrong database, make sure that you are querying the right one and that the right postgrest instance is being restarted.

[rkoberg]

OK, reverted back. Here is the view:

CREATE VIEW view_cart AS
 SELECT t.id,
    t.discount_code,
    t.subtotal,
    t.token,
    t.user_id,
    t.updated_at
   FROM carts t
  WHERE (t.user_id = app_user_id())
  WITH LOCAL CHECK OPTION;

Using /view_cart?select=*,view_line_items{*} gives me the error could not find foreign keys between these entities

I emailed you the full DDL.

[rkoberg]

I could also email you a full dump of the whole DB if you want

[ruslantalpa]

try these view definitions (your tables are fine, i am quite sure they will work when queried directly)

CREATE VIEW view_cart AS
 SELECT t.id,
    t.discount_code,
    t.subtotal,
    t.token,
    t.user_id,
    t.updated_at
   FROM public.carts t
  WHERE (t.user_id = app_user_id())
  WITH LOCAL CHECK OPTION;

CREATE VIEW view_line_items AS
 SELECT t.id,
    t.cart_id,
    t.quantity,
    t.wine_variant_id,
    t.updated_at
   FROM public.line_items t
  WHERE (t.user_id = app_user_id())
  WITH LOCAL CHECK OPTION;

remember to restart postgrest. if it does not work i'll install the schema on my and and look at it later (but not sure exactly when i'll have the time in the next few days)

[rkoberg]

That is what I have (except I did not specify the public schema like FROM public.carts t). I just did that and I get the same error.

[rkoberg]

I just tried this type of thing as an admin user that has full control over the DB. If I do a request against regular non-view tables, it works as you explain:

wines?select=id,title,wine_variants{*}

That works and pulls in all of the wine_variants records associated with their parent wine. But this does not when querying against their view representation:

view_wines?select=id,title,view_wine_variants{*}

When using the actual wine_variants instead of view_wine_variants, but using the view for the wines table, it also does not work:

view_wines?select=id,title,wine_variants{*}

[rkoberg]

Here is the exact response JSON from postgREST:

{"details":"no relation between view_wines and wine_variants","message":"could not find foreign keys between these entities"}

With the response headers:

HTTP/1.1 400 Bad Request
Transfer-Encoding: chunked
Date: Thu, 22 Sep 2016 21:55:17 GMT
Server: postgrest/0.3.2.0
Content-Type: application/json; charset=utf-8

[ruslantalpa]

The problem has to do with the part that i mentioned about the views about the public part but it seems this is how postgresql outputs it when the view is in the public schema, it does not include the name of the schema and the regular expression expects schema.tablename and it only sees tablename. While this case should be handled by postgrest, it currently is not (a bug), this is why none of the relations on your views work, because they are all in the public schema (the tables as you see work as expected).

What i would suggest to you is have all your tables in the data schema, and all your views (and rpc function) in the api schema, and when starting postgrest, add the parameter -s api, this is what you are doing anyway, you want your api to be accessed through views and not the tables directly

[rkoberg]

Thanks for all of your help. I have to think about this and try some things out this weekend. Users with the admin role currently do have the permissions to read/write to tables. I suppose I would need to create admin views for those requests.