[libgda] More user manipulation server operations for PostgreSQL:

From: Vivien Malerba <vivien src gnome org>
To: commits-list gnome org
Cc:
Subject: [libgda] More user manipulation server operations for PostgreSQL:
Date: Sun, 20 Jun 2010 21:22:28 +0000 (UTC)
commit d214ebccadf5d40cb31fb91450fbb5fa5e315934
Author: Vivien Malerba <malerba gnome-db org>
Date:   Sun Jun 20 18:18:14 2010 +0200

    More user manipulation server operations for PostgreSQL:
    
    * CREATE USER operation uses roles if possible
    * added DROP USER

 libgda/gda-server-operation.c                      |    4 +-
 providers/postgres/Makefile.am                     |    5 +-
 providers/postgres/gda-postgres-ddl.c              |  169 ++++++++++++++++++--
 providers/postgres/gda-postgres-ddl.h              |    2 +
 providers/postgres/gda-postgres-provider.c         |   51 +++++-
 .../postgres/postgres_specs_create_role.xml.in     |   47 ++++++
 .../postgres/postgres_specs_create_user.xml.in     |    7 +-
 providers/postgres/postgres_specs_drop_role.xml.in |    7 +
 providers/postgres/postgres_specs_drop_user.xml.in |    7 +
 9 files changed, 274 insertions(+), 25 deletions(-)
---
diff --git a/libgda/gda-server-operation.c b/libgda/gda-server-operation.c
index aea93c3..708530c 100644
--- a/libgda/gda-server-operation.c
+++ b/libgda/gda-server-operation.c
@@ -1225,6 +1225,8 @@ gda_server_operation_op_type_to_string (GdaServerOperationType type)
 		return "DROP_VIEW";
 	case GDA_SERVER_OPERATION_CREATE_USER:
 		return "CREATE_USER";
+	case GDA_SERVER_OPERATION_DROP_USER:
+		return "DROP_USER";
 	default:
 		g_error (_("Non handled GdaServerOperationType, please report error to "
 			   "http://bugzilla.gnome.org/ for the \"libgda\" product"));
@@ -2261,7 +2263,7 @@ gda_server_operation_is_valid (GdaServerOperation *op, const gchar *xml_file, GE
 				if (node->type == GDA_SERVER_OPERATION_NODE_PARAM) {
 					const GValue *value;
 					gchar *path;
-					
+
 					path = node_get_complete_path (op, node);
 					value = gda_server_operation_get_value_at (op, path);
 					if (!value) {
diff --git a/providers/postgres/Makefile.am b/providers/postgres/Makefile.am
index f015e2d..33f9d0e 100644
--- a/providers/postgres/Makefile.am
+++ b/providers/postgres/Makefile.am
@@ -46,7 +46,10 @@ xml_in_files = \
         postgres_specs_drop_column.xml.in \
         postgres_specs_create_view.xml.in \
         postgres_specs_drop_view.xml.in \
-	postgres_specs_create_user.xml.in
+	postgres_specs_create_user.xml.in \
+	postgres_specs_create_role.xml.in \
+	postgres_specs_drop_user.xml.in \
+	postgres_specs_drop_role.xml.in
 
 @INTLTOOL_XML_RULE@
 
diff --git a/providers/postgres/gda-postgres-ddl.c b/providers/postgres/gda-postgres-ddl.c
index 19666cc..8606a9a 100644
--- a/providers/postgres/gda-postgres-ddl.c
+++ b/providers/postgres/gda-postgres-ddl.c
@@ -1,5 +1,5 @@
 /* GDA Postgres Provider
- * Copyright (C) 2008 The GNOME Foundation
+ * Copyright (C) 2008 - 2010 The GNOME Foundation
  *
  * AUTHORS:
  *      Vivien Malerba <malerba gnome-db org>
@@ -23,6 +23,7 @@
 #include <glib/gi18n-lib.h>
 #include <libgda/libgda.h>
 #include "gda-postgres-ddl.h"
+#include "gda-postgres.h"
 
 gchar *
 gda_postgres_render_CREATE_DB (GdaServerProvider *provider, GdaConnection *cnc, 
@@ -758,9 +759,22 @@ gda_postgres_render_CREATE_USER (GdaServerProvider *provider, GdaConnection *cnc
 	const GValue *value;
 	gchar *sql = NULL;
 	gchar *tmp;
-	gboolean with = FALSE;
+	gboolean with = FALSE, first, use_role = TRUE;
+	gint nrows, i;
+	PostgresConnectionData *cdata = NULL;
+
+	if (cnc) {
+		g_return_val_if_fail (GDA_IS_CONNECTION (cnc), FALSE);
+		g_return_val_if_fail (gda_connection_get_provider (cnc) == provider, FALSE);
+		cdata = (PostgresConnectionData*) gda_connection_internal_get_provider_data (cnc);
+	}
+	if (cdata && (cdata->reuseable->version_float < 8.1))
+		use_role = FALSE;
 
-	string = g_string_new ("CREATE USER ");
+	if (use_role)
+		string = g_string_new ("CREATE ROLE ");
+	else
+		string = g_string_new ("CREATE USER ");
 
 	tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/USER_DEF_P/USER_NAME");
 	g_string_append (string, tmp);
@@ -798,6 +812,15 @@ gda_postgres_render_CREATE_USER (GdaServerProvider *provider, GdaConnection *cnc
 		g_string_append_printf (string, "SYSID %u", g_value_get_uint (value));
 	}
 
+	value = gda_server_operation_get_value_at (op, "/USER_DEF_P/CAP_SUPERUSER");
+	if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
+		if (!with) {
+			g_string_append (string, " WITH");
+			with = TRUE;
+		}
+		g_string_append (string, " SUPERUSER");
+	}
+
 	value = gda_server_operation_get_value_at (op, "/USER_DEF_P/CAP_CREATEDB");
 	if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
 		if (!with) {
@@ -807,6 +830,15 @@ gda_postgres_render_CREATE_USER (GdaServerProvider *provider, GdaConnection *cnc
 		g_string_append (string, " CREATEDB");
 	}
 
+	value = gda_server_operation_get_value_at (op, "/USER_DEF_P/CAP_CREATEROLE");
+	if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
+		if (!with) {
+			g_string_append (string, " WITH");
+			with = TRUE;
+		}
+		g_string_append (string, " CREATEROLE");
+	}
+
 	value = gda_server_operation_get_value_at (op, "/USER_DEF_P/CAP_CREATEUSER");
 	if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
 		if (!with) {
@@ -815,20 +847,89 @@ gda_postgres_render_CREATE_USER (GdaServerProvider *provider, GdaConnection *cnc
 		}
 		g_string_append (string, " CREATEUSER");
 	}
+
+	value = gda_server_operation_get_value_at (op, "/USER_DEF_P/CAP_INHERIT");
+	if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
+		if (!with) {
+			g_string_append (string, " WITH");
+			with = TRUE;
+		}
+		g_string_append (string, " INHERIT");
+	}
+	else {
+		if (!with) {
+			g_string_append (string, " WITH");
+			with = TRUE;
+		}
+		g_string_append (string, " NOINHERIT");
+	}
+
+	value = gda_server_operation_get_value_at (op, "/USER_DEF_P/CAP_LOGIN");
+	if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
+		g_string_append (string, " LOGIN");
+		value = gda_server_operation_get_value_at (op, "/USER_DEF_P/CNX_LIMIT");
+		if (value && G_VALUE_HOLDS (value, G_TYPE_INT))
+			g_string_append_printf (string, " CONNECTION LIMIT %d",
+						g_value_get_int (value));
+	}
+
 	
-	value = gda_server_operation_get_value_at (op, "/USER_DEF_P/GROUPS");
-	if (value && G_VALUE_HOLDS (value, G_TYPE_STRING) &&
-	    g_value_get_string (value) && (*g_value_get_string (value))) {
-		GdaDataHandler *dh;		
+	nrows = gda_server_operation_get_sequence_size (op, "/GROUPS_S");
+	for (first  = TRUE, i = 0; i < nrows; i++) {
+		gchar *name;
+		if (use_role)
+			name = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/GROUPS_S/%d/ROLE", i);
+		else
+			name = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/GROUPS_S/%d/USER", i);
 
-		g_string_append (string, " IN GROUP ");
-		dh = gda_server_provider_get_data_handler_g_type (provider, cnc, G_TYPE_STRING);
-		if (!dh)
-			dh = gda_get_default_handler (G_TYPE_STRING);
+		if (name && *name) {
+			if (first) {
+				first = FALSE;
+				if (use_role)
+					g_string_append (string, " IN ROLE ");
+				else
+					g_string_append (string, " IN GROUP ");
+			}
+			else
+				g_string_append (string, ", ");
 
-		tmp = gda_data_handler_get_sql_from_value (dh, value);
-		g_string_append (string, tmp);
-		g_free (tmp);
+			g_string_append (string, name);
+		}
+		g_free (name);
+	}
+
+	nrows = gda_server_operation_get_sequence_size (op, "/ROLES_S");
+	for (first  = TRUE, i = 0; i < nrows; i++) {
+		gchar *name;
+		name = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/ROLES_S/%d/ROLE", i);
+		if (name && *name) {
+			if (first) {
+				first = FALSE;
+				g_string_append (string, " ROLE ");
+			}
+			else
+				g_string_append (string, ", ");
+
+			g_string_append (string, name);
+		}
+		g_free (name);
+	}
+
+	nrows = gda_server_operation_get_sequence_size (op, "/ADMINS_S");
+	for (first  = TRUE, i = 0; i < nrows; i++) {
+		gchar *name;
+		name = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/ADMINS_S/%d/ROLE", i);
+		if (name && *name) {
+			if (first) {
+				first = FALSE;
+				g_string_append (string, " ADMIN ");
+			}
+			else
+				g_string_append (string, ", ");
+
+			g_string_append (string, name);
+		}
+		g_free (name);
 	}
 	
 	value = gda_server_operation_get_value_at (op, "/USER_DEF_P/VALIDITY");
@@ -858,3 +959,43 @@ gda_postgres_render_CREATE_USER (GdaServerProvider *provider, GdaConnection *cnc
 
 	return sql;
 }
+
+gchar *
+gda_postgres_render_DROP_USER (GdaServerProvider *provider, GdaConnection *cnc,
+			       GdaServerOperation *op, GError **error)
+{
+	GString *string;
+	const GValue *value;
+	gchar *sql = NULL;
+	gchar *tmp;
+	gboolean use_role = TRUE;
+	PostgresConnectionData *cdata = NULL;
+
+	if (cnc) {
+		g_return_val_if_fail (GDA_IS_CONNECTION (cnc), FALSE);
+		g_return_val_if_fail (gda_connection_get_provider (cnc) == provider, FALSE);
+		cdata = (PostgresConnectionData*) gda_connection_internal_get_provider_data (cnc);
+	}
+	if (cdata && (cdata->reuseable->version_float < 8.1))
+		use_role = FALSE;
+
+	if (use_role)
+		string = g_string_new ("DROP ROLE ");
+	else
+		string = g_string_new ("DROP USER ");
+
+	value = gda_server_operation_get_value_at (op, "/USER_DESC_P/USER_IFEXISTS");
+	if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
+		g_string_append (string, " IF EXISTS");
+
+	tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider,
+							  "/USER_DESC_P/USER_NAME");
+	g_string_append_c (string, ' ');
+	g_string_append (string, tmp);
+	g_free (tmp);
+
+	sql = string->str;
+	g_string_free (string, FALSE);
+
+	return sql;
+}
diff --git a/providers/postgres/gda-postgres-ddl.h b/providers/postgres/gda-postgres-ddl.h
index b8c0ecd..df49ef1 100644
--- a/providers/postgres/gda-postgres-ddl.h
+++ b/providers/postgres/gda-postgres-ddl.h
@@ -52,6 +52,8 @@ gchar *gda_postgres_render_DROP_VIEW    (GdaServerProvider *provider, GdaConnect
                                          GdaServerOperation *op, GError **error);
 gchar *gda_postgres_render_CREATE_USER  (GdaServerProvider *provider, GdaConnection *cnc,
                                          GdaServerOperation *op, GError **error);
+gchar *gda_postgres_render_DROP_USER    (GdaServerProvider *provider, GdaConnection *cnc,
+                                         GdaServerOperation *op, GError **error);
 
 G_END_DECLS
 
diff --git a/providers/postgres/gda-postgres-provider.c b/providers/postgres/gda-postgres-provider.c
index 7587061..57b484d 100644
--- a/providers/postgres/gda-postgres-provider.c
+++ b/providers/postgres/gda-postgres-provider.c
@@ -704,6 +704,7 @@ gda_postgres_provider_supports_operation (GdaServerProvider *provider, GdaConnec
         case GDA_SERVER_OPERATION_DROP_VIEW:
 
         case GDA_SERVER_OPERATION_CREATE_USER:
+        case GDA_SERVER_OPERATION_DROP_USER:
                 return TRUE;
         default:
                 return FALSE;
@@ -724,15 +725,31 @@ gda_postgres_provider_create_operation (GdaServerProvider *provider, GdaConnecti
         GdaServerOperation *op;
         gchar *str;
 	gchar *dir;
+	PostgresConnectionData *cdata = NULL;
 
 	if (cnc) {
 		g_return_val_if_fail (GDA_IS_CONNECTION (cnc), FALSE);
 		g_return_val_if_fail (gda_connection_get_provider (cnc) == provider, FALSE);
+		cdata = (PostgresConnectionData*) gda_connection_internal_get_provider_data (cnc);
 	}
 
-        file = g_utf8_strdown (gda_server_operation_op_type_to_string (type), -1);
-        str = g_strdup_printf ("postgres_specs_%s.xml", file);
-        g_free (file);
+	if (type == GDA_SERVER_OPERATION_CREATE_USER) {
+		if (cdata && (cdata->reuseable->version_float < 8.1))
+			str = g_strdup ("postgres_specs_create_user.xml");
+		else
+			str = g_strdup ("postgres_specs_create_role.xml");
+	}
+	else if (type == GDA_SERVER_OPERATION_DROP_USER) {
+		if (cdata && (cdata->reuseable->version_float < 8.1))
+			str = g_strdup ("postgres_specs_drop_user.xml");
+		else
+			str = g_strdup ("postgres_specs_drop_role.xml");
+	}
+	else {
+		file = g_utf8_strdown (gda_server_operation_op_type_to_string (type), -1);
+		str = g_strdup_printf ("postgres_specs_%s.xml", file);
+		g_free (file);
+	}
 
 	dir = gda_gbr_get_file_path (GDA_DATA_DIR, LIBGDA_ABI_NAME, NULL);
         file = gda_server_provider_find_file (provider, dir, str);
@@ -762,17 +779,34 @@ gda_postgres_provider_render_operation (GdaServerProvider *provider, GdaConnecti
         gchar *file;
         gchar *str;
 	gchar *dir;
+	PostgresConnectionData *cdata = NULL;
+	GdaServerOperationType type;
 
 	if (cnc) {
 		g_return_val_if_fail (GDA_IS_CONNECTION (cnc), FALSE);
 		g_return_val_if_fail (gda_connection_get_provider (cnc) == provider, FALSE);
 	}
 
-	/* test @op's validity */
-        file = g_utf8_strdown (gda_server_operation_op_type_to_string (gda_server_operation_get_op_type (op)), -1);
-        str = g_strdup_printf ("postgres_specs_%s.xml", file);
-        g_free (file);
+	type = gda_server_operation_get_op_type (op);
+	if (type == GDA_SERVER_OPERATION_CREATE_USER) {
+		if (cdata && (cdata->reuseable->version_float < 8.1))
+			str = g_strdup ("postgres_specs_create_user.xml");
+		else
+			str = g_strdup ("postgres_specs_create_role.xml");
+	}
+	else if (type == GDA_SERVER_OPERATION_DROP_USER) {
+		if (cdata && (cdata->reuseable->version_float < 8.1))
+			str = g_strdup ("postgres_specs_drop_user.xml");
+		else
+			str = g_strdup ("postgres_specs_drop_role.xml");
+	}
+	else {
+		file = g_utf8_strdown (gda_server_operation_op_type_to_string (type), -1);
+		str = g_strdup_printf ("postgres_specs_%s.xml", file);
+		g_free (file);
+	}
 
+	/* test @op's validity */
 	dir = gda_gbr_get_file_path (GDA_DATA_DIR, LIBGDA_ABI_NAME, NULL);
         file = gda_server_provider_find_file (provider, dir, str);
 	g_free (dir);
@@ -827,6 +861,9 @@ gda_postgres_provider_render_operation (GdaServerProvider *provider, GdaConnecti
         case GDA_SERVER_OPERATION_CREATE_USER:
                 sql = gda_postgres_render_CREATE_USER (provider, cnc, op, error);
                 break;
+        case GDA_SERVER_OPERATION_DROP_USER:
+                sql = gda_postgres_render_DROP_USER (provider, cnc, op, error);
+                break;
         default:
                 g_assert_not_reached ();
         }
diff --git a/providers/postgres/postgres_specs_create_role.xml.in b/providers/postgres/postgres_specs_create_role.xml.in
new file mode 100644
index 0000000..b0606ee
--- /dev/null
+++ b/providers/postgres/postgres_specs_create_role.xml.in
@@ -0,0 +1,47 @@
+<?xml version="1.0"?>
+<serv_op>
+<!--
+   This file contains the CREATE ROLE operation's parameters, which is
+   available since PostgreSQL 8.1
+-->
+  <parameters id="USER_DEF_P" _name="Role's definition">
+    <parameter id="USER_NAME" _name="Name" _descr="Role's name" gdatype="gchararray" nullok="FALSE"/>
+    <parameter id="PASSWORD" _name="Password" _descr="Role's password" gdatype="gchararray" plugin="string:HIDDEN=true"/>
+    <parameter id="PASSWORD_ENCRYPTED" _name="Encrypt password" _descr="Controls whether the password is stored encrypted in the system catalogs. If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is." gdatype="gboolean">
+      <gda_value>FALSE</gda_value>
+    </parameter>
+
+    <parameter id="CAP_SUPERUSER" _name="Database superuser" _descr="Set to TRUE if the role is a database superuser" gdatype="gboolean">
+      <gda_value>FALSE</gda_value>
+    </parameter>
+    <parameter id="CAP_CREATEDB" _name="Can create databases" _descr="Set to TRUE if the role is allowed to create databases" gdatype="gboolean">
+      <gda_value>FALSE</gda_value>
+    </parameter>
+    <parameter id="CAP_CREATEROLE" _name="Can create roles" _descr="Set to TRUE if the role is allowed to create roles" gdatype="gboolean">
+      <gda_value>FALSE</gda_value>
+    </parameter>
+    <parameter id="CAP_INHERIT" _name="Inherit" _descr="Set to TRUE if the created role inherits the privileges of roles it is a member of" gdatype="gboolean">
+      <gda_value>FALSE</gda_value>
+    </parameter>
+    <parameter id="CAP_LOGIN" _name="Can login" _descr="Set to TRUE if the role is allowed to log in" gdatype="gboolean">
+      <gda_value>FALSE</gda_value>
+    </parameter>
+    <parameter id="CNX_LIMIT" _name="Login limit" _descr="If the role is allowed to log in, defines the number of concurrent connections the role can make (-1 or unset for unlimited)" gdatype="gint">
+      <gda_value>-1</gda_value>
+    </parameter>
+
+    <parameter id="VALIDITY" _name="Valid until" _descr="Specifies an expiration time for a password only (not for the user account per se: the expiration time is not enforced when logging in using a non-password-based authentication method)" gdatype="timestamp"/>
+  </parameters>
+
+  <sequence id="GROUPS_S" _name="Member of" _descr="Existing roles to which the new role will be immediately added as a new member" status="OPT">
+    <parameter id="ROLE" _name="Role" _descr="Existing role" gdatype="string"/>
+  </sequence>
+
+  <sequence id="ROLES_S" _name="Role's members" _descr="Lists one or more existing roles which are automatically added as members of the new role" status="OPT">
+    <parameter id="ROLE" _name="Role" _descr="Existing role" gdatype="string"/>
+  </sequence>
+
+  <sequence id="ADMINS_S" _name="Role's admins" _descr="Lists one or more existing roles which are automatically added as members of the new role, giving them the right to grant membership in this role to others" status="OPT">
+    <parameter id="ROLE" _name="Role" _descr="Existing role" gdatype="string"/>
+  </sequence>
+</serv_op>
diff --git a/providers/postgres/postgres_specs_create_user.xml.in b/providers/postgres/postgres_specs_create_user.xml.in
index ea18664..4939ed3 100644
--- a/providers/postgres/postgres_specs_create_user.xml.in
+++ b/providers/postgres/postgres_specs_create_user.xml.in
@@ -15,9 +15,12 @@
       <gda_value>FALSE</gda_value>
     </parameter>
 
-    <parameter id="GROUPS" _name="Groups" _descr="Comma separated list of groups the user will belong to" gdatype="gchararray"/>
-
     <parameter id="VALIDITY" _name="Valid until" _descr="Specifies an expiration time for a password only (not for the user account per se: the expiration time is not enforced when logging in using a non-password-based authentication method)" gdatype="timestamp"/>
 
   </parameters>
+
+  <sequence id="GROUPS_S" _name="Member of" _descr="Existing groups to which the new user will be immediately added as a new member" status="OPT">
+    <parameter id="USER" _name="User" _descr="Existing user" gdatype="string"/>
+  </sequence>
+
 </serv_op>
diff --git a/providers/postgres/postgres_specs_drop_role.xml.in b/providers/postgres/postgres_specs_drop_role.xml.in
new file mode 100644
index 0000000..1e5474b
--- /dev/null
+++ b/providers/postgres/postgres_specs_drop_role.xml.in
@@ -0,0 +1,7 @@
+<?xml version="1.0"?>
+<serv_op>
+  <parameters id="USER_DESC_P" _name="Role's definition">
+    <parameter id="USER_NAME" _name="Name" _descr="Role's name" gdatype="gchararray" nullok="FALSE"/>
+    <parameter id="USER_IFEXISTS" _name="If exists" _descr="Drop role only if it exists" gdatype="gboolean"/>
+  </parameters>
+</serv_op>
diff --git a/providers/postgres/postgres_specs_drop_user.xml.in b/providers/postgres/postgres_specs_drop_user.xml.in
new file mode 100644
index 0000000..08ffed5
--- /dev/null
+++ b/providers/postgres/postgres_specs_drop_user.xml.in
@@ -0,0 +1,7 @@
+<?xml version="1.0"?>
+<serv_op>
+  <parameters id="USER_DESC_P" _name="User's definition">
+    <parameter id="USER_NAME" _name="Name" _descr="User's name" gdatype="gchararray" nullok="FALSE"/>
+    <parameter id="USER_IFEXISTS" _name="If exists" _descr="Drop user only if it exists" gdatype="gboolean"/>
+  </parameters>
+</serv_op>
[Date Prev][Date Next] [Thread Prev][Thread Next] [Thread Index] [Date Index] [Author Index]