út 4. 4. 2023 v 21:11 odesílatel Pavel Stehule <pavel.steh...@gmail.com>
napsal:
>
>
> út 4. 4. 2023 v 20:50 odesílatel Pavel Stehule <pavel.steh...@gmail.com>
> napsal:
>
>>
>>
>> út 4. 4. 2023 v 19:55 odesílatel Tom Lane <t...@sss.pgh.pa.us> napsal:
>>
>>> Pavel Stehule <pavel.steh...@gmail.com> writes:
>>> > út 4. 4. 2023 v 18:42 odesílatel Tom Lane <t...@sss.pgh.pa.us> napsal:
>>> >> Basically, I want to reject this on the grounds that it's not
>>> >> useful enough to justify the overhead of marking the "role" GUC
>>> >> as GUC_REPORT. The problems with it not going to work properly
>>> >> with old servers are an additional reason not to like it.
>>>
>>> > If I understand to next comment correctly, the overhead should not be
>>> too
>>> > big
>>>
>>> Yeah, but how big is the use-case? The reason I'm skeptical is that
>>> half the time what you're going to get is "none":
>>>
>>> $ psql
>>> psql (16devel)
>>> Type "help" for help.
>>>
>>> regression=# show role;
>>> role
>>> ------
>>> none
>>> (1 row)
>>>
>>> That's required by SQL spec I believe, but that doesn't make it useful
>>> data to keep in one's prompt.
>>>
>>
>> Who needs it, and who uses different roles, then very quickly uses SET
>> ROLE TO command.
>>
>> But I fully agree so current behavior can be a little bit messy. I like
>> this feature, and I think it can have some benefits. Proposed
>> implementation is minimalistic.
>>
>> One hard problem is translation of the oid of current_user to name. It
>> requires an opened transaction, and then it cannot be postponed to the end
>> of the statement. On the other hand, when the change of role is done inside
>> a nested command, then it should not be visible from the client side.
>>
>> Can you accept the introduction of a new invisible GUC, that can be
>> modified only by SET ROLE TO command when it is executed as top command?
>>
>
> It was stupid idea.
>
> There can be implemented fallbacks. When the role is "none", then the
> :USER can be displayed instead.
>
> It can work, because the custom role "none" is not allowed
>
> (2023-04-04 21:10:25) postgres=# create role none;
> ERROR: role name "none" is reserved
> LINE 1: create role none;
>
> ?
>
>
attached updated patch
Regards
Pavel
>
>
>
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>>
>>
>>>
>>> regards, tom lane
>>>
>>
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 9f72dd29d8..966cce9559 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2482,6 +2482,7 @@ const char *PQparameterStatus(const PGconn *conn, const char *paramName);
<varname>in_hot_standby</varname>,
<varname>is_superuser</varname>,
<varname>session_authorization</varname>,
+ <varname>role</varname>,
<varname>DateStyle</varname>,
<varname>IntervalStyle</varname>,
<varname>TimeZone</varname>,
@@ -2496,7 +2497,8 @@ const char *PQparameterStatus(const PGconn *conn, const char *paramName);
9.0;
<varname>default_transaction_read_only</varname> and
<varname>in_hot_standby</varname> were not reported by releases before
- 14.)
+ 14;
+ <varname>role</varname> was not reported by releases before 16;)
Note that
<varname>server_version</varname>,
<varname>server_encoding</varname> and
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 29bbec2188..98669fc18a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -4540,7 +4540,24 @@ testdb=> <userinput>INSERT INTO my_table VALUES (:'content');</userinput>
<listitem><para>The port number at which the database server is listening.</para></listitem>
</varlistentry>
- <varlistentry id="app-psql-prompting-n">
+ <varlistentry id="app-psql-prompting-n-uc">
+ <term><literal>%N</literal></term>
+ <listitem>
+ <para>
+ The database role name. This value is specified by command
+ <command>SET ROLE</command>. Until execution of this command
+ the value is set to the database session user name.
+ </para>
+
+ <para>
+ This substitution requires <productname>PostgreSQL</productname>
+ version 16 and up. When you use older version, the empty string
+ is used instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="app-psql-prompting-n-lc">
<term><literal>%n</literal></term>
<listitem>
<para>
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 8062589efd..3eec4768b3 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -4174,7 +4174,7 @@ struct config_string ConfigureNamesString[] =
{"role", PGC_USERSET, UNGROUPED,
gettext_noop("Sets the current role."),
NULL,
- GUC_IS_NAME | GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_NOT_WHILE_SEC_REST
+ GUC_IS_NAME | GUC_REPORT | GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_NOT_WHILE_SEC_REST
},
&role_string,
"none",
diff --git a/src/bin/psql/prompt.c b/src/bin/psql/prompt.c
index 969cd9908e..7399bacd5f 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -165,6 +165,41 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
if (pset.db)
strlcpy(buf, session_username(), sizeof(buf));
break;
+ /* DB server user role */
+ case 'N':
+ if (pset.db)
+ {
+ int minServerMajor;
+ int serverMajor;
+ const char *rolename;
+
+ /*
+ * This feature requires GUC "role" to be marked
+ * as GUC_REPORT. Without it is hard to specify fallback
+ * result. Returning empty value can be messy, returning
+ * PQuser like session_username can be messy too.
+ * Exec query is not too practical too, because it doesn't
+ * work when session is not in transactional state, and
+ * CURRENT_ROLE returns different result when role is not
+ * explicitly specified by SET ROLE.
+ */
+ minServerMajor = 1600;
+ serverMajor = PQserverVersion(pset.db) / 100;
+ if (serverMajor >= minServerMajor)
+ {
+ rolename = PQparameterStatus(pset.db, "role");
+
+ /* fallback when role is not set yet */
+ if (strcmp(rolename, "none") == 0)
+ rolename = session_username();
+ }
+
+ if (rolename)
+ strlcpy(buf, rolename, sizeof(buf));
+ else
+ buf[0] = '\0';
+ }
+ break;
/* backend pid */
case 'p':
if (pset.db)
