Thread: schema and roles
Hi,
several days ago i wrote a post but i did not get any answer, so i'm asking again just in case of someone hasn't seen it.
in fact i would like to know what are the default schemas allowed or active for a particular role.
usually it's public and $user, but how to get those information ?
i mean if i'm logged with the role "test_user", typing show search_path; will show me all default schemas.
1. is there another place (in som pg_... table) where this information is stored ?
2. if yes how can i get it ?
After that, i know that using alter user test_user .... i can add another schema as default, but how to remove some ?
thanks a lot,
--
Alain
------------------------------------
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
several days ago i wrote a post but i did not get any answer, so i'm asking again just in case of someone hasn't seen it.
in fact i would like to know what are the default schemas allowed or active for a particular role.
usually it's public and $user, but how to get those information ?
i mean if i'm logged with the role "test_user", typing show search_path; will show me all default schemas.
1. is there another place (in som pg_... table) where this information is stored ?
2. if yes how can i get it ?
After that, i know that using alter user test_user .... i can add another schema as default, but how to remove some ?
thanks a lot,
--
Alain
------------------------------------
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
On Fri, 2008-10-17 at 19:45 +0200, Alain Roger wrote:
> i mean if i'm logged with the role "test_user", typing show
> search_path; will show me all default schemas.
On SHOW, the manual saith[1]:
"The function current_setting produces equivalent output" ...
SELECT current_setting('search_path');
It's also in pg_settings, though not listed in the (8.2) manual:
SELECT * FROM pg_settings WHERE name = 'search_path';
> After that, i know that using alter user test_user .... i can add
> another schema as default, but how to remove some ?
> thanks a lot,
ALTER USER ... SET search_path ... will always replace the schema search
path with the value you specify, so simply leave it out of that list to
effectively remove it.
P.S. In case there's any question, search_path has nothing to do with
security and access rights to schemas.
[1] http://www.postgresql.org/docs/8.2/interactive/sql-show.html
- Josh Williams