Re: GRANT USAGE ON DATABASE xxx TO public fails

On 12/02/2013 11:36, Birchall, Austen wrote:
> 8.4.13 on Red Hat 6 64 bit
>
>
>
> Hi
>
>
>
> I’m setting up a database to use with ESRI sde/arcgis following
>
>
>
>
>
> http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002p00000007000000
>
>
>
> This includes the step
>
>
>
> 1. Grant usage privileges to the public or to specific roles and
> groups so other users can access the database.
>
> 2. GRANT USAGE
>
> 3. ON DATABASE agency
>
> TO public;
>
>
>
>
>
> Which fails and seems to be incorrect.
>
>
>
> postgres=# GRANT USAGE ON DATABASE dmms TO public;
>
> ERROR: invalid privilege type USAGE for database
>
> postgres=#
>
>
>
> An initial look at docs strongly suggests that this is the case.
>
>
>
> Is there a different command I can run which will give the same outcome
> i.e. that public can access the database.
>
>
>
> Many thanks
>
>
>
> Austen Birchall
>
>
>
>
>

As per the docs "usage" is not a privilege on a schema.
I believe the one you were looking for is CONNECT.
Revoking this bans a user from any access to the database, granting it
allows them to login to the DB (if their role is a LOGIN role) but on
it's own grant's nothing else. They must also have usage on the
schema(s) etc.

http://www.postgresql.org/docs/current/static/sql-grant.html

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [
PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]