Thread: Missing variable "role" in "pg_settings"?
Hi The per-session variable "role" is not shown when doing "select pg_settings". It is, however, possible to set it using "set role ...", and to query it using "show role". Is this per design, or is this a bug. I stumbled over this issue, because pgadmin3 lets you do "alter user set <variable> <value" using the gui (You can specify per-user variables in a tab in the user dialog). But pgadmin only lets one choose variables that are included in pg_settings, so one cannot archive the effect of "alter user <user> set role <role>" (i.e change the initial role of a user) using the gui. In case omiting role from pg_settings is per design, how could pgadmin find _all_ variables that can be set per user? greetings, Florian Pflug
"Florian G. Pflug" <fgp@phlo.org> writes:
> The per-session variable "role" is not shown when
> doing "select pg_settings". It is, however, possible
> to set it using "set role ...", and to query it using
> "show role". Is this per design, or is this a bug.
It's marked NO_SHOW_ALL in guc.c. I'm not sure about the reasoning
for this --- session_authorization is the same way, and we probably
just copied that when we made the role variable; but I've forgotten
what the rationale for marking session_authorization as NO_SHOW_ALL
was.
> In case omiting role from pg_settings is per design,
> how could pgadmin find _all_ variables that can be set per user?
If this is an argument for not having *any* NO_SHOW_ALL variables,
I think the answer will be "no".
regards, tom lane
Tom Lane wrote: > "Florian G. Pflug" <fgp@phlo.org> writes: > >>The per-session variable "role" is not shown when >>doing "select pg_settings". It is, however, possible >>to set it using "set role ...", and to query it using >>"show role". Is this per design, or is this a bug. > > It's marked NO_SHOW_ALL in guc.c. I'm not sure about the reasoning > for this --- session_authorization is the same way, and we probably > just copied that when we made the role variable; but I've forgotten > what the rationale for marking session_authorization as NO_SHOW_ALL > was. Isn't "set session authorization <user>" basically the same as "set role <user>" (Not from an implemenation standpoint, but from the standpoint of the user), with "set role" being more generic, because it's also allowed for non-superusers? In that case, omiting "session_authorization" while showing "role" would make sense.. And, additionally, "alter user <user> set session authorization <other user>" doesn't seem to make much sense... >>In case omiting role from pg_settings is per design, >>how could pgadmin find _all_ variables that can be set per user? > > If this is an argument for not having *any* NO_SHOW_ALL variables, > I think the answer will be "no". I don't quite understand what the "no" refers to... I think Andreas Pflug now commited a patch that manually adds "role" to the list of per-user variables in pgadmin3 - but in the long run, there should be a better solution... greetings, Florian Pflug
Based on this email, should we be showing ROLE from SHOW ALL? --------------------------------------------------------------------------- Florian G. Pflug wrote: > Tom Lane wrote: > > "Florian G. Pflug" <fgp@phlo.org> writes: > > > >>The per-session variable "role" is not shown when > >>doing "select pg_settings". It is, however, possible > >>to set it using "set role ...", and to query it using > >>"show role". Is this per design, or is this a bug. > > > > It's marked NO_SHOW_ALL in guc.c. I'm not sure about the reasoning > > for this --- session_authorization is the same way, and we probably > > just copied that when we made the role variable; but I've forgotten > > what the rationale for marking session_authorization as NO_SHOW_ALL > > was. > > Isn't "set session authorization <user>" basically the same > as "set role <user>" (Not from an implemenation standpoint, but > from the standpoint of the user), with "set role" being more generic, > because it's also allowed for non-superusers? In that case, omiting > "session_authorization" while showing "role" would make sense.. > > And, additionally, "alter user <user> set session authorization <other > user>" doesn't seem to make much sense... > > >>In case omiting role from pg_settings is per design, > >>how could pgadmin find _all_ variables that can be set per user? > > > > If this is an argument for not having *any* NO_SHOW_ALL variables, > > I think the answer will be "no". > I don't quite understand what the "no" refers to... > I think Andreas Pflug now commited a patch that manually adds > "role" to the list of per-user variables in pgadmin3 - but in the > long run, there should be a better solution... > > greetings, Florian Pflug > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Based on this email, should we be showing ROLE from SHOW ALL?
Only if you think we should be showing session_authorization too.
That was marked "no_show_all" quite a long time ago, and we have
not got complaints about it...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: >>Based on this email, should we be showing ROLE from SHOW ALL? > Only if you think we should be showing session_authorization too. > That was marked "no_show_all" quite a long time ago, and we have > not got complaints about it... Hm, but before 8.1 there was no "alter user set <variable> <value>", was there? So, a automatic way of retrieving all possible variables wasn't too important - at least not from a gui-tool perspektive.. Maybe pg_show_all_settings could take an additional parameter indicating wether to show NO_SHOW_ALL variables or not? Just my 2 (euro)-cent... greetings, Florian Pflug
"Florian G. Pflug" <fgp@phlo.org> writes:
> Tom Lane wrote:
>>> Based on this email, should we be showing ROLE from SHOW ALL?
>> Only if you think we should be showing session_authorization too.
>> That was marked "no_show_all" quite a long time ago, and we have
>> not got complaints about it...
> Hm, but before 8.1 there was no "alter user set <variable> <value>", was
> there? So, a automatic way of retrieving all possible variables wasn't
> too important - at least not from a gui-tool perspektive..
I don't see the connection offhand ... but ALTER USER SET <variable>
has been around since 7.3, so this doesn't seem to support whatever
argument you intended to make.
regards, tom lane