title: Create aws redshift user with access to given schema including drop tables
body: <p>I would like to seperate access to two existing data schemas (with tables already created) in redshift by creating two new users and granting them access to their relevant schemas. </p>
  
  <p>So <code>user_1</code> should have access only to <code>schema_1</code> and <code>user_2</code> should have access only to <code>schema_2</code>. </p>
  
  <p>By access I mean that the users should be able to <code>SELECT, INSERT, UPDATE, DELETE, DROP</code> from all current tables (no matter who created them) and to <code>CREATE</code> new tables on their schemas. </p>
  
  <p>I have found the below statements to create a new user and to give them specific access types:</p>
  
  <p>Create new user:</p>
  
  <pre><code> CREATE USER user_1 WITH PASSWORD 'password_1';
  </code></pre>
  
  <p>Grant usage to the given schema:</p>
  
  <pre><code> GRANT USAGE ON SCHEMA "schema_1" TO user_1;
  </code></pre>
  
  <p>Assign privileges:</p>
  
  <pre><code> GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "schema_1" TO user_1;
  </code></pre>
  
  <p>Alter Default Privileges to maintain the permissions on new tables</p>
  
  <pre><code> ALTER DEFAULT PRIVILEGES IN SCHEMA "schema_1" GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_1;
  </code></pre>
  
  <p>In the <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT.html" rel="nofollow noreferrer">documentation</a> it shows how to add all privileges required except for <code>DROP</code> tables. When testing this it errors with <code>ERROR: must be owner of relation table_to_drop</code>.</p>
  
  <p>I alternatively have tried to grant all privileges as below... </p>
  
  <pre><code> GRANT ALL TABLES IN SCHEMA "schema_1" TO user_1;
  </code></pre>
  
  <p>...but this doesn't overwrite the requirement of it needing to be the owner of the table that drops is able to drop it. </p>
  
  <p>So question is is it possible to restrict a user to a given schema with acccess to it as mentioned above?</p>