Re: Revoking CREATE TABLE - Mailing list pgsql-general

Hi Tom,

Thanks for your help and the hint (off-line) to use the \dn+ command.
You've hit the nail on the head sir!

\dn+
WARNING:  nonstandard use of \\ in a string literal at character 281
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
                                                               List of
schemas
        Name        |  Owner   |                            Access
privileges                             |           Description
--------------------+----------+----------------------------------------
----------------------------------+----------------------------------
 information_schema | postgres | {postgres=UC/postgres,=U/postgres}
|
 pg_catalog         | postgres | {postgres=UC/postgres,=U/postgres}
| system catalog schema
 pg_toast           | postgres |
| reserved schema for TOAST tables
 pg_toast_temp_1    | postgres |
|
 public             | postgres |
{postgres=UC/postgres,=U/postgres,gb_ro=UC/postgres,gb_owner=C/postgres}
| standard public schema
(5 rows)

I'd not used that before.

It shows that the gb_ro user also had extra privs granted to it for the
public schema too (unless I'm reading it wrong). I've revoked them
('all' and 'create') and it works fine now!

There should be 2 additional nologin roles on the db - gb_role and
gb_role_ro - all object privs should be via them, gb_ro should not have
its own privileges.

Thanks again, you're a star!

Pif



--
 The Wellcome Trust Sanger Institute is operated by Genome Research
 Limited, a charity registered in England with number 1021457 and a
 company registered in England with number 2742969, whose registered
 office is 215 Euston Road, London, NW1 2BE.