Minimum permission required to access Redshift External table

Question

As per the AWS documentation,

To run a Redshift Spectrum query, you need the following permissions:

Usage permission on the schema

Permission to create temporary tables in the current database

I have an External database, schema and a table created in that schema. I created a new Redshift user to which I granted 'usage' privileges on the external schema:

grant usage on external_schema to new_user;

But I did not provided 'temp' privileges on external_database to my new_user.

Also, there are no default privileges, as I checked PG_DEFAULT_ACL using master user and there are no rows in it.

Can someone let me know why I am able to query the external table?

I think it is saying they need permission on the "current database", which means Redshift (not the external database). — John Rotenstein
No, i don't this so..it is the spectrum database where metadata will be stored as this sample grant statement on their website suggests: "grant temp on database spectrumdb to group spectrumusers;". Even in your case, shouldn't the 'temp' privilege be then come up in the pg_default_Acl result? — SwapSays

Kawish Siddiqui Kawish Siddiqui · Accepted Answer · 2020-03-12T22:54:35

In Amazon Redshift, Database and Schema are different concepts. User objects (Redshift and external) are created in Schema and TEMP objects are created in "temp" schemas and are available at database level.

In some cases, where join between Spectrum tables and Redshift tables is applied, Redshift needs to create temporary tables and that's why it is mentioned in documentation to avoid any failure/error for users.

Here is what documentation says:

Grants the privilege to create temporary tables in the specified database. To run Amazon Redshift Spectrum queries, the database user must have permission to create temporary tables in the database.

Note By default, users are granted permission to create temporary tables by their automatic membership in the PUBLIC group. To remove the privilege for any users to create temporary tables, revoke the TEMP permission from the PUBLIC group. Then explicitly grant the permission to create temporary tables to specific users or groups of users.

Minimum permission required to access Redshift External table

1 Answers