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
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, Pennsylvania19073
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 >> > >
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
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
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_NAMEVARCHAR(256) NOT NULL, F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL, COORD_DIMENSION INTEGER, SRID INTEGER REFERENCESSPATIAL_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 \_
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