Doesn't have much context on how to integrate with Supabase auth, ends up creating its own auth tables.

[salvinoto]

Feature request

Is your feature request related to a problem? Please describe.

When asking to generate based around Supabase Auth, it understands that Supabase has its own built in tables for auth, but it cant generate SQL unless it puts in a "mock users table". It would be much more useful if it had more context and features for Supabase specifically so I can just drag ' n drop a generated database in.

Describe the solution you'd like

I'd like for it to be able to use features like supabase auth without having to generate fake tables to simulate supabase auth. Id rather it just properly do it so I can copy and paste my migrations and have it integrated with my database automatically.

Describe alternatives you've considered

The only thing I can do is just edit the migration to remove the fake auth table, and replace all of the SQL with Supabase Auth tables.

Additional context

Here is an example conversation

I am using Supabase Auth. Can you write a multitenant database with support for teams, and have roles setup so that the data for each team and individual user can be protected by RLS policies?
To create a multitenant database with support for teams and roles, and to protect data using Row-Level Security (RLS) policies, we can design a schema that includes tables for users, teams, roles, and team memberships. Here's a basic setup:

Users Table: Stores user information.
Teams Table: Stores team information.
Roles Table: Defines roles within teams.
Team Memberships Table: Links users to teams with specific roles.
Let's create these tables:

Users Table
CREATE TABLE users (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL
);
Teams Table
CREATE TABLE teams (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL
);
Roles Table
CREATE TABLE roles (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL
);
Team Memberships Table
CREATE TABLE team_memberships (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id BIGINT REFERENCES users(id),
    team_id BIGINT REFERENCES teams(id),
    role_id BIGINT REFERENCES roles(id),
    UNIQUE(user_id, team_id)
);
Example RLS Policies
To protect data using RLS, you can create policies that ensure users can only access data related to their teams. Here’s an example of how you might set up RLS policies for the team_memberships table:

Enable RLS on the Table:

ALTER TABLE team_memberships ENABLE ROW LEVEL SECURITY;
Create RLS Policy:

CREATE POLICY team_access ON team_memberships
USING (EXISTS (
    SELECT 1 FROM team_memberships tm
    WHERE tm.user_id = auth.uid() AND tm.team_id = team_memberships.team_id
));
Apply the Policy:

ALTER TABLE team_memberships FORCE ROW LEVEL SECURITY;
Explanation
Users Table: Contains user details.
Teams Table: Contains team details.
Roles Table: Contains role definitions (e.g., admin, member).
Team Memberships Table: Links users to teams with specific roles, ensuring a user can only be part of a team once.
RLS Policies
Enable RLS: Activates row-level security on the table.
Create Policy: Allows users to access only the rows where they are part of the team.
Force RLS: Ensures that RLS policies are always applied.
This setup provides a foundation for a multitenant application with team-based access control. You can expand this by adding more tables and policies as needed.

Conversation renamed to Multitenant Database with RLS for Teams change
Greate, can you create it?

Error executing SQL
It seems there is an issue with the auth.uid() function because the auth schema does not exist in this database. In Supabase, auth.uid() is typically used to get the current user's ID. However, in this environment, we need to simulate this behavior.

To proceed, we can create a placeholder function or adjust the policy to work without auth.uid(). Here's how you can adjust the policy:

Adjusted RLS Policy
Instead of using auth.uid(), you can replace it with a placeholder or a specific user ID for testing purposes. Here's an example:

-- Assuming a placeholder function or a specific user ID is used
CREATE POLICY team_access ON team_memberships
USING (EXISTS (
    SELECT 1 FROM team_memberships tm
    WHERE tm.user_id = 1 AND tm.team_id = team_memberships.team_id
));
This policy assumes a user ID of 1 for demonstration purposes. In a real application, you would replace this with a dynamic way to get the current user's ID.

Would you like me to proceed with this adjustment?

[gregnr]

Thanks for the suggestion @salvinoto. We have thought about providing a tighter integration with Supabase, but would also like to keep this as a generic tool that can be used with any Postgres DB. Some ideas:

• We are planning to add SQL import support. When this is available, as a short-term solution you could create a "Supabase shim" SQL file that contains things like auth.users, auth.uid, etc and import that before your conversations.
• We could potentially add common preset SQL snippets (like the above suggested Supabase SQL) built right in to the app that you can use as a starting point for your DB
• Something else?

[salvinoto]

I think what would be useful is the Supabase shim method. It doesn't necessarily need to be only for Supabase, but I think it would be helpful if it understood Supabase upon request. The problem is that I don't want it to export SQL that requires writing those tables because it will fail, thinking it needs to overwrite those tables that existing when I restore the db from file downloaded. I'm just trying to think of the best way for it to simulate being a Supabase project. I understand that this can be difficult when trying to keep it generic and not tied to a specific vendor.