S
Supabase•2mo ago
jkwok678

Unable to insert into a table

I have this RLS on my gis schema with a locations table. 
alter policy "Enable all authenticated users to access locations"
on "gis"."locations"
to authenticated
using (
  true
);
alter policy "Enable all authenticated users to access locations"
on "gis"."locations"
to authenticated
using (
  true
);
Trying to insert with 
const { data, error } = await supabase
    .schema("gis")
    .from("locations")
    .insert({
      name,
      address,
      owner: ownerId,
      point: `POINT(${longitude} ${latitude})`,
    })
    .select()
    .single();
const { data, error } = await supabase
    .schema("gis")
    .from("locations")
    .insert({
      name,
      address,
      owner: ownerId,
      point: `POINT(${longitude} ${latitude})`,
    })
    .select()
    .single();
I can't insert, - I see 
{
    "code": "42501",
    "details": null,
    "hint": null,
    "message": "permission denied for table locations"
}
{
    "code": "42501",
    "details": null,
    "hint": null,
    "message": "permission denied for table locations"
}
Is there another level of security somewhere? I am logged into my app
4 Replies
jkwok678
jkwok678OP•2mo ago
I've even tried disabling RLS on that specific table and trying to use that function again I can read into that table is there schema level permissions? Are my my RLS policies not applying? I tried 
SELECT 
  grantee,
  privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND table_name = 'profiles';
SELECT 
  grantee,
  privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND table_name = 'profiles';
and saw 
  {
    "grantee": "postgres",
    "privilege_type": "INSERT"
  },
  {
    "grantee": "postgres",
    "privilege_type": "SELECT"
  },
  {
    "grantee": "postgres",
    "privilege_type": "UPDATE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "DELETE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "TRUNCATE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "REFERENCES"
  },
  {
    "grantee": "postgres",
    "privilege_type": "TRIGGER"
  },
  ...
  {
    "grantee": "authenticated",
    "privilege_type": "INSERT"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "SELECT"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "UPDATE"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "DELETE"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "TRUNCATE"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "REFERENCES"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "TRIGGER"
  },
  ...
]
  {
    "grantee": "postgres",
    "privilege_type": "INSERT"
  },
  {
    "grantee": "postgres",
    "privilege_type": "SELECT"
  },
  {
    "grantee": "postgres",
    "privilege_type": "UPDATE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "DELETE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "TRUNCATE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "REFERENCES"
  },
  {
    "grantee": "postgres",
    "privilege_type": "TRIGGER"
  },
  ...
  {
    "grantee": "authenticated",
    "privilege_type": "INSERT"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "SELECT"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "UPDATE"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "DELETE"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "TRUNCATE"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "REFERENCES"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "TRIGGER"
  },
  ...
]
With 
SELECT 
  grantee,
  privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'gis'
  AND table_name = 'locations';
SELECT 
  grantee,
  privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'gis'
  AND table_name = 'locations';
I see 
[
  {
    "grantee": "postgres",
    "privilege_type": "INSERT"
  },
  {
    "grantee": "postgres",
    "privilege_type": "SELECT"
  },
  {
    "grantee": "postgres",
    "privilege_type": "UPDATE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "DELETE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "TRUNCATE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "REFERENCES"
  },
  {
    "grantee": "postgres",
    "privilege_type": "TRIGGER"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "SELECT"
  }
]
[
  {
    "grantee": "postgres",
    "privilege_type": "INSERT"
  },
  {
    "grantee": "postgres",
    "privilege_type": "SELECT"
  },
  {
    "grantee": "postgres",
    "privilege_type": "UPDATE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "DELETE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "TRUNCATE"
  },
  {
    "grantee": "postgres",
    "privilege_type": "REFERENCES"
  },
  {
    "grantee": "postgres",
    "privilege_type": "TRIGGER"
  },
  {
    "grantee": "authenticated",
    "privilege_type": "SELECT"
  }
]
It's like the authenticated RLS did not apply, whilst I've got extra things on my public.profile one
garyaustin
garyaustin•2mo ago
So you created this gis schema? Did you grant roles to use it? https://supabase.com/docs/guides/api/using-custom-schemas
jkwok678
jkwok678OP•2mo ago
I imagine thats it, but is there a way to do it in the GUI or is running the SQL statements the only way to do it?
garyaustin
garyaustin•2mo ago
SQL

Did you find this page helpful?