[Bug] Cannot change DB creds for primary shard

[paymog]

Bug report

I'm trying to change the credentials for my primary shard. I have the following config

[general]
query = "readonly.*"

[store]
[store.primary]
connection = "postgresql://root:<redacted>@graph-node-database-cluster.cluster-cn9cukuadcb4.us-west-2.rds.amazonaws.com/postgres"
pool_size = [
    { node = "indexer.*", size = 30 },
    { node = "readonly.*", size = 80 }
]
weight = 0
[store.primary.replicas.repl1]
connection = "postgresql://root:<redacted>@proxy-graph-node-read-only.endpoint.proxy-cn9cukuadcb4.us-west-2.rds.amazonaws.com/postgres"
weight = 1
[store.secondary]
connection = "postgresql://postgres:<redacted>@graph-node-rds.cn9cukuadcb4.us-west-2.rds.amazonaws.com/postgres"
pool_size = [
    { node = "indexer.*", size = 100 },
    { node = "readonly.*", size = 80 }
]
weight = 1

and when I change it to the following (note that root was replaced with admin for the primary shard but nothing changed for the secondary shard)

[general]
query = "readonly.*"

[store]
[store.primary]
connection = "postgresql://admin:<redacted>@graph-node-database-cluster.cluster-cn9cukuadcb4.us-west-2.rds.amazonaws.com/postgres"
pool_size = [
    { node = "indexer.*", size = 30 },
    { node = "readonly.*", size = 80 }
]
weight = 0
[store.primary.replicas.repl1]
connection = "postgresql://admin:<redacted>@proxy-graph-node-read-only.endpoint.proxy-cn9cukuadcb4.us-west-2.rds.amazonaws.com/postgres"
weight = 1
[store.secondary]
connection = "postgresql://postgres:<redacted>@graph-node-rds.cn9cukuadcb4.us-west-2.rds.amazonaws.com/postgres"
pool_size = [
    { node = "indexer.*", size = 100 },
    { node = "readonly.*", size = 80 }
]
weight = 1

I get an error about the admin user not existing when running migrations.

Relevant log output

❯❯❯ klf proofofplay-query-graph-node-656f87688b-bkmqz
May 24 09:14:13.317 INFO Graph Node version: 0.29.0 (2022-12-05)
May 24 09:14:13.317 WARN GRAPH_POI_ACCESS_TOKEN not set; might leak POIs to the public via GraphQL
May 24 09:14:13.317 INFO Reading configuration file `/etc/graphnode.toml`
May 24 09:14:13.318 WARN No fork base URL specified, subgraph forking is disabled
May 24 09:14:13.318 INFO Starting up
May 24 09:14:13.318 INFO Trying IPFS node at: http://mercury-ipfs-ipfs-service:5001/
May 24 09:14:13.325 WARN Expensive queries file not set to a valid file: /etc/graph-node/expensive-queries.txt
May 24 09:14:13.326 DEBG Cleaning up large notifications after about 300s, channel: store_events, component: NotificationListener
May 24 09:14:13.329 INFO Successfully connected to IPFS node at: http://mercury-ipfs-ipfs-service:5001/
May 24 09:14:13.343 INFO Connecting to Postgres, weight: 0, conn_pool_size: 80, url: postgresql://admin:HIDDEN_PASSWORD@graph-node-database-cluster.cluster-cn9cukuadcb4.us-west-2.rds.amazonaws.com/client__proofofplay, pool: main, shard: primary
May 24 09:14:13.344 INFO Pool successfully connected to Postgres, pool: main, shard: primary, component: Store
May 24 09:14:13.344 INFO Connecting to Postgres (read replica 1), weight: 1, url: postgresql://admin:HIDDEN_PASSWORD@proxy-graph-node-read-only.endpoint.proxy-cn9cukuadcb4.us-west-2.rds.amazonaws.com/client__proofofplay, pool: replica1, shard: primary
May 24 09:14:13.344 INFO Pool successfully connected to Postgres, pool: replica1, shard: primary, component: Store
May 24 09:14:13.344 INFO Connecting to Postgres, weight: 1, conn_pool_size: 80, url: postgresql://postgres:<redacted>@graph-node-rds.cn9cukuadcb4.us-west-2.rds.amazonaws.com/client__proofofplay, pool: main, shard: secondary
May 24 09:14:13.345 INFO Pool successfully connected to Postgres, pool: main, shard: secondary, component: Store
May 24 09:14:13.346 DEBG Using postgres host order [ReadOnly(0)], shard: primary, component: Store
May 24 09:14:13.346 DEBG Using postgres host order [Main], shard: secondary, component: Store
May 24 09:14:13.356 INFO Setting up fdw, pool: main, shard: secondary, component: ConnectionPool
May 24 09:14:13.356 INFO Setting up fdw, pool: main, shard: primary, component: ConnectionPool
May 24 09:14:13.362 DEBG Release migration lock, pool: main, shard: primary, component: ConnectionPool
May 24 09:14:13.363thread 'tokio-runtime-worker ' panicked at 'migrations failed: store error: user mapping for "admin" does not exist for server "shard_secondary"CRIT',  store/postgres/src/connection_pool.rsmigrations failed:976:13,
errornote: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
: store error: user mapping for "admin" does not exist for server "shard_secondary", pool: main, shard: primary, component: ConnectionPool
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: JoinError::Panic(...)', /graph-node/store/postgres/src/connection_pool.rs:484:10
May 24 09:14:13.368 INFO Running migrations, pool: main, shard: secondary, component: ConnectionPool
May 24 09:14:13.369 INFO Migrations finished, pool: main, shard: secondary, component: ConnectionPool
May 24 09:14:13.371 DEBG Release migration lock, pool: main, shard: secondary, component: ConnectionPool
May 24 09:14:13.372 DEBG Setup finished, setup_time_s: 0, pool: main, shard: secondary, component: ConnectionPool

IPFS hash

No response

Subgraph name or link to explorer

No response

Some information to help us out

• Tick this box if this bug is caused by a regression found in the latest release.
• Tick this box if this bug is specific to the hosted service.
• I have searched the issue tracker to make sure this issue is not a duplicate.

OS information

Linux

[paymog]

I tried creating an admin user in the secondary shard with the same password as in the primary shard but still got that issue. Here's the command I used to make the user

create role admin in group rds_superuser password '<password>' valid until 'infinity'; 

[paymog]

Seems like this statement is erroring out because I'm changing the current_user from root to admin which means that the prior entry having an entry for root can no longer be altered.

[paymog]

I reverted back to the original config and check the user mappings table

SELECT * FROM pg_user_mappings;

+----------+----------+---------------+------+-------+-----------------------------------------------------+
|umid      |srvid     |srvname        |umuser|usename|umoptions                                            |
+----------+----------+---------------+------+-------+-----------------------------------------------------+
|1105368989|1105368984|shard_secondary|16399 |root   |{user=postgres,password=<redacted>|
+----------+----------+---------------+------+-------+-----------------------------------------------------+

then I decided to manually create a mapping for the new admin user (without yet rolling to the new config) like so

create user mapping for "admin" server "shard_secondary"
options (user 'postgres', password '<redacted>');

Interestingly, this didn't set the options as expected

+----------+----------+---------------+---------+-------+-----------------------------------------------------+
|umid      |srvid     |srvname        |umuser   |usename|umoptions                                            |
+----------+----------+---------------+---------+-------+-----------------------------------------------------+
|1105368989|1105368984|shard_secondary|16399    |root   |{user=postgres,password=<redacted>}|
|1815325660|1105368984|shard_secondary|937879319|admin  |null                                                 |
+----------+----------+---------------+---------+-------+-----------------------------------------------------+

regardless, I decided to now role forward the config assuming that the alter user mapping would now succeed.

I did the roll forward and the graph node came up successfully. However, the options for the admin user mapping are still null. Not sure why.

It seems that this logic should also take into account the current user, not just the current server.

[paymog]

I also tried running drop server shard_secondary cascade and then restarting my graph node to see if the user mapping would be successfully recreated with the correct options. Unfortunately I still see a null value for the umptions column for the admin user.

[paymog]

looks like graphman database remap solves this.

[paymog]

actually, graphman database remap does not solve this. When trying to access shard_second_subgraphs from the primary database, I get the following error if I changed the user/password combo for the second shard

ERROR: user mapping not found for "root"

[paymog]

I had to manually run

CREATE USER MAPPING FOR root SERVER shard_second OPTIONS (user 'root', password '<password>');

[github-actions]

Looks like this issue has been open for 6 months with no activity. Is it still relevant? If not, please remember to close it.