Alter default privileges for Redshift native idP federated user

Question

I am trying to run a [ALTER DEFAULT PRIVILEGES](https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_DEFAULT_PRIVILEGES.html) statement so that users in a specified role can select all tables in the specified schema that are created by the specified user.

This works well if the user is a Redshift native user, However this does not seem to work with idP federated users.

For context in our environment, we use "Redshift native IdP federation with Microsoft Azure AD" and the user names are created with the prefix "oauth_aad:", And this is the query that I am trying to run from the Redshift Admin account:
```
alter default privileges
for user "oauth_aad:test.user@example.com"
in schema example_schema
grant select on tables to role "oauth_aad:ExampleCom_Engineer";
```

The above query fails with the error:
> [42501] ERROR: permission denied for schema

This issue prevents users (with the role `oauth_aad:ExampleCom_Engineer`) from being able to view tables on the above specified schema `example_schema` that are created by other federated users.

A potential workaround is to get users to execute the above query themselves without the `for user` line, However as stated above, ideally the Redshift admin/superuser should be able to alter the default privileges for idP users similar to native users, Thus I am curious if there is any workaround method that would allow admins/superusers to create default privileges for idP users at this time?

--- 
I am using the `v2.1.0.26` JDBC driver to connect to Redshift. The Redshift cluster version is:
> PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.63269

Accepted Answer

The issue you're facing with setting default privileges for federated (IdP) users in Amazon Redshift is due to the way Redshift handles these types of users.

In Redshift, the default privileges are typically set for specific user names, and Redshift does not automatically recognize the federated user names in the same way as native Redshift users.

The workaround you mentioned, where users execute the `ALTER DEFAULT PRIVILEGES` command without the `FOR USER` clause, is the recommended approach in this case. This will allow the users with the specified role to gain the necessary privileges, even though the admin/superuser cannot directly set the default privileges for the federated users.

Here's the steps you can follow:

1. Have the federated users (with the `oauth_aad:ExampleCom_Engineer` role) execute the following command:

```sql
ALTER DEFAULT PRIVILEGES
IN SCHEMA example_schema
GRANT SELECT ON TABLES TO ROLE "oauth_aad:ExampleCom_Engineer";
```

This will set the default privileges for all tables created in the `example_schema` schema by the federated users to allow the `oauth_aad:ExampleCom_Engineer` role to select from those tables.

2. Alternatively, you can have the federated users run the following command to grant the necessary privileges on specific tables:

```sql
GRANT SELECT ON ALL TABLES IN SCHEMA example_schema TO ROLE "oauth_aad:ExampleCom_Engineer";
```

This will grant the `SELECT` privilege on all tables in the `example_schema` schema to the `oauth_aad:ExampleCom_Engineer` role.

Unfortunately, as an admin/superuser, you cannot directly set the default privileges for federated users in Redshift due to the way Redshift handles these types of users. The best you can do is to provide the necessary guidance to the federated users and have them execute the appropriate commands to grant the required privileges.

If you need more control over the default privileges for federated users, you may want to consider exploring alternative authentication and authorization mechanisms, such as using AWS Identity and Access Management (IAM) policies to manage access to your Redshift resources.

Alter default privileges for Redshift native idP federated user

Relevant content