The problem at hand is how to return the value of a select with an "invisible" condition on the selection. The problem in the thread involved externally resolved permissions but the example we will discuss here is a less volatile condition--we will close the ability to select for a short time window. The condition is irrelevant to the technique, however, it is important to note that the permission problem can be solved in other ways using SQL permissions and views which show partial data.
In this example we'll use the following table and OK function. Notice that we want to abort the query by raising an exception if the qualification is not valid.
CREATE TABLE gb_54 ( colone INTEGER, coltwo INTEGER, colthree INTEGER); INSERT INTO gb_54 VALUES (1,2,3); INSERT INTO gb_54 VALUES (4,5,6); INSERT INTO gb_54 VALUES (7,8,9); CREATE OR REPLACE FUNCTION selOK() RETURNS BOOLEAN AS ' BEGIN IF current_time < ''18:00''::time or current_time >= ''18:30''::time THEN return current_time < ''18:00''::time or current_time >= ''18:30''::time; ELSE RAISE EXCEPTION ''Select Window is Closed from 6:00pm to 6:30pm Daily''; END IF; RETURN; END; ' language 'plpgsql';
The first though is to create a RULE on the table with a condition in the where clause:
#= CREATE OR REPLACE RULE COND_SEL #- AS ON SELECT TO gb_54 where selOK(); #- DO INSTEAD #- select * from gb_54 where selOK(); ERROR: view rule for "gb_54" must be named "_RETURN"Ooops. This is an implementation issue. Views are created by creating empty tables based on the target table and RULES named "_RETURN" on selections from tables.
#= CREATE TABLE myview (same column list as mytab); #= CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD #- SELECT * FROM mytab;Because we are using a SELECT RULE, essentially we are creating a view. So we must use the RULE name "_RETURN".
=# CREATE OR REPLACE RULE "_RETURN" =# AS ON SELECT TO gb_54 =# DO INSTEAD =# select * from gb_54 where selOK(); ERROR: could not convert table "gb_54" to a view because it is not emptyThe selection table has to be empty in order to be a view. Perhaps a RULE was not a good idea.
Let us try another approach: a (real) view.
=# CREATE VIEW gb_54v AS -# SELECT * FROM gb_54 WHERE selOK(); select * from gb_54v; colone | coltwo | colthree --------+--------+---------- 1 | 2 | 3 4 | 5 | 6 7 | 8 | 9 (3 rows) -- some time later.... =# select * from gb_54v; ERROR: Select Window is Closed from 6:00pm to 6:30pm Daily
Sometimes we pick the hard way to do things when an easy way is in front of our faces. So to atone for choosing the hard way as a first path, there is another tact that can always be considered when a view might also work. This is particularly helpful when the logic is too complex for a view or the dependencies are not straightforward. This other tact is to use an SRF or Set Returning Function.
An SRF to perform the same function as the view above is included below. You will have to add your own complexity. This function still relies on the selOK() which will raise an exception if the qualification criteria is not met.
CREATE TYPE gb54t AS (tone integer, ttwo integer, tthree integer); CREATE OR REPLACE FUNCTION gb54f() RETURNS setof gb54t AS ' DECLARE gb54rec gb54t%ROWTYPE; BEGIN IF selOK() THEN FOR gb54rec IN SELECT * FROM gb_54 LOOP RETURN NEXT gb54rec; END LOOP; RETURN; ELSE RETURN; END IF; END; ' language 'plpgsql';
A computer crashed as they do from time to time, unfortunately. However, the disk seems to be OK and it had an unknown older version of postgreSQL on it.
To salvage the data, first determine exactly what version the database was running by looking in the PG_VERSION file in the data ($PGDATA) directory.
Then you must install either that version, or preferably the latest minor version in that series. That means that if the database was a 7.1 database, you probably want to install 7.1.3. To see what versions there are available, go to the PostgreSQL Download Site and select your mirror and then your flavor (binary or source). You will see all of the older versions listed. Download and install the appropriate one.
Once you have an installation compatible with your data, point your installation to the data directory to be salvaged. (Don't initdb in that location, though.)
Now you have a database server pointing to your data and you can do what you want to do to use and/or salvage the data. An upgrade to the latest 7.4 version is recommended, of course.
It is tricky to create a valid user and take away all of their privileges. By "all" we mean that they should not be able to create and select from a table. This requires several steps. This first shell command creates the user "newuser" without privileges to create databases or more users. This works.
$ createuser newuser Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n CREATE USERThe following step was meant to revoke all of newuser's privileges on database a. But it doesn't work. "newuser" was still able to create tables in database a
$ psql chris -U postgres chris=# revoke all on database a from newuser; REVOKE chris=# revoke create on database a from newuser; REVOKE chris=# \q
Revoking create on the databases controls the creation of schemas. But newuser did not try to create a schema, they tried to create a table in the public schema (and succeeded).
This doesn't work either for more mysterious reasons.
chris=# REVOKE ALL ON SCHEMA public FROM newuser; REVOKEThe permissions on schema public were granted to PUBLIC, not to newuser, and so the REVOKE doesn't do anything. Revoke privileges on the public schema from "newuser" you must revoke the privileges on public from PUBLIC and then grant them back to those users who you want to have the privileges.
This is not necessarily obvious. REVOKE works on those users explicitly GRANTed privileges. It is the default that PUBLIC is explicitly granted all on schema public. The special case of user PUBLIC is all users. To change this scheme you must be specific in the revocation and granting to individual users.
An old paper written in 1998 by Whitemarsh Information Systems has a "timely" announcement the "Great News, The Relational Model Is Dead!" The topic of the paper is the SQL99 standard, also known as SQL3. This paper solicited the pointed remarks that follow:
Unlikely; if they are of the persuasion that relational DBs are uninteresting, they'd hardly be spending time working on bigger and better(?) standards for relational DBs.Bruce Momjian:
Maybe it means there isn't anything interesting to study about relational databases. From an academic perspective, the fact it meets people's needs is beyond the point. :-)Christopher Browne:
The fundamental problem there is that it is difficult for academics to come up with original research surrounding relational databases. This may make them a poor area of study for academics, but implies nothing about their usefulness in practice.To which I add, PostgreSQL is an Object Relational Database. Most databases are moving slowly toward the moving SQL3 standards. Illustra was and now PostgreSQL is the closest to that standard. This (movable) standard describes a relational database model with enhancements for a little more object oriented features. The basis is still a solid relational database model.
I read today with great relief about the guilty plea from Phillip White, former CEO of Informix. He has pleaded guilty for securities fraud in 1997 for Informix.
Informix took the lead as having one of the first corporate book-cooking scandals in these past years. The behavior is abhorrent. The fall out affected both companies' and individuals' net worth significantly. And more importantly, I believe, it began the decay of faith in the high tech industry in the United States.
Our open source community does not necessarily prevent this sort of disaster in the future. However, the mutual altruism and good will that abounds in our community (even with assorted trolls, cliques and flamewars) is inspiring. It is inspiring to those of us who love our work, believe in our products and revel in the elegance of innovative and useful creation.
As PostgreSQL spreads more and more into the mainstream let us not forget, however, our own worth and the higher values we maintain.
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009