Thread: SPI header dependencies
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
Mike Mascari <mascarm@mascari.com> writes:
> 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.
This seems like a completely redundant mechanism to me.
What is wrong with using the *existing* user authentication
mechanisms, and then using getpgusername() or CURRENT_USER
in your queries?
> 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 can't really see anyone writing SPI functions without access to
a source tree; there's too much stuff that's most readily learned
by looking at the code. But I think you are right that the installed
include tree is probably insufficient to *compile* the average SPI
function, and that's not good. We haven't been paying much attention
to the question of which headers need to be installed. There are
probably some installed that needn't be anymore, as well as vice versa.
Proposed TODO:
* Re-examine list of header files that get installed, add/delete as needed
regards, tom lane
Tom Lane wrote: >Proposed TODO: >* Re-examine list of header files that get installed, add/delete as needed I attach the list of extra include files that I needed to include in Debian's postgresql-dev package access/funcindex.h access/heapam.h access/htup.h access/ibit.h access/itup.h access/relscan.h access/sdir.h access/skey.h access/strat.h access/transam.h access/tupdesc.h access/tupmacs.h access/xact.h catalog/catname.h catalog/pg_am.h catalog/pg_attribute.h catalog/pg_class.h catalog/pg_index.h catalog/pg_language.h catalog/pg_proc.h catalog/pg_type.h executor/execdefs.h executor/execdesc.h executor/executor.h executor/hashjoin.h executor/tuptable.h lib/fstack.h nodes/execnodes.h nodes/memnodes.h nodes/nodes.h nodes/params.h nodes/parsenodes.h nodes/pg_list.h nodes/plannodes.h nodes/primnodes.h nodes/relation.h parser/parse_node.h parser/parse_type.h rewrite/prs2lock.h storage/block.h storage/buf.h storage/buf_internals.h storage/bufmgr.h storage/bufpage.h storage/fd.h storage/ipc.h storage/item.h storage/itemid.h storage/itemptr.h storage/lmgr.h storage/lock.h storage/off.h storage/page.h storage/shmem.h storage/sinvaladt.h storage/spin.h tcop/dest.h tcop/pquery.h tcop/tcopprot.h tcop/utility.h utils/array.h utils/builtins.h utils/datetime.h utils/datum.h utils/dt.h utils/fcache.h utils/hsearch.h utils/inet.h utils/int8.h utils/mcxt.h utils/memutils.h utils/nabstime.h utils/numeric.h utils/portal.h utils/rel.h utils/syscache.h utils/tqual.h -- Vote against SPAM: http://www.politik-digital.de/spam/ ======================================== Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "In the beginning was the Word, and the Word was with God, and the Word was God. The same was in the beginning with God. All things were made by him; and withouthim was not any thing made that was made." John 1:1-3
Tom Lane wrote: > > Mike Mascari <mascarm@mascari.com> writes: > > SELECT authenticate(<userid>, <password>); > > where <userid> and <password> are submitted by the client > > application as input from the user. > > This seems like a completely redundant mechanism to me. > What is wrong with using the *existing* user authentication > mechanisms, and then using getpgusername() or CURRENT_USER > in your queries? I agree. I imagine the poster's development probably took the same course as mine - first he was using PostgreSQL as a backend to a web server, like Apache. He then probably using Basic authentication with something like mod_auth_pgsql. In order to authenticate web pages using something like mod_auth_pgsql, the httpd user (www, nobody, etc.) would connect to the database and check the user name and encrypted password submitted against a user-specified table. Since the only application that is going to be connecting to PostgreSQL is the webserver, one is tempted (including me) to create and manage fake webuser id's and passwords, and only have a single real PostgreSQL user id connect to the database...particularly when the webuser list numbers in the thousands. That's why I attributed the LRU file descriptor exhaustion problem I reported about a month ago to kernel problems instead of the password authentication leak - 90% of our users use the web-server. The httpd process runs as a user id which does not have a shell account, and can only connect to the database on localhost. This whole scheme looks good at first, until you find yourself developing Windows-based clients...You either have to shoe-horn in a hack (like the above) or bite the bullet and migrate your core authentication mechanism to PostgreSQL's. > Proposed TODO: > * Re-examine list of header files that get installed, add/delete as needed > > regards, tom lane Sounds great. Although hopefully not needed in the next release :-) , the most annoying thing in the past was the inability to build a refint.so from the various binary distributions... Mike Mascari
> Proposed TODO: > * Re-examine list of header files that get installed, add/delete as needed Sounds great. Although hopefully not needed in the next release :-) , the most annoying thing in the past was the inabilityto build a refint.so from the various binary distributions... Absolutely needed! There is more to SPI than refint. The foreign keys cannot remove the need for that interface to be complete as installed. Same problem happens when trying to include trigger.h, so those dependencies need looking at too. Cheers, Brook
Oliver Elphick wrote: > > Tom Lane wrote: > >Proposed TODO: > >* Re-examine list of header files that get installed, add/delete as needed > > I attach the list of extra include files that I needed to include in Debian's > postgresql-dev package [snip] I owe you again, Oliver. This list gives the postgres-dev package everything needed to do SPI development?? Good thing I haven't done my planned 6.5.3-3 release yet. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Oliver Elphick wrote: > > Tom Lane wrote: > >Proposed TODO: > >* Re-examine list of header files that get installed, add/delete as needed > > I attach the list of extra include files that I needed to include in Debian's > postgresql-dev package [snip] I owe you again, Oliver. This list gives the postgres-dev package everything needed to do SPI development?? Good thing I haven't done my planned 6.5.3-3 release yet. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11