Hi,
I have read the entire thread discussion. I understood the importance of
this enhancement related to /conninfo+ meta command. I really appreciate
the efforts of Maiquel and suggestions made by the reviewers. According to
best of my understanding, libpq API should be used instead of creating
server query for conninfo+ meta command. Building on the patch (v29)
provided by Maiquel, I made changes to retrieve some extra information
related to connection from libpq API.
Extra information includes:
- Protocol Version
- SSL Connection
- GSSAPI Authenticated
- Client Encoding
- Server Encoding
- Session User
- Backend PID
Output of \conninfo+:
1.
$ bin/psql --port=5430 postgres -h localhost
psql (18devel)
Type "help" for help.
postgres=# \conninfo+
You are connected to database "postgres" as user "hunaid" on host
"localhost" (address "127.0.0.1") at port "5430".
Protocol Version: 3
SSL Connection: no
GSSAPI Authenticated: no
Client Encoding: UTF8
Server Encoding: UTF8
Session User: hunaid
Backend PID: 163816
I have also edited the documentation and added it to the patch. Please let
me know if any changes are required.
Regards,
Hunaid Sohail
On Wed, Jun 5, 2024 at 5:32 PM Maiquel Grassi <[email protected]> wrote:
> From a quick skim of the latest messages in this thread, it sounds like
> there are a couple of folks who think \conninfo (and consequently
> \conninfo+) should only report values from the libpq API. I think they
> would prefer server-side information to be provided via a system view or
> maybe an entirely new psql meta-command.
>
> IIUC a new system view would more-or-less just gather information from
> other system views and functions. A view would be nice because it could be
> used outside psql, but I'm not sure to what extent we are comfortable
> adding system views built on other system views. Something like
> \sessioninfo (as proposed by Sami) would look more similar to what you have
> in your latest patch, i.e., we'd teach psql about a complicated query for
> gathering all this disparate information.
>
> IMHO a good way to help move this forward is to try implementing it as a
> system view so that we can compare the two approaches. I've had luck in
> the past with implementing something a couple different ways to help drive
> consensus.
>
> --//--
>
> Great Nathan, we can follow that path of the view. I leave it open for
> anyone in the thread who has been following from the beginning to start the
> development of the view. Even you, if you have the interest and time to do
> it. At this exact moment, I am involved in a "my baby born" project, so I
> am unable to stop to look into this. If someone wants to start, I would
> appreciate it.
> Regards,
> Maiquel Grassi.
>
��d i f f - - g i t a / d o c / s r c / s g m l / r e f / p s q l - r e f . s g m l b / d o c / s r c / s g m l / r e f / p s q l - r e f . s g m l
i n d e x 0 7 4 1 9 a 3 b 9 2 . . f b 3 d 5 2 f b 1 6 1 0 0 6 4 4
- - - a / d o c / s r c / s g m l / r e f / p s q l - r e f . s g m l
+ + + b / d o c / s r c / s g m l / r e f / p s q l - r e f . s g m l
@ @ - 1 0 3 0 , 1 1 + 1 0 3 0 , 2 9 @ @ I N S E R T I N T O t b l 1 V A L U E S ( $ 1 , $ 2 ) \ b i n d ' f i r s t v a l u e ' ' s e c o n d v a l u e ' \ g
< / v a r l i s t e n t r y >
< v a r l i s t e n t r y i d = " a p p - p s q l - m e t a - c o m m a n d - c o n n i n f o " >
- < t e r m > < l i t e r a l > \ c o n n i n f o < / l i t e r a l > < / t e r m >
+ < t e r m > < l i t e r a l > \ c o n n i n f o [ + ] < / l i t e r a l > < / t e r m >
< l i s t i t e m >
- < p a r a >
- O u t p u t s i n f o r m a t i o n a b o u t t h e c u r r e n t d a t a b a s e c o n n e c t i o n .
- < / p a r a >
+ < p a r a >
+ O u t p u t s a s t r i n g d i s p l a y i n g i n f o r m a t i o n a b o u t t h e c u r r e n t
+ d a t a b a s e c o n n e c t i o n . W h e n < l i t e r a l > + < / l i t e r a l > i s a p p e n d e d ,
+ m o r e d e t a i l s a b o u t t h e c o n n e c t i o n a r e d i s p l a y e d i n t a b l e
+ f o r m a t :
+ < s i m p l e l i s t >
+ < m e m b e r > < l i t e r a l > P r o t o c o l V e r s i o n : < / l i t e r a l > T h e v e r s i o n o f t h e P o s t g r e S Q L p r o t o c o l u s e d f o r
+ t h i s c o n n e c t i o n . < / m e m b e r >
+ < m e m b e r > < l i t e r a l > S S L C o n n e c t i o n : < / l i t e r a l > T r u e i f t h e c u r r e n t c o n n e c t i o n t o t h e s e r v e r
+ u s e s S S L , a n d f a l s e o t h e r w i s e . < / m e m b e r >
+ < m e m b e r > < l i t e r a l > G S S A P I A u t h e n t i c a t e d : < / l i t e r a l > T r u e i f G S S A P I i s i n u s e , o r f a l s e i f
+ G S S A P I i s n o t i n u s e o n t h i s c o n n e c t i o n . < / m e m b e r >
+ < m e m b e r > < l i t e r a l > C l i e n t E n c o d i n g : < / l i t e r a l > T h e e n c o d i n g u s e d b y t h e c l i e n t f o r t h i s c o n n e c t i o n . < / m e m b e r >
+ < m e m b e r > < l i t e r a l > S e r v e r E n c o d i n g : < / l i t e r a l > T h e e n c o d i n g u s e d b y t h e s e r v e r f o r t h i s c o n n e c t i o n . < / m e m b e r >
+ < m e m b e r > < l i t e r a l > S e s s i o n U s e r : < / l i t e r a l > T h e s e s s i o n u s e r ' s n a m e ;
+ s e e t h e < f u n c t i o n > s e s s i o n _ u s e r ( ) < / f u n c t i o n > f u n c t i o n i n
+ < x r e f l i n k e n d = " f u n c t i o n s - i n f o - s e s s i o n - t a b l e " / > f o r m o r e d e t a i l s . < / m e m b e r >
+ < m e m b e r > < l i t e r a l > B a c k e n d P I D : < / l i t e r a l > T h e p r o c e s s I D o f t h e b a c k e n d f o r t h e
+ c o n n e c t i o n . < / m e m b e r >
+ < / s i m p l e l i s t >
+ < / p a r a >
< / l i s t i t e m >
< / v a r l i s t e n t r y >
d i f f - - g i t a / s r c / b i n / p s q l / c o m m a n d . c b / s r c / b i n / p s q l / c o m m a n d . c
i n d e x 1 8 0 7 8 1 e c d 0 . . 8 f d 3 1 4 e d 3 7 1 0 0 6 4 4
- - - a / s r c / b i n / p s q l / c o m m a n d . c
+ + + b / s r c / b i n / p s q l / c o m m a n d . c
@ @ - 6 8 , 7 + 6 8 , 8 @ @ s t a t i c b a c k s l a s h R e s u l t e x e c _ c o m m a n d _ C ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n
s t a t i c b a c k s l a s h R e s u l t e x e c _ c o m m a n d _ c o n n e c t ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h ) ;
s t a t i c b a c k s l a s h R e s u l t e x e c _ c o m m a n d _ c d ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h ,
c o n s t c h a r * c m d ) ;
- s t a t i c b a c k s l a s h R e s u l t e x e c _ c o m m a n d _ c o n n i n f o ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h ) ;
+ s t a t i c b a c k s l a s h R e s u l t e x e c _ c o m m a n d _ c o n n i n f o ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h ,
+ c o n s t c h a r * c m d ) ;
s t a t i c b a c k s l a s h R e s u l t e x e c _ c o m m a n d _ c o p y ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h ) ;
s t a t i c b a c k s l a s h R e s u l t e x e c _ c o m m a n d _ c o p y r i g h t ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h ) ;
s t a t i c b a c k s l a s h R e s u l t e x e c _ c o m m a n d _ c r o s s t a b v i e w ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h ) ;
@ @ - 3 1 8 , 8 + 3 1 9 , 8 @ @ e x e c _ c o m m a n d ( c o n s t c h a r * c m d ,
s t a t u s = e x e c _ c o m m a n d _ c o n n e c t ( s c a n _ s t a t e , a c t i v e _ b r a n c h ) ;
e l s e i f ( s t r c m p ( c m d , " c d " ) = = 0 )
s t a t u s = e x e c _ c o m m a n d _ c d ( s c a n _ s t a t e , a c t i v e _ b r a n c h , c m d ) ;
- e l s e i f ( s t r c m p ( c m d , " c o n n i n f o " ) = = 0 )
- s t a t u s = e x e c _ c o m m a n d _ c o n n i n f o ( s c a n _ s t a t e , a c t i v e _ b r a n c h ) ;
+ e l s e i f ( s t r c m p ( c m d , " c o n n i n f o " ) = = 0 | | s t r c m p ( c m d , " c o n n i n f o + " ) = = 0 )
+ s t a t u s = e x e c _ c o m m a n d _ c o n n i n f o ( s c a n _ s t a t e , a c t i v e _ b r a n c h , c m d ) ;
e l s e i f ( p g _ s t r c a s e c m p ( c m d , " c o p y " ) = = 0 )
s t a t u s = e x e c _ c o m m a n d _ c o p y ( s c a n _ s t a t e , a c t i v e _ b r a n c h ) ;
e l s e i f ( s t r c m p ( c m d , " c o p y r i g h t " ) = = 0 )
@ @ - 6 4 4 , 1 1 + 6 4 5 , 1 4 @ @ e x e c _ c o m m a n d _ c d ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h , c o n s t c h a r * c m d )
}
/ *
- * \ c o n n i n f o - - d i s p l a y i n f o r m a t i o n a b o u t t h e c u r r e n t c o n n e c t i o n
+ * \ c o n n i n f o , \ c o n n i n f o + - - d i s p l a y i n f o r m a t i o n a b o u t t h e c u r r e n t c o n n e c t i o n
* /
s t a t i c b a c k s l a s h R e s u l t
- e x e c _ c o m m a n d _ c o n n i n f o ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h )
+ e x e c _ c o m m a n d _ c o n n i n f o ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h , c o n s t c h a r * c m d )
{
+ b o o l s h o w _ v e r b o s e ;
+ s h o w _ v e r b o s e = s t r c h r ( c m d , ' + ' ) ? t r u e : f a l s e ;
+
i f ( a c t i v e _ b r a n c h )
{
c h a r * d b = P Q d b ( p s e t . d b ) ;
@ @ - 6 7 9 , 6 + 6 8 3 , 2 5 @ @ e x e c _ c o m m a n d _ c o n n i n f o ( P s q l S c a n S t a t e s c a n _ s t a t e , b o o l a c t i v e _ b r a n c h )
p r i n t f ( _ ( " Y o u a r e c o n n e c t e d t o d a t a b a s e \ " % s \ " a s u s e r \ " % s \ " o n h o s t \ " % s \ " a t p o r t \ " % s \ " . \ n " ) ,
d b , P Q u s e r ( p s e t . d b ) , h o s t , P Q p o r t ( p s e t . d b ) ) ;
}
+ / * P r i n t s o m e a d d i t i o n a l i n f o r m a t i o n a b o u t t h e c o n n e c t i o n * /
+ i f ( s h o w _ v e r b o s e )
+ {
+ i n t p r o t o c o l _ v e r s i o n = P Q p r o t o c o l V e r s i o n ( p s e t . d b ) ;
+ i n t s s l _ i n _ u s e = P Q s s l I n U s e ( p s e t . d b ) ;
+ i n t g s s a p i _ u s e d = P Q c o n n e c t i o n U s e d G S S A P I ( p s e t . d b ) ;
+ c o n s t c h a r * c l i e n t _ e n c o d i n g = P Q p a r a m e t e r S t a t u s ( p s e t . d b , " c l i e n t _ e n c o d i n g " ) ;
+ c o n s t c h a r * s e r v e r _ e n c o d i n g = P Q p a r a m e t e r S t a t u s ( p s e t . d b , " s e r v e r _ e n c o d i n g " ) ;
+ c o n s t c h a r * s e s s i o n _ u s e r = P Q p a r a m e t e r S t a t u s ( p s e t . d b , " s e s s i o n _ a u t h o r i z a t i o n " ) ;
+ i n t b a c k e n d _ p i d = P Q b a c k e n d P I D ( p s e t . d b ) ;
+
+ p r i n t f ( _ ( " P r o t o c o l V e r s i o n : % d \ n " ) , p r o t o c o l _ v e r s i o n ) ;
+ p r i n t f ( _ ( " S S L C o n n e c t i o n : % s \ n " ) , s s l _ i n _ u s e ? _ ( " y e s " ) : _ ( " n o " ) ) ;
+ p r i n t f ( _ ( " G S S A P I A u t h e n t i c a t e d : % s \ n " ) , g s s a p i _ u s e d ? _ ( " y e s " ) : _ ( " n o " ) ) ;
+ p r i n t f ( _ ( " C l i e n t E n c o d i n g : % s \ n " ) , c l i e n t _ e n c o d i n g ? c l i e n t _ e n c o d i n g : _ ( " ( n o n e ) " ) ) ;
+ p r i n t f ( _ ( " S e r v e r E n c o d i n g : % s \ n " ) , s e r v e r _ e n c o d i n g ? s e r v e r _ e n c o d i n g : _ ( " ( n o n e ) " ) ) ;
+ p r i n t f ( _ ( " S e s s i o n U s e r : % s \ n " ) , s e s s i o n _ u s e r ? s e s s i o n _ u s e r : _ ( " ( n o n e ) " ) ) ;
+ p r i n t f ( _ ( " B a c k e n d P I D : % d \ n " ) , b a c k e n d _ p i d ) ;
+ }
p r i n t S S L I n f o ( ) ;
p r i n t G S S I n f o ( ) ;
}
d i f f - - g i t a / s r c / b i n / p s q l / h e l p . c b / s r c / b i n / p s q l / h e l p . c
i n d e x 6 f 5 8 a 1 1 0 7 4 . . 9 5 5 6 7 5 e 3 e 1 1 0 0 6 4 4
- - - a / s r c / b i n / p s q l / h e l p . c
+ + + b / s r c / b i n / p s q l / h e l p . c
@ @ - 3 1 0 , 7 + 3 1 0 , 7 @ @ s l a s h U s a g e ( u n s i g n e d s h o r t i n t p a g e r )
e l s e
H E L P 0 ( " \ \ c [ o n n e c t ] { [ D B N A M E | - U S E R | - H O S T | - P O R T | - ] | c o n n i n f o } \ n "
" c o n n e c t t o n e w d a t a b a s e ( c u r r e n t l y n o c o n n e c t i o n ) \ n " ) ;
- H E L P 0 ( " \ \ c o n n i n f o d i s p l a y i n f o r m a t i o n a b o u t c u r r e n t c o n n e c t i o n \ n " ) ;
+ H E L P 0 ( " \ \ c o n n i n f o [ + ] d i s p l a y i n f o r m a t i o n a b o u t c u r r e n t c o n n e c t i o n \ n " ) ;
H E L P 0 ( " \ \ e n c o d i n g [ E N C O D I N G ] s h o w o r s e t c l i e n t e n c o d i n g \ n " ) ;
H E L P 0 ( " \ \ p a s s w o r d [ U S E R N A M E ] s e c u r e l y c h a n g e t h e p a s s w o r d f o r a u s e r \ n " ) ;
H E L P 0 ( " \ n " ) ;
