May I humbly suggest a TODO item (or two)?
On the general list a while back someone wanted to implement
a view where the records visible to the client were
dependent upon an application defined user id. I, too, am in
a similar circumstance. However, one solution would be to do
something like this:
SELECT authenticate(<userid>, <password>);
where <userid> and <password> are submitted by the client
application as input from the user. The authenticate()
routine would be a 'C' language routine which would validate
the userid and password by checking some sort of password
table (the database equivalent of shadowed passwords) and
then either set or clear an environmental variable, say,
CLIENTID and returning either 0 or 1. Then, one could have a
view such as:
CREATE VIEW 'mypayroll' AS SELECT * FROM payroll WHERE
employeeid = clientid();
and the clientid() function simply returns the value of the
environmental variable CLIENTID. Of course this would only
be useful for single-connection client applications. The
client application would initially connect to PostgreSQL
using a PostgreSQL userid/password which would only have
SELECT privileges on the 'mypayroll' table.
At any rate. the whole point of this deal is that anyone
wanting to write a 'C' function which needs to access tuples
has to use the SPI interface. And at the moment, that means
they need the source tree to the backend. I wrote Lamar a
note a while back regarding this and he said to check the
dependencies on spi.h. Well, a 'make depend' for spi.c
yielded 86 headers, so if that's any indication, it would
mean a substantial number of additional headers would be
required for properly allowing users with binary
distributions to write SPI code. Quite frankly, I don't
know why spi.h is even being shipped with various packages
at the moment. As a result, no one can use the contrib code
without the backend source, nor can one write 'C' functions
that access tables. So I was wondering if this is something
that might ever be addressed? Of course, the whole issue
disappears if there is ever a PostgreSQL equivalent of a
'setuid' attribute and grant/revoke privileges for
functions....
Just some thoughts...
Mike Mascari