Function returns set-of view fails introspection if view does not define primary key

[felipenmoura]

Describe the bug
I reported this possible bug in the main supabase repository, but I believe it could be linked here.

supabase/supabase#20106

I followed the template for issues in that repository

[olirice]

Since we don't have a reproduction case, could you please send your project_ref so I can investigate it?

Its most likely the issue solved by this PR

[felipenmoura]

Since we don't have a reproduction case, could you please send your project_ref so I can investigate it?

Its most likely the issue solved by this PR

Interesting.
The project's url is this https://supabase.com/dashboard/project/fkdkdrrilrzbkvtqglhy
Does this help?

I see that PR was already merged. How could I make use of it to solve my case?

[olirice]

Okay this is a related but different case than PR.

You've got 2 functions get_....(uuid) (omitted for privacy) that return setof <some_view>
we currently check that the returned entity is selectable before exposing a function in the GraphQL AP. The 2 views ARE selectable by authenticated and anon but the views don't define a primary key so their associated types are omitted from the __Schema, making the function's reference invalid.

---

I think the outcome you're looking for is to be able to query the two views. To do that you can add a comment directive primary key to make them show up in the GraphQL API

comment on view "or...ers" is e'@graphql({"primary_key_columns": ["col1", "col2"]})';
comment on view "us....ture" is e'@graphql({"primary_key_columns": ["col1", "col2"]})';

and then you won't need the get_... functions because the views are queryable just like your tables.

More info here https://supabase.github.io/pg_graphql/views/

---

If that assumption was wrong, and you want to exclude the 2 get_... functions instead: you can revoke execute permission for the anon, authenticated, and public roles to remove them from your API, which will also solve the introspection issue.

revoke execute on function get_orgusers from anon, public;
revoke execute on function get_user_access_structure from anon, public;

and your schema will load, but you won't be able to query those functions

[olirice]

renaming this issue for tracking

[olirice]

TODO: update this logic

pg_graphql/src/graphql.rs

Lines 1290 to 1299 in 2bb35d5

	// If the return type is a table type, it must be selectable
	if !match &return_type {
	__Type::Node(table_type) => table_type.table.permissions.is_selectable,
	__Type::Connection(table_type) => {
	table_type.table.permissions.is_selectable
	}
	_ => true,
	} {
	return None;
	}

to make sure they table/view will be in the schema.

The logic should be the same as in __Schema

pg_graphql/src/graphql.rs

Lines 3955 to 3959 in 2bb35d5

	for table in self
	.context
	.tables
	.values()
	.filter(|x| self.graphql_table_select_types_are_valid(x))

its a small/simple change

[felipenmoura]

Interesting.
Thanks.
In this case, if I revoke those permissions, my server-side authenticated token (aka "the app") will still be able to call those functions, right?

If that assumption was wrong, and you want to exclude the 2 get_... functions instead: you can revoke execute permission for the anon, authenticated, and public roles to remove them from your API, which will also solve the introspection issue.

and your schema will load, but you won't be able to query those functions

In the side notes...I hope this has helped supabase to get better :)

[olirice]

if I revoke those permissions, my server-side authenticated token (aka "the app") will still be able to call those functions, right?

If you change the permissions (option 2) you would be able to call the functions using a direct database connection but not over the API as an anonymous or authenticated user.

if you set the comment directive (option 1) you will be able to access the functions over the API and via direct connection

[olirice]

In the side notes...I hope this has helped supabase to get better :)

it did! thanks for reporting