Обсуждение: getTablePrivileges empty if table owned by group role

Hi,

I came across a situation which revealed that getTablePrivileges is not
returning any grants if a table is owned by a group role, i.e. a role wihout
login attribute. The connection is opened with superuser priviliges.

This code runs with the latest 8.4 driver (702) agains a 8.4.8 database.

    public static void listprivs() throws SQLException{
        ResultSet rs = con.getMetaData().getTablePrivileges(null, "public", "test");
        int i = 0;
        while (rs.next())
        {
           System.out.print("grantee: " + rs.getString("GRANTEE"));
           System.out.println(", privilege: " + rs.getString("PRIVILEGE"));
           i++;
        }
        System.out.println("Total: " + i);
    }

    public static void main(String[] args) throws ClassNotFoundException,
SQLException {

        Class.forName("org.postgresql.Driver");
        con = DriverManager.getConnection("jdbc:postgresql://localhost/test",
        "user", "****");
        con.setAutoCommit(true);
        Statement stmt = con.createStatement();

        stmt.executeUpdate("create table test (a int)");
        stmt.executeUpdate("create role test_group");
        stmt.executeUpdate("alter table test owner to test_group");
        System.out.println("Initial case");
        listprivs();
        stmt.executeUpdate("alter role test_group login");
        System.out.println("login attribute set");
        listprivs();
        stmt.executeUpdate("drop table test");
        stmt.executeUpdate("drop role test_group");
    }
}

Output:

Initial case
Total: 0
Group with login
grantee: test_group, privilege: DELETE
grantee: test_group, privilege: INSERT
grantee: test_group, privilege: REFERENCES
grantee: test_group, privilege: RULE
grantee: test_group, privilege: SELECT
grantee: test_group, privilege: TRIGGER
grantee: test_group, privilege: UPDATE
Total: 7

To my understanding, the result should be the same in both cases. The result is
the same even if there are explicit grants on the table to some other role.

Regards,

Jonas Sundman

Hi all,

after some digging and further reading in the todo list I learned that acl
parsing is known to be uncompatible with versions after 7.3.

I decided to have a look into the code and turned up with a working version of
the acl parsing, which I would like to share.

To summarize whats in there
    - use rolename instead of username (which corrects the problem which my
original message refers to)
    - handle the grantor part of the acl
    - handle the grantable attribute
    - use attacl for column permissions.

The patch is against the development version (build 900). I have tested this
against 8.4.4.

Best regards,

Jonas Sundman

On Tue, 23 Aug 2011, Jonas Sundman wrote:

> after some digging and further reading in the todo list I learned that acl
> parsing is known to be uncompatible with versions after 7.3.
>
> I decided to have a look into the code and turned up with a working
> version of the acl parsing, which I would like to share.

Great.  Can you resend as a context diff (diff -c), the default format
isn't really usable.  Also we need to able to support the older ACL
formats as well.  I haven't read your patch, but my ancient notes say 7.3,
7.4, and 8.0 all have different formats.  I know you aren't likely to have
these old versions handy, but can you test with the attached ACL entries
and ensure they get parsed correctly?

Kris Jurka

On 2011-08-23 20:19, Kris Jurka wrote:
>
>
> On Tue, 23 Aug 2011, Jonas Sundman wrote:
>
>> after some digging and further reading in the todo list I learned that acl
>> parsing is known to be uncompatible with versions after 7.3.
>>
>> I decided to have a look into the code and turned up with a working
>> version of the acl parsing, which I would like to share.
>
> Great.  Can you resend as a context diff (diff -c), the default format
> isn't really usable.  Also we need to able to support the older ACL
> formats as well.  I haven't read your patch, but my ancient notes say 7.3,
> 7.4, and 8.0 all have different formats.  I know you aren't likely to have
> these old versions handy, but can you test with the attached ACL entries
> and ensure they get parsed correctly?

Thank you for your response, I checked the acl:s, and they all parse correctly.
The interetation of the acl which consists of just one equals sign is a bit
unclear to me, for the moment it gives no grants at all.

I tried to follow the style of the original code but it can certainly be much
more enchanced.

Please find a context diff attached (with some debugging calls in main).

Jonas Sundman