alter user set local_preload_libraries.

[Kyotaro HORIGUCHI]

Hello, This is about a document fix of local_preload_libraries
for the versions 9.3 and earlier to at least 8.4.


There's inconsistency between documentation about
local_preload_libraries for 9.3 and earlier and their behavior.

The 9.3 document for local_preload_libraries says that:

http://www.postgresql.org/docs/9.3/static/runtime-config-client.html

| For example, debugging could be enabled for all sessions under
| a given user name by setting this parameter with ALTER ROLE
| SET.

Ok, let's do that.

postgres=# alter user horiguti2 set local_preload_libraries='libname';
ERROR: parameter "local_preload_libraries" cannot be set after connection start

Back to 8.4 shows the same behavior.

session_preload_libraries works as expected since 9.4. It is
added at 9.4 vested the context PGC_SUSET so its setting in
pg_db_role_setting can complete its mission. On the other hand,
local_preload_libraries seems to continue to have the context
PCG_BACKEND and the behavior is same to the older versions, as
shown above. And the description about 'ALTER ROLE SET' has been
removed from config.sgml. These are done by the commit
070518ddab2.

I think we should regard the real behavior as right, right?

Eventually, local_preload_libraries seems to have almost the same
function to shared_preload_libraries, except a restriction on
load module directory.

Putting aside the meaning of its existence, anyway the third
paragraph of the description for local_preload_libraries should
be removed. The change made by the attached patch for 9.3 will
show in runtime-config-client.html "18.11. Client Connection
Defaults"


regards,

[Tom Lane]

Post by Kyotaro HORIGUCHI
postgres=# alter user horiguti2 set local_preload_libraries='libname';
ERROR: parameter "local_preload_libraries" cannot be set after connection start

Hm ... it's kind of annoying that that doesn't work; it's certainly not
hard to imagine use-cases for per-user or per-database settings of
local_preload_libraries. However, I'm not sure if we apply per-user or
per-database settings early enough in backend startup that they could
affect library loading. If we do, then the ALTER code needs to be
taught to allow this.

regards, tom lane

[Fujii Masao]

Post by Tom Lane

Post by Kyotaro HORIGUCHI
postgres=# alter user horiguti2 set local_preload_libraries='libname';
ERROR: parameter "local_preload_libraries" cannot be set after connection start

Hm ... it's kind of annoying that that doesn't work; it's certainly not
hard to imagine use-cases for per-user or per-database settings of
local_preload_libraries. However, I'm not sure if we apply per-user or
per-database settings early enough in backend startup that they could
affect library loading. If we do, then the ALTER code needs to be
taught to allow this.

ISTM that's what session_preload_libraries does. Anyway I agree with
Horiguchi that we should remove incorrect description from the document
of old versions.

Regards,

[Tom Lane]

Post by Fujii Masao

Post by Tom Lane

Post by Kyotaro HORIGUCHI
postgres=# alter user horiguti2 set local_preload_libraries='libname';
ERROR: parameter "local_preload_libraries" cannot be set after connection start

Hm ... it's kind of annoying that that doesn't work; it's certainly not
hard to imagine use-cases for per-user or per-database settings of
local_preload_libraries. However, I'm not sure if we apply per-user or
per-database settings early enough in backend startup that they could
affect library loading. If we do, then the ALTER code needs to be
taught to allow this.

ISTM that's what session_preload_libraries does.

No, the reason for the distinction is that session_preload_libraries is
superuser-only, and can load anything, while local_preload_libraries is
(supposed to be) settable by ordinary users, and therefore is restricted
to load only a subset of available libraries. But if you can't apply it
via ALTER USER that seems like it takes away a lot of the value of having
a non-SUSET GUC in this area.

regards, tom lane

[Fujii Masao]

Post by Tom Lane

Post by Fujii Masao

Post by Tom Lane

Post by Kyotaro HORIGUCHI
postgres=# alter user horiguti2 set local_preload_libraries='libname';
ERROR: parameter "local_preload_libraries" cannot be set after connection start

Hm ... it's kind of annoying that that doesn't work; it's certainly not
hard to imagine use-cases for per-user or per-database settings of
local_preload_libraries. However, I'm not sure if we apply per-user or
per-database settings early enough in backend startup that they could
affect library loading. If we do, then the ALTER code needs to be
taught to allow this.

ISTM that's what session_preload_libraries does.

No, the reason for the distinction is that session_preload_libraries is
superuser-only, and can load anything, while local_preload_libraries is
(supposed to be) settable by ordinary users, and therefore is restricted
to load only a subset of available libraries. But if you can't apply it
via ALTER USER that seems like it takes away a lot of the value of having
a non-SUSET GUC in this area.

Agreed. I was also thinking we can set it per role, but got surprised
when I found that's impossible. Is this a problem of only
local_preload_libraries? I'm afraid that all PGC_BACKEND parameters
have the same problem.

Regards,

[Tom Lane]

Post by Fujii Masao
Agreed. I was also thinking we can set it per role, but got surprised
when I found that's impossible. Is this a problem of only
local_preload_libraries? I'm afraid that all PGC_BACKEND parameters
have the same problem.

Well, there aren't that many PGC_BACKEND parameters.

Two of them are log_connections and log_disconnections, which we've
been arguing over whether they should be controllable by non-superusers
in the first place. The only other ones are ignore_system_indexes and
post_auth_delay, which are debugging things that I can't see using with
ALTER. So this may be the only one where it's really of much interest.

But I agree that the problem is triggered by the PGC_BACKEND categorization
and not the meaning of this specific GUC.

regards, tom lane

[Kyotaro HORIGUCHI]

o <CAHGQGwFYiANahR_u_cHnz-***@mail.gmail.com>
<***@sss.pgh.pa.us>
User-Agent: Mew version 6.5 on Emacs 22.2 / Mule 5.0
=?iso-2022-jp?B?KBskQjpnGyhCKQ==?= / Meadow-3.01-dev (TSUBO-SUMIRE)
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hello, I'm getting confused.. The distinction between local_ and
session_ seems to be obscure..

Post by Tom Lane
Well, there aren't that many PGC_BACKEND parameters.
Two of them are log_connections and log_disconnections, which we've
been arguing over whether they should be controllable by non-superusers
in the first place. The only other ones are ignore_system_indexes and
post_auth_delay, which are debugging things that I can't see using with
ALTER. So this may be the only one where it's really of much interest.
But I agree that the problem is triggered by the PGC_BACKEND categorization
and not the meaning of this specific GUC.

I put some thoughts on this.

The current behavior is:

- Considering setting them in postgresql.conf, the two are
different only in the restriction for the locaion of modules
to be load. But setting them is allowed only for superuser
equivalent(DBA) so the difference has no meaning.

- Considering setting them on-session, only session_* can be
altered by superuser, but no valuable result would be
retrieved from that.

- Considering setting them through pg_db_role_setting using
ALTER DATABASE/USER statements, only superuser can set only
session_* and both sessions for superuser and non-superuser
can perform it. local_* cannot be set anyway.

- Considering inserting directly into pg_db_role_setting, only
superuser can insert any setting, including
local_preload_libraries, but it fails on session start.

| =# INSERT INTO pg_db_role_setting VALUES(0, 16384, '{local_preload_libraries=auto_explain}');
| INSERT 0 1
...
| $ psql postgres -U hoge
| WARNING: parameter "local_preload_libraries" cannot be set after connection start.

After all, I suppose the desirable requirements utilizing the
both *_preload_libraries are,

- (local|session)_preload_libraries shouldn't be altered
on-session. (should have PGC_BACKEND)

- ALTER ... SET local_preload_libraries should be done by any
user, but specified modules should be within plugins
directory.

- ALTER ... SET session_preload_libraries should be set only by
superuser, and modules in any directory can be specified.

- Both (local|session)_preload_libraries should work at session
start.

- Direct setting of pg_db_role_setting by superuser allows
arbitrary setting but by the superuser's own responsibility.

The changes needed to achieve the above requirements are,

- Now PGC_BACKEND and PGC_SUSET/USERSET are in orthogonal
relationship, not in parallel. But it seems to big change for
the fruits to reflect it in straightforward way. The new
context PGC_BACKEND_USERSET seems to be enough.

- set_config_option should allow PGC_BACKEND(_USERSET)
variables to be checked (without changing) on-session.

- PGC_BACKEND(_USERSET) variables should be allowed to be
changed by set_config_option if teached that "now is on
session starting". Now changeVal is not a boolean but
tri-state variable including
(exec-on-session|exec-on-session-start|check-only)


The above description is based on 9.4 and later. local_* would be
applicable on 9.3 and before, but PGC_BACKEND_USERSET is not
needed because they don't have session_preload_libraries.

9.5dev apparently deserves to be applied. I personally think that
applying to all live versions is desirable. But it seems to be a
kind of feature change, enabling a function which cannot be used
before..

For 9.4, since session_preload_library have been introduced, the
coexistence of current local_preload_library seems to be somewhat
crooked. We might want to apply this for 9.4.

For the earlier than 9.4, no one seems to have considered
seriously to use local_preload_library via ALTER statements so
far. Only document fix would be enough for them.


Any suggestions?

regards,

[Kyotaro HORIGUCHI]

Hello, I made a set of patches to fix this issue.

The attached files are the followings,

0001_Add_PGC_BACKEND_USERSET_v0.patch:

Add new GUC category PGC_BACKEND_USERSET and change
local_preload_libraries to use it.

0002_dblink_follow_change_of_set_config_options_v0.patch:
0003_postgres_fdw_follow_change_of_set_config_options_v0.patch

Change contrib modules to follow the change of
set_config_options.

regards,
--
Kyoaro Horiguchi
NTT Open Source Software Center

On Thu, Jul 3, 2014 at 1:05 PM, Kyotaro HORIGUCHI

Post by Kyotaro HORIGUCHI
User-Agent: Mew version 6.5 on Emacs 22.2 / Mule 5.0
=?iso-2022-jp?B?KBskQjpnGyhCKQ==?= / Meadow-3.01-dev (TSUBO-SUMIRE)
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hello, I'm getting confused.. The distinction between local_ and
session_ seems to be obscure..

Post by Tom Lane
Well, there aren't that many PGC_BACKEND parameters.
Two of them are log_connections and log_disconnections, which we've
been arguing over whether they should be controllable by non-superusers
in the first place. The only other ones are ignore_system_indexes and
post_auth_delay, which are debugging things that I can't see using with
ALTER. So this may be the only one where it's really of much interest.
But I agree that the problem is triggered by the PGC_BACKEND categorization
and not the meaning of this specific GUC.

I put some thoughts on this.
- Considering setting them in postgresql.conf, the two are
different only in the restriction for the locaion of modules
to be load. But setting them is allowed only for superuser
equivalent(DBA) so the difference has no meaning.
- Considering setting them on-session, only session_* can be
altered by superuser, but no valuable result would be
retrieved from that.
- Considering setting them through pg_db_role_setting using
ALTER DATABASE/USER statements, only superuser can set only
session_* and both sessions for superuser and non-superuser
can perform it. local_* cannot be set anyway.
- Considering inserting directly into pg_db_role_setting, only
superuser can insert any setting, including
local_preload_libraries, but it fails on session start.
| =# INSERT INTO pg_db_role_setting VALUES(0, 16384, '{local_preload_libraries=auto_explain}');
| INSERT 0 1
...
| $ psql postgres -U hoge
| WARNING: parameter "local_preload_libraries" cannot be set after connection start.
After all, I suppose the desirable requirements utilizing the
both *_preload_libraries are,
- (local|session)_preload_libraries shouldn't be altered
on-session. (should have PGC_BACKEND)
- ALTER ... SET local_preload_libraries should be done by any
user, but specified modules should be within plugins
directory.
- ALTER ... SET session_preload_libraries should be set only by
superuser, and modules in any directory can be specified.
- Both (local|session)_preload_libraries should work at session
start.
- Direct setting of pg_db_role_setting by superuser allows
arbitrary setting but by the superuser's own responsibility.
The changes needed to achieve the above requirements are,
- Now PGC_BACKEND and PGC_SUSET/USERSET are in orthogonal
relationship, not in parallel. But it seems to big change for
the fruits to reflect it in straightforward way. The new
context PGC_BACKEND_USERSET seems to be enough.
- set_config_option should allow PGC_BACKEND(_USERSET)
variables to be checked (without changing) on-session.
- PGC_BACKEND(_USERSET) variables should be allowed to be
changed by set_config_option if teached that "now is on
session starting". Now changeVal is not a boolean but
tri-state variable including
(exec-on-session|exec-on-session-start|check-only)
The above description is based on 9.4 and later. local_* would be
applicable on 9.3 and before, but PGC_BACKEND_USERSET is not
needed because they don't have session_preload_libraries.
9.5dev apparently deserves to be applied. I personally think that
applying to all live versions is desirable. But it seems to be a
kind of feature change, enabling a function which cannot be used
before..
For 9.4, since session_preload_library have been introduced, the
coexistence of current local_preload_library seems to be somewhat
crooked. We might want to apply this for 9.4.
For the earlier than 9.4, no one seems to have considered
seriously to use local_preload_library via ALTER statements so
far. Only document fix would be enough for them.
Any suggestions?
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut]

Post by Kyotaro HORIGUCHI
For the earlier than 9.4, no one seems to have considered
seriously to use local_preload_library via ALTER statements so
far. Only document fix would be enough for them.

I think local_preload_libraries never actually worked sensibly for any
specific purpose. It was designed to work with the pldebugger, but
pldebugger doesn't use it anymore.

So before we start bending the GUC system into new directions, let's ask
ourselves what the current practical application of
local_preload_libraries is and whether the proposed changes support that
use at all. I suspect there aren't any, and we should leave it alone.

I agree that we should fix the documentation in 9.3 and earlier.

[Kyotaro HORIGUCHI]

Hello,

Post by Peter Eisentraut

Post by Kyotaro HORIGUCHI
For the earlier than 9.4, no one seems to have considered
seriously to use local_preload_library via ALTER statements so
far. Only document fix would be enough for them.

I think local_preload_libraries never actually worked sensibly for any
specific purpose. It was designed to work with the pldebugger, but
pldebugger doesn't use it anymore.

Hmm, I see although I don't know pldebugger.

Post by Peter Eisentraut
So before we start bending the GUC system into new directions, let's ask
ourselves what the current practical application of
local_preload_libraries is and whether the proposed changes support that
use at all. I suspect there aren't any, and we should leave it alone.

I found this issue when trying per-pg_user (role) loading of
auto_analyze and some tweaking tool. It is not necessarily set by
the user by own, but the function to decide whether to load some
module by the session-user would be usable, at least, as for me:)

Post by Peter Eisentraut
I agree that we should fix the documentation in 9.3 and earlier.

It seems rather hard work if local_preload_library itself is not
removed or fixed as expressed..

regards,

[Kyotaro HORIGUCHI]

Hello,

Post by Kyotaro HORIGUCHI
I found this issue when trying per-pg_user (role) loading of
auto_analyze and some tweaking tool. It is not necessarily set by
the user by own, but the function to decide whether to load some
module by the session-user would be usable, at least, as for me:)

I think we could just set local_preload_libraries to PGC_USERSET and
document that subsequent changes won't take effect. That's the same way
session_preload_libraries works. That would avoid inventing another
very specialized GUC context.

It is enough for me. Since the only advantage of
PGC_BACKEND_USERSET is the capability to inhibit in-session
modification and I don't see another use case for it, I have no
objection for your opinion.

If you're interested in improving this area, I also suggest you read the
thread of

Although I don't understand even after reading this why
local_preload_libraries was PGC_SUSET, there seems to be no
reason it should be so.

The attached patch simply changes the context for local_... to
PGC_USERSET and edits the doc.

regards,

[Peter Eisentraut]

Post by Kyotaro HORIGUCHI
The attached patch simply changes the context for local_... to
PGC_USERSET and edits the doc.

I had this ready to commit, but then

Invent PGC_SU_BACKEND and mark log_connections/log_disconnections
that way.

was committed in the meantime.

Does this affect what we should do with this change?

I guess one thing to look into would be whether we could leave
local_preload_libraries as PGC_BACKEND and change
session_preload_libraries to PGC_SU_BACKEND, and then investigate
whether we could allow settings made with ALTER ROLE / SET to change
PGC_BACKEND settings.

In the meantime, I have committed documentation fixes for the back
branches 9.0 .. 9.3.

[Tom Lane]

Post by Peter Eisentraut

Post by Kyotaro HORIGUCHI
The attached patch simply changes the context for local_... to
PGC_USERSET and edits the doc.

I had this ready to commit, but then
Invent PGC_SU_BACKEND and mark log_connections/log_disconnections
that way.
was committed in the meantime.
Does this affect what we should do with this change?
I guess one thing to look into would be whether we could leave
local_preload_libraries as PGC_BACKEND and change
session_preload_libraries to PGC_SU_BACKEND, and then investigate
whether we could allow settings made with ALTER ROLE / SET to change
PGC_BACKEND settings.

Yeah, I was wondering about that while I was making the other commit.
I did not touch those variables at the time, but it would make sense
to restrict them as you suggest.

regards, tom lane

[Fujii Masao]

Post by Tom Lane

Post by Peter Eisentraut

Post by Kyotaro HORIGUCHI
The attached patch simply changes the context for local_... to
PGC_USERSET and edits the doc.

I had this ready to commit, but then
Invent PGC_SU_BACKEND and mark log_connections/log_disconnections
that way.
was committed in the meantime.
Does this affect what we should do with this change?
I guess one thing to look into would be whether we could leave
local_preload_libraries as PGC_BACKEND and change
session_preload_libraries to PGC_SU_BACKEND, and then investigate
whether we could allow settings made with ALTER ROLE / SET to change
PGC_BACKEND settings.

Yeah, I was wondering about that while I was making the other commit.
I did not touch those variables at the time, but it would make sense
to restrict them as you suggest.

+1

Also I think that it's useful to allow ALTER ROLE/DATABASE SET to
set PGC_BACKEND and PGC_SU_BACKEND parameters. So, what
about applying the attached patch? This patch allows that and
changes the context of session_preload_libraries to PGC_SU_BACKEND.

Regards,

[Kyotaro HORIGUCHI]

Hello, I overlooked this thread.

Post by Fujii Masao

Post by Tom Lane

Post by Peter Eisentraut

Post by Kyotaro HORIGUCHI
The attached patch simply changes the context for local_... to
PGC_USERSET and edits the doc.

I had this ready to commit, but then
Invent PGC_SU_BACKEND and mark log_connections/log_disconnections
that way.
was committed in the meantime.
Does this affect what we should do with this change?
I guess one thing to look into would be whether we could leave
local_preload_libraries as PGC_BACKEND and change
session_preload_libraries to PGC_SU_BACKEND, and then investigate
whether we could allow settings made with ALTER ROLE / SET to change
PGC_BACKEND settings.

Yeah, I was wondering about that while I was making the other commit.
I did not touch those variables at the time, but it would make sense
to restrict them as you suggest.

+1
Also I think that it's useful to allow ALTER ROLE/DATABASE SET to
set PGC_BACKEND and PGC_SU_BACKEND parameters. So, what
about applying the attached patch? This patch allows that and
changes the context of session_preload_libraries to PGC_SU_BACKEND.

It's not my business to decide to aplly it but I don't see
obvious problmen in it so far.

By the way, I became unable to login at all after wrongly setting
*_preload_libraries for all available users. Is there any releaf
measures for the situation? I think it's okay even if there's no
way to login again but want to know if any.

regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center

[Fujii Masao]

On Fri, Oct 10, 2014 at 5:36 PM, Kyotaro HORIGUCHI

Post by Kyotaro HORIGUCHI
Hello, I overlooked this thread.

Post by Fujii Masao

Post by Tom Lane

Post by Peter Eisentraut

Post by Kyotaro HORIGUCHI
The attached patch simply changes the context for local_... to
PGC_USERSET and edits the doc.

I had this ready to commit, but then
Invent PGC_SU_BACKEND and mark log_connections/log_disconnections
that way.
was committed in the meantime.
Does this affect what we should do with this change?
I guess one thing to look into would be whether we could leave
local_preload_libraries as PGC_BACKEND and change
session_preload_libraries to PGC_SU_BACKEND, and then investigate
whether we could allow settings made with ALTER ROLE / SET to change
PGC_BACKEND settings.

Yeah, I was wondering about that while I was making the other commit.
I did not touch those variables at the time, but it would make sense
to restrict them as you suggest.

+1
Also I think that it's useful to allow ALTER ROLE/DATABASE SET to
set PGC_BACKEND and PGC_SU_BACKEND parameters. So, what
about applying the attached patch? This patch allows that and
changes the context of session_preload_libraries to PGC_SU_BACKEND.

It's not my business to decide to aplly it but I don't see
obvious problmen in it so far.
By the way, I became unable to login at all after wrongly setting
*_preload_libraries for all available users. Is there any releaf
measures for the situation? I think it's okay even if there's no
way to login again but want to know if any.

Yep, that's a problem. You can login to the server even in that case
by, for example, executing the following commands, though.

$ export PGOPTIONS="-c *_preload_libraries="
$ psql

Regards,

[Kyotaro HORIGUCHI]

Wow.

Post by Fujii Masao

Post by Kyotaro HORIGUCHI
By the way, I became unable to login at all after wrongly setting
*_preload_libraries for all available users. Is there any releaf
measures for the situation? I think it's okay even if there's no
way to login again but want to know if any.

Yep, that's a problem. You can login to the server even in that case
by, for example, executing the following commands, though.
$ export PGOPTIONS="-c *_preload_libraries="
$ psql

Thank you. I see. It seems enough for me. The section 18.1.3 of
9.4 document describes about it,

http://www.postgresql.org/docs/9.4/static/config-setting.html

Post by Fujii Masao
18.1.4. Parameter Interaction via Shell

..

Post by Fujii Masao
On the libpq-client, command-line options can be specified
using the PGOPTIONS environment variable. When connecting to
the server, the contents of this variable are sent to the
server as if they were being executed via SQL SET at the
beginning of the session.

This implicitly denies PGC_BACKEND variables to be set by this
method but it also seems not proper to describe precisely...

regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center