Thread

1. Suggest a pg_privileges table

   todd brandys <brandys@eng3.hep.uiuc.edu> — 1998-01-14T05:40:57Z

   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).
   
   I don't know that I can volunteer to perform all the changes this would involve,
   but I would be very willing to help, as this would greatly improve the security
   of PostgreSQL.
   
   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.
   
   Todd A. Brandys
   brandys@eng3.hep.uiuc.edu
   
   

2. 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