Re: [HACKERS] Suggest a pg_privileges table

On Tue, 13 Jan 1998, todd brandys wrote:

>
> I would like to suggest the following augmentation to the PostgreSQL DBMS.
> This augmentation is to add a pg_privileges table for each database instance.
> Such a table should be responsible for maintaining the SELECT, UPDATE, INSERT,
> and DELETE permissions on all database objects. Furthermore, it should maintain
> other privileges such as the CREATE DATABASE, CREATE USER, DESTROY USER,
> CREATE TABLE, and the list goes on. One other benefit this would bring would be
> to allow the setting of privileges on table columns. This would alleviate
> the question of creating a separte relation for holding passwords rather than
> keeping this info in pg_user (Simply make the password field non-selectable by
> public).

This could be useful for implementing the getColumnPrivileges() and
getTablePrivileges() methods in the JDBC driver.

> If anyone has any comments or concerns about such a project, let me know. Suuch a
> system should be crafted with care. I would like to reach a consensus among the
> hacker community before I begin to make any mods to bring this about.
>
> I see the changes taking place in the following order:
>
> 1) Code the creation of pg_privileges.
> 2) Make sure the initial permissions of database instance object are in the
> pg_privileges relation upon database creation.
> 3) Rewrite the GRANT and REVOKE statements to update pg_privileges, and (this
> must be done at the same time) supplant the old privileges system. This
> would give us table privileges as they are now.
> 4-Infinity) Begin adding new privileges such as CREATE USER, CREATE DATABASE,
> CREATE TABLE, DESTROY TABLE, etc to the system.
>
> This is a very coarse view of how to accomplish this task. Also, I left out
> column privileges. This should probably be listed at (3.5) above.
>
> Let me know what you think (If you send a reply to the pgsql-hackers email
> account, please be certain to cc me also). I will pull all the comments
> together and start to create a requirements document for pg_privileges.

Hereis whats needed for JDBC:

Each privilige description has the following columns:

1. TABLE_CAT String => table catalog (may be null)
2. TABLE_SCHEM String => table schema (may be null)
3. TABLE_NAME String => table name
4. COLUMN_NAME String => column name
5. GRANTOR => grantor of access (may be null)
6. GRANTEE String => grantee of access
7. PRIVILEGE String => name of access (SELECT, INSERT, UPDATE,
REFRENCES, ...)
8. IS_GRANTABLE String => "YES" if grantee is permitted to grant
to others; "NO" if not; null if unknown

Now, the first two we return null for, and only getColumnPrivileges()
returns COLUMN_NAME

--
Peter T Mount petermount(at)earthling(dot)net or pmount(at)maidast(dot)demon(dot)co(dot)uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter(at)maidstone(dot)gov(dot)uk