Common Security Mistakes to Avoid

---

Learning from Others' Mistakes

Security incidents often stem from predictable mistakes. By understanding common vulnerabilities, you can avoid them in your own applications.

---

Mistake 1: Forgetting to Enable RLS

The most common and dangerous mistake:

-- Table created without RLS
CREATE TABLE user_secrets (
  id uuid PRIMARY KEY,
  user_id uuid REFERENCES auth.users(id),
  secret_data text
);

-- Without RLS enabled, ANYONE can query this table!
SELECT * FROM user_secrets;  -- Returns everything

The Fix

-- Always enable RLS immediately after creating a table
CREATE TABLE user_secrets (...);
ALTER TABLE user_secrets ENABLE ROW LEVEL SECURITY;

-- Now the table is inaccessible until policies are added

Prevention Habit

Make it a checklist item:

1. CREATE TABLE
2. ALTER TABLE ... ENABLE ROW LEVEL SECURITY
3. CREATE POLICY ...

---

Mistake 2: Trusting Client-Provided IDs

// WRONG: Client sends user_id
const { error } = await supabase
  .from('posts')
  .insert({
    user_id: body.userId,  // Attacker can send ANY user_id!
    title: body.title
  })

The Fix

Let RLS enforce ownership:

CREATE POLICY "Users can only insert own posts"
  ON posts FOR INSERT
  WITH CHECK (user_id = auth.uid());

// RIGHT: Client doesn't send user_id, it's from auth
const { error } = await supabase
  .from('posts')
  .insert({
    user_id: user.id,  // Or let database default handle it
    title: body.title
  })

---

Mistake 3: Overly Permissive Policies

-- WRONG: Too broad
CREATE POLICY "Allow all authenticated users"
  ON sensitive_data FOR ALL
  USING (auth.role() = 'authenticated');

-- This lets ANY logged-in user read/write ALL data!

The Fix

Be specific about who can access what:

-- RIGHT: Only owners access their data
CREATE POLICY "Users access own data"
  ON sensitive_data FOR ALL
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

---

Mistake 4: Missing WITH CHECK on Updates

-- WRONG: Missing WITH CHECK
CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (user_id = auth.uid());
  -- No WITH CHECK!

-- This allows:
UPDATE posts SET user_id = 'other-user-id' WHERE id = 'my-post';
-- User can transfer ownership or steal others' posts!

The Fix

-- RIGHT: WITH CHECK prevents changing ownership
CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

---

Mistake 5: Exposing Service Role Key

// CATASTROPHIC: Service key in frontend
const supabase = createClient(
  url,
  'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...'  // service_role key!
)

// Now anyone can:
// - Read ALL data (bypasses RLS)
// - Modify ALL data
// - Delete ALL data
// - Access auth.users table directly

The Fix

• Only use anon key in frontend
• service_role only in secure server environments
• Audit your codebase: grep -r "service_role" .

---

Mistake 6: Not Validating Input Types

-- WRONG: No type validation
CREATE TABLE orders (
  quantity text,  -- Should be integer!
  price text      -- Should be numeric!
);

-- User could insert: quantity = 'DROP TABLE orders;--'

The Fix

-- RIGHT: Proper types and constraints
CREATE TABLE orders (
  quantity integer NOT NULL CHECK (quantity > 0),
  price numeric(10,2) NOT NULL CHECK (price >= 0)
);

---

Mistake 7: Insecure Direct Object References (IDOR)

// WRONG: No ownership check
async function deletePost(postId) {
  // Any user can delete any post by guessing IDs!
  await supabase.from('posts').delete().eq('id', postId)
}

The Fix

RLS ensures ownership:

CREATE POLICY "Users can only delete own posts"
  ON posts FOR DELETE
  USING (user_id = auth.uid());

Now even if someone guesses a post ID, they can only delete it if they own it.

---

Mistake 8: Storing Secrets in the Database

-- WRONG: Plain text secrets
CREATE TABLE api_integrations (
  user_id uuid,
  api_key text,  -- Plain text!
  api_secret text  -- Plain text!
);

The Fix

Encrypt sensitive data:

-- RIGHT: Encrypted secrets
CREATE EXTENSION pgcrypto;

CREATE TABLE api_integrations (
  user_id uuid,
  api_key_encrypted bytea,
  api_secret_encrypted bytea
);

-- Encrypt on insert
INSERT INTO api_integrations (user_id, api_key_encrypted)
VALUES (
  auth.uid(),
  pgp_sym_encrypt('actual-api-key', current_setting('app.encryption_key'))
);

Or better: use a secrets manager and store references.

---

Mistake 9: Not Rate Limiting

// WRONG: No rate limiting on sensitive operations
async function forgotPassword(email) {
  // Attacker can spam this endpoint to:
  // 1. Drain your email quota
  // 2. Harass users with emails
  // 3. Enumerate valid emails
  await supabase.auth.resetPasswordForEmail(email)
}

The Fix

Implement rate limiting at application or infrastructure level:

// Edge Function with rate limiting
import { RateLimiter } from './rate-limiter'

const limiter = new RateLimiter({ points: 3, duration: 60 })

serve(async (req) => {
  const ip = req.headers.get('x-forwarded-for')

  try {
    await limiter.consume(ip)
  } catch {
    return new Response('Too many requests', { status: 429 })
  }

  // Process request...
})

---

Mistake 10: Leaking Information in Errors

// WRONG: Detailed error messages
if (!user) {
  return res.status(401).json({
    error: 'User not found with email: ' + email
  })
}

if (!bcrypt.compare(password, user.password_hash)) {
  return res.status(401).json({
    error: 'Invalid password for user: ' + email
  })
}

// Attacker now knows:
// - Which emails exist
// - That the email exists but password is wrong

The Fix

// RIGHT: Generic error messages
if (!user || !bcrypt.compare(password, user.password_hash)) {
  return res.status(401).json({
    error: 'Invalid email or password'
  })
}

---

Security Checklist

Before deploying, verify:

• RLS enabled on all tables
• No service_role key in frontend code
• WITH CHECK on all UPDATE/INSERT policies
• Sensitive data encrypted
• Input validation at database level
• Rate limiting on sensitive endpoints
• Generic error messages
• No hardcoded secrets in code
• Environment variables properly configured

---

Key Takeaways

1. Enable RLS on every table: No exceptions
2. Never trust client input: Validate and constrain
3. Use WITH CHECK: Prevent privilege escalation
4. Keep service_role secret: Only server-side
5. Encrypt sensitive data: Never store plain text secrets
6. Rate limit sensitive operations: Prevent abuse
7. Use generic errors: Don't leak information

---

Next Steps

With common mistakes understood, we'll provide a comprehensive production security checklist.

---

Security bugs are often boring—the same mistakes made by different people. The good news? You can avoid most of them by following established patterns and checklists.