Redshift: Column Rebuilds and User Configuration Parameters

Atlas now plans the Redshift column changes the database cannot apply in place, a change to NULL, to a default value, or to some types, by dropping the column and adding it back, once you allow it with allow_recreate. The user block also takes a params map for the settings every session of that user starts with.

Two additions for Redshift: column changes the database cannot apply in place are now planned by rebuilding the column, and the settings attached to a user are now part of the schema.

Rebuilding a column

Redshift decides whether a column accepts NULL and what its default is when the table is created, and it changes a column's type only when the column has no default and uses a compression encoding it can rewrite. Dropping a NOT NULL from a column that turned out to be optional is the common case, and Atlas now plans it by dropping the column and adding it back the way you declared it, comment and permissions included:

-- drop column "user_agent" on table: "events" for recreationALTER TABLE "analytics"."events" DROP COLUMN "user_agent";-- add column "user_agent" on table: "events" as replacementALTER TABLE "analytics"."events" ADD COLUMN "user_agent" character varying(256) NULL ENCODE LZO;GRANT SELECT ("user_agent") ON TABLE "analytics"."events" TO "bi_reader";

Turning it on

Since the rows in that column are lost, a rebuild never happens without your say-so. Set allow_recreate for the whole project or for one environment, where the env wins over the block above it:

// Ask before rebuilding a column.diff "redshift" {  modify_column {    allow_recreate = false  }}
env "staging" {  // Plan it outright here, and only here.  diff "redshift" {    modify_column {      allow_recreate = true    }  }}
env "prod" {  // No block of its own, so it inherits the one above.}

Without it, what happens depends on where you run the command:

# In a terminal, Atlas asks first:$ atlas migrate diff relax_user_agent --env prod? Column "user_agent" nullability change requires recreating the column:  ▸ Abort    Drop and recreate column "user_agent" (data loss)
# In CI, there is nobody to ask, so it stops:$ atlas migrate diff relax_user_agent --env prodError: Column "user_agent" nullability change requires recreating the column.Set 'allow_recreate' in the 'modify_column' block of the diff policy to plan it

Either way the command stops at the first column it may not rebuild, and no migration file is written. allow_recreate is the only setting that changes this, and listing modify_column under diff.skip will not silence it.

Settings attached to a user

Redshift lets you attach settings to a user, and every connection that user opens starts with them applied: a default search path, a query timeout, a workload management queue. The user block now holds them in a params map, and Atlas turns that into the statements Redshift expects:

user "bi_reader" {  member_of = [role.analysts]
  params = {    search_path       = "analytics, public"    statement_timeout = 120000    query_group       = "reporting"  }}

-- create user "bi_reader"CREATE USER "bi_reader" PASSWORD DISABLE;-- set query_group for "bi_reader"ALTER USER "bi_reader" SET query_group = 'reporting';-- set search_path for "bi_reader"ALTER USER "bi_reader" SET search_path = analytics, public;-- set statement_timeout for "bi_reader"ALTER USER "bi_reader" SET statement_timeout = 120000;

Take a setting out of the map and it goes back to the cluster default. Already have users set up? atlas schema inspect brings their settings back as HCL.

Getting Started

The Redshift driver is part of Atlas Pro:

$ atlas login

See the HCL reference for the user block, the config reference for the Redshift diff policy, and the Redshift security guide for the roles, groups, and grants around them.