Only One User Sees All API Calls—Others Are Limited

[Matignaciom]

Hi everyone, I have a question: has anyone experienced an issue with Supabase where you have two users with the same permissions, and one can see all the API calls but the other can only see some? Why is this happening? 🤔

Additionally, I’ve made sure that both users have the same permissions and everything in my app, yet they still can’t see all the API calls. Only the first user I created can see them all, while newly created users can only see a few. It’s a strange bug.

It’s worth mentioning that I’m using the APK of my app; I haven’t uploaded it to any store yet, and I’m only running it on an emulator and on my phone.

I don't think it's the problem, but I'm sharing this code from my user management:

-- Instalar la extensión pgcrypto si no está ya instalada
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Eliminar triggers y funciones existentes con CASCADE para eliminar dependencias
DROP TRIGGER IF EXISTS hash_password_trigger ON usuarios;
DROP FUNCTION IF EXISTS trg_hash_password CASCADE;
DROP FUNCTION IF EXISTS hash_password_with_salt CASCADE;
DROP TRIGGER IF EXISTS after_user_insert ON usuarios;
DROP FUNCTION IF EXISTS trg_create_auth_user CASCADE;
DROP FUNCTION IF EXISTS trg_update_auth_user CASCADE;
DROP FUNCTION IF EXISTS trg_delete_auth_user CASCADE;
DROP FUNCTION IF EXISTS create_user(TEXT, TEXT) CASCADE;
DROP FUNCTION IF EXISTS update_user_in_auth CASCADE;
DROP FUNCTION IF EXISTS delete_user_from_auth CASCADE;

-- Función para generar hash de contraseña con salt
CREATE OR REPLACE FUNCTION hash_password_with_salt(
    p_password TEXT
) RETURNS TEXT AS $$
DECLARE
    salt TEXT;
    hashed_password TEXT;
BEGIN
    salt := gen_salt('bf');
    hashed_password := crypt(p_password, salt);
    RETURN hashed_password;
END;
$$ LANGUAGE plpgsql;

-- Función para manejar el trigger de hashing de contraseñas
CREATE OR REPLACE FUNCTION trg_hash_password()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.contrasena IS DISTINCT FROM OLD.contrasena THEN
        NEW.contrasena := hash_password_with_salt(NEW.contrasena);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Crear trigger para el hash de contraseñas
CREATE TRIGGER hash_password_trigger
BEFORE INSERT OR UPDATE ON usuarios
FOR EACH ROW
EXECUTE FUNCTION trg_hash_password();

-- Función para crear un usuario en auth.users utilizando la contraseña hasheada de usuarios
CREATE OR REPLACE FUNCTION create_user(
    email TEXT,
    hashed_password TEXT
) RETURNS void AS $$
DECLARE
    user_id UUID;
BEGIN
    user_id := gen_random_uuid();

    INSERT INTO auth.users
        (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, recovery_sent_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, created_at, updated_at, confirmation_token, email_change, email_change_token_new, recovery_token)
    VALUES
        ('00000000-0000-0000-0000-000000000000', user_id, 'authenticated', 'authenticated', email, hashed_password, now(), now(), now(), '{"provider":"email","providers":["email"]}', '{}', now(), now(), '', '', '', '');

    INSERT INTO auth.identities (id, user_id, provider_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
    VALUES
        (gen_random_uuid(), user_id, user_id, format('{"sub":"%s","email":"%s"}', user_id::text, email)::jsonb, 'email', now(), now(), now());
END;
$$ LANGUAGE plpgsql;

-- Función para actualizar un usuario en auth.users
CREATE OR REPLACE FUNCTION update_user_in_auth(
    old_email TEXT,
    new_email TEXT,
    password TEXT
) RETURNS void AS $$
BEGIN
    IF password IS NOT NULL THEN
        UPDATE auth.users
        SET email = new_email, encrypted_password = password
        WHERE email = old_email;
    ELSE
        UPDATE auth.users
        SET email = new_email
        WHERE email = old_email;
    END IF;

    UPDATE auth.identities
    SET identity_data = jsonb_set(identity_data, '{email}', to_jsonb(new_email))
    WHERE identity_data->>'email' = old_email;
END;
$$ LANGUAGE plpgsql;

-- Función para eliminar un usuario en auth.users
CREATE OR REPLACE FUNCTION delete_user_from_auth(
    p_email TEXT
) RETURNS void AS $$
BEGIN
    DELETE FROM auth.users WHERE email = p_email;
    DELETE FROM auth.identities WHERE identity_data->>'email' = p_email;
END;
$$ LANGUAGE plpgsql;

-- Función trigger que llamará a create_user
CREATE OR REPLACE FUNCTION trg_create_auth_user()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM create_user(NEW.email, NEW.contrasena);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Función trigger que llamará a update_user_in_auth
CREATE OR REPLACE FUNCTION trg_update_auth_user()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM update_user_in_auth(OLD.email, NEW.email, NEW.contrasena);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Función trigger que llamará a delete_user_from_auth
CREATE OR REPLACE FUNCTION trg_delete_auth_user()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM delete_user_from_auth(OLD.email);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- Crear o reemplazar el trigger para llamar a la función trg_create_auth_user
DROP TRIGGER IF EXISTS after_user_insert ON usuarios;
CREATE TRIGGER after_user_insert
AFTER INSERT ON usuarios
FOR EACH ROW
EXECUTE FUNCTION trg_create_auth_user();

-- Crear o reemplazar el trigger para llamar a la función trg_update_auth_user
DROP TRIGGER IF EXISTS after_user_update ON usuarios;
CREATE TRIGGER after_user_update
AFTER UPDATE ON usuarios
FOR EACH ROW
EXECUTE FUNCTION trg_update_auth_user();

-- Crear o reemplazar el trigger para llamar a la función trg_delete_auth_user
DROP TRIGGER IF EXISTS after_user_delete ON usuarios;
CREATE TRIGGER after_user_delete
AFTER DELETE ON usuarios
FOR EACH ROW
EXECUTE FUNCTION trg_delete_auth_user();

-- Prueba de Inserción
INSERT INTO usuarios (email, nombre, direccion, telefono, fecha_nacimiento, tipo_perfil, iglesia_id, contrasena)
VALUES ('[email protected]', 'Test User', 'Test Adress', '987654321', '1995-06-15', 'Maestro Titular', (SELECT id FROM iglesia LIMIT 1), 'test123');

-- Verificar que el usuario se ha insertado en auth.users
SELECT * FROM auth.users WHERE email = '[email protected]';

-- Verificar que el usuario se ha insertado en la tabla usuarios
SELECT * FROM usuarios WHERE email = '[email protected]';

-- Prueba de Actualización
UPDATE usuarios
SET email = '[email protected]', contrasena = 'newpassword456545454'
WHERE email = '[email protected]';

-- Verificar que los cambios se han reflejado en auth.users
SELECT id, email, encrypted_password FROM auth.users WHERE email = '[email protected]';

-- Verificar que los cambios se han reflejado en la tabla usuarios
SELECT id, email, nombre, contrasena FROM usuarios WHERE email = '[email protected]';

-- Prueba de Eliminación
DELETE FROM usuarios WHERE email = '[email protected]';

-- Verificar que el usuario se ha eliminado de auth.users
SELECT * FROM auth.users WHERE email = '[email protected]';

-- Verificar que el usuario se ha eliminado de la tabla usuarios
SELECT * FROM usuarios WHERE email = '[email protected]';


-- Eliminar la función existente si ya existe
DROP FUNCTION IF EXISTS get_user_by_email(TEXT) CASCADE;

-- Crear una función para obtener información del usuario por email
CREATE OR REPLACE FUNCTION get_user_by_email(p_email TEXT)
RETURNS TABLE (
    id UUID,
    email VARCHAR(100),
    nombre VARCHAR(100),
    direccion VARCHAR(255),
    telefono VARCHAR(20),
    fecha_nacimiento DATE,
    tipo_perfil tipo_perfil,
    iglesia_id UUID,
    contrasena VARCHAR(255),
    fecha_creacion TIMESTAMP WITH TIME ZONE,
    habilitada BOOLEAN
) AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.email, u.nombre, u.direccion, u.telefono, u.fecha_nacimiento, u.tipo_perfil, u.iglesia_id, u.contrasena, u.fecha_creacion, u.habilitada
    FROM usuarios u
    WHERE u.email = p_email;
END;
$$ LANGUAGE plpgsql;

-- Ejemplo de uso de la función para obtener información del usuario con email '[email protected]'
SELECT * FROM get_user_by_email('[email protected]');

-- Crear una política que permite a los usuarios ver solo sus propias filas
CREATE POLICY select_own_user_data
ON usuarios
FOR SELECT
USING (email = current_user_email());  -- Aquí se debe definir cómo se obtiene el email del usuario autenticado