# SupaSession

> Advanced session management for Supabase

Limit and restrict user sessions in Supabase Auth, _without the Pro Plan_.

This is supposed to be an alternative to Supabase Pro's [single-session-per-user](https://supabase.com/docs/guides/auth/sessions#limiting-session-lifetime-and-number-of-allowed-sessions-per-user) feature, but more flexible and for the brokies.

![banner](https://raw.githubusercontent.com/Snehil-Shah/supasession/main/assets/banner.png)

## Prerequisites

- Supabase with Auth enabled

## Installation

Install via [database.dev](https://database.dev/Snehil_Shah/supasession):

```sql
SELECT dbdev.install('Snehil_Shah@supasession');
```

To learn how to install `dbdev` and published TLE extensions, read [here](https://supabase.github.io/dbdev/install-in-db-client/).

> [!WARNING]
> This extension is installed in the `supasession` schema and can potentially cause namespace collisions if you already had one before.

## Usage

```sql
CREATE EXTENSION "Snehil_Shah@supasession";
```

### Quick start

```sql
-- Enable session limits:
SELECT supasession.enable();

-- Set limits:
SELECT supasession.set_config(max_sessions := 3);
```



### Types

#### supasession.enforcement_strategy

Represents the strategy for enforcing session limits.

- **dequeue** - Destroys the oldest session when the limit is reached
- **reject** - Rejects any new sessions when the limit is reached

### Tables

#### supasession.config

Extension configuration.

- **enabled** (`BOOLEAN`) - Whether session limiting is enabled (Default: `FALSE`)
- **max_sessions** (`INTEGER`): Maximum number of active sessions allowed per user (Default: `1`)
- **strategy** ([`supasession.enforcement_strategy`](#supasessionenforcement_strategy)): Enforcement strategy when the session limit is reached (Default: `dequeue`)

### Functions

These functions provide a convenient layer on top of [`supasession.config`](#supasessionconfig) to manage extension configuration.
Alternatively, you can always directly query/update the [`supasession.config`](#supasessionconfig) table.

#### supasession.enable()

Enables session limits enforcement.

```sql
SELECT supasession.enable();
```

##### Returns:
  - `VOID`

#### supasession.disable()

Disables session limits enforcement.

```sql
SELECT supasession.disable();
```

##### Returns:
  - `VOID`

#### supasession.set_config( [enabled BOOLEAN], [max_sessions INTEGER], [strategy supasession.enforcement_strategy] )

Updates extension configuration.

```sql
SELECT supasession.set_config(max_sessions := 5);
SELECT supasession.set_config(enabled := FALSE, strategy := 'reject');
```

##### Parameters:
  - **enabled** (`BOOLEAN`, *optional*) - Whether session limiting is enabled
  - **max_sessions** (`INTEGER`, *optional*) - Maximum number of active sessions allowed per user
  - **strategy** ([`supasession.enforcement_strategy`](#supasessionenforcement_strategy), *optional*) - Enforcement strategy when the session limit is reached

##### Returns:
  - [`supasession.config`](#supasessionconfig) - Updated configuration

#### supasession.get_config()

Retrieves extension configuration.

```sql
SELECT supasession.get_config();
```

##### Returns:
  - [`supasession.config`](#supasessionconfig) - Current configuration

### Auth helpers

Helper functions to work with sessions within [RPCs](https://docs.postgrest.org/en/stable/references/api/functions.html).

#### supasession.sid()

Returns the session ID from the JWT of the current request. (Analogous to `auth.uid()`)

```sql
SELECT supasession.sid() AS session_id;
```

##### Returns:
  - `UUID|NULL` - The session ID (`auth.sessions.id`), or `NULL` if not available



## Caveats

Some things to be aware of:

- This extension intercepts the session-creation flow and not token-refreshes. This means **once you enable it or change any configuration, they will only be enforced for a user after that user signs in again**. Until then, all existing sessions of said user enjoy their time even if they don't meet the set limits.
  
  To avoid this, it's **recommended** to destroy all existing sessions and force all users to sign-in again fresh, **if you wish to enforce the updated extension configuration right away**. To do this: run `DELETE FROM auth.sessions`. Don't be scared of the warnings, it only deletes temporary session data to invalidate all existing user sessions and make them sign-in again.

- Just like Supabase's single-session-per-user, invalid users **won't be logged out immediately** when a new sign in occurs. A session stays active throughout the lifetime of the JWT access token and can only be invalidated once it expires. You can reduce the JWT expiry time to go against this, but Supabase doesn't recommend going below 5 minutes. To change the JWT expiration time, go to **Project Settings > JWT Keys** in your project dashboard, or set it under [`auth.jwt_expiry`](https://supabase.com/docs/guides/local-development/cli/config#auth.jwt_expiry) in `config.toml` for local instances.
- This extension can overestimate the number of valid sessions in certain cases where sessions are invalid due to Low AALs or timeouts. This is due to such configurations (mostly Pro features) being defined in the API layer which this extension doesn't have access to. Such sessions are still cleaned up by Supabase eventually, but during that window, blocking [enforcement strategies](#supasessionenforcement_strategy) like `reject` can cause your app to reject valid login requests.

  **TL;DR**: If you are using Pro Auth features like Time-boxes and inactivity timeouts, it's **recommended to use non-blocking enforcement strategies** like `dequeue`.

- As all strategies are enforced at the database level, you won't see the prettiest of error messages for blocking strategies like `reject`, requiring special error handling by the client.

***

# pg_dispatch

> A [TLE](https://github.com/aws/pg_tle)-compliant alternative to [pg_later](https://github.com/ChuckHend/pg_later).

An asynchronous task dispatcher for PostgreSQL that helps unblock your main transaction by offloading heavy SQL as deferrable jobs, allowing you to run SQL queries asynchronously.

This is meant to be a [TLE](https://github.com/aws/pg_tle)-compliant alternative to [pg_later](https://github.com/ChuckHend/pg_later) built on top of [pg_cron](https://github.com/citusdata/pg_cron), which means you can easily use it in sandboxed environments like Supabase and AWS RDS.

## Use cases

This extension is particularly useful when writing database-native server-side logic (in something like PL/pgSQL) and wanting to dispatch **_side-effects_** asynchronously in a separate transaction.

Say you have an `AFTER INSERT` trigger on a user profiles table that is called every time a new user hops in by calling an [RPC (remote procedure call)](https://docs.postgrest.org/en/v12/references/api/functions.html).
You can offload the bulky and asynchronous **_side-effects_** (written as PostgreSQL functions), such as sending notifications to other users or updating large tables storing analytics, thereby unblocking your main RPC, for which btw, the client is still waiting for a response from.

## Prerequisites

- `PostgreSQL` >= v13
- `pg_cron` >= v1.5
- `pgcrypto`

## Installation

Install via [database.dev](https://database.dev/Snehil_Shah/pg_dispatch):

```sql
SELECT dbdev.install(Snehil_Shah@pg_dispatch);
```

To learn how to install `dbdev` and published TLE extensions, read [here](https://supabase.github.io/dbdev/install-in-db-client/).

> [!WARNING]
> This extension is installed in the `pgdispatch` schema and can potentially cause namespace collisions if you already had one before.

You can also manually install the distribution from PGXN [here](https://pgxn.org/dist/pg_dispatch/).

## Usage

```sql
CREATE EXTENSION "Snehil_Shah@pg_dispatch";
```



### pgdispatch.fire( command TEXT )

Dispatches an SQL command for asynchronous execution.

```sql
SELECT pgdispatch.fire('SELECT pg_sleep(40);');
```

#### Parameters
  - **command** (`TEXT`) - The SQL statement to dispatch

#### Returns:
  - `VOID`

### pgdispatch.snooze( command TEXT, delay INTERVAL )

Dispatches a delayed SQL command for asynchronous execution.

```sql
SELECT pgdispatch.snooze('SELECT pg_sleep(20);', '20 seconds');
```

**Note**: The delay is scheduled asynchronously and will not block your main transaction.

#### Parameters:
  - **command** (`TEXT`) - The SQL statement to dispatch
  - **delay** (`INTERVAL`) - How long to delay the execution (truncates to seconds precision)

#### Returns:
  - `VOID`



***

Snehil_Shah

Packages