Thread: Revoking the right to create (non-temporary) tables?

Is there a way to revoke the right of a given user from creating
tables?  By preference I'd restrict only creating non-temporary tables.

I've tried both
REVOKE CREATE ON DATABASE dbase FROM user;
and:
REVOKE CREATE ON SCHEMA public FROM user;

neither of these seem to work.  Help?

The idea I'm looking for is a "safe" user- a user that can view the
production database but can't change or create anything.  This allows
fumble-fingered admins to keep a psql open in a window without having to
worry about typing the wrong command in the wrong window, and also
allows us to give out "guest" accounts.

Brian

Brian Hurt <bhurt@janestcapital.com> writes:
> Is there a way to revoke the right of a given user from creating
> tables?  By preference I'd restrict only creating non-temporary tables.

> I've tried both
> REVOKE CREATE ON DATABASE dbase FROM user;
> and:
> REVOKE CREATE ON SCHEMA public FROM user;

> neither of these seem to work.  Help?

What you missed is that you have to revoke those two rights from PUBLIC,
else the user still has 'em via his implicit membership in PUBLIC.  The
REVOKEs you gave were no-ops, because there was no explicit GRANT to
that particular user for them to undo.  The only GRANTs that are in the
permissions structure by default are to PUBLIC ...

            regards, tom lane