public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] Introduce timeout capability for ConditionVariableSleep
29+ messages / 6 participants
[nested] [flat]
* [PATCH] Introduce timeout capability for ConditionVariableSleep
@ 2019-03-12 23:21 Shawn Debnath <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Shawn Debnath @ 2019-03-12 23:21 UTC (permalink / raw)
This patch introduces ConditionVariableTimedSleep to enable timing out
of waiting for a condition variable to get signalled.
---
src/backend/storage/lmgr/condition_variable.c | 41 +++++++++++++++++++++++++--
src/include/storage/condition_variable.h | 2 ++
2 files changed, 41 insertions(+), 2 deletions(-)
diff --git a/src/backend/storage/lmgr/condition_variable.c b/src/backend/storage/lmgr/condition_variable.c
index 58b7b51472..a0e14a5efa 100644
--- a/src/backend/storage/lmgr/condition_variable.c
+++ b/src/backend/storage/lmgr/condition_variable.c
@@ -121,9 +121,33 @@ ConditionVariablePrepareToSleep(ConditionVariable *cv)
*/
void
ConditionVariableSleep(ConditionVariable *cv, uint32 wait_event_info)
+{
+ (void) ConditionVariableTimedSleep(cv, -1 /* no timeout */, wait_event_info);
+}
+
+/*
+ * Wait for the given condition variable to be signaled or till timeout.
+ * This should be called in a predicate loop that tests for a specific exit
+ * condition and otherwise sleeps, like so:
+ *
+ * ConditionVariablePrepareToSleep(cv); // optional
+ * while (condition for which we are waiting is not true)
+ * ConditionVariableSleep(cv, wait_event_info);
+ * ConditionVariableCancelSleep();
+ *
+ * wait_event_info should be a value from one of the WaitEventXXX enums
+ * defined in pgstat.h. This controls the contents of pg_stat_activity's
+ * wait_event_type and wait_event columns while waiting.
+ *
+ * Returns 0 or -1 if timed out.
+ */
+int
+ConditionVariableTimedSleep(ConditionVariable *cv, long timeout,
+ uint32 wait_event_info)
{
WaitEvent event;
bool done = false;
+ int ret;
/*
* If the caller didn't prepare to sleep explicitly, then do so now and
@@ -143,7 +167,7 @@ ConditionVariableSleep(ConditionVariable *cv, uint32 wait_event_info)
if (cv_sleep_target != cv)
{
ConditionVariablePrepareToSleep(cv);
- return;
+ return 0;
}
do
@@ -154,12 +178,23 @@ ConditionVariableSleep(ConditionVariable *cv, uint32 wait_event_info)
* Wait for latch to be set. (If we're awakened for some other
* reason, the code below will cope anyway.)
*/
- (void) WaitEventSetWait(cv_wait_event_set, -1, &event, 1,
+ ret = WaitEventSetWait(cv_wait_event_set, timeout, &event, 1,
wait_event_info);
/* Reset latch before examining the state of the wait list. */
ResetLatch(MyLatch);
+ /* Timeout */
+ if (ret == 0 && timeout >= 0)
+ {
+ /*
+ * In the event of a timeout, we simply return and the caller
+ * calls ConditionVariableCancelSleep to remove themselves from the
+ * wait queue
+ */
+ return -1;
+ }
+
/*
* If this process has been taken out of the wait list, then we know
* that it has been signaled by ConditionVariableSignal (or
@@ -183,6 +218,8 @@ ConditionVariableSleep(ConditionVariable *cv, uint32 wait_event_info)
}
SpinLockRelease(&cv->mutex);
} while (!done);
+
+ return 0;
}
/*
diff --git a/src/include/storage/condition_variable.h b/src/include/storage/condition_variable.h
index 2a0249392c..f62164e483 100644
--- a/src/include/storage/condition_variable.h
+++ b/src/include/storage/condition_variable.h
@@ -43,6 +43,8 @@ extern void ConditionVariableInit(ConditionVariable *cv);
* the condition variable.
*/
extern void ConditionVariableSleep(ConditionVariable *cv, uint32 wait_event_info);
+extern int ConditionVariableTimedSleep(ConditionVariable *cv, long timeout,
+ uint32 wait_event_info);
extern void ConditionVariableCancelSleep(void);
/*
--
2.16.5
--wRRV7LY7NUeQGEoC--
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
@ 2023-02-16 18:03 Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-02-16 18:03 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; David Zhang <[email protected]>; +Cc: [email protected] <[email protected]>
On 16.02.2023 00:37, David G. Johnston wrote:
> I mean, either you accept the change in how this meta-command presents
> its information or you don't.
Yes, that's the main issue of this patch.
On the one hand, it would be nice to see the membership options with the
psql command.
On the other hand, I don't have an exact understanding of how best to do
it. That's why I proposed a variant for discussion. It is quite possible
that if there is no consensus, it would be better to leave it as is, and
get information by queries to the system catalog.
-----
Pavel Luzanov
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-02-17 11:02 ` Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-02-17 11:02 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; David Zhang <[email protected]>; +Cc: [email protected] <[email protected]>
Hello,
> On the one hand, it would be nice to see the membership options with
> the psql command.
After playing with cf5eb37c and e5b8a4c0 I think something must be made
with \du command.
postgres@demo(16.0)=# CREATE ROLE admin LOGIN CREATEROLE;
CREATE ROLE
postgres@demo(16.0)=# \c - admin
You are now connected to database "demo" as user "admin".
admin@demo(16.0)=> SET createrole_self_grant = 'SET, INHERIT';
SET
admin@demo(16.0)=> CREATE ROLE bob LOGIN;
CREATE ROLE
admin@demo(16.0)=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Create role
| {bob,bob}
bob | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
| {}
We see two bob roles in the 'Member of' column.Strange? But this is correct.
admin@demo(16.0)=> select roleid::regrole, member::regrole, * from
pg_auth_members where roleid = 'bob'::regrole;
roleid | member | oid | roleid | member | grantor | admin_option |
inherit_option | set_option
--------+--------+-------+--------+--------+---------+--------------+----------------+------------
bob | admin | 16713 | 16712 | 16711 | 10 | t |
f | f
bob | admin | 16714 | 16712 | 16711 | 16711 | f |
t | t
(2 rows)
First 'grant bob to admin' command issued immediately after creating
role bob by superuser(grantor=10). Second command issues by admin role
and set membership options SET and INHERIT.
If we don't ready to display membership options with \du+ may be at
least we must group records in 'Member of' column for \du command?
-----
Pavel Luzanov
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-02-17 16:53 ` David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: David G. Johnston @ 2023-02-17 16:53 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On Fri, Feb 17, 2023 at 4:02 AM Pavel Luzanov <[email protected]>
wrote:
> List of roles
> Role name | Attributes |
> Member of
>
> -----------+------------------------------------------------------------+-----------
> admin | Create role |
> {bob,bob}
> bob | |
> {}
> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
> {}
>
> First 'grant bob to admin' command issued immediately after creating role
> bob by superuser(grantor=10). Second command issues by admin role and set
> membership options SET and INHERIT.
> If we don't ready to display membership options with \du+ may be at least
> we must group records in 'Member of' column for \du command?
>
I agree that these views should GROUP BY roleid and use bool_or(*_option)
to produce their result. Their purpose is to communicate the current
effective state to the user, not facilitate full inspection of the
configuration, possibly to aid in issuing GRANT and REVOKE commands.
One thing I found, and I plan to bring this up independently once I've
collected my thoughts, is that pg_has_role() uses the terminology "USAGE"
and "MEMBER" for "INHERIT" and "SET" respectively.
It's annoying that "member" has been overloaded here. And the choice of
USAGE just seems arbitrary (though I haven't researched it) given the
related syntax.
https://www.postgresql.org/docs/15/functions-info.html
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
@ 2023-02-21 21:14 ` Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-02-21 21:14 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On 17.02.2023 19:53, David G. Johnston wrote:
> On Fri, Feb 17, 2023 at 4:02 AM Pavel Luzanov
> <[email protected]> wrote:
>
> List of roles
> Role name | Attributes | Member of
> -----------+------------------------------------------------------------+-----------
> admin | Create
> role | {bob,bob}
> bob | | {}
> postgres | Superuser, Create role, Create DB, Replication,
> Bypass RLS | {}
>
> First 'grant bob to admin' command issued immediately after
> creating role bob by superuser(grantor=10). Second command issues
> by admin role and set membership options SET and INHERIT.
>
> If we don't ready to display membership options with \du+ may be
> at least we must group records in 'Member of' column for \du command?
>
>
> I agree that these views should GROUP BY roleid and use
> bool_or(*_option) to produce their result.
Ok, I'll try in the next few days. But what presentation format to use?
1. bob(admin_option=t inherit_option=t set_option=f) -- it seems very long
2. bob(ai) -- short, but will it be clear?
3. something else?
> Their purpose is to communicate the current effective state to the
> user, not facilitate full inspection of the configuration, possibly to
> aid in issuing GRANT and REVOKE commands.
This can help in issuing GRANT command, but not REVOKE. Revoking a
role's membership is now very similar to revoking privileges. Only the
role that granted membership can revoke that membership. So for REVOKE
you need to know who granted membership, but this information will not
be available after grouping.
> One thing I found, and I plan to bring this up independently once I've
> collected my thoughts, is that pg_has_role() uses the terminology
> "USAGE" and "MEMBER" for "INHERIT" and "SET" respectively.
>
> It's annoying that "member" has been overloaded here. And the choice
> of USAGE just seems arbitrary (though I haven't researched it) given
> the related syntax.
>
> https://www.postgresql.org/docs/15/functions-info.html
>
I didn't even know this function existed. But I see that it was changed
in 3d14e171 with updated documentation:
https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS
Maybe that's enough.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-02-21 21:34 ` David G. Johnston <[email protected]>
2023-02-27 20:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
0 siblings, 2 replies; 29+ messages in thread
From: David G. Johnston @ 2023-02-21 21:34 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On Tue, Feb 21, 2023 at 2:14 PM Pavel Luzanov <[email protected]>
wrote:
> On 17.02.2023 19:53, David G. Johnston wrote:
>
> On Fri, Feb 17, 2023 at 4:02 AM Pavel Luzanov <[email protected]>
> wrote:
>
>> List of roles
>> Role name | Attributes |
>> Member of
>>
>> -----------+------------------------------------------------------------+-----------
>> admin | Create role |
>> {bob,bob}
>> bob | |
>> {}
>> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
>> {}
>>
>> First 'grant bob to admin' command issued immediately after creating role
>> bob by superuser(grantor=10). Second command issues by admin role and set
>> membership options SET and INHERIT.
>> If we don't ready to display membership options with \du+ may be at least
>> we must group records in 'Member of' column for \du command?
>>
>
> I agree that these views should GROUP BY roleid and use bool_or(*_option)
> to produce their result.
>
>
> Ok, I'll try in the next few days. But what presentation format to use?
>
>
This is the format I've gone for (more-or-less) in my RoleGraph view (I'll
be sharing it publicly in the near future).
bob from grantor (a, s, i) \n
adam from postgres (a, s, i) \n
emily from postgres (empty)
I don't think first-letter mnemonics will be an issue - you need to learn
the syntax anyway. And it is already what we do for object grants besides.
Based upon prior comments going for something like the following is
undesirable: bob=asi/grantor
So I converted the "/" into "from" and stuck the permissions on the end
instead of in the middle (makes reading the "from" fragment cleaner).
To be clear, this is going away from grouping but trades verbosity and
deviation from what is done today for better information. If we are going
to break this I suppose we might as well break it thoroughly.
>
> I didn't even know this function existed. But I see that it was changed in
> 3d14e171 with updated documentation:
>
> https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS
> Maybe that's enough.
>
>
I think that should probably have ADMIN as one of the options as well.
Also curious what it reports for an empty membership.
David J.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
@ 2023-02-27 20:14 ` Pavel Luzanov <[email protected]>
2023-03-01 10:55 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
1 sibling, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-02-27 20:14 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On 22.02.2023 00:34, David G. Johnston wrote:
> This is the format I've gone for (more-or-less) in my RoleGraph view
> (I'll be sharing it publicly in the near future).
>
> bob from grantor (a, s, i) \n
> adam from postgres (a, s, i) \n
> emily from postgres (empty)
I think this is a good compromise.
> Based upon prior comments going for something like the following is
> undesirable: bob=asi/grantor
Agree. Membership options are not the ACL (although they have
similarities). Therefore, showing them as a ACL-like column will be
confusing.
So, please find attached the second version of the patch. It implements
suggested display format and small refactoring of existing code for \du
command.
As a non-native writer, I have doubts about the documentation part.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
Attachments:
[text/x-patch] v2-0001-psql-du-shows-membership-options.patch (10.5K, ../../[email protected]/3-v2-0001-psql-du-shows-membership-options.patch)
download | inline diff:
From 70489a605687a325287bad109e2741dd7d08cea3 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <[email protected]>
Date: Mon, 27 Feb 2023 22:35:29 +0300
Subject: [PATCH v2] psql: \du shows membership options
---
doc/src/sgml/ref/psql-ref.sgml | 12 ++++++++
src/bin/psql/describe.c | 45 +++++++++++++++++++-----------
src/test/regress/expected/psql.out | 45 ++++++++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 25 +++++++++++++++++
4 files changed, 111 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index dc6528dc11..c94a2287f0 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1724,6 +1724,12 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
+ For each membership in the role, the membership options and
+ the role that granted the membership are displayed.
+ Оne-letter abbreviations are used for membership options:
+ <literal>a</literal> — admin option, <literal>i</literal> — inherit option,
+ <literal>s</literal> — set option and <literal>empty</literal> if no one is set.
+ See <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
If the form <literal>\dg+</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
@@ -1966,6 +1972,12 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
+ For each membership in the role, the membership options and
+ the role that granted the membership are displayed.
+ Оne-letter abbreviations are used for membership options:
+ <literal>a</literal> — admin option, <literal>i</literal> — inherit option,
+ <literal>s</literal> — set option and <literal>empty</literal> if no one is set.
+ See <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
If the form <literal>\du+</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c8a0bb7b3a..27a8680ddf 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3623,22 +3623,36 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
"SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
" r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
" r.rolconnlimit, r.rolvaliduntil,\n"
- " ARRAY(SELECT b.rolname\n"
- " FROM pg_catalog.pg_auth_members m\n"
- " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
- " WHERE m.member = r.oid) as memberof");
+ " r.rolreplication, r.rolbypassrls,\n");
+
+ if (pset.sversion >= 160000)
+ appendPQExpBufferStr(&buf,
+ " (SELECT pg_catalog.string_agg(\n"
+ " pg_catalog.format('%I from %I (%s)',\n"
+ " b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text,\n"
+ " pg_catalog.regexp_replace(\n"
+ " pg_catalog.concat_ws(', ',\n"
+ " CASE WHEN m.admin_option THEN 'a' END,\n"
+ " CASE WHEN m.inherit_option THEN 'i' END,\n"
+ " CASE WHEN m.set_option THEN 's' END),\n"
+ " '^$', 'empty')\n"
+ " ), E'\\n'\n"
+ " ORDER BY b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text)\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
+ else
+ appendPQExpBufferStr(&buf,
+ " ARRAY(SELECT b.rolname\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
if (verbose)
{
appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
ncols++;
}
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
-
- if (pset.sversion >= 90500)
- {
- appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
- }
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
@@ -3692,12 +3706,11 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
add_role_attribute(&buf, _("Replication"));
- if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
- add_role_attribute(&buf, _("Bypass RLS"));
+ if (strcmp(PQgetvalue(res, i, 9), "t") == 0)
+ add_role_attribute(&buf, _("Bypass RLS"));
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
@@ -3726,10 +3739,10 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddCell(&cont, attr[i], false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
}
termPQExpBuffer(&buf);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 8fc62cebd2..a9e47f1ebf 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6578,3 +6578,48 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.extended.statistics"
+-- check \du
+CREATE ROLE regress_du0;
+CREATE ROLE regress_du1 LOGIN;
+CREATE ROLE regress_du2 LOGIN;
+COMMENT ON ROLE regress_du0 IS 'Description for regress_du0';
+COMMENT ON ROLE regress_du1 IS 'Description for regress_du1';
+COMMENT ON ROLE regress_du2 IS 'Description for regress_du2';
+GRANT regress_du0 TO regress_du1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
+GRANT regress_du0 TO regress_du2 WITH ADMIN TRUE, INHERIT FALSE, SET TRUE;
+GRANT pg_monitor TO regress_du1;
+GRANT regress_du0 TO regress_du1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du1;
+GRANT regress_du0 TO regress_du2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du1;
+GRANT regress_du0 TO regress_du2 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du2;
+GRANT regress_du0 TO regress_du1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du2;
+\du regress*
+ List of roles
+ Role name | Attributes | Member of
+-------------+--------------+----------------------------------------
+ regress_du0 | Cannot login |
+ regress_du1 | | pg_monitor from pal (i, s) +
+ | | regress_du0 from pal (a) +
+ | | regress_du0 from regress_du1 (i) +
+ | | regress_du0 from regress_du2 (a, i, s)
+ regress_du2 | | regress_du0 from pal (a, s) +
+ | | regress_du0 from regress_du1 (empty) +
+ | | regress_du0 from regress_du2 (s)
+
+\du+ regress*
+ List of roles
+ Role name | Attributes | Member of | Description
+-------------+--------------+----------------------------------------+-----------------------------
+ regress_du0 | Cannot login | | Description for regress_du0
+ regress_du1 | | pg_monitor from pal (i, s) +| Description for regress_du1
+ | | regress_du0 from pal (a) +|
+ | | regress_du0 from regress_du1 (i) +|
+ | | regress_du0 from regress_du2 (a, i, s) |
+ regress_du2 | | regress_du0 from pal (a, s) +| Description for regress_du2
+ | | regress_du0 from regress_du1 (empty) +|
+ | | regress_du0 from regress_du2 (s) |
+
+REVOKE regress_du0 FROM regress_du1 CASCADE;
+REVOKE regress_du0 FROM regress_du2 CASCADE;
+DROP ROLE regress_du2;
+DROP ROLE regress_du1;
+DROP ROLE regress_du0;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 2da9665a19..dca1ede0ab 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1791,3 +1791,28 @@ DROP FUNCTION psql_error;
\dP "no.such.database"."no.such.schema"."no.such.partitioned.relation"
\dT "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
+
+-- check \du
+CREATE ROLE regress_du0;
+CREATE ROLE regress_du1 LOGIN;
+CREATE ROLE regress_du2 LOGIN;
+COMMENT ON ROLE regress_du0 IS 'Description for regress_du0';
+COMMENT ON ROLE regress_du1 IS 'Description for regress_du1';
+COMMENT ON ROLE regress_du2 IS 'Description for regress_du2';
+
+GRANT regress_du0 TO regress_du1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
+GRANT regress_du0 TO regress_du2 WITH ADMIN TRUE, INHERIT FALSE, SET TRUE;
+GRANT pg_monitor TO regress_du1;
+GRANT regress_du0 TO regress_du1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du1;
+GRANT regress_du0 TO regress_du2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du1;
+GRANT regress_du0 TO regress_du2 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du2;
+GRANT regress_du0 TO regress_du1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du2;
+
+\du regress*
+\du+ regress*
+
+REVOKE regress_du0 FROM regress_du1 CASCADE;
+REVOKE regress_du0 FROM regress_du2 CASCADE;
+DROP ROLE regress_du2;
+DROP ROLE regress_du1;
+DROP ROLE regress_du0;
--
2.34.1
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-27 20:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-03-01 10:55 ` Pavel Luzanov <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Pavel Luzanov @ 2023-03-01 10:55 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
Next version (v3) addresses complains from cfbot. Changed only tests.
--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
Attachments:
[text/x-patch] v3-0001-psql-du-shows-memberships-options.patch (11.6K, ../../[email protected]/2-v3-0001-psql-du-shows-memberships-options.patch)
download | inline diff:
From 6db62993d4b7afbcbce3e63ce3fbe3946ec50cff Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <[email protected]>
Date: Wed, 1 Mar 2023 13:29:10 +0300
Subject: [PATCH v3] psql: \du shows memberships options
---
doc/src/sgml/ref/psql-ref.sgml | 12 ++++++++
src/bin/psql/describe.c | 45 ++++++++++++++++++----------
src/test/regress/expected/psql.out | 48 ++++++++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 29 ++++++++++++++++++
4 files changed, 118 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index dc6528dc11..c94a2287f0 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1724,6 +1724,12 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
+ For each membership in the role, the membership options and
+ the role that granted the membership are displayed.
+ Оne-letter abbreviations are used for membership options:
+ <literal>a</literal> — admin option, <literal>i</literal> — inherit option,
+ <literal>s</literal> — set option and <literal>empty</literal> if no one is set.
+ See <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
If the form <literal>\dg+</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
@@ -1966,6 +1972,12 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
+ For each membership in the role, the membership options and
+ the role that granted the membership are displayed.
+ Оne-letter abbreviations are used for membership options:
+ <literal>a</literal> — admin option, <literal>i</literal> — inherit option,
+ <literal>s</literal> — set option and <literal>empty</literal> if no one is set.
+ See <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
If the form <literal>\du+</literal> is used, additional information
is shown about each role; currently this adds the comment for each
role.
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c8a0bb7b3a..27a8680ddf 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3623,22 +3623,36 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
"SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
" r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
" r.rolconnlimit, r.rolvaliduntil,\n"
- " ARRAY(SELECT b.rolname\n"
- " FROM pg_catalog.pg_auth_members m\n"
- " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
- " WHERE m.member = r.oid) as memberof");
+ " r.rolreplication, r.rolbypassrls,\n");
+
+ if (pset.sversion >= 160000)
+ appendPQExpBufferStr(&buf,
+ " (SELECT pg_catalog.string_agg(\n"
+ " pg_catalog.format('%I from %I (%s)',\n"
+ " b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text,\n"
+ " pg_catalog.regexp_replace(\n"
+ " pg_catalog.concat_ws(', ',\n"
+ " CASE WHEN m.admin_option THEN 'a' END,\n"
+ " CASE WHEN m.inherit_option THEN 'i' END,\n"
+ " CASE WHEN m.set_option THEN 's' END),\n"
+ " '^$', 'empty')\n"
+ " ), E'\\n'\n"
+ " ORDER BY b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text)\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
+ else
+ appendPQExpBufferStr(&buf,
+ " ARRAY(SELECT b.rolname\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
if (verbose)
{
appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
ncols++;
}
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
-
- if (pset.sversion >= 90500)
- {
- appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
- }
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
@@ -3692,12 +3706,11 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
add_role_attribute(&buf, _("Replication"));
- if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
- add_role_attribute(&buf, _("Bypass RLS"));
+ if (strcmp(PQgetvalue(res, i, 9), "t") == 0)
+ add_role_attribute(&buf, _("Bypass RLS"));
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
@@ -3726,10 +3739,10 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddCell(&cont, attr[i], false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
}
termPQExpBuffer(&buf);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 8fc62cebd2..584d644261 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6578,3 +6578,51 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.extended.statistics"
+-- check \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+\du regress_du_role*
+ List of roles
+ Role name | Attributes | Member of
+------------------+--------------+--------------------------------------------------
+ regress_du_role0 | Cannot login |
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+
+ | | regress_du_role0 from regress_du_role1 (i) +
+ | | regress_du_role0 from regress_du_role2 (s)
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +
+ | | regress_du_role0 from regress_du_role1 (i, s) +
+ | | regress_du_role0 from regress_du_role2 (empty) +
+ | | regress_du_role1 from regress_du_admin (a, s)
+
+\du+ regress_du_role*
+ List of roles
+ Role name | Attributes | Member of | Description
+------------------+--------------+--------------------------------------------------+----------------------------------
+ regress_du_role0 | Cannot login | | Description for regress_du_role0
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+| Description for regress_du_role1
+ | | regress_du_role0 from regress_du_role1 (i) +|
+ | | regress_du_role0 from regress_du_role2 (s) |
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +| Description for regress_du_role2
+ | | regress_du_role0 from regress_du_role1 (i, s) +|
+ | | regress_du_role0 from regress_du_role2 (empty) +|
+ | | regress_du_role1 from regress_du_admin (a, s) |
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 2da9665a19..e54a94c96a 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1791,3 +1791,32 @@ DROP FUNCTION psql_error;
\dP "no.such.database"."no.such.schema"."no.such.partitioned.relation"
\dT "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
+
+-- check \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+
+\du regress_du_role*
+\du+ regress_du_role*
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
--
2.34.1
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
@ 2023-03-03 11:01 ` Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
1 sibling, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-03-03 11:01 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
Hello,
On 22.02.2023 00:34, David G. Johnston wrote:
> I didn't even know this function existed. But I see that it was
> changed in 3d14e171 with updated documentation:
> https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS
> Maybe that's enough.
>
>
> I think that should probably have ADMIN as one of the options as well.
> Also curious what it reports for an empty membership.
I've been experimenting for a few days and I want to admit that this is
a very difficult and not obvious topic.
I'll try to summarize what I think.
1.
About ADMIN value for pg_has_role.
Implementation of ADMIN value will be different from USAGE and SET.
To be True, USAGE value requires the full chain of memberships to have
INHERIT option.
Similar with SET: the full chain of memberships must have SET option.
But for ADMIN, only last member in the chain must have ADMIN option and
all previous members
must have INHERIT (to administer directly) or SET option (to switch to
role, last in the chain).
Therefore, it is not obvious to me that the function needs the ADMIN value.
2.
pg_has_role function description starts with: Does user have privilege
for role?
- This is not exact: function works not only with users, but with
NOLOGIN roles too.
- Term "privilege": this term used for ACL columns, such usage may
be confusing,
especially after adding INHERIT and SET in addition to ADMIN option.
3.
It is possible to grant membership with all three options turned off:
grant a to b with admin false, inherit false, set false;
But such membership is completely useless (if i didn't miss something).
May be such grants must be prohibited. At least this may be documented
in the GRANT command.
4.
Since v16 it is possible to grant membership from one role to another
several times with different grantors.
And only grantor can revoke membership.
- This is not documented anywhere.
- Current behavior of \du command with duplicated roles in "Member
of" column strongly confusing.
This is one of the goals of the discussion patch.
I think to write about this to pgsql-docs additionally to this topic.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-03-03 16:21 ` David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: David G. Johnston @ 2023-03-03 16:21 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On Fri, Mar 3, 2023 at 4:01 AM Pavel Luzanov <[email protected]>
wrote:
> Hello,
>
> On 22.02.2023 00:34, David G. Johnston wrote:
>
> I didn't even know this function existed. But I see that it was changed in
> 3d14e171 with updated documentation:
>
> https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS
> Maybe that's enough.
>
>
> I think that should probably have ADMIN as one of the options as well.
> Also curious what it reports for an empty membership.
>
>
> I've been experimenting for a few days and I want to admit that this is a
> very difficult and not obvious topic.
> I'll try to summarize what I think.
>
> 1.
> About ADMIN value for pg_has_role.
> Implementation of ADMIN value will be different from USAGE and SET.
> To be True, USAGE value requires the full chain of memberships to have
> INHERIT option.
> Similar with SET: the full chain of memberships must have SET option.
> But for ADMIN, only last member in the chain must have ADMIN option and
> all previous members
> must have INHERIT (to administer directly) or SET option (to switch to
> role, last in the chain).
> Therefore, it is not obvious to me that the function needs the ADMIN value.
>
Or you can SET to some role that then has an unbroken INHERIT chain to the
administered role.
ADMIN basically implies SET/USAGE but it doesn't work the other way around.
I'd be fine with "pg_can_admin_role" being a newly created function that
provides this true/false answer but it seems indisputable that today there
is no core-provided means to answer the question "can one role get ADMIN
rights on another role". Modifying \du to show this seems out-of-scope but
the pg_has_role function already provides that question for INHERIT and SET
so it is at least plausible to extend it to include ADMIN, even if the
phrase "has role" seems a bit of a misnomer. I do cover this aspect with
the Role Graph pseudo-extension but given the presence and ease-of-use of a
boolean-returning function this seems like a natural addition. We've also
survived quite long without it - this isn't a new concept in v16, just a
bit refined.
>
> 2.
> pg_has_role function description starts with: Does user have privilege for
> role?
> - This is not exact: function works not only with users, but with
> NOLOGIN roles too.
> - Term "privilege": this term used for ACL columns, such usage may be
> confusing,
> especially after adding INHERIT and SET in addition to ADMIN option.
>
Yes, it missed the whole "there are only roles now" memo. I don't have an
issue with using privilege here though - you have to use the GRANT command
which "defines access privileges". Otherwise "membership option" or maybe
just "option" would need to be explored.
>
> 3.
> It is possible to grant membership with all three options turned off:
> grant a to b with admin false, inherit false, set false;
> But such membership is completely useless (if i didn't miss something).
> May be such grants must be prohibited. At least this may be documented in
> the GRANT command.
>
I have no issue with prohibiting the "empty membership" if someone wants to
code that up.
> 4.
> Since v16 it is possible to grant membership from one role to another
> several times with different grantors.
> And only grantor can revoke membership.
> - This is not documented anywhere.
>
Yeah, a pass over the GRANTED BY actual operation versus documentation
seems warranted.
> - Current behavior of \du command with duplicated roles in "Member of"
> column strongly confusing.
> This is one of the goals of the discussion patch.
>
This indeed needs to be fixed, one way (include grantor) or the other
(du-duplicate), with the current proposal of including grantor getting my
vote.
>
> I think to write about this to pgsql-docs additionally to this topic.
>
I wouldn't bother starting yet another thread in this area right now, this
one can absorb some related changes as well as the subject line item.
David J.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
@ 2023-03-06 07:43 ` Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-03-06 07:43 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On 03.03.2023 19:21, David G. Johnston wrote:
> I'd be fine with "pg_can_admin_role" being a newly created function
> that provides this true/false answer but it seems indisputable that
> today there is no core-provided means to answer the question "can one
> role get ADMIN rights on another role". Modifying \du to show this
> seems out-of-scope but the pg_has_role function already provides that
> question for INHERIT and SET so it is at least plausible to extend it
> to include ADMIN, even if the phrase "has role" seems a bit of a
> misnomer. I do cover this aspect with the Role Graph pseudo-extension
> but given the presence and ease-of-use of a boolean-returning function
> this seems like a natural addition. We've also survived quite long
> without it - this isn't a new concept in v16, just a bit refined.
I must admit that I am slowly coming to the same conclusions that you
have already outlined in previous messages.
Indeed, adding ADMIN to pg_has_role looks logical. The function will
show whether one role can manage another directly or indirectly (via SET
ROLE).
Adding ADMIN will lead to the question of naming other values. It is
more reasonable to have INHERIT instead of USAGE.
And it is not very clear whether (except for backward compatibility) a
separate MEMBER value is needed at all.
> I wouldn't bother starting yet another thread in this area right now,
> this one can absorb some related changes as well as the subject line item.
I agree.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-03-07 21:02 ` David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:18 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
0 siblings, 2 replies; 29+ messages in thread
From: David G. Johnston @ 2023-03-07 21:02 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On Mon, Mar 6, 2023 at 12:43 AM Pavel Luzanov <[email protected]>
wrote:
> Indeed, adding ADMIN to pg_has_role looks logical. The function will show
> whether one role can manage another directly or indirectly (via SET ROLE).
>
FWIW I've finally gotten to publishing my beta version of the Role Graph
for PostgreSQL pseudo-extension I'd been talking about:
https://github.com/polobo/RoleGraphForPostgreSQL
The administration column basically determines all this via a recursive
CTE. I'm pondering how to incorporate some of this core material into it
now for both cross-validation purposes and ease-of-use.
I handle EMPTY explicitly in the Role Graph but as I noted somewhere in my
comments, it really shouldn't be possible to leave the database in that
state. Do we need to bug Robert on this directly or do you plan to have a
go at it?
Adding ADMIN will lead to the question of naming other values. It is more
> reasonable to have INHERIT instead of USAGE.
>
And it is not very clear whether (except for backward compatibility) a
> separate MEMBER value is needed at all.
>
There is an appeal to breaking things thoroughly here and removing both
MEMBER and USAGE terms while adding ADMIN, SET, INHERIT.
However, I am against that. Most everyday usage of this would only likely
care about SET and INHERIT even going forward - administration of roles is
distinct from how those roles generally behave at runtime. Breaking the
later because we improved upon the former doesn't seem defensible. Thus,
while adding ADMIN makes sense, keeping MEMBER (a.k.a., SET) and USAGE
(a.k.a., INHERIT) is my suggested way forward.
I'll be looking over your v3 patch sometime this week, if not today.
David J.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
@ 2023-03-08 02:31 ` David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
1 sibling, 1 reply; 29+ messages in thread
From: David G. Johnston @ 2023-03-08 02:31 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On Tue, Mar 7, 2023 at 2:02 PM David G. Johnston <[email protected]>
wrote:
>
> I'll be looking over your v3 patch sometime this week, if not today.
>
>
Moving the goal posts for this meta-command to >= 9.5 seems like it should
be done as a separate patch and thread. The documentation presently states
we are targeting 9.2 and newer.
My suggestion for the docs is below. I find saying "additional
information is shown...currently this adds the comment". Repeating that
"+" means (show more) everywhere seems excessive, just state what those
"more" things are. I consider \dFp and \dl to be good examples in this
regard.
I also think that "Wall of text" doesn't serve us well. See \dP for
permission to use paragraphs.
I didn't modify \du to match; keeping those in sync (as opposed to having
\du just say "see \dg") seems acceptable.
You had the direction of membership wrong in your copy: "For each
membership in the role" describes the reverse of "Member of" which is what
the column is. The actual format template is constructed properly.
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1727,15 +1727,18 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first
value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is
specified,
only those roles whose names match the pattern are listed.
- For each membership in the role, the membership options and
- the role that granted the membership are displayed.
- Оne-letter abbreviations are used for membership options:
- <literal>a</literal> — admin option, <literal>i</literal>
— inherit option,
- <literal>s</literal> — set option and
<literal>empty</literal> if no one is set.
- See <link linkend="sql-grant"><command>GRANT</command></link>
command for their meaning.
- If the form <literal>\dg+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ </para>
+ <para>
+ Shown within each row, in newline-separated format, are the
memberships granted to
+ the role. The presentation includes both the name of the grantor
+ as well as the membership permissions (in an abbreviated format:
+ <literal>a</literal> for admin option, <literal>i</literal> for
inherit option,
+ <literal>s</literal> for set option.) The word
<literal>empty</literal> is printed in
+ the case that none of those permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link>
command for their meaning.
+ </para>
+ <para>
+ If the form <literal>\dg+</literal> is used the comment attached
to the role is shown.
</para>
</listitem>
</varlistentry>
I would suggest tweaking the test output to include regress_du_admin and
also to make regress_du_admin a CREATEROLE role with LOGIN.
I'll need to update the Role Graph View to add the spaces and swap the
order of the "s" and "i" symbols.
David J.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
@ 2023-03-10 12:06 ` Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-03-10 12:06 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On 08.03.2023 05:31, David G. Johnston wrote:
> Moving the goal posts for this meta-command to >= 9.5 seems like it
> should be done as a separate patch and thread. The documentation
> presently states we are targeting 9.2 and newer.
I missed the comment at the beginning of the file about version 9.2. I
will return the version check for rolbypassrls.
> My suggestion for the docs is below.
> + <para>
> + Shown within each row, in newline-separated format, are the
> memberships granted to
> + the role. The presentation includes both the name of the grantor
> + as well as the membership permissions (in an abbreviated format:
> + <literal>a</literal> for admin option, <literal>i</literal>
> for inherit option,
> + <literal>s</literal> for set option.) The word
> <literal>empty</literal> is printed in
> + the case that none of those permissions are granted.
> + See the <link
> linkend="sql-grant"><command>GRANT</command></link> command for their
> meaning.
> + </para>
> + <para>
> + If the form <literal>\dg+</literal> is used the comment
> attached to the role is shown.
> </para>
Thanks. I will replace the description with this one.
> I would suggest tweaking the test output to include regress_du_admin
> and also to make regress_du_admin a CREATEROLE role with LOGIN.
Ok.
Thank you for review. I will definitely work on the new version, but
unfortunately and with a high probability it will happen after March 20.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-03-20 08:49 ` Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-03-20 08:49 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On 10.03.2023 15:06, Pavel Luzanov wrote:
> I missed the comment at the beginning of the file about version 9.2. I
> will return the version check for rolbypassrls.
>> + <para>
>> + Shown within each row, in newline-separated format, are the
>> memberships granted to
>> + the role. The presentation includes both the name of the
>> grantor
>> + as well as the membership permissions (in an abbreviated format:
>> + <literal>a</literal> for admin option, <literal>i</literal>
>> for inherit option,
>> + <literal>s</literal> for set option.) The word
>> <literal>empty</literal> is printed in
>> + the case that none of those permissions are granted.
>> + See the <link
>> linkend="sql-grant"><command>GRANT</command></link> command for their
>> meaning.
>> + </para>
>> + <para>
>> + If the form <literal>\dg+</literal> is used the comment
>> attached to the role is shown.
>> </para>
>
> Thanks. I will replace the description with this one.
>
>> I would suggest tweaking the test output to include regress_du_admin
>> and also to make regress_du_admin a CREATEROLE role with LOGIN.
>
> Ok.
Please review the attached version 4 with the changes discussed.
-----
Pavel Luzanov
Attachments:
[text/x-patch] v4-0001-psql-show-membership-options-in-the-du-command.patch (13.5K, ../../[email protected]/3-v4-0001-psql-show-membership-options-in-the-du-command.patch)
download | inline diff:
From b67cdf2299b6053ed247254c8be4edde472efdae Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <[email protected]>
Date: Mon, 20 Mar 2023 11:28:31 +0300
Subject: [PATCH v4] psql: show membership options in the \du command
---
doc/src/sgml/ref/psql-ref.sgml | 30 ++++++++++++---
src/bin/psql/describe.c | 61 ++++++++++++++++++++----------
src/test/regress/expected/psql.out | 55 +++++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 30 +++++++++++++++
4 files changed, 150 insertions(+), 26 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7b8ae9fac3..03e7da93de 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1727,9 +1727,18 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\dg+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ </para>
+ <para>
+ Shown within each row, in newline-separated format, are the memberships granted to
+ the role. The presentation includes both the name of the grantor
+ as well as the membership permissions (in an abbreviated format:
+ <literal>a</literal> for admin option, <literal>i</literal> for inherit option,
+ <literal>s</literal> for set option.) The word <literal>empty</literal> is printed in
+ the case that none of those permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ If the form <literal>\dg+</literal> is used the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
@@ -1969,9 +1978,18 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\du+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ </para>
+ <para>
+ Shown within each row, in newline-separated format, are the memberships granted to
+ the role. The presentation includes both the name of the grantor
+ as well as the membership permissions (in an abbreviated format:
+ <literal>a</literal> for admin option, <literal>i</literal> for inherit option,
+ <literal>s</literal> for set option.) The word <literal>empty</literal> is printed in
+ the case that none of those permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ If the form <literal>\du+</literal> is used the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 99e28f607e..9f7b7326e9 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3631,24 +3631,42 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printfPQExpBuffer(&buf,
"SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
" r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
- " r.rolconnlimit, r.rolvaliduntil,\n"
- " ARRAY(SELECT b.rolname\n"
- " FROM pg_catalog.pg_auth_members m\n"
- " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
- " WHERE m.member = r.oid) as memberof");
+ " r.rolconnlimit, r.rolvaliduntil, r.rolreplication,\n");
- if (verbose)
- {
- appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
- ncols++;
- }
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
+ if (pset.sversion >= 160000)
+ appendPQExpBufferStr(&buf,
+ " (SELECT pg_catalog.string_agg(\n"
+ " pg_catalog.format('%I from %I (%s)',\n"
+ " b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text,\n"
+ " pg_catalog.regexp_replace(\n"
+ " pg_catalog.concat_ws(', ',\n"
+ " CASE WHEN m.admin_option THEN 'a' END,\n"
+ " CASE WHEN m.inherit_option THEN 'i' END,\n"
+ " CASE WHEN m.set_option THEN 's' END),\n"
+ " '^$', 'empty')\n"
+ " ), E'\\n'\n"
+ " ORDER BY b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text)\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
+ else
+ appendPQExpBufferStr(&buf,
+ " ARRAY(SELECT b.rolname\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
if (pset.sversion >= 90500)
{
appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
}
+ if (verbose)
+ {
+ appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
+ ncols++;
+ }
+
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
if (!showSystem && !pattern)
@@ -3701,13 +3719,6 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
- add_role_attribute(&buf, _("Replication"));
-
- if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
- add_role_attribute(&buf, _("Bypass RLS"));
-
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
{
@@ -3735,10 +3746,20 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddCell(&cont, attr[i], false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
+ add_role_attribute(&buf, _("Replication"));
+
+ printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+
+ if (pset.sversion >= 90500)
+ if (strcmp(PQgetvalue(res, i, 10), "t") == 0)
+ add_role_attribute(&buf, _("Bypass RLS"));
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ if (pset.sversion >= 90500)
+ printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
+ else
+ printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
}
termPQExpBuffer(&buf);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index c00e28361c..b041922410 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6607,3 +6607,58 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.extended.statistics"
+-- check \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for regress_du_admin';
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+\du regress_du_admin|regress_du_role*
+ List of roles
+ Role name | Attributes | Member of
+------------------+--------------+--------------------------------------------------
+ regress_du_admin | Create role | regress_du_role0 from pal (a, i, s) +
+ | | regress_du_role1 from pal (a, i, s) +
+ | | regress_du_role2 from pal (a, i, s)
+ regress_du_role0 | Cannot login |
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+
+ | | regress_du_role0 from regress_du_role1 (i) +
+ | | regress_du_role0 from regress_du_role2 (s)
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +
+ | | regress_du_role0 from regress_du_role1 (i, s) +
+ | | regress_du_role0 from regress_du_role2 (empty) +
+ | | regress_du_role1 from regress_du_admin (a, s)
+
+\du+ regress_du_admin|regress_du_role*
+ List of roles
+ Role name | Attributes | Member of | Description
+------------------+--------------+--------------------------------------------------+----------------------------------
+ regress_du_admin | Create role | regress_du_role0 from pal (a, i, s) +| Description for regress_du_admin
+ | | regress_du_role1 from pal (a, i, s) +|
+ | | regress_du_role2 from pal (a, i, s) |
+ regress_du_role0 | Cannot login | | Description for regress_du_role0
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+| Description for regress_du_role1
+ | | regress_du_role0 from regress_du_role1 (i) +|
+ | | regress_du_role0 from regress_du_role2 (s) |
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +| Description for regress_du_role2
+ | | regress_du_role0 from regress_du_role1 (i, s) +|
+ | | regress_du_role0 from regress_du_role2 (empty) +|
+ | | regress_du_role1 from regress_du_admin (a, s) |
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 961783d6ea..9dc1da432f 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1815,3 +1815,33 @@ DROP FUNCTION psql_error;
\dP "no.such.database"."no.such.schema"."no.such.partitioned.relation"
\dT "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
+
+-- check \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for regress_du_admin';
+
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+
+\du regress_du_admin|regress_du_role*
+\du+ regress_du_admin|regress_du_role*
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
--
2.34.1
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-03-21 03:37 ` Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-03-21 03:37 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
>>> I would suggest tweaking the test output to include regress_du_admin ...
I found (with a help of cfbot) difficulty with this. The problem is the bootstrap superuser name (oid=10).
This name depends on the OS username. In my case it's pal, but in most cases it's postgres or something else.
And the output of \du regress_du_admin can't be predicted:
\du regress_du_admin
List of roles
Role name | Attributes | Member of
------------------+-------------+-------------------------------------
regress_du_admin | Create role | regress_du_role0 from pal (a, i, s)+
| | regress_du_role1 from pal (a, i, s)+
| | regress_du_role2 from pal (a, i, s)
So, I decided not to include regress_du_admin in the test output.
Please, see version 5 attached. Only tests changed.
-----
Pavel Luzanov
Attachments:
[text/x-patch] v5-0001-psql-show-membership-options-in-the-du-command.patch (13.3K, ../../[email protected]/3-v5-0001-psql-show-membership-options-in-the-du-command.patch)
download | inline diff:
From b8f35733126a843edd47a1f89da0d9f8babeec93 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <[email protected]>
Date: Tue, 21 Mar 2023 05:58:31 +0300
Subject: [PATCH v5] psql: show membership options in the \du command
Format for the "Member of" column completely redesigned.
Shown within each row, in newline-separated format, are the memberships granted to
the role. The presentation includes both the name of the grantor
as well as the membership permissions (in an abbreviated format:
a for admin option, i for inherit option,
s for set option.) The word 'empty' is printed in
the case that none of those permissions are granted.
---
doc/src/sgml/ref/psql-ref.sgml | 30 ++++++++++++---
src/bin/psql/describe.c | 61 ++++++++++++++++++++----------
src/test/regress/expected/psql.out | 49 ++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 30 +++++++++++++++
4 files changed, 144 insertions(+), 26 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7b8ae9fac3..03e7da93de 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1727,9 +1727,18 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\dg+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ </para>
+ <para>
+ Shown within each row, in newline-separated format, are the memberships granted to
+ the role. The presentation includes both the name of the grantor
+ as well as the membership permissions (in an abbreviated format:
+ <literal>a</literal> for admin option, <literal>i</literal> for inherit option,
+ <literal>s</literal> for set option.) The word <literal>empty</literal> is printed in
+ the case that none of those permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ If the form <literal>\dg+</literal> is used the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
@@ -1969,9 +1978,18 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\du+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ </para>
+ <para>
+ Shown within each row, in newline-separated format, are the memberships granted to
+ the role. The presentation includes both the name of the grantor
+ as well as the membership permissions (in an abbreviated format:
+ <literal>a</literal> for admin option, <literal>i</literal> for inherit option,
+ <literal>s</literal> for set option.) The word <literal>empty</literal> is printed in
+ the case that none of those permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ If the form <literal>\du+</literal> is used the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 99e28f607e..9f7b7326e9 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3631,24 +3631,42 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printfPQExpBuffer(&buf,
"SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
" r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
- " r.rolconnlimit, r.rolvaliduntil,\n"
- " ARRAY(SELECT b.rolname\n"
- " FROM pg_catalog.pg_auth_members m\n"
- " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
- " WHERE m.member = r.oid) as memberof");
+ " r.rolconnlimit, r.rolvaliduntil, r.rolreplication,\n");
- if (verbose)
- {
- appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
- ncols++;
- }
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
+ if (pset.sversion >= 160000)
+ appendPQExpBufferStr(&buf,
+ " (SELECT pg_catalog.string_agg(\n"
+ " pg_catalog.format('%I from %I (%s)',\n"
+ " b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text,\n"
+ " pg_catalog.regexp_replace(\n"
+ " pg_catalog.concat_ws(', ',\n"
+ " CASE WHEN m.admin_option THEN 'a' END,\n"
+ " CASE WHEN m.inherit_option THEN 'i' END,\n"
+ " CASE WHEN m.set_option THEN 's' END),\n"
+ " '^$', 'empty')\n"
+ " ), E'\\n'\n"
+ " ORDER BY b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text)\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
+ else
+ appendPQExpBufferStr(&buf,
+ " ARRAY(SELECT b.rolname\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
if (pset.sversion >= 90500)
{
appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
}
+ if (verbose)
+ {
+ appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
+ ncols++;
+ }
+
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
if (!showSystem && !pattern)
@@ -3701,13 +3719,6 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
- add_role_attribute(&buf, _("Replication"));
-
- if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
- add_role_attribute(&buf, _("Bypass RLS"));
-
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
{
@@ -3735,10 +3746,20 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddCell(&cont, attr[i], false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
+ add_role_attribute(&buf, _("Replication"));
+
+ printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+
+ if (pset.sversion >= 90500)
+ if (strcmp(PQgetvalue(res, i, 10), "t") == 0)
+ add_role_attribute(&buf, _("Bypass RLS"));
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ if (pset.sversion >= 90500)
+ printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
+ else
+ printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
}
termPQExpBuffer(&buf);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index c00e28361c..d5f31b2082 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6607,3 +6607,52 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.extended.statistics"
+-- check \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for regress_du_admin';
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+\du regress_du_role*
+ List of roles
+ Role name | Attributes | Member of
+------------------+--------------+--------------------------------------------------
+ regress_du_role0 | Cannot login |
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+
+ | | regress_du_role0 from regress_du_role1 (i) +
+ | | regress_du_role0 from regress_du_role2 (s)
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +
+ | | regress_du_role0 from regress_du_role1 (i, s) +
+ | | regress_du_role0 from regress_du_role2 (empty) +
+ | | regress_du_role1 from regress_du_admin (a, s)
+
+\du+ regress_du_role*
+ List of roles
+ Role name | Attributes | Member of | Description
+------------------+--------------+--------------------------------------------------+----------------------------------
+ regress_du_role0 | Cannot login | | Description for regress_du_role0
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+| Description for regress_du_role1
+ | | regress_du_role0 from regress_du_role1 (i) +|
+ | | regress_du_role0 from regress_du_role2 (s) |
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +| Description for regress_du_role2
+ | | regress_du_role0 from regress_du_role1 (i, s) +|
+ | | regress_du_role0 from regress_du_role2 (empty) +|
+ | | regress_du_role1 from regress_du_admin (a, s) |
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 961783d6ea..1e8d46882b 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1815,3 +1815,33 @@ DROP FUNCTION psql_error;
\dP "no.such.database"."no.such.schema"."no.such.partitioned.relation"
\dT "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
+
+-- check \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for regress_du_admin';
+
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+
+\du regress_du_role*
+\du+ regress_du_role*
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
--
2.34.1
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-03-22 18:11 ` Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Pavel Luzanov @ 2023-03-22 18:11 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
In the previous version, I didn't notice (unlike cfbot) the compiler
warning. Fixed in version 6.
-----
Pavel Luzanov
Attachments:
[text/x-patch] v6-0001-psql-show-membership-options-in-the-du-command.patch (13.3K, ../../[email protected]/2-v6-0001-psql-show-membership-options-in-the-du-command.patch)
download | inline diff:
From 1b8b5743df23637b70e8d4ad0df0e1f892c595f3 Mon Sep 17 00:00:00 2001
From: Pavel Luzanov <[email protected]>
Date: Wed, 22 Mar 2023 20:54:41 +0300
Subject: [PATCH v6] psql: show membership options in the \du command
Format for the "Member of" column completely redesigned.
Shown within each row, in newline-separated format, are the memberships granted to
the role. The presentation includes both the name of the grantor
as well as the membership permissions (in an abbreviated format:
a for admin option, i for inherit option, s for set option.)
The word 'empty' is printed in the case that none of those permissions are granted.
---
doc/src/sgml/ref/psql-ref.sgml | 30 +++++++++++---
src/bin/psql/describe.c | 63 ++++++++++++++++++++----------
src/test/regress/expected/psql.out | 49 +++++++++++++++++++++++
src/test/regress/sql/psql.sql | 30 ++++++++++++++
4 files changed, 146 insertions(+), 26 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7b8ae9fac3..03e7da93de 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1727,9 +1727,18 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\dg+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ </para>
+ <para>
+ Shown within each row, in newline-separated format, are the memberships granted to
+ the role. The presentation includes both the name of the grantor
+ as well as the membership permissions (in an abbreviated format:
+ <literal>a</literal> for admin option, <literal>i</literal> for inherit option,
+ <literal>s</literal> for set option.) The word <literal>empty</literal> is printed in
+ the case that none of those permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ If the form <literal>\dg+</literal> is used the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
@@ -1969,9 +1978,18 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
<literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
- If the form <literal>\du+</literal> is used, additional information
- is shown about each role; currently this adds the comment for each
- role.
+ </para>
+ <para>
+ Shown within each row, in newline-separated format, are the memberships granted to
+ the role. The presentation includes both the name of the grantor
+ as well as the membership permissions (in an abbreviated format:
+ <literal>a</literal> for admin option, <literal>i</literal> for inherit option,
+ <literal>s</literal> for set option.) The word <literal>empty</literal> is printed in
+ the case that none of those permissions are granted.
+ See the <link linkend="sql-grant"><command>GRANT</command></link> command for their meaning.
+ </para>
+ <para>
+ If the form <literal>\du+</literal> is used the comment attached to the role is shown.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 99e28f607e..7f2b7c9363 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3631,24 +3631,42 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printfPQExpBuffer(&buf,
"SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
" r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
- " r.rolconnlimit, r.rolvaliduntil,\n"
- " ARRAY(SELECT b.rolname\n"
- " FROM pg_catalog.pg_auth_members m\n"
- " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
- " WHERE m.member = r.oid) as memberof");
+ " r.rolconnlimit, r.rolvaliduntil, r.rolreplication,\n");
- if (verbose)
- {
- appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
- ncols++;
- }
- appendPQExpBufferStr(&buf, "\n, r.rolreplication");
+ if (pset.sversion >= 160000)
+ appendPQExpBufferStr(&buf,
+ " (SELECT pg_catalog.string_agg(\n"
+ " pg_catalog.format('%I from %I (%s)',\n"
+ " b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text,\n"
+ " pg_catalog.regexp_replace(\n"
+ " pg_catalog.concat_ws(', ',\n"
+ " CASE WHEN m.admin_option THEN 'a' END,\n"
+ " CASE WHEN m.inherit_option THEN 'i' END,\n"
+ " CASE WHEN m.set_option THEN 's' END),\n"
+ " '^$', 'empty')\n"
+ " ), E'\\n'\n"
+ " ORDER BY b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text)\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
+ else
+ appendPQExpBufferStr(&buf,
+ " ARRAY(SELECT b.rolname\n"
+ " FROM pg_catalog.pg_auth_members m\n"
+ " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
+ " WHERE m.member = r.oid) as memberof");
if (pset.sversion >= 90500)
{
appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
}
+ if (verbose)
+ {
+ appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
+ ncols++;
+ }
+
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
if (!showSystem && !pattern)
@@ -3701,13 +3719,6 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
- if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
- add_role_attribute(&buf, _("Replication"));
-
- if (pset.sversion >= 90500)
- if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
- add_role_attribute(&buf, _("Bypass RLS"));
-
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
{
@@ -3735,10 +3746,22 @@ describeRoles(const char *pattern, bool verbose, bool showSystem)
printTableAddCell(&cont, attr[i], false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
+ add_role_attribute(&buf, _("Replication"));
+
+ printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+
+ if (pset.sversion >= 90500)
+ if (strcmp(PQgetvalue(res, i, 10), "t") == 0)
+ add_role_attribute(&buf, _("Bypass RLS"));
if (verbose)
- printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ {
+ if (pset.sversion >= 90500)
+ printTableAddCell(&cont, PQgetvalue(res, i, 11), false, false);
+ else
+ printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
+ }
}
termPQExpBuffer(&buf);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index c00e28361c..d5f31b2082 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6607,3 +6607,52 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.extended.statistics"
+-- check \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for regress_du_admin';
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+\du regress_du_role*
+ List of roles
+ Role name | Attributes | Member of
+------------------+--------------+--------------------------------------------------
+ regress_du_role0 | Cannot login |
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+
+ | | regress_du_role0 from regress_du_role1 (i) +
+ | | regress_du_role0 from regress_du_role2 (s)
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +
+ | | regress_du_role0 from regress_du_role1 (i, s) +
+ | | regress_du_role0 from regress_du_role2 (empty) +
+ | | regress_du_role1 from regress_du_admin (a, s)
+
+\du+ regress_du_role*
+ List of roles
+ Role name | Attributes | Member of | Description
+------------------+--------------+--------------------------------------------------+----------------------------------
+ regress_du_role0 | Cannot login | | Description for regress_du_role0
+ regress_du_role1 | Cannot login | regress_du_role0 from regress_du_admin (a, i, s)+| Description for regress_du_role1
+ | | regress_du_role0 from regress_du_role1 (i) +|
+ | | regress_du_role0 from regress_du_role2 (s) |
+ regress_du_role2 | Cannot login | regress_du_role0 from regress_du_admin (a) +| Description for regress_du_role2
+ | | regress_du_role0 from regress_du_role1 (i, s) +|
+ | | regress_du_role0 from regress_du_role2 (empty) +|
+ | | regress_du_role1 from regress_du_admin (a, s) |
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 961783d6ea..1e8d46882b 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1815,3 +1815,33 @@ DROP FUNCTION psql_error;
\dP "no.such.database"."no.such.schema"."no.such.partitioned.relation"
\dT "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
+
+-- check \du
+CREATE ROLE regress_du_role0;
+CREATE ROLE regress_du_role1;
+CREATE ROLE regress_du_role2;
+CREATE ROLE regress_du_admin LOGIN CREATEROLE;
+COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
+COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
+COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
+COMMENT ON ROLE regress_du_admin IS 'Description for regress_du_admin';
+
+GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
+GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
+
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
+GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE GRANTED BY regress_du_admin;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE GRANTED BY regress_du_role1;
+GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE GRANTED BY regress_du_role2;
+GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
+
+\du regress_du_role*
+\du+ regress_du_role*
+
+DROP ROLE regress_du_role0;
+DROP ROLE regress_du_role1;
+DROP ROLE regress_du_role2;
+DROP ROLE regress_du_admin;
--
2.34.1
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
@ 2023-04-03 22:06 ` David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: David G. Johnston @ 2023-04-03 22:06 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; [email protected] <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On Wed, Mar 22, 2023 at 11:11 AM Pavel Luzanov <[email protected]>
wrote:
> In the previous version, I didn't notice (unlike cfbot) the compiler
> warning. Fixed in version 6.
>
>
I've marked this Ready for Committer.
My opinion is that this is a necessary modification due to the
already-committed changes to the membership grant implementation and so
only needs to be accepted prior to v16 going live, not feature freeze.
I've added Robert to this thread as the committer of said changes.
David J.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
@ 2023-04-04 16:13 ` Tom Lane <[email protected]>
2023-04-04 16:38 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:39 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
0 siblings, 2 replies; 29+ messages in thread
From: Tom Lane @ 2023-04-04 16:13 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Pavel Luzanov <[email protected]>; [email protected] <[email protected]>; David Zhang <[email protected]>; [email protected] <[email protected]>
"David G. Johnston" <[email protected]> writes:
> I've marked this Ready for Committer.
Hmm ... not sure I like the proposed output. The 'a', 'i', 's'
annotations are short but they don't have much else to recommend them.
On the other hand, there's nearby precedent for single-letter
abbreviations in ACL displays. Nobody particularly likes those,
though. Also, if we're modeling this on ACLs then the display
could be further shortened to "(ais)" or the like.
Also, the patch is ignoring i18n issues. I suppose if we stick with
said single-letter abbreviations we'd not translate them, but the
construction "rolename from rolename" ought to be translatable IMO.
Perhaps it'd be enough to allow replacement of "from", but I wonder
if the phrase order would need to be different in some languages?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
@ 2023-04-04 16:38 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 29+ messages in thread
From: David G. Johnston @ 2023-04-04 16:38 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pavel Luzanov <[email protected]>; [email protected] <[email protected]>; David Zhang <[email protected]>; [email protected] <[email protected]>
On Tue, Apr 4, 2023 at 9:13 AM Tom Lane <[email protected]> wrote:
> "David G. Johnston" <[email protected]> writes:
> > I've marked this Ready for Committer.
>
> Hmm ... not sure I like the proposed output. The 'a', 'i', 's'
> annotations are short but they don't have much else to recommend them.
> On the other hand, there's nearby precedent for single-letter
> abbreviations in ACL displays. Nobody particularly likes those,
> though. Also, if we're modeling this on ACLs then the display
> could be further shortened to "(ais)" or the like.
>
I am on board with removing the comma and space between the specifiers. My
particular issue with the condensed form is readability, especially the
lowercase "i". We aren't so desperate for horizontal space here that
compaction seems particularly desirable.
>
> Also, the patch is ignoring i18n issues.
Fair point.
> I suppose if we stick with
> said single-letter abbreviations we'd not translate them,
Correct. I don't see this being a huge issue - the abbreviations are the
first letter of the various option "keywords" specified in the syntax.
> but the
> construction "rolename from rolename" ought to be translatable IMO.
> Perhaps it'd be enough to allow replacement of "from", but I wonder
> if the phrase order would need to be different in some languages?
>
>
Leveraging position and some optional symbols for readability, and sticking
with the premise that abbreviations down to the first letter of the
relevant syntax keyword is OK:
rolename [g: grantor_role] (ais)
I don't have any ideas regarding i18n concerns besides avoiding them by not
using words...but I'd much prefer "from" and just hope the equivalent in
other languages is just as understandable.
I'd rather have the above than go and fully try to emulate ACL presentation
just to avoid i18n issues.
David J.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
@ 2023-04-04 16:39 ` Robert Haas <[email protected]>
2023-04-04 17:12 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
1 sibling, 1 reply; 29+ messages in thread
From: Robert Haas @ 2023-04-04 16:39 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pavel Luzanov <[email protected]>; David Zhang <[email protected]>; [email protected] <[email protected]>
On Tue, Apr 4, 2023 at 12:13 PM Tom Lane <[email protected]> wrote:
> Hmm ... not sure I like the proposed output. The 'a', 'i', 's'
> annotations are short but they don't have much else to recommend them.
Yeah, I don't like that, either.
I'm not sure what the right thing to do is here. It's a problem to
have new information in the catalogs that you can't view via
\d<whatever>. But displaying that information as a string of
characters that will be gibberish to anyone but an expert doesn't
necessarily seem like it really solves the problem. However, if we
spell out the words, then it gets bulky. But maybe bulky is better
than incomprehensible.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 16:39 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
@ 2023-04-04 17:12 ` Tom Lane <[email protected]>
2023-04-04 17:29 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2023-04-04 17:12 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pavel Luzanov <[email protected]>; David Zhang <[email protected]>; [email protected] <[email protected]>
Robert Haas <[email protected]> writes:
> I'm not sure what the right thing to do is here. It's a problem to
> have new information in the catalogs that you can't view via
> \d<whatever>. But displaying that information as a string of
> characters that will be gibberish to anyone but an expert doesn't
> necessarily seem like it really solves the problem. However, if we
> spell out the words, then it gets bulky. But maybe bulky is better
> than incomprehensible.
The existing precedent in \du definitely leans towards "bulky":
regression=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
alice | Cannot login | {bob}
bob | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
It seems pretty inconsistent to me to treat the role attributes this
way and then economize in the adjacent column.
Another advantage to spelling out the SQL keywords is that it removes
the question of whether we should translate them.
I wonder if, while we're here, we should apply the idea of
joining-with-newlines-not-commas to the attributes column too.
That's another source of inconsistency in the proposed display.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 16:39 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:12 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
@ 2023-04-04 17:29 ` Robert Haas <[email protected]>
2023-04-04 17:37 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Robert Haas @ 2023-04-04 17:29 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pavel Luzanov <[email protected]>; David Zhang <[email protected]>; [email protected] <[email protected]>
On Tue, Apr 4, 2023 at 1:12 PM Tom Lane <[email protected]> wrote:
> I wonder if, while we're here, we should apply the idea of
> joining-with-newlines-not-commas to the attributes column too.
> That's another source of inconsistency in the proposed display.
That would make the column narrower, which might be good, because it
seems to me that listing the memberships could require quite a lot of
space, both vertical and horizontal.
There can be any number of memberships, and each of those memberships
has a grantor and three flag bits (INHERIT, SET, ADMIN). If some user
with a long username has been granted membership with all three of
those flags by a grantor who also has a long username, and if we show
all that information, we're going to use up a lot of horizontal space.
And if there are many such grants, also a lot of vertical space.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 16:39 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:12 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 17:29 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
@ 2023-04-04 17:37 ` Tom Lane <[email protected]>
2023-04-04 19:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2023-04-04 17:37 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pavel Luzanov <[email protected]>; David Zhang <[email protected]>; [email protected] <[email protected]>
Robert Haas <[email protected]> writes:
> On Tue, Apr 4, 2023 at 1:12 PM Tom Lane <[email protected]> wrote:
>> I wonder if, while we're here, we should apply the idea of
>> joining-with-newlines-not-commas to the attributes column too.
> That would make the column narrower, which might be good, because it
> seems to me that listing the memberships could require quite a lot of
> space, both vertical and horizontal.
Right, that's what I was thinking.
> There can be any number of memberships, and each of those memberships
> has a grantor and three flag bits (INHERIT, SET, ADMIN). If some user
> with a long username has been granted membership with all three of
> those flags by a grantor who also has a long username, and if we show
> all that information, we're going to use up a lot of horizontal space.
> And if there are many such grants, also a lot of vertical space.
Yup --- and if you were incautious enough to not exclude the bootstrap
superuser, then you'll also have a very wide Attributes column. We
can buy back some of that by joining the attributes with newlines.
At some point people are going to have to resort to \x mode for this
display, but we should do what we can to put that off as long as we're
not sacrificing readability.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 16:39 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:12 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 17:29 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:37 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
@ 2023-04-04 19:02 ` David G. Johnston <[email protected]>
2023-04-04 20:00 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: David G. Johnston @ 2023-04-04 19:02 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Robert Haas <[email protected]>; Pavel Luzanov <[email protected]>; David Zhang <[email protected]>; [email protected] <[email protected]>
On Tue, Apr 4, 2023 at 10:37 AM Tom Lane <[email protected]> wrote:
> Robert Haas <[email protected]> writes:
> > On Tue, Apr 4, 2023 at 1:12 PM Tom Lane <[email protected]> wrote:
> >> I wonder if, while we're here, we should apply the idea of
> >> joining-with-newlines-not-commas to the attributes column too.
>
> > That would make the column narrower, which might be good, because it
> > seems to me that listing the memberships could require quite a lot of
> > space, both vertical and horizontal.
>
> Right, that's what I was thinking.
>
>
So, by way of example:
regress_du_role1 | cannot login | regress_du_role0 granted by
regress_du_admin with admin, inherit, set | Description for regress_du_role1
~140 character width with description
No translations, all words are either identical to syntax or identifiers.
I'm on board with newlines in the attributes field.
The specific member of column changes are:
from -> granted by
( ) -> "with"
ais -> admin, inherit, set
I'm good with any or all of those selections, either as-is or in the more
verbose form.
David J.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 16:39 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:12 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 17:29 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:37 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 19:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
@ 2023-04-04 20:00 ` Robert Haas <[email protected]>
2023-04-04 20:42 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 29+ messages in thread
From: Robert Haas @ 2023-04-04 20:00 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; Pavel Luzanov <[email protected]>; David Zhang <[email protected]>; [email protected] <[email protected]>
On Tue, Apr 4, 2023 at 3:02 PM David G. Johnston
<[email protected]> wrote:
> So, by way of example:
>
> regress_du_role1 | cannot login | regress_du_role0 granted by regress_du_admin with admin, inherit, set | Description for regress_du_role1
>
> ~140 character width with description
That seems wider than necessary. Why not have the third column be
something like regress_du_role0 by regress_du_admin (admin, inherit,
set)?
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 16:39 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:12 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 17:29 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:37 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 19:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 20:00 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
@ 2023-04-04 20:42 ` Pavel Luzanov <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Pavel Luzanov @ 2023-04-04 20:42 UTC (permalink / raw)
To: Robert Haas <[email protected]>; David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; David Zhang <[email protected]>; [email protected] <[email protected]>
On 04.04.2023 23:00, Robert Haas wrote:
> On Tue, Apr 4, 2023 at 3:02 PM David G. Johnston
> <[email protected]> wrote:
>> So, by way of example:
>>
>> regress_du_role1 | cannot login | regress_du_role0 granted by regress_du_admin with admin, inherit, set | Description for regress_du_role1
>>
>>
>> That seems wider than necessary. Why not have the third column be
>> something like regress_du_role0 by regress_du_admin (admin, inherit,
>> set)?
'granted by' can be left without translation, but just 'by' required
translation, as I think.
--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: psql: Add role's membership options to the \du+ command
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
@ 2023-03-10 12:18 ` Pavel Luzanov <[email protected]>
1 sibling, 0 replies; 29+ messages in thread
From: Pavel Luzanov @ 2023-03-10 12:18 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Zhang <[email protected]>; [email protected] <[email protected]>
On 08.03.2023 00:02, David G. Johnston wrote:
>
> FWIW I've finally gotten to publishing my beta version of the Role
> Graph for PostgreSQL pseudo-extension I'd been talking about:
>
> https://github.com/polobo/RoleGraphForPostgreSQL
Great. So far I've looked very briefly, but it's interesting.
> I handle EMPTY explicitly in the Role Graph but as I noted somewhere
> in my comments, it really shouldn't be possible to leave the database
> in that state. Do we need to bug Robert on this directly or do you
> plan to have a go at it?
I don't plan to do that. Right now I don't have enough time and
experience. This requires an experienced developer.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
^ permalink raw reply [nested|flat] 29+ messages in thread
* [PATCH v18 2/8] Row pattern recognition patch (parse/analysis).
@ 2024-05-11 07:11 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 29+ messages in thread
From: Tatsuo Ishii @ 2024-05-11 07:11 UTC (permalink / raw)
---
src/backend/parser/parse_agg.c | 7 +
src/backend/parser/parse_clause.c | 296 +++++++++++++++++++++++++++++-
src/backend/parser/parse_expr.c | 4 +
src/backend/parser/parse_func.c | 3 +
4 files changed, 309 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index bee7d8346a..9bc22a836a 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -577,6 +577,10 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
errkind = true;
break;
+ case EXPR_KIND_RPR_DEFINE:
+ errkind = true;
+ break;
+
/*
* There is intentionally no default: case here, so that the
* compiler will warn if we add a new ParseExprKind without
@@ -967,6 +971,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_RPR_DEFINE:
+ errkind = true;
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 8118036495..9762dce81f 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -98,7 +98,14 @@ static WindowClause *findWindowClause(List *wclist, const char *name);
static Node *transformFrameOffset(ParseState *pstate, int frameOptions,
Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc,
Node *clause);
-
+static void transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
+ List **targetlist);
+static List *transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
+ List **targetlist);
+static void transformPatternClause(ParseState *pstate, WindowClause *wc,
+ WindowDef *windef);
+static List *transformMeasureClause(ParseState *pstate, WindowClause *wc,
+ WindowDef *windef);
/*
* transformFromClause -
@@ -2956,6 +2963,10 @@ transformWindowDefinitions(ParseState *pstate,
rangeopfamily, rangeopcintype,
&wc->endInRangeFunc,
windef->endOffset);
+
+ /* Process Row Pattern Recognition related clauses */
+ transformRPR(pstate, wc, windef, targetlist);
+
wc->winref = winref;
result = lappend(result, wc);
@@ -3820,3 +3831,286 @@ transformFrameOffset(ParseState *pstate, int frameOptions,
return node;
}
+
+/*
+ * transformRPR
+ * Process Row Pattern Recognition related clauses
+ */
+static void
+transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
+ List **targetlist)
+{
+ /*
+ * Window definition exists?
+ */
+ if (windef == NULL)
+ return;
+
+ /*
+ * Row Pattern Common Syntax clause exists?
+ */
+ if (windef->rpCommonSyntax == NULL)
+ return;
+
+ /* Check Frame option. Frame must start at current row */
+ if ((wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("FRAME must start at current row when row patttern recognition is used")));
+
+ /* Transform AFTER MACH SKIP TO clause */
+ wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo;
+
+ /* Transform AFTER MACH SKIP TO variable */
+ wc->rpSkipVariable = windef->rpCommonSyntax->rpSkipVariable;
+
+ /* Transform SEEK or INITIAL clause */
+ wc->initial = windef->rpCommonSyntax->initial;
+
+ /* Transform DEFINE clause into list of TargetEntry's */
+ wc->defineClause = transformDefineClause(pstate, wc, windef, targetlist);
+
+ /* Check PATTERN clause and copy to patternClause */
+ transformPatternClause(pstate, wc, windef);
+
+ /* Transform MEASURE clause */
+ transformMeasureClause(pstate, wc, windef);
+}
+
+/*
+ * transformDefineClause Process DEFINE clause and transform ResTarget into
+ * list of TargetEntry.
+ *
+ * XXX we only support column reference in row pattern definition search
+ * condition, e.g. "price". <row pattern definition variable name>.<column
+ * reference> is not supported, e.g. "A.price".
+ */
+static List *
+transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
+ List **targetlist)
+{
+ /* DEFINE variable name initials */
+ static char *defineVariableInitials = "abcdefghijklmnopqrstuvwxyz";
+
+ ListCell *lc,
+ *l;
+ ResTarget *restarget,
+ *r;
+ List *restargets;
+ List *defineClause;
+ char *name;
+ int initialLen;
+ int i;
+
+ /*
+ * If Row Definition Common Syntax exists, DEFINE clause must exist. (the
+ * raw parser should have already checked it.)
+ */
+ Assert(windef->rpCommonSyntax->rpDefs != NULL);
+
+ /*
+ * Check and add "A AS A IS TRUE" if pattern variable is missing in DEFINE
+ * per the SQL standard.
+ */
+ restargets = NIL;
+ foreach(lc, windef->rpCommonSyntax->rpPatterns)
+ {
+ A_Expr *a;
+ bool found = false;
+
+ if (!IsA(lfirst(lc), A_Expr))
+ ereport(ERROR,
+ errmsg("node type is not A_Expr"));
+
+ a = (A_Expr *) lfirst(lc);
+ name = strVal(a->lexpr);
+
+ foreach(l, windef->rpCommonSyntax->rpDefs)
+ {
+ restarget = (ResTarget *) lfirst(l);
+
+ if (!strcmp(restarget->name, name))
+ {
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ {
+ /*
+ * "name" is missing. So create "name AS name IS TRUE" ResTarget
+ * node and add it to the temporary list.
+ */
+ A_Const *n;
+
+ restarget = makeNode(ResTarget);
+ n = makeNode(A_Const);
+ n->val.boolval.type = T_Boolean;
+ n->val.boolval.boolval = true;
+ n->location = -1;
+ restarget->name = pstrdup(name);
+ restarget->indirection = NIL;
+ restarget->val = (Node *) n;
+ restarget->location = -1;
+ restargets = lappend((List *) restargets, restarget);
+ }
+ }
+
+ if (list_length(restargets) >= 1)
+ {
+ /* add missing DEFINEs */
+ windef->rpCommonSyntax->rpDefs =
+ list_concat(windef->rpCommonSyntax->rpDefs, restargets);
+ list_free(restargets);
+ }
+
+ /*
+ * Check for duplicate row pattern definition variables. The standard
+ * requires that no two row pattern definition variable names shall be
+ * equivalent.
+ */
+ restargets = NIL;
+ foreach(lc, windef->rpCommonSyntax->rpDefs)
+ {
+ restarget = (ResTarget *) lfirst(lc);
+ name = restarget->name;
+
+ /*
+ * Add DEFINE expression (Restarget->val) to the targetlist as a
+ * TargetEntry if it does not exist yet. Planner will add the column
+ * ref var node to the outer plan's target list later on. This makes
+ * DEFINE expression could access the outer tuple while evaluating
+ * PATTERN.
+ *
+ * XXX: adding whole expressions of DEFINE to the plan.targetlist is
+ * not so good, because it's not necessary to evalute the expression
+ * in the target list while running the plan. We should extract the
+ * var nodes only then add them to the plan.targetlist.
+ */
+ findTargetlistEntrySQL99(pstate, (Node *) restarget->val,
+ targetlist, EXPR_KIND_RPR_DEFINE);
+
+ /*
+ * Make sure that the row pattern definition search condition is a
+ * boolean expression.
+ */
+ transformWhereClause(pstate, restarget->val,
+ EXPR_KIND_RPR_DEFINE, "DEFINE");
+
+ foreach(l, restargets)
+ {
+ char *n;
+
+ r = (ResTarget *) lfirst(l);
+ n = r->name;
+
+ if (!strcmp(n, name))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row pattern definition variable name \"%s\" appears more than once in DEFINE clause",
+ name),
+ parser_errposition(pstate, exprLocation((Node *) r))));
+ }
+ restargets = lappend(restargets, restarget);
+ }
+ list_free(restargets);
+
+ /*
+ * Create list of row pattern DEFINE variable name's initial. We assign
+ * [a-z] to them (up to 26 variable names are allowed).
+ */
+ restargets = NIL;
+ i = 0;
+ initialLen = strlen(defineVariableInitials);
+
+ foreach(lc, windef->rpCommonSyntax->rpDefs)
+ {
+ char initial[2];
+
+ restarget = (ResTarget *) lfirst(lc);
+ name = restarget->name;
+
+ if (i >= initialLen)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("number of row pattern definition variable names exceeds %d",
+ initialLen),
+ parser_errposition(pstate,
+ exprLocation((Node *) restarget))));
+ }
+ initial[0] = defineVariableInitials[i++];
+ initial[1] = '\0';
+ wc->defineInitial = lappend(wc->defineInitial,
+ makeString(pstrdup(initial)));
+ }
+
+ defineClause = transformTargetList(pstate, windef->rpCommonSyntax->rpDefs,
+ EXPR_KIND_RPR_DEFINE);
+
+ /* mark column origins */
+ markTargetListOrigins(pstate, defineClause);
+
+ /* mark all nodes in the DEFINE clause tree with collation information */
+ assign_expr_collations(pstate, (Node *) defineClause);
+
+ return defineClause;
+}
+
+/*
+ * transformPatternClause
+ * Process PATTERN clause and return PATTERN clause in the raw parse tree
+ */
+static void
+transformPatternClause(ParseState *pstate, WindowClause *wc,
+ WindowDef *windef)
+{
+ ListCell *lc;
+
+ /*
+ * Row Pattern Common Syntax clause exists?
+ */
+ if (windef->rpCommonSyntax == NULL)
+ return;
+
+ wc->patternVariable = NIL;
+ wc->patternRegexp = NIL;
+ foreach(lc, windef->rpCommonSyntax->rpPatterns)
+ {
+ A_Expr *a;
+ char *name;
+ char *regexp;
+
+ if (!IsA(lfirst(lc), A_Expr))
+ ereport(ERROR,
+ errmsg("node type is not A_Expr"));
+
+ a = (A_Expr *) lfirst(lc);
+ name = strVal(a->lexpr);
+
+ wc->patternVariable = lappend(wc->patternVariable, makeString(pstrdup(name)));
+ regexp = strVal(lfirst(list_head(a->name)));
+
+ wc->patternRegexp = lappend(wc->patternRegexp, makeString(pstrdup(regexp)));
+ }
+}
+
+/*
+ * transformMeasureClause
+ * Process MEASURE clause
+ * XXX MEASURE clause is not supported yet
+ */
+static List *
+transformMeasureClause(ParseState *pstate, WindowClause *wc,
+ WindowDef *windef)
+{
+ if (windef->rowPatternMeasures == NIL)
+ return NIL;
+
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("%s", "MEASURE clause is not supported yet"),
+ parser_errposition(pstate, exprLocation((Node *) windef->rowPatternMeasures))));
+ return NIL;
+}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index aba3546ed1..eb138087bf 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -578,6 +578,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
case EXPR_KIND_COPY_WHERE:
case EXPR_KIND_GENERATED_COLUMN:
case EXPR_KIND_CYCLE_MARK:
+ case EXPR_KIND_RPR_DEFINE:
/* okay */
break;
@@ -1817,6 +1818,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_VALUES:
case EXPR_KIND_VALUES_SINGLE:
case EXPR_KIND_CYCLE_MARK:
+ case EXPR_KIND_RPR_DEFINE:
/* okay */
break;
case EXPR_KIND_CHECK_CONSTRAINT:
@@ -3197,6 +3199,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "GENERATED AS";
case EXPR_KIND_CYCLE_MARK:
return "CYCLE";
+ case EXPR_KIND_RPR_DEFINE:
+ return "DEFINE";
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 9b23344a3b..4c482abb30 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2658,6 +2658,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_RPR_DEFINE:
+ errkind = true;
+ break;
/*
* There is intentionally no default: case here, so that the
--
2.25.1
----Next_Part(Sat_May_11_16_23_07_2024_789)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v18-0003-Row-pattern-recognition-patch-rewriter.patch"
^ permalink raw reply [nested|flat] 29+ messages in thread
end of thread, other threads:[~2024-05-11 07:11 UTC | newest]
Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-12 23:21 [PATCH] Introduce timeout capability for ConditionVariableSleep Shawn Debnath <[email protected]>
2023-02-16 18:03 Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 11:02 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-17 16:53 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-21 21:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-02-21 21:34 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-02-27 20:14 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-01 10:55 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 11:01 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-03 16:21 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-06 07:43 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-07 21:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-08 02:31 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-03-10 12:06 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-20 08:49 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-21 03:37 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-22 18:11 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-04-03 22:06 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:13 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 16:38 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 16:39 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:12 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 17:29 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 17:37 ` Re: psql: Add role's membership options to the \du+ command Tom Lane <[email protected]>
2023-04-04 19:02 ` Re: psql: Add role's membership options to the \du+ command David G. Johnston <[email protected]>
2023-04-04 20:00 ` Re: psql: Add role's membership options to the \du+ command Robert Haas <[email protected]>
2023-04-04 20:42 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2023-03-10 12:18 ` Re: psql: Add role's membership options to the \du+ command Pavel Luzanov <[email protected]>
2024-05-11 07:11 [PATCH v18 2/8] Row pattern recognition patch (parse/analysis). Tatsuo Ishii <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox