Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Getting Started

Build a User Management App with Flutter

This tutorial demonstrates how to build a basic user management app. The app authenticates and identifies the user, stores their profile information in the database, and allows the user to log in, update their profile details, and upload a profile photo. The app uses:

  • Supabase Database - a Postgres database for storing your user data and Row Level Security so data is protected and users can only access their own information.
  • Supabase Auth - users log in through magic links sent to their email (without having to set up passwords).
  • Supabase Storage - users can upload a profile photo.

Supabase User Management example

Project setup

Before we start building we're going to set up our Database and API. This is as simple as starting a new Project in Supabase and then creating a "schema" inside the database.

Create a project

  1. Create a new project in the Supabase Dashboard.
  2. Enter your project details.
  3. Wait for the new database to launch.

Set up the database schema

Now we are going to set up the database schema. We can use the "User Management Starter" quickstart in the SQL Editor, or you can just copy/paste the SQL from below and run it yourself.

  1. Go to the SQL Editor page in the Dashboard.
  2. Click User Management Starter.
  3. Click Run.

_10
supabase link --project-ref <project-id>
_10
# You can get <project-id> from your project's dashboard URL: https://supabase.com/dashboard/project/<project-id>
_10
supabase db pull

Get the API Keys

Now that you've created some database tables, you are ready to insert data using the auto-generated API. We just need to get the Project URL and anon key from the API settings.

  1. Go to the API Settings page in the Dashboard.
  2. Find your Project URL, anon, and service_role keys on this page.

Building the app

Let's start building the Flutter app from scratch.

Initialize a Flutter app

We can use flutter create to initialize an app called supabase_quickstart:


_10
flutter create supabase_quickstart

Then let's install the only additional dependency: supabase_flutter

Copy and paste the following line in your pubspec.yaml to install the package:


_10
supabase_flutter: ^2.0.0

Run flutter pub get to install the dependencies.

Now that we have the dependencies installed let's setup deep links. Setting up deep links is required to bring back the user to the app when they click on the magic link to sign in. We can setup deep links with just a minor tweak on our Flutter application.

We have to use io.supabase.flutterquickstart as the scheme. In this example, we will use login-callback as the host for our deep link, but you can change it to whatever you would like.

First, add io.supabase.flutterquickstart://login-callback/ as a new redirect URL in the Dashboard.

Supabase console deep link setting

That is it on Supabase's end and the rest are platform specific settings:

Edit the ios/Runner/Info.plist file.

Add CFBundleURLTypes to enable deep linking:

ios/Runner/Info.plist"

_20
<!-- ... other tags -->
_20
<plist>
_20
<dict>
_20
<!-- ... other tags -->
_20
_20
<!-- Add this array for Deep Links -->
_20
<key>CFBundleURLTypes</key>
_20
<array>
_20
<dict>
_20
<key>CFBundleTypeRole</key>
_20
<string>Editor</string>
_20
<key>CFBundleURLSchemes</key>
_20
<array>
_20
<string>io.supabase.flutterquickstart</string>
_20
</array>
_20
</dict>
_20
</array>
_20
<!-- ... other tags -->
_20
</dict>
_20
</plist>

Main function

Now that we have deep links ready let's initialize the Supabase client inside our main function with the API credentials that you copied earlier. These variables will be exposed on the app, and that's completely fine since we have Row Level Security enabled on our Database.

lib/main.dart

_10
Future<void> main() async {
_10
await Supabase.initialize(
_10
url: 'YOUR_SUPABASE_URL',
_10
anonKey: 'YOUR_SUPABASE_ANON_KEY',
_10
);
_10
runApp(MyApp());
_10
}
_10
_10
final supabase = Supabase.instance.client;

Set up splash screen

Let's create a splash screen that will be shown to users right after they open the app. This screen retrieves the current session and redirects the user accordingly.

lib/pages/splash_page.dart

_38
import 'package:flutter/material.dart';
_38
import 'package:supabase_quickstart/main.dart';
_38
_38
class SplashPage extends StatefulWidget {
_38
const SplashPage({super.key});
_38
_38
@override
_38
_SplashPageState createState() => _SplashPageState();
_38
}
_38
_38
class _SplashPageState extends State<SplashPage> {
_38
@override
_38
void initState() {
_38
super.initState();
_38
_redirect();
_38
}
_38
_38
Future<void> _redirect() async {
_38
await Future.delayed(Duration.zero);
_38
if (!mounted) {
_38
return;
_38
}
_38
_38
final session = supabase.auth.currentSession;
_38
if (session != null) {
_38
Navigator.of(context).pushReplacementNamed('/account');
_38
} else {
_38
Navigator.of(context).pushReplacementNamed('/login');
_38
}
_38
}
_38
_38
@override
_38
Widget build(BuildContext context) {
_38
return const Scaffold(
_38
body: Center(child: CircularProgressIndicator()),
_38
);
_38
}
_38
}

Set up a login page

Let's create a Flutter widget to manage logins and sign ups. We'll use Magic Links, so users can sign in with their email without using passwords. Notice that this page sets up a listener on the user's auth state using onAuthStateChange. A new event will fire when the user comes back to the app by clicking their magic link, which this page can catch and redirect the user accordingly.

lib/pages/login_page.dart

_98
import 'dart:async';
_98
_98
import 'package:flutter/foundation.dart';
_98
import 'package:flutter/material.dart';
_98
import 'package:supabase_flutter/supabase_flutter.dart';
_98
import 'package:supabase_quickstart/main.dart';
_98
_98
class LoginPage extends StatefulWidget {
_98
const LoginPage({super.key});
_98
_98
@override
_98
_LoginPageState createState() => _LoginPageState();
_98
}
_98
_98
class _LoginPageState extends State<LoginPage> {
_98
bool _isLoading = false;
_98
bool _redirecting = false;
_98
late final TextEditingController _emailController = TextEditingController();
_98
late final StreamSubscription<AuthState> _authStateSubscription;
_98
_98
Future<void> _signIn() async {
_98
try {
_98
setState(() {
_98
_isLoading = true;
_98
});
_98
await supabase.auth.signInWithOtp(
_98
email: _emailController.text.trim(),
_98
emailRedirectTo:
_98
kIsWeb ? null : 'io.supabase.flutterquickstart://login-callback/',
_98
);
_98
if (mounted) {
_98
ScaffoldMessenger.of(context).showSnackBar(
_98
const SnackBar(content: Text('Check your email for a login link!')),
_98
);
_98
_emailController.clear();
_98
}
_98
} on AuthException catch (error) {
_98
SnackBar(
_98
content: Text(error.message),
_98
backgroundColor: Theme.of(context).colorScheme.error,
_98
);
_98
} catch (error) {
_98
SnackBar(
_98
content: const Text('Unexpected error occurred'),
_98
backgroundColor: Theme.of(context).colorScheme.error,
_98
);
_98
} finally {
_98
if (mounted) {
_98
setState(() {
_98
_isLoading = false;
_98
});
_98
}
_98
}
_98
}
_98
_98
@override
_98
void initState() {
_98
_authStateSubscription = supabase.auth.onAuthStateChange.listen((data) {
_98
if (_redirecting) return;
_98
final session = data.session;
_98
if (session != null) {
_98
_redirecting = true;
_98
Navigator.of(context).pushReplacementNamed('/account');
_98
}
_98
});
_98
super.initState();
_98
}
_98
_98
@override
_98
void dispose() {
_98
_emailController.dispose();
_98
_authStateSubscription.cancel();
_98
super.dispose();
_98
}
_98
_98
@override
_98
Widget build(BuildContext context) {
_98
return Scaffold(
_98
appBar: AppBar(title: const Text('Sign In')),
_98
body: ListView(
_98
padding: const EdgeInsets.symmetric(vertical: 18, horizontal: 12),
_98
children: [
_98
const Text('Sign in via the magic link with your email below'),
_98
const SizedBox(height: 18),
_98
TextFormField(
_98
controller: _emailController,
_98
decoration: const InputDecoration(labelText: 'Email'),
_98
),
_98
const SizedBox(height: 18),
_98
ElevatedButton(
_98
onPressed: _isLoading ? null : _signIn,
_98
child: Text(_isLoading ? 'Loading' : 'Send Magic Link'),
_98
),
_98
],
_98
),
_98
);
_98
}
_98
}

Set up account page

After a user is signed in we can allow them to edit their profile details and manage their account. Let's create a new widget called account_page.dart for that.

lib/pages/account_page.dart"

_151
import 'package:flutter/material.dart';
_151
import 'package:supabase_flutter/supabase_flutter.dart';
_151
import 'package:supabase_quickstart/main.dart';
_151
_151
class AccountPage extends StatefulWidget {
_151
const AccountPage({super.key});
_151
_151
@override
_151
_AccountPageState createState() => _AccountPageState();
_151
}
_151
_151
class _AccountPageState extends State<AccountPage> {
_151
final _usernameController = TextEditingController();
_151
final _websiteController = TextEditingController();
_151
_151
var _loading = true;
_151
_151
/// Called once a user id is received within `onAuthenticated()`
_151
Future<void> _getProfile() async {
_151
setState(() {
_151
_loading = true;
_151
});
_151
_151
try {
_151
final userId = supabase.auth.currentUser!.id;
_151
final data =
_151
await supabase.from('profiles').select().eq('id', userId).single();
_151
_usernameController.text = (data['username'] ?? '') as String;
_151
_websiteController.text = (data['website'] ?? '') as String;
_151
} on PostgrestException catch (error) {
_151
SnackBar(
_151
content: Text(error.message),
_151
backgroundColor: Theme.of(context).colorScheme.error,
_151
);
_151
} catch (error) {
_151
SnackBar(
_151
content: const Text('Unexpected error occurred'),
_151
backgroundColor: Theme.of(context).colorScheme.error,
_151
);
_151
} finally {
_151
if (mounted) {
_151
setState(() {
_151
_loading = false;
_151
});
_151
}
_151
}
_151
}
_151
_151
/// Called when user taps `Update` button
_151
Future<void> _updateProfile() async {
_151
setState(() {
_151
_loading = true;
_151
});
_151
final userName = _usernameController.text.trim();
_151
final website = _websiteController.text.trim();
_151
final user = supabase.auth.currentUser;
_151
final updates = {
_151
'id': user!.id,
_151
'username': userName,
_151
'website': website,
_151
'updated_at': DateTime.now().toIso8601String(),
_151
};
_151
try {
_151
await supabase.from('profiles').upsert(updates);
_151
if (mounted) {
_151
const SnackBar(
_151
content: Text('Successfully updated profile!'),
_151
);
_151
}
_151
} on PostgrestException catch (error) {
_151
SnackBar(
_151
content: Text(error.message),
_151
backgroundColor: Theme.of(context).colorScheme.error,
_151
);
_151
} catch (error) {
_151
SnackBar(
_151
content: const Text('Unexpected error occurred'),
_151
backgroundColor: Theme.of(context).colorScheme.error,
_151
);
_151
} finally {
_151
if (mounted) {
_151
setState(() {
_151
_loading = false;
_151
});
_151
}
_151
}
_151
}
_151
_151
Future<void> _signOut() async {
_151
try {
_151
await supabase.auth.signOut();
_151
} on AuthException catch (error) {
_151
SnackBar(
_151
content: Text(error.message),
_151
backgroundColor: Theme.of(context).colorScheme.error,
_151
);
_151
} catch (error) {
_151
SnackBar(
_151
content: const Text('Unexpected error occurred'),
_151
backgroundColor: Theme.of(context).colorScheme.error,
_151
);
_151
} finally {
_151
if (mounted) {
_151
Navigator.of(context).pushReplacementNamed('/login');
_151
}
_151
}
_151
}
_151
_151
@override
_151
void initState() {
_151
super.initState();
_151
_getProfile();
_151
}
_151
_151
@override
_151
void dispose() {
_151
_usernameController.dispose();
_151
_websiteController.dispose();
_151
super.dispose();
_151
}
_151
_151
@override
_151
Widget build(BuildContext context) {
_151
return Scaffold(
_151
appBar: AppBar(title: const Text('Profile')),
_151
body: _loading
_151
? const Center(child: CircularProgressIndicator())
_151
: ListView(
_151
padding: const EdgeInsets.symmetric(vertical: 18, horizontal: 12),
_151
children: [
_151
TextFormField(
_151
controller: _usernameController,
_151
decoration: const InputDecoration(labelText: 'User Name'),
_151
),
_151
const SizedBox(height: 18),
_151
TextFormField(
_151
controller: _websiteController,
_151
decoration: const InputDecoration(labelText: 'Website'),
_151
),
_151
const SizedBox(height: 18),
_151
ElevatedButton(
_151
onPressed: _loading ? null : _updateProfile,
_151
child: Text(_loading ? 'Saving...' : 'Update'),
_151
),
_151
const SizedBox(height: 18),
_151
TextButton(onPressed: _signOut, child: const Text('Sign Out')),
_151
],
_151
),
_151
);
_151
}
_151
}

Launch!

Now that we have all the components in place, let's update lib/main.dart:

lib/main.dart

_44
import 'package:flutter/material.dart';
_44
import 'package:supabase_flutter/supabase_flutter.dart';
_44
import 'package:supabase_quickstart/pages/account_page.dart';
_44
import 'package:supabase_quickstart/pages/login_page.dart';
_44
import 'package:supabase_quickstart/pages/splash_page.dart';
_44
_44
Future<void> main() async {
_44
await Supabase.initialize(
_44
url: 'YOUR_SUPABASE_URL',
_44
anonKey: 'YOUR_SUPABASE_ANON_KEY',
_44
);
_44
runApp(MyApp());
_44
}
_44
_44
final supabase = Supabase.instance.client;
_44
_44
class MyApp extends StatelessWidget {
_44
@override
_44
Widget build(BuildContext context) {
_44
return MaterialApp(
_44
title: 'Supabase Flutter',
_44
theme: ThemeData.dark().copyWith(
_44
primaryColor: Colors.green,
_44
textButtonTheme: TextButtonThemeData(
_44
style: TextButton.styleFrom(
_44
foregroundColor: Colors.green,
_44
),
_44
),
_44
elevatedButtonTheme: ElevatedButtonThemeData(
_44
style: ElevatedButton.styleFrom(
_44
foregroundColor: Colors.white,
_44
backgroundColor: Colors.green,
_44
),
_44
),
_44
),
_44
initialRoute: '/',
_44
routes: <String, WidgetBuilder>{
_44
'/': (_) => const SplashPage(),
_44
'/login': (_) => const LoginPage(),
_44
'/account': (_) => const AccountPage(),
_44
},
_44
);
_44
}
_44
}

Once that's done, run this in a terminal window to launch on Android or iOS:


_10
flutter run

Or for web, run the following command to launch it on localhost:3000


_10
flutter run -d web-server --web-hostname localhost --web-port 3000

And then open the browser to localhost:3000 and you should see the completed app.

Supabase User Management example

Bonus: Profile photos

Every Supabase project is configured with Storage for managing large files like photos and videos.

Making sure we have a public bucket

We will be storing the image as a publicly sharable image. Make sure your avatars bucket is set to public, and if it is not, change the publicity by clicking the dot menu that appears when you hover over the bucket name. You should see an orange Public badge next to your bucket name if your bucket is set to public.

Adding image uploading feature to account page

We will use image_picker plugin to select an image from the device.

Add the following line in your pubspec.yaml file to install image_picker:


_10
image_picker: ^1.0.5

Using image_picker requires some additional preparation depending on the platform. Follow the instruction on README.md of image_picker on how to set it up for the platform you are using.

Once you are done with all of the above, it is time to dive into coding.

Create an upload widget

Let's create an avatar for the user so that they can upload a profile photo. We can start by creating a new component:

lib/components/avatar.dart

_99
import 'package:flutter/material.dart';
_99
import 'package:image_picker/image_picker.dart';
_99
import 'package:supabase_flutter/supabase_flutter.dart';
_99
import 'package:supabase_quickstart/main.dart';
_99
_99
class Avatar extends StatefulWidget {
_99
const Avatar({
_99
super.key,
_99
required this.imageUrl,
_99
required this.onUpload,
_99
});
_99
_99
final String? imageUrl;
_99
final void Function(String) onUpload;
_99
_99
@override
_99
_AvatarState createState() => _AvatarState();
_99
}
_99
_99
class _AvatarState extends State<Avatar> {
_99
bool _isLoading = false;
_99
_99
@override
_99
Widget build(BuildContext context) {
_99
return Column(
_99
children: [
_99
if (widget.imageUrl == null || widget.imageUrl!.isEmpty)
_99
Container(
_99
width: 150,
_99
height: 150,
_99
color: Colors.grey,
_99
child: const Center(
_99
child: Text('No Image'),
_99
),
_99
)
_99
else
_99
Image.network(
_99
widget.imageUrl!,
_99
width: 150,
_99
height: 150,
_99
fit: BoxFit.cover,
_99
),
_99
ElevatedButton(
_99
onPressed: _isLoading ? null : _upload,
_99
child: const Text('Upload'),
_99
),
_99
],
_99
);
_99
}
_99
_99
Future<void> _upload() async {
_99
final picker = ImagePicker();
_99
final imageFile = await picker.pickImage(
_99
source: ImageSource.gallery,
_99
maxWidth: 300,
_99
maxHeight: 300,
_99
);
_99
if (imageFile == null) {
_99
return;
_99
}
_99
setState(() => _isLoading = true);
_99
_99
try {
_99
final bytes = await imageFile.readAsBytes();
_99
final fileExt = imageFile.path.split('.').last;
_99
final fileName = '${DateTime.now().toIso8601String()}.$fileExt';
_99
final filePath = fileName;
_99
await supabase.storage.from('avatars').uploadBinary(
_99
filePath,
_99
bytes,
_99
fileOptions: FileOptions(contentType: imageFile.mimeType),
_99
);
_99
final imageUrlResponse = await supabase.storage
_99
.from('avatars')
_99
.createSignedUrl(filePath, 60 * 60 * 24 * 365 * 10);
_99
widget.onUpload(imageUrlResponse);
_99
} on StorageException catch (error) {
_99
if (mounted) {
_99
ScaffoldMessenger.of(context).showSnackBar(
_99
SnackBar(
_99
content: Text(error.message),
_99
backgroundColor: Theme.of(context).colorScheme.error,
_99
),
_99
);
_99
}
_99
} catch (error) {
_99
if (mounted) {
_99
ScaffoldMessenger.of(context).showSnackBar(
_99
SnackBar(
_99
content: const Text('Unexpected error occurred'),
_99
backgroundColor: Theme.of(context).colorScheme.error,
_99
),
_99
);
_99
}
_99
}
_99
_99
setState(() => _isLoading = false);
_99
}
_99
}

Add the new widget

And then we can add the widget to the Account page as well as some logic to update the avatar_url whenever the user uploads a new avatar.

lib/pages/account_page.dart

_195
import 'package:flutter/material.dart';
_195
import 'package:supabase_flutter/supabase_flutter.dart';
_195
import 'package:supabase_quickstart/components/avatar.dart';
_195
import 'package:supabase_quickstart/main.dart';
_195
_195
class AccountPage extends StatefulWidget {
_195
const AccountPage({super.key});
_195
_195
@override
_195
_AccountPageState createState() => _AccountPageState();
_195
}
_195
_195
class _AccountPageState extends State<AccountPage> {
_195
final _usernameController = TextEditingController();
_195
final _websiteController = TextEditingController();
_195
_195
String? _avatarUrl;
_195
var _loading = true;
_195
_195
/// Called once a user id is received within `onAuthenticated()`
_195
Future<void> _getProfile() async {
_195
setState(() {
_195
_loading = true;
_195
});
_195
_195
try {
_195
final userId = supabase.auth.currentSession!.user.id;
_195
final data = await supabase
_195
.from('profiles')
_195
.select()
_195
.eq('id', userId)
_195
.single();
_195
_usernameController.text = (data['username'] ?? '') as String;
_195
_websiteController.text = (data['website'] ?? '') as String;
_195
_avatarUrl = (data['avatar_url'] ?? '') as String;
_195
} on PostgrestException catch (error) {
_195
SnackBar(
_195
content: Text(error.message),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} catch (error) {
_195
SnackBar(
_195
content: const Text('Unexpected error occurred'),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} finally {
_195
if (mounted) {
_195
setState(() {
_195
_loading = false;
_195
});
_195
}
_195
}
_195
}
_195
_195
/// Called when user taps `Update` button
_195
Future<void> _updateProfile() async {
_195
setState(() {
_195
_loading = true;
_195
});
_195
final userName = _usernameController.text.trim();
_195
final website = _websiteController.text.trim();
_195
final user = supabase.auth.currentUser;
_195
final updates = {
_195
'id': user!.id,
_195
'username': userName,
_195
'website': website,
_195
'updated_at': DateTime.now().toIso8601String(),
_195
};
_195
try {
_195
await supabase.from('profiles').upsert(updates);
_195
if (mounted) {
_195
const SnackBar(
_195
content: Text('Successfully updated profile!'),
_195
);
_195
}
_195
} on PostgrestException catch (error) {
_195
SnackBar(
_195
content: Text(error.message),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} catch (error) {
_195
SnackBar(
_195
content: const Text('Unexpected error occurred'),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} finally {
_195
if (mounted) {
_195
setState(() {
_195
_loading = false;
_195
});
_195
}
_195
}
_195
}
_195
_195
Future<void> _signOut() async {
_195
try {
_195
await supabase.auth.signOut();
_195
} on AuthException catch (error) {
_195
SnackBar(
_195
content: Text(error.message),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} catch (error) {
_195
SnackBar(
_195
content: const Text('Unexpected error occurred'),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} finally {
_195
if (mounted) {
_195
Navigator.of(context).pushReplacementNamed('/login');
_195
}
_195
}
_195
}
_195
_195
/// Called when image has been uploaded to Supabase storage from within Avatar widget
_195
Future<void> _onUpload(String imageUrl) async {
_195
try {
_195
final userId = supabase.auth.currentUser!.id;
_195
await supabase.from('profiles').upsert({
_195
'id': userId,
_195
'avatar_url': imageUrl,
_195
});
_195
if (mounted) {
_195
const SnackBar(
_195
content: Text('Updated your profile image!'),
_195
);
_195
}
_195
} on PostgrestException catch (error) {
_195
SnackBar(
_195
content: Text(error.message),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
} catch (error) {
_195
SnackBar(
_195
content: const Text('Unexpected error occurred'),
_195
backgroundColor: Theme.of(context).colorScheme.error,
_195
);
_195
}
_195
if (!mounted) {
_195
return;
_195
}
_195
_195
setState(() {
_195
_avatarUrl = imageUrl;
_195
});
_195
}
_195
_195
@override
_195
void initState() {
_195
super.initState();
_195
_getProfile();
_195
}
_195
_195
@override
_195
void dispose() {
_195
_usernameController.dispose();
_195
_websiteController.dispose();
_195
super.dispose();
_195
}
_195
_195
@override
_195
Widget build(BuildContext context) {
_195
return Scaffold(
_195
appBar: AppBar(title: const Text('Profile')),
_195
body: _loading
_195
? const Center(child: CircularProgressIndicator())
_195
: ListView(
_195
padding: const EdgeInsets.symmetric(vertical: 18, horizontal: 12),
_195
children: [
_195
Avatar(
_195
imageUrl: _avatarUrl,
_195
onUpload: _onUpload,
_195
),
_195
const SizedBox(height: 18),
_195
TextFormField(
_195
controller: _usernameController,
_195
decoration: const InputDecoration(labelText: 'User Name'),
_195
),
_195
const SizedBox(height: 18),
_195
TextFormField(
_195
controller: _websiteController,
_195
decoration: const InputDecoration(labelText: 'Website'),
_195
),
_195
const SizedBox(height: 18),
_195
ElevatedButton(
_195
onPressed: _loading ? null : _updateProfile,
_195
child: Text(_loading ? 'Saving...' : 'Update'),
_195
),
_195
const SizedBox(height: 18),
_195
TextButton(onPressed: _signOut, child: const Text('Sign Out')),
_195
],
_195
),
_195
);
_195
}
_195
}

Storage management

If you upload additional profile photos, they'll accumulate in the avatars bucket because of their random names with only the latest being referenced from public.profiles and the older versions getting orphaned.

To automatically remove obsolete storage objects, extend the database triggers. Note that it is not sufficient to delete the objects from the storage.objects table because that would orphan and leak the actual storage objects in the S3 backend. Instead, invoke the storage API within Postgres via the http extension.

Enable the http extension for the extensions schema in the Dashboard. Then, define the following SQL functions in the SQL Editor to delete storage objects via the API:


_34
create or replace function delete_storage_object(bucket text, object text, out status int, out content text)
_34
returns record
_34
language 'plpgsql'
_34
security definer
_34
as $$
_34
declare
_34
project_url text := '<YOURPROJECTURL>';
_34
service_role_key text := '<YOURSERVICEROLEKEY>'; -- full access needed
_34
url text := project_url||'/storage/v1/object/'||bucket||'/'||object;
_34
begin
_34
select
_34
into status, content
_34
result.status::int, result.content::text
_34
FROM extensions.http((
_34
'DELETE',
_34
url,
_34
ARRAY[extensions.http_header('authorization','Bearer '||service_role_key)],
_34
NULL,
_34
NULL)::extensions.http_request) as result;
_34
end;
_34
$$;
_34
_34
create or replace function delete_avatar(avatar_url text, out status int, out content text)
_34
returns record
_34
language 'plpgsql'
_34
security definer
_34
as $$
_34
begin
_34
select
_34
into status, content
_34
result.status, result.content
_34
from public.delete_storage_object('avatars', avatar_url) as result;
_34
end;
_34
$$;

Next, add a trigger that removes any obsolete avatar whenever the profile is updated or deleted:


_32
create or replace function delete_old_avatar()
_32
returns trigger
_32
language 'plpgsql'
_32
security definer
_32
as $$
_32
declare
_32
status int;
_32
content text;
_32
avatar_name text;
_32
begin
_32
if coalesce(old.avatar_url, '') <> ''
_32
and (tg_op = 'DELETE' or (old.avatar_url <> coalesce(new.avatar_url, ''))) then
_32
-- extract avatar name
_32
avatar_name := old.avatar_url;
_32
select
_32
into status, content
_32
result.status, result.content
_32
from public.delete_avatar(avatar_name) as result;
_32
if status <> 200 then
_32
raise warning 'Could not delete avatar: % %', status, content;
_32
end if;
_32
end if;
_32
if tg_op = 'DELETE' then
_32
return old;
_32
end if;
_32
return new;
_32
end;
_32
$$;
_32
_32
create trigger before_profile_changes
_32
before update of avatar_url or delete on public.profiles
_32
for each row execute function public.delete_old_avatar();

Finally, delete the public.profile row before a user is deleted. If this step is omitted, you won't be able to delete users without first manually deleting their avatar image.


_14
create or replace function delete_old_profile()
_14
returns trigger
_14
language 'plpgsql'
_14
security definer
_14
as $$
_14
begin
_14
delete from public.profiles where id = old.id;
_14
return old;
_14
end;
_14
$$;
_14
_14
create trigger before_delete_user
_14
before delete on auth.users
_14
for each row execute function public.delete_old_profile();

Congratulations, you've built a fully functional user management app using Flutter and Supabase!

See also