On Thursday 07 February 2008 09:43:33 am Ed Leafe wrote:
> On Feb 5, 2008, at 9:10 PM, johnf wrote:
> > BTW if you use information_schema
> >
> > SELECT table_name,table_schema
> > FROM information_schema.tables
> > WHERE table_type = 'BASE TABLE'
> > AND table_schema NOT IN
> > ('pg_catalog', 'information_schema');
> >
> > will get the tables.
> >
> > I know you don't like information_schema but maybe it is time we
> > reconsider.
>
> No, since it seems that that may run into permission problems for
> regular users.
>
> How about this syntax:
>
> select relname from pg_class where relkind= 'r' ;
>
> I've found it on a couple of sites, and it doesn't seem to be subject
> to the same permission limitations. Does it work for you?
>
>
> -- Ed Leafe
I checked with the Postgres list and I have been told that all users have
access to 'information_schema'. If they can open postgres in any manner they
have access. And better yet - they can only retreive table names they have
access too. BTW I have not tested this.
CREATE SCHEMA information_schema
AUTHORIZATION postgres;
GRANT ALL ON SCHEMA information_schema TO postgres;
GRANT USAGE ON SCHEMA information_schema TO public;
Or how about
select relname from pg_class where relkind= 'r' and relname not like 'pg_%'
and relname not like 'sql_%'
which eliminates the pg_ and sql_ tables. We don't want regular users
accessing 'pg_' and 'sql_' tables. This has holes too. For example Postgres
allows user tables with 'sql_' in the name. The real problem is Postgres is
so flexable. I keep running into so many corner situations.
--
John Fabiani
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/dabo-dev/[EMAIL PROTECTED]
