Thread: Per-database search_path

Per-database search_path

From
David Fetter
Date:
Folks,

I'd like to propose a feature for 8.3, which would let login roles
have a default search_path both globally, as it is now, and
per-database.  This is because in general no two databases have
any schemas in common, and a login role should be able to do something
reasonable just by connecting.

What would be involved with making this so?  So far, I can see:

* Storing this information in pg_catalog somehow
* Setting the search_path when a login role connects

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: Per-database search_path

From
Josh Berkus
Date:
DF,

> I'd like to propose a feature for 8.3, which would let login roles
> have a default search_path both globally, as it is now, and
> per-database.  This is because in general no two databases have
> any schemas in common, and a login role should be able to do something
> reasonable just by connecting.

Yes.  I've been bitten by this more than once ...

However, it almost seems like this would become a piece of the other 
per-database-user stuff we'd like to do, like "local superuser".

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco


Re: Per-database search_path

From
David Fetter
Date:
On Fri, Sep 29, 2006 at 01:06:09PM -0700, Josh Berkus wrote:
> DF,
> 
> > I'd like to propose a feature for 8.3, which would let login roles
> > have a default search_path both globally, as it is now, and
> > per-database.  This is because in general no two databases have
> > any schemas in common, and a login role should be able to do
> > something reasonable just by connecting.
> 
> Yes.  I've been bitten by this more than once ...

Come to think of it, this ties in with the "ON (DIS)CONNECT TRIGGER"
idea that others have brought up in the past.  The trigger idea may be
too big a hammer, and might even be ill-advised from a design point of
view, but I thought I'd bring it up anyhow.

> However, it almost seems like this would become a piece of the other
> per-database-user stuff we'd like to do, like "local superuser".

I'm not sure that's the same.  The thing about superuser as it exists
now is the ability to write to the filesystem, which means that
there's no boundary really possible.  Maybe some kind of tiered system
of users with more than 2 tiers...but that sounds pretty byzantine to
me.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: Per-database search_path

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> On Fri, Sep 29, 2006 at 01:06:09PM -0700, Josh Berkus wrote:
>> However, it almost seems like this would become a piece of the other
>> per-database-user stuff we'd like to do, like "local superuser".

> I'm not sure that's the same.  The thing about superuser as it exists
> now is the ability to write to the filesystem, which means that
> there's no boundary really possible.

Yeah.  ISTM the correct generalization is "per-user per-database default
GUC settings", which has nothing to do with superuserness.
        regards, tom lane


Re: Per-database search_path

From
David Fetter
Date:
On Fri, Sep 29, 2006 at 05:41:35PM -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Fri, Sep 29, 2006 at 01:06:09PM -0700, Josh Berkus wrote:
> >> However, it almost seems like this would become a piece of the
> >> other per-database-user stuff we'd like to do, like "local
> >> superuser".
> 
> > I'm not sure that's the same.  The thing about superuser as it
> > exists now is the ability to write to the filesystem, which means
> > that there's no boundary really possible.
> 
> Yeah.  ISTM the correct generalization is "per-user per-database
> default GUC settings", which has nothing to do with superuserness.

This sounds like a TODO for 8.3.  What wrinkles might this involve?
Offhand, I'm thinking that it would touch the inheritance stuff that
roles have.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: Per-database search_path

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> On Fri, Sep 29, 2006 at 05:41:35PM -0400, Tom Lane wrote:
>> Yeah.  ISTM the correct generalization is "per-user per-database
>> default GUC settings", which has nothing to do with superuserness.

> This sounds like a TODO for 8.3.  What wrinkles might this involve?

Probably rethink the rolconfig/datconfig representation.  Maybe it's
time for a separate catalog for the settings.

> Offhand, I'm thinking that it would touch the inheritance stuff that
> roles have.

No, it wouldn't, because defaults only apply at the instant of
connection, so there's no inheritance or SET ROLE to worry about.
Whatever role you log in as is what you get.
        regards, tom lane


Re: Per-database search_path

From
Bruce Momjian
Date:
Added to TODO:
* Allow more complex user/database default GUC settings  Currently, ALTER USER and ALTER DATABASE support per-user and
per-databasedefaults.  Consider adding per-user-and-database  defaults so things like search_path can be defaulted for
a specific user connecting to a specific database.
 


---------------------------------------------------------------------------

Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Fri, Sep 29, 2006 at 05:41:35PM -0400, Tom Lane wrote:
> >> Yeah.  ISTM the correct generalization is "per-user per-database
> >> default GUC settings", which has nothing to do with superuserness.
> 
> > This sounds like a TODO for 8.3.  What wrinkles might this involve?
> 
> Probably rethink the rolconfig/datconfig representation.  Maybe it's
> time for a separate catalog for the settings.
> 
> > Offhand, I'm thinking that it would touch the inheritance stuff that
> > roles have.
> 
> No, it wouldn't, because defaults only apply at the instant of
> connection, so there's no inheritance or SET ROLE to worry about.
> Whatever role you log in as is what you get.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Per-database search_path

From
David Fetter
Date:
On Fri, Sep 29, 2006 at 11:55:18PM -0400, Bruce Momjian wrote:
> 
> Added to TODO:
> 
>     * Allow more complex user/database default GUC settings
>     
>       Currently, ALTER USER and ALTER DATABASE support per-user and
>       per-database defaults.  Consider adding per-user-and-database
>       defaults so things like search_path can be defaulted for a
>       specific user connecting to a specific database.

Is anybody claiming this TODO?  Is the design fleshed out enough for
someone to go forward with it?

Cheers,
D

> 
> ---------------------------------------------------------------------------
> 
> Tom Lane wrote:
> > David Fetter <david@fetter.org> writes:
> > > On Fri, Sep 29, 2006 at 05:41:35PM -0400, Tom Lane wrote:
> > >> Yeah.  ISTM the correct generalization is "per-user per-database
> > >> default GUC settings", which has nothing to do with superuserness.
> > 
> > > This sounds like a TODO for 8.3.  What wrinkles might this involve?
> > 
> > Probably rethink the rolconfig/datconfig representation.  Maybe it's
> > time for a separate catalog for the settings.
> > 
> > > Offhand, I'm thinking that it would touch the inheritance stuff that
> > > roles have.
> > 
> > No, it wouldn't, because defaults only apply at the instant of
> > connection, so there's no inheritance or SET ROLE to worry about.
> > Whatever role you log in as is what you get.
> > 
> >             regards, tom lane
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> 
> -- 
>   Bruce Momjian   bruce@momjian.us
>   EnterpriseDB    http://www.enterprisedb.com
> 
>   + If your life is a hard drive, Christ can be your backup. +

-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: Per-database search_path

From
Bruce Momjian
Date:
David Fetter wrote:
> On Fri, Sep 29, 2006 at 11:55:18PM -0400, Bruce Momjian wrote:
> > 
> > Added to TODO:
> > 
> >     * Allow more complex user/database default GUC settings
> >     
> >       Currently, ALTER USER and ALTER DATABASE support per-user and
> >       per-database defaults.  Consider adding per-user-and-database
> >       defaults so things like search_path can be defaulted for a
> >       specific user connecting to a specific database.
> 
> Is anybody claiming this TODO?  Is the design fleshed out enough for
> someone to go forward with it?

I think we just need to decide on the user API for this.

---------------------------------------------------------------------------


> 
> Cheers,
> D
> 
> > 
> > ---------------------------------------------------------------------------
> > 
> > Tom Lane wrote:
> > > David Fetter <david@fetter.org> writes:
> > > > On Fri, Sep 29, 2006 at 05:41:35PM -0400, Tom Lane wrote:
> > > >> Yeah.  ISTM the correct generalization is "per-user per-database
> > > >> default GUC settings", which has nothing to do with superuserness.
> > > 
> > > > This sounds like a TODO for 8.3.  What wrinkles might this involve?
> > > 
> > > Probably rethink the rolconfig/datconfig representation.  Maybe it's
> > > time for a separate catalog for the settings.
> > > 
> > > > Offhand, I'm thinking that it would touch the inheritance stuff that
> > > > roles have.
> > > 
> > > No, it wouldn't, because defaults only apply at the instant of
> > > connection, so there's no inheritance or SET ROLE to worry about.
> > > Whatever role you log in as is what you get.
> > > 
> > >             regards, tom lane
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: explain analyze is your friend
> > 
> > -- 
> >   Bruce Momjian   bruce@momjian.us
> >   EnterpriseDB    http://www.enterprisedb.com
> > 
> >   + If your life is a hard drive, Christ can be your backup. +
> 
> -- 
> David Fetter <david@fetter.org> http://fetter.org/
> phone: +1 415 235 3778        AIM: dfetter666
>                               Skype: davidfetter
> 
> Remember to vote!

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Per-database search_path

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> David Fetter wrote:
>> Is anybody claiming this TODO?  Is the design fleshed out enough for
>> someone to go forward with it?

> I think we just need to decide on the user API for this.

... and the catalog representation.
        regards, tom lane