Thread: Suggest a pg_privileges table

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

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