GRANT USAGE ON DATABASE xxx TO public fails

[Birchall, Austen]

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#//002p00000
007000000



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

[Thomas Kellerer]

Post by Birchall, Austen
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

[Philip Couling]

Post by Birchall, Austen
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 ]