Everytime I deploy a new stored procedure, it bothers me I have to update the middle-layer to allow my front-end code to use the function.
My middle-layer between the front-end code and the database simply translates JSON-RPC requests
to SELECT queries calling stored procedures returning JSON back to the front-end.
This is almost perfect.
But, I still have to update a file where I keep all queries,
queries.xml, every time I add a new function.
params="Username, Password, Host, TransactionType, ClearingHouse, BankCode, Amount, Currency"
SELECT * FROM Get_Bank_Transaction_Fee(?, ?, ?, ?, ?, ?, ?, ?)
This is a quite nice way to separate the SQL code from the programming code, but since the SQL query consists of
nothing more than a SELECT statement, the name of the stored procedure and the input arguments,
it might as well be automatically generated based on the JSON-RPC request.
I wanted to do
CREATE FUNCTION and nothing more before the front-end could start using the new function!
PostgreSQL 9.0 introduced calling functions using named parameters, instead of relying on the position of the arguments.
SELECT myfunc(foo := 'Hello', bar := 'World!');
-- is the same thing as,
SELECT myfunc(bar := 'World!', foo := 'Hello');
-- before 9.0 you had to do,
-- which means you must not get the positions wrong
This nice feature made it trivial and safe to map a JSON-RPC request to a stored procedure.
method is the named of the stored procedure and the
params is simply a key/value hash,
where each key maps to a named input argument.
The only tricky part which required some usage of pg_catalog.pg_proc was to determine if the function
returned a SET OF or a single row.
I’ve created two perl modules to do the trick,
joel@ubuntu:~$ psql -c 'CREATE OR REPLACE FUNCTION myfunc(foo text, bar text) RETURNS TEXT AS $ SELECT $1 || $2 $$ LANGUAGE sql SECURITY DEFINER'
joel@ubuntu:~$ curl 'http://localhost/postgres/myfunc?foo=Hello&bar=World'
"error" : null,
"result" : "HelloWorld"
joel@ubuntu:~$ curl 'http://localhost/postgres/now'
"error" : null,
"result" : "2012-06-03 10:46:23.972644+07"
joel@ubuntu:~$ curl 'http://localhost/postgres/random'
"error" : null,
"result" : "0.664589043240994"
Extract from PostgreSQL log:
2012-06-03 10:46:14.270 ICT,"www-data","joel",3601,"[local]",4fcade06.e11,2,"SELECT",2012-06-03 10:46:14 ICT,2/52,0,LOG,00000,"execute dbdpg_p26924_1: SELECT * FROM myfunc(bar := $1,foo := $2);","parameters: $1 = 'World', $2 = 'Hello'",,,,,,,,"pg_proc_jsonrpc"
2012-06-03 10:46:23.972 ICT,"www-data","joel",3603,"[local]",4fcade0f.e13,2,"SELECT",2012-06-03 10:46:23 ICT,2/58,0,LOG,00000,"execute dbdpg_p27386_2: SELECT * FROM now();",,,,,,,,,"pg_proc_jsonrpc"
2012-06-03 10:46:27.732 ICT,"www-data","joel",3605,"[local]",4fcade13.e15,2,"SELECT",2012-06-03 10:46:27 ICT,2/64,0,LOG,00000,"execute dbdpg_p17663_2: SELECT * FROM random();",,,,,,,,,"pg_proc_jsonrpc"
No more middle-layer hassle!
Well, OK, not yet, this is just a proof-of-concept, a lot of work and testing remains until it can be put into production.
I think this could potentially be a very interesting way to make a PostgreSQL database more accessible to the outside world,
in a transparent and convenient way, not requiring any extra work or restarts.
All that’s left is the browser and the database!
Less is more.
That is really nice!
This is interesting but what are the security ramifications?
Brian: The security model relies totally on PostgreSQL. The Apache user is www-data, which normally has no database access. In my example, I’ve granted CONNECT access to the database and EXECUTE rights for the functions which should be accessible by the front-end.
The www-data user cannot do any SELECT, UPDATE, INSERT, DELETE or any other SQL commands.
The functions are created by a different user, which has the necessary SELECT/UPDATE/INSERT/DELETE access. The functions are also declared SECURITY DEFINER, which means they are being run as the user who created them.
When www-data executes a function, it will be run as the other user which created the function.
If there is a security bug in Apache and someone gains access to the front-end machine, they will only be able to connect to the database and execute the functions granted to www-data. I.e., they wouldn’t be able to do much more than what they already can via the JSON-RPC API.
If there is also a security bug in PostgreSQL, then it’s game over.
I think it’s fine to rely on the security of two popular open source projects.
Interesting,I have question if you know other primoamgrng languages is it faster yo use a store procedure or just getting the data a nd filtering it with a primoamgrng languageAnyway, thanks for the post
HEllo Tommy,The ultimate will haeppn, please God: Joel, will finally be overcome and go down on you,in a moment of overwhelming passion, swallowing all he can take of that beautiful cock. You, of course, resisting this gay activity will turn him around and accidentally impale him on your gorgeous cock making him your bitch which everyone of us would so wish to be. I am sure we will all forgive you if you fuck the living daylights out of him; We will all understand and know that you are straight just like the rest of us. But please make sure the video keeps running.Wanking my way through all the vids I do believe my prick is getting bigger or maybe a little sore.Many thanks David
hello!This was a really faulbous topic!I come from itlay, I was fortunate to approach your topic in googleAlso I get much in your website really thank your very much i will come again
yes, the ex gives him all sorts of abuse and then bans him from seeing the girls for a week. At his ex’s rueeqst we have now stopped all contact at weekends, when his youngest daughter is with him.This is difficult for us both, but i dont mind doing it because the girls are innocent in all of this and he is a great dad so i want him to have a good relationship with them.The problem that i have is that his ex still wont back off. I know she doesnt want him back, she is evil towards him, leaving him voicemails telling him how awful and useless he is, when they were married she destroyed his self esteem completely which is why he left because he couldnt take the arguements and abuse anymore.now she is sending me messages insulting me and saying he is seeing other girls when i know he isnt.she even sent one today saying he has dumped me for another girl. i cant get my head around it, she doesnt love him but wont stop stirring.i suppose my question is is there anything more i can do? i love this guy and he loves me, he treats me like a princess so i want to do all i can to help him maintain a good relationship with his daughters (they are 9 and 12) xxxJust as a little note, my guy has stood up for me in the past, he is very loyal. but this just seems to aggravate her and she bans him from seeing his daughters for a while as punishment so at the moment we are just ignoring it and supporting each other. he is totally worth it. just wanted an objective view to see if i was acting right cos i dont have kids of my own so sometimes worry i dont do the right thingoh and he didnt cheat on her with me. we met 12 months after he filed for divorce and moved out. she is refusing to sign the papers, so he is seeing a solicitor.
What a pleasure to meet someone who thinks so clearly
Spiritually speaking: What do you think of this guide to mbautrsate for ex-Catholics?Read the whole thing please. Tips on how to mbautrsate;If you’re a girl1) Get something small if it’s your first time, like a lip gloss container. Make sure it’s got a rounded tip.2) Put a little water on it.3) Get yourself on the ground or your bed. Make sure you’re comfortable.4) Put your feet up on something. Make sure they are higher than your head. Spread your legs.5) For the ultimate experience, relax first. Just lay there. Think about nothing. And DONT BE NERVOUS.6) Slowly begin to touch your breasts. Feel them (have your eyes closed or open but if they are open make sure you’re not focusing on anything)7) Keep one hand on your breast and slowly move the other one down to your thigh. (I did not have underwear but I was wearing pants and a shirt, loose pants.) Move your hand up and down your thigh while massaging your breast. With your breast hand, slowly take the lip gloss container or your object of choice. Your clit might start to get a weird feeling like you really want to touch it. DON’T.9) Tease yourself with the object by gently rubbing the spot between your poophole and vagina. This will drive you nuts. Slowly begin to touch and massage the part right above the hole. (I suggest you know where it is before you start all this.)10) Rub for a while. Gently, occasionally harder but not too hard yet.11) At this point you should be aching to rub harder and just get going. Again, don’t. If you do not feel this yet, continue the teasing, very gently.12) Slowly move your fingers to the hole, don’t put them in, but just finger it softly.13) Take your object and place it near the hole and your other hand. Take your free hand off the hole and start to massage your clit harder. (That’s the spot above the hole)14) Slowly stick the object in. Gently, it shouldn’t feel good yet. It might hurt a small amount going in. That means you’ve bumped a sensitive spot. That’s not a bad thing, just angle it a little and keep going.15) Once it’s in as far as it can be without losing it to your pussy, begin slowly moving it in and out a little. Don’t take it all the way out, just a little. Get faster, and faster. Start massaging your clit HARD. Go nuts. You might feel like your on the brink of an orgasm. You might have one. This feels very good.16) Then stick it in all the way and start pushing it back and forth hitting the sides of your hole. Faster, faster. Massage clit again.17) Repeat steps 15 and 16 as much as you want. If you take it out for longer than 30 seconds, I suggest you excite yourself again with the teasing. If you do, it will be worse. Since you have already done it, you’re going to want it worse.18) I would stop with the lip gloss for now, don’t go on to something bigger. Save that for another night. You could be sore after this but you shouldn’t be unless you used something large.If you’re a boy1)Read this.2)Rub penis.
Hey, killer job on that one you guys!
and the hundreds of pages of redgnias i have to read and catch up on.recently, i was logged into my girlfriends online chat, and i found out that she was talking with another guy asking if he could get her some vodka to take to a hotel for a school event with thousands of coeds. she was going to attend. she told him that she and her friend (female) were going to have the a room to themselves in hotel and get totally f*cked up. When i read this, i was completely devastated. Nothing but horrific images came to my mind and i felt betrayed in a way because i have always trusted her, and we have had serious talks about drinking alcohol and about partying before. And to see this thing going on behind my back is devastating to me. the next day i tried to hint it out if she could tell me if she was planning something behind my back or is she was thinking of doing something. she kept denying it but i knew she didn’t want to admit it. i finally broke out and told her i knew about her plans and that i cannot trust her anymore if she was going to do these kinds of things behind my back. yet instead of feeling any sorrow for the promises she broke and heartbreak she caused, she gets mad at me for spying on her. i do respect her privacy but this time i felt suspicious of her and consequently caught her in the act. so she tells me that she does not want to hear or talk to me ever again forever. i took that comment to the heart and felt as if she was breaking up with me. so then i told her we were done . then shecompletely flips out on me and basically talks sh*t about me and how im not good enough for her. blah blah .. so then later that evening i asked if we could talk and work it this problem. and i apologized to her and everything (even though it wasnt my fault) and she wouldnt take me back. she decided we should just be friends for a while and take a break.i really love her and this is the first time we have ever crossed the line of breaking up, but i just want to fix things with her. but all she tells me is that its my fault for breaking up with her. and im the one being blamed for everything, when in reality, it was she that was doing things behind my back and she basically broke it off with me (by telling me she does not want anything to do with me anymore ever again) .. all i did was say we’re done and now im being blamed for everything.i have been trying to fix things with her and she told me that she wants a break (no communication, no texting, email, nothing whatsoever) until the time is right. after a day or two she sent me a couple messages saying hi and we talked, then the day after we hung out. and we ended up kissing, everything had gone great, romantic dinner, fun evening, happy conversations then the next day she emails me saying that she still feels that im a complete asshole for breaking up with her and that we should go on a break again. this really breaks my heart because i feel as if i have become a complete stranger to her and she calls me by my first name now as if im some random guy talking to her.. im just really scared that she wants to go on this break so that she could move on and forget about me. i really dont know what to do anymore. she keeps bringing up the fact that i broke up with her when in fact it was her fault and i only feel worse about myself for something i shouldnt even be getting blamed for. i really want this relationship to workout for us and i want to put all this mess behind us but she doesnt see it that way and she doesnt want to talk or see me anymore.i want to fix things with her but i dont know how. she wants space but i want to talk and fix things. i really believe we can both work this out by communication, but shes giving me the silent treatment. what should i do???
Cool, and I guess you can make functions which return SETOF to “federate” the database. Interesting stuff.
Great step in the right direction. I think if you guys can give most of the stadrand features of the carts that are out there now (ie CS cart) along with the branding free that would be great. The great thing I like about send studio is the multi user support and admin. Your store suite product should have this as well. What I mean by that is the option to permission customers to use store suite but allow them to have their own domain name without installing anything new. That would be the perfect solution.
trying to install open sis on a sarehd server, it giving me This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) ” error , i also tried to set mysql> SET GLOBAL log_bin_trust_function_creators = 1; it ask for privileges ,also tried manual installation ,but no luck, any suggestions??
I did something similar for PHP and SOAP. I published a project that exposes all my PG functions as a SOAP web service. Naturally I don’t expose this to the WAN and I access these services over a GB connection on the same rack so it’s really fast and secure. The only client connecting is my own PHP app so I naturally do validation and checking before passing anything over to the WS. The project can be found here: https://github.com/sylnsr/pgfunc2php There is just one drawback which highlights a difference with PHP and PG functions: You cannot have 2 functions with the same name regardless of the parameter list being different for each because PHP does not support it.
Hi blogger, i must say you have hi quality articles here.
Your blog should go viral. You need initial traffic only. How to get it?
Search for: Mertiso’s tips go viral
Hello! Since you’re reading this message then you’re living proof that contact form advertising works! We can send your ad message to people via their contact us form on their website. The advantage of this kind of promotion is that messages sent through feedback forms are inherently whitelisted. This improves the chances that your ad will be seen. Absolutely NO Pay per click costs! Pay a one time fee and reach millions of people. For more information please reply to: firstname.lastname@example.org
Looking for fresh buyers? Receive tons of people who are ready to buy sent directly to your website. Boost revenues quick. Start seeing results in as little as 48 hours. To get details reply to: email@example.com
Good evening, I was just visiting your site and filled out your “contact us” form. The feedback page on your site sends you these messages via email which is the reason you’re reading through my message right now right? That’s the most important accomplishment with any kind of online ad, getting people to actually READ your message and I did that just now with you! If you have an advertisement you would like to blast out to millions of websites via their contact forms in the U.S. or to any country worldwide send me a quick note now, I can even focus on particular niches and my pricing is super reasonable. Write a reply here: firstname.lastname@example.org