Postgraphile: Hide functions that the user does not have access to in introspection

[EyMaddis]

Hi,
the introspection query currently always delivers all functions that exist in the watched schema.
Using functions will return a permission denied error for the relation/function.
It would be awesome if the introspection would only return all the functions that the user can actually access (select/execute for queries and update/insert/execute for mutations).

In our setup, we have a "public" GraphQL API, but several services (internal user roles) that have access to internal functions via PostGraphQL that are only usable with very specific roles (JWT) that the user is not able to get granted.
This leads to a lot of unusable functions for people who use GraphiQL.

A workaround could be #411

[calebmer]

To clarify when saying “user” do you mean the user that was authenticated with the Postgres connection string, or the user identified by the JWT token? It’s still an open question on whether we should change the schema on a user-by-user basis for GraphQL in general, but I was under the impression that the former case would hide functions that the connection string user does not have access to 😣

In development the connection string user might be a super user (to enable watch mode) which may mean they can see everything. Is this consistent with what you are seeing?

[EyMaddis]

To clarify when saying “user” do you mean the user that was authenticated with the Postgres connection string, or the user identified by the JWT token?

By the JWT token. I guess that PGQL needs to know which role has access in order to hide/show functions...?

In development the connection string user might be a super user (to enable watch mode) which may mean they can see everything. Is this consistent with what you are seeing?

We do not use a superuser.

[ed-g]

@EyMaddis I've made a patch to our version of postgraphql to limit which functions are visible. The difference is we only have one group of users all of whom can execute the same functions.

Maybe you'll find it useful as a starting place to customize your graphql server by building two graphql schemas for "internal" versus "public" users, and passing one schema or the other to graphql() depending on which role a connection's JWT has claims for.

diff --git a/resources/introspection-query.sql b/resources/introspection-query.sql
index 01e06c5..d4ac75d 100644
--- a/resources/introspection-query.sql
+++ b/resources/introspection-query.sql
@@ -45,6 +45,8 @@ with
       pg_catalog.pg_proc as pro
       left join pg_catalog.pg_description as dsc on dsc.objoid = pro.oid
     where
+      -- Hide functions we cannot execute.
+      has_function_privilege (current_role, pro.oid, 'execute') and 
       pro.pronamespace in (select "id" from namespace) and
       -- Currently we don’t support functions with variadic arguments. In the
       -- future we may, but for now let’s just ignore functions with variadic

[benjie]

I think this is best solved via running multiple schemas:

https://github.com/postgraphql/postgraphql/issues/427

I'm closing this to keep things tidy - feel free to re-open if you need more input.

[polobo]

The pg_catalog is a read-only view of every object in the system. The information_schema is similar but limits what is visible both by the types of objects defined in the standard as well as the visibility (through grants) of the user that is presently connected to the database. The fundamental design question here is whether this choice to not restrict the output of pg_catalog based upon user visibility is the correct one.

There is no way to "work around" this fundamental behavior of the PostgreSQL database by having multiple GraphQL schemas. #427 asks about connecting to different databases at run-time but the goal here is to hide parts of a single database based upon the user being connected. By definition both schemas have to point to the same location and thus both have the same view of pg_catalog.

I'm reasonably confident that using pg_catalog (instead of information_schema) is the right choice for these introspection queries. Thsu, if the goal is, during introspection, to only show those objects that the connected PostgreSQL Role can see/use, then the liberal interspersion of the functions defined at https://www.postgresql.org/docs/9.6/static/functions-info.html (Table 9-60) within the introspection queries is necessary.

It would probably be worthwhile to allow for runtime configuration (potentially on a per-object-type basis) so that one schema could enable the "visible only" filter while another could be left showing everything. Basically, AND (has_function_privilege(...) OR global_show_all_functions)

[polobo]

Re-reading...sorry, new here...I see that largely the use of schema does mean "PostgreSQL schema". I dislike the idea of adapting an application/data driven multi-schema system to accommodate limitations of tools. I do think I mis-interpreted some parts of how things work but overall I think my point still stands, generally

Whether to SET ROLE is definitely a different matter. I think now that this is what would be required for this particular scenario.

[benjie]

PostGraphQL can run as multiple roles (roles are specified via the JWT token's role attribute), but there is only one GraphQL schema for all roles. I believe this single schema is the way that GraphQL is intended to be used (for one thing, having one schema means you can leverage powerful tools such as https://github.com/apollographql/eslint-plugin-graphql to do static analysis and checking of your queries which is much more complicated if you have multiple schemas). Limiting the schema to only that functionality visible to the default role is overly limiting in my opinion. Given it's available to that database role via pg_catalog it seems the PostgreSQL team agree that allowing users to "see" that stuff is not a security concern. So I think the concern here is mostly to do with developer experience (DX) or just the desire for tidiness of the generated schema?

We've found also that pg_catalog is significantly faster to query than information_schema; and startup speed is definitely a concern we care about.

Given the above, please could you clarify exactly what you think we should do to improve PostGraphQL? There is definitely room for improvement, the introspection query for example has not been changed in quite a while!

[polobo]

Some have argued about PostgreSQL's interpretation of allowing visibility of unusable objects via pg_catalog. I'm on the fence but don't see it changing regardless. But since one has to have given a user explicit login credentials, and access to, the PostgreSQL server instance there are a lot of things that user can do to mess around regardless. This project, however, is placing itself in between the server and the ultimate end users and acting on their behalf. It is a quite reasonable desire for the admin of this software to expect this type of application to be a bit more conservative in what it makes visible to the users. That said I would indeed classify this as DX/UX.

A quick skim of the issues shows that there is a desire that, given a JWT role, and assuming those are mapped onto database roles, that the visibility of the corresponding database objects be taken into account when displaying introspection results.

Something like:
object, visible_to[], not_visible_to[]

If the in-scope JWT role is in one of those two arrays the object would be shown (or not) as indicated. If it is in neither array a new role-specific query would be run and the appropriate update made (kinda like cache invalidation). At launch you'd populate the listing of objects and have no visibility information recorded.

I don't know enough about the workings of PostGraphQL to comment on desirability or to be more specific. I just saw a repeated (with some variation) request being addressed in a somewhat undesirable way and figured I'd try to bring a different perspective to the conversation. I do think, though, as a middle tier layer, that dealing with visibility, even though the underlying system does not, would bring additional value to the application.

I have a monolithic database, with many specialized schemas, and have brought on a team that is introducing PostGraphQL to our company. Being able to setup a user for the PostGraphQL user that through permissions would limit the extent of the data model they can see to just those items that are currently being worked with would make on-boarding and active development easier than having to say "just ignore these other 80% of the objects that don't apply right now".

[benjie]

Thanks for your input. I don't think we can change the GraphQL schema on a JWT-by-JWT basis (based on roles) but you could specify a role to use for performing the introspection and then only make things visible to that role visible. Alternative GraphQL schemas should be accomplished by running multiple PostGraphQL instances, as detailed here: https://github.com/postgraphql/postgraphql/issues/427#issuecomment-293032341

Fortunately, even the introspection in PostGraphQL will be pluggable in v4; so if anyone wants to they can make an alternative introspection plugin, and if that's successful enough and well-liked it may even make its way into core.

If you wanted to take this on, v4 is fairly usable as-is (though I'm still calling it alpha-quality at this stage) and I can give instructions on how to get it set up if you like (requires familiarity with Node.js) - so you can start using this functionality today!