Thread: Not able to access schema functions and table...
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.
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=>
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
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:
Le 19/02/2010 08:21, dipti shah a écrit :> execute permissions for only few users(like above user1).*> 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
>
You should at least GRANT USAGE on your schema mydb_schema to your users.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
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