a better option might be nanoid like
# off-topicc
Crembo
05/21/2023, 1:14 PMa better option might be nanoid, like this one https://github.com/viascom/nanoid-postgres/blob/main/nanoid.sql
r
Razoth
05/21/2023, 1:15 PMcontinue here please 😄
c
Crembo
05/21/2023, 1:16 PMright
Crembo
05/21/2023, 1:16 PMI suppose using uuid v4's as the primary key is an option
Crembo
05/21/2023, 1:16 PMwhat does that do to database perf these days? googling info gives WILDLY different results
Crembo
05/21/2023, 1:16 PMthe gamut is "never do it YOU WILL EXPLODE" all the way to "it's fine"
r
Razoth
05/21/2023, 1:17 PMhonestly don't know about which version supabases uses.
Razoth
05/21/2023, 1:18 PMbut it uses the UUID-OSSP plugin, so you could prob use v5
c
Crembo
05/21/2023, 1:20 PMit's mostly the perf issues of a primary key that isn't sequential
r
Razoth
05/21/2023, 1:21 PMwhat are using for the frontend?
c
Crembo
05/21/2023, 1:22 PMsveltekit
r
Razoth
05/21/2023, 1:24 PMyou could do all kinds of weird things, like, this is going to sound a bit far fetched, but the WiiU from nintendo just switched the bytes in the wifi packages around like (64 Byte Package, A:32 B:32 and just did B first and then A to secure it.
you could use some kind of base encoding, switch it around a bit on the frontend and spit it out as a user facing ID.
Razoth
05/21/2023, 1:25 PMsecurity by obscurity.
c
Crembo
05/21/2023, 1:26 PMsince the client is talking directly to the database over postgREST, the database needs to be in on things on its end..
r
Razoth
05/21/2023, 1:27 PMwhy?
Razoth
05/21/2023, 1:28 PMmaybe i don't get something from sveltekit, but on nextjs, which i'm familiar with, you can do all kinds of things with the data before it goes to the user.
c
Crembo
05/21/2023, 1:28 PMbecause, unless I force all requests to go through my server and then on through to supabase, the client can see everything going on in the 'network' tab
Crembo
05/21/2023, 1:28 PMand if I do THAT, I lose out on a lot, y'know?
r
Razoth
05/21/2023, 1:30 PMAHHHHHH client side. i'm so sorry. 😄
Razoth
05/21/2023, 1:31 PMnextjs can render serverside or client side, depending on what you want and need. i didnt get that.
c
Crembo
05/21/2023, 1:31 PMyeah, once the page has been initially rendered and delivered the client, the client can then do a lot of the work
Crembo
05/21/2023, 1:31 PMand if the client can talk to supabase directly instead of having to detour via my server..
r
Razoth
05/21/2023, 1:32 PMisn't there something like an API route on swelte that runs server side?
c
Crembo
05/21/2023, 1:32 PMyes, there is
r
Razoth
05/21/2023, 1:32 PMthen handle it by "proxy-ing" through the api route?
c
Crembo
05/21/2023, 1:33 PMsure, but you've added the extra roundtrip, and theoretically the endpoint is still open to the internet
Crembo
05/21/2023, 1:33 PMunless you decide to only allow the service user role
r
Razoth
05/21/2023, 1:33 PMyou could also make an account for the API route and and use RLS
c
Crembo
05/21/2023, 1:34 PMthe problem is that the supabase API endpoint is still exposed and you can talk to it directly since your project ID and anon key are both embedded in the client
r
Razoth
05/21/2023, 1:34 PMwhat about.... edge functions!
Razoth
05/21/2023, 1:34 PMadds a little bit of roundtrip time, but that would probably be the best solution
c
Crembo
05/21/2023, 1:34 PMthe obfuscation should be in the actual database to mean anything honestly
Crembo
05/21/2023, 1:35 PMI was planning on making it a generated column and then
REVOKEr
Razoth
05/21/2023, 1:35 PMyou can do postgres functions too, but i don't know how resource intensive they are.
c
Crembo
05/21/2023, 1:36 PMmy last resort really is going to be adding a "short_id" column with a randomly generated nanoid (will need a function to retry on collision), index that, and do a bunch of nonsense to make sense of many-to-many relations
Crembo
05/21/2023, 1:36 PMprobably revoke all direct access to the n:n tables, only through stored procedures
r
Razoth
05/21/2023, 1:46 PMmhhhh, if you use something unix time based, and work something in the to obfuscate it further you don't need the collision check.
Razoth
05/21/2023, 1:47 PMother than that, no idea anymore 😄 i am pretty sure i've gone over everything i can think of
c
Crembo
05/21/2023, 1:49 PMyeah the biggest baddest issue is keep it alphanumeric and 5-10 characters
Crembo
05/21/2023, 1:49 PMand ideally without vowels so you cannot accidentally form swear words on a purely statistical basis
Crembo
05/21/2023, 1:50 PMUUIDs are too long to make good URLs because they 1) don't all get selected when you double click 2) no way are you gonna be able to say one on the phone or what have you
r
Razoth
05/21/2023, 1:53 PMunique string based on unix time, switch it around a bit, like splitting and moving around, hash it, remove vovels. done?
Razoth
05/21/2023, 1:54 PMshould be doable in a postgres function
c
Crembo
05/21/2023, 1:56 PMCrembo
05/23/2023, 1:43 PMI ended up doin something a little silly
Crembo
05/23/2023, 1:43 PM Copy code
CREATE OR REPLACE function unique_code_for(tbl regclass)
RETURNS text
LANGUAGE plpgsql
volatile
AS
$$
DECLARE
new_shortcode text;
exists boolean;
BEGIN
LOOP
new_shortcode := nanoid(8, '0123456789BCDFGHJKLMNPQRSTVWXYZbcdfghjklmnpqrstvwxyz');
EXECUTE 'SELECT EXISTS (SELECT * from ' || tbl || ' WHERE shortcode = ''' || new_shortcode || ''')' INTO exists;
IF exists = false THEN
RETURN new_shortcode;
END IF;
END LOOP;
END;
$$Crembo
05/23/2023, 1:44 PMand the tables have a
shortcode text default unique_code_for('tablenamehere')unique