\du in psql patchp 
Author Message
 \du in psql patchp

--ELM989427470-8228-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

Patch applied.  I removed the reference to asprintf() and changed it to
a string constant.  I have also added documentation changes.  Patch
attached.

The new feature looks like:

        test=> \du
                        List of Users
         User Name | User ID |       Attributes      
        -----------+---------+------------------------
         demouser2 | 140     | create DB
         postgres  | 139     | create user, create DB

Very nice.

Quote:
> Hi everyone,

> I quickly put together a patch to psql that allows one to view current
> system users. The code is very simple, but it seems to work.

> The usage is as follows:

> database=# \du [username]

> I apologize in advance for my lack of C skills, and I will gladly
> accept any criticism or advice. I'm hoping I can figure out a way
> to write an equivalent for groups, once I determine how to
> get around the grolist integer[] column in pg_group.

> Please take a look at the patch and see if it's worth anything.

> Thanks

> Alex

>       Alex G. Perel  -=-  AP5081

>                play  -=-  work                      

> Disturbed Networks - Powered exclusively by FreeBSD
> == The Power to Serve -=- http://www.***.com/ ;  

Content-Description:

[ Attachment, skipping... ]

--
  Bruce Momjian                        |   http://www.***.com/

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

--ELM989427470-8228-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename="/bjm/diff"

Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.50
diff -c -r1.50 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml      2001/05/07 19:31:33     1.50
--- doc/src/sgml/ref/psql-ref.sgml      2001/05/09 16:54:45
***************
*** 495,500 ****
--- 495,510 ----

        <varlistentry>
+         <term><literal>\du [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+         <listitem>
+         <para>
+         Lists all configured users or only those that match <replaceable class="parameter">pattern</replaceable>.
+         </para>
+         </listitem>
+       </varlistentry>
+
+
+       <varlistentry>
          <term><literal>\edit</literal> (or <literal>\e</literal>) [ <replaceable class="parameter">filename</replaceable> ]</term>

          <listitem>
Index: src/bin/psql/command.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.51
diff -c -r1.51 command.c
*** src/bin/psql/command.c      2001/05/07 19:31:33     1.51
--- src/bin/psql/command.c      2001/05/09 16:54:56
***************
*** 357,362 ****
--- 357,365 ----
                        case 'S':
                                success = listTables(&cmd[1], name, show_verbose);
                                break;
+                       case 'u':
+                               success = describeUsers(name);
+                               break;
                        default:
                                status = CMD_UNKNOWN;
                }
Index: src/bin/psql/describe.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.28
diff -c -r1.28 describe.c
*** src/bin/psql/describe.c     2001/03/22 04:00:19     1.28
--- src/bin/psql/describe.c     2001/05/09 16:54:59
***************
*** 890,895 ****
--- 890,992 ----
  }

+ /*
+  * describeUsers()
+  *
+  * \du [user]
+  *
+  * Describes users, possibly based on a simplistic prefix search on the
+  * argument.
+  */
+
+ bool
+ describeUsers (const char *name)
+ {
+       char            buf[384 + REGEXP_CUTOFF];
+       PGresult   *res;
+       printTableOpt myopt = pset.popt.topt;
+       int             i;
+       char       *title;
+     const char *headers[4];
+       char      **cells       = NULL;
+       unsigned int cols;
+      
+       /*
+        * All we want to know is the user names and permissions
+        * for the system.
+        */
+
+       title = "List of Users";
+
+       cols = 0;
+       headers[cols++] = "User Name";
+       headers[cols++] = "User ID";
+       headers[cols++] = "Attributes";
+       headers[cols] = NULL;
+
+       strcpy(buf,
+                  "SELECT u.usename AS \"User Name\"\n
+                  , u.usesysid AS \"User ID\"\n
+                  , u.usesuper AS \"Super User\"\n
+                  , u.usecreatedb AS \"Create DB\"\n
+               FROM pg_user u\n");
+       if (name)
+       {
+               strcat(buf, "   WHERE u.usename ~ '^");
+               strncat(buf, name, REGEXP_CUTOFF);
+               strcat(buf, "'\n");
+       }
+       strcat(buf, "ORDER BY \"User Name\"\n");
+
+       res = PSQLexec(buf);
+       if (!res)
+               return false;
+
+       cells = xmalloc((PQntuples(res) * cols + 1) * sizeof(*cells));
+       cells[PQntuples(res) * cols] = NULL;
+
+       for (i = 0; i < PQntuples(res); i++)
+       {
+               char createuser[2] = "";
+               char createdb[2]  = "";
+
+               /* Name */
+               cells[i * cols + 0] = PQgetvalue(res, i, 0);
+
+               /* ID */
+               cells[i * cols + 1] = PQgetvalue(res, i, 1);
+
+               /* Super */
+               strcpy(createuser, PQgetvalue(res, i, 2));
+
+               /* Create DB */
+               strcpy(createdb, PQgetvalue(res, i, 3));
+
+               cells[i * cols + 2] = xmalloc((strlen("create user, create DB") * sizeof(char)) + 1);
+               strcpy(cells[i * cols + 2], "");
+              
+               if (strcmp(createuser, "t") == 0)
+                       strcat(cells[i * cols + 2], "create user");
+              
+               if (strcmp(createdb, "t") == 0) {
+                       if (strcmp(createuser, "t") == 0)
+                               strcat(cells[i * cols + 2], ", ");
+                       strcat(cells[i * cols + 2], "create DB");
+               }
+       }          
+
+       printTable(title, headers,
+                          (const char **) cells,
+                          NULL,
+                          "lll", &myopt, pset.queryFout);
+
+       /* clean up */
+       free(cells);
+
+       PQclear(res);
+       return true;
+ }
+

  /*
   * listTables()
Index: src/bin/psql/describe.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/psql/describe.h,v
retrieving revision 1.9
diff -c -r1.9 describe.h
*** src/bin/psql/describe.h     2000/04/12 17:16:22     1.9
--- src/bin/psql/describe.h     2001/05/09 16:54:59
***************
*** 22,27 ****
--- 22,30 ----
  /* \do */
  bool          describeOperators(const char *name);

+ /* \du */
+ bool          describeUsers(const char *name);
+
  /* \z (or \dp) */
  bool          permissionsList(const char *name);

--ELM989427470-8228-0_
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

--ELM989427470-8228-0_--



Mon, 27 Oct 2003 01:01:15 GMT
 \du in psql patchp

Thanks for the catch, I'll keep an eye on this in the future.

(my C skills aren't what I'd like them to be...)

Quote:
> Bruce Momjian writes:

> > Patch applied.  I removed the reference to asprintf() and changed it to
> > a string constant.  I have also added documentation changes.  Patch
> > attached.

> I think there's a memory leak, count your xmalloc's.  Also, perhaps the
> user ids should be right-aligned (printTable, 5th argument).

> > Very nice.

> Yes.

> --


Alex Perel
Lead Database Analyst
T. 416.341.8950 ext. 238
F. 416.341.8959

www.verticalscope.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Mon, 27 Oct 2003 02:28:28 GMT
 \du in psql patchp

Quote:
Bruce Momjian writes:
> Patch applied.  I removed the reference to asprintf() and changed it to
> a string constant.  I have also added documentation changes.  Patch
> attached.

I think there's a memory leak, count your xmalloc's.  Also, perhaps the
user ids should be right-aligned (printTable, 5th argument).

Quote:
> Very nice.

Yes.

--

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Mon, 27 Oct 2003 02:29:31 GMT
 \du in psql patchp

Quote:

> Thanks for the catch, I'll keep an eye on this in the future.

> (my C skills aren't what I'd like them to be...)

Hey, but a nice feature.

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------



Mon, 27 Oct 2003 02:34:05 GMT
 \du in psql patchp

Quote:
> Bruce Momjian writes:

> > Patch applied.  I removed the reference to asprintf() and changed it to
> > a string constant.  I have also added documentation changes.  Patch
> > attached.

> I think there's a memory leak, count your xmalloc's.  Also, perhaps the

I thought I had too many mallocs, but when I started looking, I couldn't
find the second one anymore.  I see it now, and fixed.  Looped through
cell and free'ed:

    for (i = 0; i < PQntuples(res); i++)
        free(cells[i * cols + 2]);
    free(cells);

Quote:
> user ids should be right-aligned (printTable, 5th argument).

Got it:

        test=> \du
                        List of Users
         User Name | User ID |       Attributes      
        -----------+---------+------------------------
         demouser2 |     140 | create DB
         postgres  |     139 | create user, create DB

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Mon, 27 Oct 2003 02:39:47 GMT
 \du in psql patchp

Thanks. :)

On a related note, do either of you know a good way to join the contents
of the grolist[] array in pg_group with pg_user's? I'd like to add a similar
\dg function, because I think that would be even more useful.

Thanks

Alex

Quote:

> > Thanks for the catch, I'll keep an eye on this in the future.

> > (my C skills aren't what I'd like them to be...)

> Hey, but a nice feature.

> --
>   Bruce Momjian                        |  http://candle.pha.pa.us

>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Alex Perel
Lead Database Analyst
T. 416.341.8950 ext. 238
F. 416.341.8959

www.verticalscope.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Mon, 27 Oct 2003 02:58:05 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. \du in psql patchp

2. \du in psql patchp

3. \du undocumented in psql help

4. psql:relocation error:psql:unfefined symbol: PQgetssl

5. How do I uninstall P.SQL 2000 SP2 and replace it with P.SQL 7

6. Upgarding P.SQL 7 to P.SQL 2000

7. pgsql/src/bin psql/Tag: psql/REL7_2_STABLE psq ...

8. Migrating from PSQL 7 to PSQL 2000

9. Using Psql 7 and PSQL 2000 together

10. psql: relocation error: psql: undefined symbol: PQgetssl

11. NW server abend - P.SQL 2000 SP3 client to P.SQL 2000 SP2 server

12. Eviter la modification du curseur de la souris lors d'une requete


 
Powered by phpBB® Forum Software