1

I'm writing a Redshift migration that will be run on a staging DB and a production DB. I want this migration to grant an access to a user, and the name of the user depends on what DB is being migrated.

For example, I want the migration to grant a permission to "staging_user" if the current DB is "staging_db", and to "prod_user" if the current DB is "prod_db".

A naive implementation would look like this :

GRANT SELECT
ON TABLE my_table
TO (SELECT CASE current_database()
           WHEN 'staging_db' THEN 'staging_user'
           WHEN 'prod_db' THEN 'db_user'
           END);

Although, this error tells me SELECT subqueries are not allowed in GRANT statements :

ERROR:  syntax error at or near "("
LINE 3: TO (SELECT CASE current_database()

What would be the preferred way of doing this in Redshift?

Edit: I'm running the migrations with Migratus in Clojure, if it can be of any help.

GeorgCantor
  • 65
  • 1
  • 7

3 Answers3

0

Use a group (CREATE GROUP) so you can assign your permissions to the group.

Then your only problem is the ALTER GROUP required which can be done up front. So the migration script can be hard coded:

GRANT SELECT ON TABLE my_table TO MyGroup;

Update, after comment

On prod (reverse for staging)

ALTER GROUP MyGroup ADD USER db_user; ALTER GROUP MyGroup DROP USER staging_user;

Don't forget the GRANT USAGE ON SCHEMA too

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thanks for your fast answer. Having both users in the same group will unfortunately grant the "staging_user" access to the "prod_db" once the migration will be run in production – GeorgCantor Oct 12 '17 at 13:46
  • @GeorgCantor only grant the correct user membership based on environment. That is, the group and membership can be done separately or more dynamically in the client. – gbn Oct 12 '17 at 13:49
  • Will this not lead to the same problem? I will need to dynamically grant memberships based on current DB, which I'm not able to do. – GeorgCantor Oct 12 '17 at 14:05
  • You can do that in the client then, and send a different ALTER GROUP. It only needs done once, instead of for every table – gbn Oct 12 '17 at 14:07
  • I want to avoid making the client handle the environment. – GeorgCantor Oct 12 '17 at 14:37
  • I mean client code that deploys. Does it have IF logic? – gbn Oct 12 '17 at 14:47
  • Yes, but the goal of my question was precisely to avoid putting this logic in the client. – GeorgCantor Oct 12 '17 at 15:11
0

With migratus you can run EDN migrations to have fully programmable queries from Clojure.

You should be able to implement the logic of that subquery there!

Jeremie Pelletier
  • 680
  • 8
  • 8
0

Apparently, Redshift does not have mechanisms to deal with subqueries in statements. Since Redshift is a fork of PostgreSQL 8.0.2, it neither has the DO blocks feature available in later PostgreSQL versions.

A better approach would be to have a completely separate cluster for Staging and Production environments, instead of having separate databases and users. This will remove the need of having "if logic" in the SQL migration statement and will avoid other downsides of sharing the same cluster for different environments.

For useful tips on how to reduce costs on Staging/Testing environments, see this post.

GeorgCantor
  • 65
  • 1
  • 7