Not able to access schema functions and table...

[dipti shah <shahdipti1980@xxxxxxxxx>]

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).

 

Please let me know if there is any alternative way.

 

Thanks,

Dipti