Hi Hackers,
This patch tackles the visualization of properties on a GreenPlum Database.
Previously when you tried to access these properties a SQL error was
displayed.
Also in the patch we made some correction to feature tests, and skipped
other test that were failing against GreenPlum
Thanks
Joao
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 00000000..48e2a95b
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/acl.sql
@@ -0,0 +1,35 @@
+SELECT
+ 'datacl' AS deftype, COALESCE(gt.rolname, 'PUBLIC') AS grantee,
+ g.rolname AS 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
+ (d).grantee AS grantee, (d).grantor AS grantor,
+ (d).is_grantable AS is_grantable,
+ (d).privilege_type AS privilege_type
+ FROM
+ (SELECT aclexplode(db.datacl) AS d FROM pg_database db
+ WHERE db.oid = {{ did|qtLiteral }}::OID) a
+ ) 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;
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 00000000..aa08b56b
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/defacl.sql
@@ -0,0 +1,34 @@
+SELECT
+ CASE (a.deftype)
+ WHEN 'r' THEN 'deftblacl'
+ WHEN 'S' THEN 'defseqacl'
+ WHEN 'f' THEN 'deffuncacl'
+ WHEN 'T' THEN 'deftypeacl'
+ END AS deftype,
+ COALESCE(gt.rolname, 'PUBLIC') grantee, g.rolname grantor,
array_agg(a.privilege_type) as privileges, array_agg(a.is_grantable) as
grantable
+FROM
+ (SELECT
+ (acl).grantee as grantee, (acl).grantor AS grantor, (acl).is_grantable
AS is_grantable,
+ CASE (acl).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,
+ defaclobjtype as deftype
+ FROM
+ (SELECT defaclobjtype, aclexplode(defaclacl) as acl FROM
pg_catalog.pg_default_acl dacl
+ WHERE dacl.defaclnamespace = 0::OID) d) a
+ LEFT JOIN pg_catalog.pg_roles g ON (a.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (a.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname, a.deftype
+ORDER BY a.deftype
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 00000000..46246ef6
--- /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_variables.sql
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/get_variables.sql
new file mode 100644
index 00000000..d76d02b3
--- /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/properties.sql
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/properties.sql
new file mode 100644
index 00000000..a251ebd5
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.1_plus/properties.sql
@@ -0,0 +1,43 @@
+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, datcollate, datctype, datconnlimit,
+ has_database_privilege(db.oid, 'CREATE') AS cancreate,
+ current_setting('default_tablespace') AS default_tablespace,
+ descr.description AS comments, db.datistemplate AS is_template,
+ {### Default ACL for Tables ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'r' AND defaclnamespace = 0::OID
+ ), ', ')) AS tblacl,
+ {### Default ACL for Sequnces ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'S' AND defaclnamespace = 0::OID
+ ), ', ')) AS seqacl,
+ {### Default ACL for Functions ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'f' AND defaclnamespace = 0::OID
+ ), ', ')) AS funcacl,
+ array_to_string(datacl::text[], ', ') AS acl
+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
+ )
+WHERE {% if did %}
+db.oid = {{ did|qtLiteral }}::OID{% else %}{% if name %}
+db.datname = {{ name|qtLiteral }}::text{% else %}
+db.oid > {{ last_system_oid|qtLiteral }}::OID
+{% endif %}{% endif %}
+{% if db_restrictions %}
+
+AND
+db.datname in ({{db_restrictions}})
+{% endif %}
+
+ORDER BY datname;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/acl.sql
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/acl.sql
index 48e2a95b..226f4b22 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/acl.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/acl.sql
@@ -4,7 +4,9 @@ SELECT
array_agg(is_grantable) AS grantable
FROM
(SELECT
- d.grantee, d.grantor, d.is_grantable,
+ d.grantee,
+ d.grantor,
+ d.is_grantable,
CASE d.privilege_type
WHEN 'CONNECT' THEN 'c'
WHEN 'CREATE' THEN 'C'
@@ -22,14 +24,36 @@ FROM
END AS privilege_type
FROM
(SELECT
- (d).grantee AS grantee, (d).grantor AS grantor,
- (d).is_grantable AS is_grantable,
- (d).privilege_type AS privilege_type
- FROM
- (SELECT aclexplode(db.datacl) AS d FROM pg_database db
- WHERE db.oid = {{ did|qtLiteral }}::OID) a
+ u_grantor.oid AS grantor,
+ grantee.oid AS grantee,
+ pr.type AS privilege_type,
+ aclcontains(c.datacl, makeaclitem(grantee.oid, u_grantor.oid,
pr.type, true)) AS is_grantable
+ FROM pg_database c, pg_authid u_grantor, (
+ SELECT pg_authid.oid, pg_authid.rolname
+ FROM pg_authid
+ UNION ALL
+ SELECT 0::oid AS oid, 'PUBLIC') grantee(oid, rolname),
+ ( SELECT 'SELECT'
+ UNION ALL
+ SELECT 'INSERT'
+ UNION ALL
+ SELECT 'UPDATE'
+ UNION ALL
+ SELECT 'DELETE'
+ UNION ALL
+ SELECT 'TRUNCATE'
+ UNION ALL
+ SELECT 'REFERENCES'
+ UNION ALL
+ SELECT 'TRIGGER') pr(type)
+ WHERE aclcontains(c.datacl, makeaclitem(grantee.oid, u_grantor.oid,
pr.type, false))
+ AND (pg_has_role(u_grantor.oid, 'USAGE'::text) OR
pg_has_role(grantee.oid, 'USAGE'::text)
+ OR grantee.rolname = 'PUBLIC'::name)
+ AND c.oid = {{ did|qtLiteral }}::OID
) 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;
+
+
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/defacl.sql
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/defacl.sql
index aa08b56b..191e40cc 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/defacl.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/defacl.sql
@@ -1,34 +1,7 @@
SELECT
- CASE (a.deftype)
- WHEN 'r' THEN 'deftblacl'
- WHEN 'S' THEN 'defseqacl'
- WHEN 'f' THEN 'deffuncacl'
- WHEN 'T' THEN 'deftypeacl'
- END AS deftype,
- COALESCE(gt.rolname, 'PUBLIC') grantee, g.rolname grantor,
array_agg(a.privilege_type) as privileges, array_agg(a.is_grantable) as
grantable
-FROM
- (SELECT
- (acl).grantee as grantee, (acl).grantor AS grantor, (acl).is_grantable
AS is_grantable,
- CASE (acl).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,
- defaclobjtype as deftype
- FROM
- (SELECT defaclobjtype, aclexplode(defaclacl) as acl FROM
pg_catalog.pg_default_acl dacl
- WHERE dacl.defaclnamespace = 0::OID) d) a
- LEFT JOIN pg_catalog.pg_roles g ON (a.grantor = g.oid)
- LEFT JOIN pg_catalog.pg_roles gt ON (a.grantee = gt.oid)
-GROUP BY g.rolname, gt.rolname, a.deftype
-ORDER BY a.deftype
+ '' AS deftype,
+ '' AS grantee,
+ '' AS grantor,
+ '' AS grantor,
+ '' AS privileges,
+ '' AS grantable
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/get_ctypes.sql
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/get_ctypes.sql
index 46246ef6..523db309 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/get_ctypes.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/get_ctypes.sql
@@ -1,5 +1,3 @@
-SELECT DISTINCT(datctype) AS cname
-FROM pg_database
+SELECT current_setting('lc_ctype') as cname
UNION
-SELECT DISTINCT(datcollate) AS cname
-FROM pg_database
\ No newline at end of file
+SELECT current_setting('lc_collate') as cname
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/get_variables.sql
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/get_variables.sql
index d76d02b3..d5384c64 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/get_variables.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/get_variables.sql
@@ -1,5 +1,2 @@
- 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}}
+SELECT NULL
+WHERE false
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/properties.sql
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/properties.sql
index a251ebd5..5db9b882 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/properties.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/properties.sql
@@ -1,28 +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, datcollate, datctype, datconnlimit,
+ pg_get_userbyid(datdba) AS datowner,
+ (select current_setting('lc_collate')) as datcollate,
+ (select current_setting('lc_ctype')) as datctype,
+ datconnlimit,
has_database_privilege(db.oid, 'CREATE') AS cancreate,
current_setting('default_tablespace') AS default_tablespace,
descr.description AS comments, db.datistemplate AS is_template,
{### Default ACL for Tables ###}
- (SELECT array_to_string(ARRAY(
- SELECT array_to_string(defaclacl::text[], ', ')
- FROM pg_default_acl
- WHERE defaclobjtype = 'r' AND defaclnamespace = 0::OID
- ), ', ')) AS tblacl,
+ '' AS tblacl,
{### Default ACL for Sequnces ###}
- (SELECT array_to_string(ARRAY(
- SELECT array_to_string(defaclacl::text[], ', ')
- FROM pg_default_acl
- WHERE defaclobjtype = 'S' AND defaclnamespace = 0::OID
- ), ', ')) AS seqacl,
+ '' AS seqacl,
{### Default ACL for Functions ###}
- (SELECT array_to_string(ARRAY(
- SELECT array_to_string(defaclacl::text[], ', ')
- FROM pg_default_acl
- WHERE defaclobjtype = 'f' AND defaclnamespace = 0::OID
- ), ', ')) AS funcacl,
+ '' AS funcacl,
array_to_string(datacl::text[], ', ') AS acl
FROM pg_database db
LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace=ta.OID
diff --git a/web/pgadmin/feature_tests/query_tool_tests.py
b/web/pgadmin/feature_tests/query_tool_tests.py
index 4122ddf3..63c29d04 100644
--- a/web/pgadmin/feature_tests/query_tool_tests.py
+++ b/web/pgadmin/feature_tests/query_tool_tests.py
@@ -53,16 +53,22 @@ class QueryToolFeatureTest(BaseFeatureTest):
# explain query with verbose and cost
print("Explain query with verbose and cost... ",
file=sys.stderr, end="")
- self._query_tool_explain_with_verbose_and_cost()
- print("OK.", file=sys.stderr)
- self._clear_query_tool()
+ if self._test_explain_plan_feature():
+ self._query_tool_explain_with_verbose_and_cost()
+ print("OK.", file=sys.stderr)
+ self._clear_query_tool()
+ else:
+ print("Skipped.", file=sys.stderr)
# explain analyze query with buffers and timing
print("Explain analyze query with buffers and timing... ",
file=sys.stderr, end="")
- self._query_tool_explain_analyze_with_buffers_and_timing()
- print("OK.", file=sys.stderr)
- self._clear_query_tool()
+ if self._test_explain_plan_feature():
+ self._query_tool_explain_analyze_with_buffers_and_timing()
+ print("OK.", file=sys.stderr)
+ self._clear_query_tool()
+ else:
+ print("Skipped.", file=sys.stderr)
# auto commit disabled.
print("Auto commit disabled... ", file=sys.stderr, end="")
@@ -567,3 +573,12 @@ SELECT 1, pg_sleep(300)"""
self.page.find_by_xpath(
'//div[contains(@class, "sql-editor-message") and
contains(string(), "canceling statement due to user request")]'
)
+
+ def _test_explain_plan_feature(self):
+ connection = test_utils.get_db_connection(self.server['db'],
+ self.server['username'],
+ self.server['db_password'],
+ self.server['host'],
+ self.server['port'],
+ self.server['sslmode'])
+ return connection.server_version > 90100
diff --git a/web/pgadmin/feature_tests/xss_checks_roles_control_test.py
b/web/pgadmin/feature_tests/xss_checks_roles_control_test.py
index e73826f7..1120cad5 100644
--- a/web/pgadmin/feature_tests/xss_checks_roles_control_test.py
+++ b/web/pgadmin/feature_tests/xss_checks_roles_control_test.py
@@ -21,6 +21,10 @@ class CheckRoleMembershipControlFeatureTest(BaseFeatureTest):
]
def before(self):
+ with test_utils.Database(self.server) as (connection, _):
+ if connection.server_version < 90100:
+ self.skipTest("Membership is not present in pgAdmin below PG
v9.1")
+
# Some test function is needed for debugger
test_utils.create_role(self.server, "postgres",
"test_role")
diff --git a/web/regression/python_test_utils/test_utils.py
b/web/regression/python_test_utils/test_utils.py
index 5dc0d128..b934e907 100644
--- a/web/regression/python_test_utils/test_utils.py
+++ b/web/regression/python_test_utils/test_utils.py
@@ -302,15 +302,19 @@ def create_role(server, db_name, role_name="test_role"):
old_isolation_level = connection.isolation_level
connection.set_isolation_level(0)
pg_cursor = connection.cursor()
- pg_cursor.execute('''
+ sql_query = '''
CREATE USER "%s" WITH
LOGIN
NOSUPERUSER
INHERIT
CREATEDB
NOCREATEROLE
- NOREPLICATION
''' % (role_name)
+ if connection.server_version > 90100:
+ sql_query += '\nNOREPLICATION'
+
+ pg_cursor.execute(
+ sql_query
)
connection.set_isolation_level(old_isolation_level)
connection.commit()
