a better option might be nanoid like
# off-topic
c
a better option might be nanoid, like this one https://github.com/viascom/nanoid-postgres/blob/main/nanoid.sql
r
continue here please 😄
c
right
I suppose using uuid v4's as the primary key is an option
what does that do to database perf these days? googling info gives WILDLY different results
the gamut is "never do it YOU WILL EXPLODE" all the way to "it's fine"
r
honestly don't know about which version supabases uses.
but it uses the UUID-OSSP plugin, so you could prob use v5
c
it's mostly the perf issues of a primary key that isn't sequential
r
what are using for the frontend?
c
sveltekit
r
you 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.
security by obscurity.
c
since the client is talking directly to the database over postgREST, the database needs to be in on things on its end..
r
why?
maybe 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
because, 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
and if I do THAT, I lose out on a lot, y'know?
r
AHHHHHH client side. i'm so sorry. 😄
nextjs can render serverside or client side, depending on what you want and need. i didnt get that.
c
yeah, once the page has been initially rendered and delivered the client, the client can then do a lot of the work
and if the client can talk to supabase directly instead of having to detour via my server..
r
isn't there something like an API route on swelte that runs server side?
c
yes, there is
r
then handle it by "proxy-ing" through the api route?
c
sure, but you've added the extra roundtrip, and theoretically the endpoint is still open to the internet
unless you decide to only allow the service user role
r
you could also make an account for the API route and and use RLS
c
the 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
what about.... edge functions!
adds a little bit of roundtrip time, but that would probably be the best solution
c
the obfuscation should be in the actual database to mean anything honestly
I was planning on making it a generated column and then
REVOKE
-ing select privileges for the real key to the anon and authed roles
r
you can do postgres functions too, but i don't know how resource intensive they are.
c
my 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
probably revoke all direct access to the n:n tables, only through stored procedures
r
mhhhh, if you use something unix time based, and work something in the to obfuscate it further you don't need the collision check.
other than that, no idea anymore 😄 i am pretty sure i've gone over everything i can think of
c
yeah the biggest baddest issue is keep it alphanumeric and 5-10 characters
and ideally without vowels so you cannot accidentally form swear words on a purely statistical basis
UUIDs 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
unique string based on unix time, switch it around a bit, like splitting and moving around, hash it, remove vovels. done?
should be doable in a postgres function
I ended up doin something a little silly
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;
$$
and the tables have a
shortcode text default unique_code_for('tablenamehere')
column constrained
unique