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
  [EMAIL PROTECTED]               |  (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"));
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to