Postgres Pro
Downloads
Home   >   mailing lists

Thread: postgres 9.3

postgres 9.3

From
Ramesh T
Date:
I have one database and two schemas in that public,preview and role preview
automatically i want to connect preview schema with preview role.

set search_path to preview.
show search_path;
..
preview.


when disconnect and connect database it's showing $user$:public schema not showing preview.

what is the problem..?how to resolve the issue...?


when session closed showing postgres user..even set preview.

Re: postgres 9.3

From
Melvin Davidson
Date:
You cannot directly "connect" one schema with another,
but you can set the default to have both in the search_path.
Uncomment the #search_path parameter in Postgresql.conf
and change it to
search_path = 'preview, role,public,"$user",public'   

The make sure you do:
sudo su - postgres
pg_ctl reload [-D datadir]

On Sat, Sep 19, 2015 at 4:36 AM, Ramesh T <rameshparnanditech@gmail.com> wrote:
I have one database and two schemas in that public,preview and role preview
automatically i want to connect preview schema with preview role.

set search_path to preview.
show search_path;
..
preview.


when disconnect and connect database it's showing $user$:public schema not showing preview.

what is the problem..?how to resolve the issue...?


when session closed showing postgres user..even set preview.



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: postgres 9.3

From
John R Pierce
Date:
On 9/21/2015 11:52 AM, Melvin Davidson wrote:
> You cannot directly "connect" one schema with another,
> but you can set the default to have both in the search_path.
> Uncomment the #search_path parameter in Postgresql.conf
> and change it to
> search_path = 'preview, role,public,"$user",public'

the above would change it globally for all databases and users on the
system, which is, IMHO, probably not what you want to do.

better would be to...

     ALTER ROLE username SET SEARCH_PATH='preview,"$user", public';
or
     ALTER DATABASE dbname SET...;

and then this change just applies to that named role or database...

to the OP,  a simple SET xxxx   only applies to the current session, its
not remembered.   ALTER ROLE (or ALTER DATABASE) will remember the
setting for the specified user or database.

but if as you said, its the "preview" role that you want to access the
"preview" schema, then the default search path would suffice, as $USER
== preview.



--
john r pierce, recycling bits in santa cruz

Re: postgres 9.3

From
Alvaro Herrera
Date:
John R Pierce wrote:

> better would be to...
>
>     ALTER ROLE username SET SEARCH_PATH='preview,"$user", public';
> or
>     ALTER DATABASE dbname SET...;
>
> and then this change just applies to that named role or database...

(or
   ALTER ROLE username IN DATABASE dbname SET ..
which applies to the specified role in the specified database)

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: postgres 9.3

From
Melvin Davidson
Date:
You are correct John, but in this case, he stated only one (1) database. So changing the search path in .postgresql.conf  simplifies things for all users.
However, should he create additional databases later on, then yes, your solution would be better.

On Mon, Sep 21, 2015 at 3:08 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
John R Pierce wrote:

> better would be to...
>
>     ALTER ROLE username SET SEARCH_PATH='preview,"$user", public';
> or
>     ALTER DATABASE dbname SET...;
>
> and then this change just applies to that named role or database...

(or
   ALTER ROLE username IN DATABASE dbname SET ..
which applies to the specified role in the specified database)

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.