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)