Postgres Authentication for Apps: A Practical Guide

When people search for Postgres authentication, they usually mean one of two different things. One is how a client proves who it is to the database server: roles, passwords and pg_hba.conf. The other, and the one this guide is about, is how the people using your app sign up, sign in and stay signed in, with their data stored in Postgres. Database roles are the wrong tool for that job. Instead, you put an auth layer in front of the database that issues tokens, and you tie rows to the user ID inside them.

This guide walks through the options and then adds working authentication to an app with WoWSQL's TypeScript SDK.

Key facts

• Database roles are not app accounts. App users live in auth tables and carry a signed token (JWT). The app never sees database credentials.
• WoWSQL auth endpoints: https://<slug>.wowsqlconnect.com/auth/v1 (also reachable as /api/auth), called with the project's anon key.
• Sign-in methods in the docs (checked 25 October 2026): email and password, magic links, email or phone OTP, OAuth with 19 working providers, TOTP two-factor and optional Cloudflare Turnstile CAPTCHA.
• SDK: @wowsql/sdk 3.10.0 ships ProjectAuthClient next to new WowSQLClient({ projectUrl, apiKey }).table(...).
• Plans: authentication is on every plan. Free covers 10,000 monthly active users; Pro is $25 a month and Scale is $99 a month.
• Regions: Mumbai (the default) and US East 1.

Three ways to add authentication to a Postgres app

1. Build it yourself. Create a users table, hash passwords with bcrypt or Argon2, issue sessions or JWTs from your API server, and write the email flows for verification and password resets. You get full control, and you own every security detail: rate limiting, token rotation, reset-link expiry, OAuth state checks and 2FA.

2. Use an auth library inside your own server. Libraries for Express, FastAPI, Django or Next.js handle sessions and OAuth for you, and store users in your Postgres database through an adapter. This fits teams that already run an API server.

3. Use a backend that includes auth. A Postgres backend-as-a-service gives you a managed database, a REST API over it and an auth service that issues tokens for the same project. Your frontend or mobile app calls auth and data APIs directly with a public key. WoWSQL works this way, and so do other Postgres platforms.

Step 1: Turn on authentication for the project

Create a project at app.wowsql.com and pick Mumbai or US East 1. In the dashboard, open Authentication and click Enable Authentication. The setup asks for a JWT secret, which should be a random string of at least 32 characters, and creates the auth tables. You can add OAuth providers on the same screen with a client ID, client secret, redirect URI and scopes. The setting up authentication guide has screenshots.

Copy the project slug and the anon key (wowsql_anon_…). The anon key is safe in browser and mobile code. The service key (wowsql_service_…) is for servers only.

Step 2: Sign users up and in

// lib/auth.ts
import { ProjectAuthClient } from '@wowsql/sdk';

export const auth = new ProjectAuthClient({
  projectUrl: process.env.NEXT_PUBLIC_WOWSQL_PROJECT!, // e.g. "myproject"
  apiKey: process.env.NEXT_PUBLIC_WOWSQL_ANON_KEY!,    // anon key only
});

// Sign up
const { user } = await auth.signUp({
  email: 'asha@example.com',
  password: 'a-long-unique-password',
  fullName: 'Asha',
});

// Sign in
const result = await auth.signIn({
  email: 'asha@example.com',
  password: 'a-long-unique-password',
});

A successful sign-in returns user and a session with an access token, a refresh token and expiresIn. Token lifetime follows the project's JWT expiry setting, which defaults to 24 hours. Use auth.getUser() to read the signed-in user, auth.refreshSession() to renew tokens and auth.logout() to sign out.

By default the client keeps tokens in memory. To keep users signed in after a reload or an app restart, pass a storage object with getAccessToken, setAccessToken, getRefreshToken and setRefreshToken. Our React Native Postgres backend guide shows that pattern with AsyncStorage.

Step 3: Add passwordless and social sign-in

The SDK also wraps the passwordless and OAuth flows:

// Magic link by email
await auth.sendMagicLink('asha@example.com');

// One-time code by email or SMS
await auth.sendOtp({ phone: '+919876543210', purpose: 'login' });
await auth.verifyOtp({ phone: '+919876543210', otp: '123456' });

// Google, GitHub and others
const { authorizationUrl } = await auth.getOAuthAuthorizationUrl(
  'google',
  'https://yourapp.com/auth/callback',
);
window.location.href = authorizationUrl;

Phone numbers use E.164 format. For OAuth, configure the provider in the dashboard first, or the request returns 400. After the provider signs the user in, WoWSQL redirects to your callback URL with the tokens in the query string. Read them there, store them and remove them from the address bar. The OAuth for projects docs list the 20 provider values in the dashboard. keycloak can be saved but isn't usable for sign-in yet, and X uses the twitter value.

Step 4: Protect accounts with 2FA and CAPTCHA

TOTP two-factor works with apps like Google Authenticator, 1Password and Authy. A signed-in user calls auth.setupTotp() to get a secret and QR code, then auth.enableTotp(code) to switch it on. Enabling returns eight single-use backup codes, shown only once.

When a user with 2FA signs in, signIn doesn't return tokens. It returns requires2fa: true and a pendingToken that expires after 10 minutes:

const res = await auth.signIn({ email, password });
if (res.requires2fa && res.pendingToken) {
  const done = await auth.verifyTwoFactorLogin(res.pendingToken, { code: totpCode });
  // done.session now holds the tokens
}

To slow down bots and credential stuffing, turn on Cloudflare Turnstile under Authentication → Attack Protection with your own site key and secret. It's off by default. Once it's on, pass captchaToken to signUp, signIn and the other public auth calls. We covered the setup in Cloudflare Turnstile CAPTCHA in WoWSQL Auth.

Step 5: Connect users to their rows

Authentication tells you who the user is. Authorization decides which rows they can touch. A simple, reliable pattern is to store the auth user ID on every row the user owns:

create table public.projects (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null,
  name text not null,
  created_at timestamptz not null default now()
);

In Postgres, Row Level Security (RLS) can enforce "owner only" rules inside the database. WoWSQL's self-hosted edition ships auth.uid(), auth.role() and auth.jwt() helpers for writing those policies. On the hosted SDK, version 3.10.0 sends the project API key on table requests, and the docs don't yet show a table query running as the signed-in user. Until they do, send owner-scoped writes through a small server route. The route checks the user's access token with auth.getUser(token), then reads or writes with the service key and filters on owner_id. Our Next.js Postgres backend guide shows where that route lives in an App Router project.

Either way, take the user ID from the verified token, never from the request body.

Common mistakes

• Shipping the service key. Anything in a browser bundle or mobile app can be extracted, and the service key bypasses RLS.
• Using one Postgres role per user. It doesn't scale and mixes database administration with app logins.
• Leaving OAuth tokens in the URL. Clear the query string after you store them, so they don't end up in history or analytics.

WoWSQL is a Postgres backend built in India, DPIIT-recognised and part of NVIDIA Inception. For the data side of the same project, see our instant REST API for Postgres guide.

FAQ

Can I use Postgres roles to log in my app's users?

You can, but you shouldn't. Roles are meant for services and administrators. App users belong in auth tables, and they carry a signed token that your API or backend checks on each request.

What sign-in methods does WoWSQL support?

Email and password, magic links, one-time codes by email or SMS, OAuth with 19 working providers including Google, GitHub, Apple and Microsoft, and TOTP two-factor. You can also require a Cloudflare Turnstile CAPTCHA.

Is authentication included on the free plan?

Yes. The free plan includes authentication for up to 10,000 monthly active users. Pro ($25 a month) includes 100,000 and Scale ($99 a month) includes 2,000,000, with extra active users billed per user.

How long do WoWSQL access tokens last?

They follow the project's JWT expiry setting, which defaults to 24 hours. Use the refresh token with refreshSession() to get a new access token without asking the user to sign in again.

How do I make sure users only see their own data?

Store the auth user ID on each row and filter on it. In the self-hosted edition you can enforce this with RLS helpers such as auth.uid(). On the hosted SDK today, route owner-scoped reads and writes through a server endpoint that verifies the user's token first.