Schema mapping with Redshift column level security

mfrye · July 13, 2020, 7:45pm

Hey there,

Our team has been using Redash internally for awhile now. Thank you for the awesome product.

We recently switched over to use Redshift column level security, which enables granting “select” access to a specific subset of columns in a table. It looks like this breaks the schema mapping functions in Redash though, as none of the tables setup from the new schema with column level security are showing up in the Redash UI.

Is there a potential workaround for this, or would this require adding a fix in the Redash code to support this?

Michael

k4s1m · July 14, 2020, 11:30am

I don’t know Redshift very well. But I think it behaves like Postgres and uses the pg.py query runner:

github.com

getredash/redash/blob/81e7c72d48e6715e10a820c7fc97cf215d8bbd19/redash/query_runner/pg.py#L80-L107


def build_schema(query_result, schema):
    # By default we omit the public schema name from the table name. But there are
    # edge cases, where this might cause conflicts. For example:
    # * We have a schema named "main" with table "users".
    # * We have a table named "main.users" in the public schema.
    # (while this feels unlikely, this actually happened)
    # In this case if we omit the schema name for the public table, we will have
    # a conflict.
    table_names = set(
        map(
            lambda r: full_table_name(r["table_schema"], r["table_name"]),
            query_result["rows"],
        )
    )

    for row in query_result["rows"]:
        if row["table_schema"] != "public":
            table_name = full_table_name(row["table_schema"], row["table_name"])
        else:
            if row["table_name"] in table_names:

This file has been truncated. show original

This code builds the schema. Maybe an answer is apparent? I assume that if your permissions are set properly in Redshift then the schema will appear in Redash. Have you double-checked? If so, you might need to edit this Python file.

mfrye · July 14, 2020, 3:35pm

Thanks for getting back to me. Yes, you’re correct in that Redshift is based on Postgres.

I figured out the specific code that is causing the issue:

github.com

getredash/redash/blob/81e7c72d48e6715e10a820c7fc97cf215d8bbd19/redash/query_runner/pg.py#L372-L403


def _get_tables(self, schema):
    # Use svv_columns to include internal & external (Spectrum) tables and views data for Redshift
    # https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_COLUMNS.html
    # Use HAS_SCHEMA_PRIVILEGE(), SVV_EXTERNAL_SCHEMAS and HAS_TABLE_PRIVILEGE() to filter
    # out tables the current user cannot access.
    # https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_SCHEMA_PRIVILEGE.html
    # https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_SCHEMAS.html
    # https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_TABLE_PRIVILEGE.html
    query = """
    WITH tables AS (
        SELECT DISTINCT table_name,
                        table_schema,
                        column_name,
                        ordinal_position AS pos
        FROM svv_columns
        WHERE table_schema NOT IN ('pg_internal','pg_catalog','information_schema')
        AND table_schema NOT LIKE 'pg_temp_%'
    )
    SELECT table_name, table_schema, column_name
    FROM tables

This file has been truncated. show original

Specifically, this block here:

github.com

getredash/redash/blob/81e7c72d48e6715e10a820c7fc97cf215d8bbd19/redash/query_runner/pg.py#L392-L397


WHERE
    HAS_SCHEMA_PRIVILEGE(table_schema, 'USAGE') AND
    (
        table_schema IN (SELECT schemaname FROM SVV_EXTERNAL_SCHEMAS) OR
        HAS_TABLE_PRIVILEGE('"' || table_schema || '"."' || table_name || '"', 'SELECT')
    )

Basically, Redshift doesn’t have an internal function for “partial” select grant queries. The default is HAS_TABLE_PRIVILEGE(), which only checks if the user has full “select” access to the table. Since the Redash user only has access to some of the columns, this function returns false and the schema doesn’t show up in the UI.

I’m not sure of what the best solution should be, but in the meantime, I figured out a rather hacky workaround to avoid touching / customizing the Redash code for us. I noticed that the block I mentioned above will return true if the table’s schema is listed as a Redshift external schema. So I hacked SVV_EXTERNAL_SCHEMAS, which is a view, to include the schema that won’t show up. I don’t know yet how stable this is, but the schema now shows in the UI.

k4s1m · July 14, 2020, 3:48pm

Nice hack! Maybe the Redash code could be adjusted using a CASE for tables where column restrictions are in effect.

mfrye · July 14, 2020, 4:20pm

Thanks! Yeah, that’s possible. I haven’t been able to figure out a clean way to query Redshift though to detect if column restrictions are in effect.

▼Categories

▼Tags

Schema mapping with Redshift column level security