how to revoke CREATE privilege on public schema from specific user?

Question

I am currently trying to create a new read-only user in Amazon Redshift.

The Redshift version I am using 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.735

After creating the read-only user, I need to be able to restrict the CREATE privilege on PUBLIC SCHEMA for only that particular user (but not for the rest of the users!).

Using the following command, I am able to revoke CREATE access from all users of the database cluster. However, that's not something I want to do. What I need is to retain the CREATE privileges on all users except for the ones that I am going to restrict explicitly.

REVOKE CREATE ON SCHEMA public FROM public;

Any suggestions are more than welcome. Thank you

Answer 1

PostgreSQL has no DENY ACLs. So you can't grant to all, then revoke from one.

You must instead revoke the right from public, create a new role, grant it to that role, and grant all the users who should have the desired access inherit membership of the new role, leaving the one who should not have access out of the role.

This may be true for Redshift too, I don't know for sure and Amazon don't offer any free public test instances.