Hi
pá 3. 2. 2023 v 21:43 odesílatel Pavel Stehule <pavel.steh...@gmail.com>
napsal:
>
>
> pá 3. 2. 2023 v 21:21 odesílatel Tom Lane <t...@sss.pgh.pa.us> napsal:
>
>> Pavel Stehule <pavel.steh...@gmail.com> writes:
>> > Both patches are very simple - and they use almost already prepared
>> > infrastructure.
>>
>> It's not simple at all to make the psql feature depend on marking
>> "role" as GUC_REPORT when it never has been before. That will
>> cause the feature to misbehave when using older servers. I'm
>> even less impressed by having it fall back on PQuser(), which
>> would be misleading at exactly the times when it matters.
>>
>
> It is a good note. This can be disabled for older servers, and maybe it
> can introduce its own GUC (and again - it can be disallowed for older
> servers).
>
Here is another version. For older servers it shows the string ERR0A000.
That is ERR code of "feature is not supported"
> My goal at this moment is to get some prototype. We can talk if this
> feature request is valid or not, and we can talk about implementation.
>
> There is another possibility to directly execute "select current_user()"
> instead of looking at status parameters inside prompt processing. It can
> work too.
>
I tested using the query SELECT CURRENT_USER, but I don't think it is
usable now, because it doesn't work in the broken transaction.
Regards
Pavel
>
> Regards
>
> Pavel
>
>
>
>
>
>> regards, tom lane
>>
>
From 3b42bd47db4488f320b166d4c580193bd2c02de8 Mon Sep 17 00:00:00 2001
From: "ok...@github.com" <pavel.steh...@gmail.com>
Date: Sat, 4 Feb 2023 21:16:30 +0100
Subject: [PATCH] implementation of psql prompt substitution %N
this substitution shows used role specified by command SET ROLE.
it can show string ERRA0000 (SQL code - feature not supported)
when the variable "role" is not reported (PostgreSQL 15 and older).
---
doc/src/sgml/libpq.sgml | 4 +++-
doc/src/sgml/ref/psql-ref.sgml | 21 +++++++++++++++-
src/backend/utils/misc/guc_tables.c | 2 +-
src/bin/psql/prompt.c | 37 +++++++++++++++++++++++++++++
4 files changed, 61 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 0e7ae70c70..6a2f92b368 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2270,6 +2270,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>,
@@ -2284,7 +2285,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 dc6528dc11..5e313f06ab 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -4509,7 +4509,26 @@ 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 <literal>none</literal>. The value is same like
+ the value of the parameter <varname>role</varname> (can be
+ displayed by <command>SHOW</command>.
+ </para>
+
+ <para>
+ This substitution requires <productname>PostgreSQL</productname>
+ version 15 and up. When you use older version, the error value
+ <literal>ERR0A000</literal> 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 b46e3b8c55..3188fd015d 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -4144,7 +4144,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..ef720c8496 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -165,6 +165,43 @@ 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;
+
+ /*
+ * 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)
+ {
+ const char *val;
+
+ val = PQparameterStatus(pset.db, "role");
+ if (val)
+ strlcpy(buf, val, sizeof(buf));
+ else
+ /* missing data */
+ strlcpy(buf, "ERR02000", sizeof(buf));
+ }
+ else
+ {
+ /* feature not supported */
+ strlcpy(buf, "ERR0A000", sizeof(buf));
+ }
+ }
+ break;
/* backend pid */
case 'p':
if (pset.db)
--
2.39.1
