Thread: Trigger on 'create table' ?

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

Perhaps you can define a base class (table) and let others inherit from it.

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 4: Don't 'kill -9' the postmaster
>
>