Hi, Please find the attached patches:
Database node patch (depends on privilege_macro, node_ui_typo, variable_js patches) *How to use controls:* *1] Privilege control:* *a]* Data expected from server in following format: eg: * {grantee: public, privileges: Tc* , grantor: postgres} should be converted to below object* (data parsing logic can be found in databases/__init__.py and browser/utils.py) "<model_key>": [{"grantor": "postgres", "privileges": [ {"with_grant": false, "privilege_type": "T", "privilege": true }, {"with_grant": true, "privilege_type": "c", "privilege": true }], "grantee": "public" }] *b]* Privilege control field schema: When using this control extend PrivilegeRoleModel with actual privileges you want to show in your control. See privilege.js for all supported privileges and their mapping. { id: 'datacl', label: '{{ _('Privileges') }}', model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend( {privileges: ['C', 'T', 'c']}), uniqueCol : ['grantee', 'grantor'], editable: false, type: 'collection', group: '{{ _('Security') }}', mode: ['properties', 'edit', 'create'], canAdd: true, canDelete: true, control: 'unique-col-collection', } *2] Variable control:* * a]* Data expected from server: "variables": [{"database": "db", "value": "ser", "name": "application_name", "role": "postgres"}, {"database": "db" , "value": "on", "name": "enable_seqscan", "role": "postgres"}] *b] Variable control field schema:* Note: database and role fields are optional are not included in variable control unless specified in field schema. Use below flag to enable them * hasDatabase: true and hasRole:true (both can not be used at same time)* (In below example you can see hasRole:true as I need role field in variable control for database node) { id: 'variables', label: '{{ _('Variables') }}', type: 'collection', model: pgAdmin.Browser.Node.VariableModel, editable: false, group: '{{ _('Security') }}', mode: ['properties', 'edit', 'create'], canAdd: true, canEdit: false, canDelete: true, hasRole: true, control: Backform.VariableCollectionControl, node: 'role' } Let me know if you have any queries regarding how to use above contorls. -- *Harshal Dhumal* *Software Engineer * EenterpriseDB <http://www.enterprisedb.com>
diff --git a/web/pgadmin/browser/server_groups/servers/static/js/privilege.js b/web/pgadmin/browser/server_groups/servers/static/js/privilege.js index e1d3b76..b89e676 100644 --- a/web/pgadmin/browser/server_groups/servers/static/js/privilege.js +++ b/web/pgadmin/browser/server_groups/servers/static/js/privilege.js @@ -272,6 +272,9 @@ $(self.$el.find('input[type=checkbox]')).on('blur',function() { self.$el.blur(); }); + + $(self.$el).pgMakeVisible('backform-tab'); + self.delegateEvents(); return this;
diff --git a/web/pgadmin/browser/server_groups/servers/templates/macros/default_privilege.macros b/web/pgadmin/browser/server_groups/servers/templates/macros/default_privilege.macros new file mode 100644 index 0000000..81fb07d --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/templates/macros/default_privilege.macros @@ -0,0 +1,14 @@ +{% macro APPLY(conn, type, role, priv, with_grant) -%} +{% if priv %} +ALTER DEFAULT PRIVILEGES +GRANT {{ priv }} ON {{ type }} TO {{ self.conn|qtIdent(role) }}; +{% endif %} +{% if with_grant %} +ALTER DEFAULT PRIVILEGES +GRANT {{ with_grant }} ON {{ type }} TO {{ self.conn|qtIdent(role) }} WITH GRANT OPTION; +{% endif %} +{%- endmacro %} +{% macro RESETALL(conn, type, role) -%} +ALTER DEFAULT PRIVILEGES + REVOKE ALL ON {{ type }} FROM {{ self.conn|qtIdent(role) }}; +{%- endmacro %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/templates/macros/privilege.macros b/web/pgadmin/browser/server_groups/servers/templates/macros/privilege.macros new file mode 100644 index 0000000..08d53a4 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/templates/macros/privilege.macros @@ -0,0 +1,11 @@ +{% macro APPLY(conn, type, role, param, priv, with_grant) -%} +{% if priv %} +GRANT {{ priv }} ON {{ type }} {{ self.conn|qtIdent(param) }} TO {{ self.conn|qtIdent(role) }}; +{% endif %} +{% if with_grant %} +GRANT {{ with_grant }} ON {{ type }} {{ self.conn|qtIdent(param) }} TO {{ self.conn|qtIdent(role) }} WITH GRANT OPTION; +{% endif %} +{%- endmacro %} +{% macro RESETALL(conn, type, role, param) -%} +REVOKE ALL ON {{ type }} {{ self.conn|qtIdent(param) }} FROM {{ self.conn|qtIdent(role) }}; +{%- endmacro %} \ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/static/js/variable.js b/web/pgadmin/browser/server_groups/servers/static/js/variable.js index 2ead1a0..8ee77f1 100644 --- a/web/pgadmin/browser/server_groups/servers/static/js/variable.js +++ b/web/pgadmin/browser/server_groups/servers/static/js/variable.js @@ -198,13 +142,22 @@ ), initialize: function(opts) { - var self = this; + var self = this, + uniqueCol = ['name']; + + self.hasDatabase = opts.field.get('hasDatabase'); + self.hasRole = opts.field.get('hasRole'); + if (self.hasDatabase) { + uniqueCol.push('database') + } else if (self.hasRole) { + uniqueCol.push('role') + } // Overriding the uniqueCol in the field if (opts && opts.field) { if (opts.field instanceof Backform.Field) { opts.field.set({ - uniqueCol: ['name', 'role', 'database'], + uniqueCol: uniqueCol || self.uniqueCol, model: pgNode.VariableModel }, { @@ -212,7 +165,7 @@ }); } else { opts.field.extend({ - uniqueCol: ['name', 'role', 'database'], + uniqueCol: uniqueCol || self.uniqueCol, model: pgNode.VariableModel }); } @@ -222,8 +175,7 @@ self, arguments ); - self.hasDatabase = self.field.get('hasDatabase'); - self.hasRole = self.field.get('hasRole'); + self.availVariables = {}; var node = self.field.get('node').type, @@ -566,7 +518,7 @@ checkVars.push('database'); } - if (self.role) { + if (self.hasRole) { checkVars.push('role'); } @@ -618,4 +570,4 @@ }); return VariableModel; -})); +})); \ No newline at end of file
diff --git a/web/pgadmin/browser/static/js/node.ui.js b/web/pgadmin/browser/static/js/node.ui.js index 9eb7e26..f2d04a2 100644 --- a/web/pgadmin/browser/static/js/node.ui.js +++ b/web/pgadmin/browser/static/js/node.ui.js @@ -401,7 +401,7 @@ function($, _, pgAdmin, Backbone, Backform, Alertify, Node) { optionValues = _.clone(this.optionValues || _.isFunction(this.column.get('options')) ? this.column.get('options').apply(this) : - this.column.get(' options')), + this.column.get('options')), select2_opts = _.defaults({}, col.select2, this.defaults.select2), evalF = function(f, col, m) { return (_.isFunction(f) ? !!f.apply(col, [m]) : !!f);
diff --git a/web/pgadmin/browser/server_groups/servers/databases/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/__init__.py new file mode 100644 index 0000000..900337b --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/__init__.py @@ -0,0 +1,715 @@ +########################################################################## +# +# pgAdmin 4 - PostgreSQL Tools +# +# Copyright (C) 2013 - 2016, The pgAdmin Development Team +# This software is released under the PostgreSQL Licence +# +########################################################################## +import json +from flask import render_template, make_response, current_app, request, jsonify +from flask.ext.babel import gettext +from pgadmin.utils.ajax import make_json_response, \ + make_response as ajax_response, internal_server_error +from pgadmin.browser.utils import NodeView, parse_db_privileges +from pgadmin.browser.collection import CollectionNodeModule +import pgadmin.browser.server_groups.servers as servers +from pgadmin.utils.ajax import precondition_required +from pgadmin.utils.driver import get_driver +from config import PG_DEFAULT_DRIVER +from functools import wraps + +class DatabaseModule(CollectionNodeModule): + NODE_TYPE = 'database' + COLLECTION_LABEL = gettext("Databases") + + def __init__(self, *args, **kwargs): + self.min_ver = None + self.max_ver = None + + super(DatabaseModule, 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 servers.ServerModule.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, + _=gettext + ), + render_template( + "databases/css/database.css", + node_type=self.node_type, + _=gettext + ) + ] + + for submodule in self.submodules: + snippets.extend(submodule.csssnippets) + + return snippets + + +blueprint = DatabaseModule(__name__) + + +class DatabaseView(NodeView): + node_type = blueprint.node_type + + parent_ids = [ + {'type': 'int', 'id': 'gid'}, + {'type': 'int', 'id': 'sid'} + ] + ids = [ + {'type': 'int', 'id': 'did'} + ] + + operations = dict({ + 'obj': [ + {'get': 'properties', 'delete': 'delete', 'put': 'update'}, + {'get': 'list', 'post': 'create'} + ], + 'nodes': [{'get': 'node'}, {'get': 'nodes'}], + 'sql': [{'get': 'sql'}], + 'msql': [{'get': 'msql'}, {'get': 'msql'}], + 'stats': [{'get': 'statistics'}], + 'dependency': [{'get': 'dependencies'}], + 'dependent': [{'get': 'dependents'}], + 'children': [{'get': 'children'}], + 'module.js': [{}, {}, {'get': 'module_js'}], + 'connect': [{ + 'get': 'connect_status', 'post': 'connect', 'delete': 'disconnect' + }], + 'get_encodings': [{'get': 'getEncodings'}, {'get': 'getEncodings'}], + 'get_ctypes': [{'get': 'getCtypes'}, {'get': 'getCtypes'}], + 'vopts': [{}, {'get': 'variable_options'}] + }) + + def check_precondition(f): + """ + This function will behave as a decorator which will checks + database connection before running view, it will also attaches + manager,conn & template_path properties to self + """ + @wraps(f) + def wrap(*args, **kwargs): + # Here args[0] will hold self & kwargs will hold gid,sid,did + self = args[0] + self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(kwargs['sid']) + if 'did' in kwargs: + self.conn = self.manager.connection(did=kwargs['did']) + else: + self.conn = self.manager.connection() + # If DB not connected then return error to browser + if not self.conn.connected(): + return precondition_required( + gettext( + "Connection to the server has been lost!" + ) + ) + + ver = self.manager.version + server_type = self.manager.server_type + # we will set template path for sql scripts + if ver >= 90200: + self.template_path = 'databases/sql/9.2_plus' + else: + self.template_path = 'databases/sql/9.1_plus' + return f(*args, **kwargs) + + return wrap + + + @check_precondition + def list(self, gid, sid): + SQL = render_template("/".join([self.template_path, 'properties.sql'])) + status, res = self.conn.execute_dict(SQL) + + if not status: + return internal_server_error(errormsg=res) + + return ajax_response( + response=res['rows'], + status=200 + ) + + @check_precondition + def nodes(self, gid, sid): + res = [] + SQL = render_template("/".join([self.template_path, 'get_nodes.sql'])) + status, rset = self.conn.execute_2darray(SQL) + + if not status: + return internal_server_error(errormsg=rest) + + for row in rset['rows']: + if self.manager.db == row['name']: + connected=True + canDisConn=False + else: + conn=self.manager.connection(row['name']) + connected=conn.connected() + canDisConn=True + + + res.append( + self.blueprint.generate_browser_node( + row['did'], + row['name'], + icon="icon-database-not-connected" if not connected \ + else "pg-icon-database", + connected=connected, + tablespace=row['spcname'], + allowConn=row['datallowconn'], + canCreate=row['cancreate'], + canDisconn=canDisConn + ) + ) + + return make_json_response( + data=res, + status=200 + ) + + @check_precondition + def node(self, gid, sid, did): + SQL = render_template("/".join([self.template_path, 'get_nodes.sql']), did=did) + status, rset = self.conn.execute_2darray(SQL) + + if not status: + return internal_server_error(errormsg=rest) + + for row in rset['rows']: + if self.manager.db == row['name']: + connected=True + else: + conn=self.manager.connection(row['name']) + connected=self.conn.connected() + return make_json_response( + data=self.blueprint.generate_browser_node( + row['did'], + row['name'], + icon="icon-database-not-connected" if not connected \ + else "pg-icon-database", + connected=connected, + spcname=row['spcname'], + allowConn=row['datallowconn'], + canCreate=row['cancreate'] + ), + status=200 + ) + + @check_precondition + def properties(self, gid, sid, did): + SQL = render_template("/".join([self.template_path, 'properties.sql']), did=did) + + status, res = self.conn.execute_dict(SQL) + + if not status: + return internal_server_error(errormsg=res) + + SQL = render_template("/".join([self.template_path, 'acl.sql']), did=did) + status, dataclres = self.conn.execute_dict(SQL) + if not status: + return internal_server_error(errormsg=res) + + for row in dataclres['rows']: + priv = parse_db_privileges(row) + if row['deftype'] in res['rows'][0]: + res['rows'][0][row['deftype']].append(priv) + else: + res['rows'][0][row['deftype']] = [priv] + + SQL = render_template("/".join([self.template_path, 'defacl.sql']), did=did) + status, defaclres = self.conn.execute_dict(SQL) + if not status: + return internal_server_error(errormsg=res) + + for row in defaclres['rows']: + priv = parse_db_privileges(row) + if row['deftype'] in res['rows'][0]: + res['rows'][0][row['deftype']].append(priv) + else: + res['rows'][0][row['deftype']] = [priv] + + result = res['rows'][0] + # Fetching variable for database + SQL = render_template("/".join([self.template_path, 'get_variables.sql']), did=did) + + status, res1 = self.conn.execute_dict(SQL) + + if not status: + return internal_server_error(errormsg=res1) + # sending result to formtter + frmtd_reslt = self.formatter(result, res1) + # mergeing formated result with main result again + result.update(frmtd_reslt) + return ajax_response( + response=result, + status=200 + ) + + @staticmethod + def formatter(result, varibles_rset): + """ We will use this function to format our output for + security label & variables""" + frmtd_result = dict() + sec_lbls = [] + if 'seclabels' in result and result['seclabels'] is not None: + for sec in result['seclabels']: + sec = re.search(r'([^=]+)=(.*$)', sec) + sec_lbls.append({ + 'provider': sec.group(1), + 'security_label': sec.group(2) + }) + + frmtd_result.update({"seclabels" :sec_lbls}) + + variablesLst = [] + for row in varibles_rset['rows']: + for d in row['setconfig']: + var_name, var_value = d.split("=") + # Because we save as boolean string in db so it needs conversion + if var_value == 'false' or var_value == 'off': + var_value = False + variablesLst.append({'role': row['user_name'], 'name': var_name, 'value': var_value, 'database': row['db_name']}) + frmtd_result.update({"variables" : variablesLst}) + # returning final result + return frmtd_result + + + def module_js(self): + """ + This property defines (if javascript) exists for this node. + Override this property for your own logic. + """ + return make_response( + render_template( + "databases/js/databases.js", + _=gettext + ), + 200, {'Content-Type': 'application/x-javascript'} + ) + + + def connect(self, gid, sid, did): + """Connect the Database.""" + from pgadmin.utils.driver import get_driver + manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid) + conn = manager.connection(did=did, auto_reconnect=True) + status, errmsg = conn.connect() + + if not status: + current_app.logger.error( + "Could not connected to database(#{0}).\nError: {2}".format( + did, errmsg + ) + ) + + return internal_server_error(errmsg) + else: + current_app.logger.info('Connection Established for Database Id: \ + %s' % (did)) + + return make_json_response( + success=1, + info=gettext("Database Connected."), + data={ + 'icon': 'pg-icon-database', + 'connected': True + } + ) + + def disconnect(self, gid, sid, did): + """Disconnect the database.""" + + # Release Connection + from pgadmin.utils.driver import get_driver + manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid) + + status = manager.release(did=did) + + if not status: + return unauthorized(gettext("Database Could Not Disconnect.")) + else: + return make_json_response( + success=1, + info=gettext("Database Disconnected."), + data={ + 'icon': 'icon-database-not-connected', + 'connected': False + } + ) + + @check_precondition + def getEncodings(self, gid, sid, did=None): + """ + This function to return list of avialable encodings + """ + res = [{ 'label': '', 'value': '' }] + try: + SQL = render_template("/".join([self.template_path, 'get_encodings.sql'])) + status, rset = self.conn.execute_2darray(SQL) + if not status: + return internal_server_error(errormsg=res) + + for row in rset['rows']: + res.append( + { 'label': row['encoding'], 'value': row['encoding'] } + ) + + return make_json_response( + data=res, + status=200 + ) + + except Exception as e: + return internal_server_error(errormsg=str(e)) + + @check_precondition + def getCtypes(self, gid, sid, did=None): + """ + This function to return list of available collation/character types + """ + res = [{ 'label': '', 'value': '' }] + default_list = ['C', 'POSIX'] + for val in default_list: + res.append( + {'label': val, 'value': val} + ) + try: + SQL = render_template("/".join([self.template_path, 'get_ctypes.sql'])) + status, rset = self.conn.execute_2darray(SQL) + if not status: + return internal_server_error(errormsg=res) + + for row in rset['rows']: + if row['cname'] not in default_list: + res.append( + { 'label': row['cname'], 'value': row['cname'] } + ) + + return make_json_response( + data=res, + status=200 + ) + + except Exception as e: + return internal_server_error(errormsg=str(e)) + + @check_precondition + def create(self, gid, sid): + """Create the database.""" + required_args = [ + u'name' + ] + + data = request.form if request.form else json.loads(request.data.decode()) + + for arg in required_args: + if arg not in data: + return make_json_response( + status=410, + success=0, + errormsg=gettext( + "Couldn't find the required parameter (%s)." % arg + ) + ) + try: + # The below SQL will execute CREATE DDL only + SQL = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn) + status, msg = self.conn.execute_scalar(SQL) + if not status: + return internal_server_error(errormsg=msg) + + if 'datacl' in data: + data['datacl'] = self.parse_privileges(data['datacl']) + + # The below SQL will execute rest DMLs because we can not execute CREATE with any other + SQL = render_template("/".join([self.template_path, 'grant.sql']), data=data, conn=self.conn) + if SQL: + status, msg = self.conn.execute_scalar(SQL) + if not status: + return internal_server_error(errormsg=msg) + + # We need oid of newly created database + SQL = render_template("/".join([self.template_path, 'properties.sql']), name=data['name']) + status, res = self.conn.execute_dict(SQL) + if not status: + return internal_server_error(errormsg=res) + + response=res['rows'][0] + + return jsonify( + node=self.blueprint.generate_browser_node( + response['did'], + response['name'], + icon="icon-database-not-connected", + connected=False, + tablespace=response['default_tablespace'], + allowConn=True, + canCreate=response['cancreate'], + canDisconn=True + ) + ) + + except Exception as e: + return make_json_response( + status=410, + success=0, + errormsg=e.message + ) + + + @check_precondition + def update(self, gid, sid, did): + """Update the database.""" + + data = request.form if request.form else json.loads(request.data.decode()) + + try: + SQL = self.getSQL(gid, sid, data, did) + if SQL: + status, msg = conn.execute_scalar(SQL) + + if not status: + return internal_server_error(errormsg=msg) + + info = "Database updated." + else: + info = "Nothing to update." + + return make_json_response( + success=1, + info = info, + data={ + 'id': did, + 'sid': sid, + 'gid': gid, + } + ) + + except Exception as e: + return make_json_response( + status=410, + success=0, + errormsg=str(e) + ) + + @check_precondition + def delete(self, gid, sid, did): + """Delete the database.""" + + SQL = render_template("/".join([self.template_path, 'delete.sql']), did=did) + status, res = self.conn.execute_scalar(SQL) + if not status: + return internal_server_error(errormsg=res) + + if res is None: + return make_json_response( + success=0, + errormsg=gettext( + 'The specified database could not be found.\n' + ) + ) + else: + try: + SQL = render_template("/".join([self.template_path, 'delete.sql']), datname=res, conn=self.conn) + status, res = self.conn.execute_scalar(SQL) + if not status: + return internal_server_error(errormsg=res) + except Exception as e: + return make_json_response( + success=0, + errormsg=str(e)) + + try: + info = traceback.format_exc() + except Exception as e: + info = str(e) + + return make_json_response(success=1, + info=info) + + @staticmethod + def parse_privileges(str_privileges, object_type= 'DATABASE'): + """Parse Privileges.""" + db_privileges = { + 'c': 'CONNECT', + 'C': 'CREATE', + 'T': 'TEMPORARY', + 'a': 'INSERT', + 'r': 'SELECT', + 'w': 'UPDATE', + 'd': 'DELETE', + 'D': 'TRUNCATE', + 'x': 'REFERENCES', + 't': 'TRIGGER', + 'U': 'USAGE', + 'X': 'EXECUTE' + } + privileges_max_cnt = { + 'DATABASE': 3, + 'TABLE': 7, + 'SEQUENCE': 3, + 'FUNCTION': 1, + 'TYPE': 1 + } + + privileges = [] + + for priv in str_privileges: + priv_with_grant = [] + priv_without_grant = [] + for privilege in priv['privileges']: + if privilege['with_grant']: + priv_with_grant.append(db_privileges[privilege['privilege_type']]) + elif privilege['privilege']: + priv_without_grant.append(db_privileges[privilege['privilege_type']]) + + if object_type == "DATABASE": + priv_with_grant = ", ".join(priv_with_grant) if len(priv_with_grant) < privileges_max_cnt[object_type.upper()] else 'ALL' + priv_without_grant = ", ".join(priv_without_grant) if len(priv_without_grant) < privileges_max_cnt[object_type.upper()] else 'ALL' + else: + priv_with_grant = ", ".join(priv_with_grant) + priv_without_grant = ", ".join(priv_without_grant) + + privileges.append({'grantee': priv['grantee'], 'with_grant': priv_with_grant, 'without_grant': priv_without_grant}) + + return privileges + + @check_precondition + def msql(self, gid, sid, did=None): + """ + This function to return modified SQL + """ + data = {} + for k, v in request.args.items(): + try: + data[k] = json.loads(v) + except ValueError: + data[k] = v + + SQL = self.getSQL(gid, sid, data, did) + + if isinstance(SQL, str) and SQL and SQL.strip('\n') and SQL.strip(' '): + return make_json_response( + data=SQL, + status=200 + ) + else: + return make_json_response( + data="-- modified SQL", + status=200 + ) + + def getSQL(self, gid, sid, data, did=None): + """ + This function will generate sql from model data + """ + + try: + if did is not None: + # Fetch the name of database for comperision + SQL = render_template("/".join([self.template_path, 'update.sql']), did=did) + status, name = self.conn.execute_scalar(SQL) + if not status: + return internal_server_error(errormsg=name) + + data['old_name'] = name + if 'name' not in data: + data['name'] = name + + for key in ['datacl', 'deftblacl', 'defseqacl', 'deffuncacl', 'deftypeacl']: + if key in data and data[key] is not None: + if 'added' in data[key]: + data[key]['added'] = self.parse_privileges(data[key]['added']) + if 'changed' in data[key]: + data[key]['changed'] = self.parse_privileges(data[key]['changed']) + if 'deleted' in data[key]: + data[key]['deleted'] = self.parse_privileges(data[key]['deleted']) + + SQL = render_template("/".join([self.template_path, 'update.sql']), data=data, conn=self.conn) + + else: + + required_args = [ + u'name' + ] + + for arg in required_args: + if arg not in data: + return " -- definition incomplete" + + for key in ['datacl', 'deftblacl', 'defseqacl', 'deffuncacl', 'deftypeacl']: + if key in data and data[key] is not None: + data[key] = self.parse_privileges(data[key]) + + SQL = render_template("/".join([self.template_path, 'create.sql']), data=data) + SQL += "\n" + SQL += render_template("/".join([self.template_path, 'grant.sql']), data=data) + return SQL + + except Exception as e: + return internal_server_error(errormsg=str(e)) + + @check_precondition + def variable_options(self, gid, sid): + res = [] + SQL = render_template("/".join([self.template_path, 'variables.sql'])) + status, rset = self.conn.execute_dict(SQL) + if not status: + return internal_server_error(errormsg=rset) + + return make_json_response( + data=rset['rows'], + status=200 + ) + + @check_precondition + def sql(self, gid, sid, did): + """ + This function will generate sql for sql panel + """ + #try: + SQL = render_template("/".join([self.template_path, 'properties.sql']), did=did) + status, res = self.conn.execute_dict(SQL) + if not status: + return internal_server_error(errormsg=res) + + result = res['rows'][0] + + SQL = render_template("/".join([self.template_path, 'get_variables.sql']), did=did) + status, res1 = self.conn.execute_dict(SQL) + if not status: + return internal_server_error(errormsg=res1) + + frmtd_reslt = self.formatter(result, res1) + result.update(frmtd_reslt) + + SQL = render_template("/".join([self.template_path, 'create.sql']), data=result, conn=self.conn) + SQL += "\n\n" + SQL += render_template("/".join([self.template_path, 'grant.sql']), data=result, conn=self.conn) + return ajax_response(response=SQL) + + #except Exception as e: + # return ajax_response(response=str(e)) + +DatabaseView.register_node_view(blueprint) diff --git a/web/pgadmin/browser/server_groups/servers/databases/static/img/coll-database.png b/web/pgadmin/browser/server_groups/servers/databases/static/img/coll-database.png new file mode 100755 index 0000000000000000000000000000000000000000..bfc0966c24b062675a6d2923e6670b238bd5b5e4 GIT binary patch literal 771 zcmV+e1N{7nP)<h;3K|Lk000e1NJLTq000mG000mO0{{R3C@l|D0004TP)t-s0002P zuxhuUORttU#<XeL%Z}#UsqN^!^76v(?6>6KqSnrc%Drv8tx~U;H^HrB-O!o;{qFz$ z<No>B`T5cH@Wt`&yzJ?+<>jm7;h^8&o5#Fru$VjP;kW$x+4}d-^z_H>?!N8qyX@(| z?d{6+^xORT^0=Z-tdcO}*}Li7x$We=_3+g7^4a_Q@&5k%_w?xS?b7V&#OmO<+tIG{ z>e&DI^Z)nm{`AxI?6vFRnB&=l;M#lN+Jxlbm*3Z&(Zre1zLDkAv-0G}_Uy*=?6T_N zm)z8W*v)v+#CyuSeAB~><JrCB*Sg@&tJK1h$FqLHt#iPva?8Ae*UFgV+p_TO*ZlnV zvz$cb+rRSY+5h_V|NQm-_T~Kf?fdxY`T6Db^3(F~%>ViB>*JW?-htuVgyh_q&b*P| z*MHN-jOE?J^6J_C`Q`ok`0nYx&A@fAnK|3Wl<nQM^X9(u>#ynIlibvI)5vDYxm?r5 zk;P-HF8}}l0d!JMQvg8b*k%9#010qNS#tmY07w7;07w8v$!k6U007BJL_t&-(_>%& z0!AigAYfu-fC3g)Hg*n9E^Z!PJ`k5*Ku}0nL{v;%LQ+baA1ELrD<~u<ub`-;tfH!> z4iwPP)Y8_`)zi~AFf=mO0123wnweWzT3OrJ+S!8z92}jTU0mJVJv_a<!2&+Me*OW0 zLBS!RVc`)Vfyk)nnAo^@{e;9MZHO6B(a9E;DXBJT=}<E=z-D9u&B(%TMs`kgOm19! z9?*<@u!>+)Gmse|cNBmG7z+7|ic3n%$}1}Q`5?ZnLiRoYZDKCz4W-Rk0000bbVXQn zWMOn=I%9HWVRU5xGB7bPEip1JFfmjzFgi3dIy5vZFf}?bFgTed9smFUC3HntbYx+4 zWjbwdWNBu305UK!FfA}SEif@uGBY|fG&(RgD=;-WFfhuORjdF2002ovPDHLkV1g|G BtCs)( literal 0 HcmV?d00001 diff --git a/web/pgadmin/browser/server_groups/servers/databases/static/img/database.png b/web/pgadmin/browser/server_groups/servers/databases/static/img/database.png new file mode 100755 index 0000000000000000000000000000000000000000..1a743d7a37e5781872a37dedc5a03bb9df931973 GIT binary patch literal 706 zcmV;z0zLhSP)<h;3K|Lk000e1NJLTq000mG000mO0{{R3C@l|D0003sP)t-s0002f z#gW6XYPX<Eua-Bmn>)s|Y1+$<=G>|6=)Lmt!td<2<lmy!&WOsrZM>~gub4K$tz+HL zng9Ln|NZ0s`PljS(e?1f@$S6r>9XbJtK{LJ-`|_ZylSwRJL%!K{Q24X_s{h7$L{XF z?d`kl>A&sm%JlTx{Q2^@qE4)mFyq_3>D;;P<Gc0n)b;Y&`}^_!{`>dz=<w~*?CHen z;=1MC!u0Cc|M>I&_wN4m)Aa1M>*1K=*n{BOd*9lG<l&a+<E!)O+V<|W>*JW=+=S@h ztMcgC|N8Ia-ht%bmjC+n|M~9!^V9R`w&dKH>gB8c^wHznf%59v?B|y0<f_ZOg8%sP z;@X1b-<Zw7b+DN^!mef8#+2>dwe#k_^Xsqa;gj6dchksb$+=v;sZg(&I9k(EZvX%Q z0d!JMQvg8b*k%9#010qNS#tmY07w7;07w8v$!k6U006{EL_t&-(_>&@U|?ioW&r|b zCI$##Wn<^y<l^Sx<>MCs@dbs1MMTBKB_yS!Wn=|`0&?=gA_|I1$||aA>Kd9r0WEDE zT|IpR14AQY6H_gafSI|4rIodft)0DtBSgRnDB$ekYUk?U1`%+#@Bj*U+PQi`Rk(Zm z`1-j3wRpL~1+1(=D*PRhwAgq8RRll{_4Wv~b_oIsKve_-wfKdA3=IVfgqeqf%!shF zkBkBdFhs}1#>FQjCMBn&ra}FS?0o>+m?|%1vwO+_001R)MObuXVRU6WV{&C-bY%cC zFflMKF)}SMF;p@zIy5snG&CzPH99abIGH6L0000bbVXQnWMOn=I&E)cX=Zr<GB7bP oEigGPFfmjzGdeUhIxsdXFf}?bFv^!ztN;K207*qoM6N<$g7w#XBLDyZ literal 0 HcmV?d00001 diff --git a/web/pgadmin/browser/server_groups/servers/databases/static/img/databasebad.png b/web/pgadmin/browser/server_groups/servers/databases/static/img/databasebad.png new file mode 100644 index 0000000000000000000000000000000000000000..96832908714ed515d594bd898486b8b925c7331f GIT binary patch literal 814 zcmeAS@N?(olHy`uVBq!ia0vp^0wB!63?wyl`GXl47;6K3LR^8|%SR_3-jKC@f%p1p z=FjdOxOljC(`@TwTQYB*?t6A;<=Yo~KYcp%{>}C$_ZM9|*KulZ?w++l>t~o9T$_CN z!p#4F-~Ipl_}|YPKYw2Q`r+uucYEHv-1PM6>L(8u+`m8j_@1l{Gpt`e-2Uh1&0pWo zfBAC!-Mf8n-|l|%a{t@6r@nl-{paVW9gF<dOfq<Ud+*CTJKsLu{q@6@ub*!I{{8Xa zzu(`#y!i0;(wmn@UOn3N^zNZAuWtPR@%jJvcXv|4k1{Y^jkSMxv*p39>PxY9yKO{1 zzr1xfE$Lf%`Ne3nkKy5WGc!NExcRND>|0UM*Sx$7QO586{oi?b{{QvmZhF$!+}tl2 z8K0AqK7@w;|M~87V&cc>=nugm?|uE=xx4@W_5R<N%Qus}-uwDqiF5l99Q@wL=bfwT z8$0{g78ajg-TV*~^xoV1t&8hxbMse*hA*|XPw#2^|Krn#z`%E2UT>XUUZ|=*S5Vk6 z)AG=|)Z53VT#fdA>*Rd-MB3>fo#*ljd+fwd1=`$BPkH-fS2-{O7)yfuf*Bm1-ADs+ zI14-?i-EKU7`vU!wgWO=c)B=-NL)@%0D_dHG)AU0HMcN@{>I+9&6B%lw@>encL?B6 zQP5G+Qqxn^RMl11R@dhd2?!7o4Gszm3=O`1;mRd8t`{#}y_A-al9Zf1W7f>q4J<l3 zYuDuFynQ44MmBfu0j6_ddfV1*%j3)2_HLV;0;9inw0^#PbbNHVeSP!>2Zsf@0R<ho z8WZ>f1qITQrR4-AMR`pRU)EAHJ7;EOcgu`1sk_*rr#Ck#sK`qz#8v7-&zByb+iTv) zY>?bJt#aE>)>en;qOPvSZ3a`OOq+T;?8dLKu$y7cAq{)ulcSlx)UC~xWoD@JF#fK# zIy4LDXVntdh?11Vl2ohYqEsNoU}RuuplfKPYhV~+WME}tY-M7iZD49;U|=y*RSrc% nZhlH;S|vn-fhAZ2NVS!L8AQXW>7i?Y8W=oX{an^LB{Ts5xrU7; literal 0 HcmV?d00001 diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/css/database.css b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/css/database.css new file mode 100644 index 0000000..f59bc8e --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/css/database.css @@ -0,0 +1,12 @@ +.pg-icon-database { + background-image: url('{{ url_for('NODE-database.static', filename='img/database.png') }}') !important; + background-repeat: no-repeat; + align-content: center; + vertical-align: middle; + height: 1.3em; +} + +.icon-database-not-connected { + background-image: url('{{ url_for('NODE-database.static', filename='img/databasebad.png') }}') !important; + border-radius: 10px +} diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/js/databases.js b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/js/databases.js new file mode 100644 index 0000000..997184b --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/js/databases.js @@ -0,0 +1,358 @@ +define( + [ + 'jquery', 'underscore', 'underscore.string', 'pgadmin', + 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection', + 'pgadmin.browser.server.privilege', 'pgadmin.browser.server.variable', + ], +function($, _, S, pgAdmin, pgBrowser, Alertify) { + + if (!pgBrowser.Nodes['coll-database']) { + var databases = pgAdmin.Browser.Nodes['coll-database'] = + pgAdmin.Browser.Collection.extend({ + node: 'database', + label: '{{ _('Databases') }}', + type: 'coll-database', + columns: ['name', 'did', 'datowner', 'comments'] + }); + }; + + var SecurityModel = pgAdmin.Browser.Node.Model.extend({ + defaults: { + provider: undefined, + securitylabel: undefined + }, + schema: [{ + id: 'provider', label: '{{ _('Provider') }}', + type: 'text', editable: true + },{ + id: 'security_label', label: '{{ _('Security Label') }}', + type: 'text', editable: true + }], + validate: function() { + var err = {}, + errmsg = null, + data = this.toJSON(); + + if (_.isUndefined(data.label) || + _.isNull(data.label) || + String(data.label).replace(/^\s+|\s+$/g, '') == '') { + return _("Please specify the value for all the security providers."); + } + return null; + } + }); + + if (!pgBrowser.Nodes['database']) { + pgAdmin.Browser.Nodes['database'] = pgAdmin.Browser.Node.extend({ + parent_type: 'server', + type: 'database', + hasSQL: true, + canDrop: true, + label: '{{ _('Database') }}', + Init: function() { + /* Avoid mulitple registration of menus */ + if (this.initialized) + return; + + this.initialized = true; + + pgBrowser.add_menus([{ + name: 'create_database_on_server', node: 'server', module: this, + applies: ['object', 'context'], callback: 'show_obj_properties', + category: 'create', priority: 4, label: '{{ _('Database...') }}', + icon: 'wcTabIcon pg-icon-database', data: {action: 'create'} + },{ + name: 'create_database_on_coll', node: 'coll-database', module: this, + applies: ['object', 'context'], callback: 'show_obj_properties', + category: 'create', priority: 4, label: '{{ _('Database...') }}', + icon: 'wcTabIcon pg-icon-database', data: {action: 'create'} + },{ + name: 'create_database', node: 'database', module: this, + applies: ['object', 'context'], callback: 'show_obj_properties', + category: 'create', priority: 4, label: '{{ _('Database...') }}', + icon: 'wcTabIcon pg-icon-database', data: {action: 'create'} + },{ + name: 'connect_database', node: 'database', module: this, + applies: ['object', 'context'], callback: 'connect_database', + category: 'connect', priority: 4, label: '{{ _('Connect Database...') }}', + icon: 'fa fa-link', enable : 'is_not_connected' + },{ + name: 'disconnect_database', node: 'database', module: this, + applies: ['object', 'context'], callback: 'disconnect_database', + category: 'drop', priority: 5, label: '{{ _('Disconnect Database...') }}', + icon: 'fa fa-chain-broken', enable : 'is_connected' + },{ + name: 'drop_database', node: 'database', module: this, + applies: ['object', 'context'], callback: 'delete_obj', + category: 'drop', priority: 4, label: '{{ _('Drop Database...') }}', + icon: 'fa fa-chain-broken' + }]); + + }, + is_not_connected: function(node) { + return (node && node.connected != true); + }, + is_connected: function(node) { + return (node && node.connected == true && node.canDisconn == true); + }, + callbacks: { + /* Connect the database */ + connect_database: function(args){ + var input = args || {}; + obj = this, + t = pgBrowser.tree, + i = input.item || t.selected(), + d = i && i.length == 1 ? t.itemData(i) : undefined; + + if (!d) + return false; + + connect_to_database(obj, d, t, i); + return false; + }, + /* Disconnect the database */ + disconnect_database: function(args) { + var input = args || {}; + obj = this, + t = pgBrowser.tree, + i = input.item || t.selected(), + d = i && i.length == 1 ? t.itemData(i) : undefined; + + if (!d) + return false; + + Alertify.confirm( + '{{ _('Disconnect the database') }}', + S('{{ _('Are you sure you want to disconnect the database - %%s ?') }}').sprintf(d.label).value(), + function(evt) { + $.ajax({ + url: obj.generate_url(i, 'connect', d, true), + type:'DELETE', + success: function(res) { + if (res.success == 1) { + Alertify.success("{{ _('" + res.info + "') }}"); + t.removeIcon(i); + d.connected = false; + d.icon = 'icon-database-not-connected'; + t.addIcon(i, {icon: d.icon}); + t.unload(i); + t.setInode(i); + } + }, + error: function(xhr, status, error) { + try { + var err = $.parseJSON(xhr.responseText); + if (err.success == 0) { + msg = S('{{ _(' + err.errormsg + ')}}').value(); + Alertify.error("{{ _('" + err.errormsg + "') }}"); + } + } catch (e) {} + t.unload(i); + } + }); + }, + function(evt) { + return true; + }); + + return false; + }, + + /* Connect the database (if not connected), before opening this node */ + beforeopen: function(item, data) { + if(!data || data._type != 'database') { + return false; + } + + pgBrowser.tree.addIcon(item, {icon: data.icon}); + if (!data.connected) { + connect_to_database(this, data, pgBrowser.tree, item); + return false; + } + return true; + }, + }, + model: pgAdmin.Browser.Node.Model.extend({ + defaults: { + name: undefined, + owner: undefined, + comment: undefined, + encoding: 'UTF8', + template: undefined, + tablespace: undefined, + collation: undefined, + char_type: undefined, + conn_limit: -1, + variables: [], + privileges: [], + securities: [], + datacl: [], + deftblacl: [], + deffuncacl: [], + defseqacl: [], + deftypeacl: [] + }, + schema: [{ + id: 'name', label: '{{ _('Database') }}', cell: 'string', + editable: false, type: 'text' + },{ + id: 'did', label:'{{ _('Oid') }}', cell: 'string', mode: ['properties'], + editable: false, type: 'text', visible: false + },{ + id: 'datowner', label:'{{ _('Owner') }}', + editable: false, type: 'text', node: 'role', + control: Backform.NodeListByNameControl + },{ + id: 'comments', label:'{{ _('Comment') }}', + editable: false, type: 'multiline' + },{ + id: 'encoding', label: '{{ _('Encoding') }}', + editable: false, type: 'text', group: 'Definition', + disabled: function(m) { return !m.isNew(); }, url: 'get_encodings', + control: 'node-ajax-options' + },{ + id: 'template', label: '{{ _('Template') }}', + editable: false, type: 'text', group: 'Definition', + disabled: function(m) { return !m.isNew(); }, + control: 'node-list-by-name', node: 'database' + },{ + id: 'spcname', label: '{{ _('Tablespace') }}', + editable: false, type: 'text', group: 'Definition', + control: 'node-list-by-name', node: 'tablespace', + filter: function(m) { + if (m.label == "pg_global") return false; + else return true; + } + },{ + id: 'datcollate', label: '{{ _('Collation') }}', + editable: false, type: 'text', group: 'Definition', + disabled: function(m) { return !m.isNew(); }, url: 'get_ctypes', + control: 'node-ajax-options' + },{ + id: 'datctype', label: '{{ _('Character Type') }}', + editable: false, type: 'text', group: 'Definition', + disabled: function(m) { return !m.isNew(); }, url: 'get_ctypes', + control: 'node-ajax-options' + },{ + id: 'conn_limit', label: '{{ _('Connection Limit') }}', + editable: false, type: 'int', group: 'Definition', min: -1 + },{ + id: 'datacl', label: '{{ _('Privileges') }}', model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend( + {privileges: ['C', 'T', 'c']}), uniqueCol : ['grantee', 'grantor'], + editable: false, type: 'collection', group: '{{ _('Security') }}', mode: ['properties', 'edit', 'create'], + canAdd: true, canDelete: true, control: 'unique-col-collection', + }/*,{ + id: 'variables', label: '{{ _('Variables') }}', type: 'collection', + model: pgAdmin.Browser.Node.VariableModel, editable: false, + group: '{{ _('Security') }}', mode: ['properties', 'edit', 'create'], + canAdd: true, canEdit: false, canDelete: true, hasRole: true, + control: Backform.VariableCollectionControl, node: 'role' + },{ + id: 'securities', label: '{{ _('Securitiy Labels') }}', model: SecurityModel, + editable: false, type: 'collection', canEdit: false, + group: '{{ _('Security') }}', canDelete: true, + mode: ['properties', 'edit', 'create'], canAdd: true, + control: 'unique-col-collection', uniqueCol : ['provider'], + min_version: 90200 + }*/,{ + type: 'nested', control: 'tab', group: '{{ _('Default Privileges') }}', + schema:[{ + id: 'deftblacl', model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend( + {privileges: ['a', 'r', 'w', 'd', 'D', 'x', 't']}), label: '{{ _('Default Privileges: Tables') }}', + editable: false, type: 'collection', group: '{{ _('Tables') }}', + mode: ['edit', 'create'], control: 'unique-col-collection', + canAdd: true, canDelete: true, uniqueCol : ['grantee', 'grantor'] + },{ + id: 'defseqacl', model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend( + {privileges: ['r', 'w', 'U']}), label: '{{ _('Default Privileges: Sequences') }}', + editable: false, type: 'collection', group: '{{ _('Sequences') }}', + mode: ['edit', 'create'], control: 'unique-col-collection', + canAdd: true, canDelete: true, uniqueCol : ['grantee', 'grantor'] + },{ + id: 'deffuncacl', model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend( + {privileges: ['X']}), label: '{{ _('Default Privileges: Functions') }}', + editable: false, type: 'collection', group: '{{ _('Functions') }}', + mode: ['edit', 'create'], control: 'unique-col-collection', + canAdd: true, canDelete: true, uniqueCol : ['grantee', 'grantor'] + },{ + id: 'deftypeacl', model: pgAdmin.Browser.Node.PrivilegeRoleModel.extend( + {privileges: ['U']}), label: '{{ _('Default Privileges: Types') }}', + editable: false, type: 'collection', group: '{{ _('Types') }}', + mode: ['edit', 'create'], control: 'unique-col-collection', + canAdd: true, canDelete: true, uniqueCol : ['grantee', 'grantor'], + min_version: 90200 + }] + } + ], + validate: function(keys) { + var name = this.get('name'); + if (_.isUndefined(name) || _.isNull(name) || + String(name).replace(/^\s+|\s+$/g, '') == '') { + var msg = '{{ _('Name can not be empty!') }}'; + this.errorModel.set('name', msg); + return msg; + } else { + this.errorModel.unset('name'); + } + return null; + } + }) + }); + function connect_to_database(obj, data, tree, item) { + var onFailure = function(xhr, status, error, _model, _data, _tree, _item) { + + tree.setInode(_item); + tree.addIcon(_item, {icon: 'icon-database-not-connected'}); + + Alertify.pgNotifier('error', xhr, error, function(msg) { + setTimeout(function() { + Alertify.dlgServerPass( + '{{ _('Connect to Database') }}', + msg, _model, _data, _tree, _item + ).resizeTo(); + }, 100); + }); + }, + onSuccess = function(res, model, data, tree, item) { + tree.deselect(item); + tree.setInode(item); + + if (res && res.data) { + if(typeof res.data.connected == 'boolean') { + data.connected = res.data.connected; + } + if (typeof res.data.icon == 'string') { + tree.removeIcon(item); + data.icon = res.data.icon; + tree.addIcon(item, {icon: data.icon}); + } + + Alertify.success(res.info); + setTimeout(function() {tree.select(item);}, 10); + setTimeout(function() {tree.open(item);}, 100); + } + }; + Alertify.confirm( + '{{ _('Connect to Database') }}', + '{{ _('Do you want to connect the database?') }}', + function(evt) { + url = obj.generate_url(item, "connect", data, true); + $.post(url) + .done( + function(res) { + if (res.success == 1) { + return onSuccess(res, obj, data, tree, item); + } + }) + .fail( + function(xhr, status, error) { + return onFailure(xhr, status, error, obj, data, tree, item); + }); + }, + function() {}); + } + + } + + return pgBrowser.Nodes['coll-database']; +}); diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/acl.sql new file mode 100644 index 0000000..99b4dab --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/acl.sql @@ -0,0 +1,33 @@ +SELECT 'datacl' as deftype, COALESCE(gt.rolname, 'public') grantee, g.rolname grantor, array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable +FROM + (SELECT + d.grantee, d.grantor, d.is_grantable, + CASE d.privilege_type + WHEN 'CONNECT' THEN 'c' + WHEN 'CREATE' THEN 'C' + WHEN 'DELETE' THEN 'd' + WHEN 'EXECUTE' THEN 'X' + WHEN 'INSERT' THEN 'a' + WHEN 'REFERENCES' THEN 'x' + WHEN 'SELECT' THEN 'r' + WHEN 'TEMPORARY' THEN 'T' + WHEN 'TRIGGER' THEN 't' + WHEN 'TRUNCATE' THEN 'D' + WHEN 'UPDATE' THEN 'w' + WHEN 'USAGE' THEN 'U' + ELSE 'UNKNOWN' + END AS privilege_type + FROM + (SELECT datacl FROM pg_database db + LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace=ta.OID + LEFT OUTER JOIN pg_shdescription descr ON ( + db.oid=descr.objoid AND descr.classoid='pg_database'::regclass) +{% if did %} + WHERE db.oid = {{ did|qtLiteral }}::OID +{% endif %} + ) acl, + aclexplode(datacl) d + ) d + LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid) + LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid) +GROUP BY g.rolname, gt.rolname \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/create.sql new file mode 100644 index 0000000..b3138e6 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/create.sql @@ -0,0 +1,22 @@ +CREATE DATABASE {{ conn|qtIdent(data.name) }} +{% if data.datowner %} + WITH {% endif %}{% if data.datowner %}OWNER = {{ conn|qtIdent(data.datowner) }} +{% endif %} +{% if data.template %} + TEMPLATE = {{ conn|qtIdent(data.template) }} +{% endif %} +{% if data.encoding %} + ENCODING = {{ data.encoding|qtLiteral }} +{% endif %} +{% if data.datcollate %} + LC_COLLATE = {{ data.datcollate|qtLiteral }} +{% endif %} +{% if data.datctype %} + LC_CTYPE = {{ data.datctype|qtLiteral }} +{% endif %} +{% if data.spcname %} + TABLESPACE = {{ conn|qtIdent(data.spcname) }} +{% endif %} +{% if data.conn_limit %} + CONNECTION LIMIT = {{ data.conn_limit }} +{% endif %}; \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/defacl.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/defacl.sql new file mode 100644 index 0000000..5adb7f8 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/defacl.sql @@ -0,0 +1,87 @@ +SELECT * FROM +(SELECT + 'deftblacl' as deftype, COALESCE(gt.rolname, 'public') grantee, g.rolname grantor, array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable +FROM + (SELECT + d.grantee, d.grantor, d.is_grantable, + CASE d.privilege_type + WHEN 'CONNECT' THEN 'c' + WHEN 'CREATE' THEN 'C' + WHEN 'DELETE' THEN 'd' + WHEN 'EXECUTE' THEN 'X' + WHEN 'INSERT' THEN 'a' + WHEN 'REFERENCES' THEN 'x' + WHEN 'SELECT' THEN 'r' + WHEN 'TEMPORARY' THEN 'T' + WHEN 'TRIGGER' THEN 't' + WHEN 'TRUNCATE' THEN 'D' + WHEN 'UPDATE' THEN 'w' + WHEN 'USAGE' THEN 'U' + ELSE 'UNKNOWN' + END AS privilege_type + FROM + (SELECT defaclacl defacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='r') acl, + aclexplode(defacl) d + ) d + LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid) + LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid) +GROUP BY g.rolname, gt.rolname +UNION ALL +SELECT + 'defseqacl' as deftype, COALESCE(gt.rolname, 'public') grantee, g.rolname grantor, array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable +FROM + (SELECT + d.grantee, d.grantor, d.is_grantable, + CASE d.privilege_type + WHEN 'CONNECT' THEN 'c' + WHEN 'CREATE' THEN 'C' + WHEN 'DELETE' THEN 'd' + WHEN 'EXECUTE' THEN 'X' + WHEN 'INSERT' THEN 'a' + WHEN 'REFERENCES' THEN 'x' + WHEN 'SELECT' THEN 'r' + WHEN 'TEMPORARY' THEN 'T' + WHEN 'TRIGGER' THEN 't' + WHEN 'TRUNCATE' THEN 'D' + WHEN 'UPDATE' THEN 'w' + WHEN 'USAGE' THEN 'U' + ELSE 'UNKNOWN' + END AS privilege_type + FROM + (SELECT defaclacl defacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='S') acl, + aclexplode(defacl) d + ) d + LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid) + LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid) +GROUP BY g.rolname, gt.rolname +UNION ALL +SELECT + 'deffuncacl' as deftype, COALESCE(gt.rolname, 'public') grantee, g.rolname grantor, array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable +FROM + (SELECT + d.grantee, d.grantor, d.is_grantable, + CASE d.privilege_type + WHEN 'CONNECT' THEN 'c' + WHEN 'CREATE' THEN 'C' + WHEN 'DELETE' THEN 'd' + WHEN 'EXECUTE' THEN 'X' + WHEN 'INSERT' THEN 'a' + WHEN 'REFERENCES' THEN 'x' + WHEN 'SELECT' THEN 'r' + WHEN 'TEMPORARY' THEN 'T' + WHEN 'TRIGGER' THEN 't' + WHEN 'TRUNCATE' THEN 'D' + WHEN 'UPDATE' THEN 'w' + WHEN 'USAGE' THEN 'U' + ELSE 'UNKNOWN' + END AS privilege_type + FROM + (SELECT defaclacl defacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='f') acl, + aclexplode(defacl) d + ) d + LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid) + LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid) +GROUP BY g.rolname, gt.rolname +) t + +ORDER BY deftype, grantee; \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/delete.sql new file mode 100644 index 0000000..042113e --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/delete.sql @@ -0,0 +1,8 @@ +{# We need database name before we execute drop #} +{% if did %} +SELECT db.datname as name FROM pg_database as db WHERE db.oid = {{did}} +{% endif %} +{# Using name from above query we will drop the database #} +{% if datname %} + DROP DATABASE {{ conn|qtIdent(datname) }}; +{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_ctypes.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_ctypes.sql new file mode 100644 index 0000000..d559aa0 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_ctypes.sql @@ -0,0 +1,5 @@ +SELECT DISTINCT(datctype) AS cname +FROM pg_database +UNION +SELECT DISTINCT(datcollate) AS cname +FROM pg_database \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_encodings.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_encodings.sql new file mode 100644 index 0000000..03ae601 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_encodings.sql @@ -0,0 +1,17 @@ +{# +-- Given SQL is workaround +-- We need to implement a mechanism to check for valid supported server encoding +#} +SELECT * FROM +(SELECT pg_encoding_to_char(s.i) AS encoding + FROM (SELECT generate_series(0, 100, 1) as i) s) a +WHERE encoding != ''; + +{# +-- For future use, Do not delete +--SELECT * FROM +--(SELECT s.i as id, pg_encoding_to_char(s.i) +-- as encoding +-- FROM (SELECT generate_series(0, 100, 1) as i) s) a +--WHERE encoding != '' +#} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_nodes.sql new file mode 100644 index 0000000..87e32e5 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_nodes.sql @@ -0,0 +1,7 @@ +SELECT + db.oid as did, db.datname as name, ta.spcname as spcname, db.datallowconn, + has_database_privilege(db.oid, 'CREATE') as cancreate +FROM + pg_database db + LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace = ta.oid + {% if did %}WHERE db.oid={{did}}::int {% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_variables.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_variables.sql new file mode 100644 index 0000000..a9ab7a9 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_variables.sql @@ -0,0 +1,5 @@ +SELECT rl.*, r.rolname AS user_name, db.datname as db_name +FROM pg_db_role_setting AS rl + LEFT JOIN pg_roles AS r ON rl.setrole = r.oid + LEFT JOIN pg_database AS db ON rl.setdatabase = db.oid +WHERE setdatabase = {{did}} diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/grant.sql new file mode 100644 index 0000000..64e7d43 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/grant.sql @@ -0,0 +1,47 @@ +{# +# CREATE DATABSE does not allow us to run any +# other sql statments along with it, so we wrote +# seprate sql for rest alter sql statments here +#} +{% import 'macros/variable.macros' as VARIABLE %} +{% import 'macros/privilege.macros' as PRIVILEGE %} +{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} +{% if data.comments %} +COMMENT ON DATABASE {{ conn|qtIdent(data.name) }} + IS {{ data.comments|qtLiteral }}; +{% endif %} + +{# We will generate Variable SQL's using macro #} +{% if data.variables %} +{% for var in data.variables %} +{% if var.value == True %} +{{ VARIABLE.APPLY(conn, data.name, var.role, var.name, 'on') }} +{% elif var.value == False %} +{{ VARIABLE.APPLY(conn, data.name, var.role, var.name, 'off') }} +{% else %} +{{ VARIABLE.APPLY(conn, data.name, var.role, var.name, var.value) }} +{% endif %} +{% endfor %} +{% endif %} + +{% if data.datacl %} +{% for priv in data.datacl %} +{{ PRIVILEGE.APPLY(conn, 'DATABASE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} + +{% if data.deftblacl %} +{% for priv in data.deftblacl %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if data.defseqacl %} +{% for priv in data.defseqacl %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if data.deffuncacl %} +{% for priv in data.deffuncacl %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/properties.sql new file mode 100644 index 0000000..3d8dcb5 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/properties.sql @@ -0,0 +1,19 @@ +SELECT + db.oid as did, db.datname as name, db.dattablespace AS spcoid, + spcname, datallowconn, pg_encoding_to_char(encoding) AS encoding, + pg_get_userbyid(datdba) AS datowner, + has_database_privilege(db.oid, 'CREATE') as cancreate, + current_setting('default_tablespace') AS default_tablespace, + descr.description as comments +FROM pg_database db + LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace=ta.OID + LEFT OUTER JOIN pg_shdescription descr ON ( + db.oid=descr.objoid AND descr.classoid='pg_database'::regclass + ) +{% if did %} +WHERE db.oid= {{did}}::int +{% endif %} +{% if name %} +WHERE db.datname = {{name|qtLiteral}} +{% endif %} +ORDER BY datname diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/update.sql new file mode 100644 index 0000000..a750f67 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/update.sql @@ -0,0 +1,163 @@ +{% import 'macros/variable.macros' as VARIABLE %} +{% if data %} +{# The SQL generated below will change name #} +{% if data.old_name != data.name %} +ALTER DATABASE {{ conn|qtIdent(data.old_name) }} RENAME TO {{ conn|qtIdent(data.name) }}; +{% endif %} +{# The SQL generated below will change owner #} +{% if data.datowner %} +ALTER DATABASE {{ conn|qtIdent(data.name) }} OWNER TO {{ conn|qtIdent(data.datowner) }}; +{% endif %} +{# The SQL generated below will change comments #} +{% if data.comments %} +COMMENT ON DATABASE {{ conn|qtIdent(data.name) }} +IS '{{ data.comments }}'; +{% endif %} +{# The SQL generated below will change tablespace #} +{% if data.spcname %} +ALTER DATABASE {{ conn|qtIdent(data.name) }} SET TABLESPACE {{ conn|qtIdent(data.spcname) }}; +{% endif %} +{# The SQL generated below will change conn limit #} +{% if data.conn_limit %} +ALTER DATABASE {{ conn|qtIdent(data.name) }} WITH CONNECTION LIMIT = {{ conn|qtIdent(data.conn_limit) }}; +{% endif %} +{# The SQL generated below will change Variables #} +{% if data.variables 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, var.role, var.name) }} +{% endfor %} +{% endif %} +{% if 'added' in variables and variables.added|length > 0 %} +{% for var in variables.added %} +{{ VARIABLE.APPLY(conn, var.database, var.role, 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, var.role, var.name, var.value) }} +{% endfor %} +{% endif %} +{% endif %} + +{# The SQL generated below will change priviledges #} +{% if data.datacl %} +{% if 'deleted' in data.datacl %} +{% for priv in data.datacl.deleted %} +{{ PRIVILEGE.RESETALL(conn, 'DATABASE', priv.grantee, data.name) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.datacl %} +{% for priv in data.datacl.changed %} +{{ PRIVILEGE.RESETALL(conn, 'DATABASE', priv.grantee, data.name) }} +{{ PRIVILEGE.APPLY(conn, 'DATABASE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.datacl %} +{% for priv in data.datacl.added %} +{{ PRIVILEGE.APPLY(conn, 'DATABASE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% if data.deftblacl %} +{% if 'deleted' in data.deftblacl %} +{% for priv in data.deftblacl.deleted %} +{{ DEFAULT_PRIVILEGE.RESET(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.deftblacl %} +{% for priv in data.deftblacl.changed %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.deftblacl %} +{% for priv in data.deftblacl.added %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{# The SQL generated below will change priviledges #} +{% if data.datacl %} +{% if 'deleted' in data.datacl %} +{% for priv in data.datacl.deleted %} +{{ PRIVILEGE.RESETALL(conn, 'DATABASE', priv.grantee, data.name) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.datacl %} +{% for priv in data.datacl.changed %} +{{ PRIVILEGE.RESETALL(conn, 'DATABASE', priv.grantee, data.name) }} +{{ PRIVILEGE.APPLY(conn, 'DATABASE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.datacl %} +{% for priv in data.datacl.added %} +{{ PRIVILEGE.APPLY(conn, 'DATABASE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% if data.deftblacl %} +{% if 'deleted' in data.deftblacl %} +{% for priv in data.deftblacl.deleted %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TABLES', priv.grantee) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.deftblacl %} +{% for priv in data.deftblacl.changed %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TABLES', priv.grantee) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.deftblacl %} +{% for priv in data.deftblacl.added %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% if data.defseqacl %} +{% if 'deleted' in data.defseqacl %} +{% for priv in data.defseqacl.deleted %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'SEQUENCES', priv.grantee) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.defseqacl %} +{% for priv in data.defseqacl.changed %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'SEQUENCES', priv.grantee) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.defseqacl %} +{% for priv in data.defseqacl.added %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% if data.deffuncacl %} +{% if 'deleted' in data.deffuncacl %} +{% for priv in data.deffuncacl.deleted %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'FUNCTIONS', priv.grantee) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.deffuncacl %} +{% for priv in data.deffuncacl.changed %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'FUNCTIONS', priv.grantee) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.deffuncacl %} +{% for priv in data.deffuncacl.added %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% endif %} +{# We need database name #} +{% if did %} +SELECT db.datname as name FROM pg_database as db WHERE db.oid = {{did}} +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/variables.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/variables.sql new file mode 100644 index 0000000..b4e59ec --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/variables.sql @@ -0,0 +1,2 @@ +SELECT name, vartype, min_val, max_val, enumvals +FROM pg_settings WHERE context in ('user', 'superuser') \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/acl.sql new file mode 100644 index 0000000..99b4dab --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/acl.sql @@ -0,0 +1,33 @@ +SELECT 'datacl' as deftype, COALESCE(gt.rolname, 'public') grantee, g.rolname grantor, array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable +FROM + (SELECT + d.grantee, d.grantor, d.is_grantable, + CASE d.privilege_type + WHEN 'CONNECT' THEN 'c' + WHEN 'CREATE' THEN 'C' + WHEN 'DELETE' THEN 'd' + WHEN 'EXECUTE' THEN 'X' + WHEN 'INSERT' THEN 'a' + WHEN 'REFERENCES' THEN 'x' + WHEN 'SELECT' THEN 'r' + WHEN 'TEMPORARY' THEN 'T' + WHEN 'TRIGGER' THEN 't' + WHEN 'TRUNCATE' THEN 'D' + WHEN 'UPDATE' THEN 'w' + WHEN 'USAGE' THEN 'U' + ELSE 'UNKNOWN' + END AS privilege_type + FROM + (SELECT datacl FROM pg_database db + LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace=ta.OID + LEFT OUTER JOIN pg_shdescription descr ON ( + db.oid=descr.objoid AND descr.classoid='pg_database'::regclass) +{% if did %} + WHERE db.oid = {{ did|qtLiteral }}::OID +{% endif %} + ) acl, + aclexplode(datacl) d + ) d + LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid) + LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid) +GROUP BY g.rolname, gt.rolname \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/create.sql new file mode 100644 index 0000000..b3138e6 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/create.sql @@ -0,0 +1,22 @@ +CREATE DATABASE {{ conn|qtIdent(data.name) }} +{% if data.datowner %} + WITH {% endif %}{% if data.datowner %}OWNER = {{ conn|qtIdent(data.datowner) }} +{% endif %} +{% if data.template %} + TEMPLATE = {{ conn|qtIdent(data.template) }} +{% endif %} +{% if data.encoding %} + ENCODING = {{ data.encoding|qtLiteral }} +{% endif %} +{% if data.datcollate %} + LC_COLLATE = {{ data.datcollate|qtLiteral }} +{% endif %} +{% if data.datctype %} + LC_CTYPE = {{ data.datctype|qtLiteral }} +{% endif %} +{% if data.spcname %} + TABLESPACE = {{ conn|qtIdent(data.spcname) }} +{% endif %} +{% if data.conn_limit %} + CONNECTION LIMIT = {{ data.conn_limit }} +{% endif %}; \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/defacl.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/defacl.sql new file mode 100644 index 0000000..03084cf --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/defacl.sql @@ -0,0 +1,114 @@ +SELECT * FROM +(SELECT + 'deftblacl' as deftype, COALESCE(gt.rolname, 'public') grantee, g.rolname grantor, array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable +FROM + (SELECT + d.grantee, d.grantor, d.is_grantable, + CASE d.privilege_type + WHEN 'CONNECT' THEN 'c' + WHEN 'CREATE' THEN 'C' + WHEN 'DELETE' THEN 'd' + WHEN 'EXECUTE' THEN 'X' + WHEN 'INSERT' THEN 'a' + WHEN 'REFERENCES' THEN 'x' + WHEN 'SELECT' THEN 'r' + WHEN 'TEMPORARY' THEN 'T' + WHEN 'TRIGGER' THEN 't' + WHEN 'TRUNCATE' THEN 'D' + WHEN 'UPDATE' THEN 'w' + WHEN 'USAGE' THEN 'U' + ELSE 'UNKNOWN' + END AS privilege_type + FROM + (SELECT defaclacl defacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='r') acl, + aclexplode(defacl) d + ) d + LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid) + LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid) +GROUP BY g.rolname, gt.rolname +UNION ALL +SELECT + 'defseqacl' as deftype, COALESCE(gt.rolname, 'public') grantee, g.rolname grantor, array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable +FROM + (SELECT + d.grantee, d.grantor, d.is_grantable, + CASE d.privilege_type + WHEN 'CONNECT' THEN 'c' + WHEN 'CREATE' THEN 'C' + WHEN 'DELETE' THEN 'd' + WHEN 'EXECUTE' THEN 'X' + WHEN 'INSERT' THEN 'a' + WHEN 'REFERENCES' THEN 'x' + WHEN 'SELECT' THEN 'r' + WHEN 'TEMPORARY' THEN 'T' + WHEN 'TRIGGER' THEN 't' + WHEN 'TRUNCATE' THEN 'D' + WHEN 'UPDATE' THEN 'w' + WHEN 'USAGE' THEN 'U' + ELSE 'UNKNOWN' + END AS privilege_type + FROM + (SELECT defaclacl defacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='S') acl, + aclexplode(defacl) d + ) d + LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid) + LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid) +GROUP BY g.rolname, gt.rolname +UNION ALL +SELECT + 'deffuncacl' as deftype, COALESCE(gt.rolname, 'public') grantee, g.rolname grantor, array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable +FROM + (SELECT + d.grantee, d.grantor, d.is_grantable, + CASE d.privilege_type + WHEN 'CONNECT' THEN 'c' + WHEN 'CREATE' THEN 'C' + WHEN 'DELETE' THEN 'd' + WHEN 'EXECUTE' THEN 'X' + WHEN 'INSERT' THEN 'a' + WHEN 'REFERENCES' THEN 'x' + WHEN 'SELECT' THEN 'r' + WHEN 'TEMPORARY' THEN 'T' + WHEN 'TRIGGER' THEN 't' + WHEN 'TRUNCATE' THEN 'D' + WHEN 'UPDATE' THEN 'w' + WHEN 'USAGE' THEN 'U' + ELSE 'UNKNOWN' + END AS privilege_type + FROM + (SELECT defaclacl defacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='f') acl, + aclexplode(defacl) d + ) d + LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid) + LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid) +GROUP BY g.rolname, gt.rolname +UNION ALL +SELECT + 'deftypeacl' as deftype, COALESCE(gt.rolname, 'public') grantee, g.rolname grantor, array_agg(privilege_type) as privileges, array_agg(is_grantable) as grantable +FROM + (SELECT + d.grantee, d.grantor, d.is_grantable, + CASE d.privilege_type + WHEN 'CONNECT' THEN 'c' + WHEN 'CREATE' THEN 'C' + WHEN 'DELETE' THEN 'd' + WHEN 'EXECUTE' THEN 'X' + WHEN 'INSERT' THEN 'a' + WHEN 'REFERENCES' THEN 'x' + WHEN 'SELECT' THEN 'r' + WHEN 'TEMPORARY' THEN 'T' + WHEN 'TRIGGER' THEN 't' + WHEN 'TRUNCATE' THEN 'D' + WHEN 'UPDATE' THEN 'w' + WHEN 'USAGE' THEN 'U' + ELSE 'UNKNOWN' + END AS privilege_type + FROM + (SELECT defaclacl defacl FROM pg_catalog.pg_default_acl dacl WHERE dacl.defaclnamespace = 0::OID AND defaclobjtype='T') acl, + aclexplode(defacl) d + ) d + LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid) + LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid) +GROUP BY g.rolname, gt.rolname) t + +ORDER BY deftype, grantee; \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/delete.sql new file mode 100644 index 0000000..042113e --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/delete.sql @@ -0,0 +1,8 @@ +{# We need database name before we execute drop #} +{% if did %} +SELECT db.datname as name FROM pg_database as db WHERE db.oid = {{did}} +{% endif %} +{# Using name from above query we will drop the database #} +{% if datname %} + DROP DATABASE {{ conn|qtIdent(datname) }}; +{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_ctypes.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_ctypes.sql new file mode 100644 index 0000000..d559aa0 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_ctypes.sql @@ -0,0 +1,5 @@ +SELECT DISTINCT(datctype) AS cname +FROM pg_database +UNION +SELECT DISTINCT(datcollate) AS cname +FROM pg_database \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_encodings.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_encodings.sql new file mode 100644 index 0000000..03ae601 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_encodings.sql @@ -0,0 +1,17 @@ +{# +-- Given SQL is workaround +-- We need to implement a mechanism to check for valid supported server encoding +#} +SELECT * FROM +(SELECT pg_encoding_to_char(s.i) AS encoding + FROM (SELECT generate_series(0, 100, 1) as i) s) a +WHERE encoding != ''; + +{# +-- For future use, Do not delete +--SELECT * FROM +--(SELECT s.i as id, pg_encoding_to_char(s.i) +-- as encoding +-- FROM (SELECT generate_series(0, 100, 1) as i) s) a +--WHERE encoding != '' +#} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_nodes.sql new file mode 100644 index 0000000..87e32e5 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_nodes.sql @@ -0,0 +1,7 @@ +SELECT + db.oid as did, db.datname as name, ta.spcname as spcname, db.datallowconn, + has_database_privilege(db.oid, 'CREATE') as cancreate +FROM + pg_database db + LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace = ta.oid + {% if did %}WHERE db.oid={{did}}::int {% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_variables.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_variables.sql new file mode 100644 index 0000000..a9ab7a9 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/get_variables.sql @@ -0,0 +1,5 @@ +SELECT rl.*, r.rolname AS user_name, db.datname as db_name +FROM pg_db_role_setting AS rl + LEFT JOIN pg_roles AS r ON rl.setrole = r.oid + LEFT JOIN pg_database AS db ON rl.setdatabase = db.oid +WHERE setdatabase = {{did}} diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/grant.sql new file mode 100644 index 0000000..56958de --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/grant.sql @@ -0,0 +1,60 @@ +{# +# CREATE DATABSE does not allow us to run any +# other sql statments along with it, so we wrote +# seprate sql for rest alter sql statments here +#} +{% import 'macros/security.macros' as SECLABLE %} +{% import 'macros/variable.macros' as VARIABLE %} +{% import 'macros/privilege.macros' as PRIVILEGE %} +{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} +{% if data.comments %} +COMMENT ON DATABASE {{ conn|qtIdent(data.name) }} + IS {{ data.comments|qtLiteral }}; +{% endif %} + +{# We will generate Security Label SQL's using macro #} +{% if data.securities %} +{% for r in data.securities %} +{{ SECLABLE.APPLY(conn, 'DATABASE', data.name, r.provider, r.securitylabel) }} +{% endfor %} +{% endif %} + +{# We will generate Variable SQL's using macro #} +{% if data.variables %} +{% for var in data.variables %} +{% if var.value == True %} +{{ VARIABLE.APPLY(conn, data.name, var.role, var.name, 'on') }} +{% elif var.value == False %} +{{ VARIABLE.APPLY(conn, data.name, var.role, var.name, 'off') }} +{% else %} +{{ VARIABLE.APPLY(conn, data.name, var.role, var.name, var.value) }} +{% endif %} +{% endfor %} + +{% endif %} +{% if data.datacl %} +{% for priv in data.datacl %} +{{ PRIVILEGE.APPLY(conn, 'DATABASE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} + +{% if data.deftblacl %} +{% for priv in data.deftblacl %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if data.defseqacl %} +{% for priv in data.defseqacl %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if data.deffuncacl %} +{% for priv in data.deffuncacl %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if data.deftypeacl %} +{% for priv in data.deftypeacl %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/properties.sql new file mode 100644 index 0000000..978629d --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/properties.sql @@ -0,0 +1,20 @@ +SELECT + db.oid as did, db.datname as name, db.dattablespace AS spcoid, + spcname, datallowconn, pg_encoding_to_char(encoding) AS encoding, + pg_get_userbyid(datdba) AS datowner, + has_database_privilege(db.oid, 'CREATE') as cancreate, + current_setting('default_tablespace') AS default_tablespace, + descr.description as comments + ,(SELECT array_agg(provider || '=' || label) FROM pg_shseclabel sl1 WHERE sl1.objoid=db.oid) AS seclabels +FROM pg_database db + LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace=ta.OID + LEFT OUTER JOIN pg_shdescription descr ON ( + db.oid=descr.objoid AND descr.classoid='pg_database'::regclass + ) +{% if did %} +WHERE db.oid= {{did}}::int +{% endif %} +{% if name %} +WHERE db.datname = {{name|qtLiteral}} +{% endif %} +ORDER BY datname diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/update.sql new file mode 100644 index 0000000..4abad80 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/update.sql @@ -0,0 +1,178 @@ +{% import 'macros/security.macros' as SECLABLE %} +{% import 'macros/variable.macros' as VARIABLE %} +{% import 'macros/privilege.macros' as PRIVILEGE %} +{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %} +{% if data %} +{# The SQL generated below will change name #} +{% if data.old_name != data.name %} +ALTER DATABASE {{ conn|qtIdent(data.old_name) }} RENAME TO {{ conn|qtIdent(data.name) }}; +{% endif %} +{# The SQL generated below will change owner #} +{% if data.datowner %} +ALTER DATABASE {{ conn|qtIdent(data.name) }} OWNER TO {{ conn|qtIdent(data.datowner) }}; +{% endif %} +{# The SQL generated below will change comments #} +{% if data.comments %} +COMMENT ON DATABASE {{ conn|qtIdent(data.name) }} +IS '{{ data.comments }}'; +{% endif %} +{# The SQL generated below will change tablespace #} +{% if data.spcname %} +ALTER DATABASE {{ conn|qtIdent(data.name) }} SET TABLESPACE {{ conn|qtIdent(data.spcname) }}; +{% endif %} +{# The SQL generated below will change conn limit #} +{% if data.conn_limit %} +ALTER DATABASE {{ conn|qtIdent(data.name) }} WITH CONNECTION LIMIT = {{ conn|qtIdent(data.conn_limit) }}; +{% endif %} +{# The SQL generated below will change Security Label #} +{% 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, 'DATABASE', data.name, r.provider) }} +{% endfor %} +{% endif %} +{% if 'added' in seclabels and seclabels.added|length > 0 %} +{% for r in seclabels.added %} +{{ SECLABLE.APPLY(conn, 'DATABASE', data.name, r.provider, r.security_label) }} +{% endfor %} +{% endif %} +{% if 'changed' in seclabels and seclabels.changed|length > 0 %} +{% for r in seclabels.changed %} +{{ SECLABLE.APPLY(conn, 'DATABASE', data.name, r.provider, r.security_label) }} +{% endfor %} +{% endif %} +{% endif %} +{# The SQL generated below will change Variables #} +{% if data.variables 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, var.role, var.name) }} +{% endfor %} +{% endif %} +{% if 'added' in variables and variables.added|length > 0 %} +{% for var in variables.added %} +{% if var.value == True %} +{{ VARIABLE.APPLY(conn, var.database, var.role, var.name, 'on') }} +{% elif var.value == False %} +{{ VARIABLE.APPLY(conn, var.database, var.role, var.name, 'off') }} +{% else %} +{{ VARIABLE.APPLY(conn, var.database, var.role, var.name, var.value) }} +{% endif %} +{% endfor %} +{% endif %} +{% if 'changed' in variables and variables.changed|length > 0 %} +{% for var in variables.changed %} +{% if var.value == True %} +{{ VARIABLE.APPLY(conn, var.database, var.role, var.name, 'on') }} +{% elif var.value == False %} +{{ VARIABLE.APPLY(conn, var.database, var.role, var.name, 'off') }} +{% else %} +{{ VARIABLE.APPLY(conn, var.database, var.role, var.name, var.value) }} +{% endif %} +{% endfor %} +{% endif %} +{% endif %} + +{# The SQL generated below will change priviledges #} +{% if data.datacl %} +{% if 'deleted' in data.datacl %} +{% for priv in data.datacl.deleted %} +{{ PRIVILEGE.RESETALL(conn, 'DATABASE', priv.grantee, data.name) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.datacl %} +{% for priv in data.datacl.changed %} +{{ PRIVILEGE.RESETALL(conn, 'DATABASE', priv.grantee, data.name) }} +{{ PRIVILEGE.APPLY(conn, 'DATABASE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.datacl %} +{% for priv in data.datacl.added %} +{{ PRIVILEGE.APPLY(conn, 'DATABASE', priv.grantee, data.name, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% if data.deftblacl %} +{% if 'deleted' in data.deftblacl %} +{% for priv in data.deftblacl.deleted %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TABLES', priv.grantee) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.deftblacl %} +{% for priv in data.deftblacl.changed %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TABLES', priv.grantee) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.deftblacl %} +{% for priv in data.deftblacl.added %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% if data.defseqacl %} +{% if 'deleted' in data.defseqacl %} +{% for priv in data.defseqacl.deleted %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'SEQUENCES', priv.grantee) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.defseqacl %} +{% for priv in data.defseqacl.changed %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'SEQUENCES', priv.grantee) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.defseqacl %} +{% for priv in data.defseqacl.added %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% if data.deffuncacl %} +{% if 'deleted' in data.deffuncacl %} +{% for priv in data.deffuncacl.deleted %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'FUNCTIONS', priv.grantee) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.deffuncacl %} +{% for priv in data.deffuncacl.changed %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'FUNCTIONS', priv.grantee) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.deffuncacl %} +{% for priv in data.deffuncacl.added %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% if data.deftypeacl %} +{% if 'deleted' in data.deftypeacl %} +{% for priv in data.deftypeacl.deleted %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TYPES', priv.grantee) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.deftypeacl %} +{% for priv in data.deftypeacl.changed %} +{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TYPES', priv.grantee) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% if 'added' in data.deftypeacl %} +{% for priv in data.deftypeacl.added %} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant) }} +{% endfor %} +{% endif %} +{% endif %} + +{% endif %} +{# We need database name #} +{% if did %} +SELECT db.datname as name FROM pg_database as db WHERE db.oid = {{did}} +{% endif %} \ No newline at end of file diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/variables.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/variables.sql new file mode 100644 index 0000000..b316dbd --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.2_plus/variables.sql @@ -0,0 +1 @@ +SELECT name, vartype, min_val, max_val, enumvals FROM pg_settings WHERE context in ('user', 'superuser') \ No newline at end of file diff --git a/web/pgadmin/browser/utils.py b/web/pgadmin/browser/utils.py index 408003b..74a8be8 100644 --- a/web/pgadmin/browser/utils.py +++ b/web/pgadmin/browser/utils.py @@ -366,3 +366,27 @@ class PGChildNodeView(NodeView): nodes.extend(module.get_nodes(**kwargs)) return make_json_response(data=nodes) + + +def parse_db_privileges(db_privileges): + acl = {'grantor':db_privileges['grantor'], + 'grantee':db_privileges['grantee'], + 'privileges':[] + } + + privileges = [] + for idx, priv in enumerate(db_privileges['privileges']): + if db_privileges['grantable'][idx]: + privileges.append({"privilege_type": priv, + "privilege": True, + "with_grant": True + }) + else: + privileges.append({"privilege_type": priv, + "privilege": True, + "with_grant": False + }) + + acl['privileges'] = privileges + + return acl diff --git a/web/pgadmin/static/js/backform.pgadmin.js b/web/pgadmin/static/js/backform.pgadmin.js index be061e2..6662a51 100644 --- a/web/pgadmin/static/js/backform.pgadmin.js +++ b/web/pgadmin/static/js/backform.pgadmin.js @@ -75,7 +75,8 @@ 'collection': ['sub-node-collection', 'sub-node-collection', 'string'], 'uniqueColCollection': ['unique-col-collection', 'unique-col-collection', 'string'], 'switch' : 'switch', - 'select2': 'select2' + 'select2': 'select2', + 'radio': 'radio' }; var getMappedControl = Backform.getMappedControl = function(type, mode) { diff --git a/web/pgadmin/static/js/backgrid/backgrid.pgadmin.js b/web/pgadmin/static/js/backgrid/backgrid.pgadmin.js index b9a3f81..700238e 100644 --- a/web/pgadmin/static/js/backgrid/backgrid.pgadmin.js +++ b/web/pgadmin/static/js/backgrid/backgrid.pgadmin.js @@ -22,6 +22,49 @@ factory(root, root._, (root.jQuery || root.Zepto || root.ender || root.$), root.Backbone, root.Backform); } } (this, function(root, _, $, Backbone, Backform, Alertify) { + + /* + * Add mechanism in backgrid to render different types of cells in + * same column; + */ + + // Add new property cellFunction in Backgrid.Column. + _.extend(Backgrid.Column.prototype.defaults, { cellFunction: undefined }); + + _.extend(Backgrid.Row.prototype, { + makeCell: function (column) { + return new (this.getCell(column))({ + column: column, + model: this.model + }); + }, + /* + * getCell function will check and execute user given cellFunction to get + * appropriate cell class for current cell being rendered. + * User provided cellFunction must return valid cell class. + * cellFunction will be called with context (this) as column and model as + * argument. + */ + getCell: function (column) { + var cf = column.get("cellFunction"); + if (_.isFunction(cf)){ + var cell = cf.apply(column, [this.model]); + try { + return Backgrid.resolveNameToClass(cell, "Cell"); + } catch (e) { + if (e instanceof ReferenceError) { + // Fallback to column cell. + return column.get("cell"); + } else { + throw e; // Let other exceptions bubble up + } + } + } else { + return column.get("cell"); + } + } + }); + var ObjectCellEditor = Backgrid.Extension.ObjectCellEditor = Backgrid.CellEditor.extend({ modalTemplate: _.template([ '<div class="subnode-dialog" tabindex="1">',
-- Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers