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 [EMAIL PROTECTED] 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)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Reply via email to