[pgAdmin][RM1974] Include the hashed password in reverse engineered SQL for roles
Aditya Toshniwal <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.pgadmin.devel |
|---|---|
| Message-ID | <CAM9w-_=uXXO6QA5rYBzpZuqrt2CmR_Z+0xOKuQkrKyN0L-qjKg@mail.gmail.com> |
Hi Hackers, Attached is the patch to show the password of a login role in the SQL tab. Please note, password clause will only be visible to super users. Kindly review. -- Thanks and Regards, Aditya Toshniwal Sr. Software Engineer | EnterpriseDB India | Pune "Don't Complain about Heat, Plant a TREE"
RM1974.patch
(application/octet-stream, 11.3 KB)
diff --git a/web/pgadmin/browser/server_groups/servers/roles/__init__.py b/web/pgadmin/browser/server_groups/servers/roles/__init__.py
index 4d83f650e..8dbaf2a6a 100644
--- a/web/pgadmin/browser/server_groups/servers/roles/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/roles/__init__.py
@@ -735,9 +735,10 @@ rolmembership:{
@check_precondition()
def sql(self, gid, sid, rid):
+ show_password = self.conn.manager.user_info['is_superuser']
status, res = self.conn.execute_scalar(
render_template(
- self.sql_path + 'sql.sql'
+ self.sql_path + 'sql.sql', show_password=show_password
),
dict({'rid': rid})
)
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/roles/sql/9.1_plus/sql.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/roles/sql/9.1_plus/sql.sql
index 0fcad2679..054f1887b 100644
--- a/web/pgadmin/browser/server_groups/servers/roles/templates/roles/sql/9.1_plus/sql.sql
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/roles/sql/9.1_plus/sql.sql
@@ -16,6 +16,9 @@ FROM
-- PostgreSQL >= 9.1
CASE WHEN rolreplication THEN 'REPLICATION' ELSE 'NOREPLICATION' END ||
CASE WHEN rolconnlimit > 0 THEN E'\n CONNECTION LIMIT ' || rolconnlimit ELSE '' END ||
+{% if show_password %}
+ (SELECT COALESCE(E'\n PASSWORD ''' || rolpassword || '''', '') FROM pg_authid au WHERE au.oid=r.oid) ||
+{% endif %}
CASE WHEN rolvaliduntil IS NOT NULL THEN E'\n VALID UNTIL ' || quote_literal(rolvaliduntil::text) ELSE '' END || ';' ||
-- PostgreSQL < 9.5
CASE WHEN rolsuper AND NOT rolcatupdate THEN E'\n\nUPDATE pg_authid SET rolcatupdate=false WHERE rolname=' || pg_catalog.quote_literal(rolname) || ';' ELSE '' END AS sql
diff --git a/web/pgadmin/browser/server_groups/servers/roles/templates/roles/sql/9.4_plus/sql.sql b/web/pgadmin/browser/server_groups/servers/roles/templates/roles/sql/9.4_plus/sql.sql
index cda36c336..101ee88b8 100644
--- a/web/pgadmin/browser/server_groups/servers/roles/templates/roles/sql/9.4_plus/sql.sql
+++ b/web/pgadmin/browser/server_groups/servers/roles/templates/roles/sql/9.4_plus/sql.sql
@@ -16,6 +16,9 @@ FROM
-- PostgreSQL >= 9.1
CASE WHEN rolreplication THEN 'REPLICATION' ELSE 'NOREPLICATION' END ||
CASE WHEN rolconnlimit > 0 THEN E'\n CONNECTION LIMIT ' || rolconnlimit ELSE '' END ||
+{% if show_password %}
+ (SELECT COALESCE(E'\n PASSWORD ''' || rolpassword || '''', '') FROM pg_authid au WHERE au.oid=r.oid) ||
+{% endif %}
CASE WHEN rolvaliduntil IS NOT NULL THEN E'\n VALID UNTIL ' || quote_literal(rolvaliduntil::text) ELSE '' END || ';' AS sql
FROM
pg_roles r
diff --git a/web/pgadmin/browser/server_groups/servers/roles/tests/9.1_plus/alter_login_role_options.sql b/web/pgadmin/browser/server_groups/servers/roles/tests/9.1_plus/alter_login_role_options.sql
index 046d06912..66cf2d1bf 100644
--- a/web/pgadmin/browser/server_groups/servers/roles/tests/9.1_plus/alter_login_role_options.sql
+++ b/web/pgadmin/browser/server_groups/servers/roles/tests/9.1_plus/alter_login_role_options.sql
@@ -9,6 +9,7 @@ CREATE ROLE "Role2_$%{}[]()&*^!@""'`\/#" WITH
CREATEROLE
NOREPLICATION
CONNECTION LIMIT 100
+ PASSWORD '<PASSWORD>'
VALID UNTIL '2050-01-01 00:00:00+05:30';
ALTER ROLE "Role2_$%{}[]()&*^!@""'`\/#" IN DATABASE postgres SET application_name TO 'pg4';
diff --git a/web/pgadmin/browser/server_groups/servers/roles/tests/9.1_plus/alter_role_options.sql b/web/pgadmin/browser/server_groups/servers/roles/tests/9.1_plus/alter_role_options.sql
index fb8b74a7f..6666ed866 100644
--- a/web/pgadmin/browser/server_groups/servers/roles/tests/9.1_plus/alter_role_options.sql
+++ b/web/pgadmin/browser/server_groups/servers/roles/tests/9.1_plus/alter_role_options.sql
@@ -9,6 +9,7 @@ CREATE ROLE "Role2_$%{}[]()&*^!@""'`\/#" WITH
NOCREATEROLE
NOREPLICATION
CONNECTION LIMIT 100
+ PASSWORD '<PASSWORD>'
VALID UNTIL '2050-01-01 00:00:00+05:30';
ALTER ROLE "Role2_$%{}[]()&*^!@""'`\/#" IN DATABASE postgres SET application_name TO 'pg4';
diff --git a/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/alter_login_role_options.sql b/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/alter_login_role_options.sql
index 04ac56221..86020b6a7 100644
--- a/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/alter_login_role_options.sql
+++ b/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/alter_login_role_options.sql
@@ -9,6 +9,7 @@ CREATE ROLE "Role2_$%{}[]()&*^!@""'`\/#" WITH
CREATEROLE
NOREPLICATION
CONNECTION LIMIT 100
+ PASSWORD '<PASSWORD>'
VALID UNTIL '<TIMESTAMPTZ>';
ALTER ROLE "Role2_$%{}[]()&*^!@""'`\/#" IN DATABASE postgres SET application_name TO 'pg4';
diff --git a/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/alter_role_options.sql b/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/alter_role_options.sql
index f1572037e..e030a8c5c 100644
--- a/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/alter_role_options.sql
+++ b/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/alter_role_options.sql
@@ -9,6 +9,7 @@ CREATE ROLE "Role2_$%{}[]()&*^!@""'`\/#" WITH
NOCREATEROLE
NOREPLICATION
CONNECTION LIMIT 100
+ PASSWORD '<PASSWORD>'
VALID UNTIL '<TIMESTAMPTZ>';
ALTER ROLE "Role2_$%{}[]()&*^!@""'`\/#" IN DATABASE postgres SET application_name TO 'pg4';
diff --git a/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/test.json b/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/test.json
index 3de2d40ac..2e184feef 100644
--- a/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/test.json
+++ b/web/pgadmin/browser/server_groups/servers/roles/tests/9.4_plus/test.json
@@ -65,7 +65,8 @@
},
"expected_sql_file": "alter_role_options.sql",
"expected_msql_file": "alter_role_options.msql",
- "convert_timestamp_columns": ["rolvaliduntil"]
+ "convert_timestamp_columns": ["rolvaliduntil"],
+ "replace_password": true
},
{
"type": "delete",
@@ -138,7 +139,8 @@
},
"expected_sql_file": "alter_login_role_options.sql",
"expected_msql_file": "alter_login_role_options.msql",
- "convert_timestamp_columns": ["rolvaliduntil"]
+ "convert_timestamp_columns": ["rolvaliduntil"],
+ "replace_password": true
},
{
"type": "delete",
diff --git a/web/regression/re_sql/tests/test_resql.py b/web/regression/re_sql/tests/test_resql.py
index e9de05cac..fc1e59473 100644
--- a/web/regression/re_sql/tests/test_resql.py
+++ b/web/regression/re_sql/tests/test_resql.py
@@ -9,6 +9,7 @@
from __future__ import print_function
import json
import os
+import re
import traceback
from flask import url_for
import regression
@@ -103,7 +104,8 @@ class ReverseEngineeredSQLTestCases(BaseTestGenerator):
# while running the test cases
self.JSON_PLACEHOLDERS = {'schema_id': '<SCHEMA_ID>',
'owner': '<OWNER>',
- 'timestamptz': '<TIMESTAMPTZ>'}
+ 'timestamptz': '<TIMESTAMPTZ>',
+ 'password': '<PASSWORD>'}
resql_module_list = create_resql_module_list(
BaseTestGenerator.re_sql_module_list,
@@ -416,14 +418,7 @@ class ReverseEngineeredSQLTestCases(BaseTestGenerator):
fp = open(output_file, "r")
# Used rstrip to remove trailing \n
sql = fp.read().rstrip()
- # Replace place holder <owner> with the current username
- # used to connect to the database
- if 'username' in self.server:
- sql = sql.replace(self.JSON_PLACEHOLDERS['owner'],
- self.server['username'])
- # Convert timestamp with timezone from json file to the
- # database server's correct timestamp
- sql = self.convert_timestamptz(scenario, sql)
+ sql = self.preprocess_expected_sql(scenario, sql, resp_sql)
try:
self.assertEquals(sql, resp_sql)
except Exception as e:
@@ -477,14 +472,7 @@ class ReverseEngineeredSQLTestCases(BaseTestGenerator):
fp = open(output_file, "r")
# Used rstrip to remove trailing \n
sql = fp.read().rstrip()
- # Replace place holder <owner> with the current username
- # used to connect to the database
- if 'username' in self.server:
- sql = sql.replace(self.JSON_PLACEHOLDERS['owner'],
- self.server['username'])
- # Convert timestamp with timezone from json file to the
- # database server's correct timestamp
- sql = self.convert_timestamptz(scenario, sql)
+ sql = self.preprocess_expected_sql(scenario, sql, resp_sql)
try:
self.assertEquals(sql, resp_sql)
except Exception as e:
@@ -500,14 +488,7 @@ class ReverseEngineeredSQLTestCases(BaseTestGenerator):
return False
elif 'expected_sql' in scenario:
exp_sql = scenario['expected_sql']
- # Replace place holder <owner> with the current username
- # used to connect to the database
- if 'username' in self.server:
- exp_sql = exp_sql.replace(self.JSON_PLACEHOLDERS['owner'],
- self.server['username'])
- # Convert timestamp with timezone from json file to the
- # database server's correct timestamp
- sql = self.convert_timestamptz(scenario, exp_sql)
+ exp_sql = self.preprocess_expected_sql(scenario, exp_sql, resp_sql)
try:
self.assertEquals(exp_sql, resp_sql)
except Exception as e:
@@ -643,6 +624,38 @@ class ReverseEngineeredSQLTestCases(BaseTestGenerator):
return data
+ def preprocess_expected_sql(self, scenario, sql, resp_sql):
+ """
+ This function preprocesses expected sql before comparing
+ it with response sql.
+ :param data: sql
+ :param data: resp_sql
+ :return:
+ """
+ # Replace place holder <owner> with the current username
+ # used to connect to the database
+ if 'username' in self.server:
+ sql = sql.replace(self.JSON_PLACEHOLDERS['owner'],
+ self.server['username'])
+ # Convert timestamp with timezone from json file to the
+ # database server's correct timestamp
+ sql = self.convert_timestamptz(scenario, sql)
+
+ # extract password fields from response and replace in expected
+ # to match the response
+ if 'replace_password' in scenario:
+ password = ''
+ for line in resp_sql.split('\n'):
+ if 'PASSWORD' in line:
+ found = re.search("'([\w\W]*)'", line)
+ if found:
+ password = found.groups(0)[0]
+ break
+
+ sql = sql.replace(self.JSON_PLACEHOLDERS['password'], password)
+
+ return sql
+
def replace_placeholder_with_id(self, value):
"""
This function is used to replace the place holder with id.