Luke Bechtel from Revaly recently asked "is there any way to get things like Browser Type or Host Name from inside a PostgreSQL trigger or RLS (Row Level Security) Policy?" Well, since the Supabase client uses PostgREST, and PostgREST is a web tool, then it should be able to access to the server's request object. And indeed, it does.
Why is this useful or important? Imagine these use cases:
- Allow-listing IPs: only allow users to select, insert, update, or delete if they're coming from a pre-defined list of IP addresses.
- Origin Restrictions: allow a feature only during development (when the request is coming from localhost but not your production domain).
- Platform Checking: only allow users from mobile platforms to use your application (no desktop browsers).
- Logging: Log the user's IP address and browser User Agent in your database with their anonymous request data.
Of course, if the user is logged into our app, we can also get their id using
auth.uid(), or their email using
auth.jwt() ->> 'email'.
How can we get access to all this useful information? By using the PostgreSQL
current_setting function, we can access the
request.headers liks this:
current_setting('request.headers', true). So, to put that into a useful function, we get:
CREATE OR REPLACE FUNCTION get_headers() RETURNS jsonb LANGUAGE sql STABLE AS $$ SELECT current_setting('request.headers', true)::json $$;
This function returns a JSON object with all the header information from the request. We get things like:
accept-encoding: "gzip" accept-language: "en-US,en;q=0.9" host: "localhost:3000" origin: "http://localhost:8100" referer: "http://localhost:8100/" user-agent: "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/18.104.22.168 Safari/537.36" x-client-info: "supabase-js/1.35.7" x-consumer-username: "anon-key" x-forwarded-for: "22.214.171.124, 126.96.36.199" x-forwarded-host: "xxxxxxxxxxxxxxxx.supabase.co"
If we want to get the data from a specific header, we can create this function:
CREATE OR REPLACE FUNCTION get_header(item text) RETURNS text LANGUAGE sql STABLE AS $$ SELECT (current_setting('request.headers', true)::json)->>item $$;
This allows us to get the text value for any specified header, such as:
Let's say we want to only allow records to be inserted into our table
beta_tests if the request is coming from a server running on
CREATE POLICY "only allow inserts on public.beta_tests from localhost:3000" ON public.beta_tests FOR INSERT WITH CHECK (get_header('host')='localhost:3000');
For security purposes, we can restict the usage for a table based on a whitelisted set of IPs. First, we need to get the user's IP address, which is found in
x-forwarded-for, but that has 2 IP addresses separated by commas, and we only want the first one. So we can use the PostgreSQL
SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1). Note how we concatenate a comma to the
x-forwarded-for header (
get_header('x-forwarded-for') || ','), just in case there's an empty string there?
Now that we have the user's IP address, we can test to see if it's in our whitelist set:
CREATE POLICY "only allow access to table_for_internal_use_only from a set of IPs" ON "public"."table_for_internal_use_only" AS PERMISSIVE FOR ALL TO public USING (SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1) = ANY (ARRAY['188.8.131.52','184.108.40.206','220.127.116.11'])) WITH CHECK (SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1) = ANY (ARRAY['18.104.22.168','22.214.171.124','126.96.36.199']));
You could extend this by creating a table of IP addresses and check against that table (
(SELECT count(*) from my_whitelist_table where ip = SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1)) > 0), but be careful, this adds an extra lookup to another table, and this slows down your RLS policy considerably and could lead to scaling problems down the road.
Let's create a log table caled
log_table, and then for every record inserted into our
test_table, we'll log a record there with the user's
CREATE TABLE IF NOT EXISTS log_table (id serial primary key, table_name text, key text, created_at timestamptz DEFAULT now(), user_agent text, host text, origin text, referer text, ip text); CREATE OR REPLACE FUNCTION log_user_data() RETURNS trigger AS $$ BEGIN INSERT INTO log_table(table_name, key, user_agent, host, origin, referer, ip) VALUES(TG_TABLE_NAME::regclass::text, NEW.id::text, get_header('user-agent'), get_header('host'), get_header('origin'), get_header('referer'), SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1)); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER test_trigger AFTER INSERT ON test_table FOR EACH ROW EXECUTE PROCEDURE log_user_data();
Things of note here:
TG_TABLE_NAME::regclass::textreturns the current table name in our trigger (so we can re-use this trigger on other tables!)
idfield of the current table to text (a string). I use a
idfield for almost every table I create, so this should work just fine. If you use a different convention or primary key type, you may have to alter this.
SPLIT_PART(get_header('x-forwarded-for') || ',', ',', 1), as mentioned earlier, grabs the first
ipfound in the
We can parse the
user-agent header to get relevant information, such as:
Is the user running on a Windows platform:
get_header('user-agent') LIKE '%Windows%'
get_header('user-agent') LIKE '%Mac OS X%'
Is the user on a Mobile device:
get_header('user-agent') LIKE 'Mobile/%'
Check iOS Major Version:
get_header('user-agent') LIKE '%iPhone OS 16%'
user-agent isn't the most accurate way to get this information, though, and
user-agents are always subject to change (and can be forged) so be careful with this.
You may find additional headers beyond the ones listed here available to you when testing this, but it's best not to rely on them, as they:
- may not be available on every platform or device (some headers exist on desktop systems but not mobile systems, for instance)
- may change or go away completely based on infrastructure changes or changes to PostgREST or the Supabase Client Libraries.