Thread

Re: [HACKERS] Suggest a pg_privileges table

Peter T Mount <psqlhack@maidast.demon.co.uk> — 1998-01-14T07:07:58Z

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@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk