DB Best Practices & PostgreSQL Snowflake ID Generator Function

snowflake.sql

CREATE SEQUENCE IF NOT EXISTS public.global_id_sequence;

CREATE OR REPLACE FUNCTION id_generator(OUT result BIGINT) AS $$
DECLARE
    epoch BIGINT := 1610850820000;
    seq_id BIGINT;
    now_millis BIGINT;
    shard_id INT := 1;
BEGIN
    SELECT nextval('public.global_id_sequence') % 1024 INTO seq_id;

    SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
    result := (now_millis - epoch) << 23;
    result := result | (shard_id << 10);
    result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;

Twitter thread: https://twitter.com/benawad/status/1350586766684782592

best practices for protecting your db before deploying:

0. back it up
1. don't expose it to the internet
2. sanitize/escape inputs
3. ensure no sequential IDs
4. Always run up to date version to ensure no vulnerabilities
5. Limit access
6. Separate your database server from your app server
7. If your backing up your db make sure it’s encrypted
8. make sure to turn synchronization off if you use that in dev!
9. Automated snapshots.
10. a strong master password
11. Create an under priviliged user for the services connecting to it.
12. Think about a more resilient rollback strategy if you can handle that from a financial point of view (replication factor, main/replicas, bluegreen, etc)
13. Use SSL certificate to encrypt the connection between your backend and the DB.
14. Specific user with explicit rights for your data layer connection. i.e. dont just use root
15. Use soft delete
16. Have a regular process of testing and certifying said backups work. That way you verify there are good and you know how to quickly recover when something does happen. Rather than figuring it out in a rush then hoping it works. (Experience taught me this, thankfully it worked.)
17. Test that you're able to restore the DB backup (make sure restore of backups are working. sometimes restore breaks if db versions mismatch)
18. Change default ports, and use ssl.
19. Don't run your own server on compute. Use cloud providers like rds or sql from gcloud

For mongodb: https://medium.com/javascript-in-plain-english/common-nodejs-mongo-api-security-problems-and-how-to-overcome-them-548d0137984c