Create an ALTER DEFAULT PRIVILEGES command, which allows users to adjust

the privileges that will be applied to subsequently-created objects.

Such adjustments are always per owning role, and can be restricted to objects
created in particular schemas too.  A notable benefit is that users can
override the traditional default privilege settings, eg, the PUBLIC EXECUTE
privilege traditionally granted by default for functions.

Petr Jelinek

12 files changed, 370 insertions, 40 deletions

diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 789638fec45..ea314be0fa5 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/bin/pg_dump/common.c,v 1.107 2009/06/11 14:49:07 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/bin/pg_dump/common.c,v 1.108 2009/10/05 19:24:45 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -93,6 +93,7 @@ getSchemaData(int *numTablesPtr)
 	TSConfigInfo *cfginfo;
 	FdwInfo    *fdwinfo;
 	ForeignServerInfo *srvinfo;
+	DefaultACLInfo *daclinfo;
 	int			numNamespaces;
 	int			numAggregates;
 	int			numInherits;
@@ -108,6 +109,7 @@ getSchemaData(int *numTablesPtr)
 	int			numTSConfigs;
 	int			numForeignDataWrappers;
 	int			numForeignServers;
+	int			numDefaultACLs;
 
 	if (g_verbose)
 		write_msg(NULL, "reading schemas\n");
@@ -167,6 +169,10 @@ getSchemaData(int *numTablesPtr)
 	srvinfo = getForeignServers(&numForeignServers);
 
 	if (g_verbose)
+		write_msg(NULL, "reading default privileges\n");
+	daclinfo = getDefaultACLs(&numDefaultACLs);
+
+	if (g_verbose)
 		write_msg(NULL, "reading user-defined operator families\n");
 	opfinfo = getOpfamilies(&numOpfamilies);
 
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index 8037c1d7e16..a9a27625707 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.c,v 1.48 2009/08/04 21:56:08 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.c,v 1.49 2009/10/05 19:24:45 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -490,18 +490,22 @@ parsePGArray(const char *atext, char ***itemarray, int *nitems)
  *	acls: the ACL string fetched from the database
  *	owner: username of object owner (will be passed through fmtId); can be
  *		NULL or empty string to indicate "no owner known"
+ *	prefix: string to prefix to each generated command; typically empty
  *	remoteVersion: version of database
  *
  * Returns TRUE if okay, FALSE if could not parse the acl string.
  * The resulting commands (if any) are appended to the contents of 'sql'.
  *
+ * Note: when processing a default ACL, prefix is "ALTER DEFAULT PRIVILEGES "
+ * or something similar, and name is an empty string.
+ *
  * Note: beware of passing a fmtId() result directly as 'name' or 'subname',
  * since this routine uses fmtId() internally.
  */
 bool
 buildACLCommands(const char *name, const char *subname,
 				 const char *type, const char *acls, const char *owner,
-				 int remoteVersion,
+				 const char *prefix, int remoteVersion,
 				 PQExpBuffer sql)
 {
 	char	  **aclitems;
@@ -549,7 +553,7 @@ buildACLCommands(const char *name, const char *subname,
 	 * wire-in knowledge about the default public privileges for different
 	 * kinds of objects.
 	 */
-	appendPQExpBuffer(firstsql, "REVOKE ALL");
+	appendPQExpBuffer(firstsql, "%sREVOKE ALL", prefix);
 	if (subname)
 		appendPQExpBuffer(firstsql, "(%s)", subname);
 	appendPQExpBuffer(firstsql, " ON %s %s FROM PUBLIC;\n", type, name);
@@ -564,8 +568,8 @@ buildACLCommands(const char *name, const char *subname,
 	if (remoteVersion < 80200 && strcmp(type, "DATABASE") == 0)
 	{
 		/* database CONNECT priv didn't exist before 8.2 */
-		appendPQExpBuffer(firstsql, "GRANT CONNECT ON %s %s TO PUBLIC;\n",
-						  type, name);
+		appendPQExpBuffer(firstsql, "%sGRANT CONNECT ON %s %s TO PUBLIC;\n",
+						  prefix, type, name);
 	}
 
 	/* Scan individual ACL items */
@@ -594,20 +598,20 @@ buildACLCommands(const char *name, const char *subname,
 					? strcmp(privswgo->data, "ALL") != 0
 					: strcmp(privs->data, "ALL") != 0)
 				{
-					appendPQExpBuffer(firstsql, "REVOKE ALL");
+					appendPQExpBuffer(firstsql, "%sREVOKE ALL", prefix);
 					if (subname)
 						appendPQExpBuffer(firstsql, "(%s)", subname);
 					appendPQExpBuffer(firstsql, " ON %s %s FROM %s;\n",
 									  type, name, fmtId(grantee->data));
 					if (privs->len > 0)
 						appendPQExpBuffer(firstsql,
-										  "GRANT %s ON %s %s TO %s;\n",
-										  privs->data, type, name,
+										  "%sGRANT %s ON %s %s TO %s;\n",
+										  prefix, privs->data, type, name,
 										  fmtId(grantee->data));
 					if (privswgo->len > 0)
 						appendPQExpBuffer(firstsql,
-							  "GRANT %s ON %s %s TO %s WITH GRANT OPTION;\n",
-										  privswgo->data, type, name,
+							  "%sGRANT %s ON %s %s TO %s WITH GRANT OPTION;\n",
+										  prefix, privswgo->data, type, name,
 										  fmtId(grantee->data));
 				}
 			}
@@ -623,8 +627,8 @@ buildACLCommands(const char *name, const char *subname,
 
 				if (privs->len > 0)
 				{
-					appendPQExpBuffer(secondsql, "GRANT %s ON %s %s TO ",
-									  privs->data, type, name);
+					appendPQExpBuffer(secondsql, "%sGRANT %s ON %s %s TO ",
+									  prefix, privs->data, type, name);
 					if (grantee->len == 0)
 						appendPQExpBuffer(secondsql, "PUBLIC;\n");
 					else if (strncmp(grantee->data, "group ",
@@ -636,8 +640,8 @@ buildACLCommands(const char *name, const char *subname,
 				}
 				if (privswgo->len > 0)
 				{
-					appendPQExpBuffer(secondsql, "GRANT %s ON %s %s TO ",
-									  privswgo->data, type, name);
+					appendPQExpBuffer(secondsql, "%sGRANT %s ON %s %s TO ",
+									  prefix, privswgo->data, type, name);
 					if (grantee->len == 0)
 						appendPQExpBuffer(secondsql, "PUBLIC");
 					else if (strncmp(grantee->data, "group ",
@@ -661,7 +665,7 @@ buildACLCommands(const char *name, const char *subname,
 	 */
 	if (!found_owner_privs && owner)
 	{
-		appendPQExpBuffer(firstsql, "REVOKE ALL");
+		appendPQExpBuffer(firstsql, "%sREVOKE ALL", prefix);
 		if (subname)
 			appendPQExpBuffer(firstsql, "(%s)", subname);
 		appendPQExpBuffer(firstsql, " ON %s %s FROM %s;\n",
@@ -683,6 +687,50 @@ buildACLCommands(const char *name, const char *subname,
 }
 
 /*
+ * Build ALTER DEFAULT PRIVILEGES command(s) for single pg_default_acl entry.
+ *
+ *	type: the object type (as seen in GRANT command)
+ *	nspname: schema name, or NULL for global default privileges
+ *	acls: the ACL string fetched from the database
+ *	owner: username of privileges owner (will be passed through fmtId)
+ *	remoteVersion: version of database
+ *
+ * Returns TRUE if okay, FALSE if could not parse the acl string.
+ * The resulting commands (if any) are appended to the contents of 'sql'.
+ */
+bool
+buildDefaultACLCommands(const char *type, const char *nspname,
+						const char *acls, const char *owner,
+						int remoteVersion,
+						PQExpBuffer sql)
+{
+	bool		result;
+	PQExpBuffer prefix;
+
+	prefix = createPQExpBuffer();
+
+	/*
+	 * We incorporate the target role directly into the command, rather than
+	 * playing around with SET ROLE or anything like that.  This is so that
+	 * a permissions error leads to nothing happening, rather than
+	 * changing default privileges for the wrong user.
+	 */
+	appendPQExpBuffer(prefix, "ALTER DEFAULT PRIVILEGES FOR ROLE %s ",
+					  fmtId(owner));
+	if (nspname)
+		appendPQExpBuffer(prefix, "IN SCHEMA %s ", fmtId(nspname));
+
+	result = buildACLCommands("", NULL,
+							  type, acls, owner,
+							  prefix->data, remoteVersion,
+							  sql);
+
+	destroyPQExpBuffer(prefix);
+
+	return result;
+}
+
+/*
  * This will parse an aclitem string, having the general form
  *		username=privilegecodes/grantor
  * or
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
index 3c56c3f9a4d..a5bfe1bcfda 100644
--- a/src/bin/pg_dump/dumputils.h
+++ b/src/bin/pg_dump/dumputils.h
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.h,v 1.25 2009/08/04 21:56:08 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.h,v 1.26 2009/10/05 19:24:45 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -34,8 +34,12 @@ extern int	parse_version(const char *versionString);
 extern bool parsePGArray(const char *atext, char ***itemarray, int *nitems);
 extern bool buildACLCommands(const char *name, const char *subname,
 				 const char *type, const char *acls, const char *owner,
-				 int remoteVersion,
+				 const char *prefix, int remoteVersion,
 				 PQExpBuffer sql);
+extern bool buildDefaultACLCommands(const char *type, const char *nspname,
+						const char *acls, const char *owner,
+						int remoteVersion,
+						PQExpBuffer sql);
 extern void processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
 					  const char *pattern,
 					  bool have_where, bool force_escape,
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 20bd3eb7eac..e15e4dbdb9e 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -15,7 +15,7 @@
  *
  *
  * IDENTIFICATION
- *		$PostgreSQL: pgsql/src/bin/pg_dump/pg_backup_archiver.c,v 1.175 2009/08/07 22:48:34 tgl Exp $
+ *		$PostgreSQL: pgsql/src/bin/pg_dump/pg_backup_archiver.c,v 1.176 2009/10/05 19:24:45 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2072,7 +2072,8 @@ ReadToc(ArchiveHandle *AH)
 			 * the entries into sections
 			 */
 			if (strcmp(te->desc, "COMMENT") == 0 ||
-				strcmp(te->desc, "ACL") == 0)
+				strcmp(te->desc, "ACL") == 0 ||
+				strcmp(te->desc, "DEFAULT ACL") == 0)
 				te->section = SECTION_NONE;
 			else if (strcmp(te->desc, "TABLE DATA") == 0 ||
 					 strcmp(te->desc, "BLOBS") == 0 ||
@@ -2227,7 +2228,8 @@ _tocEntryRequired(TocEntry *te, RestoreOptions *ropt, bool include_acls)
 		return 0;
 
 	/* If it's an ACL, maybe ignore it */
-	if ((!include_acls || ropt->aclsSkip) && strcmp(te->desc, "ACL") == 0)
+	if ((!include_acls || ropt->aclsSkip) &&
+		(strcmp(te->desc, "ACL") == 0 || strcmp(te->desc, "DEFAULT ACL") == 0))
 		return 0;
 
 	if (!ropt->create && strcmp(te->desc, "DATABASE") == 0)
@@ -2721,12 +2723,14 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat
 	/* ACLs are dumped only during acl pass */
 	if (acl_pass)
 	{
-		if (strcmp(te->desc, "ACL") != 0)
+		if (!(strcmp(te->desc, "ACL") == 0 ||
+			  strcmp(te->desc, "DEFAULT ACL") == 0))
 			return;
 	}
 	else
 	{
-		if (strcmp(te->desc, "ACL") == 0)
+		if (strcmp(te->desc, "ACL") == 0 ||
+			strcmp(te->desc, "DEFAULT ACL") == 0)
 			return;
 	}
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 34ebc27168d..d1715eccce8 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -12,7 +12,7 @@
  *	by PostgreSQL
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.548 2009/09/22 23:43:38 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.549 2009/10/05 19:24:45 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -34,6 +34,7 @@
 #include "access/sysattr.h"
 #include "catalog/pg_cast.h"
 #include "catalog/pg_class.h"
+#include "catalog/pg_default_acl.h"
 #include "catalog/pg_largeobject.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_trigger.h"
@@ -162,6 +163,7 @@ static void dumpForeignServer(Archive *fout, ForeignServerInfo *srvinfo);
 static void dumpUserMappings(Archive *fout, const char *target,
 				 const char *servername, const char *namespace,
 				 const char *owner, CatalogId catalogId, DumpId dumpId);
+static void dumpDefaultACL(Archive *fout, DefaultACLInfo *daclinfo);
 
 static void dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
 		const char *type, const char *name, const char *subname,
@@ -1050,6 +1052,23 @@ selectDumpableType(TypeInfo *tinfo)
 }
 
 /*
+ * selectDumpableDefaultACL: policy-setting subroutine
+ *		Mark a default ACL as to be dumped or not
+ *
+ * For per-schema default ACLs, dump if the schema is to be dumped.
+ * Otherwise dump if we are dumping "everything".  Note that dataOnly
+ * and aclsSkip are checked separately.
+ */
+static void
+selectDumpableDefaultACL(DefaultACLInfo *dinfo)
+{
+	if (dinfo->dobj.namespace)
+		dinfo->dobj.dump = dinfo->dobj.namespace->dobj.dump;
+	else
+		dinfo->dobj.dump = include_everything;
+}
+
+/*
  * selectDumpableObject: policy-setting subroutine
  *		Mark a generic dumpable object as to be dumped or not
  *
@@ -1779,7 +1798,7 @@ dumpDatabase(Archive *AH)
 		PQExpBuffer loFrozenQry = createPQExpBuffer();
 		PQExpBuffer loOutQry = createPQExpBuffer();
 		int			i_relfrozenxid;
-		
+
 		appendPQExpBuffer(loFrozenQry, "SELECT relfrozenxid\n"
 							"FROM pg_catalog.pg_class\n"
 							"WHERE oid = %d;\n",
@@ -1808,7 +1827,7 @@ dumpDatabase(Archive *AH)
 					 loOutQry->data, "", NULL,
 					 NULL, 0,
 					 NULL, NULL);
-						  
+
 		PQclear(lo_res);
 		destroyPQExpBuffer(loFrozenQry);
 		destroyPQExpBuffer(loOutQry);
@@ -5646,6 +5665,94 @@ getForeignServers(int *numForeignServers)
 }
 
 /*
+ * getDefaultACLs:
+ *	  read all default ACL information in the system catalogs and return
+ *	  them in the DefaultACLInfo structure
+ *
+ *	numDefaultACLs is set to the number of ACLs read in
+ */
+DefaultACLInfo *
+getDefaultACLs(int *numDefaultACLs)
+{
+	DefaultACLInfo *daclinfo;
+	PQExpBuffer query;
+	PGresult   *res;
+	int			i_oid;
+	int			i_tableoid;
+	int			i_defaclrole;
+	int			i_defaclnamespace;
+	int			i_defaclobjtype;
+	int			i_defaclacl;
+	int			i,
+				ntups;
+
+	if (g_fout->remoteVersion < 80500)
+	{
+		*numDefaultACLs = 0;
+		return NULL;
+	}
+
+	query = createPQExpBuffer();
+
+	/* Make sure we are in proper schema */
+	selectSourceSchema("pg_catalog");
+
+	appendPQExpBuffer(query, "SELECT oid, tableoid, "
+					  "(%s defaclrole) AS defaclrole, "
+					  "defaclnamespace, "
+					  "defaclobjtype, "
+					  "defaclacl "
+					  "FROM pg_default_acl",
+					  username_subquery);
+
+	res = PQexec(g_conn, query->data);
+	check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
+
+	ntups = PQntuples(res);
+	*numDefaultACLs = ntups;
+
+	daclinfo = (DefaultACLInfo *) malloc(ntups * sizeof(DefaultACLInfo));
+
+	i_oid = PQfnumber(res, "oid");
+	i_tableoid = PQfnumber(res, "tableoid");
+	i_defaclrole = PQfnumber(res, "defaclrole");
+	i_defaclnamespace = PQfnumber(res, "defaclnamespace");
+	i_defaclobjtype = PQfnumber(res, "defaclobjtype");
+	i_defaclacl = PQfnumber(res, "defaclacl");
+
+	for (i = 0; i < ntups; i++)
+	{
+		Oid		nspid = atooid(PQgetvalue(res, i, i_defaclnamespace));
+
+		daclinfo[i].dobj.objType = DO_DEFAULT_ACL;
+		daclinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
+		daclinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
+		AssignDumpId(&daclinfo[i].dobj);
+		/* cheesy ... is it worth coming up with a better object name? */
+		daclinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_defaclobjtype));
+
+		if (nspid != InvalidOid)
+			daclinfo[i].dobj.namespace = findNamespace(nspid,
+													   daclinfo[i].dobj.catId.oid);
+		else
+			daclinfo[i].dobj.namespace = NULL;
+
+		daclinfo[i].defaclrole = strdup(PQgetvalue(res, i, i_defaclrole));
+		daclinfo[i].defaclobjtype = *(PQgetvalue(res, i, i_defaclobjtype));
+		daclinfo[i].defaclacl = strdup(PQgetvalue(res, i, i_defaclacl));
+
+		/* Decide whether we want to dump it */
+		selectDumpableDefaultACL(&(daclinfo[i]));
+	}
+
+	PQclear(res);
+
+	destroyPQExpBuffer(query);
+
+	return daclinfo;
+}
+
+/*
  * dumpComment --
  *
  * This routine is used to dump any comments associated with the
@@ -6058,6 +6165,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
 		case DO_FOREIGN_SERVER:
 			dumpForeignServer(fout, (ForeignServerInfo *) dobj);
 			break;
+		case DO_DEFAULT_ACL:
+			dumpDefaultACL(fout, (DefaultACLInfo *) dobj);
+			break;
 		case DO_BLOBS:
 			ArchiveEntry(fout, dobj->catId, dobj->dumpId,
 						 dobj->name, NULL, NULL, "",
@@ -9791,6 +9901,72 @@ dumpUserMappings(Archive *fout, const char *target,
 	destroyPQExpBuffer(q);
 }
 
+/*
+ * Write out default privileges information
+ */
+static void
+dumpDefaultACL(Archive *fout, DefaultACLInfo *daclinfo)
+{
+	PQExpBuffer q;
+	PQExpBuffer tag;
+	const char *type;
+
+	/* Skip if not to be dumped */
+	if (!daclinfo->dobj.dump || dataOnly || aclsSkip)
+		return;
+
+	q = createPQExpBuffer();
+	tag = createPQExpBuffer();
+
+	switch (daclinfo->defaclobjtype)
+	{
+		case DEFACLOBJ_RELATION:
+			type = "TABLE";
+			break;
+		case DEFACLOBJ_SEQUENCE:
+			type = "SEQUENCE";
+			break;
+		case DEFACLOBJ_FUNCTION:
+			type = "FUNCTION";
+			break;
+		default:
+			/* shouldn't get here */
+			write_msg(NULL, "unknown object type (%d) in default privileges\n",
+					  (int) daclinfo->defaclobjtype);
+			exit_nicely();
+			type = "";			/* keep compiler quiet */
+	}
+
+	appendPQExpBuffer(tag, "DEFAULT %s PRIVILEGES", type);
+
+	/* build the actual command(s) for this tuple */
+	if (!buildDefaultACLCommands(type,
+								 daclinfo->dobj.namespace != NULL ?
+								 daclinfo->dobj.namespace->dobj.name : NULL,
+								 daclinfo->defaclacl,
+								 daclinfo->defaclrole,
+								 fout->remoteVersion,
+								 q))
+	{
+		write_msg(NULL, "could not parse default ACL list (%s)\n",
+				  daclinfo->defaclacl);
+		exit_nicely();
+	}
+
+	ArchiveEntry(fout, daclinfo->dobj.catId, daclinfo->dobj.dumpId,
+				 tag->data,
+				 daclinfo->dobj.namespace ? daclinfo->dobj.namespace->dobj.name : NULL,
+				 NULL,
+				 daclinfo->defaclrole,
+				 false, "DEFAULT ACL", SECTION_NONE,
+				 q->data, "", NULL,
+				 daclinfo->dobj.dependencies, daclinfo->dobj.nDeps,
+				 NULL, NULL);
+
+	destroyPQExpBuffer(tag);
+	destroyPQExpBuffer(q);
+}
+
 /*----------
  * Write out grant/revoke information
  *
@@ -9820,7 +9996,8 @@ dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
 
 	sql = createPQExpBuffer();
 
-	if (!buildACLCommands(name, subname, type, acls, owner, fout->remoteVersion, sql))
+	if (!buildACLCommands(name, subname, type, acls, owner,
+						  "", fout->remoteVersion, sql))
 	{
 		write_msg(NULL, "could not parse ACL list (%s) for object \"%s\" (%s)\n",
 				  acls, name, type);
@@ -10263,7 +10440,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
 								  fmtId(tbinfo->dobj.name));
 				appendPQExpBuffer(q, "ALTER COLUMN %s ",
 								  fmtId(tbinfo->attnames[j]));
-				appendPQExpBuffer(q, "SET STATISTICS DISTINCT %g;\n", 
+				appendPQExpBuffer(q, "SET STATISTICS DISTINCT %g;\n",
 								  tbinfo->attdistinct[j]);
 			}
 
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index beec160110c..5b2c3d1e6bf 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.157 2009/09/22 23:43:40 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.158 2009/10/05 19:24:46 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -114,6 +114,7 @@ typedef enum
 	DO_TSCONFIG,
 	DO_FDW,
 	DO_FOREIGN_SERVER,
+	DO_DEFAULT_ACL,
 	DO_BLOBS,
 	DO_BLOB_COMMENTS
 } DumpableObjectType;
@@ -432,6 +433,14 @@ typedef struct _foreignServerInfo
 	char	   *srvoptions;
 } ForeignServerInfo;
 
+typedef struct _defaultACLInfo
+{
+	DumpableObject dobj;
+	char	   *defaclrole;
+	char	    defaclobjtype;
+	char	   *defaclacl;
+} DefaultACLInfo;
+
 /* global decls */
 extern bool force_quotes;		/* double-quotes for identifiers flag */
 extern bool g_verbose;			/* verbose flag */
@@ -516,5 +525,6 @@ extern TSTemplateInfo *getTSTemplates(int *numTSTemplates);
 extern TSConfigInfo *getTSConfigurations(int *numTSConfigs);
 extern FdwInfo *getForeignDataWrappers(int *numForeignDataWrappers);
 extern ForeignServerInfo *getForeignServers(int *numForeignServers);
+extern DefaultACLInfo *getDefaultACLs(int *numDefaultACLs);
 
 #endif   /* PG_DUMP_H */
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index a56f429a7a8..4e95618318b 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump_sort.c,v 1.25 2009/06/11 14:49:07 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump_sort.c,v 1.26 2009/10/05 19:24:46 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -23,8 +23,8 @@ static const char *modulename = gettext_noop("sorter");
  * Objects are sorted by priority levels, and within an equal priority level
  * by OID.	(This is a relatively crude hack to provide semi-reasonable
  * behavior for old databases without full dependency info.)  Note: text
- * search and foreign-data objects can't really happen here, so the rather
- * bogus priorities for them don't matter.
+ * search, foreign-data, and default ACL objects can't really happen here,
+ * so the rather bogus priorities for them don't matter.
  */
 static const int oldObjectTypePriority[] =
 {
@@ -54,6 +54,7 @@ static const int oldObjectTypePriority[] =
 	5,							/* DO_TSCONFIG */
 	3,							/* DO_FDW */
 	4,							/* DO_FOREIGN_SERVER */
+	17,							/* DO_DEFAULT_ACL */
 	10,							/* DO_BLOBS */
 	11							/* DO_BLOB_COMMENTS */
 };
@@ -90,6 +91,7 @@ static const int newObjectTypePriority[] =
 	13,							/* DO_TSCONFIG */
 	14,							/* DO_FDW */
 	15,							/* DO_FOREIGN_SERVER */
+	27,							/* DO_DEFAULT_ACL */
 	20,							/* DO_BLOBS */
 	21							/* DO_BLOB_COMMENTS */
 };
@@ -1139,6 +1141,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
 					 "FOREIGN SERVER %s  (ID %d OID %u)",
 					 obj->name, obj->dumpId, obj->catId.oid);
 			return;
+		case DO_DEFAULT_ACL:
+			snprintf(buf, bufsize,
+					 "DEFAULT ACL %s  (ID %d OID %u)",
+					 obj->name, obj->dumpId, obj->catId.oid);
+			return;
 		case DO_BLOBS:
 			snprintf(buf, bufsize,
 					 "BLOBS  (ID %d)",
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 454b95c106c..f0a4d67d2af 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.126 2009/06/11 14:49:07 momjian Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.127 2009/10/05 19:24:46 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -989,7 +989,7 @@ dumpTablespaces(PGconn *conn)
 
 		if (!skip_acls &&
 			!buildACLCommands(fspcname, NULL, "TABLESPACE", spcacl, spcowner,
-							  server_version, buf))
+							  "", server_version, buf))
 		{
 			fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
 					progname, spcacl, fspcname);
@@ -1289,7 +1289,7 @@ dumpCreateDB(PGconn *conn)
 
 		if (!skip_acls &&
 			!buildACLCommands(fdbname, NULL, "DATABASE", dbacl, dbowner,
-							  server_version, buf))
+							  "", server_version, buf))
 		{
 			fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"),
 					progname, dbacl, fdbname);
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 67f05a89de0..d94d8b80c52 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2009, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.207 2009/09/13 22:18:22 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.208 2009/10/05 19:24:46 tgl Exp $
  */
 #include "postgres_fe.h"
 #include "command.h"
@@ -361,7 +361,10 @@ exec_command(const char *cmd,
 				success = listCasts(pattern);
 				break;
 			case 'd':
-				success = objectDescription(pattern, show_system);
+				if (strcmp(cmd, "ddp") == 0)
+					success = listDefaultACLs(pattern);
+				else
+					success = objectDescription(pattern, show_system);
 				break;
 			case 'D':
 				success = listDomains(pattern, show_system);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 6e288da67a8..1644623812c 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -8,7 +8,7 @@
  *
  * Copyright (c) 2000-2009, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.226 2009/07/29 20:56:19 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.227 2009/10/05 19:24:46 tgl Exp $
  */
 #include "postgres_fe.h"
 
@@ -732,6 +732,73 @@ permissionsList(const char *pattern)
 }
 
 
+/*
+ * \ddp
+ *
+ * List DefaultACLs.  The pattern can match either schema or role name.
+ */
+bool
+listDefaultACLs(const char *pattern)
+{
+	PQExpBufferData buf;
+	PGresult   *res;
+	printQueryOpt myopt = pset.popt;
+	static const bool translate_columns[] = {false, false, true, false};
+
+	if (pset.sversion < 80500)
+	{
+		fprintf(stderr, _("The server (version %d.%d) does not support altering default privileges.\n"),
+				pset.sversion / 10000, (pset.sversion / 100) % 100);
+		return true;
+	}
+
+	initPQExpBuffer(&buf);
+
+	printfPQExpBuffer(&buf,
+					  "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
+					  "  n.nspname AS \"%s\",\n"
+					  "  CASE d.defaclobjtype WHEN 'r' THEN '%s' WHEN 'S' THEN '%s' WHEN 'f' THEN '%s' END AS \"%s\",\n"
+					  "  ",
+					  gettext_noop("Owner"),
+					  gettext_noop("Schema"),
+					  gettext_noop("table"),
+					  gettext_noop("sequence"),
+					  gettext_noop("function"),
+					  gettext_noop("Type"));
+
+	printACLColumn(&buf, "d.defaclacl");
+
+	appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_default_acl d\n"
+	   "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n");
+
+	processSQLNamePattern(pset.db, &buf, pattern, false, false,
+						  NULL,
+						  "n.nspname",
+						  "pg_catalog.pg_get_userbyid(d.defaclrole)",
+						  NULL);
+
+	appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
+
+	res = PSQLexec(buf.data, false);
+	if (!res)
+	{
+		termPQExpBuffer(&buf);
+		return false;
+	}
+
+	myopt.nullPrint = NULL;
+	printfPQExpBuffer(&buf, _("Default access privileges"));
+	myopt.title = buf.data;
+	myopt.translate_header = true;
+	myopt.translate_columns = translate_columns;
+
+	printQuery(res, &myopt, pset.queryFout, pset.logfile);
+
+	termPQExpBuffer(&buf);
+	PQclear(res);
+	return true;
+}
+
 
 /*
  * Get object comments
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 769ee9e975d..169ceb3739a 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2009, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/describe.h,v 1.40 2009/04/21 15:49:06 momjian Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/describe.h,v 1.41 2009/10/05 19:24:46 tgl Exp $
  */
 #ifndef DESCRIBE_H
 #define DESCRIBE_H
@@ -30,6 +30,9 @@ extern bool describeRoles(const char *pattern, bool verbose);
 /* \z (or \dp) */
 extern bool permissionsList(const char *pattern);
 
+/* \ddp */
+extern bool listDefaultACLs(const char *pattern);
+
 /* \dd */
 extern bool objectDescription(const char *pattern, bool showSystem);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 947eff050a1..f21099a6923 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2009, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/help.c,v 1.152 2009/09/18 05:00:42 petere Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/help.c,v 1.153 2009/10/05 19:24:46 tgl Exp $
  */
 #include "postgres_fe.h"
 
@@ -201,6 +201,7 @@ slashUsage(unsigned short int pager)
 	fprintf(output, _("  \\dc[S]  [PATTERN]      list conversions\n"));
 	fprintf(output, _("  \\dC     [PATTERN]      list casts\n"));
 	fprintf(output, _("  \\dd[S]  [PATTERN]      show comments on objects\n"));
+	fprintf(output, _("  \\ddp    [PATTERN]      list default privileges\n"));
 	fprintf(output, _("  \\dD[S]  [PATTERN]      list domains\n"));
 	fprintf(output, _("  \\des[+] [PATTERN]      list foreign servers\n"));
 	fprintf(output, _("  \\deu[+] [PATTERN]      list user mappings\n"));