Re: psql schema permissions - Mailing list pgsql-patches

Dennis Bjorklund wrote:
> There is no way to show schema permissions in psql.
>
> The alternatives are:
>
>   1) A new \dpn command
>
>   2) Extend \dp to also show rights for schemas, but then we need
>      an extra column to describe what kind of object it is, and it
>      would mess up the pattern stuff.
>
>   3) Do nothing and let people who want to see schema permissions
>      query the system table (select * from pg_namespace).
>
>   4) Since \dn only shows the name and the owner we can simply
>      add a permission column.
>
>   5) Some better way that is unknow to me
>
> I've attached a patch for number 4) which I think is the best. Well, 5) is
> better, but also harder to implement.

I have applied a modified version of your patch, attached.  It makes
\dn+ show schema permissions and descriptions.  (Idea from Tom.)

I don't think \dp makes sense because it is for data containers, not for
something like schemas.  In fact the big issue is that \dp with no arg
would make no sense if it displayed schemas along with tables/sequences:

       Access privileges for database "test"
     Schema | Name  | Type  | Access privileges
    --------+-------+-------+-------------------
     public | test  | table |
     public | test2 | table |
     public | test3 | table |
    (3 rows)

It seems much more logical to add the info to \dn+:

    test=> \dn
            List of schemas
            Name        |  Owner
    --------------------+----------
     information_schema | postgres
     pg_catalog         | postgres
     pg_toast           | postgres
     public             | postgres
    (4 rows)

    test=> \dn+
                                                List of schemas
            Name        |  Owner   |          Access privileges          |           Description
    --------------------+----------+-------------------------------------+----------------------------------
     information_schema | postgres | {postgres=UC/postgres,=U/postgres}  |
     pg_catalog         | postgres | {postgres=UC/postgres,=U/postgres}  | System catalog schema
     pg_toast           | postgres |                                     | Reserved schema for TOAST tables
     public             | postgres | {postgres=UC/postgres,=UC/postgres} | I am public
    (4 rows)

A little wide, but I don't see a better solution for this.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.117
diff -c -c -r1.117 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml    12 Jul 2004 20:41:08 -0000    1.117
--- doc/src/sgml/ref/psql-ref.sgml    13 Jul 2004 16:42:35 -0000
***************
*** 990,996 ****
          Lists all available schemas (namespaces). If <replaceable
          class="parameter">pattern</replaceable> (a regular expression)
          is specified, only schemas whose names match the pattern are listed.
!         Non-local temporary schemas are suppressed.
          </para>
          </listitem>
        </varlistentry>
--- 990,998 ----
          Lists all available schemas (namespaces). If <replaceable
          class="parameter">pattern</replaceable> (a regular expression)
          is specified, only schemas whose names match the pattern are listed.
!         Non-local temporary schemas are suppressed.  If <literal>+</literal>
!         is appended to the command name, each object is listed with its associated
!         permissions and description, if any.
          </para>
          </listitem>
        </varlistentry>
Index: src/bin/psql/command.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/command.c,v
retrieving revision 1.120
diff -c -c -r1.120 command.c
*** src/bin/psql/command.c    11 Jul 2004 21:34:03 -0000    1.120
--- src/bin/psql/command.c    13 Jul 2004 16:42:36 -0000
***************
*** 326,332 ****
                  success = do_lo_list();
                  break;
              case 'n':
!                 success = listSchemas(pattern);
                  break;
              case 'o':
                  success = describeOperators(pattern);
--- 326,332 ----
                  success = do_lo_list();
                  break;
              case 'n':
!                 success = listSchemas(pattern, show_verbose);
                  break;
              case 'o':
                  success = describeOperators(pattern);
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.101
diff -c -c -r1.101 describe.c
*** src/bin/psql/describe.c    13 Jul 2004 02:46:21 -0000    1.101
--- src/bin/psql/describe.c    13 Jul 2004 16:42:39 -0000
***************
*** 1693,1699 ****
   * Describes schemas (namespaces)
   */
  bool
! listSchemas(const char *pattern)
  {
      PQExpBufferData buf;
      PGresult   *res;
--- 1693,1699 ----
   * Describes schemas (namespaces)
   */
  bool
! listSchemas(const char *pattern, bool verbose)
  {
      PQExpBufferData buf;
      PGresult   *res;
***************
*** 1702,1714 ****
      initPQExpBuffer(&buf);
      printfPQExpBuffer(&buf,
          "SELECT n.nspname AS \"%s\",\n"
!         "       u.usename AS \"%s\"\n"
!         "FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
          "       ON n.nspowner=u.usesysid\n"
          "WHERE    (n.nspname NOT LIKE 'pg\\\\_temp\\\\_%%' OR\n"
!         "         n.nspname = (pg_catalog.current_schemas(true))[1])\n",    /* temp schema is first */
!                       _("Name"),
!                       _("Owner"));
      processNamePattern(&buf, pattern, true, false,
                         NULL, "n.nspname", NULL,
                         NULL);
--- 1702,1722 ----
      initPQExpBuffer(&buf);
      printfPQExpBuffer(&buf,
          "SELECT n.nspname AS \"%s\",\n"
!         "       u.usename AS \"%s\"",
!         _("Name"), _("Owner"));
!
!     if (verbose)
!         appendPQExpBuffer(&buf,
!             ",\n  n.nspacl as \"%s\","
!             "  pg_catalog.obj_description(n.oid, 'pg_namespace') as \"%s\"",
!             _("Access privileges"), _("Description"));
!
!     appendPQExpBuffer(&buf,
!         "\nFROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
          "       ON n.nspowner=u.usesysid\n"
          "WHERE    (n.nspname NOT LIKE 'pg\\\\_temp\\\\_%%' OR\n"
!         "         n.nspname = (pg_catalog.current_schemas(true))[1])\n");    /* temp schema is first */
!
      processNamePattern(&buf, pattern, true, false,
                         NULL, "n.nspname", NULL,
                         NULL);
Index: src/bin/psql/describe.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.h,v
retrieving revision 1.24
diff -c -c -r1.24 describe.h
*** src/bin/psql/describe.h    18 Jun 2004 06:14:04 -0000    1.24
--- src/bin/psql/describe.h    13 Jul 2004 16:42:39 -0000
***************
*** 56,62 ****
  bool        listCasts(const char *pattern);

  /* \dn */
! bool        listSchemas(const char *pattern);


  #endif   /* DESCRIBE_H */
--- 56,62 ----
  bool        listCasts(const char *pattern);

  /* \dn */
! bool        listSchemas(const char *pattern, bool verbose);


  #endif   /* DESCRIBE_H */
Index: src/bin/psql/help.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/help.c,v
retrieving revision 1.88
diff -c -c -r1.88 help.c
*** src/bin/psql/help.c    18 Jun 2004 06:14:04 -0000    1.88
--- src/bin/psql/help.c    13 Jul 2004 16:42:40 -0000
***************
*** 218,224 ****
      fprintf(output, _("  \\dD [PATTERN]  list domains\n"));
      fprintf(output, _("  \\df [PATTERN]  list functions (add \"+\" for more detail)\n"));
      fprintf(output, _("  \\dg [PATTERN]  list groups\n"));
!     fprintf(output, _("  \\dn [PATTERN]  list schemas\n"));
      fprintf(output, _("  \\do [NAME]     list operators\n"));
      fprintf(output, _("  \\dl            list large objects, same as \\lo_list\n"));
      fprintf(output, _("  \\dp [PATTERN]  list table, view and sequence access privileges\n"));
--- 218,224 ----
      fprintf(output, _("  \\dD [PATTERN]  list domains\n"));
      fprintf(output, _("  \\df [PATTERN]  list functions (add \"+\" for more detail)\n"));
      fprintf(output, _("  \\dg [PATTERN]  list groups\n"));
!     fprintf(output, _("  \\dn [PATTERN]  list schemas (add \"+\" for more detail)\n"));
      fprintf(output, _("  \\do [NAME]     list operators\n"));
      fprintf(output, _("  \\dl            list large objects, same as \\lo_list\n"));
      fprintf(output, _("  \\dp [PATTERN]  list table, view and sequence access privileges\n"));