Redshift database schema not detected

Get Help
1 
{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.93 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.13+8",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.13",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.13+8",
    "os.name": "Linux",
    "os.version": "4.14.219-164.354.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "redshift",
      "postgres",
      "bigquery",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "11.9"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.23"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-12-10",
      "tag": "v1.41.4",
      "branch": "release-x.41.4",
      "hash": "471cfb9"
    },
    "settings": {
      "report-timezone": null
    }
  }
}
2

Hello everyone,
To add more detail to my thread, the problem we are facing is that one of our newly created schemas cannot be seen under our redshift database.

Redshift connection was working properly till now and rest all of the schemas are visible.

To ensure that the new schema can be read by metabase, we run the query:
GRANT SELECT ON ALL TABLES IN SCHEMA dwh_v1 TO metabase;
so that metabase gets access to this schema.

Post this we have rescanned this db from admin and waited for 24 hours, but we are still not able to access the tables and views under this schema.

Help appreciated. Thanks !

3

Hi @abhi_apm
First of, are you using Metabase Cloud?
Can you run a query select * from dwh_v1.some_table limit 1 via Native query in Metabase and get the results you expect?

4

Hi @flamber,
Yes, we are using Metabase Cloud.

When I run the native query I get the following error:
ERROR: permission denied for schema dwh_v1

5

@abhi_apm Perfect, then it makes sense that Metabase didn't pick up the schema during sync.
You'll need to grant more privileges. I would say that at least USAGE is needed.

6

@flamber thank you. It was indeed a privileges issue. I am able to run the native query now. I hope when I refresh schema metabase will display the new schema under UI.

In case someone wonders upon this thread with a similar problem, noting my solution below-
To take full measure, I gave all below privileges on my schema 'dwh_v1' to my user 'metabase'

GRANT USAGE ON SCHEMA dwh_v1 TO metabase;
GRANT ALL ON SCHEMA dwh_v1 TO metabase;
GRANT ALL ON ALL TABLES IN SCHEMA dwh_v1 TO metabase;
ALTER DEFAULT PRIVILEGES IN SCHEMA dwh_v1 GRANT ALL ON TABLES TO metabase;

7

@abhi_apm Go to Admin > Databases > (db) > Sync database schema now. Then wait a little while (that can be anything from a few seconds to many minutes depending on your database size) and then it should appear as expected.

And :+1: on posting what you did.