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

brunasiq · April 16, 2020, 3:30pm

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?

flamber · April 16, 2020, 4:02pm

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

teja · April 22, 2020, 3:19pm

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

flamber · April 22, 2020, 5:03pm

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

teja · April 22, 2020, 6:11pm

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"
    }
  }
}

flamber · April 22, 2020, 6:18pm

@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.

teja · April 22, 2020, 6:24pm

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.

teja · April 22, 2020, 6:25pm

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.

flamber · April 22, 2020, 6:44pm

@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.

teja · April 22, 2020, 6:50pm

@flamber, just tagging in case

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

Choose a table document from Dotcool DB
Joined the table to another table Account from Dotcool DB
Added relevant filters
Grouped by a field and got the count of rows
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

flamber · April 22, 2020, 7:29pm

@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

teja · April 22, 2020, 7:46pm

@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.

flamber · April 22, 2020, 7:52pm

@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?

teja · April 22, 2020, 8:31pm

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

teja · May 4, 2020, 4:26pm

@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.

flamber · May 4, 2020, 4:48pm

@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

brunasiq · May 27, 2020, 6:22pm

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

Thanks

flamber · May 27, 2020, 7:20pm

@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).

wtomlinson · July 31, 2020, 6:30pm

I have non-admin users who also have this same error. The only way I have found to circumvent the error is making the user an admin otherwise they get the error: "You do not have permissions to run ad-hoc native queries against Database {x}". I have verified that permissions exists for the user to run ad-hoc queries and full access to the database.

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.105 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.7+10",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.7",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.7+10",
"os.name": "Linux",
"os.version": "4.14.123-86.109.amzn1.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"mysql",
"sqlserver",
"googleanalytics",
"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-05-28",
"tag": "v0.35.4",
"branch": "release-0.35.x",
"hash": "b3080fa"
},
"settings": {
"report-timezone": "US/Mountain"
}
}
}

flamber · July 31, 2020, 7:13pm

@wtomlinson If you could provide steps-to-reproduce, then it would be very helpful and then we would be able to get this issue fixed. I have tried multiple times, but I haven’t been able to nail this yet.
From your timezone, I’m guessing you’re in the US and so are your users, so I don’t think it’s a problem with languages in the interface.