public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yogesh Mahajan <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: Re: [Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC:
Date: Mon, 28 Mar 2022 16:42:19 +0530
Message-ID: <CAMa=N=OS_jw+PWo21PHw2xhGTQW=sG2XCgr_Zqq25gE4V7mj_A@mail.gmail.com> (raw)
In-Reply-To: <CAM9w-_=TAoziBkqQWkdBY2SQYwQJ4kaC6yLRuPNtRrT3Kf0LoA@mail.gmail.com>
References: <CAMa=N=NPPqARLNtwh8hBXHYwOPG6grkFUDKDw0vB74TrYfMuKg@mail.gmail.com>
<CANxoLDfuwg_z2LgnqFJeXEV_Ys2=dGNMRigcXCwvWb+9Xo4DEg@mail.gmail.com>
<CAM9w-_=TAoziBkqQWkdBY2SQYwQJ4kaC6yLRuPNtRrT3Kf0LoA@mail.gmail.com>
Hello Team,
Please find the updated patch which fixes sql revoke statements shown for
databases.
Thanks,
Yogesh Mahajan
EnterpriseDB
On Tue, Mar 1, 2022 at 12:50 PM Aditya Toshniwal <
[email protected]> wrote:
> Hi Yogesh,
>
> The original bug is not fixed with the patch. You might have fixed the
> related ones.
>
> On Mon, Feb 28, 2022 at 6:24 PM Akshay Joshi <
> [email protected]> wrote:
>
>> Hi Aditya
>>
>> Can you please review it?
>>
>> On Mon, Feb 28, 2022 at 6:03 PM Yogesh Mahajan <
>> [email protected]> wrote:
>>
>>> Hi Team,
>>>
>>> Please find a patch which fixes sql for alter default privileges.
>>>
>>> Thanks,
>>> Yogesh Mahajan
>>> EnterpriseDB
>>>
>>
>>
>> --
>> *Thanks & Regards*
>> *Akshay Joshi*
>> *pgAdmin Hacker | Principal Software Architect*
>> *EDB Postgres <http://edbpostgres.com>*
>>
>> *Mobile: +91 976-788-8246*
>>
>
>
> --
> Thanks,
> Aditya Toshniwal
> pgAdmin Hacker | Software Architect | *edbpostgres.com*
> <http://edbpostgres.com;
> "Don't Complain about Heat, Plant a TREE"
>
Attachments:
[application/octet-stream] RM_4256_v2.patch (84.9K, 3-RM_4256_v2.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/__init__.py
index 5ddc31c3f..63a6e8ac8 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/__init__.py
@@ -426,7 +426,7 @@ class DatabaseView(PGChildNodeView):
SQL = render_template(
"/".join([self.template_path, 'defacl.sql']),
- did=did, conn=self.conn
+ did=did, conn=self.conn, grant_reovke_sql=False
)
status, defaclres = self.conn.execute_dict(SQL)
if not status:
@@ -1172,7 +1172,7 @@ class DatabaseView(PGChildNodeView):
SQL = render_template(
"/".join([self.template_path, 'defacl.sql']),
- did=did, conn=self.conn
+ did=did, conn=self.conn, grant_reovke_sql=True
)
status, defaclres = self.conn.execute_dict(SQL)
if not status:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/9.2_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/9.2_plus/sql/create.sql
index 874236465..bbbb2477c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/9.2_plus/sql/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/9.2_plus/sql/create.sql
@@ -27,7 +27,7 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{{ DEFAULT_PRIVILEGE.SET(
conn, 'SCHEMA', data.name, type, priv.grantee,
- priv.without_grant, priv.with_grant
+ priv.without_grant, priv.with_grant, priv.grantor
) }}{% endfor %}
{% endif %}
{% endfor %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/9.2_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/9.2_plus/sql/update.sql
index 5bb1cdf02..e04140875 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/9.2_plus/sql/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/9.2_plus/sql/update.sql
@@ -50,22 +50,22 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{% if data[defacl] %}{% set acl = data[defacl] %}
{% if 'deleted' in acl %}
{% for priv in acl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in acl %}
{% for priv in acl.changed %}
{% if priv.grantee != priv.old_grantee %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.old_grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.old_grantee, priv.grantor) }}
{% else %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee, priv.grantor) }}
{% endif %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in acl %}
{% for priv in acl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/default/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/default/sql/create.sql
index 642045e7f..f148797cc 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/default/sql/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/default/sql/create.sql
@@ -27,7 +27,7 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{{ DEFAULT_PRIVILEGE.SET(
conn, 'SCHEMA', data.name, type, priv.grantee,
- priv.without_grant, priv.with_grant
+ priv.without_grant, priv.with_grant, priv.grantor
) }}{% endfor %}
{% endif %}
{% endfor %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/default/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/default/sql/update.sql
index 495aaa538..315dc312d 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/default/sql/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/pg/default/sql/update.sql
@@ -50,22 +50,22 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{% if data[defacl] %}{% set acl = data[defacl] %}
{% if 'deleted' in acl %}
{% for priv in acl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in acl %}
{% for priv in acl.changed %}
{% if priv.grantee != priv.old_grantee %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.old_grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.old_grantee, priv.grantor) }}
{% else %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee, priv.grantor) }}
{% endif %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in acl %}
{% for priv in acl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.1_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.1_plus/sql/create.sql
index a6824c229..7b3367f1c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.1_plus/sql/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.1_plus/sql/create.sql
@@ -27,7 +27,7 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{{ DEFAULT_PRIVILEGE.SET(
conn, 'SCHEMA', data.name, type, priv.grantee,
- priv.without_grant, priv.with_grant
+ priv.without_grant, priv.with_grant, priv.grantor
) }}{% endfor %}
{% endif %}
{% endfor %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.1_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.1_plus/sql/update.sql
index 2535732fe..9a263662e 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.1_plus/sql/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.1_plus/sql/update.sql
@@ -69,22 +69,22 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{% if data.deftblacl %}
{% if 'deleted' in data.deftblacl %}
{% for priv in data.deftblacl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.deftblacl %}
{% for priv in data.deftblacl.changed %}
{% if priv.grantee != priv.old_grantee %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.old_grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.old_grantee, priv.grantor) }}
{% else %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.grantee, priv.grantor) }}
{% endif %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.deftblacl %}
{% for priv in data.deftblacl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
@@ -93,18 +93,18 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{% if data.defseqacl %}
{% if 'deleted' in data.defseqacl %}
{% for priv in data.defseqacl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.defseqacl %}
{% for priv in data.defseqacl.changed %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.grantor) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.defseqacl %}
{% for priv in data.defseqacl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
@@ -113,18 +113,18 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{% if data.deffuncacl %}
{% if 'deleted' in data.deffuncacl %}
{% for priv in data.deffuncacl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.deffuncacl %}
{% for priv in data.deffuncacl.changed %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.grantor) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.deffuncacl %}
{% for priv in data.deffuncacl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.2_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.2_plus/sql/create.sql
index 874236465..bbbb2477c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.2_plus/sql/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.2_plus/sql/create.sql
@@ -27,7 +27,7 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{{ DEFAULT_PRIVILEGE.SET(
conn, 'SCHEMA', data.name, type, priv.grantee,
- priv.without_grant, priv.with_grant
+ priv.without_grant, priv.with_grant, priv.grantor
) }}{% endfor %}
{% endif %}
{% endfor %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.2_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.2_plus/sql/update.sql
index 55b8afbca..3f9a26587 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.2_plus/sql/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/ppas/9.2_plus/sql/update.sql
@@ -69,22 +69,22 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{% if data.deftblacl %}
{% if 'deleted' in data.deftblacl %}
{% for priv in data.deftblacl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.deftblacl %}
{% for priv in data.deftblacl.changed %}
{% if priv.grantee != priv.old_grantee %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.old_grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.old_grantee, priv.grantor) }}
{% else %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, 'TABLES', priv.grantee, priv.grantor) }}
{% endif %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.deftblacl %}
{% for priv in data.deftblacl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
@@ -93,18 +93,18 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{% if data.defseqacl %}
{% if 'deleted' in data.defseqacl %}
{% for priv in data.defseqacl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.defseqacl %}
{% for priv in data.defseqacl.changed %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.grantor) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.defseqacl %}
{% for priv in data.defseqacl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
@@ -113,18 +113,18 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{% if data.deffuncacl %}
{% if 'deleted' in data.deffuncacl %}
{% for priv in data.deffuncacl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.deffuncacl %}
{% for priv in data.deffuncacl.changed %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.grantor) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.deffuncacl %}
{% for priv in data.deffuncacl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
@@ -133,18 +133,18 @@ COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
{% if data.deftypeacl %}
{% if 'deleted' in data.deftypeacl %}
{% for priv in data.deftypeacl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'TYPES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'TYPES', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.deftypeacl %}
{% for priv in data.deftypeacl.changed %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'TYPES', priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.UNSET(conn,'SCHEMA', data.name, 'TYPES', priv.grantee, priv.grantor) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.deftypeacl %}
{% for priv in data.deftypeacl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_add_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_add_priv.sql
index 82ad3b78b..4c57a1392 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_add_priv.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_add_priv.sql
@@ -9,14 +9,14 @@ GRANT CREATE ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO postgres;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT, UPDATE ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT, UPDATE ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_add_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_add_priv_msql.sql
index ca4e5eba8..adf741941 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_add_priv_msql.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_add_priv_msql.sql
@@ -1,13 +1,13 @@
GRANT CREATE ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT, UPDATE ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT, UPDATE ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_drop_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_drop_priv.sql
index 1cc638667..09c5b7f88 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_drop_priv.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_drop_priv.sql
@@ -7,8 +7,8 @@ CREATE SCHEMA IF NOT EXISTS "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO postgres;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_drop_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_drop_priv_msql.sql
index 9ebb719ae..9263bc42f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_drop_priv_msql.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_drop_priv_msql.sql
@@ -1,5 +1,5 @@
REVOKE ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FROM PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
REVOKE ALL ON SEQUENCES FROM PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
REVOKE ALL ON TYPES FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_update_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_update_priv.sql
index 5b58e2c63..1d03b22bc 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_update_priv.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_update_priv.sql
@@ -9,14 +9,14 @@ GRANT USAGE ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO postgres;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT UPDATE ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_update_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_update_priv_msql.sql
index 5f67cc02a..8ce58a3c2 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_update_priv_msql.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/alter_schema_update_priv_msql.sql
@@ -1,12 +1,12 @@
REVOKE ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FROM PUBLIC;
GRANT USAGE ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
REVOKE ALL ON TABLES FROM PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
REVOKE ALL ON SEQUENCES FROM PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT UPDATE ON SEQUENCES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/create_schema_all_options.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/create_schema_all_options.sql
index 836124403..bb31d9943 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/create_schema_all_options.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/create_schema_all_options.sql
@@ -12,14 +12,14 @@ GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO postgres;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/create_schema_all_options_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/create_schema_all_options_msql.sql
index 2c80b5146..647449cd6 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/create_schema_all_options_msql.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/pg/9.2_plus/create_schema_all_options_msql.sql
@@ -5,14 +5,14 @@ COMMENT ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_add_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_add_priv.sql
index bd5e39ffc..383f433c1 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_add_priv.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_add_priv.sql
@@ -9,14 +9,14 @@ GRANT CREATE ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO enterprisedb;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT, UPDATE ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT, UPDATE ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_add_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_add_priv_msql.sql
index 1163b0687..e8d2ad361 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_add_priv_msql.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_add_priv_msql.sql
@@ -1,17 +1,17 @@
GRANT CREATE ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT, UPDATE ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT, UPDATE ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_drop_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_drop_priv.sql
index f74bbabd4..e6d8d3c4c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_drop_priv.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_drop_priv.sql
@@ -7,8 +7,8 @@ CREATE SCHEMA IF NOT EXISTS "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO enterprisedb;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_drop_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_drop_priv_msql.sql
index 9378fcfc3..a4d92d710 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_drop_priv_msql.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_drop_priv_msql.sql
@@ -1,7 +1,7 @@
REVOKE ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FROM PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
REVOKE ALL ON SEQUENCES FROM PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
REVOKE ALL ON TYPES FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_update_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_update_priv.sql
index 33060693a..bbf6559d9 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_update_priv.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_update_priv.sql
@@ -9,14 +9,14 @@ GRANT USAGE ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO enterprisedb;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT UPDATE ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_update_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_update_priv_msql.sql
index bbc594d03..449b7d27c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_update_priv_msql.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/alter_schema_update_priv_msql.sql
@@ -2,13 +2,13 @@ REVOKE ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FROM PUBLIC;
GRANT USAGE ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
REVOKE ALL ON TABLES FROM PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT SELECT ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
REVOKE ALL ON SEQUENCES FROM PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT UPDATE ON SEQUENCES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/create_schema_all_options.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/create_schema_all_options.sql
index bb7da31f4..f205ca935 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/create_schema_all_options.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/create_schema_all_options.sql
@@ -12,14 +12,14 @@ GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO enterprisedb;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/create_schema_all_options_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/create_schema_all_options_msql.sql
index 2c80b5146..f6aea452b 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/create_schema_all_options_msql.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/ppas/9.2_plus/create_schema_all_options_msql.sql
@@ -5,14 +5,14 @@ COMMENT ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON TABLES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT ALL ON SEQUENCES TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
-ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#"
GRANT USAGE ON TYPES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/alter_online.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/alter_online.sql
index 7e5e0c320..40ec137ba 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/alter_online.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/alter_online.sql
@@ -78,18 +78,18 @@
{% if data.deftblacl %}
{% if 'deleted' in data.deftblacl %}
{% for priv in data.deftblacl.deleted %}
-{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TABLES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TABLES', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.deftblacl %}
{% for priv in data.deftblacl.changed %}
-{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TABLES', priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TABLES', priv.grantee, priv.grantor) }}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.deftblacl %}
{% for priv in data.deftblacl.added %}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% endif %}
@@ -98,18 +98,18 @@
{% if data.defseqacl %}
{% if 'deleted' in data.defseqacl %}
{% for priv in data.defseqacl.deleted %}
-{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'SEQUENCES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'SEQUENCES', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.defseqacl %}
{% for priv in data.defseqacl.changed %}
-{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'SEQUENCES', priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'SEQUENCES', priv.grantee, priv.grantor) }}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.defseqacl %}
{% for priv in data.defseqacl.added %}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% endif %}
@@ -118,18 +118,18 @@
{% if data.deffuncacl %}
{% if 'deleted' in data.deffuncacl %}
{% for priv in data.deffuncacl.deleted %}
-{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'FUNCTIONS', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'FUNCTIONS', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.deffuncacl %}
{% for priv in data.deffuncacl.changed %}
-{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'FUNCTIONS', priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'FUNCTIONS', priv.grantee, priv.grantor) }}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.deffuncacl %}
{% for priv in data.deffuncacl.added %}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% endif %}
@@ -138,18 +138,18 @@
{% if data.deftypeacl %}
{% if 'deleted' in data.deftypeacl %}
{% for priv in data.deftypeacl.deleted %}
-{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TYPES', priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TYPES', priv.grantee, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'changed' in data.deftypeacl %}
{% for priv in data.deftypeacl.changed %}
-{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TYPES', priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.RESETALL(conn, 'TYPES', priv.grantee, priv.grantor) }}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% if 'added' in data.deftypeacl %}
{% for priv in data.deftypeacl.added %}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
{% endfor %}
{% endif %}
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/defacl.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/defacl.sql
index e1284f51b..e9f9eac89 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/defacl.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/defacl.sql
@@ -1,10 +1,12 @@
-SELECT
+{% if not grant_reovke_sql %}
+(SELECT
CASE (a.deftype)
WHEN 'r' THEN 'deftblacl'
WHEN 'S' THEN 'defseqacl'
WHEN 'f' THEN 'deffuncacl'
WHEN 'T' THEN 'deftypeacl'
END AS deftype,
+ 'defaultacls' as acltype,
COALESCE(gt.rolname, 'PUBLIC') AS grantee, g.rolname AS grantor, pg_catalog.array_agg(a.privilege_type) as privileges, pg_catalog.array_agg(a.is_grantable) as grantable
FROM
(SELECT
@@ -32,3 +34,128 @@ FROM
LEFT JOIN pg_catalog.pg_roles gt ON (a.grantee = gt.oid)
GROUP BY g.rolname, gt.rolname, a.deftype
ORDER BY a.deftype
+ )
+{% else %}
+
+(SELECT
+ CASE (e.deftype)
+ WHEN 'r' THEN 'deftblacl'
+ WHEN 'S' THEN 'defseqacl'
+ WHEN 'f' THEN 'deffuncacl'
+ WHEN 'T' THEN 'deftypeacl'
+ END AS deftype,
+ 'revoke' as acltype,
+ COALESCE(gt.rolname, 'PUBLIC') AS grantee, g.rolname AS grantor, pg_catalog.array_agg(e.privilege_type) as privileges, pg_catalog.array_agg(e.is_grantable) as grantable
+FROM(
+ SELECT
+ (d.acl).grantee as grantee, (d.acl).grantor AS grantor, (d.acl).is_grantable AS is_grantable,
+ CASE (d.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,
+ d.defaclobjtype as deftype
+ FROM
+ (select
+ b.defaclobjtype,
+ pg_catalog.aclexplode(b.revoke_priv) as acl
+ from
+ (select
+ a.defaclobjtype,
+ a.defaclrole,
+ a.defaultprivileges,
+ a.acldefault,
+ array(select unnest(a.acldefault) except select unnest(a.defaultprivileges)) as revoke_priv
+ from
+ (SELECT
+ defaclobjtype,
+ defaclrole,
+ defaclacl as defaultprivileges,
+ CASE
+ WHEN defaclnamespace = 0 THEN acldefault(CASE WHEN defaclobjtype = 'S' THEN 's'::"char" ELSE defaclobjtype END, defaclrole)
+ ELSE '{}'
+ END AS acldefault
+ FROM pg_catalog.pg_default_acl dacl
+ WHERE dacl.defaclnamespace = 0::OID
+ ) a
+ ) b
+ where not b.revoke_priv = '{}'
+ ) d
+ ) e
+LEFT JOIN pg_catalog.pg_roles g ON (e.grantor = g.oid)
+LEFT JOIN pg_catalog.pg_roles gt ON (e.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname, e.deftype
+ORDER BY e.deftype)
+
+UNION
+(
+SELECT
+ CASE (e.deftype)
+ WHEN 'r' THEN 'deftblacl'
+ WHEN 'S' THEN 'defseqacl'
+ WHEN 'f' THEN 'deffuncacl'
+ WHEN 'T' THEN 'deftypeacl'
+ END AS deftype,
+ 'grant' as acltype,
+ COALESCE(gt.rolname, 'PUBLIC') AS grantee, g.rolname AS grantor, pg_catalog.array_agg(e.privilege_type) as privileges, pg_catalog.array_agg(e.is_grantable) as grantable
+FROM(
+ SELECT
+ (d.acl).grantee as grantee, (d.acl).grantor AS grantor, (d.acl).is_grantable AS is_grantable,
+ CASE (d.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,
+ d.defaclobjtype as deftype
+ FROM(
+ select
+ *,
+ pg_catalog.aclexplode(b.grant_priv) as acl
+ from
+ (select
+ a.defaclobjtype,
+ a.defaclrole,
+ a.defaultprivileges,
+ a.acldefault,
+ array(select unnest(a.defaultprivileges) except select unnest(a.acldefault)) as grant_priv
+ from
+ (SELECT
+ defaclobjtype,
+ defaclrole,
+ defaclacl as defaultprivileges,
+ CASE
+ WHEN defaclnamespace = 0
+ THEN acldefault(CASE WHEN defaclobjtype = 'S' THEN 's'::"char" ELSE defaclobjtype END, defaclrole)
+ ELSE '{}'
+ END AS acldefault
+ FROM pg_catalog.pg_default_acl dacl
+ WHERE dacl.defaclnamespace = 0::OID
+ ) a
+ ) b where not b.grant_priv = '{}'
+ ) d
+ ) e
+LEFT JOIN pg_catalog.pg_roles g ON (e.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (e.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname, e.deftype
+ORDER BY e.deftype)
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/grant.sql
index 6aa4af668..87e18ee0d 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/grant.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/9.3_plus/grant.sql
@@ -41,24 +41,43 @@ COMMENT ON DATABASE {{ conn|qtIdent(data.name) }}
{# Default privileges/ACLs for tables #}
{% if data.deftblacl %}
{% for priv in data.deftblacl %}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{% if priv.acltype == 'grant' %}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
+{% else %}
+{{ DEFAULT_PRIVILEGE.REMOVE(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
+{% endif %}
{% endfor %}
+
{% endif %}
{# Default privileges/ACLs for sequences #}
{% if data.defseqacl %}
{% for priv in data.defseqacl %}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{% if priv.acltype == 'grant' %}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
+{% else %}
+{{ DEFAULT_PRIVILEGE.REMOVE(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
+{% endif %}
{% endfor %}
{% endif %}
+
{# Default privileges/ACLs for functions #}
{% if data.deffuncacl %}
{% for priv in data.deffuncacl %}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant) }}
+{% if priv.acltype == 'grant' %}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
+{% else %}
+{{ DEFAULT_PRIVILEGE.REMOVE(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
+{% endif %}
{% endfor %}
{% endif %}
+
{# Default privileges/ACLs for types #}
{% if data.deftypeacl %}
{% for priv in data.deftypeacl %}
-{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant) }}
+{% if priv.acltype == 'grant' %}
+{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
+{% else %}
+{{ DEFAULT_PRIVILEGE.REMOVE(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }}
+{% endif %}
{% endfor %}
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/create.sql
index 58b37da76..9f153356b 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/templates/databases/sql/default/create.sql
@@ -1,7 +1,7 @@
{% if data %}
CREATE DATABASE {{ conn|qtIdent(data.name) }}
{% if data.datowner %}
- WITH {% endif %}{% if data.datowner %}
+ WITH{% endif %}{% if data.datowner %}
OWNER = {{ conn|qtIdent(data.datowner) }}{% endif %}{% if data.template %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_function.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_function.sql
new file mode 100644
index 000000000..de9c22c0e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_function.sql
@@ -0,0 +1,14 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = postgres
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_function_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_function_msql.sql
new file mode 100644
index 000000000..21f42e662
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_function_msql.sql
@@ -0,0 +1,4 @@
+
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres
+ REVOKE ALL ON FUNCTIONS FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_reset_all.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_reset_all.sql
new file mode 100644
index 000000000..ae9e747bb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_reset_all.sql
@@ -0,0 +1,12 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = postgres
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_sequences.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_sequences.sql
new file mode 100644
index 000000000..494432610
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_sequences.sql
@@ -0,0 +1,21 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = postgres
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON TABLES FROM postgres;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres
+GRANT SELECT, USAGE ON SEQUENCES TO PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON SEQUENCES FROM postgres;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_sequences_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_sequences_msql.sql
new file mode 100644
index 000000000..21fa758f5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_sequences_msql.sql
@@ -0,0 +1,9 @@
+
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres
+ REVOKE ALL ON TABLES FROM PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres
+ REVOKE ALL ON SEQUENCES FROM postgres;
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres
+GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_tables.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_tables.sql
new file mode 100644
index 000000000..ec797f442
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_tables.sql
@@ -0,0 +1,19 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = postgres
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON TABLES FROM postgres;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres
+GRANT SELECT ON TABLES TO PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_tables_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_tables_msql.sql
new file mode 100644
index 000000000..c0847b51c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_tables_msql.sql
@@ -0,0 +1,6 @@
+
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres
+ REVOKE ALL ON TABLES FROM PUBLIC;
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres
+GRANT SELECT ON TABLES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_types.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_types.sql
new file mode 100644
index 000000000..90541625d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_types.sql
@@ -0,0 +1,23 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = postgres
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON TABLES FROM postgres;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres
+GRANT SELECT, USAGE ON SEQUENCES TO PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON SEQUENCES FROM postgres;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE USAGE ON TYPES FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/test_database.json b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/test_database.json
new file mode 100644
index 000000000..f262efe3d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/test_database.json
@@ -0,0 +1,141 @@
+{
+ "scenarios": [
+ {
+ "type": "alter",
+ "name": "Alert default priviliges for functions",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "msql_endpoint": "NODE-database.msql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "deffuncacl": {
+ "deleted": [
+ {
+ "grantor": "postgres",
+ "grantee": "PUBLIC",
+ "privileges": [
+ {
+ "privilege_type": "X",
+ "privilege": true,
+ "with_grant": false
+ }
+ ],
+ "acltype": "defaultacls"
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_default_db_privileges_function.sql",
+ "expected_msql_file": "alter_default_db_privileges_function_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alert default privileges for tables",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "deftblacl": {
+ "deleted": [
+ {
+ "grantor": "postgres",
+ "grantee": "postgres",
+ "privileges": [
+ {"privilege_type":"D","privilege":true,"with_grant":false},
+ {"privilege_type":"w","privilege":true,"with_grant":false}
+ ],
+ "acltype": "deftblacl"
+ }
+ ],
+ "added": [
+ {
+ "grantee": "PUBLIC",
+ "privileges": [
+ {
+ "privilege_type": "r",
+ "privilege": true,
+ "with_grant": false
+ }
+ ],
+ "grantor": "postgres"
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_default_db_privileges_tables.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alert default privileges for sequences",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "msql_endpoint": "NODE-database.msql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "defseqacl": {
+ "deleted": [
+ {
+ "grantor": "postgres",
+ "grantee": "postgres",
+ "privileges": [
+ {
+ "privilege_type": "w",
+ "privilege": true,
+ "with_grant": false
+ }
+ ],
+ "acltype": "defaultacls"
+ }
+ ],
+ "added":[
+ {"grantee":"PUBLIC","privileges":[{"privilege_type":"U","privilege":true,"with_grant":false},
+ {"privilege_type":"r","privilege":true,"with_grant":false}],"grantor":"postgres"}]
+ },
+ "deftblacl": {"deleted":[{"grantor":"postgres","grantee":"PUBLIC","privileges":[{"privilege_type":"r","privilege":true,"with_grant":false}],"acltype":"defaultacls"}]}
+ },
+ "expected_sql_file": "alter_default_db_privileges_sequences.sql",
+ "expected_msql_file": "alter_default_db_privileges_sequences_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alert default privileges for types",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "deftypeacl": {
+ "deleted": [
+ {
+ "grantor": "postgres",
+ "grantee": "PUBLIC",
+ "privileges": [
+ {
+ "privilege_type": "U",
+ "privilege": true,
+ "with_grant": false
+ }
+ ],
+ "acltype": "defaultacls"
+ }
+ ]
+ }
+
+ },
+ "expected_sql_file": "alter_default_db_privileges_types.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alert default privileges reset all",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "deffuncacl": {"added":[{"grantee":"PUBLIC","privileges":[{"privilege_type":"X","privilege":true,"with_grant":false}],"grantor":"postgres"}]},
+ "deftypeacl": {"added":[{"grantee":"PUBLIC","privileges":[{"privilege_type":"U","privilege":true,"with_grant":false}],"grantor":"postgres"}]},
+ "deftblacl":{"added":[{"grantee":"postgres","privileges":[{"privilege_type":"a","privilege":true,"with_grant":false},{"privilege_type":"r","privilege":true,"with_grant":false},{"privilege_type":"w","privilege":true,"with_grant":false},{"privilege_type":"d","privilege":true,"with_grant":false},{"privilege_type":"D","privilege":true,"with_grant":false},{"privilege_type":"x","privilege":true,"with_grant":false},{"privilege_type":"t","privilege":true,"with_grant":false}],"grantor":"postgres"}],"deleted":[{"grantor":"postgres","grantee":"PUBLIC","privileges":[{"privilege_type":"a","privilege":true,"with_grant":false,"cid":"nn626"},{"privilege_type":"r","privilege":true,"with_grant":false,"cid":"nn627"},{"privilege_type":"w","privilege":true,"with_grant":false,"cid":"nn628"},{"privilege_type":"d","privilege":true,"with_grant":false},{"privilege_type":"D","privilege":true,"with_grant":false},{"privilege_type":"x","privilege":true,"with_grant":false},{"privilege_type":"t","privilege":true,"with_grant":false}],"acltype":"defaultacls"}]},
+ "defseqacl":{"added":[{"grantee":"postgres","privileges":[{"privilege_type":"r","privilege":true,"with_grant":false},{"privilege_type":"w","privilege":true,"with_grant":false},{"privilege_type":"U","privilege":true,"with_grant":false}],"grantor":"postgres"}],"deleted":[{"grantor":"postgres","grantee":"PUBLIC","privileges":[{"privilege_type":"r","privilege":true,"with_grant":false,"cid":"nn673"},{"privilege_type":"U","privilege":true,"with_grant":false}],"acltype":"defaultacls"}]}
+ },
+ "expected_sql_file": "alter_default_db_privileges_reset_all.sql"
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_function.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_function.sql
new file mode 100644
index 000000000..bab00465d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_function.sql
@@ -0,0 +1,14 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = enterprisedb
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_function_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_function_msql.sql
new file mode 100644
index 000000000..2cb0beecc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_function_msql.sql
@@ -0,0 +1,4 @@
+
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb
+ REVOKE ALL ON FUNCTIONS FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_reset_all.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_reset_all.sql
new file mode 100644
index 000000000..962ead7f5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_reset_all.sql
@@ -0,0 +1,12 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = enterprisedb
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_sequences.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_sequences.sql
new file mode 100644
index 000000000..9e1146890
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_sequences.sql
@@ -0,0 +1,21 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = enterprisedb
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE ALL ON TABLES FROM enterprisedb;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE ALL ON SEQUENCES FROM enterprisedb;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb
+GRANT SELECT, USAGE ON SEQUENCES TO PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_sequences_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_sequences_msql.sql
new file mode 100644
index 000000000..87538766f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_sequences_msql.sql
@@ -0,0 +1,9 @@
+
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb
+ REVOKE ALL ON TABLES FROM PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb
+ REVOKE ALL ON SEQUENCES FROM enterprisedb;
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb
+GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_tables.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_tables.sql
new file mode 100644
index 000000000..86e1b7e8b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_tables.sql
@@ -0,0 +1,19 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = enterprisedb
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb
+GRANT SELECT ON TABLES TO PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE ALL ON TABLES FROM enterprisedb;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_tables_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_tables_msql.sql
new file mode 100644
index 000000000..b6a451d03
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_tables_msql.sql
@@ -0,0 +1,6 @@
+
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb
+ REVOKE ALL ON TABLES FROM PUBLIC;
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb
+GRANT SELECT ON TABLES TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_types.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_types.sql
new file mode 100644
index 000000000..905f447f5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/alter_default_db_privileges_types.sql
@@ -0,0 +1,23 @@
+-- Database: <TEST_DB_NAME>
+
+-- DROP DATABASE IF EXISTS <TEST_DB_NAME>;
+
+CREATE DATABASE <TEST_DB_NAME>
+ WITH
+ OWNER = enterprisedb
+ ENCODING = 'UTF8'
+ LC_COLLATE = 'C'
+ LC_CTYPE = 'C'
+ TABLESPACE = pg_default
+ CONNECTION LIMIT = -1;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE ALL ON TABLES FROM enterprisedb;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE ALL ON SEQUENCES FROM enterprisedb;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb
+GRANT SELECT, USAGE ON SEQUENCES TO PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
+
+ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE USAGE ON TYPES FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/test_database.json b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/test_database.json
new file mode 100644
index 000000000..5a6d98bad
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/tests/ppas/default/test_database.json
@@ -0,0 +1,141 @@
+{
+ "scenarios": [
+ {
+ "type": "alter",
+ "name": "Alert default priviliges for functions",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "msql_endpoint": "NODE-database.msql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "deffuncacl": {
+ "deleted": [
+ {
+ "grantor": "enterprisedb",
+ "grantee": "PUBLIC",
+ "privileges": [
+ {
+ "privilege_type": "X",
+ "privilege": true,
+ "with_grant": false
+ }
+ ],
+ "acltype": "defaultacls"
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_default_db_privileges_function.sql",
+ "expected_msql_file": "alter_default_db_privileges_function_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alert default privileges for tables",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "deftblacl": {
+ "deleted": [
+ {
+ "grantor": "enterprisedb",
+ "grantee": "enterprisedb",
+ "privileges": [
+ {"privilege_type":"D","privilege":true,"with_grant":false},
+ {"privilege_type":"w","privilege":true,"with_grant":false}
+ ],
+ "acltype": "deftblacl"
+ }
+ ],
+ "added": [
+ {
+ "grantee": "PUBLIC",
+ "privileges": [
+ {
+ "privilege_type": "r",
+ "privilege": true,
+ "with_grant": false
+ }
+ ],
+ "grantor": "enterprisedb"
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_default_db_privileges_tables.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alert default privileges for sequences",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "msql_endpoint": "NODE-database.msql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "defseqacl": {
+ "deleted": [
+ {
+ "grantor": "enterprisedb",
+ "grantee": "enterprisedb",
+ "privileges": [
+ {
+ "privilege_type": "w",
+ "privilege": true,
+ "with_grant": false
+ }
+ ],
+ "acltype": "defaultacls"
+ }
+ ],
+ "added":[{"grantee":"PUBLIC","privileges":[{"privilege_type":"U","privilege":true,"with_grant":false},
+ {"privilege_type":"r","privilege":true,"with_grant":false}],"grantor":"enterprisedb"}]
+ },
+ "deftblacl": {"deleted":[{"grantor":"enterprisedb","grantee":"PUBLIC","privileges":[{"privilege_type":"r","privilege":true,"with_grant":false}],"acltype":"defaultacls"}]}
+
+ },
+ "expected_sql_file": "alter_default_db_privileges_sequences.sql",
+ "expected_msql_file": "alter_default_db_privileges_sequences_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alert default privileges for types",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "deftypeacl": {
+ "deleted": [
+ {
+ "grantor": "enterprisedb",
+ "grantee": "PUBLIC",
+ "privileges": [
+ {
+ "privilege_type": "U",
+ "privilege": true,
+ "with_grant": false
+ }
+ ],
+ "acltype": "defaultacls"
+ }
+ ]
+ }
+
+ },
+ "expected_sql_file": "alter_default_db_privileges_types.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alert default privileges reset all",
+ "endpoint": "NODE-database.obj_id",
+ "sql_endpoint": "NODE-database.sql_id",
+ "TEST_DB_NAME": "<TEST_DB_NAME>",
+ "data": {
+ "deffuncacl": {"added":[{"grantee":"PUBLIC","privileges":[{"privilege_type":"X","privilege":true,"with_grant":false}],"grantor":"enterprisedb"}]},
+ "deftypeacl": {"added":[{"grantee":"PUBLIC","privileges":[{"privilege_type":"U","privilege":true,"with_grant":false}],"grantor":"enterprisedb"}]},
+ "deftblacl":{"added":[{"grantee":"enterprisedb","privileges":[{"privilege_type":"a","privilege":true,"with_grant":false},{"privilege_type":"r","privilege":true,"with_grant":false},{"privilege_type":"w","privilege":true,"with_grant":false},{"privilege_type":"d","privilege":true,"with_grant":false},{"privilege_type":"D","privilege":true,"with_grant":false},{"privilege_type":"x","privilege":true,"with_grant":false},{"privilege_type":"t","privilege":true,"with_grant":false}],"grantor":"enterprisedb"}],"deleted":[{"grantor":"enterprisedb","grantee":"PUBLIC","privileges":[{"privilege_type":"a","privilege":true,"with_grant":false,"cid":"nn626"},{"privilege_type":"r","privilege":true,"with_grant":false,"cid":"nn627"},{"privilege_type":"w","privilege":true,"with_grant":false,"cid":"nn628"},{"privilege_type":"d","privilege":true,"with_grant":false},{"privilege_type":"D","privilege":true,"with_grant":false},{"privilege_type":"x","privilege":true,"with_grant":false},{"privilege_type":"t","privilege":true,"with_grant":false}],"acltype":"defaultacls"}]},
+ "defseqacl":{"added":[{"grantee":"enterprisedb","privileges":[{"privilege_type":"r","privilege":true,"with_grant":false},{"privilege_type":"w","privilege":true,"with_grant":false},{"privilege_type":"U","privilege":true,"with_grant":false}],"grantor":"enterprisedb"}],"deleted":[{"grantor":"enterprisedb","grantee":"PUBLIC","privileges":[{"privilege_type":"r","privilege":true,"with_grant":false,"cid":"nn673"},{"privilege_type":"U","privilege":true,"with_grant":false}],"acltype":"defaultacls"}]}
+ },
+ "expected_sql_file": "alter_default_db_privileges_reset_all.sql"
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/templates/macros/default_privilege.macros b/web/pgadmin/browser/server_groups/servers/templates/macros/default_privilege.macros
index 6372d6b58..fc564f72d 100644
--- a/web/pgadmin/browser/server_groups/servers/templates/macros/default_privilege.macros
+++ b/web/pgadmin/browser/server_groups/servers/templates/macros/default_privilege.macros
@@ -1,29 +1,38 @@
-{% macro APPLY(conn, type, role, privs, with_grant_privs) -%}
+{% macro APPLY(conn, type, role, privs, with_grant_privs, grantor) -%}
{% if privs %}
-ALTER DEFAULT PRIVILEGES
+ALTER DEFAULT PRIVILEGES FOR ROLE {{grantor}}
GRANT {{ privs|join(', ') }} ON {{ type }} TO {{ role }};
{% endif %}
{% if with_grant_privs %}
-ALTER DEFAULT PRIVILEGES
+ALTER DEFAULT PRIVILEGES FOR ROLE {{grantor}}
GRANT {{ with_grant_privs|join(', ') }} ON {{ type }} TO {{ role }} WITH GRANT OPTION;
{% endif %}
{%- endmacro %}
-{% macro RESETALL(conn, type, role) -%}
-ALTER DEFAULT PRIVILEGES
+{% macro RESETALL(conn, type, role, grantor) -%}
+ALTER DEFAULT PRIVILEGES FOR ROLE {{grantor}}
REVOKE ALL ON {{ type }} FROM {{ role }};
{%- endmacro %}
{### To allow create macro for specific database object ###}
-{% macro SET(conn, db_object_type, db_object_name, type, role, privs, with_grant_privs) -%}
+{% macro SET(conn, db_object_type, db_object_name, type, role, privs, with_grant_privs, grantor) -%}
{% if privs %}
-ALTER DEFAULT PRIVILEGES IN {{ db_object_type }} {{ conn|qtIdent(db_object_name) }}
+ALTER DEFAULT PRIVILEGES FOR ROLE {{grantor}} IN {{ db_object_type }} {{ conn|qtIdent(db_object_name) }}
GRANT {{ privs|join(', ') }} ON {{ type }} TO {{ role }};
{% endif %}
{% if with_grant_privs %}
-ALTER DEFAULT PRIVILEGES IN {{ db_object_type }} {{ conn|qtIdent(db_object_name) }}
+ALTER DEFAULT PRIVILEGES FOR ROLE {{grantor}} IN {{ db_object_type }} {{ conn|qtIdent(db_object_name) }}
GRANT {{ with_grant_privs|join(', ') }} ON {{ type }} TO {{ role }} WITH GRANT OPTION;
{% endif %}
{%- endmacro %}
-{% macro UNSET(conn, db_object_type, db_object_name, type, role) -%}
-ALTER DEFAULT PRIVILEGES IN {{ db_object_type }} {{ conn|qtIdent(db_object_name) }}
+{% macro UNSET(conn, db_object_type, db_object_name, type, role, grantor) -%}
+ALTER DEFAULT PRIVILEGES FOR ROLE {{grantor}} IN {{ db_object_type }} {{ conn|qtIdent(db_object_name) }}
REVOKE ALL ON {{ type }} FROM {{ role }};
{%- endmacro %}
+
+{% macro REMOVE(conn, type, role, privs, with_grant_privs, grantor) -%}
+{% if privs %}
+ALTER DEFAULT PRIVILEGES FOR ROLE {{grantor}} REVOKE {{ privs|join(', ') }} ON {{ type }} FROM {{ role }};
+{% endif %}
+{% if with_grant_privs %}
+ALTER DEFAULT PRIVILEGES FOR ROLE {{grantor}} REVOKE {{ with_grant_privs|join(', ') }} ON {{ type }} FROM {{ role }} WITH GRANT OPTION;
+{% endif %}
+{%- endmacro %}
diff --git a/web/pgadmin/browser/server_groups/servers/utils.py b/web/pgadmin/browser/server_groups/servers/utils.py
index 2b318b444..dcf725d50 100644
--- a/web/pgadmin/browser/server_groups/servers/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/utils.py
@@ -31,6 +31,8 @@ def parse_priv_from_db(db_privileges):
'grantee': db_privileges['grantee'],
'privileges': []
}
+ if 'acltype' in db_privileges:
+ acl['acltype'] = db_privileges['acltype']
privileges = []
for idx, priv in enumerate(db_privileges['privileges']):
@@ -133,12 +135,16 @@ def parse_priv_to_db(str_privileges, allowed_acls=[]):
if 'old_grantee' in priv and priv['old_grantee'] != 'PUBLIC' \
else grantee
+ acltype = priv['acltype'] if 'acltype' in priv else 'defaultacls'
+
# Appending and returning all ACL
privileges.append({
+ 'grantor': priv['grantor'],
'grantee': grantee,
'with_grant': priv_with_grant,
'without_grant': priv_without_grant,
- 'old_grantee': old_grantee
+ 'old_grantee': old_grantee,
+ 'acltype': acltype
})
return privileges
diff --git a/web/regression/python_test_utils/test_utils.py b/web/regression/python_test_utils/test_utils.py
index 2b6bf82fc..d7dfc5f1b 100644
--- a/web/regression/python_test_utils/test_utils.py
+++ b/web/regression/python_test_utils/test_utils.py
@@ -633,9 +633,11 @@ def add_db_to_parent_node_dict(srv_id, db_id, test_db_name):
})
-def add_schema_to_parent_node_dict(srv_id, db_id, schema_id, schema_name):
+def add_schema_to_parent_node_dict(srv_id, db_name, db_id, schema_id,
+ schema_name):
""" This function stores the schema details into parent dict """
server_information = {"server_id": srv_id, "db_id": db_id,
+ "test_db_name": db_name,
"schema_id": schema_id,
"schema_name": schema_name}
regression.parent_node_dict["schema"].append(server_information)
@@ -653,7 +655,8 @@ def create_parent_server_node(server_info):
srv_id = create_server(server_info)
# Create database
test_db_name = "test_db_%s" % str(uuid.uuid4())[1:6]
- db_id = create_database(server_info, test_db_name)
+ encodings = ['UTF-8', 'C', 'C']
+ db_id = create_database(server_info, test_db_name, encodings)
add_db_to_parent_node_dict(srv_id, db_id, test_db_name)
# Create schema
schema_name = "test_schema_%s" % str(uuid.uuid4())[1:6]
@@ -668,7 +671,7 @@ def create_parent_server_node(server_info):
schema = regression.schema_utils.create_schema(connection, schema_name)
return add_schema_to_parent_node_dict(
- srv_id, db_id, schema[0], schema[1]
+ srv_id, test_db_name, db_id, schema[0], schema[1]
)
diff --git a/web/regression/re_sql/tests/test_resql.py b/web/regression/re_sql/tests/test_resql.py
index 5a1d9684f..cc3dd1474 100644
--- a/web/regression/re_sql/tests/test_resql.py
+++ b/web/regression/re_sql/tests/test_resql.py
@@ -104,7 +104,8 @@ class ReverseEngineeredSQLTestCases(BaseTestGenerator):
'timestamptz_1': '<TIMESTAMPTZ_1>',
'password': '<PASSWORD>',
'pga_job_id': '<PGA_JOB_ID>',
- 'timestamptz_2': '<TIMESTAMPTZ_2>'}
+ 'timestamptz_2': '<TIMESTAMPTZ_2>',
+ 'db_name': '<TEST_DB_NAME>'}
resql_module_list = create_resql_module_list(
BaseTestGenerator.re_sql_module_list,
@@ -752,6 +753,10 @@ class ReverseEngineeredSQLTestCases(BaseTestGenerator):
sql = sql.replace(self.JSON_PLACEHOLDERS['pga_job_id'],
str(object_id))
+ if 'TEST_DB_NAME' in scenario:
+ sql = sql.replace(self.JSON_PLACEHOLDERS['db_name'],
+ self.server_information['test_db_name'])
+
return sql
def replace_placeholder_with_id(self, value):
view thread (7+ 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] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC:
In-Reply-To: <CAMa=N=OS_jw+PWo21PHw2xhGTQW=sG2XCgr_Zqq25gE4V7mj_A@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