Thread: "external" variables and triggers

Hi there,

I have a problem that I'm not even 100% certain of how to word:

I'm writing a little postgres-app with a web-frontend. I was
going to map users to actual postgres users, but someone
on irc.freenode.net#postgresql was adamant that that was
bad practice and posed a security risk.

So I changed my plan and put user-IDs and md5 password-
hashes into a table of their own.

My problem now is that I'm using a separate table to keep
track of changes to the status of another one, trying to keep
the name, the date and a field called status.  My problem is
that I can't figure out how to pass the user name (that has
been authenticated via a table and some php) to my trigger.

Can this be achieved at all?

If it can't, I'd rather re-create the database again and use
native postgres users (unless of course it REALLY is a
horrible security risk) than implement a whole lot of logic
in a language I'm not that familiar with.


Thanks for your patience reading this :)


Cheers,
Andrej

Andrej Ricnik-Bay <andrej.groups@gmail.com> writes:
> I'm writing a little postgres-app with a web-frontend. I was
> going to map users to actual postgres users, but someone
> on irc.freenode.net#postgresql was adamant that that was
> bad practice and posed a security risk.

Who exactly?  I think you got bad advice.  It seems pretty silly
to me to invent your own permissions-checking mechanism if you can
use the one that's built into PG.

Of course, if the permissions mechanism doesn't match up with the
rules you want to enforce, then it won't help you.  But it sounded
like you didn't have that problem.

One possible issue is that you probably won't be able to use connection
pooling effectively; if this is going to be a big site then that might
be reason enough not to do it that way.

BTW, you will probably want to take care that the web users can't
actually reach the database server, else they could log in and issue
random SQL commands --- at the very least causing denial-of-service
issues by chewing up resources.  Set it up so those userIDs can only
connect from the machine that's running the webserver.  But I see
nothing wrong with the basic idea of using Postgres user identities as
proxies for real outer-world users.

            regards, tom lane