Existing Google Search Console API integration doesn’t work on new install

[readtedium]

Past Issues Searched

• I have searched open and closed issues to make sure that the bug has not yet been reported

Issue is a Bug Report

• This is a bug report and not a feature request, nor asking for self-hosted support

Using official Plausible Cloud hosting or self-hosting?

Self-hosting

Describe the bug

Hi there, I’m trying to move a Docker install of my Plausible Community Edition from x64 to ARM. I am using the same domain as previously, just trying to reset the data. The installation has gone fine, but I’m running into an issue getting my existing Google Analytics integration to work.

Essentially, I go through the process of connecting to my existing API connection, and I run into an error that states: “Your Search Console account hasn't been connected successfully. Please unlink your Google account and try linking it again.”

I even tried building a fresh integration via the Google API, going off the docs, and it did not work. (I should note that one difference between the docs and my experience was that I was unable to click a checkbox to approve the integration, as I show in one of the screenshots. Could that be a factor?)

I think there is a chance it may be a bug given the fact that the integration worked fine on another server with the very same credentials on the same URL.

Expected behavior

A successful integration between Google and Plausible.

Screenshots

Environment

- OS: Nobara Linux 40 (Desktop); AlmaLinux 9 on ARM (Server)
- Browser: Vivaldi
- Browser Version: 6.9.3447.37

[ruslandoga]

👋 @readtedium

What Plausible version are you using? Are the versions the same on both servers?

FWIW I set up Google integration "from scratch" recently for #4485 and it worked fine.

[readtedium]

The old app used:

  plausible:
    image: ghcr.io/plausible/community-edition:v2.1.1
    restart: always
    command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh run"
    depends_on:
      - plausible_db
      - plausible_events_db
      - mail
    ports:
      - 8000:8000
    env_file:
      - plausible-conf.env

The new one uses:

  plausible:
    image: ghcr.io/plausible/community-edition:v2.1.1
    restart: always
    command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh run"
    depends_on:
      - plausible_db
      - plausible_events_db
    ports:
      - 8000:8000
    env_file:
      - plausible-conf.env

I should make it clear, I actually built a new “for scratch” integration after the initial one did not work, following the guide, and it did not work for me.

I also tried two approaches to importing the database—using the internal import/export tool, and by dumping the Clickhouse and Postgres databases and re-installing them on the new site. I would prefer to stick with the latter option as it allows me to keep my hourly data.

[ruslandoga]

Thank you for the additional information! Since the same version of Plausible and (I assume) plausible-conf.env are used in both working and not-working integrations, I doubt it's an issue with the source code. So I'm moving it to discussions.

[readtedium]

I did test inside the container to see if for some reason it was some kind of network issue, but it seems to be pinging externally with no issues.

[ruslandoga]

The error seems to be shown when we get 401 or 403 responses from Google. It's hard to debug right now but should become easier in the next release. I know that in the past these "auth" errors were caused by some APIs not being enabled (like the Admin API, etc.), but that was for exports/imports of GA4 and not integration with the Search Console ...

Some more links:

• here's the controller action that gets executed on /settings/integrations

  analytics/lib/plausible_web/controllers/site_controller.ex

  Lines 220 to 241 in 8fa3a83

  	def settings_integrations(conn, _params) do
  	site =
  	conn.assigns.site
  	|> Repo.preload([:google_auth])
  	search_console_domains =
  	if site.google_auth do
  	Plausible.Google.API.fetch_verified_properties(site.google_auth)
  	end
  	has_plugins_tokens? = Plausible.Plugins.API.Tokens.any?(site)
  	conn
  	|> render("settings_integrations.html",
  	site: site,
  	has_plugins_tokens?: has_plugins_tokens?,
  	search_console_domains: search_console_domains,
  	dogfood_page_path: "/:dashboard/settings/integrations",
  	connect_live_socket: true,
  	layout: {PlausibleWeb.LayoutView, "site_settings.html"}
  	)
  	end

  The interesting bit here is that the auth is stored in google_auth column in sites table in PostgreSQL. Would you be able to check what it's set to in your new installation for the problematic site? Then maybe we would be able to build a curl request to get the actual error Plausible receives.

[readtedium]

I can give it a look, sure. Will report back.

[ruslandoga]

Based off

analytics/lib/plausible/google/api.ex

Lines 51 to 61 in 8fa3a83

	def fetch_verified_properties(auth) do
	with {:ok, access_token} <- maybe_refresh_token(auth),
	{:ok, sites} <- Plausible.Google.HTTP.list_sites(access_token) do
	sites
	|> Map.get("siteEntry", [])
	|> Enum.filter(fn site -> site["permissionLevel"] in @verified_permission_levels end)
	|> Enum.map(fn site -> site["siteUrl"] end)
	|> Enum.map(fn url -> String.trim_trailing(url, "/") end)
	|> then(&{:ok, &1})
	end
	end

the curl request would be something like

curl -H "authorization: Bearer $access_token" https://www.googleapis.com/webmasters/v3/sites

[ruslandoga]

Oops, it's actually google_auth table. Sorry! The access token would be in access_token column.

Here's one way to get it:

$ docker compose exec plausible bin/plausible remote

iex> domain = "problematic.site"
iex> site = Plausible.Site |> Plausible.Repo.get_by!(domain: domain) |> Plausible.Repo.preload([:google_auth])
iex> site.google_auth.access_token

[readtedium]

OK, here’s what I have. I will probably reset the SECRET on the integration after this to be safe but I’ll share for testing purposes.

                                                                                                           access_token                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ya29.a0AXooCgvVdsNwr645w_t0FNoRK8bElZVHuTnV5TzkL7Wc1phHyNfiO8fT92NrWWC7VYd4Y9rgDjNOV4r_24fQKnnoN2y8ZzRgivhJVBWi8MbSs-hdWtA69-XYHiLajRt8-OBMAcSMyTwNm_U56n6kf1LjMaVSPyA_PBfyNgaCgYKAa0SARESFQHGX2MiU6aykYF3gI-Vaz8IMNZgjg0173
 ya29.a0AVvZVso29gRi1WVOBMfHrJnuv-iy-HJx6_WuAv32SDLgwperUhFemKqJWh6znt-fGm9PGmE0YGS_qHryuwhMe3LPupKcoLyCFrWqKgfpgmU8XwA6px-5GCpZ-T_zuf6jYxG-oIpoDA0RjP8gH65Us7fg3TDA8o6-aCgYKAX4SAQASFQGbdwaI9XEk2xenOCQeh58aIqRwbw0167
 ya29.a0AcM612yjb_AJXhCcFNSIaxpBwY8Yyu7zmt04QOl9OJ55N6ctIpha164dcTFObyJFUdc8QcFz853-ClHzSpzCkkRdDVC5k8MJQIJhHeQmmoSZ5zceiC64quls8gFrKqu5nErrdt0KsWgWqp2h5GVRjSQV9zgm0TUHGpLAzxaSOuYaCgYKAXESARESFQHGX2MiJVqwC19uT4CuGaebqGt39A0178
 ya29.a0AXooCgs5yP7qOAsnNsctHWFKJQMBA_KyGLBqLXvSmnpfze8OfFLcFsUAQlG56FwUhEOORqg5wwRpn_BAuaYlnvKeOVknGItatiSm69Ls2lYFU9rIu-F_fG0nxid19583pzEud6bvLj-nYXJUiJUJXljTcyJxyXfMIgEogQaCgYKAVYSARESFQHGX2MiZFjQB4ubLu8nDtmI4nMbFg0173
 ya29.a0AcM612wX9S4mYE23-C5y_zAelV_r5BFw8mB63lTnMjGxmepd2TNKsbIXG61hoZvs3xnXCXas1Rh9JakthPAdEwCOcTF1BvyVuCCfx4fy-F6xo1Q56mEKPgTqjHFVOAFJyWFCZOVcGMu7pbzm3xT9mhOE8OryaPErT5I_KIvtaCgYKAaQSARESFQHGX2MiPMTAjLB824qizaBvy066sQ0175
(5 rows)

[readtedium]

The error I got with the first command was a memory issue; kicking up the RAM confirms that the times match. As far as the trace, this is what it threw me:

iex(plausible@99597dddc935)1> :recon_trace.calls({Plausible.Google.API, :_, :return_trace}, 100, scope: :local)
28

So yeah, not quite what you got. :)

[readtedium]

Scratch that, here’s what it threw:

15:2:00.884364 <0.4666.0> 'Elixir.Plausible.Google.API':search_console_authorize_url(1)

15:2:00.886964 <0.4666.0> 'Elixir.Plausible.Google.API':client_id()

15:2:00.888036 <0.4666.0> 'Elixir.Plausible.Google.API':client_id/0 --> <<"383176452597-6t4bubhf26ebd9dnfa08vvv133ob8kf8.apps.googleusercontent.com">>

15:2:00.889101 <0.4666.0> 'Elixir.Plausible.Google.API':redirect_uri()

15:2:00.889791 <0.4666.0> 'Elixir.Plausible.Google.API':redirect_uri/0 --> <<"https://analytics.tedium.co/auth/google/callback">>

15:2:00.890427 <0.4666.0> 'Elixir.Plausible.Google.API':search_console_authorize_url/1 --> <<"https://accounts.google.com/o/oauth2/v2/auth?client_id=383176452597-6t4bubhf26ebd9dnfa08vvv133ob8kf8.apps.googleusercontent.com&redirect_uri=https://analytics.tedium.co/auth/google/callback&prompt=consent&response_type=code&access_type=offline&scope=email+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fwebmasters.readonly&state=[1,\"search-console\"]">>

15:2:09.524455 <0.4667.0> 'Elixir.Plausible.Google.API':'fetch_access_token!'(<<"4/0AQlEd8wPz9Xm1h2gbXaUXE3EbzNwWT-waCFSjI1jec8Rere8SmAoAswK1DEeMq0opqVg7g">>)

15:2:09.682753 <0.4667.0> 'Elixir.Plausible.Google.API':'fetch_access_token!'/1 --> #{<<"access_token">>=><<"ya29.a0AcM612xywrWXN51jo7reRIylaBv7kL1phmySQGKT6hW6VJ-j6ZpRX86fmsT_BoaW1dR1g45LqfmHwt2BosnrYNDVqHsURb1UwOsuIhbNjh7wQ41DRDDsmsGdVmGn4oAWPoHtL8XkGI4hgASuUydZiLg5UV_KmtjxV6B7mTrlaCgYKAcMSARESFQHGX2Mi3hA93NRv4yYOXYogR-dbJw0175">>, <<"expires_in">>=>3599, <<"id_token">>=><<"eyJhbGciOiJSUzI1NiIsImtpZCI6ImQ3YjkzOTc3MWE3ODAwYzQxM2Y5MDA1MTAxMmQ5NzU5ODE5MTZkNzEiLCJ0eXAiOiJKV1QifQ.eyJpc3MiOiJodHRwczovL2FjY291bnRzLmdvb2dsZS5jb20iLCJhenAiOiIzODMxNzY0NTI1OTctNnQ0YnViaGYyNmViZDlkbmZhMDh2dnYxMzNvYjhrZjguYXBwcy5nb29nbGV1c2VyY29udGVudC5jb20iLCJhdWQiOiIzODMxNzY0NTI1OTctNnQ0YnViaGYyNmViZDlkbmZhMDh2dnYxMzNvYjhrZjguYXBwcy5nb29nbGV1c2VyY29udGVudC5jb20iLCJzdWIiOiIxMDY2NTAzMzQwNjIxODk5NjQxOTAiLCJoZCI6InRlZGl1bS5jbyIsImVtYWlsIjoiZXJuaWVAdGVkaXVtLmNvIiwiZW1haWxfdmVyaWZpZWQiOnRydWUsImF0X2hhc2giOiI1ckp1LUpDNF9tMXlvRHJtV3NiVmNnIiwiaWF0IjoxNzI1NDYyMTI5LCJleHAiOjE3MjU0NjU3Mjl9.DVONttLEGJ1zjgFS4u1a2nXovk7iYGcz95viUWYiPfj-zQJ8xs2srCemDo5cY2lnMUUjwTSgYVBtgaJMzIJNf_BW2Z3ZaJmoB0J0HhUXlxs2M1cftk4T6b0awlxI_NJcMh9rYxTEptObYXgRrhD_TCY6tziaC-ZiatwyDOyQHfFXrzCuHvhIhxruqIrXjfFVfYBUqZOEP3u470yUFSj3IlZM-G0ZiyVZjhgmgzB35s-aqL9asAgVZZSw7scQooqrFAzejBU0Y9gnq1JQ-e2xPDQMUvNerW0GEDZT7dFx99jrztDTLzkDSDBDIl2lTLdXjs-N1cY4_5hcgB7FBTinSw">>, <<"refresh_token">>=><<"1//0cP3Fn-mxRvCGCgYIARAAGAwSNwF-L9IrWFc7kBsEPGtRp_sLD4AsrY5wVKp_dSnlKk9kQN8x1F6Cig2CpGYflviTtCfjCQdwROg">>, <<"scope">>=><<"https://www.googleapis.com/auth/webmasters.readonly https://www.googleapis.com/auth/userinfo.email openid">>, <<"token_type">>=><<"Bearer">>}

15:2:09.851052 <0.4672.0> 'Elixir.Plausible.Google.API':fetch_verified_properties(#{id=>16, user=>#{'__struct__'=>'Elixir.Ecto.Association.NotLoaded', '__cardinality__'=>one, '__field__'=>user, '__owner__'=>'Elixir.Plausible.Site.GoogleAuth'}, property=>nil, '__struct__'=>'Elixir.Plausible.Site.GoogleAuth', email=><<"[email protected]">>, expires=>#{microsecond=>{0,0}, second=>8, calendar=>'Elixir.Calendar.ISO', month=>9, '__struct__'=>'Elixir.NaiveDateTime', year=>2024, hour=>16, day=>4, minute=>2}, '__meta__'=>#{state=>loaded, context=>nil, prefix=>nil, source=><<"google_auth">>, '__struct__'=>'Elixir.Ecto.Schema.Metadata', schema=>'Elixir.Plausible.Site.GoogleAuth'}, inserted_at=>#{microsecond=>{0,0}, second=>9, calendar=>'Elixir.Calendar.ISO', month=>9, '__struct__'=>'Elixir.NaiveDateTime', year=>2024, hour=>15, day=>4, minute=>2}, updated_at=>#{microsecond=>{0,0}, second=>9, calendar=>'Elixir.Calendar.ISO', month=>9, '__struct__'=>'Elixir.NaiveDateTime', year=>2024, hour=>15, day=>4, minute=>2}, refresh_token=><<"1//0cP3Fn-mxRvCGCgYIARAAGAwSNwF-L9IrWFc7kBsEPGtRp_sLD4AsrY5wVKp_dSnlKk9kQN8x1F6Cig2CpGYflviTtCfjCQdwROg">>, site=>#{'__struct__'=>'Elixir.Ecto.Association.NotLoaded', '__cardinality__'=>one, '__field__'=>site, '__owner__'=>'Elixir.Plausible.Site.GoogleAuth'}, user_id=>1, site_id=>1, access_token=><<"ya29.a0AcM612xywrWXN51jo7reRIylaBv7kL1phmySQGKT6hW6VJ-j6ZpRX86fmsT_BoaW1dR1g45LqfmHwt2BosnrYNDVqHsURb1UwOsuIhbNjh7wQ41DRDDsmsGdVmGn4oAWPoHtL8XkGI4hgASuUydZiLg5UV_KmtjxV6B7mTrlaCgYKAcMSARESFQHGX2Mi3hA93NRv4yYOXYogR-dbJw0175">>})

15:2:09.852622 <0.4672.0> 'Elixir.Plausible.Google.API':maybe_refresh_token(#{id=>16, user=>#{'__struct__'=>'Elixir.Ecto.Association.NotLoaded', '__cardinality__'=>one, '__field__'=>user, '__owner__'=>'Elixir.Plausible.Site.GoogleAuth'}, property=>nil, '__struct__'=>'Elixir.Plausible.Site.GoogleAuth', email=><<"[email protected]">>, expires=>#{microsecond=>{0,0}, second=>8, calendar=>'Elixir.Calendar.ISO', month=>9, '__struct__'=>'Elixir.NaiveDateTime', year=>2024, hour=>16, day=>4, minute=>2}, '__meta__'=>#{state=>loaded, context=>nil, prefix=>nil, source=><<"google_auth">>, '__struct__'=>'Elixir.Ecto.Schema.Metadata', schema=>'Elixir.Plausible.Site.GoogleAuth'}, inserted_at=>#{microsecond=>{0,0}, second=>9, calendar=>'Elixir.Calendar.ISO', month=>9, '__struct__'=>'Elixir.NaiveDateTime', year=>2024, hour=>15, day=>4, minute=>2}, updated_at=>#{microsecond=>{0,0}, second=>9, calendar=>'Elixir.Calendar.ISO', month=>9, '__struct__'=>'Elixir.NaiveDateTime', year=>2024, hour=>15, day=>4, minute=>2}, refresh_token=><<"1//0cP3Fn-mxRvCGCgYIARAAGAwSNwF-L9IrWFc7kBsEPGtRp_sLD4AsrY5wVKp_dSnlKk9kQN8x1F6Cig2CpGYflviTtCfjCQdwROg">>, site=>#{'__struct__'=>'Elixir.Ecto.Association.NotLoaded', '__cardinality__'=>one, '__field__'=>site, '__owner__'=>'Elixir.Plausible.Site.GoogleAuth'}, user_id=>1, site_id=>1, access_token=><<"ya29.a0AcM612xywrWXN51jo7reRIylaBv7kL1phmySQGKT6hW6VJ-j6ZpRX86fmsT_BoaW1dR1g45LqfmHwt2BosnrYNDVqHsURb1UwOsuIhbNjh7wQ41DRDDsmsGdVmGn4oAWPoHtL8XkGI4hgASuUydZiLg5UV_KmtjxV6B7mTrlaCgYKAcMSARESFQHGX2Mi3hA93NRv4yYOXYogR-dbJw0175">>})

15:2:09.854373 <0.4672.0> 'Elixir.Plausible.Google.API':'needs_to_refresh_token?'(#{microsecond=>{0,0}, second=>8, calendar=>'Elixir.Calendar.ISO', month=>9, '__struct__'=>'Elixir.NaiveDateTime', year=>2024, hour=>16, day=>4, minute=>2})

15:2:09.855076 <0.4672.0> 'Elixir.Plausible.Google.API':'needs_to_refresh_token?'/1 --> false

15:2:09.855777 <0.4672.0> 'Elixir.Plausible.Google.API':maybe_refresh_token/1 --> {ok,<<"ya29.a0AcM612xywrWXN51jo7reRIylaBv7kL1phmySQGKT6hW6VJ-j6ZpRX86fmsT_BoaW1dR1g45LqfmHwt2BosnrYNDVqHsURb1UwOsuIhbNjh7wQ41DRDDsmsGdVmGn4oAWPoHtL8XkGI4hgASuUydZiLg5UV_KmtjxV6B7mTrlaCgYKAcMSARESFQHGX2Mi3hA93NRv4yYOXYogR-dbJw0175">>}

15:2:09.964488 <0.4672.0> 'Elixir.Plausible.Google.API':fetch_verified_properties/1 --> {error,<<"google_auth_error">>}

[ruslandoga]

Seems like the token refresh logic is correct, a token issued at ~N[2024-09-04 15:05:38.326920] does not need to be refreshed :) Can you verify it with PostgreSQL, has expires (as in #4530 (reply in thread)) been updated? If it has, we are back at step 1 -- Google Search Console configuration ... Or something else I might be missing. It's getting late where I am, so I will return to this issue tomorrow morning. Sorry we couldn't resolve it today!

[readtedium]

Here’s that so you have it:

      expires       
---------------------
 2024-06-09 12:51:30
 2023-02-10 18:42:28
 2024-09-03 23:15:36
 2024-06-27 18:05:14
 2024-09-04 16:02:08
(5 rows)

I appreciate you working with me on this, it has been very helpful. Have a good night!

[readtedium]

Replying to this thread to inform that what ultimately solved this problem was an upgrade to the latest version.

Looks like an upgrade/transition repaired whatever issue was causing the problem in the database.

[readtedium]

I’m presuming it’s showing one for each of my attempts to connect since I moved to the database dump.