0

I am using Sqlalchemy to interact with Redshift via sqlalchemy-redshift. I need to run a query to grant some permissions:

GRANT SELECT ON <schema>.<table> TO <user>

As far as I am aware, the only way to run something like this is to use text(). Thus, I need to somehow provide the schema, table name, and username. The ideal approach is this:

text("GRANT SELECT ON :schema.:table TO :user").bindparams(schema="...", table="...", user="...")

However, this results in a syntax error. My understanding is that's because when the schema and table name parameters are bound, Sqlalchemy adds quotation marks, making the final command look like this:

GRANT SELECT ON '...'.'...' TO '...'

On Redshift (and Postgres) I think this isn't acceptable syntax. The schema and table name need to be provided as-is, without quotes. Previous posts like this and this have suggested psycopg2 constructs like psycopg2.extensions.AsIs to bind the table name when calling cursor.execute, or using psycopg2.extensions.quote_ident, or psycopg2.sql.Identifier to sanitise the parameters before using string formatting.

Also, I want to avoid the "obvious" solution of just using string formatting as it is bad practice and insecure:

f"GRANT SELECT ON {schema}.{table} TO {user}"

However, these solutions are psycopg2 specific. I don't want to introduce a new dependency in my project, or inconsistently stop using the Redshift connector.

Is there a solution in Sqlalchemy's API that's database agnostic? Or one that uses methods from sqlalchemy-redshift?

stelioslogothetis
  • 9,371
  • 3
  • 28
  • 53
  • I think the closest you can get is playing around with the `literal_binds` keyword argument to the SQL compiler. See https://docs.sqlalchemy.org/en/20/core/compiler.html#cross-compiling-between-sql-and-ddl-compilers – MP24 Aug 26 '23 at 18:44
  • @MP24 I suppose. However, I'm concerned because the documentation warns that this keyword shouldn't be used for "untrusted inputs" either, which mine could potentially be. – stelioslogothetis Aug 27 '23 at 19:36

1 Answers1

-1

You could try doing this using the SQLAlchemy Engine

https://docs.sqlalchemy.org/en/20/core/engines.html

from sqlalchemy import create_engine, text

# Your SQLAlchemy Redshift connection URL
redshift_url = 'postgresql+psycopg2://user:password@host:port/database'

# Replace with your schema, table, and user values
schema_name = 'my_schema'
table_name = 'my_table'
user_name = 'my_user'

engine = create_engine(redshift_url)

grant_sql = text(
    f"GRANT SELECT ON {schema_name}.{table_name} TO {user_name}"
)

print(grant_sql)

Output

DataWrangler
  • 1,804
  • 17
  • 32
  • Thanks, that's the "simple" option I came up with as well. However, using plain old string formatting like this is generally to be avoided, as it is *the* way applications are vulnerable to SQL injection attacks. I need to be able to "sanitise" the parameters. Is there a way to do so when passing them to `text`? – stelioslogothetis Aug 24 '23 at 13:50