Not able to access schema functions and table...

Le 19/02/2010 08:21, dipti shah a �crit :

Hi,

I have executed below queries.

CREATE SCHEMA mydb_schema AUTHORIZATION postgres;

GRANT ALL ON SCHEMA mydb_schema TO postgres;

REVOKE ALL ON SCHEMA mydb_schema FROM PUBLIC;
REVOKE ALL ON FUNCTION mydb_schema.readtable() FROM PUBLIC;

GRANT EXECUTE ON FUNCTION mydb_schema.readtable() to user1;

GRANT SELECT ON mydb_schema.test1 TO user1;

$ psql -h postgresqlhost.aus -d mydb -U user1
psql (8.4.1)
Type "help" for help.
user1=> select mydb_schema.readtable();
ERROR: permission denied for schema mydb_schema
user1=> select * from mydb_schema.test1;
ERROR: permission denied for mydb_schema
LINE 1: select * from mydb_schema.test1;
^
user1=>

Could anyone please tell me what is wrong here? *I want users to have only
select persions on tables of mydb_schema schema and function readtable
execute permissions for only few users(like above user1).*

You should at least GRANT USAGE on your schema mydb_schema to your users.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Thanks. That worked.

On Fri, Feb 19, 2010 at 2:02 PM, Guillaume Lelarge
<guillaume@lelarge.info>wrote:

On 19/02/10 07:21, dipti shah wrote:

Hi,

I have executed below queries.

CREATE SCHEMA mydb_schema AUTHORIZATION postgres;

GRANT ALL ON SCHEMA mydb_schema TO postgres;

REVOKE ALL ON SCHEMA mydb_schema FROM PUBLIC;

user1=> select * from mydb_schema.test1;
ERROR: permission denied for mydb_schema

Could anyone please tell me what is wrong here?

There is a USAGE permission for the schema that you need to GRANT.

--
Richard Huxton
Archonet Ltd