Get a list of all accounts that currently have an access key matching a given public key

[pkudinov]

Get a list of all accounts that currently have an access key matching a given public key

This is done by joining access_keys and accounts tables to find current on-chain accounts that have an access_keys entry for the provided public key right now.

There is no specific receiverId or publicKey to be tracked -- this is a general purpose query that allows consumers of the API to discover all accounts that have a given public key added to them.
For example, if I secure an account (or multiple accounts) with a Ledger device, most wallet UI’s only request the public key of the ledger, then use that public key to find all accounts that are secured on-chain using that key — it could be 1, 2, 10 or more accounts that are all controlled by the same public key. For e.g. NEAR wallet, entering a seed phrase may import multiple accounts if someone has used the same seed phrase to secure more than one account, by way of the same querying.
This is a critical building block for NEAR in particular because, unlike chains where the account ID can be computed from the public key (e.g. implicit-only accounts), NEAR has no such guarantees for named accounts and any number of accounts can be secured by the same keypair. Also, many NEAR users with implicit accounts may not even know their implicit account IDs — all they know is that their seed phrase or ledger device automatically ‘discovers’ the appropriate account. I think this one should be part of the basic enhanced API spec rather than a custom indexer since it is so commonly required for account discovery.

Link to SQL Query: https://github.com/near/near-contract-helper/blob/master/src/middleware/indexer.js#L97-L108

[eduohe]

One of the steps of this ticket is the Manual Backfill

Goal:
Avoid the QueryAPI native backfill since genesis that can take a very long time to process.

How:

1. Create a script to run a SQL in the Explorer PostgreSQL DB (that joins access_keys and accounts tables) and exports the data into a TSV
2. Create a script to copy the data from the TSV and insert into the QueryAPI PostgreSQL DB.
3. Use a GCP VM in the same region of QueryAPI PostgreSQL DB

Details:
We implemented the first version on the QueryAPI dev environment. All the steps including VM setup are described here: https://4221960800361869.9.gcp.databricks.com/?o=4221960800361869#notebook/2188906080660589/command/1330040471488534

Table DDL:

-- to speed up the COPY process PK and INDEXES will be created later 
CREATE TABLE
  "access_keys_v1" (
    "public_key" text NOT NULL,
    "account_id" text NOT NULL,
    "created_by_receipt_id" text NULL,
    "deleted_by_receipt_id" text NULL,
    "account_deleted_by_receipt_id" text NULL,
    "permission_kind" text NOT NULL,
    "last_updated_block_height" numeric(20) NOT NULL,
    "block_timestamp_utc" timestamp,
    "indexed_at_timestamp_utc" timestamp,
    "indexed_lag_in_seconds" numeric(20)
  );

CREATE INDEX
  access_keys_v1_account_id_idx ON access_keys_v1 USING btree (account_id);

CREATE INDEX
  access_keys_v1_last_updated_block_height_idx ON access_keys_v1 USING btree (last_updated_block_height);

CREATE INDEX
  access_keys_v1_public_key_idx ON access_keys_v1 USING btree (public_key);
 
ALTER TABLE access_keys_v1 ADD PRIMARY KEY ("public_key", "account_id");

Scripts:

• Export

import os
import psycopg2
import datetime

driver = "org.postgresql.Driver"

explorer_db_config = {
  "host": "35.240.76.233",
  "port": "5432",
  "database": "mainnet_explorer",
  "user": "mainnet",
  "password": os.environ["pg_explorer_mainnet_user_pwd"],
  "url": "jdbc:postgresql://35.240.76.233:5432/mainnet_explorer",
}

db_conn = psycopg2.connect(dbname=explorer_db_config["database"],\
                           user=explorer_db_config["user"],\
                           password=explorer_db_config["password"],\
                           host=explorer_db_config["host"],\
                           port=explorer_db_config["port"])

cursor = db_conn.cursor()

sql = """
          COPY (
            SELECT
                ak.public_key,
                ak.account_id,
                ak.created_by_receipt_id,
                ak.deleted_by_receipt_id,
                a.deleted_by_receipt_id as account_deleted_by_receipt_id,
                ak.permission_kind,
                ak.last_update_block_height as last_updated_block_height,
                to_timestamp(b.block_timestamp / 1000000000) as block_timestamp_utc,
                to_timestamp(b.block_timestamp / 1000000000) as indexed_at_timestamp_utc,
                0 as indexed_lag_in_seconds
              FROM access_keys ak
              JOIN accounts a ON ak.account_id = a.account_id
              LEFT JOIN blocks b ON b.block_height = ak.last_update_block_height
              ORDER BY account_id
          ) TO stdout CSV HEADER DELIMITER E'\t'
        """

print(datetime.datetime.now())

with open('/mnt/disks/disk-1/queryapi/data.csv', 'w+') as fp:
    cursor.copy_expert(sql, fp)

print(datetime.datetime.now())

• Import

import os
import psycopg2
import datetime

driver = "org.postgresql.Driver"

queryapi_db_config = {
  "host": "35.233.16.174",
  "port": "5432",
  "database": "eduohe_near",
  "user": "eduohe_near",
  "password": os.environ["pg_queryapi_eduohe_near_dev_user_pwd"],
  "url": "jdbc:postgresql://35.233.16.174:5432/eduohe_near",
}

db_conn_queryapi_db = psycopg2.connect(dbname=queryapi_db_config["database"],\
                           user=queryapi_db_config["user"],\
                           password=queryapi_db_config["password"],\
                           host=queryapi_db_config["host"],\
                           port=queryapi_db_config["port"])

cursor_queryapi_db = db_conn_queryapi_db.cursor()

cursor_queryapi_db.execute("TRUNCATE eduohe_near_access_keys_v1.access_keys")

sql = """
      COPY eduohe_near_access_keys_v1.access_keys
      FROM STDIN WITH CSV HEADER DELIMITER E'\t'
     """

print(datetime.datetime.now())

with open("/mnt/disks/disk-1/queryapi/data.csv", "r") as f:
    cursor_queryapi_db.copy_expert(sql, f)

cursor_queryapi_db.execute("COMMIT")

print(datetime.datetime.now())

cc @darunrs @pkudinov

[eduohe]

This ticket is waiting for QueryAPI performance improvements.