regen-network/regen-server

:seedling: Regen web server and database

regen-server's Issues

Protect POST /iri-gen with API key

After a brief convo with @clevinson in today's engineering check-in, we would like to implement some lightweight auth for the /iri-gen endpoint.
We would like to require that developers who want to use that API, for example, sign-up with an email address.
I wanted to open this ticket as a place to discuss some ideas for this.

Cory and I discussed some light-weight solutions, and we agree it's good to keep this simple for now.
The main concern is just to prevent DoS or huge amount of data being input into our database via /iri-gen.
By having a sign-up and API keys, we can track who's doing what, and also can implement some rate-limiting.

The first simple solution is an email sign-up page. If a developer wants to POST data, they submit a request to the sign-up form. After confirming their email address we generate an API key, and record there email/key in the DB. Subsequent requests to /iri-gen would verify that a valid API key is present in the request, and if not, abort with status 401.

A second idea, is that we can have a keplr address sign-up. This is nice because then we don't need email confirmation. The user would go to sign-up page, and after clicking "sign-up", we invoke the signArbitrary API of the keplr wallet. After verifying the signature, we create the API key and save it the DB.

In either of these scenarios, we would need a front-end adjustment. Because the UI does make use of /iri-gen. We would need to adjust the API call there to use withCredentials. This would allow the UI to interact with that API in secure way, while at the same time, not requiring an API key to be present in the app.

So the adjustments to the /iri-gen endpoint would need to be at least two-fold. We would require one of these two checks to pass:

1. The API key is valid
2. It's a credentialed request (it's a request coming from an authenticated user session in the app)

An alternative solution, could be to automatically generate an API key for users that sign-in to our application. This API key could be rendered in the portfolio/profile page. This has the benefit of not requiring an extra signature, aside from signing in the app. But this might clutter the UI or confuse users.

WDYT @clevinson ? do you have any other ideas?

/cc @blushi

Add an endpoint to verify a signature and connect a wallet address to an existing account

We can't use our /marketplace/v1/web3auth/login endpoint and keplr strategy as is for connecting a wallet address to an existing (web2) account since it will create a new account if it cannot find an account with the given address.
In order to implement this, we need a new endpoint that will verify a signature and assign the addr to the existing account with provided email.

Update s3 credentials for the files endpoint in staging

Currently, the staging environment returns a 500 error when using the files endpoint:

2023-03-21T14:30:04.790767+00:00 heroku[router]: at=info method=POST path="/files" host=api-staging.registry.regen.network request_id=effad66a-187e-41
f3-a464-cec481822e88 fwd="90.126.106.114" dyno=web.1 connect=0ms service=101ms status=500 bytes=532 protocol=https
2023-03-21T14:30:03.135326+00:00 heroku[web.1]: Process running mem=1056M(102.8%)                                                                     
2023-03-21T14:30:03.137252+00:00 heroku[web.1]: Error R14 (Memory quota exceeded)                                                                     
2023-03-21T14:30:04.790378+00:00 app[web.1]: registry-server: s3 Error AccessDenied: Access Denied                                                    
2023-03-21T14:30:04.790415+00:00 app[web.1]: registry-server:     at Request.extractError (/app/node_modules/aws-sdk/lib/services/s3.js:710:35)       
2023-03-21T14:30:04.790451+00:00 app[web.1]: registry-server:     at Request.callListeners (/app/node_modules/aws-sdk/lib/sequential_executor.js:106:2
0)
2023-03-21T14:30:04.790473+00:00 app[web.1]: registry-server:     at Request.emit (/app/node_modules/aws-sdk/lib/sequential_executor.js:78:10)
2023-03-21T14:30:04.790493+00:00 app[web.1]: registry-server:     at Request.emit (/app/node_modules/aws-sdk/lib/request.js:686:14)
2023-03-21T14:30:04.790510+00:00 app[web.1]: registry-server:     at Request.transition (/app/node_modules/aws-sdk/lib/request.js:22:10)
2023-03-21T14:30:04.790530+00:00 app[web.1]: registry-server:     at AcceptorStateMachine.runTo (/app/node_modules/aws-sdk/lib/state_machine.js:14:12)
2023-03-21T14:30:04.790550+00:00 app[web.1]: registry-server:     at /app/node_modules/aws-sdk/lib/state_machine.js:26:10
2023-03-21T14:30:04.790569+00:00 app[web.1]: registry-server:     at Request.<anonymous> (/app/node_modules/aws-sdk/lib/request.js:38:9)
2023-03-21T14:30:04.790597+00:00 app[web.1]: registry-server:     at Request.<anonymous> (/app/node_modules/aws-sdk/lib/request.js:688:12)
2023-03-21T14:30:04.790610+00:00 app[web.1]: registry-server:     at Request.callListeners (/app/node_modules/aws-sdk/lib/sequential_executor.js:116:1
8) {
2023-03-21T14:30:04.790643+00:00 app[web.1]: registry-server:   code: 'AccessDenied',
2023-03-21T14:30:04.790660+00:00 app[web.1]: registry-server:   region: null,
2023-03-21T14:30:04.790684+00:00 app[web.1]: registry-server:   time: 2023-03-21T14:30:04.782Z,
2023-03-21T14:30:04.790703+00:00 app[web.1]: registry-server:   requestId: '804GGBMQYD7H9DQN',
2023-03-21T14:30:04.790723+00:00 app[web.1]: registry-server:   extendedRequestId: 'w0Z8CdFV4xMmkd+Q95rsgIGLW9h5zpm8eQn2rVgX/+yPJEMA9/UEsHWtLQYkYr4ZPP
6Xl/pbzUo=',
2023-03-21T14:30:04.790741+00:00 app[web.1]: registry-server:   cfId: undefined,
2023-03-21T14:30:04.790767+00:00 app[web.1]: registry-server:   statusCode: 403,
2023-03-21T14:30:04.790779+00:00 app[web.1]: registry-server:   retryable: false,
2023-03-21T14:30:04.790811+00:00 app[web.1]: registry-server:   retryDelay: 1.1237871116859033
2023-03-21T14:30:04.790830+00:00 app[web.1]: registry-server: }

It seems like it's an issue with the credentials, so this task is to update those.

Support private columns for the account table

As part of the web2 auth support (email through passcode and google initially), we added an email and google columns to the account table for storing those info.
But currently any app_user is granted SELECT access on all columns on the account table while these columns should remain private and only be available for the authenticated accounts.

We have 2 options to implement this:

• either create a separate private.account table with those email and google columns only and return those info as part of the GET /accounts endpoint in addition to the active and authenticated accounts ids ;
• or only GRANT SELECT to app_user on the public account columns (all except email and google) and create a security definer function that would return an account by id with all columns available for any authenticated accounts. This would give us a graphql query through postgraphile, allowing us to keep using the same generated types on the front-end.

ref: #404 (comment)

Is lucia compatible with our existing passport middleware?

Summary

As a result of regen-network/regen-web#2073, we are considering a custom authentication service that leverages lucia (a lightweight authentication library). We need to assess compatibility with our existing express passport middleware.

Fix local development setup for indexer database

Currently, the local development setup does not account for the newly added connection to the indexer database.
At present, the current local dev setup will not work (the dockerized setup).
We need to adjust the code base so that we provision an extra database for the indexer, which gets all indexer migrations applied to it.
Without this the application will be tricky to develop locally.

/cc @ryanchristo @blushi

Add instructions for how to manually add a profile

We want to document the process of how to edit a profile manually.
Most commonly this happens when we have a multisig account that cannot use the login system.
Several weeks ago we discussed that we can just add this as a markdown doc here in the regen-server docs:
https://github.com/regen-network/regen-server/tree/dev/docs
This guide will show how to create an account in the database as well as how to make updates to the relevant parts of the corresponding profile in the database.
Typically when I have done this I also need to manually upload profile and background images to S3.
This will document that as well.

Adjust project table to store the admin party id

Currently we map a project to its admin using admin_wallet_id column which references a wallet address, mimicking what we have on chain.
But in order to support off chain projects created by web2 users, we need to adjust that since they don't have any wallet address (yet).
We have 2 options here:

• either keep the current admin_wallet_id column and add a admin_party_id column that would only reference web2 users. This would support the following edge case scenario:#375 (comment)
• or get rid of the current admin_wallet_id column and add a admin_party_id column that would reference any kind of users

Define user account model with web2 login support

Summary

With the addition of web2 authentication (regen-network/regen-web#2071), we are re-evaluating our user account model.

In the current implementation, an account can be linked to one or more party. Each party the account is linked to represents an individual or an organization, i.e. each party represents a single identity (i.e. an account can have multiple identities).

If the party represents an individual, the party is not linked to an organization. If the party represents an organization, the party is linked to an organization. Each party is linked to a wallet, therefore each wallet also represents an identity.

Questions:

• If the user logs in with web2 authentication (no wallet), how do we link a party to the user?
• Should a single account be linked to one or more party and therefore one or more wallet as we have currently?

Diagram (Current)

Document google and passcode authentication strategies

As part of #400 and #402, we've added support for logging in via google or a verification/passcode sent via email. We should document the different endpoints available for that in our existing login-system.md

HEIC format support for proxying images through sharp

As part of #427, we're proxying any image from a project post with private data locations through our sharp-express middleware so that the resulting available image doesn't include any metadata from the original file.

But sharp doesn't support HEIC by default, although lipvips (used internally by sharp) does: lovell/sharp#3680
So it looks like it can be supported with a custom build of libvips.
https://sharp.pixelplumbing.com/install#custom-libvips

ref: #427 (comment)

Explore tools for generating docs for the database

It'd be nice to have generated documentation for our database.
I.e. a tool like schemaspy provides a webpage where various aspects of the db are documented.

Ideally the tool we choose to use would support documenting policies.
This task is to explore some tools for generating docs, to determine which one best suites our needs.

As I see it our needs for this are:

• docs for tables, columns, constraints, views, functions, policies and if row-level security is enabled on a table.
• ERD for a particular table, as well as ERD for the whole schema
• an ability to generate docs for different schema's, i.e. we use public and private.

A secondary need is that we can build these docs ideally for both dev and master when new commits are added to those branches. But starting with just master would be fine.

This task should also figure out some deployment options. Maybe github pages, maybe netlify, maybe gitbook..

Uploading jpg to /files for profiles fails

When testing the add address to account PR, I noticed that somehow AWS S3 returns this when a error profile image is a jpg, but if it's jpeg, it's fine. Maybe mimetype issues?

Here's the cryptic error from AWS in the registry-server.

2023-05-10T21:31:40.982001+00:00 app[web.1]: server: s3 Error MalformedXML: The XML you provided was not well-formed or did not validate against our published schema
2023-05-10T21:31:40.982037+00:00 app[web.1]: server:     at Request.extractError (/app/node_modules/aws-sdk/lib/services/s3.js:710:35)
2023-05-10T21:31:40.982059+00:00 app[web.1]: server:     at Request.callListeners (/app/node_modules/aws-sdk/lib/sequential_executor.js:106:20)
2023-05-10T21:31:40.982076+00:00 app[web.1]: server:     at Request.emit (/app/node_modules/aws-sdk/lib/sequential_executor.js:78:10)
2023-05-10T21:31:40.982096+00:00 app[web.1]: server:     at Request.emit (/app/node_modules/aws-sdk/lib/request.js:686:14)
2023-05-10T21:31:40.982113+00:00 app[web.1]: server:     at Request.transition (/app/node_modules/aws-sdk/lib/request.js:22:10)
2023-05-10T21:31:40.982132+00:00 app[web.1]: server:     at AcceptorStateMachine.runTo (/app/node_modules/aws-sdk/lib/state_machine.js:14:12)
2023-05-10T21:31:40.982150+00:00 app[web.1]: server:     at /app/node_modules/aws-sdk/lib/state_machine.js:26:10
2023-05-10T21:31:40.982167+00:00 app[web.1]: server:     at Request.<anonymous> (/app/node_modules/aws-sdk/lib/request.js:38:9)
2023-05-10T21:31:40.982187+00:00 app[web.1]: server:     at Request.<anonymous> (/app/node_modules/aws-sdk/lib/request.js:688:12)
2023-05-10T21:31:40.982204+00:00 app[web.1]: server:     at Request.callListeners (/app/node_modules/aws-sdk/lib/sequential_executor.js:116:18) {
2023-05-10T21:31:40.982223+00:00 app[web.1]: server:   code: 'MalformedXML',
2023-05-10T21:31:40.982240+00:00 app[web.1]: server:   region: null,
2023-05-10T21:31:40.982258+00:00 app[web.1]: server:   time: 2023-05-10T21:31:40.979Z,
2023-05-10T21:31:40.982278+00:00 app[web.1]: server:   requestId: 'RFM8WAQ13WTT7Z6W',
2023-05-10T21:31:40.982296+00:00 app[web.1]: server:   extendedRequestId: 'qYNk+Mof15edqupogakiFgNaPNcD0dhLlFrFy8cDZvkYmOsdHBIeC79iLMjXvo871ez8A/X7V+Q=',
2023-05-10T21:31:40.982314+00:00 app[web.1]: server:   cfId: undefined,
2023-05-10T21:31:40.982333+00:00 app[web.1]: server:   statusCode: 400,
2023-05-10T21:31:40.982350+00:00 app[web.1]: server:   retryable: false,
2023-05-10T21:31:40.982369+00:00 app[web.1]: server:   retryDelay: 43.03713501525477
2023-05-10T21:31:40.982386+00:00 app[web.1]: server: }

/cc @blushi could you see if you can repro this?

Implement endpoint to connect and disconnect to/from google

Implement:

• an endpoint to connect an existing account to google (whether it's a web2 or web3 account). We should probably implement a new google passport strategy for this purpose, in particular to make it work with existing web3 accounts, or try to pass some params to our existing google auth endpoints.
• an endpoint to disconnect an existing account from google. Internally it should set the account google value to null.

update CORS rules in based on new netlify urls for deploy previews

since we made the name change for registry to marketplace in the regen-web repo, the deploy preview url's have changed and because of that deploy previews are receving CORS errors from the regen-server.

we need to update the CORS regex pattern to allow the new deploy previews.

Add support for multiple logins to user sessions

We want to adjust the login system so that within a single user session cookie we have:

1. an active user
2. a list of active users

We want to have an endpoint that returns the current active user as an object from the session.
This endpoint should probably also return the list of active users.

We want to have an endpoint that switches the current active user in the session.
The active users list needs to be updated each time a particular login occurs.
This means for any supported login method; keplr, google, magic link etc. the list always has to be updated.
Additionally, the switch must check that user that is being switched is within the active users list.
Without such a check, there is a vulnerability.

For this task, we can pick a particular login type to support.
For now, it's probably simplest just to get this working with magic links.
Because the data model is going to be changing as it relates to keplr logins, we should wait for that.
This task doesn't have to be blocked if we just get things working with multiple magic logins.
And at that point it can be extended for the other login methods which are still under development and added for keplr once the data model is solid.

To summarize, this task can be thought of as handling these issues for magic links:

1. adjust the session cookie to have active user and active users list
2. create an API endpoint that allows setting the "active user" in session cookie
3. create an API endpoint that returns active user and active users list from the session cookie (via req.session)

See this related task for much more detailed but not summarized discussion.
See this experimental PR that does some of what the goals are for this task.

Implement OTPW authentication using passport

The first web2 authentication option should be based on a one-time password sent via email, similar to the UX provided by https://logto.io/products/passwordless

We need to implement a new passport strategy for this. It doesn't seem like there's any existing open source passport strategy for this. There is https://www.passportjs.org/packages/passport-totp/ but that is meant for 2FA usage, meaning the code is generated by external applications.
If we need to implement a custom strategy, we already have our keplrStrategy that could serve as an example.
Some other ressources that could be helpful:

• https://github.com/nickbalestra/zero (a bit outdated)
• https://github.com/florianheinemann/passwordless/

We'll need 2 endpoints that should be added to our existing /marketplace/v1/auth route:

1. one for sending the email with the OTP
   The email sent to the user with the code should look like this:
   https://www.figma.com/file/Sb9BavGvlCFPQFpjdDlwsL/Web-2.0-log-in-and-actions?type=design&node-id=1514-75392&mode=design&t=GUYNDxgbImiKnEZ4-0
   We already have MJML setup for building such emails and a very similar one was already built: https://github.com/regen-network/regen-server/blob/5cf07ee3b81d218fc05318dce17dc39a838ea291/server/worker/templates/login_with_magic_link.mjml
   This is how we can send emails: https://github.com/regen-network/regen-server/pull/389/files#diff-827bcdf6d2361844ec855ffe61e6474e2ffaed4721a9c56f053c568b05239880R19-R32
2. another endpoint for verifying the code and logging in the user

Define organization model and permissions architecture

Summary

This issue is an initial research task part of an epic to add support for organizations and off-chain permissions. Some of this is already implemented (on the individual account level, i.e. for managing a project page) but we need to start thinking about how this would be updated to support multiple admin (or other levels of permissions) within the context of an organization.

The solution for organizations and permissions will be influenced by #375 but we should be able to start defining the organization and permissions architecture alongside this work. The goal is to have an organization model that supports permissions, for example, the ability to edit a project page (the off-chain data) as a member of an organization managing a project page.

One of the more challenging components to this is providing a good user experience when multiple levels of permissions are present (i.e. on-chain permissions as a member of a group and off-chain permissions as a member of an organization).

Also, who has the ability to post updates in the organization profile? Is it restricted to the organization admin, any member of the organization, selected members of the organization? What levels of permissions would we need for MVP?

Add e2e tests for magic link authentication

We should write e2e tests for magic link authentication, that check or validate some of the setup and expected outcomes, similar to what we have for the web3 login e2e tests as we've been calling them (like those in server/__tests__/e2e/web3auth.*.test.ts).
In this case, the complicated part would be mocking the AWS SES service.

ref: #389 (comment)

Error when trying to log in with google when google email is used by another user as main email

Describe the bug
Getting internal server error when trying to log in with google when google email is used by another user as main email.

To Reproduce

1. Log in with email using some google email address (account A)
2. Log out then log in with Keplr to another account (account B)
3. From the settings page, connect to google using the same email address as in 1.
4. Log out and try to log in via google with this email address, you will get an internal server error

Expected behavior
You should be logged in with account B.

---

For Admin Use

• Not duplicate issue
• Appropriate labels and zenhub epics applied
• Appropriate contributors tagged

Uniqueness across Google email and main email

We should have uniqueness across Google email and main email, meaning that if an account has addr A as main email and addr B as google email then no other account can have addr A or addr B as main email or google email.
When connecting to google, set main email to google email if not already set (that can happen for web3 users connecting to google)

Fix postgraphile connection to indexer db so it can detect schema changes

When doing a recent deployment, I noticed that the newly added postgraphile connection to indexer database did not detect a schema change in the indexer database.
This required restarting the staging server.
This task is to fix the root cause issue of this which I found in the heroku staging logs as:

2023-06-28T15:14:15.854248+00:00 app[web.1]: server: Failed to setup watch fixtures in Postgres database ️️⚠️
2023-06-28T15:14:15.854298+00:00 app[web.1]: server: This is likely because the PostgreSQL user in the connection string does not have sufficient privileges; you can solve this by passing the 'owner' connection string via '--owner-connection' / 'ownerConnectionString'. If the fixtures already exist, the watch functionality may still work.
2023-06-28T15:14:15.854320+00:00 app[web.1]: server: Enable DEBUG='graphile-build-pg' to see the error
2023-06-28T15:14:15.854338+00:00 app[web.1]: server: 2023-06-28T15:14:14.599Z graphile-build-pg error: permission denied to create event trigger "postgraphile_watch_ddl"
2023-06-28T15:14:15.854359+00:00 app[web.1]: server:     at Parser.parseErrorMessage (/app/node_modules/pg-protocol/src/parser.ts:369:69)
2023-06-28T15:14:15.854378+00:00 app[web.1]: server:     at Parser.handlePacket (/app/node_modules/pg-protocol/src/parser.ts:188:21)
2023-06-28T15:14:15.854393+00:00 app[web.1]: server:     at Parser.parse (/app/node_modules/pg-protocol/src/parser.ts:103:30)
2023-06-28T15:14:15.854408+00:00 app[web.1]: server:     at TLSSocket.<anonymous> (/app/node_modules/pg-protocol/src/index.ts:7:48)
2023-06-28T15:14:15.854424+00:00 app[web.1]: server:     at TLSSocket.emit (node:events:513:28)
2023-06-28T15:14:15.854437+00:00 app[web.1]: server:     at TLSSocket.emit (node:domain:489:12)
2023-06-28T15:14:15.854455+00:00 app[web.1]: server:     at addChunk (node:internal/streams/readable:315:12)
2023-06-28T15:14:15.854469+00:00 app[web.1]: server:     at readableAddChunk (node:internal/streams/readable:289:9)
2023-06-28T15:14:15.854483+00:00 app[web.1]: server:     at TLSSocket.Readable.push (node:internal/streams/readable:228:10)
2023-06-28T15:14:15.854498+00:00 app[web.1]: server:     at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)

Anchor data on-chain when uploading files and posts

When we store JSON-LD posts and their attached media files we should anchor the data on-chain using a hot wallet with a minimum balance of REGEN just to be used for anchoring data.

Track per account storage usage of media

For #418 and other data uploads, we want to track per account storage usage of any media and data uploads and tally it against a quota.

There are two general approaches we can take:

• keep track of the storage in some table and add/remove from the total whenever data is uploaded/deleted
• dynamically calculate the storage usage based on scanning postgres and S3 buckets

Implement twitter authentication using passport

For authentication, we've decided to build a custom solution on top of passport.

The second web2 authentication option we would like to support is twitter authentication:

• https://www.passportjs.org/packages/passport-twitter/

In order to implement this, we will need to add twitter to party, which should be unique and nullable.

CORS not enabled for production deploy preview

Summary

When following the release steps for regen-web, there is a step to check the preview before publishing. This preview link is not supported by our current CORS configuration. We may want to consider adding support for it:

CORS configuration:

regen-server/server/Server.ts

Lines 57 to 58 in 9ac1ff7

	const MARKETPLACE_PREVIEW_HOSTNAME_PATTERN =
	/deploy-preview-\d+--regen-marketplace\.netlify\.app$/;

Example of preview link:

https://6509caf51822320008c51f72--regen-marketplace.netlify.app/

Implement google authentication using passport

For authentication, we've decided to build a custom solution on top of passport.

The fourth web2 authentication option we would like to support is google authentication:

• https://www.passportjs.org/packages/passport-google/ (i don't think we want to use the is the openid version)
• https://www.passportjs.org/packages/passport-google-oauth20/

In order to implement this, we will need to add google to party, which should be unique and nullable.

How to manage user sessions for multiple accounts?

Summary

We've discussed the possibility of managing user sessions within the database. This is dependent on what authentication service we choose or whether we build a custom authentication service.

Following regen-network/regen-web#2073, and given user sessions is something we want to manage within the database, what are the requirements? What is the model for user sessions and how are they linked to a user account?

UPDATE

We are considering the use of express middleware (we are currently using express cookie middleware), but we need to first determine whether express middleware supports sessions for multiple accounts. We need to investigate how multiple accounts and multiple sessions work in other applications and determine if express middleware satisfies our needs.

If express middleware supports multiple accounts, a followup issue will be created to switch to express middleware.

Implement linkedin authentication using passport

For authentication, we've decided to build a custom solution on top of passport.

The third web2 authentication option we would like to support is linkedin authentication:

• https://www.passportjs.org/packages/passport-linkedin/ (outdated)
• https://www.passportjs.org/packages/passport-linkedin-oauth2/

In order to implement this, we will need to add linkedin to party, which should be unique and nullable.

Remove some unused worker tasks

Update our database user roles to be based on `party.id` instead of wallet address

In order to define our RLS policies, we rely on:

• the current database user that is currently the current user wallet address
• the account_id set as a session variable which is the id of the aggregation of parties (currently called an account, this is something that we might want to rename separately)

When dealing with web2 users, we can't really use any wallet address so instead we should set the database role name to the party.id which is really the only unique identifier that we have for a user.
We might want to revisit the account_id session variable too but this will likely be part of a separate issue once the preliminary research/POC work in #376 is done.

ref: https://www.notion.so/regennetwork/Engineering-Architecture-2023-09-28-ba776c136c394a59ac8bf370b6ce8abf

Update db reset instructions for local development

I believe we might actually want to update the README accordingly now that we have auth_user role, I guess when resetting the db we should not only drop app_user as currently written but also auth_user, if you only dropped app_user as stated then I guess that might just be the reason for this behavior:

• when dropping app_user I guess it also removes the fact that auth_user is a member of app_user
• when running the migrations from a blank db, app_user is created again (v1_0)
• but since auth_user is still there, v2_10 is skipped, hence GRANT app_user TO auth_user; is not run

https://github.com/regen-network/registry-server/blob/master/README.md#dropping-local-database

/cc @blushi @flagrede

Clean up database model for web2 authentication

As discussed in #375, we should proceed to the following:

1. removal of the account table as it stands today (this might be addressed by #391)
2. renaming of the party table to the account table
3. removal of the wallet table
4. add addr and email(already done) column to the new account table

But 3. and 4. are still being discussed: #375 (comment)

Add e2e test for update party policy

This is to check that the login system, is working correctly with the RLS policies for updating parties/profiles.
We would like a e2e test to check the behavior for the edit profile story.
Currently we have a unit test that issues SQL for testing the policy.
We would like a more broad e2e test, since we are seeing a confusing behavior in the PR for this task:
regen-network/rnd-dev-team#1562

Add openapi API docs for `files` endpoint

It'd be great if this endpoint was documented with openapi:
https://github.com/regen-network/registry-server/blob/master/server/routes/files.ts#L5

Using the same method as what we used for metadata-graph:
https://github.com/regen-network/registry-server/blob/master/server/routes/metadata-graph.ts#L11

It's a bit hidden otherwise, and it'd be nice to see it at this page:
https://api-staging.registry.regen.network/api-docs/

Add approved boolean to project table

A project should be created with approved set to false by default. This field will be manually updated.

Implement per-user storage quotas

When uploading files we should check a user's storage quota before processing the upload. We need to define a reasonable free tier, have a way to increase the storage quota (possibly manually) on a per account basis for our partners and eventually a way to purchase additional storage credits.

Add an email to an existing web3 account

We can't use our /marketplace/v1/auth/passcode/verify endpoint and passcode strategy as is for connecting an email to an existing (web3) account since it will create a new account if it cannot find an account with the given email.
In order to implement this, we need to upgrade our strategy to check for an authenticated web3 account and adapt the logic in verifyPasscode.

Add support for project posts

We should add a new table for storing project posts:

• creation timestamp
• account id who posted it, owner
• project id

The following might be stored as JSON-LD (regen-network/regen-registry-standards#82) in a jsonb column:

• title (110 max char)
• comment (md text)
• files, captions and credits (optional)

Only the project admin is able to create/add/delete posts for a project.

Privacy settings:
The entire post content can be private.
The files can be private.
The files locations can be private.

Implement magic link authentication using passport

For authentication, we've decided to build a custom solution on top of passport.

The first web2 authentication option we would like to support is magic link authentication:

• https://www.passportjs.org/packages/passport-magic-login/

In order to implement this, we will need to add email to party, which should be unique and nullable.

Add URL signing to express-sharp middleware

In order to reduce the attack surface on the server, we could use signed URLs in our express-sharp middleware (imageOptimizer). In particular for getting the URLs for private files.

https://github.com/pmb0/express-sharp?tab=readme-ov-file#url-signing

ref: #427 (comment)

This might have some consequences on the way we use the image optimizer on the front-end size (see https://github.com/regen-network/regen-web/blob/b84410329fe7dd8bb06f3086b59eb211c436e02c/web-components/src/utils/optimizedImageSrc.ts)