ERROR: '' You do not have permissions to run native ad-hoc queries against database 3. ''

THE ERROR: ‘’ You do not have permissions to run native ad-hoc queries against database 3. ‘’
After upgrading the metabase to version 35.02, I am unable to save questions to my personal collection. But I have access to those tables. Would you help me?

image

Hi @brunasiq
Please post “Diagnostic Info” from Admin > Troubleshooting.
And see if there’s a more detailed error in Admin > Troubleshooting > Logs.

Hi @flamber,

My users, who do not have Query access to the database but have access to the tables, are also facing similar issues in our environment. I thought this was resolved according to this.

Could you please help us debug this?

Here’s a screenshot, if it helps:

Thanks

@teja Post “Diagnostic Info” from Admin > Troubleshooting.

Here you go, @flamber:

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.5+10",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.5",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.5+10",
    "os.name": "Linux",
    "os.version": "4.14.171-105.231.amzn1.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "redshift",
      "h2"
    ],
    "hosting-env": "elastic-beanstalk",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "10.6"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.8"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2020-04-10",
      "tag": "v0.35.2",
      "branch": "release-0.35.x",
      "hash": "f3b2857"
    },
    "settings": {
      "report-timezone": "US/Central"
    }
  }
}

@teja

I don’t think I’ve ever seen that error before. Adding as text, so it can be searched in the future: You do not have permissions to run ad-hoc native queries against Database 9

Do you see more details in Admin > Troubleshooting > Logs? (I don’t expect it, just curious)

Can you provide a way to reproduce this? Meaning what permissions, question creation, and if possible use Sample Dataset as the test database, since we all have access to that.

This is the error I see in logs around the time the user posted this issue, if that helps:

[27410c87-6475-41e6-8319-1f7b745f606e] 2020-04-22T09:54:38-05:00 ERROR metabase.models.query.permissions Error calculating permissions for query: Attempting to fetch second Database. Queries can only reference one Database.
["--> query_processor.store$fn__38109$fetch_and_store_database_BANG___38114$fn__38115.invoke(store.clj:155)"
"query_processor.store$fn__38109$fetch_and_store_database_BANG___38114.invoke(store.clj:147)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_STAR_.invokeStatic(resolve_database_and_driver.clj:15)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_STAR_.invoke(resolve_database_and_driver.clj:12)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__43894.invoke(resolve_database_and_driver.clj:26)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__42937.invoke(fetch_source_query.clj:243)"
"query_processor.middleware.store$initialize_store$fn__46978$fn__46979.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:44)"
"query_processor.store$do_with_store.invoke(store.clj:40)"
"query_processor.middleware.store$initialize_store$fn__46978.invoke(store.clj:10)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__41993.invoke(cache.clj:208)"
"query_processor.middleware.validate$validate_query$fn__46987.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__43035.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__39170.invoke(add_rows_truncated.clj:36)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__46954.invoke(results_metadata.clj:128)"
"query_processor.reducible$async_qp$qp_STAR___37971$thunk__37972.invoke(reducible.clj:100)"
"query_processor.reducible$async_qp$qp_STAR___37971.invoke(reducible.clj:106)"
"query_processor.reducible$sync_qp$qp_STAR___37980$fn__37983.invoke(reducible.clj:132)"
"query_processor.reducible$sync_qp$qp_STAR___37980.invoke(reducible.clj:131)"
"query_processor$preprocess_query.invokeStatic(query_processor.clj:142)"
"query_processor$preprocess_query.invoke(query_processor.clj:134)"
"query_processor$query__GT_preprocessed.invokeStatic(query_processor.clj:148)"
"query_processor$query__GT_preprocessed.invoke(query_processor.clj:144)"
"models.query.permissions$preprocess_query.invokeStatic(permissions.clj:104)"
"models.query.permissions$preprocess_query.invoke(permissions.clj:99)"
"models.query.permissions$fn__37227$mbql_permissions_path_set__37232$fn__37236.invoke(permissions.clj:120)"
"models.query.permissions$fn__37227$mbql_permissions_path_set__37232.invoke(permissions.clj:106)"
"models.query.permissions$fn__37260$perms_set_STAR___37265$fn__37269.invoke(permissions.clj:140)"
"models.query.permissions$fn__37260$perms_set_STAR___37265.invoke(permissions.clj:133)"
"models.query.permissions$perms_set.invokeStatic(permissions.clj:148)"
"models.query.permissions$perms_set.doInvoke(permissions.clj:143)"
"models.query.permissions$fn__37290$can_run_query_QMARK___37295$fn__37296.invoke(permissions.clj:155)"
"models.query.permissions$fn__37290$can_run_query_QMARK___37295.invoke(permissions.clj:150)"
"models.card$pre_insert.invokeStatic(card.clj:116)"
"models.card$pre_insert.invoke(card.clj:108)"
"api.card$save_new_card_async_BANG_$fn__50663$fn__50664.invoke(card.clj:210)"
"api.card$save_new_card_async_BANG_$fn__50663.invoke(card.clj:206)"
"async.util$cancelable_thread_call$fn__22853$fn__22854.invoke(util.clj:51)"
"async.util$cancelable_thread_call$fn__22853.invoke(util.clj:50)"]

I could save the question as I have SQL access to the database.

Just to clarify that even though the log says that it is using a secon database, it is not. I could save the question myself.

@teja Can you provide steps to reproduce this? Can you post the query?

If the users don’t have access to the query, then it’s correct that they cannot save the question, but they shouldn’t even be able to get to that point.

@flamber, just tagging in case

They are not actually writing a query. This is what they did.

  1. Choose a table document from Dotcool DB
  2. Joined the table to another table Account from Dotcool DB
  3. Added relevant filters
  4. Grouped by a field and got the count of rows
  5. Tried to the save the question, but failed with the error.

I should have shared the complete screen shot that they shared, but here it is

@teja
This is really strange. With the amount of installations of Metabase, I don’t understand why this problem hasn’t happened more often. It must be some combination of factors that triggers this.

Which database type is “Dotcool”, Redshift?
And the users just have “Data Access” to the entire database, or only to specific tables?
Does this happen with all users on that database, or is it the specific tables, or specific filters that causes the error?

There hasn’t been any changes to the logic behind this permission check for a very long time, so something makes me think that to root cause is somewhere else:
https://github.com/metabase/metabase/blob/891e128b1f3dfad7e73250e54108148cba491678/src/metabase/models/query/permissions.clj#L106-L131
https://github.com/metabase/metabase/blob/1d9368c2826d12f2c9ece335c4e10861acc8cec9/src/metabase/query_processor/store.clj#L147-L161
https://github.com/metabase/metabase/blob/43f5f662890c2539189c8ae2d759967ebae806a2/src/metabase/models/card.clj#L108-L120

@flamber

Yes, we are using Redshift for Dotcool.
All users just have access to few specific schemas in the database.

This is the first time that any user has posted this issue. Also, we are a small company, so I wonder how many non-admin users are actually creating their own reports. I am sorry that I couldn’t be of much help on how widely this issue persists.

@teja

All users just have access to few specific schemas in the database.

But are they also restricted to specific tables?
The tables Document and Account are in the same schema, right?

@flamber
Yes, they are both in the same schema, and all users have access to all tables in that schema.

@flamber just wanted to see if you have any solution here.

I noticed that more and more users are facing these issues when they are trying to build new reports from existing questions.

@teja I’m not sure what the trigger is, but I haven’t been able to reproduce this at all. And I haven’t seen anyone (besides this forum topic) report this error.
You’re welcome to open an issue on this, but please try to explain in full details your permission levels and anything else you think might be helpful:
https://github.com/metabase/metabase/issues/new/choose

Hello, how are you? did you receive any solution about the error of saving as questions in the collection ??

Thanks

@brunasiq I haven’t been able to reproduce this, but it would help if you posted “Diagnostic Info” from Admin > Troubleshooting, and provided steps to reproduce the issue or at least information about your database permissions (Admin > Permissions).