All,

I'm trying to implement row level security using PostgreSQL.  I envision
having column on each and every table in the database that will contain a
discriminating value that determines if a given user is able to see that row
or not.  Users do not directly access the database; they get at it only
through client applications.

Is there a way to initially configure PostgreSQL such that each and every
time a 'create table foo....' command is executed that, something else would
happen instead?

For example,

CREATE TABLE weather (
    city        varchar(80),
    lo        int,
    hi        int,
    prcp    real,
    date    date
);

would result in the following being executed automatically:

CREATE TABLE t_weather (
    city        varchar(80),
    lo        int,
    hi        int,
    prcp    real,
    date    date,
    hidden_column    int
);

CREATE VIEW weather AS
    SELECT city, lo, hi, prcp, date
    FROM t_weather;


How the view gets populated is another discussion entirely.

But is there a way to set things up initially such that each 'create table'
command will exhibit the behavior described above?

Thanks.

--

Bruce





Fri, 25 Oct 2002 13:50:45 -0400
"Bruce David" <brucedavid@bigfoot.com>
Trigger on 'create table' ?

Sorry, I can't think of any to have a view created automatically for
every table creation.  Interesting idea, though.  I wonder if you could
pass the CREATE string into a function that does the proper creation.

---------------------------------------------------------------------------

Bruce David wrote:
> All,
> 
> I'm trying to implement row level security using PostgreSQL.  I envision
> having column on each and every table in the database that will contain a
> discriminating value that determines if a given user is able to see that row
> or not.  Users do not directly access the database; they get at it only
> through client applications.
> 
> Is there a way to initially configure PostgreSQL such that each and every
> time a 'create table foo....' command is executed that, something else would
> happen instead?
> 
> For example,
> 
> CREATE TABLE weather (
>     city        varchar(80),
>     lo        int,
>     hi        int,
>     prcp    real,
>     date    date
> );
> 
> would result in the following being executed automatically:
> 
> CREATE TABLE t_weather (
>     city        varchar(80),
>     lo        int,
>     hi        int,
>     prcp    real,
>     date    date,
>     hidden_column    int
> );
> 
> CREATE VIEW weather AS
>     SELECT city, lo, hi, prcp, date
>     FROM t_weather;
> 
> 
> How the view gets populated is another discussion entirely.
> 
> But is there a way to set things up initially such that each 'create table'
> command will exhibit the behavior described above?
> 
> Thanks.
> 
> --
> 
> Bruce
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 28 Oct 2002 21:23:56 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Trigger on 'create table' ?
We have a similar requirement for PostGIS.
In order to meet the OpenGIS specification, we have to maintain a 
GEOMETRY_COLUMNS table which includes an entry for every column in the 
db which holds a spatial column. It would be ideal if we could have 
triggers run on CREATE TABLE, DROP TABLE, and ALTER TABLE to watch for 
the comings and goings of spatial columns and update GEOMETRY_COLUMNS 
appropriately.
Unfortunately, triggers seem to not be allowed on system tables, or we 
would just watch pg_class for new geometry columns and update the 
OpenGIS table as necessary. :/


Bruce Momjian wrote:
> Sorry, I can't think of any to have a view created automatically for
> every table creation.  Interesting idea, though.  I wonder if you could
> pass the CREATE string into a function that does the proper creation.
> 
> ---------------------------------------------------------------------------
> 
> Bruce David wrote:
> 
>>All,
>>
>>I'm trying to implement row level security using PostgreSQL.  I envision
>>having column on each and every table in the database that will contain a
>>discriminating value that determines if a given user is able to see that row
>>or not.  Users do not directly access the database; they get at it only
>>through client applications.
>>
>>Is there a way to initially configure PostgreSQL such that each and every
>>time a 'create table foo....' command is executed that, something else would
>>happen instead?
>>
>>For example,
>>
>>CREATE TABLE weather (
>>    city        varchar(80),
>>    lo        int,
>>    hi        int,
>>    prcp    real,
>>    date    date
>>);
>>
>>would result in the following being executed automatically:
>>
>>CREATE TABLE t_weather (
>>    city        varchar(80),
>>    lo        int,
>>    hi        int,
>>    prcp    real,
>>    date    date,
>>    hidden_column    int
>>);
>>
>>CREATE VIEW weather AS
>>    SELECT city, lo, hi, prcp, date
>>    FROM t_weather;
>>
>>
>>How the view gets populated is another discussion entirely.
>>
>>But is there a way to set things up initially such that each 'create table'
>>command will exhibit the behavior described above?
>>
>>Thanks.
>>
>>--
>>
>>Bruce
>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
> 
> 



Mon, 28 Oct 2002 18:33:21 -0800
Paul Ramsey <pramsey@refractions.net>
Re: Trigger on 'create table' ?
Paul Ramsey writes:

> In order to meet the OpenGIS specification, we have to maintain a
> GEOMETRY_COLUMNS table which includes an entry for every column in the
> db which holds a spatial column.

Why not make a view?

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 29 Oct 2002 23:18:27 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Trigger on 'create table' ?
Paul Ramsey <pramsey@refractions.net> writes:
> We have a similar requirement for PostGIS.
> In order to meet the OpenGIS specification, we have to maintain a 
> GEOMETRY_COLUMNS table which includes an entry for every column in the 
> db which holds a spatial column. It would be ideal if we could have 
> triggers run on CREATE TABLE, DROP TABLE, and ALTER TABLE to watch for 
> the comings and goings of spatial columns and update GEOMETRY_COLUMNS 
> appropriately.

Couldn't you define GEOMETRY_COLUMNS as a view?  Seems a lot more
efficient than firing random triggers on every table operation.

			regards, tom lane

Tue, 29 Oct 2002 17:33:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Trigger on 'create table' ? 
So close... It is a testament to my blockheadedness that I had not 
considered that (Dave probably did). Here is the standards-mandated 
definition of the table:

CREATE TABLE GEOMETRY_COLUMNS (
   F_TABLE_CATALOG VARCHAR(256) NOT NULL,
   F_TABLE_SCHEMA VARCHAR(256) NOT NULL,
   F_TABLE_NAME VARCHAR(256) NOT NULL,
   F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL,
   COORD_DIMENSION INTEGER,
   SRID INTEGER REFERENCES SPATIAL_REF_SYS,
   CONSTRAINT GC_PK PRIMARY KEY
(F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN)
)

Everything except for COORD_DIMENSION and SRID could be extracted from 
the pg_class table as a view, which would be nice. The COORD_DIMENSION 
is just the dimensionality of the geometries, and the SRID is the 
spatial reference system identifier, which is a key into another 
mandated OpenGIS table, 'SPATIAL_REF_SYS'.

Now, if we changed the geometry type so that when one defined a geometry 
column, one had to include info about what SRID and what dimension it 
was, (ala varchar(243)) maybe the whole schmeer could reside in pg_class 
and geometry_columns would be a trivial view?

Crazy? Loony?

P.

Tom Lane wrote:
> Paul Ramsey <pramsey@refractions.net> writes:
> 
>>We have a similar requirement for PostGIS.
>>In order to meet the OpenGIS specification, we have to maintain a 
>>GEOMETRY_COLUMNS table which includes an entry for every column in the 
>>db which holds a spatial column. It would be ideal if we could have 
>>triggers run on CREATE TABLE, DROP TABLE, and ALTER TABLE to watch for 
>>the comings and goings of spatial columns and update GEOMETRY_COLUMNS 
>>appropriately.
> 
> 
> Couldn't you define GEOMETRY_COLUMNS as a view?  Seems a lot more
> efficient than firing random triggers on every table operation.
> 
> 			regards, tom lane
> 


-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey@refractions.net
      | Phone: (250) 885-0632
      \_


Tue, 29 Oct 2002 16:56:31 -0800
Paul Ramsey <pramsey@refractions.net>
Re: Trigger on 'create table' ?
Paul Ramsey <pramsey@refractions.net> writes:
> Now, if we changed the geometry type so that when one defined a geometry 
> column, one had to include info about what SRID and what dimension it 
> was, (ala varchar(243)) maybe the whole schmeer could reside in pg_class 
> and geometry_columns would be a trivial view?

atttypmod would be a natural place for that stuff, if you can fit it
into 31 bits.  The bigger problem is how are you going to get
type-specific syntax through the Postgres parser?  Currently, all the
types that use typmod have to have special-purpose syntax to set it.
I'm not eager to see extension datatypes trying to do the same thing...

			regards, tom lane

Wed, 30 Oct 2002 12:42:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Trigger on 'create table' ? 