Re: Patch for %Allow per-database permissions to be set via - Mailing list pgsql-patches

Documentation added, patch attached and applied.  Thanks.

---------------------------------------------------------------------------

Gevik Babakhani wrote:
> This patch implements the TODO Item: "%Allow per-database permissions to
> be set via GRANT"
>
> Implementation details:
>
> 1. A privilege ACL_CONNECT has been added to the ACL bits
>
> 2. The ACL_CONNECT can be recognized by character "c" in
> pg_database/dataacl
>
> 3. The patch implements:
>
> GRANT CONNECTION ON DATABASE mydatabase TO myuser
>
> REVOKE CONNECTION ON DATABASE mydatabase FROM myuser
>
> 4. The initial condition ACL=NULL is treated as default
> ACL=ACL_CREATE_TEMP | ACL_CONNECT providing backward compatibility with
> the current pg_hba.conf
> Notes:
>
> As discussed :
> A database owner WITHOUT SUPERUSER privileges can lock himself out from
> connecting to his database. Try:
>
> #psql -U user1 -d user1
> Revoke connection on database user1 from public;
> Revoke connection on database user1 from user1;
>
> In this case no warning will be shown to the user informing he/she is
> possibly locked out. This behavior is discussed in the hackers list.
>
> The solution for a possible lockout would be to connect as a superuser
> and GRANT CONNECTION ON DATABASE user1 TO <anyuser or public>
>
> The implementation is best used for systems not wishing to change
> pg_hba.conf frequently. In that case a simple host record can be added
> to pg_hba.conf, providing from witch network the server is allowed to be
> connected from and the database connection privilege can be granted or
> revoked from withing SQL.
>
> e.g.
> CREATE USER user1 LOGIN;
> CREATE USER user2 LOGIN;
> CREATE DATABASE user1 OWNER user1;
> REVOKE CONNECTION ON DATABASE user1 FROM PUBLIC;
> GRANT CONNECTION,CREATE ON DATABASE user1 TO user2;
> SELECT datname,datacl FROM pg_catalog.pg_database;
>
> The patch can be downloaded from:
>
> http://www.xs4all.nl/~gevik/patch/patch-0.7.diff
>
> Many thanks to Tom Lane and Alvaro Herrera for their insight and
> coaching.
>
> Regards,
> Gevik.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/client-auth.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/client-auth.sgml,v
retrieving revision 1.87
diff -c -c -r1.87 client-auth.sgml
*** doc/src/sgml/client-auth.sgml    10 Mar 2006 19:10:47 -0000    1.87
--- doc/src/sgml/client-auth.sgml    30 Apr 2006 01:42:16 -0000
***************
*** 206,211 ****
--- 206,213 ----
         Multiple user names can be supplied by separating them with commas.
         A separate file containing user names can be specified by preceding the
         file name with <literal>@</>.
+        User and group connectivity can also be restricted by <command>GRANT
+        CONNECTION ON DATABASE</>.
        </para>
       </listitem>
      </varlistentry>
Index: doc/src/sgml/ref/grant.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.52
diff -c -c -r1.52 grant.sgml
*** doc/src/sgml/ref/grant.sgml    14 Feb 2006 03:32:14 -0000    1.52
--- doc/src/sgml/ref/grant.sgml    30 Apr 2006 01:42:16 -0000
***************
*** 30,36 ****
      ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
      TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable
class="PARAMETER">groupname</replaceable>| PUBLIC } [, ...] [ WITH GRANT OPTION ] 

! GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
      ON DATABASE <replaceable>dbname</replaceable> [, ...]
      TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable
class="PARAMETER">groupname</replaceable>| PUBLIC } [, ...] [ WITH GRANT OPTION ] 

--- 30,36 ----
      ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
      TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable
class="PARAMETER">groupname</replaceable>| PUBLIC } [, ...] [ WITH GRANT OPTION ] 

! GRANT { { CREATE | TEMPORARY | TEMP | CONNECTION } [,...] | ALL [ PRIVILEGES ] }
      ON DATABASE <replaceable>dbname</replaceable> [, ...]
      TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable
class="PARAMETER">groupname</replaceable>| PUBLIC } [, ...] [ WITH GRANT OPTION ] 

***************
*** 230,235 ****
--- 230,247 ----
      </varlistentry>

      <varlistentry>
+      <term>CONNECTION</term>
+      <listitem>
+       <para>
+        Allows the ability to connect to the specified database.
+        By default, Grant permissions allow users to connect to any database,
+        though <filename>pg_hba.conf</> can add additional connection
+        restrictions.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term>TEMPORARY</term>
       <term>TEMP</term>
       <listitem>
***************
*** 417,422 ****
--- 429,435 ----
                    X -- EXECUTE
                    U -- USAGE
                    C -- CREATE
+                   c -- CONNECTION
                    T -- TEMPORARY
              arwdRxt -- ALL PRIVILEGES (for tables)
                    * -- grant option for preceding privilege
Index: doc/src/sgml/ref/revoke.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v
retrieving revision 1.36
diff -c -c -r1.36 revoke.sgml
*** doc/src/sgml/ref/revoke.sgml    21 Jan 2006 02:16:18 -0000    1.36
--- doc/src/sgml/ref/revoke.sgml    30 Apr 2006 01:42:16 -0000
***************
*** 35,41 ****
      [ CASCADE | RESTRICT ]

  REVOKE [ GRANT OPTION FOR ]
!     { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
      ON DATABASE <replaceable>dbname</replaceable> [, ...]
      FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable
class="PARAMETER">groupname</replaceable>| PUBLIC } [, ...] 
      [ CASCADE | RESTRICT ]
--- 35,41 ----
      [ CASCADE | RESTRICT ]

  REVOKE [ GRANT OPTION FOR ]
!     { { CREATE | TEMPORARY | TEMP | CONNECTION } [,...] | ALL [ PRIVILEGES ] }
      ON DATABASE <replaceable>dbname</replaceable> [, ...]
      FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable
class="PARAMETER">groupname</replaceable>| PUBLIC } [, ...] 
      [ CASCADE | RESTRICT ]
Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.125
diff -c -c -r1.125 aclchk.c
*** src/backend/catalog/aclchk.c    5 Mar 2006 15:58:22 -0000    1.125
--- src/backend/catalog/aclchk.c    30 Apr 2006 01:42:17 -0000
***************
*** 1368,1373 ****
--- 1368,1375 ----
          return ACL_CREATE_TEMP;
      if (strcmp(privname, "temp") == 0)
          return ACL_CREATE_TEMP;
+     if (strcmp(privname, "connection") == 0)
+         return ACL_CONNECT;
      ereport(ERROR,
              (errcode(ERRCODE_SYNTAX_ERROR),
               errmsg("unrecognized privilege type \"%s\"", privname)));
***************
*** 1401,1406 ****
--- 1403,1410 ----
              return "CREATE";
          case ACL_CREATE_TEMP:
              return "TEMP";
+         case ACL_CONNECT:
+             return "CONNECTION";
          default:
              elog(ERROR, "unrecognized privilege: %d", (int) privilege);
      }
Index: src/backend/utils/adt/acl.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/acl.c,v
retrieving revision 1.131
diff -c -c -r1.131 acl.c
*** src/backend/utils/adt/acl.c    5 Mar 2006 15:58:40 -0000    1.131
--- src/backend/utils/adt/acl.c    30 Apr 2006 01:42:18 -0000
***************
*** 287,292 ****
--- 287,295 ----
              case ACL_CREATE_TEMP_CHR:
                  read = ACL_CREATE_TEMP;
                  break;
+             case ACL_CONNECT_CHR:
+                 read = ACL_CONNECT;
+                 break;
              default:
                  ereport(ERROR,
                          (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
***************
*** 550,556 ****
              owner_default = ACL_ALL_RIGHTS_SEQUENCE;
              break;
          case ACL_OBJECT_DATABASE:
!             world_default = ACL_CREATE_TEMP;    /* not NO_RIGHTS! */
              owner_default = ACL_ALL_RIGHTS_DATABASE;
              break;
          case ACL_OBJECT_FUNCTION:
--- 553,559 ----
              owner_default = ACL_ALL_RIGHTS_SEQUENCE;
              break;
          case ACL_OBJECT_DATABASE:
!             world_default = ACL_CREATE_TEMP | ACL_CONNECT;    /* not NO_RIGHTS! */
              owner_default = ACL_ALL_RIGHTS_DATABASE;
              break;
          case ACL_OBJECT_FUNCTION:
Index: src/backend/utils/init/postinit.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/init/postinit.c,v
retrieving revision 1.162
diff -c -c -r1.162 postinit.c
*** src/backend/utils/init/postinit.c    29 Mar 2006 21:17:39 -0000    1.162
--- src/backend/utils/init/postinit.c    30 Apr 2006 01:42:19 -0000
***************
*** 51,57 ****


  static bool FindMyDatabase(const char *name, Oid *db_id, Oid *db_tablespace);
! static void ReverifyMyDatabase(const char *name);
  static void InitCommunication(void);
  static void ShutdownPostgres(int code, Datum arg);
  static bool ThereIsAtLeastOneRole(void);
--- 51,57 ----


  static bool FindMyDatabase(const char *name, Oid *db_id, Oid *db_tablespace);
! static void ReverifyMyDatabase(const char *name, const char *user_name);
  static void InitCommunication(void);
  static void ShutdownPostgres(int code, Datum arg);
  static bool ThereIsAtLeastOneRole(void);
***************
*** 130,137 ****
   * during session startup, this place is also fitting to set up any
   * database-specific configuration variables.
   */
  static void
! ReverifyMyDatabase(const char *name)
  {
      Relation    pgdbrel;
      SysScanDesc pgdbscan;
--- 130,138 ----
   * during session startup, this place is also fitting to set up any
   * database-specific configuration variables.
   */
+
  static void
! ReverifyMyDatabase(const char *name, const char *user_name)
  {
      Relation    pgdbrel;
      SysScanDesc pgdbscan;
***************
*** 211,216 ****
--- 212,234 ----
                      (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
                       errmsg("too many connections for database \"%s\"",
                              name)));
+
+         /*
+          * Checking for privilege to connect to the database
+          * We want to bypass the test if we are running in bootstrap mode
+          */
+         if (!IsBootstrapProcessingMode())
+         {
+                 if(pg_database_aclcheck(MyDatabaseId,GetUserId()
+                     ,ACL_CONNECT) != ACLCHECK_OK )
+                 {
+                     ereport(FATAL,
+                             (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                  errmsg("couldn't connect to database %s", NameStr(dbform->datname)),
+                                  errdetail("User %s doesn't have the CONNECTION privilege for database %s.",
+                                 user_name, NameStr(dbform->datname))));
+                 }
+         }
      }

      /*
***************
*** 487,493 ****
       * superuser, so the above stuff has to happen first.)
       */
      if (!bootstrap)
!         ReverifyMyDatabase(dbname);

      /*
       * Final phase of relation cache startup: write a new cache file if
--- 505,511 ----
       * superuser, so the above stuff has to happen first.)
       */
      if (!bootstrap)
!         ReverifyMyDatabase(dbname,username);

      /*
       * Final phase of relation cache startup: write a new cache file if
Index: src/include/catalog/catversion.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/catversion.h,v
retrieving revision 1.326
diff -c -c -r1.326 catversion.h
*** src/include/catalog/catversion.h    26 Apr 2006 22:33:13 -0000    1.326
--- src/include/catalog/catversion.h    30 Apr 2006 01:42:19 -0000
***************
*** 53,58 ****
   */

  /*                            yyyymmddN */
! #define CATALOG_VERSION_NO    200604262

  #endif
--- 53,58 ----
   */

  /*                            yyyymmddN */
! #define CATALOG_VERSION_NO    200604291

  #endif
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.308
diff -c -c -r1.308 parsenodes.h
*** src/include/nodes/parsenodes.h    27 Apr 2006 00:33:46 -0000    1.308
--- src/include/nodes/parsenodes.h    30 Apr 2006 01:42:20 -0000
***************
*** 57,63 ****
  #define ACL_USAGE        (1<<8)    /* for languages and namespaces */
  #define ACL_CREATE        (1<<9)    /* for namespaces and databases */
  #define ACL_CREATE_TEMP (1<<10) /* for databases */
! #define N_ACL_RIGHTS    11        /* 1 plus the last 1<<x */
  #define ACL_NO_RIGHTS    0
  /* Currently, SELECT ... FOR UPDATE/FOR SHARE requires UPDATE privileges */
  #define ACL_SELECT_FOR_UPDATE    ACL_UPDATE
--- 57,64 ----
  #define ACL_USAGE        (1<<8)    /* for languages and namespaces */
  #define ACL_CREATE        (1<<9)    /* for namespaces and databases */
  #define ACL_CREATE_TEMP (1<<10) /* for databases */
! #define ACL_CONNECT    (1<<11) /* for database connection privilege */
! #define N_ACL_RIGHTS    12        /* 1 plus the last 1<<x */
  #define ACL_NO_RIGHTS    0
  /* Currently, SELECT ... FOR UPDATE/FOR SHARE requires UPDATE privileges */
  #define ACL_SELECT_FOR_UPDATE    ACL_UPDATE
Index: src/include/utils/acl.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/acl.h,v
retrieving revision 1.93
diff -c -c -r1.93 acl.h
*** src/include/utils/acl.h    5 Mar 2006 15:59:06 -0000    1.93
--- src/include/utils/acl.h    30 Apr 2006 01:42:20 -0000
***************
*** 135,150 ****
  #define ACL_USAGE_CHR            'U'
  #define ACL_CREATE_CHR            'C'
  #define ACL_CREATE_TEMP_CHR        'T'

  /* string holding all privilege code chars, in order by bitmask position */
! #define ACL_ALL_RIGHTS_STR    "arwdRxtXUCT"

  /*
   * Bitmasks defining "all rights" for each supported object type
   */
  #define ACL_ALL_RIGHTS_RELATION
(ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_RULE|ACL_REFERENCES|ACL_TRIGGER)
  #define ACL_ALL_RIGHTS_SEQUENCE        (ACL_USAGE|ACL_SELECT|ACL_UPDATE)
! #define ACL_ALL_RIGHTS_DATABASE        (ACL_CREATE|ACL_CREATE_TEMP)
  #define ACL_ALL_RIGHTS_FUNCTION        (ACL_EXECUTE)
  #define ACL_ALL_RIGHTS_LANGUAGE        (ACL_USAGE)
  #define ACL_ALL_RIGHTS_NAMESPACE    (ACL_USAGE|ACL_CREATE)
--- 135,151 ----
  #define ACL_USAGE_CHR            'U'
  #define ACL_CREATE_CHR            'C'
  #define ACL_CREATE_TEMP_CHR        'T'
+ #define ACL_CONNECT_CHR            'c'

  /* string holding all privilege code chars, in order by bitmask position */
! #define ACL_ALL_RIGHTS_STR    "arwdRxtXUCTc"

  /*
   * Bitmasks defining "all rights" for each supported object type
   */
  #define ACL_ALL_RIGHTS_RELATION
(ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_RULE|ACL_REFERENCES|ACL_TRIGGER)
  #define ACL_ALL_RIGHTS_SEQUENCE        (ACL_USAGE|ACL_SELECT|ACL_UPDATE)
! #define ACL_ALL_RIGHTS_DATABASE        (ACL_CREATE|ACL_CREATE_TEMP|ACL_CONNECT )
  #define ACL_ALL_RIGHTS_FUNCTION        (ACL_EXECUTE)
  #define ACL_ALL_RIGHTS_LANGUAGE        (ACL_USAGE)
  #define ACL_ALL_RIGHTS_NAMESPACE    (ACL_USAGE|ACL_CREATE)