Thread: Select permissions on system tables
I created a view as: CREATE OR REPLACE VIEW caviar_schemas AS SELECT n.oid::integer AS schema_id, n.nspname::information_schema.sql_identifier::text AS schema_name FROM pg_namespace n, pg_authid u WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'MEMBER'::text) AND u.rolname::information_schema.sql_identifier::text <> 'postgres'::text ORDER BY n.nspname::information_schema.sql_identifier::text; I then granted Select permissions on both the view and underlying tables to public. When I select form the table as a non super user I get no error, but no rows. Super users select from the view and underlying tables fine. How can I give select only permissions to this view to non super users? Thanks
I think that your problem is related with the search_path, rather than with grants. On 4 Nov 2006 07:24:02 -0800, ketema@gmail.com <ketema@gmail.com> wrote: > I created a view as: > CREATE OR REPLACE VIEW caviar_schemas AS > SELECT n.oid::integer AS schema_id, > n.nspname::information_schema.sql_identifier::text AS schema_name > FROM pg_namespace n, pg_authid u > WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'MEMBER'::text) > AND u.rolname::information_schema.sql_identifier::text <> > 'postgres'::text > ORDER BY n.nspname::information_schema.sql_identifier::text; > > I then granted Select permissions on both the view and underlying > tables to public. When I select form the table as a non super user I > get no error, but no rows. Super users select from the view and > underlying tables fine. How can I give select only permissions to this > > view to non super users? > > Thanks > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > -- Guido Barosio ----------------------- http://www.globant.com guido.barosio@globant.com