public inbox for [email protected]help / color / mirror / Atom feed
[Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC: 7+ messages / 3 participants [nested] [flat]
* [Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC: @ 2022-02-28 12:33 Yogesh Mahajan <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Yogesh Mahajan @ 2022-02-28 12:33 UTC (permalink / raw) To: pgadmin-hackers Hi Team, Please find a patch which fixes sql for alter default privileges. Thanks, Yogesh Mahajan EnterpriseDB Attachments: [application/octet-stream] RM_4256_v1.patch (43.3K, 3-RM_4256_v1.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/macros/schemas/privilege.macros b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/macros/schemas/privilege.macros index 5211b00de..0dd9ce9dd 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/macros/schemas/privilege.macros +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/macros/schemas/privilege.macros @@ -15,4 +15,4 @@ GRANT {{ with_grant_privs|join(', ') }} ON {{ type }} {{ conn|qtIdent(schema, pa {%- endmacro %} {% macro UNSETALL(conn, type, role, param, schema) -%} REVOKE ALL ON {{ type }} {{ conn|qtIdent(schema, param) }} FROM {{ role }}; -{%- endmacro %} \ No newline at end of file +{%- endmacro %} 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..3f0bf136d 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT SELECT, UPDATE ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT SELECT, UPDATE ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres 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..86a0852c0 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT SELECT, UPDATE ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT SELECT, UPDATE ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres 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..418276045 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT SELECT ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres 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..3541fe1a1 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres REVOKE ALL ON SEQUENCES FROM PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres 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..85ddbf509 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT SELECT ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT UPDATE ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres 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..b2720735e 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres REVOKE ALL ON TABLES FROM PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT SELECT ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres REVOKE ALL ON SEQUENCES FROM PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres 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..10827cff9 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT ALL ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT ALL ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres 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..9cc21ae50 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT ALL ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT ALL ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER postgres 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..f5e3957f9 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT SELECT, UPDATE ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT SELECT, UPDATE ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb 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..c0a1ef417 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT SELECT, UPDATE ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT SELECT, UPDATE ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb 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..255d50c16 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT SELECT ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb 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..b3543a71d 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb REVOKE ALL ON SEQUENCES FROM PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb 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..633de2f45 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT SELECT ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT UPDATE ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb 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..f2d21b226 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb REVOKE ALL ON TABLES FROM PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT SELECT ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb REVOKE ALL ON SEQUENCES FROM PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb 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..3c6b20e81 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT ALL ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT ALL ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb 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..71fbaea2e 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 IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT ALL ON TABLES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT ALL ON SEQUENCES TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT EXECUTE ON FUNCTIONS TO PUBLIC; -ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" +ALTER DEFAULT PRIVILEGES IN SCHEMA "test_schema_$%{}[]()&*^!@""""'`\/#" FOR USER enterprisedb GRANT USAGE ON TYPES TO PUBLIC; 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..63005c681 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,24 @@ 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) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TABLES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }} {% 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) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'SEQUENCES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }} {% 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) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'FUNCTIONS', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }} {% 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) }} +{{ DEFAULT_PRIVILEGE.APPLY(conn, 'TYPES', priv.grantee, priv.without_grant, priv.with_grant, priv.grantor) }} {% endfor %} {% endif %} 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..acb454fdf 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,4 +1,4 @@ -{% macro APPLY(conn, type, role, privs, with_grant_privs) -%} +{% macro APPLY(conn, type, role, privs, with_grant_privs, grantor) -%} {% if privs %} ALTER DEFAULT PRIVILEGES GRANT {{ privs|join(', ') }} ON {{ type }} TO {{ role }}; @@ -13,17 +13,17 @@ ALTER DEFAULT PRIVILEGES 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 IN {{ db_object_type }} {{ conn|qtIdent(db_object_name) }} FOR USER {{grantor}} 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 IN {{ db_object_type }} {{ conn|qtIdent(db_object_name) }} FOR USER {{grantor}} 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 IN {{ db_object_type }} {{ conn|qtIdent(db_object_name) }} FOR USER {{grantor}} REVOKE ALL ON {{ type }} FROM {{ role }}; {%- endmacro %} diff --git a/web/pgadmin/browser/server_groups/servers/utils.py b/web/pgadmin/browser/server_groups/servers/utils.py index 2b318b444..1282e9af5 100644 --- a/web/pgadmin/browser/server_groups/servers/utils.py +++ b/web/pgadmin/browser/server_groups/servers/utils.py @@ -135,6 +135,7 @@ def parse_priv_to_db(str_privileges, allowed_acls=[]): # Appending and returning all ACL privileges.append({ + 'grantor': priv['grantor'], 'grantee': grantee, 'with_grant': priv_with_grant, 'without_grant': priv_without_grant, ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC: @ 2022-02-28 12:53 Akshay Joshi <[email protected]> parent: Yogesh Mahajan <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Akshay Joshi @ 2022-02-28 12:53 UTC (permalink / raw) To: Yogesh Mahajan <[email protected]>; +Cc: pgadmin-hackers 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* ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC: @ 2022-03-01 07:20 Aditya Toshniwal <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Aditya Toshniwal @ 2022-03-01 07:20 UTC (permalink / raw) To: Akshay Joshi <[email protected]>; +Cc: Yogesh Mahajan <[email protected]>; pgadmin-hackers 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" ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC: @ 2022-03-28 11:12 Yogesh Mahajan <[email protected]> parent: Aditya Toshniwal <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Yogesh Mahajan @ 2022-03-28 11:12 UTC (permalink / raw) To: pgadmin-hackers 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): ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC: @ 2022-03-28 12:07 Akshay Joshi <[email protected]> parent: Yogesh Mahajan <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Akshay Joshi @ 2022-03-28 12:07 UTC (permalink / raw) To: Yogesh Mahajan <[email protected]>; +Cc: pgadmin-hackers Thanks, the patch applied On Mon, Mar 28, 2022 at 4:43 PM Yogesh Mahajan < [email protected]> wrote: > 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" >> > -- *Thanks & Regards* *Akshay Joshi* *pgAdmin Hacker | Principal Software Architect* *EDB Postgres <http://edbpostgres.com>* *Mobile: +91 976-788-8246* ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC: @ 2022-03-29 09:22 Yogesh Mahajan <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Yogesh Mahajan @ 2022-03-29 09:22 UTC (permalink / raw) To: pgadmin-hackers Hello, Please find the attached patch which fixes resql test failures due to the above patch. Thanks, Yogesh Mahajan EnterpriseDB On Mon, Mar 28, 2022 at 5:37 PM Akshay Joshi <[email protected]> wrote: > Thanks, the patch applied > > On Mon, Mar 28, 2022 at 4:43 PM Yogesh Mahajan < > [email protected]> wrote: > >> 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" >>> >> > > -- > *Thanks & Regards* > *Akshay Joshi* > *pgAdmin Hacker | Principal Software Architect* > *EDB Postgres <http://edbpostgres.com>* > > *Mobile: +91 976-788-8246* > Attachments: [application/octet-stream] RM_4256_v3.patch (3.8K, 3-RM_4256_v3.patch) download | inline diff: 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 e9f9eac89..2527a5d36 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 @@ -36,7 +36,7 @@ GROUP BY g.rolname, gt.rolname, a.deftype ORDER BY a.deftype ) {% else %} - +SELECT * from ( (SELECT CASE (e.deftype) WHEN 'r' THEN 'deftblacl' @@ -157,5 +157,5 @@ FROM( 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) +ORDER BY e.deftype)) f order by f.acltype DESC {% endif %} 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 index 494432610..3fac293cf 100644 --- 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 @@ -13,9 +13,9 @@ CREATE DATABASE <TEST_DB_NAME> ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON TABLES FROM postgres; +ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON SEQUENCES 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_types.sql b/web/pgadmin/browser/server_groups/servers/databases/tests/pg/default/alter_default_db_privileges_types.sql index 90541625d..f3dbd0523 100644 --- 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 @@ -13,11 +13,11 @@ CREATE DATABASE <TEST_DB_NAME> ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON TABLES FROM postgres; +ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON SEQUENCES 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/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 index 86e1b7e8b..ec12171f3 100644 --- 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 @@ -11,9 +11,9 @@ CREATE DATABASE <TEST_DB_NAME> TABLESPACE = pg_default CONNECTION LIMIT = -1; +ALTER DEFAULT PRIVILEGES FOR ROLE enterprisedb REVOKE ALL ON TABLES FROM enterprisedb; + 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; ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC: @ 2022-03-29 09:28 Akshay Joshi <[email protected]> parent: Yogesh Mahajan <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Akshay Joshi @ 2022-03-29 09:28 UTC (permalink / raw) To: Yogesh Mahajan <[email protected]>; +Cc: pgadmin-hackers Thanks, the patch applied. On Tue, Mar 29, 2022 at 2:52 PM Yogesh Mahajan < [email protected]> wrote: > Hello, > > Please find the attached patch which fixes resql test failures due to the > above patch. > > Thanks, > Yogesh Mahajan > EnterpriseDB > > > On Mon, Mar 28, 2022 at 5:37 PM Akshay Joshi < > [email protected]> wrote: > >> Thanks, the patch applied >> >> On Mon, Mar 28, 2022 at 4:43 PM Yogesh Mahajan < >> [email protected]> wrote: >> >>> 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" >>>> >>> >> >> -- >> *Thanks & Regards* >> *Akshay Joshi* >> *pgAdmin Hacker | Principal Software Architect* >> *EDB Postgres <http://edbpostgres.com>* >> >> *Mobile: +91 976-788-8246* >> > -- *Thanks & Regards* *Akshay Joshi* *pgAdmin Hacker | Principal Software Architect* *EDB Postgres <http://edbpostgres.com>* *Mobile: +91 976-788-8246* ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-03-29 09:28 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-02-28 12:33 [Patch] Bug #4256 - ALTER DEFAULT PRIVILEGES FOR ROLE some_role REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC: Yogesh Mahajan <[email protected]> 2022-02-28 12:53 ` Akshay Joshi <[email protected]> 2022-03-01 07:20 ` Aditya Toshniwal <[email protected]> 2022-03-28 11:12 ` Yogesh Mahajan <[email protected]> 2022-03-28 12:07 ` Akshay Joshi <[email protected]> 2022-03-29 09:22 ` Yogesh Mahajan <[email protected]> 2022-03-29 09:28 ` Akshay Joshi <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox