Thread: select a list of schema names

I want to implement a multi-company database where each schema represents 
different company.

I created a number of schemas in a database.

How to select a list of schema names which current user is authorized to 
access ?

I want to get the list of companies to allow user pick the one.

On Fri, Mar 25, 2005 at 09:50:18PM +0200, Andrus Moor wrote:
> 
> How to select a list of schema names which current user is authorized to 
> access ?

See "System Catalogs" and "System Information Functions" (or
"Miscellaneous Functions") in the documentation.  Here are links
to documentation for the latest release:

http://www.postgresql.org/docs/8.0/interactive/catalogs.html
http://www.postgresql.org/docs/8.0/interactive/functions-info.html

Something like this might be what you're looking for:

SELECT nspname
FROM pg_namespace
WHERE has_schema_privilege(nspname, 'USAGE')
ORDER BY nspname;

Note that USAGE privilege on a schema doesn't necessarily mean the
user has any privileges on the objects in that schema.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/