public inbox for [email protected]
help / color / mirror / Atom feed[pgAdmin][RM5764] : RLS SQL Incorrectly generated
2+ messages / 2 participants
[nested] [flat]
* [pgAdmin][RM5764] : RLS SQL Incorrectly generated
@ 2020-09-07 12:25 Pradip Parkale <[email protected]>
2020-09-07 13:18 ` Re: [pgAdmin][RM5764] : RLS SQL Incorrectly generated Akshay Joshi <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Pradip Parkale @ 2020-09-07 12:25 UTC (permalink / raw)
To: pgadmin-hackers
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [pgAdmin][RM5764] : RLS SQL Incorrectly generated
2020-09-07 12:25 [pgAdmin][RM5764] : RLS SQL Incorrectly generated Pradip Parkale <[email protected]>
@ 2020-09-07 13:18 ` Akshay Joshi <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Akshay Joshi @ 2020-09-07 13:18 UTC (permalink / raw)
To: Pradip Parkale <[email protected]>; +Cc: pgadmin-hackers
Thanks, patch applied.
On Mon, Sep 7, 2020 at 5:55 PM Pradip Parkale <
[email protected]> wrote:
>
> Hi Hackers,
>
> Please find the attached patch for the RLS SQL error.It was incorrectly
> generating.
> --
> Thanks & Regards,
> Pradip Parkale
> Software Engineer | EnterpriseDB Corporation
>
--
*Thanks & Regards*
*Akshay Joshi*
*pgAdmin Hacker | Sr. Software Architect*
*EDB Postgres <http://edbpostgres.com>*
*Mobile: +91 976-788-8246*
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-09-07 13:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-07 12:25 [pgAdmin][RM5764] : RLS SQL Incorrectly generated Pradip Parkale <[email protected]>
2020-09-07 13:18 ` Akshay Joshi <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox