Thread: \du in psql patchp
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
veers@disturbed.net -=- aperel@verticalscope.com
play -=- work
Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=- http://www.freebsd.org/
Attachment
Alex Perel writes: > 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] Hi, we're currently in beta, but this one seems reasonable to add in 7.2. > 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. I think the pg_group table will be redesigned in 7.2 so it can be queried better. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Mon, 8 Jan 2001, Peter Eisentraut wrote:
> Alex Perel writes:
>
> > 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]
>
> Hi, we're currently in beta, but this one seems reasonable to add in 7.2.
Great, that's perfectly fine for me.
>
> > 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.
>
> I think the pg_group table will be redesigned in 7.2 so it can be queried
> better.
Excellent! Is there any way I can help out with that? Do you have any
pointers to where the relevant code is? I've tried looking but didn't
come up with anything.
Alex G. Perel -=- AP5081
veers@disturbed.net -=- aperel@verticalscope.com
play -=- work
Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=- http://www.freebsd.org/
I will keep it for 7.2. > 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 > veers@disturbed.net -=- aperel@verticalscope.com > play -=- work > > Disturbed Networks - Powered exclusively by FreeBSD > == The Power to Serve -=- http://www.freebsd.org/ Content-Description: [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
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.
> 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
> veers@disturbed.net -=- aperel@verticalscope.com
> play -=- work
>
> Disturbed Networks - Powered exclusively by FreeBSD
> == The Power to Serve -=- http://www.freebsd.org/
Content-Description:
[ Attachment, skipping... ]
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
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);
On Wed, 9 May 2001, Peter Eisentraut wrote: 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...) > 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. > > -- > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter > > Alex Perel Lead Database Analyst T. 416.341.8950 ext. 238 F. 416.341.8959 E. aperel@verticalscope.com www.verticalscope.com
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. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> On Wed, 9 May 2001, Peter Eisentraut wrote: > > 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 pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> 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);
> 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
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Wed, 9 May 2001, Bruce Momjian wrote: 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 > > On Wed, 9 May 2001, Peter Eisentraut wrote: > > > > 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 > pgman@candle.pha.pa.us | (610) 853-3000 > + 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 E. aperel@verticalscope.com www.verticalscope.com