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

4 files changed, 79 insertions, 5 deletions

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"));