public inbox for [email protected]
help / color / mirror / Atom feedFrom: Murtuza Zabuawala <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: PATCH: To fix the isssue where several default privileges cause error (pgAdmin4)
Date: Fri, 16 Sep 2016 16:47:05 +0530
Message-ID: <CAKKotZTS-f1UHFM5WhvjwOqQZnT9EGJOEDaOBAeqea8A4=nw-g@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi,
PFA patch to fix the issue where several default privileges cause error on
database.
RM#1670
*Issue:*
There were multiple rows returned by subquery that was the issue.
--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[application/octet-stream] RM_1670.patch (15.3K, 3-RM_1670.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/properties.sql
index 8c9d47a..7e18973 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.1_plus/sql/properties.sql
@@ -12,9 +12,24 @@ SELECT
CASE
WHEN nspname LIKE E'pg\\_%' THEN true
ELSE false END AS is_sys_object,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.oid) AS tblacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'S' AND defaclnamespace = nsp.oid) AS seqacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'f' AND defaclnamespace = nsp.oid) AS funcacl,
+ {### Default ACL for Tables ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.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 = nsp.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 = nsp.oid
+ ), ', ')) AS funcacl,
(SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS seclabels
FROM
pg_namespace nsp
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/properties.sql
index e46a630..3cb0572 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/pg/9.2_plus/sql/properties.sql
@@ -12,10 +12,30 @@ SELECT
CASE
WHEN nspname LIKE E'pg\\_%' THEN true
ELSE false END AS is_sys_object,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.oid) AS tblacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'S' AND defaclnamespace = nsp.oid) AS seqacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'f' AND defaclnamespace = nsp.oid) AS funcacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'T' AND defaclnamespace = nsp.oid) AS typeacl,
+ {### Default ACL for Tables ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.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 = nsp.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 = nsp.oid
+ ), ', ')) AS funcacl,
+ {### Default ACL for Type ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'T' AND defaclnamespace = nsp.oid
+ ), ', ')) AS typeacl,
(SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS seclabels
FROM
pg_namespace nsp
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/properties.sql
index fcb31ce..f3e7a08 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.1_plus/sql/properties.sql
@@ -12,9 +12,24 @@ SELECT
CASE
WHEN nspname LIKE E'pg\\_%' THEN true
ELSE false END AS is_sys_object,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.oid) AS tblacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'S' AND defaclnamespace = nsp.oid) AS seqacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'f' AND defaclnamespace = nsp.oid) AS funcacl
+ {### Default ACL for Tables ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.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 = nsp.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 = nsp.oid
+ ), ', ')) AS funcacl
FROM
pg_namespace nsp
LEFT OUTER JOIN pg_description des ON
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/properties.sql
index 001b1ab..33dc16c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schema/ppas/9.2_plus/sql/properties.sql
@@ -12,10 +12,30 @@ SELECT
CASE
WHEN nspname LIKE E'pg\\_%' THEN true
ELSE false END AS is_sys_object,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.oid) AS tblacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'S' AND defaclnamespace = nsp.oid) AS seqacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'f' AND defaclnamespace = nsp.oid) AS funcacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'T' AND defaclnamespace = nsp.oid) AS typeacl
+ {### Default ACL for Tables ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.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 = nsp.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 = nsp.oid
+ ), ', ')) AS funcacl,
+ {### Default ACL for Type ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'T' AND defaclnamespace = nsp.oid
+ ), ', ')) AS typeacl
FROM
pg_namespace nsp
LEFT OUTER JOIN pg_description des ON
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
index 34969d6..65711ce 100644
--- 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
@@ -5,9 +5,24 @@ SELECT
has_database_privilege(db.oid, 'CREATE') as cancreate,
current_setting('default_tablespace') AS default_tablespace,
descr.description as comments,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'r' AND defaclnamespace = 0::OID) AS tblacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'S' AND defaclnamespace = 0::OID) AS seqacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'f' AND defaclnamespace = 0::OID) AS funcacl,
+ {### 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
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
index 3c6a749..d519823 100644
--- 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
@@ -5,10 +5,30 @@ SELECT
has_database_privilege(db.oid, 'CREATE') as cancreate,
current_setting('default_tablespace') AS default_tablespace,
descr.description as comments,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'r' AND defaclnamespace = 0::OID) AS tblacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'S' AND defaclnamespace = 0::OID) AS seqacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'f' AND defaclnamespace = 0::OID) AS funcacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'T' AND defaclnamespace = 0::OID) AS typeacl,
+ {### 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,
+ {### Default ACL for Type ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'T' AND defaclnamespace = 0::OID
+ ), ', ')) AS typeacl,
(SELECT array_agg(provider || '=' || label) FROM pg_shseclabel sl1 WHERE sl1.objoid=db.oid) AS seclabels,
array_to_string(datacl::text[], ', ') AS acl
FROM pg_database db
@@ -22,4 +42,4 @@ db.datname = {{ name|qtLiteral }}::text{% else %}
db.oid > {{ last_system_oid|qtLiteral }}::OID
{% endif %}{% endif %}
-ORDER BY datname
+ORDER BY datname;
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/properties.sql
index 3c6a749..d519823 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/properties.sql
@@ -5,10 +5,30 @@ SELECT
has_database_privilege(db.oid, 'CREATE') as cancreate,
current_setting('default_tablespace') AS default_tablespace,
descr.description as comments,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'r' AND defaclnamespace = 0::OID) AS tblacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'S' AND defaclnamespace = 0::OID) AS seqacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'f' AND defaclnamespace = 0::OID) AS funcacl,
- (SELECT array_to_string(defaclacl::text[], ', ') FROM pg_default_acl WHERE defaclobjtype = 'T' AND defaclnamespace = 0::OID) AS typeacl,
+ {### 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,
+ {### Default ACL for Type ###}
+ (SELECT array_to_string(ARRAY(
+ SELECT array_to_string(defaclacl::text[], ', ')
+ FROM pg_default_acl
+ WHERE defaclobjtype = 'T' AND defaclnamespace = 0::OID
+ ), ', ')) AS typeacl,
(SELECT array_agg(provider || '=' || label) FROM pg_shseclabel sl1 WHERE sl1.objoid=db.oid) AS seclabels,
array_to_string(datacl::text[], ', ') AS acl
FROM pg_database db
@@ -22,4 +42,4 @@ db.datname = {{ name|qtLiteral }}::text{% else %}
db.oid > {{ last_system_oid|qtLiteral }}::OID
{% endif %}{% endif %}
-ORDER BY datname
+ORDER BY datname;
\ No newline at end of file
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: PATCH: To fix the isssue where several default privileges cause error (pgAdmin4)
In-Reply-To: <CAKKotZTS-f1UHFM5WhvjwOqQZnT9EGJOEDaOBAeqea8A4=nw-g@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