Thread: GRANT USAGE ON DATABASE xxx TO public fails

Birchall, Austen, 12.02.2013 12:36:
> 8.4.13 on Red Hat 6 64 bit
>
> 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.

You probably want

    grant connect on database agency to public;

But you also need to give public access to the tables. With 8.4 you will need to do that for each
table manually (grant select on foobar to public). With a more recent version you could grant
that to all tables in a schema with a single statement.


Thomas

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 ]