public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ashesh Vashi <[email protected]>
To: Murtuza Zabuawala <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Cc: Akshay Joshi <[email protected]>
Subject: Re: PATCH: Login/Group Role Node
Date: Thu, 4 Feb 2016 18:32:19 +0530
Message-ID: <CAG7mmowKAv8XR0A0F+sUYmcBLwg+oz+ouBQByor2m+YQ_bbzXw@mail.gmail.com> (raw)
In-Reply-To: <CAG7mmowomS77wvuZdsyv9TgOT9C559xHnRmc+72Ld4VsPhpoEw@mail.gmail.com>
References: <CAG7mmoy=Wd5xHV=e734d4=8pqS7BOKyL8mUdh-5AMfs84Tm6VQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAG7mmowomS77wvuZdsyv9TgOT9C559xHnRmc+72Ld4VsPhpoEw@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi Team,
Please ignore the previous patch.
It was not a git binary-patch.
Please find the updated patch, which includes both binary patch, and
Security Label changes (which were disabled by default).
--
Thanks & Regards,
Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company
<http://www.enterprisedb.com;
*http://www.linkedin.com/in/asheshvashi*
<http://www.linkedin.com/in/asheshvashi;
On Thu, Feb 4, 2016 at 5:37 PM, Ashesh Vashi <[email protected]>
wrote:
> Hi Team,
>
> Please find the update patch for the Login/Group Role node.
>
>
> On Wed, Jan 20, 2016 at 2:10 PM, Murtuza Zabuawala <
> [email protected]> wrote:
>
>> Hi Ashesh,
>>
>> Addition to below, I also observed two more issues,
>>
>> - It's a bad idea to give md5 hash of current role's password in
>> properties/edit mode, A malicious user can use that hash to crack another
>> role's password.
>>
> Ok.
> I am not sending the password any more to the client in edit mode.
>
>>
>> - When you select a role and click on SQL panel (near to Statistic
>> panel), we encounter exception as below,
>>
>> File
>> "/home/murtuza/PROJECT/pgadmin4/web/pgadmin/browser/server_groups/servers/roles/__init__.py",
>> line 531, in wrapped
>> return f(self, **kwargs)
>> File
>> "/home/murtuza/PROJECT/pgadmin4/web/pgadmin/browser/server_groups/servers/roles/__init__.py",
>> line 714, in sql
>> data=self.request,
>> AttributeError: 'RoleView' object has no attribute 'request'
>>
> Statistics are yet implemented for any node.
> It will be taken care as a separate patch.
>
>>
>>
>> Regards,
>> Murtuza
>>
>>
>>
>> On Thursday 14 January 2016 01:48 PM, Murtuza Zabuawala wrote:
>>
>> Hi Ashesh,
>>
>> Observed so far,
>>
>> In pgAdmin4,
>> 1) We are not displaying below items in properties when compare to
>> pgAdmin3
>> - Account expires
>> - Member of
>>
>> Added.
>
>>
>> 2) Seems life one of menu item missing when right click on role
>> - Reassign/ Drop owned
>>
>> It'll be added as a separate module.
>
>>
>> 3) Security label is disabled by default, Even though security label is
>> disabled, we get precondition error from server saying.
>>
>> *"errormsg": "Security Label must be passed as an array of JSON object in the following.."*
>>
>> Done.
>
>>
>> Causing:
>> - When we add variables in Security tab on existing role, Sql does not
>> gets generated in sql tab.
>> - When we add roles role membership on existing role, Sql does not gets
>> generated in sql tab and it also allow us to enter invalid role which does
>> not exists.
>>
>> Done.
>
> --
>
> Thanks & Regards,
>
> Ashesh Vashi
> EnterpriseDB INDIA: Enterprise PostgreSQL Company
> <http://www.enterprisedb.com;
>
>
> *http://www.linkedin.com/in/asheshvashi*
> <http://www.linkedin.com/in/asheshvashi;
>
>
>>
>>
>> Regards,
>> Murtuza
>>
>> On Tuesday 12 January 2016 10:47 PM, Ashesh Vashi wrote:
>>
>> Hi Team,
>>
>> Please find the patch for Login/Group Role(s).
>>
>> Akshay/Murtuza,
>>
>> Can you please review it?
>>
>> --
>>
>> Thanks & Regards,
>>
>> Ashesh Vashi
>> EnterpriseDB INDIA: Enterprise PostgreSQL Company
>> <http://www.enterprisedb.com;
>>
>>
>> *
>> <http://www.linkedin.com/in/asheshvashi>http://www.linkedin.com/in/asheshvashi
>> <http://www.linkedin.com/in/asheshvashi>*
>>
>>
>>
>>
>
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[application/octet-stream] role_v4.patch (108.0K, 3-role_v4.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/roles/__init__.py b/web/pgadmin/browser/server_groups/servers/roles/__init__.py
new file mode 100644
index 0000000..430dc48
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/__init__.py
@@ -0,0 +1,902 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+from flask import render_template, request, current_app, jsonify
+from flask.ext.babel import gettext as _
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, precondition_required, \
+ internal_server_error, forbidden, \
+ not_implemented, success_return
+from pgadmin.browser.utils import NodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups as sg
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+import re
+import datetime
+from functools import wraps
+import simplejson as json
+
+
+class RoleModule(CollectionNodeModule):
+ NODE_TYPE = 'role'
+ COLLECTION_LABEL = _("Login/Group Roles")
+
+ def __init__(self, *args, **kwargs):
+ self.min_ver = None
+ self.max_ver = None
+
+ super(RoleModule, self).__init__(*args, **kwargs)
+
+ def get_nodes(self, gid, sid):
+ """
+ Generate the collection node
+ """
+
+ yield self.generate_browser_collection_node(sid)
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for server, when any of the server-group node is
+ initialized.
+ """
+ return sg.ServerGroupModule.NODE_TYPE
+
+ @property
+ def csssnippets(self):
+ """
+ Returns a snippet of css to include in the page
+ """
+ snippets = [
+ render_template(
+ "browser/css/collection.css",
+ node_type=self.node_type
+ ),
+ render_template("role/css/role.css")]
+
+ for submodule in self.submodules:
+ snippets.extend(submodule.csssnippets)
+
+ return snippets
+
+
+blueprint = RoleModule(__name__)
+
+
+class RoleView(NodeView):
+ node_type = 'role'
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'rid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'drop', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'children': [{'get': 'children'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'vopts': [{}, {'get': 'voptions'}],
+ 'variables': [{'get': 'variables'}],
+ })
+
+ def validate_request(f):
+ @wraps(f)
+ def wrap(self, **kwargs):
+
+ data = None
+ if request.data:
+ data = json.loads(request.data)
+ else:
+ data = dict()
+ req = request.args or request.form
+
+ for key in req:
+
+ val = req[key]
+ if key in [
+ u'rolcanlogin', u'rolsuper', u'rolcreatedb',
+ u'rolcreaterole', u'rolinherit', u'rolreplication',
+ u'rolcatupdate', u'variables', u'rolmembership',
+ u'seclabels'
+ ]:
+ data[key] = json.loads(val)
+ else:
+ data[key] = val
+
+ if u'rid' not in kwargs or kwargs['rid'] == -1:
+ if u'rolname' not in data:
+ return precondition_required(
+ _("Name is not provided!")
+ )
+
+ if u'rolconnlimit' in data:
+ if data[u'rolconnlimit'] is not None:
+ data[u'rolconnlimit'] = int(data[u'rolconnlimit'])
+ if type(data[u'rolconnlimit']) != int or data[u'rolconnlimit'] < -1:
+ return precondition_required(
+ _("Connection limit must be an integer value or equals to -1!")
+ )
+
+ if u'rolmembership' in data:
+ if u'rid' not in kwargs or kwargs['rid'] == -1:
+ msg = _("""
+Role membership information must be passed as an array of JSON object in the
+following format:
+
+rolmembership:[{
+ role: [rolename],
+ admin: True/False
+ },
+ ...
+]""")
+ if type(data[u'rolmembership']) != list:
+ return precondition_required(msg)
+
+ data[u'members'] = []
+ data[u'admins'] = []
+
+ for r in data[u'rolmembership']:
+ if type(r) != dict or u'role' not in r or u'admin' not in r:
+ return precondition_required(msg)
+ else:
+ if r[u'admin']:
+ data[u'admins'].append(r[u'role'])
+ else:
+ data[u'members'].append(r[u'role'])
+ else:
+ msg = _("""
+Role membership information must be passed a string representing an array of
+JSON object in the following format:
+rolmembership:{
+ 'added': [{
+ role: [rolename],
+ admin: True/False
+ },
+ ...
+ ],
+ 'deleted': [{
+ role: [rolename],
+ admin: True/False
+ },
+ ...
+ ],
+ 'updated': [{
+ role: [rolename],
+ admin: True/False
+ },
+ ...
+ ]
+""")
+ if type(data[u'rolmembership']) != dict:
+ return precondition_required(msg)
+
+ data[u'members'] = []
+ data[u'admins'] = []
+ data[u'revoked_admins'] = []
+ data[u'revoked'] = []
+
+ if u'added' in data[u'rolmembership']:
+ roles = (data[u'rolmembership'])[u'added']
+
+ if type(roles) != list:
+ return precondition_required(msg)
+
+ for r in roles:
+ if (type(r) != dict or u'role' not in r or
+ u'admin' not in r):
+ return precondition_required(msg)
+
+ if r[u'admin']:
+ data[u'admins'].append(r[u'role'])
+ else:
+ data[u'members'].append(r[u'role'])
+
+ if u'deleted' in data[u'rolmembership']:
+ roles = (data[u'rolmembership'])[u'deleted']
+
+ if type(roles) != list:
+ return precondition_required(msg)
+
+ for r in roles:
+ if type(r) != dict or u'role' not in r:
+ return precondition_required(msg)
+
+ data[u'revoked'].append(r[u'role'])
+
+ if u'changed' in data[u'rolmembership']:
+ roles = (data[u'rolmembership'])[u'changed']
+
+ if type(roles) != list:
+ return precondition_required(msg)
+
+ for r in roles:
+ if (type(r) != dict or u'role' not in r or
+ u'admin' not in r):
+ return precondition_required(msg)
+
+ if not r[u'admin']:
+ data[u'revoked_admins'].append(r[u'role'])
+ else:
+ data[u'admins'].append(r[u'role'])
+
+ if self.manager.version >= 90200:
+ if u'seclabels' in data:
+ if u'rid' not in kwargs or kwargs['rid'] == -1:
+ msg = _("""
+Security Label must be passed as an array of JSON object in the following
+format:
+seclabels:[{
+ provider: <provider>,
+ label: <label>
+ },
+ ...
+]""")
+ if type(data[u'seclabels']) != list:
+ return precondition_required(msg)
+
+ for s in data[u'seclabels']:
+ if (type(s) != dict or u'provider' not in s or
+ u'label' not in s):
+ return precondition_required(msg)
+ else:
+ msg = _("""
+Security Label must be passed as an array of JSON object in the following
+format:
+seclabels:{
+ 'added': [{
+ provider: <provider>,
+ label: <label>
+ },
+ ...
+ ],
+ 'deleted': [{
+ provider: <provider>,
+ label: <label>
+ },
+ ...
+ ],
+ 'updated': [{
+ provider: <provider>,
+ label: <label>
+ },
+ ...
+ ]
+""")
+ seclabels = data[u'seclabels']
+ if type(seclabels) != dict:
+ return precondition_required(msg)
+
+ if u'added' in seclabels:
+ new_seclabels = seclabels[u'added']
+
+
+ if type(new_seclabels) != list:
+ return precondition_required(msg)
+
+ for s in new_seclabels:
+ if (type(s) != dict or u'provider' not in s or
+ u'label' not in s):
+ return precondition_required(msg)
+
+ if u'deleted' in seclabels:
+ removed_seclabels = seclabels[u'deleted']
+
+ if type(removed_seclabels) != list:
+ return precondition_required(msg)
+
+ for s in removed_seclabels:
+ if (type(s) != dict or u'provider' not in s):
+ return precondition_required(msg)
+
+ if u'changed' in seclabels:
+ changed_seclabels = seclabels[u'deleted']
+
+ if type(changed_seclabels) != list:
+ return precondition_required(msg)
+
+ for s in changed_seclabels:
+ if (type(s) != dict or u'provider' not in s
+ and u'label' not in s):
+ return precondition_required(msg)
+
+ if u'variables' in data:
+ if u'rid' not in kwargs or kwargs['rid'] == -1:
+ msg = _("""
+Configuration parameters/variables must be passed as an array of JSON object in
+the following format (create mode):
+variables:[{
+ database: <database> or null,
+ name: <configuration>,
+ value: <value>
+ },
+ ...
+]""")
+ if type(data[u'variables']) != list:
+ return precondition_required(msg)
+
+ for r in data[u'variables']:
+ if (type(r) != dict or
+ u'name' not in r or
+ u'value' not in r):
+ return precondition_required(msg)
+ else:
+ msg = _("""
+Configuration parameters/variables must be passed as an array of JSON object in
+the following format (update mode):
+rolmembership:{
+ 'added': [{
+ database: <database> or null,
+ name: <configuration>,
+ value: <value>
+ },
+ ...
+ ],
+ 'deleted': [{
+ database: <database> or null,
+ name: <configuration>,
+ value: <value>
+ },
+ ...
+ ],
+ 'updated': [{
+ database: <database> or null,
+ name: <configuration>,
+ value: <value>
+ },
+ ...
+ ]
+""")
+ variables = data[u'variables']
+ if type(variables) != dict:
+ return precondition_required(msg)
+
+ if u'added' in variables:
+ new_vars = variables[u'added']
+
+ if type(new_vars) != list:
+ return precondition_required(msg)
+
+ for v in new_vars:
+ if (type(v) != dict or u'name' not in v or
+ u'value' not in v):
+ return precondition_required(msg)
+
+ if u'deleted' in variables:
+ delete_vars = variables[u'deleted']
+
+ if type(delete_vars) != list:
+ return precondition_required(msg)
+
+ for v in delete_vars:
+ if type(v) != dict or u'name' not in v:
+ return precondition_required(msg)
+
+ if u'changed' in variables:
+ new_vars = variables[u'changed']
+
+ if type(new_vars) != list:
+ return precondition_required(msg)
+
+ for v in new_vars:
+ if (type(v) != dict or u'name' not in v or
+ u'value' not in v):
+ return precondition_required(msg)
+
+ self.request = data
+
+ return f(self, **kwargs)
+ return wrap
+
+ def check_precondition(action=None):
+ """
+ This function will behave as a decorator which will checks the status
+ of the database connection for the maintainance database of the server,
+ beforeexecuting rest of the operation for the wrapped function. It will
+ also attach manager, conn (maintenance connection for the server) as
+ properties of the instance.
+ """
+ def wrap(f):
+ @wraps(f)
+ def wrapped(self, **kwargs):
+ self.manager = get_driver(
+ PG_DEFAULT_DRIVER
+ ).connection_manager(
+ kwargs['sid']
+ )
+ self.conn = self.manager.connection()
+
+ if not self.conn.connected():
+ return precondition_required(
+ _("Connection to the server has been lost!")
+ )
+
+ ver = self.manager.version
+
+ self.sql_path = 'role/sql/{0}/'.format(
+ 'post9_4' if ver >= 90500 else \
+ 'post9_1' if ver >= 90200 else \
+ 'post9_0' if ver >= 90100 else \
+ 'post8_4'
+ )
+
+ self.alterKeys = [
+ u'rolcanlogin', u'rolsuper', u'rolcreatedb',
+ u'rolcreaterole', u'rolinherit', u'rolreplication',
+ u'rolconnlimit', u'rolvaliduntil', u'rolpassword'
+ ] if ver >= 90200 else [
+ u'rolcanlogin', u'rolsuper', u'rolcreatedb',
+ u'rolcreaterole', u'rolinherit', u'rolconnlimit',
+ u'rolvaliduntil', u'rolpassword'
+ ]
+
+ auth_tbl=False
+ check_permission=False
+ fetch_name=False
+ forbidden_msg = None
+
+ if action in ['list', 'properties']:
+ auth_tbl = True
+ elif action in ['drop', 'update']:
+ check_permission = True
+ fetch_name = True
+ if action == 'drop':
+ forbidden_msg = _(
+ "The current user does not have permission to drop the role!"
+ )
+ else:
+ forbidden_msg = _(
+ "The current user does not have permission to update the role!"
+ )
+ elif action == 'create':
+ check_permission = True
+ forbidden_msg = _(
+ "The current user does not have permission to create the role!"
+ )
+ elif (action == 'msql' and
+ 'rid' in kwargs and kwargs['rid'] != -1):
+ fetch_name = True
+
+ if auth_tbl:
+ status, res = self.conn.execute_scalar(
+ "SELECT has_table_privilege('pg_authid', 'SELECT')"
+ )
+
+ if not status:
+ return internal_server_error(
+ _(
+ "Error checking the permission to the pg_authid!\n{0}"
+ ).format(res)
+ )
+ self.role_tbl = 'pg_authid' if res else 'pg_roles'
+ else:
+ self.role_tbl = 'pg_roles'
+
+ if check_permission:
+ user = self.manager.user_info
+
+ if not user['is_superuser'] and not user['can_create_role']:
+ if (action != 'update' or
+ 'rid' in kwargs and kwargs['rid'] != -1 and
+ user['id'] != rid):
+ return forbidden(forbidden_msg)
+
+ if fetch_name:
+ status, res = self.conn.execute_2darray(
+ """
+SELECT
+ rolname, rolcanlogin, rolcatupdate, rolsuper
+FROM
+ pg_roles
+WHERE oid=%(rid)s::OID""",
+ {'rid': kwargs['rid']}
+ )
+
+ if not status:
+ return internal_server_error(
+ _(
+ "ERROR: fetching the role information!\n{0}"
+ ).format(res)
+ )
+
+ if len(res['rows']) == 0:
+ return gone(
+ _("Couldn't find the specific role in the database server!")
+ )
+
+ row = res['rows'][0]
+
+ self.role = row[0]
+ self.rolCanLogin = row[1]
+ self.rolCatUpdate = row[2]
+ self.rolSuper = row[3]
+
+ return f(self, **kwargs)
+ return wrapped
+ return wrap
+
+ @check_precondition(action='list')
+ def list(self, gid, sid):
+ status, res = self.conn.execute_dict(
+ render_template(self.sql_path + 'properties.sql',
+ role_tbl=self.role_tbl
+ )
+ )
+
+ if not status:
+ return internal_server_error(
+ _(
+ "Error fetching the roles from the database server!\n{0}"
+ ).format(res)
+ )
+
+ self.transform(res)
+
+ return ajax_response(
+ response=res['rows'],
+ status=200
+ )
+
+ @check_precondition(action='nodes')
+ def nodes(self, gid, sid):
+
+ status, rset = self.conn.execute_2darray(
+ render_template(self.sql_path + 'nodes.sql',
+ role_tbl=self.role_tbl
+ )
+ )
+
+ if not status:
+ return internal_server_error(
+ _(
+ "Error fetching the roles information from the database server!\n{0}"
+ ).format(res)
+ )
+
+ res = []
+ for row in rset['rows']:
+ res.append(
+ self.blueprint.generate_browser_node(
+ row['oid'],
+ row['rolname'],
+ 'icon-role' if row['rolcanlogin'] else 'icon-group',
+ can_login=row['rolcanlogin'],
+ is_superuser=row['rolsuper']
+ )
+ )
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition(action='node')
+ def node(self, gid, sid, rid):
+
+ status, rset = self.conn.execute_2darray(
+ render_template(self.sql_path + 'nodes.sql',
+ rid=rid,
+ role_tbl=self.role_tbl
+ )
+ )
+
+ if not status:
+ return internal_server_error(
+ _(
+ "Error fetching the roles information from the database server!\n{0}"
+ ).format(res)
+ )
+
+ for row in rset['rows']:
+ return make_json_response(
+ data=self.blueprint.generate_browser_node(
+ row['oid'],
+ row['rolname'],
+ 'icon-role' if row['rolcanlogin'] else 'icon-group',
+ can_login=row['rolcanlogin'],
+ is_superuser=row['rolsuper']
+ ),
+ status=200
+ )
+
+ return gone(_("Couldn't find the role information!"))
+
+ def transform(self, rset):
+ for row in rset['rows']:
+ res = []
+ roles = row['rolmembership']
+ row['rolpassword'] = ''
+ for role in roles:
+ role = re.search(r'([01])(.+)', role)
+ res.append({
+ 'role': role.group(2),
+ 'admin': True if role.group(1) == '1' else False
+ })
+ row['rolmembership'] = res
+ row['rolvaliduntil'] = row['rolvaliduntil'].isoformat() \
+ if isinstance(
+ row['rolvaliduntil'],
+ (datetime.date, datetime.datetime)
+ ) else None
+ if 'seclabels' in row and row['seclabels'] is not None:
+ res = []
+ for sec in row['seclabels']:
+ sec = re.search(r'([^=]+)=(.*$)', sec)
+ res.append({
+ 'provider': sec.group(1),
+ 'label': sec.group(2)
+ })
+
+ @check_precondition(action='properties')
+ def properties(self, gid, sid, rid):
+
+ status, res = self.conn.execute_dict(
+ render_template(self.sql_path + 'properties.sql',
+ role_tbl=self.role_tbl,
+ rid=rid
+ )
+ )
+
+ if not status:
+ return internal_server_error(
+ _(
+ "Error fetching the roles from the database server!\n{0}"
+ ).format(res)
+ )
+
+ self.transform(res)
+ if len(res['rows']) == 0:
+ return gone(_("Couldn't find the role information!"))
+
+ return ajax_response(
+ response=res['rows'][0],
+ status=200
+ )
+
+ @check_precondition(action='drop')
+ def drop(self, gid, sid, rid):
+
+ status, res = self.conn.execute_2darray(
+ "DROP ROLE {0};".format(self.role)
+ )
+ if not status:
+ return internal_server_error(
+ _("ERROR: Couldn't drop the user!\n{0}").format(res)
+ )
+
+ return success_return()
+
+ @check_precondition()
+ def sql(self, gid, sid, rid):
+ status, res = self.conn.execute_scalar(
+ render_template(
+ self.sql_path + 'sql.sql',
+ role_tbl=self.role_tbl
+ ),
+ dict({'rid':rid})
+ )
+
+ if not status:
+ return internal_server_error(
+ _("ERROR: Couldn't generate reversed engineered Query for the role/user!\n{0}").format(
+ res
+ )
+ )
+
+ if res is None:
+ return gone(
+ _("ERROR: Couldn't generate reversed engineered Query for the role/user!")
+ )
+
+ return ajax_response(response=res)
+
+ @check_precondition(action='create')
+ @validate_request
+ def create(self, gid, sid):
+
+ sql = render_template(
+ self.sql_path + 'create.sql',
+ data=self.request,
+ dummy=False,
+ conn=self.conn
+ )
+
+ status, msg = self.conn.execute_dict(sql)
+
+ if not status:
+ return internal_server_error(
+ _("ERROR: Couldn't create the role!\n{0}").format(msg)
+ )
+
+ status, rid = self.conn.execute_scalar(
+ "SELECT oid FROM {0} WHERE rolname = %(rolname)s".format(
+ self.role_tbl
+ ),
+ {'rolname': self.request[u'rolname']}
+ )
+
+ if not status:
+ return internal_server_error(
+ _("ERROR: Couldn't fetch the role information!\n{0}").format(msg)
+ )
+
+
+ status, rset = self.conn.execute_dict(
+ render_template(self.sql_path + 'nodes.sql',
+ rid=rid,
+ role_tbl=self.role_tbl
+ )
+ )
+
+ if not status:
+ return internal_server_error(
+ _(
+ "Error fetching the roles information from the database server!\n{0}"
+ ).format(res)
+ )
+ for row in rset['rows']:
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ rid,
+ row['rolname'],
+ 'icon-role' if row['rolcanlogin'] else 'icon-group',
+ can_login=row['rolcanlogin']
+ )
+ )
+
+ return gone(_("Couldn't find the role information!"))
+
+ @check_precondition(action='update')
+ @validate_request
+ def update(self, gid, sid, rid):
+
+ sql = render_template(
+ self.sql_path + 'update.sql',
+ data=self.request,
+ dummy=False,
+ conn=self.conn,
+ role=self.role,
+ rolCanLogin=self.rolCanLogin,
+ rolCatUpdate=self.rolCatUpdate,
+ rolSuper=self.rolSuper,
+ alterKeys=self.alterKeys
+ )
+
+ status, msg = self.conn.execute_dict(sql)
+
+ if not status:
+ return internal_server_error(
+ _("ERROR: Couldn't create the role!\n{0}").format(msg)
+ )
+
+ status, rset = self.conn.execute_dict(
+ render_template(self.sql_path + 'nodes.sql',
+ rid=rid,
+ role_tbl=self.role_tbl
+ )
+ )
+
+ if not status:
+ return internal_server_error(
+ _(
+ "Error fetching the roles information from the database server!\n{0}"
+ ).format(res)
+ )
+
+ for row in rset['rows']:
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ rid,
+ row['rolname'],
+ 'icon-role' if row['rolcanlogin'] else 'icon-group',
+ can_login=row['rolcanlogin'],
+ is_superuser=row['rolsuper']
+ )
+ )
+
+ return gone(_("Couldn't find the role information!"))
+
+ @check_precondition(action='msql')
+ @validate_request
+ def msql(self, gid, sid, rid=-1):
+ if rid == -1:
+ return make_json_response(
+ data=render_template(
+ self.sql_path + 'create.sql',
+ data=self.request,
+ dummy=True,
+ conn=self.conn
+ )
+ )
+ else:
+ return make_json_response(
+ data=render_template(
+ self.sql_path + 'update.sql',
+ data=self.request,
+ dummy=True,
+ conn=self.conn,
+ role=self.role,
+ rolCanLogin=self.rolCanLogin,
+ rolCatUpdate=self.rolCatUpdate,
+ rolSuper=self.rolSuper,
+ alterKeys=self.alterKeys
+ )
+ )
+
+ @check_precondition()
+ def dependencies(self, gid, sid, rid):
+ return not_implemented()
+
+ @check_precondition()
+ def dependents(self, gid, sid, rid):
+ return not_implemented()
+
+ @check_precondition()
+ def variables(self, gid, sid, rid):
+
+ status, rset = self.conn.execute_dict(
+ render_template(self.sql_path + 'variables.sql',
+ rid=rid
+ )
+ )
+
+ if not status:
+ return internal_server_error(
+ _(
+ "Error fetching the variable information for the given role!\n{0}"
+ ).format(rset)
+ )
+
+ return make_json_response(
+ data=rset['rows']
+ )
+
+ @check_precondition()
+ def voptions(self, gid, sid):
+
+ status, res = self.conn.execute_dict(
+ """
+SELECT
+ name, vartype, min_val, max_val, enumvals
+FROM
+ (
+ SELECT
+ 'role'::text AS name, 'string'::text AS vartype,
+ NULL AS min_val, NULL AS max_val, NULL::text[] AS enumvals
+ UNION ALL
+ SELECT
+ name, vartype, min_val::numeric AS min_val, max_val::numeric AS max_val, enumvals
+ FROM
+ pg_settings
+ WHERE
+ context in ('user', 'superuser')
+ ) a""")
+
+ if not status:
+ return internal_server_error(
+ _(
+ "Error fetching the variable options for role!\n{0}"
+ ).format(res)
+ )
+ return make_json_response(
+ data=res['rows']
+ )
+
+
+RoleView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/roles/static/img/coll-role.png b/web/pgadmin/browser/server_groups/servers/roles/static/img/coll-role.png
new file mode 100644
index 0000000000000000000000000000000000000000..f5c20bfb9df3fcfd5637142b5edadee88d16f1dd
GIT binary patch
literal 472
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbK}dw@@fE08{V^k{v3eRq4)g`;~e
z9NpL5-umO!!|$)2oIiTx`>Pk{j~?H@e}8v-M|4a~ZF5V0aY<frNo8Y8R7}kF?b~~%
zPQUW%!-W?gPCfg0<nhO%nugrm+}X2dH#9VCIdWpw#y!!|(PtjN-uK{RZh1{fRdwTp
z=?fPw3=It}uB@KF`_#M*+jrdiwDJ0z+|K(gK&LR41o;IsI6S+N2IO!SctjQhX%8@V
zJDF_<WOR7CIEF}E_U*eV)?~oLdiew&*D0mYiA-HP{`~(w(TrnnzGvDwUIq>q#;_yj
z3T>;K`{Y|Vq&xEjj%G~k6hHR+h!THhnaxw_$SGHMPMfZMPi)i9%XhNulY>gPzF~iB
z|KgKQLWq9XH}ze6r2ic?sZII+S2M0acw&}`vH8UR{h3j<5wpVPAM$;3o^cn?p;iC4
zi2>cDTH+c}l9E`GYL#4+3Zxi}3=9o)4UKdS3`2|ztW1opOiZ;6OsxzI=DpKfg`y!h
mKP5A*5~9Jt608BJ+QiDh45H!G^w2dx4Gf;HelF{r5}E*Jd&X1%
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/roles/static/img/group.png b/web/pgadmin/browser/server_groups/servers/roles/static/img/group.png
new file mode 100644
index 0000000000000000000000000000000000000000..819ff810f49f937fc3fc6d1d85a01a2f4bd81277
GIT binary patch
literal 804
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GXl47|R2ELR^8|RjXDN78WKY#xGqs
z@z#}{H?QnmvT)Luw`V_t(7Cy@d*WiE`Wp(j&uiH+zj;zq;iq@!rcLcQwWjyX+MaXk
zdM<A0nbK15<iX*^3nxWJM5HFhPG~GRysZ85rtSkvnvxO|mM@rj_u`4W7f&r)FefQ7
z@x-dOtDCzIEp6Pte}7U^{E|hJe!RN><JH6OuO5GY^=#RK`BPg8ZfxzE(N<8LmwI$l
z|EgK_QIX-_U%j|<@ltwHOhRnb^5x6d&TIbvVAcQo%L}s;zrT8Y=i-%!sIUpE;ujrB
z$}EbgsHnJeWaj_73s%jj{Qm0gvIUDy{mnS>J8i<M*r=$eU$>Y3zcY9Lin^r4q<Q-i
zkN-+tcRe9AH1y%&?wo>xRXcW1p1&wFH$NjcziZCY*_-yH=N4|hn{@O?@_|o@|8Gs7
zzIf@SmmkhP|9JB0$0LtF?tl1k_x+FEOJWXxOFZ;7;s5oCJ5OKOcK6eQ1DCSP!Vi9l
zpFMl_|7(3w5n-nuzukQM)4FT#A|gY-TX+irgPyS@$S;_|;n|HeAcwQSBeEDsdw{Xq
z$!t3y<B6w>V~E7%<OCo{NlHp_U^=6-Cg+XEp&lKXIWaMJZ1&XrVX{$RR0~sx59kPw
za0&5|S<=?o+wJWX<Q3%>=HoYyb4lx5Zzsn<&q&u$-&kgq1uK{8E?Kd7_453Lj1-<r
zNl960>sX#dM?^_RGygQ(R`!kWl*7KolQ(y-mS%3=-M)AEbgl{S-#&iy`g#6g0f85i
zB5Se|UOo{PdL=IQ?wr6wNfAj=Sz&4M`4eVLnIm*SKyZrG#A)*;&YU_|c>e6^@&^Sb
zMhOIRJQEk4{$#qF!4%gi;UP^wL$8`H6-?!E5L5{`#=v0l(|FM{-7kSa|EiX_MwFx^
zmZVxG7o`Fz1|tJQ16@NST?4}qBLgcFV=EI=Z39y)1A}?*^j4v0$jwj5Osj-wFt7w`
c0ID{zGBAT^I5j<V4NwDvr>mdKI;Vst0Cs_b9RL6T
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/roles/static/img/role.png b/web/pgadmin/browser/server_groups/servers/roles/static/img/role.png
new file mode 100644
index 0000000000000000000000000000000000000000..ef54e88ad5ed7543fe5cbc287ecd997a470026bd
GIT binary patch
literal 557
zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GbMf`2e2~S0Fue=FF_Dti5~p)>V~n
zU$^|pjdM?KT-dg5Rb5r(^)rWmyt@D6)x+<v9({lH<ocPDbyZd0Up@Q&>czHo>%PBw
z_4vlE@2}oGzH$5et9RShZM=5oLTyz|bWBWCOiWg8?y@c0w;Vm$K4Ef7dPZ`3Mswff
z75h)kT)RCrJ2xsSYVn56S6+R%`0~TK=O0c#`*{4x$0LtF9(?$5?}Lw%R&0rgif-=e
z*>&&J_Pd|TTYIKVnX+{A_APflO<1)fFE4NZ<(r#se_DL(c0@$PnWyhI-ukrR#)r_*
zP}ZdzDuG^NED7=pW^j0RBMr#mEbxdd2GSm2>~=ES4#=45>Eak7aoP3kOQC}XB5e;<
z?&j)-hPuA}y-&<_!tecRo<4o=?|(iw$CBe@qLaZd|I56WH_7rg%{wjLrFuR1SX;r)
zN2|4yyuz6mHL1pOhaT8gV%y~2r+ug6q4(v_%?4f3r`|5P{PrvR;fp_>pDZzp*9m`p
zwru<KhYM<K=C)qu{!!&!dE(uJ<HnC0_Z@i8|FkiXZ81+RqrZZf+3KyIRsbESTH+c}
zl9E`GYL#4+3Zxi}3=9o)4UKdS3`2|ztW1opOiZ;6OsxzI=DpKfg`y!hKP5A*5~9Jt
e608BJ+QiDh45H!G^w2dx4Gf;HelF{r5}E*2d<@$F
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/css/role.css b/web/pgadmin/browser/server_groups/servers/roles/templates/role/css/role.css
new file mode 100644
index 0000000..7145fec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/css/role.css
@@ -0,0 +1,27 @@
+.icon-role {
+ background-image: url('{{ url_for('NODE-role.static', filename='img/role.png')}}') !important;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
+.icon-group {
+ background-image: url('{{ url_for('NODE-role.static', filename='img/group.png')}}') !important;
+ background-repeat: no-repeat;
+ align-content: center;
+ vertical-align: middle;
+ height: 1.3em;
+}
+
+.pgadmin-node-select option[node="role"] {
+ background-image: url('{{ url_for('NODE-role.static', filename='img/role.png')}}') !important;
+ background-repeat: no-repeat;
+ background-position: center left;
+}
+
+.pgadmin-node-select option[node="group"] {
+ background-image: url('{{ url_for('NODE-role.static', filename='img/group.png')}}') !important;
+ background-repeat: no-repeat;
+ background-position: center left;
+}
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/js/role.js b/web/pgadmin/browser/server_groups/servers/roles/templates/role/js/role.js
new file mode 100644
index 0000000..d715f5d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/js/role.js
@@ -0,0 +1,450 @@
+define(
+ [
+ 'jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser',
+ 'alertify', 'pgadmin.backform', 'select2', 'pgadmin.browser.collection',
+ 'pgadmin.browser.node.ui', 'pgadmin.browser.server.variable'
+ ],
+function($, _, S, pgAdmin, pgBrowser, alertify, Backform) {
+
+ if (!pgBrowser.Nodes['coll-role']) {
+ var role = pgAdmin.Browser.Nodes['coll-role'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'role',
+ type: 'coll-role',
+ columns: [
+ 'rolname', 'rolvaliduntil', 'rolconnlimit', 'rolcanlogin',
+ 'rolsuper', 'rolcreaterole', 'rolcreatedb', 'rolcatupdate',
+ 'rolinherit', 'rolreplication'
+ ]
+ });
+ };
+
+ var SecurityModel = pgAdmin.Browser.SecurityModel =
+ pgAdmin.Browser.SecurityModel || pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ provider: null,
+ label: null
+ },
+ schema: [{
+ id: 'provider', label: '{{ _('Provider') }}',
+ type: 'text', disabled: false
+ },{
+ id: 'label', label: '{{ _('Security Label') }}',
+ type: 'text', disabled: false
+ }],
+ validate: function() {
+ var err = {},
+ errmsg = null,
+ data = this.toJSON();
+
+ if (_.isUndefined(data.provider) ||
+ _.isNull(data.provider) ||
+ String(data.provider).replace(/^\s+|\s+$/g, '') == '') {
+ var msg = '{{ _('Please specify the value for all the security providers.') }}';
+
+ this.errorModel.set('provider', msg);
+ return msg;
+ } else {
+ this.errorModel.unset('provider');
+ }
+
+ if (_.isUndefined(data.label) ||
+ _.isNull(data.label) ||
+ String(data.label).replace(/^\s+|\s+$/g, '') == '') {
+ var msg = '{{ _('Please specify the value for all the security providers.') }}' ;
+
+ this.errorModel.set('label', msg);
+ return msg;
+ } else {
+ this.errorModel.unset('label');
+ }
+
+ return null;
+ }
+ });
+
+ var switchOptions = {
+ 'onText': 'Yes', 'offText': 'No',
+ 'onColor': 'warning', 'offColor': 'primary',
+ 'size': 'small'
+ };
+
+ var RoleMembersControl = Backform.Control.extend({
+ defaults: _.defaults(
+ {extraClasses: ['col-xs-12 col-sm-12 col-md-12']},
+ Backform.NodeListByNameControl.prototype.defaults
+ ),
+ initialize: function() {
+ Backform.NodeListByNameControl.prototype.initialize.apply(this, arguments);
+ },
+ formatter: {
+ fromRaw: function (rawData, model) {
+ var res = _.isObject(rawData) ? rawData : JSON.parse(rawData);
+
+ return _.pluck(res, 'role');
+ },
+ toRaw: function (formattedData, model) {
+ return formattedData;
+ }
+ },
+ template: _.template([
+ '<label class="control-label col-xs-12"><%=label%></label>',
+ '<div class="pgadmin-controls col-xs-12">',
+ ' <select multiple="multiple" style="width:100%;" class="pgadmin-controls <%=extraClasses.join(\' \')%>" name="<%=name%>" value="<%-JSON.stringify(value)%>" <%=disabled ? "disabled" : ""%> <%=required ? "required" : ""%>>',
+ ' <% for (var i=0; i < options.length; i++) { %>',
+ ' <% var option = options[i]; %>',
+ ' <option value=<%-option.value%> data-icon=<%-option.image%> <%=value != null && _.indexOf(value, option.value) != -1 ? "selected" : ""%> <%=option.disabled ? "disabled=\'disabled\'" : ""%>><%-option.label%></option>',
+ ' <% } %>',
+ ' </select>',
+ '</div>',
+ '<div class="note <%=Backform.helpMessageClassName%> col-xs-12"></div>',
+ ].join("\n")),
+ events: {"change select": "onChange"},
+ getValueFromDOM: function() {
+ var res = [];
+
+ this.$el.find("select").find(':selected').each(function() {
+ res.push($(this).attr('value'));
+ });
+
+ return res;
+ },
+ render: function() {
+ Backform.SelectControl.prototype.render.apply(this, arguments);
+ var collection = this.model.get(this.field.get('name')),
+ formatState = function(opt) {
+ if (!opt.id) {
+ return opt.text;
+ }
+
+ var optimage = $(opt.element).data('icon');
+
+ if(!optimage){
+ return opt.text;
+ } else {
+ return $(
+ '<span><span class="wcTabIcon ' + optimage + '"/>' + opt.text + '</span>'
+ );
+ }
+ },
+ formatSelection = function (opt) {
+
+ if (!opt.id) {
+ return opt.text;
+ }
+
+ var optimage = $(opt.element).data('icon'),
+ grantUpdate = function(ev) {
+
+ _.each(collection.where({role: opt.id}), function(m) {
+ m.set('admin', $(ev.target).is(":checked"));
+ });
+
+ return false;
+ };
+
+ if(!optimage){
+ return opt.text;
+ } else {
+ var j = $('<span><span class="wcTabIcon ' + optimage + '"/>' + opt.text + ' <span><input type="checkbox"/></span></span>');
+
+ // Update the checkbox lazy
+ setTimeout(
+ function() {
+ _.each(collection.where({role: opt.id}), function(m) {
+ j.find('input').prop('checked', m.get('admin'));
+ });
+ }, 200);
+
+ (j.find('input')).on('change', grantUpdate);
+
+ return j;
+ }
+ };
+
+ this.$el.find('select').select2({
+ templateResult: formatState,
+ templateSelection: formatSelection,
+ multiple: true,
+ tags: true,
+ allowClear: true,
+ placeholder: "Select members",
+ width: 'style'
+ }).on("change", function(e) {
+ $(e.target).find(':selected').each(function() {
+ });
+ });
+
+ this.$el.find('div.note').html("<span>NOTE:<br/>In order to give grant options select the checkbox next to the roles.</span>");
+
+ return this;
+ },
+ onChange: function(e) {
+ var model = this.model,
+ $el = $(e.target),
+ attrArr = this.field.get("name").split('.'),
+ name = attrArr.shift(),
+ path = attrArr.join('.'),
+ vals = this.getValueFromDOM(),
+ collection = model.get(name),
+ removed = [];
+
+ this.stopListening(this.model, "change:" + name, this.render);
+
+ /*
+ * Iterate through all the values, and find out how many are already
+ * present in the collection.
+ */
+ collection.each(function(m) {
+ var role = m.get('role'),
+ idx = _.indexOf(vals, role);
+
+ if (idx > -1) {
+ vals.splice(idx, 1);
+ } else {
+ removed.push(role);
+ }
+ });
+
+ /*
+ * Adding new values
+ */
+ _.each(vals, function(v) {
+ collection.add({role: v});
+ });
+
+ /*
+ * Removing unwanted!
+ */
+ _.each(removed, function(v) {
+ collection.remove(collection.where({role: v}));
+ });
+
+ this.listenTo(this.model, "change:" + name, this.render);
+ }
+ });
+
+ if (!pgBrowser.Nodes['role']) {
+ var role = pgAdmin.Browser.Nodes['role'] = pgAdmin.Browser.Node.extend({
+ parent_type: 'server',
+ type: 'role',
+ label: '{{ _('Login/Group Role') }}',
+ hasSQL: true,
+ canDrop: true,
+ node_label: function(r) {
+ if (!r.can_login) {
+ return r.label + '(GROUP)';
+ }
+ return r.label;
+ },
+ node_image: function(r) {
+ return (r.can_login ? 'icon-role' : 'icon-group');
+ },
+ title: function(d) {
+ if (!d) {
+ return this.label;
+ }
+ if (d.can_login) {
+ return '{{ _('Login Role') }}' + ' - ' + d.label;
+ }
+ return '{{ _('Group Role') }}' + ' - ' + d.label;
+ },
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_role_on_server', node: 'server', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Login/Group Role...') }}',
+ icon: 'wcTabIcon icon-role', data: {action: 'create'},
+ enable: 'can_create_role'
+ },{
+ name: 'create_role_on_roles', node: 'coll-role', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Login/Group Role...') }}',
+ icon: 'wcTabIcon icon-role', data: {action: 'create'},
+ enable: 'can_create_role'
+ },{
+ name: 'create_role', node: 'role', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Login/Group Role...') }}',
+ icon: 'wcTabIcon icon-role', data: {action: 'create'},
+ enable: 'can_create_role'
+ }]);
+ },
+ can_create_role: function(node, item) {
+ var treeData = this.getTreeNodeHierarchy(item),
+ server = treeData['server'];
+
+ return server.connected && server.user.can_create_role;
+ },
+ model: pgAdmin.Browser.Node.Model.extend({
+ idAttribute: 'oid',
+ defaults: {
+ oid: null,
+ rolname: undefined,
+ rolcanlogin: false,
+ rolpassword: null,
+ rolconnlimit: -1,
+ rolsuper: false,
+ rolcreaterole: false,
+ rolcreatedb: false,
+ rolinherit: true,
+ rolcatupdate: false,
+ rolreplication: false,
+ rolmembership: [],
+ rolvaliduntil: null,
+ seclabels: [],
+ variables: []
+ },
+ schema: [{
+ id: 'rolname', label: '{{ _('Name') }}', type: 'text',
+ disabled: 'readonly'
+ },{
+ id: 'oid', label:'{{ _('Role ID') }}', cell: 'string',
+ editable: false, type: 'text', visible: true, disabled: true
+ },{
+ id: 'rolpassword', label: '{{ _('Password') }}', type: 'password',
+ group: '{{ _('Definition') }}', mode: ['edit', 'create'],
+ control: 'input', deps: ['rolcanlogin'], retype: true,
+ cell: 'string', disabled: function(m) {
+ if (!m.isNew()) {
+ var user = this.node_info.server.user;
+
+ return (!(user.is_superuser || user.can_create_role) &&
+ user.id != m.get('oid'));
+ }
+ return false;
+ }
+ },{
+ id: 'rolvaliduntil', disabled: 'readonly', type: 'date',
+ group: '{{ _('Definition') }}', label: '{{ _('Account Expires') }}',
+ cell: 'string', mode: ['properties', 'edit', 'create'], retype: true,
+ deps: ['rolcanlogin'], options: {}
+ },{
+ id: 'rolconnlimit', type: 'int', group: '{{ _('Definition') }}',
+ label: '{{ _('Connection Limit') }}', cell: 'number',
+ mode: ['properties', 'edit', 'create'], disabled: 'readonly'
+ },{
+ id: 'rolcanlogin', label:'{{ _('Can Login?') }}', type: 'switch',
+ group: '{{ _('Role Privileges') }}', options: switchOptions,
+ disabled: 'readonly'
+ },{
+ id: 'rolsuper', label:'{{ _('Super User') }}', type: 'switch',
+ group: '{{ _('Role Privileges') }}', options: switchOptions,
+ control: Backform.SwitchControl.extend({
+ onChange: function() {
+ Backform.SwitchControl.prototype.onChange.apply(this, arguments);
+
+ this.model.set('rolcatupdate', this.model.get('rolsuper'));
+ this.model.set('rolcreaterole', this.model.get('rolsuper'));
+ this.model.set('rolcreatedb', this.model.get('rolsuper'));
+ }
+ }),
+ disabled: 'readonly'
+ },{
+ id: 'rolcreaterole', label:'{{ _('Can Create Role?') }}',
+ group: '{{ _('Role Privileges') }}', type: 'switch',
+ options: switchOptions, disabled: 'readonly'
+ },{
+ id: 'description', label:'{{ _('Comments') }}', type: 'multiline',
+ group: null, mode: ['properties', 'edit', 'create'],
+ options: switchOptions, disabled: 'readonly'
+ },{
+ id: 'rolcreatedb', label:'{{ _('Can create databases?') }}',
+ group: '{{ _('Role Privileges') }}', type: 'switch',
+ options: switchOptions, disabled: 'readonly'
+ },{
+ id: 'rolcatupdate', label:'{{ _('Can update catalog?') }}',
+ type: 'switch', max_version: 90500, options: switchOptions,
+ group: '{{ _('Role Privileges') }}', disabled: function(m) {
+ return (m.get('read_only') || (!m.get('rolsuper')));
+ }
+ },{
+ id: 'rolinherit', group: '{{ _('Role Privileges') }}',
+ label:'{{ _('Inherit rights from the parent roles?') }}',
+ type: 'switch', options: switchOptions, disabled: 'readonly'
+ },{
+ id: 'rolreplication', group: '{{ _('Role Privileges') }}',
+ label:'{{ _('Can initiate streaming replication and backups?') }}',
+ type: 'switch', min_version: 90100, options: switchOptions,
+ disabled: 'readonly'
+ },{
+ id: 'rolmembership', label: '{{ _('Role Membership') }}',
+ group: '{{ _('Role Privileges') }}', type: 'collection',
+ cell: 'string', disabled: 'readonly',
+ mode: ['properties', 'edit', 'create'],
+ control: RoleMembersControl, model: pgBrowser.Node.Model.extend({
+ keys: ['role'],
+ defaults: {
+ role: undefined,
+ admin: false
+ }
+ }),
+ filter: function(d) {
+ return this.model.isNew() || (this.model.get('rolname') != d.label);
+ }
+ },{
+ id: 'variables', label: '{{ _('Variables') }}', type: 'collection',
+ group: '{{ _('Security') }}', hasDatabase: true, url: 'variables',
+ model: pgBrowser.Node.VariableModel, control: 'variable-collection',
+ mode: [ 'edit', 'create'], canAdd: true, canDelete: true,
+ url: "variables", disabled: 'readonly'
+ },{
+ id: 'seclabels', label: '{{ _('Security Labels') }}',
+ model: SecurityModel, editable: false, type: 'collection',
+ group: '{{ _('Security') }}', mode: ['edit', 'create'],
+ min_version: 90200, disabled: 'readonly', canAdd: true,
+ canEdit: false, canDelete: true, control: 'unique-col-collection'
+ }],
+ readonly: function(m) {
+ if (!m.has('read_only')) {
+ var user = this.node_info.server.user;
+
+ m.set('read_only', !(user.is_superuser || user.can_create_role));
+ }
+
+ return m.get('read_only');
+ },
+ validate: function()
+ {
+ var err = {},
+ errmsg,
+ seclabels = this.get('seclabels');
+
+ if (_.isUndefined(this.get('rolname')) || String(this.get('rolname')).replace(/^\s+|\s+$/g, '') == '') {
+ err['name'] = '{{ _('Name can be empty!') }}';
+ errmsg = errmsg || err['name'];
+ }
+
+ if (seclabels) {
+ var secLabelsErr;
+ for (var i = 0; i < seclabels.models.length && !secLabelsErr; i++) {
+ secLabelsErr = (seclabels.models[i]).validate.apply(seclabels.models[i]);
+ if (secLabelsErr) {
+ err['seclabels'] = secLabelsErr;
+ errmsg = errmsg || secLabelsErr;
+ }
+ }
+ }
+
+ this.errorModel.clear().set(err);
+
+ if (_.size(err)) {
+ this.trigger('on-status', {msg: errmsg});
+ return errmsg;
+ }
+
+ return null;
+ }
+ })
+ }),
+ sg = pgBrowser.Nodes['server-group'];
+ }
+
+ return pgBrowser.Nodes['role'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/create.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/create.sql
new file mode 100644
index 0000000..8ed6e17
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/create.sql
@@ -0,0 +1,49 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+CREATE {% if data.rolcanlogin %}USER{% else %}ROLE{% endif %} {{ conn|qtIdent(data.rolname) }} WITH{% if data.rolcanlogin and data.rolcanlogin is sameas True %}
+
+ LOGIN{% else %}
+
+ NOLOGIN{% endif %}{% if data.rolsuper %}
+
+ SUPERUSER{% else %}
+
+ NOSUPERUSER{% endif %}{% if data.rolcreatedb %}
+
+ CREATEDB{% else %}
+
+ NOCREATEDB{% endif %}{% if data.rolcreaterole %}
+
+ CREATEROLE{% else %}
+
+ NOCREATEROLE{% endif %}{% if data.rolinherit is sameas true %}
+
+ INHERIT{% else %}
+
+ NOINHERIT{% endif %}{% if data.rolreplication %}
+
+ REPLICATION{% else %}
+
+ NOREPLICATION{% endif %}{% if 'rolconnlimit' in data and data.rolconnlimit is number and data.rolconnlimit >= -1 %}
+
+ CONNECTION LIMIT {{ data.rolconnlimit }}{% endif %}{% if data.rolvaliduntil and data.rolvaliduntil is not none %}
+
+ VALID UNTIL {{ data.rolvaliduntil|qtLiteral }} {% endif %}{% if data.rolpassword %}
+
+ PASSWORD {% if data.rolpassword is none %}NULL{% else %}{% if dummy %}'xxxxxx'{% else %} {{ data.rolpassword | qtLiteral }}{% endif %}{% endif %}{% endif %};{% if data.rolsuper and data.rolcatupdate is sameas false %}
+
+
+UPDATE pg_authid SET rolcatupdate=false WHERE rolname = {{ data.rolname|qtLiteral }};{% endif %}{% if data.members and data.members|length > 0 %}
+
+
+GRANT {{ conn|qtIdent(data.members)|join(', ') }} TO {{ conn|qtIdent(data.rolname) }};{% endif %}{% if data.admins and data.admins|length > 0 %}
+
+GRANT {{ conn|qtIdent(data.admins)|join(', ') }} TO {{ conn|qtIdent(data.rolname) }} WITH ADMIN OPTION;{% endif %}{% if data.variables %}
+
+{% for var in data.variables %}
+
+{{ VARIABLE.APPLY(conn, var.database, data.rolname, var.name, var.value) }}
+{% endfor %}{% endif %}{% if data.description %}
+
+COMMENT ON ROLE {{ conn|qtIdent(data.rolname) }} IS {{ data.description|qtLiteral }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/nodes.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/nodes.sql
new file mode 100644
index 0000000..fcd4e48
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/nodes.sql
@@ -0,0 +1,8 @@
+SELECT
+ r.oid, r.rolname, r.rolcanlogin, r.rolsuper
+FROM
+ {{ role_tbl }} r
+{% if rid %}
+WHERE r.oid = {{ rid }}::int
+{% endif %}
+ORDER BY r.rolcanlogin, r.rolname
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/properties.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/properties.sql
new file mode 100644
index 0000000..f993663
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/properties.sql
@@ -0,0 +1,16 @@
+SELECT
+ r.oid, r.*,
+ pg_catalog.shobj_description(r.oid, 'pg_authid') AS description,
+ ARRAY(
+ SELECT
+ CASE WHEN am.admin_option THEN '1' ELSE '0' END || rm.rolname
+ FROM
+ (SELECT * FROM pg_auth_members WHERE member = r.oid) am
+ LEFT JOIN pg_catalog.pg_roles rm ON (rm.oid = am.roleid)
+ ) rolmembership
+FROM
+ {{ role_tbl }} r
+{% if rid %}
+WHERE r.oid = {{ rid }}::int
+{% endif %}
+ORDER BY r.rolcanlogin, r.rolname
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/sql.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/sql.sql
new file mode 100644
index 0000000..d54a0ff
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/sql.sql
@@ -0,0 +1,98 @@
+SELECT
+ array_to_string(array_agg(sql), E'\n\n')
+FROM
+(SELECT
+ CASE WHEN rolcanlogin THEN '-- User: ' ELSE '-- Role: ' END ||
+ pg_catalog.quote_ident(rolname) ||
+ E'\n-- DROP ' || CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END ||
+ pg_catalog.quote_ident(rolname) || E';\n\nCREATE ' ||
+ CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END ||
+ pg_catalog.quote_ident(rolname) || E' WITH\n ' ||
+ CASE WHEN rolcanlogin THEN 'LOGIN' ELSE 'NOLOGIN' END || E'\n ' ||
+ CASE WHEN rolcanlogin AND rolpassword LIKE 'md5%%' THEN 'ENCRYPTED PASSWORD ' || quote_literal(rolpassword) || E'\n ' ELSE '' END ||
+ CASE WHEN rolsuper THEN 'SUPERUSER' ELSE 'NOSUPERUSER' END || E'\n ' ||
+ CASE WHEN rolinherit THEN 'INHERIT' ELSE 'NOINHERIT' END || E'\n ' ||
+ CASE WHEN rolcreatedb THEN 'CREATEDB' ELSE 'NOCREATEDB' END || E'\n ' ||
+ CASE WHEN rolcreaterole THEN 'CREATEROLE' ELSE 'NOCREATEROLE' END || E'\n ' ||
+ CASE WHEN rolconnlimit > 0 THEN E'\n CONNECTION LIMIT ' || rolconnlimit ELSE '' END ||
+ CASE WHEN rolvaliduntil IS NOT NULL THEN E'\n VALID UNTIL ' || quote_literal(rolvaliduntil::text) ELSE ';' END ||
+ -- PostgreSQL < 9.5
+ CASE WHEN rolsuper AND NOT rolcatupdate THEN E'\n\nUPDATE pg_authid SET rolcatupdate=false WHERE rolname=' || pg_catalog.quote_literal(rolname) || ';' ELSE '' END AS sql
+FROM
+ {{ role_tbl }} r
+WHERE
+ r.oid=%(rid)s::OID
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n')
+FROM
+(SELECT
+ 'GRANT ' || array_to_string(array_agg(rolname), ', ') || ' TO ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) ||
+ CASE WHEN admin_option THEN ' WITH ADMIN OPTION;' ELSE ';' END sql
+FROM
+ (SELECT
+ quote_ident(r.rolname) AS rolname, m.admin_option AS admin_option
+ FROM
+ pg_auth_members m
+ LEFT JOIN pg_roles r ON (m.roleid = r.oid)
+ WHERE
+ m.member=%(rid)s::OID
+ ORDER BY
+ r.rolname
+ ) a
+GROUP BY admin_option) s)
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') sql
+FROM
+(SELECT
+ 'ALTER ' || CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END || pg_catalog.quote_ident(rolname) || ' SET ' || param || ' TO ' || CASE WHEN param IN ('search_path', 'temp_tablespaces') THEN value ELSE quote_literal(value) END || ';' AS sql
+FROM
+(SELECT
+ rolcanlogin, rolname, split_part(rolconfig, '=', 1) AS param, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value
+FROM
+ (SELECT
+ unnest(rolconfig) AS rolconfig, rolcanlogin, rolname
+ FROM
+ pg_catalog.pg_roles
+ WHERE
+ oid=%(rid)s::OID
+ ) r
+) a) b)
+-- PostgreSQL >= 9.0
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') AS sql
+FROM
+ (SELECT
+ 'ALTER ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) ||
+ ' IN DATABASE ' || pg_catalog.quote_ident(datname) ||
+ ' SET ' || param|| ' TO ' ||
+ CASE
+ WHEN param IN ('search_path', 'temp_tablespaces') THEN value
+ ELSE quote_literal(value)
+ END || ';' AS sql
+ FROM
+ (SELECT
+ datname, split_part(rolconfig, '=', 1) AS param, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value
+ FROM
+ (SELECT
+ d.datname, unnest(c.setconfig) AS rolconfig
+ FROM
+ (SELECT *
+ FROM
+ pg_catalog.pg_db_role_setting dr
+ WHERE
+ dr.setrole=%(rid)s::OID AND dr.setdatabase!=0) c
+ LEFT JOIN pg_catalog.pg_database d ON (d.oid = c.setdatabase)
+ ) a
+ ) b
+ ) d
+)
+UNION ALL
+(SELECT
+ 'COMMENT ON ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) || ' IS ' || pg_catalog.quote_literal(description) || ';' AS sql
+FROM
+ (SELECT pg_catalog.shobj_description(%(rid)s::OID, 'pg_authid') AS description) a
+WHERE
+ description IS NOT NULL)) AS a
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/update.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/update.sql
new file mode 100644
index 0000000..316b5d0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/update.sql
@@ -0,0 +1,110 @@
+{% import 'macros/variable.macros' as VARIABLE %}
+{% if 'rolname' in data %}
+{% set rolname=data.rolname %}
+ALTER{% if rolCanLogin %} USER {% else %} ROLE {% endif %}{{ conn|qtIdent(role) }}
+ RENAME TO {{ conn|qtIdent(rolname) }};
+
+{% else %}
+{% set rolname=role %}
+{% endif %}
+{% if data|hasAny(alterKeys) %}
+ALTER {% if rolCanLogin %}USER{% else %}ROLE{% endif %} {{ conn|qtIdent(rolname) }}{% if 'rolcanlogin' in data %}
+
+{% if data.rolcanlogin %}
+ LOGIN{% else %}
+ NOLOGIN{% endif %}{% endif %}{% if 'rolsuper' in data %}
+
+{% if data.rolsuper %}
+ SUPERUSER{% else %}
+ NOSUPERUSER{% endif %}{% endif %}{% if 'rolcreatedb' in data %}
+
+{% if data.rolcreatedb %}
+ CREATEDB{% else %}
+ NOCREATEDB{% endif %}{% endif %}{% if 'rolcreaterole' in data %}
+
+{% if data.rolcreaterole %}
+ CREATEROLE{% else %}
+ NOCREATEROLE{% endif %}{% endif %}{% if 'rolinherit' in data %}
+
+{% if data.rolinherit %}
+ INHERIT{% else %}
+ NOINHERIT{% endif %}{% endif %}{% if 'rolreplication' in data %}
+
+{% if data.rolreplication %}
+ REPLICATION{% else %}
+ NOREPLICATION{% endif %}{% endif %}{% if 'rolconnlimit' in data and data.rolconnlimit is number and data.rolconnlimit >= -1 %}
+
+ CONNECTION LIMIT {{ data.rolconnlimit }}
+{% endif %}{% if 'rolvaliduntil' in data and data.rolvaliduntil %}
+
+ VALID UNTIL {% if data.rolvaliduntil is not none %}{{ data.rolvaliduntil|qtLiteral }}{% else %}'infinity'
+{% endif %}{% endif %}{% if 'rolpassword' in data %}
+
+ PASSWORD{% if data.rolpassword is none %} NULL{% else %}{% if dummy %} 'xxxxxx'{% else %} {{ data.rolpassword|qtLiteral }}{% endif %}{% endif %}{% endif %};{% endif %}
+
+{% if
+ not rolSuper and
+ 'rolsuper' in data and
+ 'rolcatupdate' not in data and
+ data.rolsuper %}
+
+
+UPDATE pg_authid SET rolcatupdate=false WHERE rolname = {{ rolname|qtLiteral }};
+
+{% elif
+ rolSuper and
+ 'rolsuper' not in data and
+ 'rolcatupdate' in data %}
+{% if data.rolcatupdate %}
+
+
+UPDATE pg_authid SET rolcatupdate=true WHERE rolname = {{ rolname|qtLiteral }};
+
+{% else %}
+
+
+UPDATE pg_authid SET rolcatupdate=false WHERE rolname = {{ rolname|qtLiteral }};
+
+{% endif %}
+{% endif %}
+{% if 'revoked_admins' in data and
+ data.revoked_admins|length > 0
+%}
+
+-- Revoked the admin options from the members
+REVOKE ADMIN OPTION FOR {{ conn|qtIdent(data.revoked_admins)|join(', ') }} FROM {{ conn|qtIdent(rolname) }};{% endif %}{% if 'revoked' in data and data.revoked|length > 0 %}
+
+
+-- Following are no more the members
+REVOKE {{ conn|qtIdent(data.revoked)|join(', ') }} FROM {{ conn|qtIdent(rolname) }};{% endif %}{% if data.admins and data.admins|length > 0 %}
+
+-- Following are the new admins (or, existing members made admins)
+GRANT {{ conn|qtIdent(data.admins)|join(', ') }} TO {{ conn|qtIdent(rolname) }} WITH ADMIN OPTION;{% endif %}{% if data.members and data.members|length > 0 %}
+
+
+-- Following are the new members
+GRANT {{ conn|qtIdent(data.members)|join(', ') }} TO {{ conn|qtIdent(rolname) }};{% endif %}
+{% if 'variables' in data and data.variables|length > 0 %}
+{% set variables = data.variables %}
+{% if 'deleted' in variables and variables.deleted|length > 0 %}
+
+{% for var in variables.deleted %}
+{{ VARIABLE.RESET(conn, var.database, rolname, var.name) }}
+{% endfor %}{% endif %}
+{% if 'added' in variables and variables.added|length > 0 %}
+
+{% for var in variables.added %}
+{{ VARIABLE.APPLY(conn, var.database, rolname, var.name, var.value) }}
+{% endfor %}{% endif %}
+{% if 'changed' in variables and variables.changed|length > 0 %}
+
+{% for var in variables.changed %}
+{{ VARIABLE.APPLY(conn, var.database, rolname, var.name, var.value) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% if 'description' in data %}
+
+
+COMMENT ON ROLE {{ conn|qtIdent(rolname) }} IS {{ data.description|qtLiteral }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/variables.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/variables.sql
new file mode 100644
index 0000000..b8620c1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post8_4/variables.sql
@@ -0,0 +1,25 @@
+SELECT
+ split_part(rolconfig, '=', 1) AS name, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value, NULL::text AS database
+FROM
+ (SELECT
+ unnest(rolconfig) AS rolconfig, rolcanlogin, rolname
+ FROM
+ pg_catalog.pg_roles
+ WHERE
+ oid={{ rid|qtLiteral }}::OID
+ ) r
+
+UNION ALL
+SELECT
+ split_part(rolconfig, '=', 1) AS name, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value, datname AS database
+FROM
+ (SELECT
+ d.datname, unnest(c.setconfig) AS rolconfig
+ FROM
+ (SELECT *
+ FROM pg_catalog.pg_db_role_setting dr
+ WHERE
+ dr.setrole={{ rid|qtLiteral }}::OID AND dr.setdatabase!=0
+ ) c
+ LEFT JOIN pg_catalog.pg_database d ON (d.oid = c.setdatabase)
+ ) a;
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/create.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/create.sql
new file mode 100644
index 0000000..0feb5d0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/create.sql
@@ -0,0 +1,45 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+CREATE {% if data.rolcanlogin %}USER{% else %}ROLE{% endif %} {{ conn|qtIdent(data.rolname) }} WITH{% if data.rolcanlogin and data.rolcanlogin is sameas True %}
+
+ LOGIN{% else %}
+
+ NOLOGIN{% endif %}{% if data.rolsuper %}
+
+ SUPERUSER{% else %}
+
+ NOSUPERUSER{% endif %}{% if data.rolcreatedb %}
+
+ CREATEDB{% else %}
+
+ NOCREATEDB{% endif %}{% if data.rolcreaterole %}
+
+ CREATEROLE{% else %}
+
+ NOCREATEROLE{% endif %}{% if data.rolinherit is sameas true %}
+
+ INHERIT{% else %}
+
+ NOINHERIT{% endif %}{% if 'rolconnlimit' in data and data.rolconnlimit is number and data.rolconnlimit >= -1 %}
+
+ CONNECTION LIMIT {{ data.rolconnlimit }}{% endif %}{% if data.rolvaliduntil and data.rolvaliduntil is not none %}
+
+ VALID UNTIL {{ data.rolvaliduntil|qtLiteral }} {% endif %}{% if data.rolpassword %}
+
+ PASSWORD {% if data.rolpassword is none %}NULL{% else %}{% if dummy %}'xxxxxx'{% else %} {{ data.rolpassword | qtLiteral }}{% endif %}{% endif %}{% endif %};{% if data.rolsuper and data.rolcatupdate is sameas false %}
+
+
+UPDATE pg_authid SET rolcatupdate=false WHERE rolname = {{ data.rolname|qtLiteral }};{% endif %}{% if data.members and data.members|length > 0 %}
+
+
+GRANT {{ conn|qtIdent(data.members)|join(', ') }} TO {{ conn|qtIdent(data.rolname) }};{% endif %}{% if data.admins and data.admins|length > 0 %}
+
+GRANT {{ conn|qtIdent(data.admins)|join(', ') }} TO {{ conn|qtIdent(data.rolname) }} WITH ADMIN OPTION;{% endif %}{% if data.variables %}
+
+{% for var in data.variables %}
+
+{{ VARIABLE.APPLY(conn, var.database, data.rolname, var.name, var.value) }}
+{% endfor %}{% endif %}{% if data.description %}
+
+COMMENT ON ROLE {{ conn|qtIdent(data.rolname) }} IS {{ data.description|qtLiteral }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/nodes.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/nodes.sql
new file mode 100644
index 0000000..fcd4e48
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/nodes.sql
@@ -0,0 +1,8 @@
+SELECT
+ r.oid, r.rolname, r.rolcanlogin, r.rolsuper
+FROM
+ {{ role_tbl }} r
+{% if rid %}
+WHERE r.oid = {{ rid }}::int
+{% endif %}
+ORDER BY r.rolcanlogin, r.rolname
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/properties.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/properties.sql
new file mode 100644
index 0000000..f993663
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/properties.sql
@@ -0,0 +1,16 @@
+SELECT
+ r.oid, r.*,
+ pg_catalog.shobj_description(r.oid, 'pg_authid') AS description,
+ ARRAY(
+ SELECT
+ CASE WHEN am.admin_option THEN '1' ELSE '0' END || rm.rolname
+ FROM
+ (SELECT * FROM pg_auth_members WHERE member = r.oid) am
+ LEFT JOIN pg_catalog.pg_roles rm ON (rm.oid = am.roleid)
+ ) rolmembership
+FROM
+ {{ role_tbl }} r
+{% if rid %}
+WHERE r.oid = {{ rid }}::int
+{% endif %}
+ORDER BY r.rolcanlogin, r.rolname
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/sql.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/sql.sql
new file mode 100644
index 0000000..d54a0ff
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/sql.sql
@@ -0,0 +1,98 @@
+SELECT
+ array_to_string(array_agg(sql), E'\n\n')
+FROM
+(SELECT
+ CASE WHEN rolcanlogin THEN '-- User: ' ELSE '-- Role: ' END ||
+ pg_catalog.quote_ident(rolname) ||
+ E'\n-- DROP ' || CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END ||
+ pg_catalog.quote_ident(rolname) || E';\n\nCREATE ' ||
+ CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END ||
+ pg_catalog.quote_ident(rolname) || E' WITH\n ' ||
+ CASE WHEN rolcanlogin THEN 'LOGIN' ELSE 'NOLOGIN' END || E'\n ' ||
+ CASE WHEN rolcanlogin AND rolpassword LIKE 'md5%%' THEN 'ENCRYPTED PASSWORD ' || quote_literal(rolpassword) || E'\n ' ELSE '' END ||
+ CASE WHEN rolsuper THEN 'SUPERUSER' ELSE 'NOSUPERUSER' END || E'\n ' ||
+ CASE WHEN rolinherit THEN 'INHERIT' ELSE 'NOINHERIT' END || E'\n ' ||
+ CASE WHEN rolcreatedb THEN 'CREATEDB' ELSE 'NOCREATEDB' END || E'\n ' ||
+ CASE WHEN rolcreaterole THEN 'CREATEROLE' ELSE 'NOCREATEROLE' END || E'\n ' ||
+ CASE WHEN rolconnlimit > 0 THEN E'\n CONNECTION LIMIT ' || rolconnlimit ELSE '' END ||
+ CASE WHEN rolvaliduntil IS NOT NULL THEN E'\n VALID UNTIL ' || quote_literal(rolvaliduntil::text) ELSE ';' END ||
+ -- PostgreSQL < 9.5
+ CASE WHEN rolsuper AND NOT rolcatupdate THEN E'\n\nUPDATE pg_authid SET rolcatupdate=false WHERE rolname=' || pg_catalog.quote_literal(rolname) || ';' ELSE '' END AS sql
+FROM
+ {{ role_tbl }} r
+WHERE
+ r.oid=%(rid)s::OID
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n')
+FROM
+(SELECT
+ 'GRANT ' || array_to_string(array_agg(rolname), ', ') || ' TO ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) ||
+ CASE WHEN admin_option THEN ' WITH ADMIN OPTION;' ELSE ';' END sql
+FROM
+ (SELECT
+ quote_ident(r.rolname) AS rolname, m.admin_option AS admin_option
+ FROM
+ pg_auth_members m
+ LEFT JOIN pg_roles r ON (m.roleid = r.oid)
+ WHERE
+ m.member=%(rid)s::OID
+ ORDER BY
+ r.rolname
+ ) a
+GROUP BY admin_option) s)
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') sql
+FROM
+(SELECT
+ 'ALTER ' || CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END || pg_catalog.quote_ident(rolname) || ' SET ' || param || ' TO ' || CASE WHEN param IN ('search_path', 'temp_tablespaces') THEN value ELSE quote_literal(value) END || ';' AS sql
+FROM
+(SELECT
+ rolcanlogin, rolname, split_part(rolconfig, '=', 1) AS param, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value
+FROM
+ (SELECT
+ unnest(rolconfig) AS rolconfig, rolcanlogin, rolname
+ FROM
+ pg_catalog.pg_roles
+ WHERE
+ oid=%(rid)s::OID
+ ) r
+) a) b)
+-- PostgreSQL >= 9.0
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') AS sql
+FROM
+ (SELECT
+ 'ALTER ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) ||
+ ' IN DATABASE ' || pg_catalog.quote_ident(datname) ||
+ ' SET ' || param|| ' TO ' ||
+ CASE
+ WHEN param IN ('search_path', 'temp_tablespaces') THEN value
+ ELSE quote_literal(value)
+ END || ';' AS sql
+ FROM
+ (SELECT
+ datname, split_part(rolconfig, '=', 1) AS param, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value
+ FROM
+ (SELECT
+ d.datname, unnest(c.setconfig) AS rolconfig
+ FROM
+ (SELECT *
+ FROM
+ pg_catalog.pg_db_role_setting dr
+ WHERE
+ dr.setrole=%(rid)s::OID AND dr.setdatabase!=0) c
+ LEFT JOIN pg_catalog.pg_database d ON (d.oid = c.setdatabase)
+ ) a
+ ) b
+ ) d
+)
+UNION ALL
+(SELECT
+ 'COMMENT ON ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) || ' IS ' || pg_catalog.quote_literal(description) || ';' AS sql
+FROM
+ (SELECT pg_catalog.shobj_description(%(rid)s::OID, 'pg_authid') AS description) a
+WHERE
+ description IS NOT NULL)) AS a
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/update.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/update.sql
new file mode 100644
index 0000000..ba654eb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/update.sql
@@ -0,0 +1,106 @@
+{% import 'macros/variable.macros' as VARIABLE %}
+{% if 'rolname' in data %}
+{% set rolname=data.rolname %}
+ALTER{% if rolCanLogin %} USER {% else %} ROLE {% endif %}{{ conn|qtIdent(role) }}
+ RENAME TO {{ conn|qtIdent(rolname) }};
+
+{% else %}
+{% set rolname=role %}
+{% endif %}
+{% if data|hasAny(alterKeys) %}
+ALTER {% if rolCanLogin %}USER{% else %}ROLE{% endif %} {{ conn|qtIdent(rolname) }}{% if 'rolcanlogin' in data %}
+
+{% if data.rolcanlogin %}
+ LOGIN{% else %}
+ NOLOGIN{% endif %}{% endif %}{% if 'rolsuper' in data %}
+
+{% if data.rolsuper %}
+ SUPERUSER{% else %}
+ NOSUPERUSER{% endif %}{% endif %}{% if 'rolcreatedb' in data %}
+
+{% if data.rolcreatedb %}
+ CREATEDB{% else %}
+ NOCREATEDB{% endif %}{% endif %}{% if 'rolcreaterole' in data %}
+
+{% if data.rolcreaterole %}
+ CREATEROLE{% else %}
+ NOCREATEROLE{% endif %}{% endif %}{% if 'rolinherit' in data %}
+
+{% if data.rolinherit %}
+ INHERIT{% else %}
+ NOINHERIT{% endif %}{% endif %}{% if 'rolconnlimit' in data and data.rolconnlimit is number and data.rolconnlimit >= -1 %}
+
+ CONNECTION LIMIT {{ data.rolconnlimit }}
+{% endif %}{% if 'rolvaliduntil' in data and data.rolvaliduntil %}
+
+ VALID UNTIL {% if data.rolvaliduntil is not none %}{{ data.rolvaliduntil|qtLiteral }}{% else %}'infinity'
+{% endif %}{% endif %}{% if 'rolpassword' in data %}
+
+ PASSWORD{% if data.rolpassword is none %} NULL{% else %}{% if dummy %} 'xxxxxx'{% else %} {{ data.rolpassword|qtLiteral }}{% endif %}{% endif %}{% endif %};{% endif %}
+
+{% if
+ not rolSuper and
+ 'rolsuper' in data and
+ 'rolcatupdate' not in data and
+ data.rolsuper %}
+
+
+UPDATE pg_authid SET rolcatupdate=false WHERE rolname = {{ rolname|qtLiteral }};
+
+{% elif
+ rolSuper and
+ 'rolsuper' not in data and
+ 'rolcatupdate' in data %}
+{% if data.rolcatupdate %}
+
+
+UPDATE pg_authid SET rolcatupdate=true WHERE rolname = {{ rolname|qtLiteral }};
+
+{% else %}
+
+
+UPDATE pg_authid SET rolcatupdate=false WHERE rolname = {{ rolname|qtLiteral }};
+
+{% endif %}
+{% endif %}
+{% if 'revoked_admins' in data and
+ data.revoked_admins|length > 0
+%}
+
+-- Revoked the admin options from the members
+REVOKE ADMIN OPTION FOR {{ conn|qtIdent(data.revoked_admins)|join(', ') }} FROM {{ conn|qtIdent(rolname) }};{% endif %}{% if 'revoked' in data and data.revoked|length > 0 %}
+
+
+-- Following are no more the members
+REVOKE {{ conn|qtIdent(data.revoked)|join(', ') }} FROM {{ conn|qtIdent(rolname) }};{% endif %}{% if data.admins and data.admins|length > 0 %}
+
+-- Following are the new admins (or, existing members made admins)
+GRANT {{ conn|qtIdent(data.admins)|join(', ') }} TO {{ conn|qtIdent(rolname) }} WITH ADMIN OPTION;{% endif %}{% if data.members and data.members|length > 0 %}
+
+
+-- Following are the new members
+GRANT {{ conn|qtIdent(data.members)|join(', ') }} TO {{ conn|qtIdent(rolname) }};{% endif %}
+{% if 'variables' in data and data.variables|length > 0 %}
+{% set variables = data.variables %}
+{% if 'deleted' in variables and variables.deleted|length > 0 %}
+
+{% for var in variables.deleted %}
+{{ VARIABLE.RESET(conn, var.database, rolname, var.name) }}
+{% endfor %}{% endif %}
+{% if 'added' in variables and variables.added|length > 0 %}
+
+{% for var in variables.added %}
+{{ VARIABLE.APPLY(conn, var.database, rolname, var.name, var.value) }}
+{% endfor %}{% endif %}
+{% if 'changed' in variables and variables.changed|length > 0 %}
+
+{% for var in variables.changed %}
+{{ VARIABLE.APPLY(conn, var.database, rolname, var.name, var.value) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% if 'description' in data %}
+
+
+COMMENT ON ROLE {{ conn|qtIdent(rolname) }} IS {{ data.description|qtLiteral }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/variables.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/variables.sql
new file mode 100644
index 0000000..b8620c1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_0/variables.sql
@@ -0,0 +1,25 @@
+SELECT
+ split_part(rolconfig, '=', 1) AS name, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value, NULL::text AS database
+FROM
+ (SELECT
+ unnest(rolconfig) AS rolconfig, rolcanlogin, rolname
+ FROM
+ pg_catalog.pg_roles
+ WHERE
+ oid={{ rid|qtLiteral }}::OID
+ ) r
+
+UNION ALL
+SELECT
+ split_part(rolconfig, '=', 1) AS name, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value, datname AS database
+FROM
+ (SELECT
+ d.datname, unnest(c.setconfig) AS rolconfig
+ FROM
+ (SELECT *
+ FROM pg_catalog.pg_db_role_setting dr
+ WHERE
+ dr.setrole={{ rid|qtLiteral }}::OID AND dr.setdatabase!=0
+ ) c
+ LEFT JOIN pg_catalog.pg_database d ON (d.oid = c.setdatabase)
+ ) a;
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/create.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/create.sql
new file mode 100644
index 0000000..3dc80fb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/create.sql
@@ -0,0 +1,54 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+CREATE {% if data.rolcanlogin %}USER{% else %}ROLE{% endif %} {{ conn|qtIdent(data.rolname) }} WITH{% if data.rolcanlogin and data.rolcanlogin is sameas True %}
+
+ LOGIN{% else %}
+
+ NOLOGIN{% endif %}{% if data.rolsuper %}
+
+ SUPERUSER{% else %}
+
+ NOSUPERUSER{% endif %}{% if data.rolcreatedb %}
+
+ CREATEDB{% else %}
+
+ NOCREATEDB{% endif %}{% if data.rolcreaterole %}
+
+ CREATEROLE{% else %}
+
+ NOCREATEROLE{% endif %}{% if data.rolinherit is sameas true %}
+
+ INHERIT{% else %}
+
+ NOINHERIT{% endif %}{% if data.rolreplication %}
+
+ REPLICATION{% else %}
+
+ NOREPLICATION{% endif %}{% if 'rolconnlimit' in data and data.rolconnlimit is number and data.rolconnlimit >= -1 %}
+
+ CONNECTION LIMIT {{ data.rolconnlimit }}{% endif %}{% if data.rolvaliduntil and data.rolvaliduntil is not none %}
+
+ VALID UNTIL {{ data.rolvaliduntil|qtLiteral }} {% endif %}{% if data.rolpassword %}
+
+ PASSWORD {% if data.rolpassword is none %}NULL{% else %}{% if dummy %}'xxxxxx'{% else %} {{ data.rolpassword | qtLiteral }}{% endif %}{% endif %}{% endif %};{% if data.rolsuper and data.rolcatupdate is sameas false %}
+
+
+UPDATE pg_authid SET rolcatupdate=false WHERE rolname = {{ data.rolname|qtLiteral }};{% endif %}{% if data.members and data.members|length > 0 %}
+
+
+GRANT {{ conn|qtIdent(data.members)|join(', ') }} TO {{ conn|qtIdent(data.rolname) }};{% endif %}{% if data.admins and data.admins|length > 0 %}
+
+GRANT {{ conn|qtIdent(data.admins)|join(', ') }} TO {{ conn|qtIdent(data.rolname) }} WITH ADMIN OPTION;{% endif %}{% if data.seclabels and data.seclabels|length > 0 %}
+
+{% for r in data.seclabels %}
+
+{{ SECLABLE.APPLY(conn, 'ROLE', data.rolname, r.provider, r.label) }}
+{% endfor %}{% endif %}{% if data.variables %}
+
+{% for var in data.variables %}
+
+{{ VARIABLE.APPLY(conn, var.database, data.rolname, var.name, var.value) }}
+{% endfor %}{% endif %}{% if data.description %}
+
+COMMENT ON ROLE {{ conn|qtIdent(data.rolname) }} IS {{ data.description|qtLiteral }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/nodes.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/nodes.sql
new file mode 100644
index 0000000..fcd4e48
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/nodes.sql
@@ -0,0 +1,8 @@
+SELECT
+ r.oid, r.rolname, r.rolcanlogin, r.rolsuper
+FROM
+ {{ role_tbl }} r
+{% if rid %}
+WHERE r.oid = {{ rid }}::int
+{% endif %}
+ORDER BY r.rolcanlogin, r.rolname
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/properties.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/properties.sql
new file mode 100644
index 0000000..bb56ec4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/properties.sql
@@ -0,0 +1,17 @@
+SELECT
+ r.oid, r.*,
+ pg_catalog.shobj_description(r.oid, 'pg_authid') AS description,
+ ARRAY(
+ SELECT
+ CASE WHEN am.admin_option THEN '1' ELSE '0' END || rm.rolname
+ FROM
+ (SELECT * FROM pg_auth_members WHERE member = r.oid) am
+ LEFT JOIN pg_catalog.pg_roles rm ON (rm.oid = am.roleid)
+ ) rolmembership,
+ (SELECT array_agg(provider || '=' || label) FROM pg_shseclabel sl1 WHERE sl1.objoid=r.oid) AS seclabels
+FROM
+ {{ role_tbl }} r
+{% if rid %}
+WHERE r.oid = {{ rid }}::int
+{% endif %}
+ORDER BY r.rolcanlogin, r.rolname
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/sql.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/sql.sql
new file mode 100644
index 0000000..ab78baf
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/sql.sql
@@ -0,0 +1,119 @@
+SELECT
+ array_to_string(array_agg(sql), E'\n\n')
+FROM
+(SELECT
+ CASE WHEN rolcanlogin THEN '-- User: ' ELSE '-- Role: ' END ||
+ pg_catalog.quote_ident(rolname) ||
+ E'\n-- DROP ' || CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END ||
+ pg_catalog.quote_ident(rolname) || E';\n\nCREATE ' ||
+ CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END ||
+ pg_catalog.quote_ident(rolname) || E' WITH\n ' ||
+ CASE WHEN rolcanlogin THEN 'LOGIN' ELSE 'NOLOGIN' END || E'\n ' ||
+ CASE WHEN rolcanlogin AND rolpassword LIKE 'md5%%' THEN 'ENCRYPTED PASSWORD ' || quote_literal(rolpassword) || E'\n ' ELSE '' END ||
+ CASE WHEN rolsuper THEN 'SUPERUSER' ELSE 'NOSUPERUSER' END || E'\n ' ||
+ CASE WHEN rolinherit THEN 'INHERIT' ELSE 'NOINHERIT' END || E'\n ' ||
+ CASE WHEN rolcreatedb THEN 'CREATEDB' ELSE 'NOCREATEDB' END || E'\n ' ||
+ CASE WHEN rolcreaterole THEN 'CREATEROLE' ELSE 'NOCREATEROLE' END || E'\n ' ||
+ -- PostgreSQL >= 9.1
+ CASE WHEN rolreplication THEN 'REPLICATION' ELSE 'NOREPLICATION' END ||
+ CASE WHEN rolconnlimit > 0 THEN E'\n CONNECTION LIMIT ' || rolconnlimit ELSE '' END ||
+ CASE WHEN rolvaliduntil IS NOT NULL THEN E'\n VALID UNTIL ' || quote_literal(rolvaliduntil::text) ELSE ';' END ||
+ -- PostgreSQL < 9.5
+ CASE WHEN rolsuper AND NOT rolcatupdate THEN E'\n\nUPDATE pg_authid SET rolcatupdate=false WHERE rolname=' || pg_catalog.quote_literal(rolname) || ';' ELSE '' END AS sql
+FROM
+ {{ role_tbl }} r
+WHERE
+ r.oid=%(rid)s::OID
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n')
+FROM
+(SELECT
+ 'GRANT ' || array_to_string(array_agg(rolname), ', ') || ' TO ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) ||
+ CASE WHEN admin_option THEN ' WITH ADMIN OPTION;' ELSE ';' END sql
+FROM
+ (SELECT
+ quote_ident(r.rolname) AS rolname, m.admin_option AS admin_option
+ FROM
+ pg_auth_members m
+ LEFT JOIN pg_roles r ON (m.roleid = r.oid)
+ WHERE
+ m.member=%(rid)s::OID
+ ORDER BY
+ r.rolname
+ ) a
+GROUP BY admin_option) s)
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') sql
+FROM
+(SELECT
+ 'ALTER ' || CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END || pg_catalog.quote_ident(rolname) || ' SET ' || param || ' TO ' || CASE WHEN param IN ('search_path', 'temp_tablespaces') THEN value ELSE quote_literal(value) END || ';' AS sql
+FROM
+(SELECT
+ rolcanlogin, rolname, split_part(rolconfig, '=', 1) AS param, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value
+FROM
+ (SELECT
+ unnest(rolconfig) AS rolconfig, rolcanlogin, rolname
+ FROM
+ pg_catalog.pg_roles
+ WHERE
+ oid=%(rid)s::OID
+ ) r
+) a) b)
+-- PostgreSQL >= 9.0
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') AS sql
+FROM
+ (SELECT
+ 'ALTER ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) ||
+ ' IN DATABASE ' || pg_catalog.quote_ident(datname) ||
+ ' SET ' || param|| ' TO ' ||
+ CASE
+ WHEN param IN ('search_path', 'temp_tablespaces') THEN value
+ ELSE quote_literal(value)
+ END || ';' AS sql
+ FROM
+ (SELECT
+ datname, split_part(rolconfig, '=', 1) AS param, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value
+ FROM
+ (SELECT
+ d.datname, unnest(c.setconfig) AS rolconfig
+ FROM
+ (SELECT *
+ FROM
+ pg_catalog.pg_db_role_setting dr
+ WHERE
+ dr.setrole=%(rid)s::OID AND dr.setdatabase!=0) c
+ LEFT JOIN pg_catalog.pg_database d ON (d.oid = c.setdatabase)
+ ) a
+ ) b
+ ) d
+)
+UNION ALL
+(SELECT
+ 'COMMENT ON ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) || ' IS ' || pg_catalog.quote_literal(description) || ';' AS sql
+FROM
+ (SELECT pg_catalog.shobj_description(%(rid)s::OID, 'pg_authid') AS description) a
+WHERE
+ description IS NOT NULL)
+-- PostgreSQL >= 9.2
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') AS sql
+FROM
+ (SELECT
+ 'SECURITY LABEL FOR ' || provider ||
+ E'\n ON ROLE ' || pg_catalog.quote_ident(rolname) ||
+ E'\n IS ' || pg_catalog.quote_literal(label) || ';' AS sql
+ FROM
+ (SELECT
+ label, provider, rolname
+ FROM
+ (SELECT *
+ FROM
+ pg_shseclabel sl1
+ WHERE sl1.objoid=%(rid)s::OID) s
+ LEFT JOIN pg_catalog.pg_roles r ON (s.objoid=r.oid)) a) b
+)) AS a
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/update.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/update.sql
new file mode 100644
index 0000000..8ee9bdd
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/update.sql
@@ -0,0 +1,132 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{% if 'rolname' in data %}
+{% set rolname=data.rolname %}
+ALTER{% if rolCanLogin %} USER {% else %} ROLE {% endif %}{{ conn|qtIdent(role) }}
+ RENAME TO {{ conn|qtIdent(rolname) }};
+
+{% else %}
+{% set rolname=role %}
+{% endif %}
+{% if data|hasAny(alterKeys) %}
+ALTER {% if rolCanLogin %}USER{% else %}ROLE{% endif %} {{ conn|qtIdent(rolname) }}{% if 'rolcanlogin' in data %}
+
+{% if data.rolcanlogin %}
+ LOGIN{% else %}
+ NOLOGIN{% endif %}{% endif %}{% if 'rolsuper' in data %}
+
+{% if data.rolsuper %}
+ SUPERUSER{% else %}
+ NOSUPERUSER{% endif %}{% endif %}{% if 'rolcreatedb' in data %}
+
+{% if data.rolcreatedb %}
+ CREATEDB{% else %}
+ NOCREATEDB{% endif %}{% endif %}{% if 'rolcreaterole' in data %}
+
+{% if data.rolcreaterole %}
+ CREATEROLE{% else %}
+ NOCREATEROLE{% endif %}{% endif %}{% if 'rolinherit' in data %}
+
+{% if data.rolinherit %}
+ INHERIT{% else %}
+ NOINHERIT{% endif %}{% endif %}{% if 'rolreplication' in data %}
+
+{% if data.rolreplication %}
+ REPLICATION{% else %}
+ NOREPLICATION{% endif %}{% endif %}{% if 'rolconnlimit' in data and data.rolconnlimit is number and data.rolconnlimit >= -1 %}
+
+ CONNECTION LIMIT {{ data.rolconnlimit }}
+{% endif %}{% if 'rolvaliduntil' in data and data.rolvaliduntil %}
+
+ VALID UNTIL {% if data.rolvaliduntil is not none %}{{ data.rolvaliduntil|qtLiteral }}{% else %}'infinity'
+{% endif %}{% endif %}{% if 'rolpassword' in data %}
+
+ PASSWORD{% if data.rolpassword is none %} NULL{% else %}{% if dummy %} 'xxxxxx'{% else %} {{ data.rolpassword|qtLiteral }}{% endif %}{% endif %}{% endif %};{% endif %}
+
+{% if
+ not rolSuper and
+ 'rolsuper' in data and
+ 'rolcatupdate' not in data and
+ data.rolsuper %}
+
+
+UPDATE pg_authid SET rolcatupdate=false WHERE rolname = {{ rolname|qtLiteral }};
+
+{% elif
+ rolSuper and
+ 'rolsuper' not in data and
+ 'rolcatupdate' in data %}
+{% if data.rolcatupdate %}
+
+
+UPDATE pg_authid SET rolcatupdate=true WHERE rolname = {{ rolname|qtLiteral }};
+
+{% else %}
+
+
+UPDATE pg_authid SET rolcatupdate=false WHERE rolname = {{ rolname|qtLiteral }};
+
+{% endif %}
+{% endif %}
+{% if 'revoked_admins' in data and
+ data.revoked_admins|length > 0
+%}
+
+-- Revoked the admin options from the members
+REVOKE ADMIN OPTION FOR {{ conn|qtIdent(data.revoked_admins)|join(', ') }} FROM {{ conn|qtIdent(rolname) }};{% endif %}{% if 'revoked' in data and data.revoked|length > 0 %}
+
+
+-- Following are no more the members
+REVOKE {{ conn|qtIdent(data.revoked)|join(', ') }} FROM {{ conn|qtIdent(rolname) }};{% endif %}{% if data.admins and data.admins|length > 0 %}
+
+-- Following are the new admins (or, existing members made admins)
+GRANT {{ conn|qtIdent(data.admins)|join(', ') }} TO {{ conn|qtIdent(rolname) }} WITH ADMIN OPTION;{% endif %}{% if data.members and data.members|length > 0 %}
+
+
+-- Following are the new members
+GRANT {{ conn|qtIdent(data.members)|join(', ') }} TO {{ conn|qtIdent(rolname) }};{% endif %}{% if data.seclabels and
+ data.seclabels|length > 0
+%}{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'ROLE', data.rolname, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'ROLE', data.rolname, r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'ROLE', data.rolname, r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% if 'variables' in data and data.variables|length > 0 %}
+{% set variables = data.variables %}
+{% if 'deleted' in variables and variables.deleted|length > 0 %}
+
+{% for var in variables.deleted %}
+{{ VARIABLE.RESET(conn, var.database, rolname, var.name) }}
+{% endfor %}{% endif %}
+{% if 'added' in variables and variables.added|length > 0 %}
+
+{% for var in variables.added %}
+{{ VARIABLE.APPLY(conn, var.database, rolname, var.name, var.value) }}
+{% endfor %}{% endif %}
+{% if 'changed' in variables and variables.changed|length > 0 %}
+
+{% for var in variables.changed %}
+{{ VARIABLE.APPLY(conn, var.database, rolname, var.name, var.value) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% if 'description' in data %}
+
+
+COMMENT ON ROLE {{ conn|qtIdent(rolname) }} IS {{ data.description|qtLiteral }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/variables.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/variables.sql
new file mode 100644
index 0000000..b8620c1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_1/variables.sql
@@ -0,0 +1,25 @@
+SELECT
+ split_part(rolconfig, '=', 1) AS name, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value, NULL::text AS database
+FROM
+ (SELECT
+ unnest(rolconfig) AS rolconfig, rolcanlogin, rolname
+ FROM
+ pg_catalog.pg_roles
+ WHERE
+ oid={{ rid|qtLiteral }}::OID
+ ) r
+
+UNION ALL
+SELECT
+ split_part(rolconfig, '=', 1) AS name, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value, datname AS database
+FROM
+ (SELECT
+ d.datname, unnest(c.setconfig) AS rolconfig
+ FROM
+ (SELECT *
+ FROM pg_catalog.pg_db_role_setting dr
+ WHERE
+ dr.setrole={{ rid|qtLiteral }}::OID AND dr.setdatabase!=0
+ ) c
+ LEFT JOIN pg_catalog.pg_database d ON (d.oid = c.setdatabase)
+ ) a;
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/create.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/create.sql
new file mode 100644
index 0000000..ebfe6ce
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/create.sql
@@ -0,0 +1,51 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+CREATE {% if data.rolcanlogin %}USER{% else %}ROLE{% endif %} {{ conn|qtIdent(data.rolname) }} WITH{% if data.rolcanlogin and data.rolcanlogin is sameas True %}
+
+ LOGIN{% else %}
+
+ NOLOGIN{% endif %}{% if data.rolsuper %}
+
+ SUPERUSER{% else %}
+
+ NOSUPERUSER{% endif %}{% if data.rolcreatedb %}
+
+ CREATEDB{% else %}
+
+ NOCREATEDB{% endif %}{% if data.rolcreaterole %}
+
+ CREATEROLE{% else %}
+
+ NOCREATEROLE{% endif %}{% if data.rolinherit is sameas true %}
+
+ INHERIT{% else %}
+
+ NOINHERIT{% endif %}{% if data.rolreplication %}
+
+ REPLICATION{% else %}
+
+ NOREPLICATION{% endif %}{% if 'rolconnlimit' in data and data.rolconnlimit is number and data.rolconnlimit >= -1 %}
+
+ CONNECTION LIMIT {{ data.rolconnlimit }}{% endif %}{% if data.rolvaliduntil and data.rolvaliduntil is not none %}
+
+ VALID UNTIL {{ data.rolvaliduntil|qtLiteral }} {% endif %}{% if data.rolpassword %}
+
+ PASSWORD {% if data.rolpassword is none %}NULL{% else %}{% if dummy %}'xxxxxx'{% else %} {{ data.rolpassword | qtLiteral }}{% endif %}{% endif %}{% endif %};{% if data.members and data.members|length > 0 %}
+
+
+GRANT {{ conn|qtIdent(data.members)|join(', ') }} TO {{ conn|qtIdent(data.rolname) }};{% endif %}{% if data.admins and data.admins|length > 0 %}
+
+GRANT {{ conn|qtIdent(data.admins)|join(', ') }} TO {{ conn|qtIdent(data.rolname) }} WITH ADMIN OPTION;{% endif %}{% if data.seclabels and data.seclabels|length > 0 %}
+
+{% for r in data.seclabels %}
+
+{{ SECLABLE.APPLY(conn, 'ROLE', data.rolname, r.provider, r.label) }}
+{% endfor %}{% endif %}{% if data.variables %}
+
+{% for var in data.variables %}
+
+{{ VARIABLE.APPLY(conn, var.database, data.rolname, var.name, var.value) }}
+{% endfor %}{% endif %}{% if data.description %}
+
+COMMENT ON ROLE {{ conn|qtIdent(data.rolname) }} IS {{ data.description|qtLiteral }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/nodes.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/nodes.sql
new file mode 100644
index 0000000..fcd4e48
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/nodes.sql
@@ -0,0 +1,8 @@
+SELECT
+ r.oid, r.rolname, r.rolcanlogin, r.rolsuper
+FROM
+ {{ role_tbl }} r
+{% if rid %}
+WHERE r.oid = {{ rid }}::int
+{% endif %}
+ORDER BY r.rolcanlogin, r.rolname
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/properties.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/properties.sql
new file mode 100644
index 0000000..bb56ec4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/properties.sql
@@ -0,0 +1,17 @@
+SELECT
+ r.oid, r.*,
+ pg_catalog.shobj_description(r.oid, 'pg_authid') AS description,
+ ARRAY(
+ SELECT
+ CASE WHEN am.admin_option THEN '1' ELSE '0' END || rm.rolname
+ FROM
+ (SELECT * FROM pg_auth_members WHERE member = r.oid) am
+ LEFT JOIN pg_catalog.pg_roles rm ON (rm.oid = am.roleid)
+ ) rolmembership,
+ (SELECT array_agg(provider || '=' || label) FROM pg_shseclabel sl1 WHERE sl1.objoid=r.oid) AS seclabels
+FROM
+ {{ role_tbl }} r
+{% if rid %}
+WHERE r.oid = {{ rid }}::int
+{% endif %}
+ORDER BY r.rolcanlogin, r.rolname
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/sql.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/sql.sql
new file mode 100644
index 0000000..81fe40b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/sql.sql
@@ -0,0 +1,117 @@
+SELECT
+ array_to_string(array_agg(sql), E'\n\n')
+FROM
+(SELECT
+ CASE WHEN rolcanlogin THEN '-- User: ' ELSE '-- Role: ' END ||
+ pg_catalog.quote_ident(rolname) ||
+ E'\n-- DROP ' || CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END ||
+ pg_catalog.quote_ident(rolname) || E';\n\nCREATE ' ||
+ CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END ||
+ pg_catalog.quote_ident(rolname) || E' WITH\n ' ||
+ CASE WHEN rolcanlogin THEN 'LOGIN' ELSE 'NOLOGIN' END || E'\n ' ||
+ CASE WHEN rolcanlogin AND rolpassword LIKE 'md5%%' THEN 'ENCRYPTED PASSWORD ' || quote_literal(rolpassword) || E'\n ' ELSE '' END ||
+ CASE WHEN rolsuper THEN 'SUPERUSER' ELSE 'NOSUPERUSER' END || E'\n ' ||
+ CASE WHEN rolinherit THEN 'INHERIT' ELSE 'NOINHERIT' END || E'\n ' ||
+ CASE WHEN rolcreatedb THEN 'CREATEDB' ELSE 'NOCREATEDB' END || E'\n ' ||
+ CASE WHEN rolcreaterole THEN 'CREATEROLE' ELSE 'NOCREATEROLE' END || E'\n ' ||
+ -- PostgreSQL >= 9.1
+ CASE WHEN rolreplication THEN 'REPLICATION' ELSE 'NOREPLICATION' END ||
+ CASE WHEN rolconnlimit > 0 THEN E'\n CONNECTION LIMIT ' || rolconnlimit ELSE '' END ||
+ CASE WHEN rolvaliduntil IS NOT NULL THEN E'\n VALID UNTIL ' || quote_literal(rolvaliduntil::text) ELSE ';' END AS sql
+FROM
+ {{ role_tbl }} r
+WHERE
+ r.oid=%(rid)s::OID
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n')
+FROM
+(SELECT
+ 'GRANT ' || array_to_string(array_agg(rolname), ', ') || ' TO ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) ||
+ CASE WHEN admin_option THEN ' WITH ADMIN OPTION;' ELSE ';' END sql
+FROM
+ (SELECT
+ quote_ident(r.rolname) AS rolname, m.admin_option AS admin_option
+ FROM
+ pg_auth_members m
+ LEFT JOIN pg_roles r ON (m.roleid = r.oid)
+ WHERE
+ m.member=%(rid)s::OID
+ ORDER BY
+ r.rolname
+ ) a
+GROUP BY admin_option) s)
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') sql
+FROM
+(SELECT
+ 'ALTER ' || CASE WHEN rolcanlogin THEN 'USER ' ELSE 'ROLE ' END || pg_catalog.quote_ident(rolname) || ' SET ' || param || ' TO ' || CASE WHEN param IN ('search_path', 'temp_tablespaces') THEN value ELSE quote_literal(value) END || ';' AS sql
+FROM
+(SELECT
+ rolcanlogin, rolname, split_part(rolconfig, '=', 1) AS param, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value
+FROM
+ (SELECT
+ unnest(rolconfig) AS rolconfig, rolcanlogin, rolname
+ FROM
+ pg_catalog.pg_roles
+ WHERE
+ oid=%(rid)s::OID
+ ) r
+) a) b)
+-- PostgreSQL >= 9.0
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') AS sql
+FROM
+ (SELECT
+ 'ALTER ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) ||
+ ' IN DATABASE ' || pg_catalog.quote_ident(datname) ||
+ ' SET ' || param|| ' TO ' ||
+ CASE
+ WHEN param IN ('search_path', 'temp_tablespaces') THEN value
+ ELSE quote_literal(value)
+ END || ';' AS sql
+ FROM
+ (SELECT
+ datname, split_part(rolconfig, '=', 1) AS param, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value
+ FROM
+ (SELECT
+ d.datname, unnest(c.setconfig) AS rolconfig
+ FROM
+ (SELECT *
+ FROM
+ pg_catalog.pg_db_role_setting dr
+ WHERE
+ dr.setrole=%(rid)s::OID AND dr.setdatabase!=0) c
+ LEFT JOIN pg_catalog.pg_database d ON (d.oid = c.setdatabase)
+ ) a
+ ) b
+ ) d
+)
+UNION ALL
+(SELECT
+ 'COMMENT ON ROLE ' || pg_catalog.quote_ident(pg_get_userbyid(%(rid)s::OID)) || ' IS ' || pg_catalog.quote_literal(description) || ';' AS sql
+FROM
+ (SELECT pg_catalog.shobj_description(%(rid)s::OID, 'pg_authid') AS description) a
+WHERE
+ description IS NOT NULL)
+-- PostgreSQL >= 9.2
+UNION ALL
+(SELECT
+ array_to_string(array_agg(sql), E'\n') AS sql
+FROM
+ (SELECT
+ 'SECURITY LABEL FOR ' || provider ||
+ E'\n ON ROLE ' || pg_catalog.quote_ident(rolname) ||
+ E'\n IS ' || pg_catalog.quote_literal(label) || ';' AS sql
+ FROM
+ (SELECT
+ label, provider, rolname
+ FROM
+ (SELECT *
+ FROM
+ pg_shseclabel sl1
+ WHERE sl1.objoid=%(rid)s::OID) s
+ LEFT JOIN pg_catalog.pg_roles r ON (s.objoid=r.oid)) a) b
+)) AS a
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/update.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/update.sql
new file mode 100644
index 0000000..7ad498f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/update.sql
@@ -0,0 +1,107 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{% if 'rolname' in data %}
+{% set rolname=data.rolname %}
+ALTER{% if rolCanLogin %} USER {% else %} ROLE {% endif %}{{ conn|qtIdent(role) }}
+ RENAME TO {{ conn|qtIdent(rolname) }};
+
+{% else %}
+{% set rolname=role %}
+{% endif %}
+{% if data|hasAny(alterKeys) %}
+ALTER {% if rolCanLogin %}USER{% else %}ROLE{% endif %} {{ conn|qtIdent(rolname) }}{% if 'rolcanlogin' in data %}
+
+{% if data.rolcanlogin %}
+ LOGIN{% else %}
+ NOLOGIN{% endif %}{% endif %}{% if 'rolsuper' in data %}
+
+{% if data.rolsuper %}
+ SUPERUSER{% else %}
+ NOSUPERUSER{% endif %}{% endif %}{% if 'rolcreatedb' in data %}
+
+{% if data.rolcreatedb %}
+ CREATEDB{% else %}
+ NOCREATEDB{% endif %}{% endif %}{% if 'rolcreaterole' in data %}
+
+{% if data.rolcreaterole %}
+ CREATEROLE{% else %}
+ NOCREATEROLE{% endif %}{% endif %}{% if 'rolinherit' in data %}
+
+{% if data.rolinherit %}
+ INHERIT{% else %}
+ NOINHERIT{% endif %}{% endif %}{% if 'rolreplication' in data %}
+
+{% if data.rolreplication %}
+ REPLICATION{% else %}
+ NOREPLICATION{% endif %}{% endif %}{% if 'rolconnlimit' in data and data.rolconnlimit is number and data.rolconnlimit >= -1 %}
+
+ CONNECTION LIMIT {{ data.rolconnlimit }}
+{% endif %}{% if 'rolvaliduntil' in data and data.rolvaliduntil %}
+
+ VALID UNTIL {% if data.rolvaliduntil is not none %}{{ data.rolvaliduntil|qtLiteral }}{% else %}'infinity'
+{% endif %}{% endif %}{% if 'rolpassword' in data %}
+
+ PASSWORD{% if data.rolpassword is none %} NULL{% else %}{% if dummy %} 'xxxxxx'{% else %} {{ data.rolpassword|qtLiteral }}{% endif %}{% endif %}{% endif %};{% endif %}
+
+{% if 'revoked_admins' in data and
+ data.revoked_admins|length > 0
+%}
+
+-- Revoked the admin options from the members
+REVOKE ADMIN OPTION FOR {{ conn|qtIdent(data.revoked_admins)|join(', ') }} FROM {{ conn|qtIdent(rolname) }};{% endif %}{% if 'revoked' in data and data.revoked|length > 0 %}
+
+
+-- Following are no more the members
+REVOKE {{ conn|qtIdent(data.revoked)|join(', ') }} FROM {{ conn|qtIdent(rolname) }};{% endif %}{% if data.admins and data.admins|length > 0 %}
+
+-- Following are the new admins (or, existing members made admins)
+GRANT {{ conn|qtIdent(data.admins)|join(', ') }} TO {{ conn|qtIdent(rolname) }} WITH ADMIN OPTION;{% endif %}{% if data.members and data.members|length > 0 %}
+
+
+-- Following are the new members
+GRANT {{ conn|qtIdent(data.members)|join(', ') }} TO {{ conn|qtIdent(rolname) }};{% endif %}{% if data.seclabels and
+ data.seclabels|length > 0
+%}{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'ROLE', data.rolname, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'ROLE', data.rolname, r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'ROLE', data.rolname, r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% if 'variables' in data and data.variables|length > 0 %}
+{% set variables = data.variables %}
+{% if 'deleted' in variables and variables.deleted|length > 0 %}
+
+{% for var in variables.deleted %}
+{{ VARIABLE.RESET(conn, var.database, rolname, var.name) }}
+{% endfor %}{% endif %}
+{% if 'added' in variables and variables.added|length > 0 %}
+
+{% for var in variables.added %}
+{{ VARIABLE.APPLY(conn, var.database, rolname, var.name, var.value) }}
+{% endfor %}{% endif %}
+{% if 'changed' in variables and variables.changed|length > 0 %}
+
+{% for var in variables.changed %}
+{{ VARIABLE.APPLY(conn, var.database, rolname, var.name, var.value) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% if 'description' in data %}
+
+
+COMMENT ON ROLE {{ conn|qtIdent(rolname) }} IS {{ data.description|qtLiteral }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/variables.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/variables.sql
new file mode 100644
index 0000000..b8620c1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/role/sql/post9_4/variables.sql
@@ -0,0 +1,25 @@
+SELECT
+ split_part(rolconfig, '=', 1) AS name, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value, NULL::text AS database
+FROM
+ (SELECT
+ unnest(rolconfig) AS rolconfig, rolcanlogin, rolname
+ FROM
+ pg_catalog.pg_roles
+ WHERE
+ oid={{ rid|qtLiteral }}::OID
+ ) r
+
+UNION ALL
+SELECT
+ split_part(rolconfig, '=', 1) AS name, replace(rolconfig, split_part(rolconfig, '=', 1) || '=', '') AS value, datname AS database
+FROM
+ (SELECT
+ d.datname, unnest(c.setconfig) AS rolconfig
+ FROM
+ (SELECT *
+ FROM pg_catalog.pg_db_role_setting dr
+ WHERE
+ dr.setrole={{ rid|qtLiteral }}::OID AND dr.setdatabase!=0
+ ) c
+ LEFT JOIN pg_catalog.pg_database d ON (d.oid = c.setdatabase)
+ ) a;
diff --git a/web/pgadmin/browser/static/js/datamodel.js b/web/pgadmin/browser/static/js/datamodel.js
index 4dc290e..9385adf 100644
--- a/web/pgadmin/browser/static/js/datamodel.js
+++ b/web/pgadmin/browser/static/js/datamodel.js
@@ -218,7 +218,12 @@ function(_, pgAdmin, $, Backbone) {
set: function(key, val, options) {
var opts = _.isObject(key) ? val : options;
+ this._changing = true;
+ this._previousAttributes = _.clone(this.attributes);
+ this.changed = {};
+
var res = Backbone.Model.prototype.set.call(this, key, val, options);
+ this._changing = false;
if ((opts&& opts.intenal) || !this.trackChanges) {
return true;
@@ -233,7 +238,7 @@ function(_, pgAdmin, $, Backbone) {
return;
}
attrs[k] = v;
- if (self.origSessAttrs[k] == v) {
+ if (_.isEqual(self.origSessAttrs[k], v)) {
delete self.sessAttrs[k];
} else {
self.sessAttrs[k] = v;
@@ -248,7 +253,9 @@ function(_, pgAdmin, $, Backbone) {
}
self.trigger('pgadmin-session:set', self, attrs);
-
+ if (!options || !options.silent) {
+ self.trigger('change', self, options);
+ }
if ('validate' in self && typeof(self['validate']) === 'function') {
var msg = self.validate(_.keys(attrs));
@@ -770,17 +777,15 @@ function(_, pgAdmin, $, Backbone) {
isAlreadyInvalid = (_.size(self.sessAttrs['invalid']) != 0),
idx = self.objFindInSession(obj, 'deleted');
- if ('validate' in obj && typeof(obj.validate) === 'function') {
- msg = obj.validate();
-
- if (msg) {
- self.sessAttrs['invalid'].push(obj);
- }
- }
-
// Hmm.. - it was originally deleted from this collection, we should
// remove it from the 'deleted' list.
if (idx >= 0) {
+ var origObj = self.sessAttrs['deleted'][idx];
+
+ obj.origSessAttrs = _.clone(origObj.origSessAttrs);
+ obj.attributes = _.extend(obj.attributes, origObj.attributes);
+ obj.sessAttrs = _.clone(origObj.sessAttrs);
+
self.sessAttrs['deleted'].splice(idx, 1);
// It has been changed originally!
@@ -790,6 +795,14 @@ function(_, pgAdmin, $, Backbone) {
(self.handler || self).trigger('pgadmin-session:added', self, obj);
+ if ('validate' in obj && typeof(obj.validate) === 'function') {
+ msg = obj.validate();
+
+ if (msg) {
+ self.sessAttrs['invalid'].push(obj);
+ }
+ }
+
/*
* If the collection was already invalid, we don't need to inform the
* parent, or raise the event for the invalid status.
@@ -804,6 +817,13 @@ function(_, pgAdmin, $, Backbone) {
return true;
}
+ if ('validate' in obj && typeof(obj.validate) === 'function') {
+ msg = obj.validate();
+
+ if (msg) {
+ self.sessAttrs['invalid'].push(obj);
+ }
+ }
self.sessAttrs['added'].push(obj);
/*
@@ -840,6 +860,7 @@ function(_, pgAdmin, $, Backbone) {
}
idx = self.objFindInSession(obj, 'added');
+
// Hmm - it was newly added, we can safely remove it.
if (idx >= 0) {
self.sessAttrs['added'].splice(idx, 1);
view thread (7+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: PATCH: Login/Group Role Node
In-Reply-To: <CAG7mmowKAv8XR0A0F+sUYmcBLwg+oz+ouBQByor2m+YQ_bbzXw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox