feat: use user tags to check PSA auth

[alexandrastoica]

Removes the old pinning_authorization table and uses user tags instead

[alexandrastoica]

SQL for migrating the existing data from pinning_authorization to user_tag

BEGIN TRANSACTION;
INSERT INTO public.user_tag
(
    user_id,
    tag,
    value,
    reason,
	inserted_at
)
SELECT
     user_id,
     'HasPsaAccess' as tag,
     'true' as value,
     'Approved access' as reason,
	 inserted_at
 FROM public.pinning_authorization
 WHERE deleted_at IS NULL;
 
INSERT INTO public.user_tag
(
    user_id,
    tag,
    value,
    reason,
	inserted_at,
	deleted_at
)
SELECT
     user_id,
     'HasPsaAccess' as tag,
     'false' as value,
     'Revoked access' as reason,
	 inserted_at,
	 deleted_at
 FROM public.pinning_authorization
 WHERE deleted_at IS NOT NULL;

DROP TABLE public.pinning_authorization;
COMMIT;

[alanshaw]

@alexandrastoica could you add the migration SQL to here please? https://github.com/web3-storage/web3.storage/tree/main/packages/db/postgres/migrations

[flea89]

@alanshaw thanks for pointing that out! We noticed this morning that folder was created 🎉

While I understand this is not the full, polished solution for migrations, (since we're still missing the automation side of it) I do believe moving migrations scripts to version control is a great improvement to what we're doing now.
So big plus and thanks 🙏 !

I'm wondering if we should be adding some documentation to make sure developers know what's expected when altering schemas?
Created a minimal PR.

Do we have an issue tracking the bigger DB migration epic/approach?

[alexandrastoica]

@alanshaw added the migration file. Let me know if this is ok to merge. Thanks!

Made the changes requested. Thanks!

[alanshaw]

LGTM

[vasco-santos]

Runing SQL migration for deploy got:

web3-storage-prod::HEROKU_POSTGRESQL_AQUA=> INSERT INTO public.user_tag
(
    user_id,
    tag,
    value,
    reason,
    inserted_at
)
SELECT
    user_id,
    'HasPsaAccess' as tag,
    'true' as value,
    'Approved access' as reason,
    inserted_at
 FROM public.pinning_authorization
 WHERE deleted_at IS NULL;
ERROR:  invalid input value for enum user_tag_type: "HasPsaAccess"
LINE 11:     'HasPsaAccess' as tag,

Looking into it

cc @alexandrastoica

[flea89]

@vasco-santos the user_tag enum was created (and values updated) as part of 2 separate PRs from trigram folks, is it possible they haven't created a migration to create the ENUM?

-- User tags are associated to a user for the purpose of granting/restricting them
-- in the application.
CREATE TYPE user_tag_type AS ENUM
(
  'HasAccountRestriction',
  'HasPsaAccess',
  'StorageLimitBytes'
);

[vasco-santos]

@flea89 thanks, you were right! The type was outdated and it was merged without updating the type. We need to improve communication on DB changes

[ghost]

@flea89 thanks, you were right! The type was outdated and it was merged without updating the type. We need to improve communication on DB changes

@vasco-santos i'll take ownership for this in this instance. if you recall the conversations we were having a few weeks ago about naming... i meant to follow up once we were all in agreement and the conversations had died down to avoid unnecessary DB updates in prod, but i forgot to do so. i apologize for that.

i also feel this brings to light the need for an automated migration strategy as part of our deployments.