Re: "external" variables and triggers

Andrej Ricnik-Bay <andrej(dot)groups(at)gmail(dot)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