Forem: Dave Parsons

PostgreSQL Functions in Typescript

Dave Parsons — Sat, 31 Dec 2022 02:58:00 +0000

This is the seventh article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL (a.k.a. Postgres) topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will cover why you might need PostgreSQL functions for your project, and how to write them in Typescript using the plv8ts NPM package.

The other articles in this series covered Row-Level Security (RLS) policies, sending email from Postgres, and other related topics, but they aren't required reading for this article.

tl;dr

To try the example, copy the example files from Github into a clean directory:

curl  https://raw.githubusercontent.com/Davepar/plv8ts/main/example/package.json -O
curl  https://raw.githubusercontent.com/Davepar/plv8ts/main/example/tsconfig.json -O
mkdir src; cd src
curl https://raw.githubusercontent.com/Davepar/plv8ts/main/example/src/sample_function.ts -O

Then install and build:

npm i
npm run build

The result will be in a subdirectory called sql. To add drop statements before each create function statement, use npm run build -- -d instead.

To use the lint and fix commands, copy the other files from the example directory on Github.

Note: You may be tempted to put the example code into an existing project, but the settings inside the example tsconfig.json are necessary to build the code correctly. See the readme for the project for more details.

What is a Postgres function?

In addition to providing all of the usual database storage functionality, Postgres also allows defining functions for running any type of code that interacts with the database, or with extensions can issue HTTP requests, encrypt data, and many other useful operations.

Functions can be executed via a database query or on a regular schedule.
Trigger functions are a special type of function that are covered in a previous article. They provide a way to execute an action based on a database event.

When possible, it's best to write functions in SQL. Postgres will compile and optimize SQL functions, while other languages can't be optimized as much. However, sometimes the logic is too complicated to represent in SQL. And sometimes, e.g. when you're prototyping, you don't want to waste time on complex SQL statements. That's where PLV8 is great. Assuming you're writing your frontend in Javascript, you can use the same language for your database functions.

The downsides

Since Javascript is an interpreted language, one huge drawback is that syntax errors won't be found until run time. Which means you'll write the CREATE FUNCTION statement, execute it in your database, and then try running the function before realizing it has a syntax error. And you'll do that over and over again. Making matters worse is that editor-based linting and syntax highlighting won't recognize that you have embedded Javascript in your SQL statement.

Testing is equally difficult. How can you easily test Javascript inside a SQL statement?

Introducing plv8ts

The plv8ts NPM package addresses these problems. You can write the functions in Typescript, compile them, and run the included script to convert them into ready-to-run SQL CREATE FUNCTION statements.

The package provides Postgres-compatible type definitions and a mock plv8 library with functions that you can call from your code to mimic the behavior of the PLV8 extension. Check out the source code for a full list of types and functions defined.

Just follow the instructions for the package to get started. To use the SQL files you can execute them with the \i command in the psql Postgres console. Or if you have an UI for your database (which Supabase provides), copy/paste the SQL statement into the SQL editor.

Why use Typescript?

If you're not already familiar with Typescript, it's basically Javascript with types and some extensions. For PLV8 the main attraction is the compiler can catch some errors early, before running the code in the database. The Typescript compiler will print messages for syntax errors, invalid names, mis-matched types, and many other common issues.

The example also includes a strict linting setup that will catch other potential issues. Just run npm run lint to check your code. And the npm run fix command will auto-format your code.

Testing

The plv8ts package also shows how to use the Jasmine testing framework to test your functions before installing them in Postgres. You can mock out the PLV8 functions and test the results of your code. Check out the example for more details.

Conclusion

Hopefully this article helped you out by explaining the purpose of PostgreSQL functions and how to write them in Typescript with tests to have a tight, simple development loop.

Let me know in the comments if you have any questions or anything was unclear. And follow along for future articles on using PostgreSQL and Supabase as a serverless backend.

(Photo by Florian Klauer on Unsplash.)

Sending Email From Postgres

Dave Parsons — Tue, 27 Dec 2022 16:25:43 +0000

This is the sixth article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will cover how to send email from a PostgreSQL function. The examples are tailored for Sendgrid, but the concepts will work with any API-based email provider.

Setup for the examples

The second article in the series ("Intro to Postgres RLS") has more details on the database tables, but this is the entity-relationship diagram for the examples in this article:

The other articles in this series have so far covered writing and testing Row-Level Security (RLS) policies for these tables and how to write functions, but they aren't required reading for this article.

Why send email from a database?

For my current project I need to notify users when certain situations occur. In particular, when a user is signed up for an in-person event but there isn't enough room, they are put on a wait list. When another attendee indicates they can't go, the first person on the wait list will now be able to attend and needs to be notified.

This is a purely backend action. User A triggers the action, but I want to send the email to User B. I can't send the email from the frontend.

How to send an email

Step 1. Choose a provider

There are several (maybe dozens?) companies out there that will send email for you, AWS SES, Mailgun, Sendgrid, Sendinblue, Mailjet, Mailersend. I've used Sendgrid in the past and like their free trial of 100 emails/month forever, among other features they provide.

Step 2. Set up the provider

I haven't used all of the services, but they usually follow a similar approach. You need to create an account, verify a "from" email address, and probably provide some more info to verify you aren't trying to spam the world about your get-rich-quick Bitcoin scheme.

Note that if you're sending test messages to a Gmail account, that platform is particularly picky about spam. You may need to set up extra email authentication before you receive messages there.

Step 3. Create a Postgres trigger function

The mechanism for sending an email based on a database event is to write a "trigger function." For example in my project, whenever a user changes their "attendee" record it may trigger sending an email to another user. Here's the SQL that creates the function and the trigger:

create function attendees_promote_trigger()
  returns trigger as $$
  begin
    perform private.promote_attendees(NEW.event_id);
    return NULL;
  end;
  $$ language plpgsql;

CREATE TRIGGER attendees_promote_trigger
  AFTER INSERT OR UPDATE OR DELETE ON attendees
  -- To make recovery easier, skip trigger when using service role
  FOR EACH ROW WHEN (auth.uid() IS NOT NULL)
  EXECUTE FUNCTION attendees_promote_trigger();

Note that it skips the trigger when there is no user ID associated with the request. That will help when inserting records as part of a database recovery, or setting up dummy data for testing.

The trigger function is simply calling another function. I did this primarily because for my project there are two separate triggers for sending this email, insert/update attendee row AND update group row. To avoid repeating the logic, I created a separate function.

Step 4. Call the API

Next step is to use the provider's API to send an email. You send an HTTP request to the API with a secret key, the recipient(s) info, and the message. You might send the entire message (text and HTML) in the API call, or you might be able to pre-define a template email and just send values that will be filled in. Sort of like a mail merge.

I was excited to find the supabase-mailer project, but unfortunately it seems that only the Mailgun provider is fully supported and working. Mailgun only has a 30 day trial, which didn't work for my needs.

Calling the API directly is not difficult. Look at the API docs for your email service. Sendgrid's docs recommend using a client library, but scroll past that to the details of the API request including the exact JSON format to send.

From here you'll write a send_email function in Postgres that calls the API. I initially wrote the entire function in PLpgSQL and spent an entire day to get it debugged and working. And even then I wasn't happy with it. In my opinion that language is unintuitive and difficult to learn and debug. I switched over to PLV8, an extension for Postgres that supports writing functions in Javascript. It takes one click in the Supabase UI to enable this extension, and it will save hours and hours of time.

Borrowing some ideas from supabase-mailer, I decided to store my Sendgrid secret API key in a private schema (which is not accessible from the API) of the database. Create the schema and table with:

CREATE SCHEMA IF NOT EXISTS private;
CREATE TABLE IF NOT EXISTS private.keys (
    key text primary key not null,
    value text
);

And then add the key SENDGRID_API_KEY and value via the Supabase UI console.

Here's the send_grid function I ended up with:

create or replace function private.send_email(payload jsonb)
returns void AS $$
    let apiKey = '';
    let debugMail = false;
    try {
        apiKey = plv8.execute(
            `select value from private.keys
                where key = 'SENDGRID_API_KEY'`)[0].value;
        const debugValue = plv8.execute(
            `select value from private.keys
                where key = 'SENDGRID_DEBUG'`)[0].value;
        debugMail = debugValue === 'true';
    }
    catch {
        plv8.elog(ERROR, 'Missing entry in private.keys');
    }
    if (debugMail) {
        plv8.elog(LOG, `Debug email: ${JSON.stringify(payload)}`);
    }
    else {
        const result = plv8.execute(`select * from http((
        'POST',
        'https://api.sendgrid.com/v3/mail/send',
        ARRAY[http_header('Authorization', $1)],
        'application/json',
        $2
      )::http_request);`, ['Bearer ' + apiKey, JSON.stringify(payload)]);
        const { status, content } = result[0];
        if (status !== 202) {
            plv8.elog(ERROR, `Sending email failed, status: ${status}, content: ${content}`);
        }
    }
$$ language plv8;

The function first retrieves my secret Sendgrid API key from the private.keys table. It then retrieves another value from the table that indicates whether to write messages to the database log or actually send them to Sendgrid. The former is handy for debugging since it takes a while for messages to flow through an email provider.

The next step is to either write the info to the log, or use the HTTP extension (which is also enabled via the Supabase UI) to send an API request. If the status code from that request is something other than 202, something went wrong and an error message is logged.

Step 5. The update function

The only thing missing at this point is the promote_attendee function that the trigger calls. Most of the logic isn't relevant for this article, so I'll include just the part that calls send_email. Again this function is written in PLV8:

const user_data_rows = plv8.execute(
    `select au.email, p.full_name
        from auth.users as au
        join profiles as p on au.id = p.id
        where au.id = any($1)`,
        [user_ids]);
// Get template ID from the private.keys table.
const template_id = plv8.execute(
    `select value from private.keys
        where key = 'EMAIL_TEMPLATE_PROMOTE_ATTENDEE'`)[0].value;
// Create the data structure that SendGrid expects.
const personalizations = user_data_rows.map(({ email, full_name }) => ({
    to: [
        {
            email,
            name: full_name,
        },
    ],
    dynamic_template_data: {
        event_name,
        event_location,
        event_date_time: localizedDateTime,
    },
}));
const payload = {
    from: {
        email: 'admin@example.com',
        name: 'Admin',
    },
    template_id,
    personalizations,
};
plv8.execute('select private.send_email($1)', [payload]);

This code first retrieves the email address and name for each user that is listed in the user_ids variable. It next retrieves the template ID from the database. This is a value I also stored in the private.keys table. The value was provided by Sendgrid when I created an email template in their UI.

The next step is to generate the JSON blob that Sendgrid expects. This follows their documentation for generating an array of email addresses and names, along with "from" data and the template ID.

Last step is to finally send the JSON blob to the send_email function from above.

Future work

Sendgrid (and probably the other providers as well) have a feature where an HTTP endpoint can be called whenever the status of an email changes. I'll cover setting this up in a future article.

I also set up an environment for writing PLV8 functions in Typescript which simplifies the process of writing the wrapper create function SQL and provides type checking, linting, prettier(ing), and testing. That will be a future article as well.

Postgres Trigger Functions

Dave Parsons — Mon, 12 Dec 2022 02:54:49 +0000

This is the fifth article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will cover the why and how of PostgreSQL trigger functions.

Setup for the examples

The second article in the series ("Intro to Postgres RLS") has more details on the database tables, but this is the entity-relationship diagram for the examples in this article:

The other articles in this series have so far covered writing and testing Row-Level Security (RLS) policies for these tables, but they aren't required reading for this article.

Why trigger functions?

The previous articles set up RLS policies for the groups and members tables. Any authenticated user can insert a new group, but only admin members can update or delete a group or member records in that group. And users can insert their own member records, but only at the "approved" level, not "admin".

So how are the admin records created in the first place? That's where trigger functions come into play. They are great for handling logic beyond the typical database CRUD operations

A simple trigger function

Here's a trigger function that will handle adding an admin member record:

create function groups_insert_admin_trigger()
returns trigger as $$
begin
  -- To make recovery easier, skip trigger when using service role
  IF auth.uid() IS NULL THEN
    return NEW;
  END IF;
  -- Insert admin members record
  INSERT INTO members (user_id, group_id, level)
    VALUES (auth.uid(), NEW.id, 'admin');
  RETURN NEW;
end;
$$ language plpgsql security definer;

There is a lot to unpack here. First off, the part between the double dollar signs ($$, which you can change to another delimiter if you want) is the actual function. It's written in plpgsql, which is Postgres' built-in procedural language. (You can install the Javascript extension and write the function in JS, but I haven't personally tried that yet.) The language is rather clunky, but understandable.

The function first checks for a null user and immediately returns. This will handle the case where we are loading data from a backup. The next statement inserts the group admin member record using the current user's ID, the new group's ID, and a level of "admin". It then finally returns the new records, which is a requirement of trigger functions.

The part outside of the function is fairly straightforward except for the security definer part at the very end. That causes the trigger function to execute with the security context of the user that created it, which is typically a database administrator. Be very careful with this statement! For my situation, this is the only way to insert an admin member record. And this function will only be called when a new group is inserted, so the risk is low.

One more statement is needed to create the trigger itself:

CREATE TRIGGER groups_insert_admin_trigger
  AFTER INSERT ON groups
  FOR EACH ROW EXECUTE FUNCTION groups_insert_admin_trigger();

Now every time a group is created, the user who created it will be added as an admin member. Perfect!

Trigger function for altering data

Another situation where trigger functions are useful is to modify data before it is inserted or updated. Let's say we want to automatically create a canonical URL-friendly field for each group based on its name. For example, "My taco group" would have the canonical URL "my-taco-group". First we add the new field to the table and then create a trigger function:

create function groups_create_canonical_trigger()
returns trigger as $$
declare
  temp text;
begin
  temp := lower(NEW.name);
  temp := regexp_replace(temp, ' ', '-', 'g');
  NEW.canonical = regexp_replace(temp, '[^\w-]+', '', 'g');
  return NEW;
end;
$$ language plpgsql;

This function declares a temporary variable and uses that to store the group name. Then it converts the name to lowercase, replaces all the spaces with dashes, and removes any non-word characters. It then assigns that value to the "canonical" field and returns the record.

Note that this function does not need an elevated security context, so we left off the security definer phrase at the end. And to create the actual trigger:

CREATE TRIGGER groups_create_canonical_trigger
  BEFORE INSERT ON groups
  FOR EACH ROW EXECUTE FUNCTION groups_create_canonical_trigger();

This will overwrite any value the user provides in the canonical field.

Conclusion

This articles provided some motivation for trigger functions and provided a couple of examples. Let me know in the comments if you have any questions about trigger functions, or need any clarification.

This series of articles is going to pause here, but I will be back soon to write more articles on using other Supabase services! Follow along to catch those articles, too!

Testing Supabase Row Level Security

Dave Parsons — Mon, 12 Dec 2022 02:53:54 +0000

This is the fourth article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will cover how to test PostgreSQL Row-Level Security (RLS) policies. In particular, how to test them with the Supabase Javascript client library.

Setup for the examples

The second article in the series ("Intro to Postgres RLS") has more details on the database tables, but this is the entity-relationship diagram for the examples in this article:

That article plus the third article also contains the RLS policies for these tables.

Why test at all?

I'm not even going to answer that question. I'll just point you to this article on the top 12 most expensive data breaches. Nearly (or probably over) $5 billion total.

But why not use pgTAP? I don't know about you, but when a project's most simple example is 20 lines of code in an unfamiliar language I start looking for something easier. Plus most of the policies for my application are user-based, so I guess I'd need to fake out the auth.uid() Supabase helper.

Writing tests in Typescript

For my Svelte application I'm already writing tests in Typescript, so why not write the database tests that way as well? Turns out to be very easy to install and configure Vitest, a unit test runner for Vite (the frontend tooling that SvelteKit uses). Vitest shares the configuration from Vite, making things very easy to set up. I didn't need to set any additional configuration. Just install and add this command to the package.json scripts:



"testdb": "vitest run",

Here's a basic test for the public group view policy using Supabase's Javscript client library:



import {describe, expect, it} from 'vitest';
import {supabase} from './lib';

describe('anonymous user', () => {
  it('can only read public groups', async () => {
    const {data, error} = await supabase.from('groups').select('name').order('name');
    expect(error).toBeNull();
    const names = data!.map((entry) => entry.name);
    expect(names).toEqual(['Biking Group', 'Book Club', 'Morning Yoga']);
  });
});

Supabase is a global client created with the createClient() function from the supabase-js library. After creating some dummy test data, run the test with npm run testdb. Very easy.

Test for RLS violation

This will test that an anonymous user can't insert a group record:



// imports from above
import type {StorageError} from '@supabase/storage-js';
import type {PostgrestError} from '@supabase/supabase-js';

/** Verify row-level security error */
function rlsError(error: PostgrestError | StorageError | null) {
  return error?.message.startsWith('new row violates row-level security policy') || false;
}

describe('anonymous user', () => {
  // other tests
  it('cannot insert', async () => {
    const {error} = await supabase.from('groups').insert({
      name: 'Test group',
      is_public: true,
    });
    expect(rlsError(error)).toBe(true);
  });
});

The helper function verifies that the insert operation returns the expected row-level security violation.

Test for authenticated users

After creating some test users, the more complicated policies can be tested as well:



// imports
describe('authenticated user', () => {
  beforeEach(async () => {
    const {error} = await supabase.auth.signInWithPassword({
      email: 'sam@example.com',
      password: 'BadPassword',
    });
    expect(error).toBeNull();
  });

  it('can insert and delete', async () => {
    const {data, error} = await supabase
      .from('groups')
      .insert({
        name: 'Test group',
        description: 'Test description',
        location: 'Nowhere',
      })
      .select()
      .single();
    expect(error).toBeNull();
    expect(data.name).toBe('Test group');
  });
});

By setting up the test data with members at each level (e.g. waiting, approved, admin), each of the policies can easily be tested. You'll want to set up a separate database, and thus Supabase project, just for testing. You can use the Supabase service role to set up and clean up the databases before and after the tests. See the "API" section of "Settings" in the Supabase Admin UI.

Conclusion

With comprehensive tests in place, modifying and adding to the RLS policies is much easier and more reliable. Hopefully this overview provided a good start for you to write your own tests, but please leave comments if anything is unclear or you have questions.

With our RLS policies written and covered by tests, the next article will take a look at Postgres functions and how they are useful in our project.

Advanced Postgres Row Level Security

Dave Parsons — Mon, 12 Dec 2022 02:39:26 +0000

This is the third article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL topics. See the first article ("Going Serverless With Supabase") in the series for more background.

In this article, I'll show how to write PostgreSQL Row-Level Policies (RLS) policies for modifying table rows, e.g. the CUD part of CRUD.

Setup for the examples

The second article in the series ("Intro to Postgres RLS") has more details on the database tables, but this is the entity-relationship diagram for the examples in this article:

The previous article also included two policies that will allow users to view public groups and the private groups of which they are an approved member.

Allowing insert

For my application, I want to allow any authenticated user to create a group.



CREATE POLICY "Groups: users can insert"
  ON groups FOR INSERT TO authenticated
  WITH CHECK (TRUE);

You'll notice these differences to the policies from the previous article:

FOR INSERT makes this policy apply to insert operations (including upsert for non-existent records).
WITH CHECK instead of USING. Insert and update policies use this boolean clause to check the content of the incoming data.
TRUE indicates that every authenticated user can perform this operation.

Allowing update and delete

While any user can create groups, I only want the admins to be able to update and delete.



CREATE POLICY "Groups: admins can update, delete"
  ON groups FOR ALL TO authenticated
  USING (EXISTS (SELECT 1 FROM members AS m
    WHERE auth.uid() = m.user_id
      AND m.level >= 'admin'
      AND id = m.group_id))
  WITH CHECK (EXISTS (SELECT 1 FROM members AS m
    WHERE auth.uid() = m.user_id
      AND m.level >= 'admin'
      AND id = m.group_id));

The highlights of this policy are:

FOR ALL will make this policy apply to all operations, select, insert, update, and delete. Select and insert are already more permissive than this rule, so it's easier to just use ALL to cover update and delete. The query planner should optimize out this check for select and insert. Note that this will only work with "permissive" rules, which is the default and what all these examples are using.
There is a USING and WITH CHECK clause. The docs cover exactly which is used in which situation. For this policy, USING will determine which rows can be updated or deleted, and WITH CHECK will limit the new data for the updated row.

Yes, the two clauses are the same. An admin can modify their groups, but not change the group ID to some random value. An even better check for not modifying the group ID would be to use a "before update trigger", but that's a topic for a future article.

More complex policies

To see something more complex, let's move on to the "Members" table. Users should be able to insert and view their own member records (or memberships). Beyond that users should also be able to view members of their groups.

Let's start with the easiest one first, allowing users to view their memberships:



CREATE POLICY "Members: users can view their own"
  ON members FOR SELECT TO authenticated
  USING ((auth.uid() = user_id));

Nothing new here. Next up the insert policy:



CREATE POLICY "Members: users can insert their own"
  ON members FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id AND level = 'waiting');

Hopefully that looks straightforward too. Users can only insert records for themselves, and the membership level must be set to "waiting" so that an admin can approve them.

And let's also allow approved group members to see the other members of the group:



CREATE POLICY "Members: viewable by approved members"
  ON members FOR SELECT TO authenticated
  USING (EXISTS (SELECT 1 FROM members AS m
    WHERE auth.uid() = m.user_id
      AND m.level >= 'approved'
      AND members.group_id = m.group_id ));

Hopefull that policy makes sense. Check that the user has a member record for the group with at least level "approved". Postgres will accept this policy without any hesitation. However, when a user tries to read the members for one of their groups they will see:



infinite recursion detected in policy for relation "members"

How to fix infinite recursion errors

Yikes! So what happened? First off, the SQL syntax is correct so that's why there was no error when the policy was created. It's only when the policy is executed that the problem occurs. (And thus tests for your policies are very important.) Postgres is trying to evaluate the policy by reading members records, which then invokes the policy and leads to recursion.

An easy way to solve this problem is to create a view, which is like a window into the database filtered through a query:



CREATE OR REPLACE VIEW my_memberships AS
  SELECT * FROM members AS m
    WHERE auth.uid() = m.user_id AND m.level >= 'approved';

This view by default will run in the security context of the user who creates it, which should be your database admin. (So be very careful with views.) This effectively pokes a hole through the RLS policies and returns all of the approved member rows for the current user, regardless of any policies. We can then use this view in the above policy:



CREATE POLICY "Members: viewable by approved members"
  ON members FOR SELECT TO authenticated
  USING (EXISTS (SELECT 1 FROM my_memberships AS m
    WHERE members.group_id = m.group_id));

This resolves the recursion and even simplifies the policy! Remember that EXISTS converts a query into a boolean, and SELECT 1 is just a simple way to return a value from a query since we don't care what that value is. The whole thing will evaluate to false if there are zero rows in the query, in this case no admin member records for the user.

Poking more holes in RLS (on purpose)

Everything is working great so far. But let's add a requirement that users can view the number of members in each group, regardless of whether they are a member. That gives people an idea of how popular a group is before joining. But if the user isn't a member of group 1, SELECT * FROM members WHERE members.group_id = 1 will always return zero rows. How do we solve this?

Let's write another view for this query:



CREATE OR REPLACE VIEW num_members AS
  SELECT g.id as group_id,
    sum(case when m.level >= 'approved' then 1 else 0 end) AS num_members
  FROM members AS m
  JOIN groups AS g ON m.group_id = g.id
  WHERE g.is_public
    OR g.id IN (select group_id from my_memberships)
  GROUP BY g.id;

This will count the number of approved members in all of the groups the user can access (via public groups or approved membership). Note that we had to spell out which groups the user can access instead of relying on the group policies we defined previously. That's because the view is executed with a higher security context which can always see all the groups.

Again, be very careful with these views. Make sure you only use them in very limited cases where more access is required.

Another way to poke a hole in the RLS policies is by writing a function with the security definer clause, which will be covered in a later article.

Conclusion

Things are starting to get more complex, but hopefully breaking down these policies bit by bit is making it easier to understand the flexibility and power of these policies. Let me know in the comments if anything needs more explanation.

With the basic concepts of RLS policies covered, the next article will cover how to test these policies on Supabase. Because nobody should leave data security to chance.

And kudos if you've noticed a chicken-and-egg problem with our admin records. Check out the solution to that in the article on triggers.

Intro to Postgres Row-Level Security

Dave Parsons — Mon, 12 Dec 2022 02:37:02 +0000

This is the second article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will introduce you to Row-Level Security policies, which are key to using a serverless platform. At first, you're going to think these are convoluted and difficult, but there are two very cool things about RLS that I'll point out below.

Setup for the examples

My application has users (called "profiles"), groups, members, events, and attendees. Groups can be private or public, and members can have a status of not approved, waiting approval, approved, or admin. Attendees similarly can be going, not going, or on the wait list. Here's the simplified table structure in diagram form:

A simple policy

Let's start with something easy. Let's make the public groups visible to everybody, including visitors who aren't signed in (i.e. are unauthenticated). We need to write a SELECT policy that is very similar to a WHERE clause:

CREATE POLICY "Groups: public groups viewable by everyone"
  ON groups
  FOR SELECT
  USING (is_public = TRUE);

This will make every row in the groups table visible if it has the is_public boolean set to true. Simple right? You'll see I've given it a descriptive name.

A word about workflow

At this point you may be asking, where I do put this policy? You can use Supabase's great admin UI, which is an excellent place to start playing with policies. In the Authentication section, click on Policies, find the table you want to work on, Enable RLS, and click New Policy. You can either use a template policy, or start from scratch. It will guide you through the options.

However, once you get deeper into writing policies the UI will become a hindrance. You'll eventually want to write these policies in a text file that's backed up by source control. By adding a DROP line before each CREATE, you can easily copy/paste the text into the Supabase UI's SQL Editor to run, edit, and repeat on the policies until they're working perfectly. Or you can connect directly to your database with psql and use the /i command to execute these files.

DROP POLICY IF EXISTS
  "Groups: public groups viewable by everyone" ON groups;

And while you're at it, you might as well add this statement to enable RLS for the table to make your setup repeatable:

ALTER TABLE groups ENABLE ROW LEVEL SECURITY;

By default, tables have no security at all. Anybody can view, insert, update, and delete rows. So definitely be sure to enable RLS for all of your tables, or set up a table policy.

Now for something more complicated

What about private groups? Let's add a policy so that approved members can view their private groups.

CREATE POLICY "Groups: viewable by approved members"
  ON groups FOR SELECT TO authenticated
  USING (EXISTS (SELECT 1 FROM members AS m
    WHERE groups.id = m.group_id
      AND auth.uid() = m.user_id
      AND m.level >= 'approved'));

It starts out the same as the previous policy, but here are the notable differences:

TO authenticated means the user must be signed in.
EXISTS is a SQL operator that turns a subquery into a boolean.
And we have the subquery that looks for a membership record for the authenticated user and current group record with a member level at or above "approved".

The auth.uid() function is a helper that Supabase provides from their authentication system. It returns the current user's ID (which is an UUID).

The first super, awesome part of policies

And this is the first crazy cool part about policies is that when a user signs in and executes a SELECT * ON groups. They are going to get every public group, and every group they are a member of. And that's it! Automatically!! You can add a WHERE clause onto that, but the results will always be filtered by the policies. Your API is secure AND your database is secure. Doesn't matter how you change your logic in the future, or if you switch frontend stacks entirely. Cool, right?

(Although one thing to be careful of is views.)

Chaining policies

Now let's write a policy for events. An event should be viewable by anyone who can view the group.

CREATE POLICY "Events: viewable by anyone who can view the group"
  ON events FOR SELECT
  USING (EXISTS (SELECT 1 FROM groups AS g
    WHERE group_id = g.id));

That's it! The event policy just relies on the group policy. This makes writing policies much simpler than it might first appear. And that's the second very cool thing about policies. You can chain them together.

Conclusion

This article introduced you to Row-Level Security policies, and hopefully left you with the impression that they are not difficult to write.

In the next few articles, I'll cover writing policies for altering rows, how to handle some tricky situations, and finally how to test policies.

Photo by Erik Mclean on Unsplash

Going "Serverless" With Supabase

Dave Parsons — Mon, 12 Dec 2022 02:36:10 +0000

This is the first in a series of articles on my experience using the Supabase platform instead of writing a backend server. I put "serverless" in quotes, because some would consider Supabase to be a server. From my perspective, I'm not writing a server in the traditional sense. I'm defining tables and other components in a database, and the rest is handled for me, authentication, the API, object storage, and more.

The first several articles will cover setting up the PostgreSQL database that is supplied with your project. After that, I'll dive into the other components of Supabase.

My project

I'm creating a new website that contains several entities, e.g. users, groups, members, events, attendees. Naturally I started writing a server in one of my favorite languages, Python. I've used Django and Flask in the past, but decided to try FastAPI for this project due to its everything-included style of defining an API.

For the frontend, I wanted to try Svelte. I've used Angular and React professionally and found both of them to be cumbersome. Svelte (plus its framework counterpart SvelteKit) feels lightweight and simple while delivering amazing performance.

So there I was writing the frontend in Typescript plus the backend in Python and constantly context switching between two very different languages, when it dawned on me that eventually I would need to find a hosting platform for the server. SvelteKit provides an amazingly simple path for deploying a production-ready site on Vercel and other platforms, but standing up a FastAPI server + SQL database looked like it was going to be difficult. And that's when I ran across Supabase.

Why Supabase?

Supabase is similar to Firebase, but based on open-source technologies. You can even stand up your own Supabase server if you want. So in theory no lock-in with a single vendor. At its core a Supabase project is a PostgreSQL database with some extensions to provide an API, authentication, object storage (based on AWS S3), logging, and other basic services. It also provides a very nice administration console to interact with your project and data.

And yes there is a free tier so you can get started with your project without spending a fortune.

Why not Supabase?

Once you start investigating, you will very quickly have one more more of the following questions or concerns:

Where's the server code?

Short answer: there isn't any.

Using the full power of PostgreSQL plus some open-source extensions and a few other services, writing a traditional web server isn't necessary. You don't have to write, test, optimize, host, deploy, monitor, and update all that code. Yes you will have to learn some new technologies and write some SQL and probably some PostgreSQL functions, but that will be far easier than creating and maintaining an entire server architecture.

Why not use SvelteKit's server?

That is certainly an option for my project, and a pretty good one. However, where is that server going to run? Where is the database? They should be very close together to minimize data lag, and ideally running on the same host. And the usual coding steps (write, test, optimize, etc.) will still be required.

Is Supabase better than Firebase?

I haven't tried using Firebase. I did look at Pocketbase, which is another open-source backend platform written in Go. These platforms seem to rely on row-level security (the next few articles in this series cover RLS in depth). The issue with Firebase is that the RLS policies are written in a proprietary language. Same with Pocketbase. Which locks you into those platforms. Supabase uses PostgreSQL RLS, so you could host your own PostgreSQL database and use the same policies. Same issue for functions you need to write (a later article covers writing functions).

And that's the advantage of a platform that is based on open-source technologies. No lock in.

Row-Level Security (RLS) looks hard.

Like many technologies, RLS takes a while to grasp. But once you understand the basics you'll realize how it will greatly simplify your application while maintaining tight security of your data.

Check out the other articles in this series for an introduction to writing and testing RLS policies.

The JS client library queries don't look very powerful.

Good news! You don't have to use them. Personally I prefer to work with SQL. Every database library I've ever used attempts to make things simple, but instead increases the complexity.

Instead write PostgreSQL SQL functions (or even write the functions in Javascript!) and call them via supabase.rpc('function_name', {param1: 'value'});. Or it's even simpler to write Postgres Views, but be careful because by default run they may use an elevated security context.

What if some fields have different visibility?

Separate the fields into separate tables based on their visibility. For example, in my application for events the name is public but the location is only visible to group members. Create a one-to-one relationship between the tables and create separate RLS policies for each. Is that a little convoluted? Perhaps. But it's much simpler than writing an entire backend server.

How can I test the server?

Since you no longer have a server, you just need to test your database. Supabase supports pgTAP for writing database tests, but I personally found it much easier to write client-side tests against the database using test data. Check out the article for more info.

Can I use GraphQL?

Yes. I haven't personally tried it, but [Supabase supports the GraphQL extension for PostgreSQL].(https://supabase.com/blog/graphql-now-available).

I need cron jobs.

Check out the pgcron extension. I also haven't used this yet, but seems like it will work fine for my needs.

Conclusion

Hopefully this article is piquing your interest in using Supabase for your next project. Follow along in this series for more articles on using Supabase and PostgreSQL.

And please comment with any questions or concerns you had while considering or using Supabase.

(I have not been compensated by Supabase in any way for these articles. I'm just a customer that is very happy with the service so far.)

How to modularize and test Google Apps Scripts

Dave Parsons — Mon, 17 Aug 2020 03:00:35 +0000

Overview

Google Apps Script (GAS) is the mechanism for adding additional functionality to Google Docs, Sheets, Calendar, and other products. For example, I wrote a script that synchronizes Google Calendar events with rows in a Google Sheet. It's fairly easy to write a short script to automate many tasks in Google's apps. However, until recently it was fairly difficult to make more complicated scripts due to a lack of module support.

Over the past year Google has introduced two huge features to GAS development, Typescript (TS) support (currently pegged at TS 3.5.2) and switching to the V8 runtime and thus modern ECMAScript. Prior to these updates scripts had to be written in a very old version (ES5) of Javascript. While these two changes bring many improvements (arrow functions, classes, static type checking, etc.) there are a couple of unexpected behaviors when using modules. I'll discuss these and offer work arounds.

Setup

Setting up Typescript for GAS is straightforward and well documented, so I won't repeat it here. You'll need to install npm if you don't have it already, and then the "clasp" tool that does the Typescript conversion along with some type definitions. More instructions guide you in either creating a new script project or cloning one that already exists. You write your code in .ts files and they will be converted to Javascript and uploaded into your script project as corresponding .gs files (gs for Google Script?).

Using TS Modules

A typical GAS script includes top-level functions that the Google infrastructure will call in response to events like opening a Sheet (onOpen) or installing the add-on (onInstall). Your script can install a menu that will also call top-level functions using any name you choose. I put these functions in a file named Code.ts based on the convention that GAS code lives in a file called Code.gs.

I also wanted some helper functions for a settings dialog and a class for helping convert between Calendar events and Sheet rows. I put these in separate files, which in Typescript defines separate modules. However, and this is important, during clasp translation the modules will be stripped away. All of the code from all of your .ts files will live in one global namespace. It's just like having all of the code merged together into one file. You don't need (and can't have) import statements.

Testing issues

I wanted to maintain reliability while developing some new features, so I decided to add some tests. The lack of module support presented some problems. I created a tests directory, wrote some tests, and installed Jasmine to run them. I added the tests directory to the .claspignore file so they wouldn't compile and upload as part of the script project, and thus I could use import to include the file under test. Great! However, my code modules depend on each other. Without import statements the tests failed to compile.

My work around is admittedly hacky and only works on Linux. (I welcome suggestions.) I wrote two scripts pretest:

#!/bin/bash
for f in *.ts; do
  sed -i 's|/\*%|/\*% \*/|;s|%\*/|/\* %\*/|' $f
done

And posttest:

#!/bin/bash
for f in *.ts; do
  sed -i 's|/\*% \*/|/\*%|;s|/\* %\*/|%\*/|' $f
done

When run before and after testing, they comment and uncomment the tags /*% and %*/. I then wrote the import statements for my module dependencies like this:

/*% import {Util} from './Util'; %*/

And then tied it all together with the following configuration in package.json:

  "scripts": {
    "pretest": "./pretest",
    "posttest": "./posttest",
    "test": "ts-node node_modules/jasmine/bin/jasmine --config=jasmine.json || :"
  },

This hides the import statements from clasp but enables them for testing making it easy to run the tests with npm test.
Note: The || : at the end of the test command hides the Jasmine exit code. That ensures that posttest always runs even when the tests fail. SO hat tip

Syntax checking issues

The next problem I encountered is that using clasp to translate and upload the TS code does not perform static analysis. For example, if you have a typo in a variable name the error won't become apparent until you upload the code and run it. Using a smart editor like VSCode will highlight at least some of these problems. A more thorough solution is to run a test against the code which executes a full static analysis. One problem with this approach is that I did not have any tests for the main file Code.ts. It primarily calls GAS functions and I didn't have time to write test fakes for all of them. My solution was to add a small dummy function in Code.ts:

export function exerciseSyntax() {
  return true;
}

And then write a small test in Code_test.ts:

export function exerciseSyntax() {
  return true;
}

Now running the tests will catch any issues in all of the files, including Code.ts.

Future Improvements

The GAS script I wrote gets a fair amount of usage, especially from teachers using it for class schedules. I get a steady stream of feature requests that I work on occasionally. I might also write some test fakes for the GAS entry points in order to improve the code coverage for the tests.

Let me know in the comments if this article was useful for you, and if you'd like to see future topics such as how to save and load user settings, the (arduous) process of publishing a script, the ins and outs of scripting Google Calendar, or another topic.