public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pradip Parkale <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin][RM5764] : RLS SQL Incorrectly generated
Date: Mon, 7 Sep 2020 17:55:39 +0530
Message-ID: <CAJ9T6Svxbd1RenFrU6J_FQfq2NqoL+NMst7Cv=VTc95Z6tN=Lw@mail.gmail.com> (raw)
Hi Hackers,
Please find the attached patch for the RLS SQL error.It was incorrectly
generating.
--
Thanks & Regards,
Pradip Parkale
Software Engineer | EnterpriseDB Corporation
Attachments:
[application/octet-stream] RM5764.patch (8.3K, 3-RM5764.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/row_security_policies/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/row_security_policies/__init__.py
index c899c9fcf..a82f9aa69 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/row_security_policies/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/row_security_policies/__init__.py
@@ -315,7 +315,8 @@ class RowSecurityView(PGChildNodeView):
"""
sql = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]
- ), plid=plid, scid=scid, datlastsysoid=self.datlastsysoid)
+ ), plid=plid, scid=scid, policy_table_id=tid,
+ datlastsysoid=self.datlastsysoid)
status, res = self.conn.execute_dict(sql)
if not status:
@@ -415,6 +416,7 @@ class RowSecurityView(PGChildNodeView):
try:
sql, name = row_security_policies_utils.get_sql(
self.conn, data=data, scid=scid, plid=plid,
+ policy_table_id=tid,
schema=self.schema, table=self.table)
# Most probably this is due to error
@@ -475,7 +477,7 @@ class RowSecurityView(PGChildNodeView):
for plid in data['ids']:
try:
- # Get name for policy from plid
+ # Get name of policy using plid
sql = render_template("/".join([self.template_path,
'get_policy_name.sql']),
plid=plid)
@@ -525,7 +527,7 @@ class RowSecurityView(PGChildNodeView):
data = dict(request.args)
sql, name = row_security_policies_utils.get_sql(
- self.conn, data=data, scid=scid, plid=plid,
+ self.conn, data=data, scid=scid, plid=plid, policy_table_id=tid,
schema=self.schema, table=self.table)
if not isinstance(sql, str):
return sql
@@ -554,7 +556,7 @@ class RowSecurityView(PGChildNodeView):
SQL = row_security_policies_utils.get_reverse_engineered_sql(
self.conn, schema=self.schema, table=self.table, scid=scid,
- plid=plid, datlastsysoid=self.datlastsysoid)
+ plid=plid, policy_table_id=tid, datlastsysoid=self.datlastsysoid)
return ajax_response(response=SQL)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/row_security_policies/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/row_security_policies/utils.py
index 2375ef9f0..339a89916 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/row_security_policies/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/row_security_policies/utils.py
@@ -68,13 +68,15 @@ def get_sql(conn, **kwargs):
data = kwargs.get('data')
scid = kwargs.get('scid')
plid = kwargs.get('plid')
+ policy_table_id = kwargs.get('policy_table_id')
schema = kwargs.get('schema')
table = kwargs.get('table')
template_path = kwargs.get('template_path', None)
if plid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
- schema=schema, plid=plid, scid=scid)
+ schema=schema, plid=plid, scid=scid,
+ policy_table_id=policy_table_id)
status, res = conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
@@ -110,12 +112,14 @@ def get_reverse_engineered_sql(conn, **kwargs):
table = kwargs.get('table')
scid = kwargs.get('scid')
plid = kwargs.get('plid')
+ policy_table_id = kwargs.get('policy_table_id')
datlastsysoid = kwargs.get('datlastsysoid')
template_path = kwargs.get('template_path', None)
with_header = kwargs.get('with_header', True)
SQL = render_template("/".join(
- [template_path, 'properties.sql']), plid=plid, scid=scid)
+ [template_path, 'properties.sql']), plid=plid, scid=scid,
+ policy_table_id=policy_table_id)
status, res = conn.execute_dict(SQL)
if not status:
@@ -130,6 +134,7 @@ def get_reverse_engineered_sql(conn, **kwargs):
data['table'] = table
SQL, name = get_sql(conn, data=data, scid=scid, plid=None,
+ policy_table_id=policy_table_id,
datlastsysoid=datlastsysoid, schema=schema,
table=table)
if with_header:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/10_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/10_plus/properties.sql
index c3bc2b12d..6d50c3234 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/10_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/10_plus/properties.sql
@@ -13,9 +13,10 @@ FROM
pg_policy pl
JOIN pg_policies rw ON pl.polname=rw.policyname
JOIN pg_namespace n ON n.nspname=rw.schemaname
+JOIN pg_class rel on rel.relname=rw.tablename
WHERE
{% if plid %}
- pl.oid = {{ plid }} and n.oid = {{ scid }};
+ pl.oid = {{ plid }} and n.oid = {{ scid }} and rel.relfilenode = {{ policy_table_id }};
{% endif %}
{% if tid %}
pl.polrelid = {{ tid }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/9.5_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/9.5_plus/properties.sql
index d69776194..dcaa60cb9 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/9.5_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/9.5_plus/properties.sql
@@ -12,9 +12,10 @@ FROM
pg_policy pl
JOIN pg_policies rw ON pl.polname=rw.policyname
JOIN pg_namespace n ON n.nspname=rw.schemaname
+JOIN pg_class rel on rel.relname=rw.tablename
WHERE
{% if plid %}
- pl.oid = {{ plid }} and n.oid = {{ scid }};
+ pl.oid = {{ plid }} and n.oid = {{ scid }} and rel.relfilenode = {{ policy_table_id }};
{% endif %}
{% if tid %}
pl.polrelid = {{ tid }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/default/properties.sql
index d69776194..dcaa60cb9 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/row_security_policies/sql/default/properties.sql
@@ -12,9 +12,10 @@ FROM
pg_policy pl
JOIN pg_policies rw ON pl.polname=rw.policyname
JOIN pg_namespace n ON n.nspname=rw.schemaname
+JOIN pg_class rel on rel.relname=rw.tablename
WHERE
{% if plid %}
- pl.oid = {{ plid }} and n.oid = {{ scid }};
+ pl.oid = {{ plid }} and n.oid = {{ scid }} and rel.relfilenode = {{ policy_table_id }};
{% endif %}
{% if tid %}
pl.polrelid = {{ tid }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
index bf3344afa..fb0e93b94 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
@@ -547,7 +547,8 @@ class BaseTableView(PGChildNodeView, BasePartitionTable):
policy_sql = row_security_policies_utils. \
get_reverse_engineered_sql(
self.conn, schema=schema, table=table, scid=scid,
- plid=row['oid'], datlastsysoid=self.datlastsysoid,
+ plid=row['oid'], policy_table_id=tid,
+ datlastsysoid=self.datlastsysoid,
template_path=None, with_header=json_resp)
policy_sql = "\n" + policy_sql
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [pgAdmin][RM5764] : RLS SQL Incorrectly generated
In-Reply-To: <CAJ9T6Svxbd1RenFrU6J_FQfq2NqoL+NMst7Cv=VTc95Z6tN=Lw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox