I Altered My Redshift Schema's Default Privileges to Allow Access. Why Can't My Users Query My New Tables?

Like many Data Engineers, the most common questions I get are "do you have this data?" and "why is my query running slow?" Just behind those is the occasional "why can't I query this table?" Redshift gives users a lot of control in doling out permissions to users but some of the intricacies can cause users to fall between the cracks.

Recently, an engineer who depends on my Redshift cluster for his product reached out to me asking why they suddenly lost access to a table. By running the following SQL statement I was able to confirm that not only them but many others had lost access to this heavily trafficked table.

select usename,
has_table_privilege(usename, 'my_schema.my_table', 'select')
from pg_user
order by usename asc;

I knew others on my team were actively working on this table and that likely it had been dropped and recreated several times recently. My engineer losing access perplexed me as just the previous week another user had come to me with a similar question and I had modified the schema using ALTER DEFAULT PRIVILEGES to what I thought would cause all new tables created in the schema to automatically grant his group read access.

After restoring access to my users by running the following command, I went back to trying to figure out why my previous ALTER DEFAULT PRIVILEGES had failed.

GRANT SELECT
  ON all tables IN SCHEMA my_schema
  TO group my_users;

My previous privilege modification likely looked something like this.

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT
  ON tables
  TO group my_users

While the language of the SQL command may make it seem like I am altering the privileges in the schema, running this command only altered the default privileges of the schema for objects I create.

ALTER DEFAULT PRIVILEGES IN SCHEMA ISN'T APPLIED AT THE SCHEMA LEVEL. IT IS APPLIED AT THE SCHEMA-CREATOR COMBINATION.

From the Redshift docs, ALTER DEFAULT PRIVILEGES "Defines the default set of access privileges to be applied to objects that are created in the future by the specified user." Emphasis my own and the key to the issue.

To make it so other users' created tables automatically grants rights, you need to make use of the optional FOR USER clause. Without the FOR USER clause, the command only alters the default permissions for objects created by the user who ran the ALTER DEFAULT PRIVILEGES command. The FOR USER clause has a counter intuitive name. You aren't altering the permissions for the "FOR USER" user to grant or revoke their privileges. You're making the modification to what happens when the FOR USER creates an object.

The command I should have ran was actually a series of commands. Something like the following.

ALTER DEFAULT PRIVILEGES
FOR
USER my_etl_user1 IN SCHEMA my_schema GRANT SELECT
  ON tables
  TO group my_users;
ALTER DEFAULT PRIVILEGES
FOR
USER my_etl_user2 IN SCHEMA my_schema GRANT SELECT
  ON tables
  TO group my_users;
ALTER DEFAULT PRIVILEGES
FOR
USER my_etl_user3 IN SCHEMA my_schema GRANT SELECT
  ON tables
  TO group my_users;

I have multiple human and machine Redshift users that are responsible for creating tables and each one of them needs to have their default permissions modified separately. As such, I need to run ALTER DEFAULT PRIVILEGES for each schema and ETL user combination.

If you want to dig further into your current default privileges, check out the system table PG_DEFAULT_ACL. The field defaclacl includes a complex string. Pay attention to the "grantor" section after the slash in this string. It informs you who the specified "FOR USER" was for ALTER DEFAULT PRIVILEGES command. Filling in any holes you notice for users who are likely to create tables can save you user contacts in the future.

Query on,

Bob