Filter all queries

[mateo2181]

I want to add a condition where(companyId={idCompanyId}) in all queries. This column companyId is present in all tables, companyId is in every request inside jwt token.

Thanks in advance!

[benjie]

A restrictive RLS policy would be the best way to do this:

CREATE FUNCTION current_company_id() RETURNS INT AS $$
  select nullif(current_setting('jwt.claims.company_id', true), '')::int;
$$ LANGUAGE SQL STABLE;

CREATE POLICY must_match_company_id ON my_table
  AS RESTRICTIVE
  FOR ALL
  USING (company_id = current_company_id());

You could then execute the CREATE POLICY in a loop, once for each table.

It's also possible with plugins; but this way is more secure.

[mateo2181]

Yes, I would want to do this using a plugin. In Sequelize, I use a scope this way: scopes: { tenant: (tenant_id) => {return{ where: {empresaId: tenant_id}}} } I am trying using makeWrapResolversPlugin but I don't know how do it.

[mateo2181]

I can do it this way: query { mesas( filter: { companyId: { equalTo: 2 } } ) { totalCount } } but I want to manage this logic in the API.

I am using ConnectionFilterPlugin, PgSimplifyInflectorPlugin too.

[benjie]

There's no clean API for adding this restriction in the GraphQL layer and guaranteeing that you've added the restriction to every possible fetch on that table currently. PostGraphile is currently built according to the standard GraphQL pattern where the business logic (authorization, etc) is performed a layer below GraphQL; in our case it's performed by PostgreSQL RBAC, RLS, views and/or functions. You can read about this "business logic layer" approach in the GraphQL documentation here:

https://graphql.org/learn/thinking-in-graphs/#business-logic-layer

That said, adding a nice interface for adding these kinds of restrictions at the GraphQL layer is on my TODO list. For now, I'd recommend using RLS policies.

[mateo2181]

Good, that policy should be write directly in postgres, right? if I add that policy, I will be able to return all values from a table without that policy? (to a superadmin role)

[mateo2181]

I think the problem is that my companyId is inside the token that is generated in the API and used in every request from my webapp. I don't know how I can send that value (companyId) to the function current_company_id(). I mean, how that function know the companyId value.

[benjie]

Yes, write the policy into PostgreSQL. Superuser and database owner roles ignore RLS policies by default, you'll want to have PostGraphile connect to PostgreSQL as an unprivileged user.

You mentioned the company_id was in the JWT token, so PostGraphile will automatically expose it to PostgreSQL as jwt.claims.[claim_here] if you set a JWT secret - this function will then extract it inside your database:

CREATE FUNCTION current_company_id() RETURNS INT AS $$
  select nullif(current_setting('jwt.claims.company_id', true), '')::int;
$$ LANGUAGE SQL STABLE;

Read more about this here: https://www.graphile.org/postgraphile/jwt-guide/#postgresql-json-web-token-serialization-specification

[mateo2181]

I understand. I need to allow some users get all rows because I have an admin user (in API layer) who connect to database using same postgres user. So I will try to find another way to do this. I think I can do it if I extend schema (https://www.graphile.org/postgraphile/make-extend-schema-plugin)
is possible modify query that come from webapp?

[benjie]

That's fine; just change the policy:

CREATE POLICY must_match_company_id ON my_table
  AS RESTRICTIVE
  FOR ALL
  USING (current_user_is_admin() or company_id = current_company_id());

(You could use current_company_id() is null as the check if you want.)

[mateo2181]

I am trying to do it but I can't. In my tables companyId is empresaId. My pgSettings: pgSettings: async req => { console.log("EMPRESA",req.empresa); return ({ 'jwt.claims.empresa': req.empresa}) },

Commands I run in database: ALTER TABLE mesas FORCE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION empresa_id() RETURNS INT AS $$ select nullif(current_setting('jwt.claims.empresa', true), '')::int; $$ LANGUAGE SQL STABLE;

CREATE POLICY match_empresa ON mesas AS RESTRICTIVE FOR ALL USING ("empresaId" = empresa_id());

Perhaps I forgot something. The req.empresa value is OK. I checked that with log.

[benjie]

Superuser always ignores RLS; only the table owners are affected by FORCE ROW LEVEL SECURITY. Better to use a non-privileged role. Here’s some additional information: https://learn.graphile.org/docs/PostgreSQL_Row_Level_Security_Infosheet.pdf

[mateo2181]

Excuse me to do these questions here but I need to solve this, I am having permissions problems with user "client" that I created. I have tried some commands to allow this user read and update tables: GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO client; GRANT ALL PRIVILEGES ON DATABASE bar_desa TO client;

but when I use pgweb I always get same error: ERROR: pq: permission denied for relation 'table'

SOLVED :100:

[benjie]

👍 Well done! Remember we’re also on chat if you need us: http://discord.gg/graphile

[mateo2181]

Any way the policy is not working, I created the user in postgres, but the response with both users (with different emrpesa_id ) is the same.

[benjie]

drop schema if exists test cascade;
drop role if exists graphql431;

create role graphql431 with login password 'password';
create schema test;
set search_path to test, public;

CREATE OR REPLACE FUNCTION empresa_id() RETURNS INT AS $$
select nullif(current_setting('jwt.claims.empresa', true), '')::int;
$$ LANGUAGE SQL STABLE;

create table mesas (
  id serial primary key,
  "empresaId" int not null
);
alter table mesas enable row level security;

create policy select_everything on mesas for all using (true);
CREATE POLICY restrict_only_matching_empresa_id ON mesas
AS RESTRICTIVE
FOR ALL
USING ("empresaId" = empresa_id());

grant usage on schema test to graphql431;
grant select on mesas to graphql431;

insert into mesas("empresaId") values (1), (1), (2), (3), (5);

begin;
  set local role to graphql431;
  select * from mesas;
commit;

begin;
  set local role to graphql431;
  set local jwt.claims.empresa to '1';
  select * from mesas;
commit;

begin;
  set local role to graphql431;
  set local jwt.claims.empresa to '2';
  select * from mesas;
commit;

psql:/Users/benjiegillam/Dev/graphile-build/issue431.sql:1: NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function test.empresa_id()
drop cascades to table test.mesas
DROP SCHEMA
DROP ROLE
CREATE ROLE
CREATE SCHEMA
SET
CREATE FUNCTION
CREATE TABLE
ALTER TABLE
CREATE POLICY
CREATE POLICY
GRANT
GRANT
INSERT 0 5
BEGIN
SET
 id | empresaId
----+-----------
(0 rows)

COMMIT
BEGIN
SET
SET
 id | empresaId
----+-----------
  1 |         1
  2 |         1
(2 rows)

COMMIT
BEGIN
SET
SET
 id | empresaId
----+-----------
  3 |         2
(1 row)

COMMIT

[benjie]

[semi-automated message] Thanks for your question; hopefully we're well on the way to helping you solve your issue. This doesn't currently seem to be a bug in the library so I'm going to close the issue, but please feel free to keep requesting help below and if it does turn out to be a bug we can definitely re-open it 👍