Re: How to revoke "Create Privilege" from a readonly user in postgres?

[Rui DeSousa <rui@xxxxxxxxxxxxx>]

On Nov 7, 2018, at 12:52 AM, pavan95 <pavan.postgresdba@xxxxxxxxx> wrote:

grant SELECT on all tables in schema  readonly to readonly;

That grants select on all existing tables to readonly user; not future tables.

To auto grant select on future tables you have to change the default privileges. 

I.e.

alter default privileges in schema readonly grant select on tables to readonly;

You can also scope it to only a user instead of anyone to creates a table in readonly schema.  Then only when xxx creates a table in schema readonly will it auto grant the select.

alter default privileges for user xxx in schema readonly grant select on tables to readonly;