Skip site navigation (1) Skip section navigation (2)

PATCH: Ticket#99: Support for Default Privileges

From: Ashesh Vashi <ashesh(dot)vashi(at)enterprisedb(dot)com>
To: Guillaume Lelarge <guillaume(at)lelarge(dot)info>, Dave Page <dave(dot)page(at)enterprisedb(dot)com>
Cc: pgadmin-hackers <pgadmin-hackers(at)postgresql(dot)org>
Subject: PATCH: Ticket#99: Support for Default Privileges
Date: 2010-04-05 10:11:37
Message-ID: z2i8cf965d31004050311xb49788ecmb1e5d18836ba3eaf@mail.gmail.com (view raw or flat)
Thread:
Lists: pgadmin-hackers
Hi Guillaume/Dave,

PFA: patch for the support for Default Privileges.
http://developer.postgresql.org/pgdocs/postgres/sql-alterdefaultprivileges.html

- Introduced a new property dialog - dlgDefaultSecurityProperty (inherited
from dlgSecurityProperty)
- Introduced two new controls
  1. ctlDefaultPrivilegesPanel
  -> A common panel for the default privileges
  -> We will have need three panels each for FUNCTIONS, SEQUENCES, TABLES.
  -> Each privilege type has different privileges. This will be taken care
by this panel.

  2. ctlDefaultSecurityPanel
  -> Keeps common data shared between each ctlDefaultPrivilegesPanel.
  -> Communication between privileges panels and dialog will be only done
via this class.
- dlgDatabase and dlgSchema both are inherited from this new dialog.
- dlgDatabase have all the default privileges on all the schemas.
- dlgSchema have the default privileges for the particular schema.

I had to hack the dlgDatabase::OnOK function and need to move the definition
of replClientData in the include/dlg/dlgProperty from dlg/dlgProperty.
Because, the connection from the database is getting disconnected, and the
connection for the server is getting used, in the dlgProperty::apply
function for the dlgDatabase. Hence, any sql running in dlgDatabase dialog
will not run against the actual database. :-(

And for the same reason, I had to disable the default privileges page on
creation of the database. :-(

The current implementation supports the following statement:


ALTER DEFAULT PRIVILEGES
    [ IN SCHEMA *schema_name* [, ...] ]
    *abbreviated_grant_or_revoke*
where *abbreviated_grant_or_revoke* is one of:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON TABLES
    TO { [ GROUP ] *role_name* | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    TO { [ GROUP ] *role_name* | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTIONS
    TO { [ GROUP ] *role_name* | PUBLIC } [, ...] [ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON TABLES
    FROM { [ GROUP ] *role_name* | PUBLIC } [, ...]

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    FROM { [ GROUP ] *role_name* | PUBLIC } [, ...]

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTIONS
    FROM { [ GROUP ] *role_name* | PUBLIC } [, ...]

The current implementation does not have the following two statements from
the actual statement: :-(
- [ FOR { ROLE | USER } *target_role* [, ...] ] -- line#2 in actual
statement
- [ CASCADE | RESTRICT ] -- from the REVOKE statements

I couldn't find a way to support these two statements.
Second one is still doable: we can ask for "RESTRICT | CASCADE ?" on revoke
statements.
But, how to represent it in UI?

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company<http://www.enterprisedb.com>

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company<http://www.enterprisedb.com>

Attachment: Ticket99_DefaultPrivileges_v1_5.patch
Description: application/octet-stream (53.7 KB)

Responses

pgadmin-hackers by date

Next:From: Guillaume LelargeDate: 2010-04-05 10:29:36
Subject: Re: PATCH: Ticket#99: Support for Default Privileges
Previous:From: Dave PageDate: 2010-04-05 09:21:46
Subject: Re: Bug in status window

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group