Re: [GENERAL] Implementing "thick"/"fat" databases
Hello Chris, > > In LedgerSMB, we take this a step further by making the procedures > > into discoverable interfaces, how do you do that ? Karsten -- NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie! Jetzt informieren: http://www.gmx.net/de/go/freephone -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication does not work across datacenter with 20ms latency?
is there anything special you have configured on master and slave? could I see the related configuration on your master and slave? such as wal_keep_segments,checkpoint_segments or any other might be related? thanks a lot! On Sun, Jul 24, 2011 at 11:23 AM, Scott Ribe wrote: > On Jul 23, 2011, at 8:43 PM, Yan Chunlu wrote: > >> I used apt-get to install postgresql, running pg_config showing they >> are exactly the same... > > BTW, forgot to mention this in my first message: I run streaming replication > across the country with latency well over 100ms and no problems. > > -- > Scott Ribe > scott_r...@elevated-dev.com > http://www.elevated-dev.com/ > (303) 722-0567 voice > > > > > -- 闫春路 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Re : [GENERAL] Update columns in same table from update trigger?
On 23 Jul 2011, at 16:49, Pablo Romero Abiti wrote: > Hi Alban, thank's for your reply. I already changed it as you wrote, but I'm > still having the problem that the trigger won't execute unless the value > specified for idcolor in table warehouse does exist. Shouldn't the trigger > execute BEFORE the update process? Ah of course, there is no matching row for the update that you expect to fire the trigger, so it never gets fired (no update takes place). I hadn't realised that from your examples. You can't fire a trigger on non-existant data. > De : Alban Hertroys > À : Pablo Romero Abiti > Cc : "pgsql-general@postgresql.org" > Envoyé le : Samedi 23 Juillet 2011 3h59 > Objet : Re: [GENERAL] Update columns in same table from update trigger? > > On 22 Jul 2011, at 22:02, Pablo Romero Abiti wrote: > > > The problem I'm facing is that the trigger before update won't execute if > > there isn't a row with idcol=3 in the table warehouse. > > > > Here's my code: > > > > CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS ' > >declare idcolmaestro float:=0; > >BEGIN > > select into a idcolor1 from color_eq where idcolor2=old.idcolor; > > You didn't declare 'a'. Perhaps you meant to use idcolmaestro? > > > if a is null then > > Try 'if NOT FOUND' here instead. > > > a=old.idcolor; > > end if; > > > > new.idcolor=a; > > return new; > > END; > > ' LANGUAGE 'plpgsql' VOLATILE; > > I'd probably change this code a bit so that the assignment to idcolor only > takes place if a value with idcolor2=old.idvalue was found: > > if FOUND then > new.idcolor := a; > endif > > return new; > > That saves a few unnecessary CPU cycles. > > Alban Hertroys > > -- > The scale of a problem often equals the size of an ego. > > > > > > > > Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4e2bdf4512091401616986! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Re : [GENERAL] Update columns in same table from update trigger?
On 24 Jul 2011, at 11:00, Alban Hertroys wrote: > You can't fire a trigger on non-existant data. I just realised the above statement is a little inexact: you can't fire row-level triggers on non-existant data. You can still fire statement-level triggers on them, but with those you won't have information about which row(s) were involved, so it won't help your case. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4e2be09d12091863320979! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update columns in same table from update trigger?
On 07/22/2011 11:02 PM, Pablo Romero Abiti wrote: Here's what I want to do: I have a master table that has 2 columns: idcol1 and idcol2, where idcol2 is equivalent to idcol1 Table: color_eq idcol1 idcol2 1 1 2 2 2 3 Table: warehouse idcolqty 110 220 if I execute "update warehouse set qty=10 where idcolor=3", I want the trigger to search table color_eq for idcol2=3, picks its corresponding idcol1 and update the table warehouse with idcol1. The problem I'm facing is that the trigger before update won't execute if there isn't a row with idcol=3 in the table warehouse. Here's my code: CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS ' declare idcolmaestro float:=0; BEGIN select into a idcolor1 from color_eq where idcolor2=old.idcolor; if a is null then a=old.idcolor; end if; new.idcolor=a; return new; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER update_warehouse_trigger before UPDATE ON warehouse FOR EACH ROW EXECUTE PROCEDURE update_warehouse(); Any help would be greatly appreciated! You have 2 choices: 1) Use a before insert trigger and if the row exists, change it to an update. 2) Use a rule Sim
Re: [GENERAL] Implementing "thick"/"fat" databases
On 24/07/11 17:51, Chris Travers wrote: I was thinking similar thoughts, but you not only beat me to it, you made some good points I had not thought of! The only thing I can think of adding: is that it would be good to lock down the database so that only the middleware can access it, everything else accesses the database via the middleware. In general, I am not convinced that middleware is inherently more maintainable than in-db procedures. But the fundamental question is: Is this a a one-application database? If it is, you can use the middleware to be that application lock the db down so only the middleware can use it etc. But what if it isn't?What if we want to support a variety of applications against the same relational database? This has to be fairly commonplace. In this way my experience is that it is often helpful to maintain several levels of stable, public API's both on a table level if possible (as attachment points for triggers), stored proc API's for actually inserting data into relevant areas while enforcing appropriate business logic, and so forth. One of the things we are doing in LedgerSMB is to make the stored procedures discoverable, so the argument names (and eventually the return types) will have meaning the application can use in building calls for the procedure. This eases one important maintenance point because arguments are automatically picked up by the application and as long as best practices in coding are followed, will be handled sanely. (The interface will be extended in the future so that return types determine the class, and the arguments in determine whether we are talking about a presumed object property or a presumed application-specified argument.) Theoretically, we should be able to build objects in languages picking up methods and properties from the Pg system catalogs but we haven't gotten that far yet with code generation. Best Wishes, Chris Travers So it really boils down to 'It depends...' :-) I first started designing systems over 30 years ago. I remember my first design principle I came up with, but more importantly that my next project ignored it for good reasons (same mainframe COBOL environment in both cases)! I feel that for a large company, then the middleware approach is probably better when you have many diverse applications that share a lot in common, but it depends on many different factors. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication does not work across datacenter with 20ms latency?
On 24 Červenec 2011, 6:09, Yan Chunlu wrote: > thanks for all the help! > > @Adrian: yes, only one instance on each machine > > not the slave finally started and could be connect, replication didn't > begin, just following errors: > https://gist.github.com/1102225 These errors just mean the master already removed WAL segments, so the slave can't actually start the replication because there'd be a gap. This usually happens with enough write activity (inserts, updates) when the slave is being setup. Whaht is your wal_keep_segments value? Increase it or set up WAL archiving, so that the slave can get the data. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication does not work across datacenter with 20ms latency?
checkpoint_segments = 64 wal_keep_segments = 128 On Sun, Jul 24, 2011 at 8:25 PM, Tomas Vondra wrote: > On 24 Červenec 2011, 6:09, Yan Chunlu wrote: >> thanks for all the help! >> >> @Adrian: yes, only one instance on each machine >> >> not the slave finally started and could be connect, replication didn't >> begin, just following errors: >> https://gist.github.com/1102225 > > These errors just mean the master already removed WAL segments, so the > slave can't actually start the replication because there'd be a gap. This > usually happens with enough write activity (inserts, updates) when the > slave is being setup. > > Whaht is your wal_keep_segments value? Increase it or set up WAL > archiving, so that the slave can get the data. > > Tomas > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgpool HA not working
Hi I am working on pgpool /postgresql/linux first time. All these three are new to me. I am running pgpool-HA using pacemaker-corosync. I am having following error in my setup. Does any body have any idea about the warning highlighted in logs ? Any type of help would be appreciated a lot... Jul 24 18:40:41 squarepant attrd: [3772]: info: attrd_perform_update: Sent update 1010: probe_complete=true Jul 24 18:40:41 squarepant cib: [3770]: WARN: cib_process_request: Operation complete: op cib_modify for section status (origin=local/attrd/1010, version=0.52.352): The object/attribute does not exist (rc=-22) Jul 24 18:40:41 squarepant attrd: [3772]: WARN: attrd_cib_callback: Update 1010 for probe_complete=true failed: The object/attribute does not exist Jul 24 18:40:41 squarepant pgpool[7635]: INFO: pgpoolRA: request stop, but not running. Jul 24 18:40:41 squarepant crmd: [3896]: info: process_lrm_event: LRM operation pgpool_stop_0 (call=339, rc=0, cib-update=472, confirmed=true) ok Jul 24 18:40:41 squarepant crmd: [3896]: info: match_graph_event: Action pgpool_stop_0 (2) confirmed on squarepant (rc=0) Jul 24 18:40:41 squarepant crmd: [3896]: info: te_rsc_command: Initiating action 12: start pgpool_start_0 on squarepant (local) Jul 24 18:40:41 squarepant crmd: [3896]: info: do_lrm_rsc_op: Performing key=12:112:0:6d68acf3-ab99-409f-b686-8533e4b24ca0 op=pgpool_start_0 ) Jul 24 18:40:41 squarepant lrmd: [3771]: info: rsc:pgpool:340: start Jul 24 18:40:41 squarepant crmd: [3896]: info: te_pseudo_action: Pseudo action 5 fired and confirmed ouput of crm_mon command is Last updated: Sun Jul 24 18:15:59 2011 Stack: Heartbeat Current DC: squarepant (85f4f3d6-650e-4620-8cbb-edb1bc9d389c) - partition with quorum Version: 1.1.5-1.fc14-01e86afaaa6d4a8c4836f68df80ababd6ca3902f 1 Nodes configured, unknown expected votes 3 Resources configured. Online: [ squarepant ] DBIP (ocf::heartbeat:IPaddr2): Started squarepant postgresql (ocf::heartbeat:pgsql): Started squarepant pgpool (ocf::heartbeat:pgpool):Started squarepant FAILED Failed actions: pgpool_monitor_3 (node=squarepant, call=1874, rc=7, status=complete): not running Thanks & Regards Sanjay
Re: [GENERAL] Implementing "thick"/"fat" databases
On Sun, Jul 24, 2011 at 12:34 AM, Karsten Hilbert wrote: > Hello Chris, > >> > In LedgerSMB, we take this a step further by making the procedures >> > into discoverable interfaces, > > how do you do that ? The way we do things in 1.3 beta is relatively free-form and not idea. In future versions I think we intend to tighten this up.I will give you examples of where we are for now as well as where we are headed. In 1.3, the idea is that as much of the function declaration should be semantically meaningful as possible. However we didn't spec out everything. (Problems and solutions below) So you might have a function like: CREATE OR REPLACE FUNCTION batch_search(in_class_id int, in_description text, in_created_by_eid int, in_date_from date, in_date_to date, in_amount_gt numeric, in_amount_lt numeric, in_approved bool) An application can query the argument names as long as the application knows this is a routine for searching batches. It can then map in the class_id, description, created_by_eid, date_from, date_to, amount_gt, amount_lt, and approved fields from a specified source to the arguments and generate an SQL query to hit this argument. There are some problems with this approach as we follow it right now. The biggest one is consistency. If the variables are not always named consistently to object properties it can cause maintenance problems. So this means object definitions and sql statements form complementary portions of code and are in different languages. A second problem is function overloading with loosely typed languages. Our solution is to test for and not allow function overloading. The final problem is that this does not separate object properties from function arguments. A better approach would be to name object properties and arguments with different prefixes. So: CREATE OR REPLACE FUNCTION -- we aren't doing anything with a batch, just looking for them batch_search(arg_class_id int, arg_description text, arg_created_by_eid int, arg_date_from date, arg_date_to date, arg_amount_gt numeric, arg_amount_lt numeric, arg_approved bool) RETURNS SETOF batch_search_result but CREATE OR REPLACE FUNCTION batch_post(obj_id INTEGER) RETURNS batch If we enforce that the function return type must return a type whose properties can be specified as input properties, the following becomes possible: 1) Automatic argument mapping to object properties in a fairly robust way and 2) Automatic merging of return results back into the object that called it. It would also be possible to write code generators to create object wrappers for the SQL types and functions which could then be extended as needed. It also means that object properties and methods as general data structures have at least part of their definition in SQL code. Applications of course may have to extend this in various ways in order to avoid ravioli code. But it means you have an internally consistent basis to build things on. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Implementing "thick"/"fat" databases
On Sat, Jul 23, 2011 at 11:44 PM, Sim Zacks wrote: > I gave a talk on using postgresql as an application server at PG East in > March. > > Basically, we try to implement all business logic using functions, using > plpythonu when necessary. > > For example, we have functions that send email, ftp files, sync remote > databases, etc. > > > It is important to keep your MVC intact and not "php" your function code by > mixing business logic with SQL statements. It depends on what you mean by "business logic." In general my view is that some business logic doesn't belong in stored procedures. In general where I draw the line is between 'data logic' (i.e. logic necessary to retrieve, process, and store data in a meaningful and consistent way meeting inherent requirements including security requirements) and 'application logic' (i.e. logic as to how you obtain, display, present, and use the data). If the goal is to provide a consistent set of business logic to several applications hitting the database, in general you may not want your database to make assumptions about how the data is going to be used, or where it is going to come from. OTOH, if this is a single application database, then I would ask: What do you gain by putting it in plpythonu on the db server instead of on the client? > > > I am currently playing with interactive queries, where the function stops in > the middle, and sends a message to the client asking for input. This isn't a > necessarily a good idea in all cases, but there are some functions where you > don't have the ability to ask a question until it is mostly finished > processing (ie you dont have the information needed to ask the question > until it finishes munging the data, which might be a long process). How does this interface work? You have a separate network socket for this? Something else? What happens if a user goes to lunch or leaves early and then the query hangs pending input? How do you handle this? Is there a timeout on the input request? Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication does not work across datacenter with 20ms latency?
I did the SR procedure again, still no luck: is that normal that after start slave postgresql, the first line of log is: database system was interrupted; last known up at 2011-07-24 10:53:38 CDT?? 4760 2011-07-24 10:55:58 CDT 2011-07-24 10:55:58 CDT @ LOG: database system was interrupted; last known up at 2011-07-24 10:53:38 CDT 4760 2011-07-24 10:55:58 CDT 2011-07-24 10:55:58 CDT @ LOG: entering standby mode 4762 2011-07-24 10:55:59 CDT 2011-07-24 10:55:59 CDT postgres@postgres [local]FATAL: the database system is starting up 4761 2011-07-24 10:55:59 CDT 2011-07-24 10:55:59 CDT @ LOG: streaming replication successfully connected to primary 4764 2011-07-24 10:55:59 CDT 2011-07-24 10:55:59 CDT postgres@postgres 10.28.53.11(53442)FATAL: the database system is starting up 4770 2011-07-24 10:56:00 CDT 2011-07-24 10:56:00 CDT postgres@postgres [local]FATAL: the database system is starting up 4802 2011-07-24 10:56:01 CDT 2011-07-24 10:56:01 CDT postgres@postgres [local]FATAL: the database system is starting up 4760 2011-07-24 10:56:01 CDT 2011-07-24 10:56:01 CDT @ LOG: redo starts at 57/6B002028 4760 2011-07-24 10:56:01 CDT 2011-07-24 10:56:01 CDT @ LOG: invalid record length at 57/6B20E010 4761 2011-07-24 10:56:01 CDT 2011-07-24 10:56:01 CDT @ FATAL: terminating walreceiver process due to administrator command 4760 2011-07-24 10:56:01 CDT 2011-07-24 10:56:01 CDT @ LOG: invalid magic number in log file 87, segment 107, offset 2490368 4847 2011-07-24 10:56:02 CDT 2011-07-24 10:56:02 CDT postgres@postgres [local]FATAL: the database system is starting up 4850 2011-07-24 10:56:02 CDT 2011-07-24 10:56:02 CDT postgres@postgres 10.28.53.11(53443)FATAL: the database system is starting up 4851 2011-07-24 10:56:03 CDT 2011-07-24 10:56:03 CDT postgres@postgres [local]FATAL: the database system is starting up 4860 2011-07-24 10:56:04 CDT 2011-07-24 10:56:04 CDT postgres@postgres [local]FATAL: the database system is starting up 4865 2011-07-24 10:56:05 CDT 2011-07-24 10:56:05 CDT postgres@postgres [local]FATAL: the database system is starting up 4859 2011-07-24 10:56:05 CDT 2011-07-24 10:56:05 CDT @ LOG: streaming replication successfully connected to primary 4874 2011-07-24 10:56:06 CDT 2011-07-24 10:56:06 CDT postgres@postgres [local]FATAL: the database system is starting up 4869 2011-07-24 10:56:06 CDT 2011-07-24 10:56:06 CDT postgres@template1 10.28.53.11(53444)FATAL: the database system is starting up 4879 2011-07-24 10:56:07 CDT 2011-07-24 10:56:07 CDT postgres@postgres [local]FATAL: the database system is starting up 4760 2011-07-24 10:56:07 CDT 2011-07-24 10:56:07 CDT @ LOG: invalid record length at 57/6B2BA010 4859 2011-07-24 10:56:07 CDT 2011-07-24 10:56:07 CDT @ FATAL: terminating walreceiver process due to administrator command 4760 2011-07-24 10:56:07 CDT 2011-07-24 10:56:07 CDT @ LOG: invalid magic number in log file 87, segment 107, offset 2883584 4887 2011-07-24 10:56:08 CDT 2011-07-24 10:56:08 CDT postgres@postgres [local]FATAL: the database system is starting up 4888 2011-07-24 10:56:08 CDT 2011-07-24 10:56:08 CDT @ LOG: streaming replication successfully connected to primary 4892 2011-07-24 10:56:09 CDT 2011-07-24 10:56:09 CDT postgres@postgres [local]FATAL: the database system is starting up 4896 2011-07-24 10:56:09 CDT 2011-07-24 10:56:09 CDT postgres@template1 10.28.53.11(53445)FATAL: the database system is starting up 4901 2011-07-24 10:56:10 CDT 2011-07-24 10:56:10 CDT postgres@postgres [local]FATAL: the database system is starting up 4906 2011-07-24 10:56:11 CDT 2011-07-24 10:56:11 CDT postgres@postgres [local]FATAL: the database system is starting up 4760 2011-07-24 10:56:11 CDT 2011-07-24 10:56:11 CDT @ LOG: invalid record length at 57/6B486010 4888 2011-07-24 10:56:11 CDT 2011-07-24 10:56:11 CDT @ FATAL: terminating walreceiver process due to administrator command 4760 2011-07-24 10:56:11 CDT 2011-07-24 10:56:11 CDT @ LOG: invalid magic number in log file 87, segment 107, offset 4849664 On Sun, Jul 24, 2011 at 8:46 PM, Yan Chunlu wrote: > checkpoint_segments = 64 > wal_keep_segments = 128 > > On Sun, Jul 24, 2011 at 8:25 PM, Tomas Vondra wrote: >> On 24 Červenec 2011, 6:09, Yan Chunlu wrote: >>> thanks for all the help! >>> >>> @Adrian: yes, only one instance on each machine >>> >>> not the slave finally started and could be connect, replication didn't >>> begin, just following errors: >>> https://gist.github.com/1102225 >> >> These errors just mean the master already removed WAL segments, so the >> slave can't actually start the replication because there'd be a gap. This >> usually happens with enough write activity (inserts, updates) when the >> slave is being setup. >> >> Whaht is your wal_keep_segments value? Increase it or set up WAL >> archiving, so that the slave can get the data. >> >> Tomas >> >> > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mai
Re: [GENERAL] weird table sizes
On Saturday, July 23, 2011 3:34:07 pm MirrorX wrote: > i mentioned the sequences number only b/c it seemed stange and i didnt know > if it could be related to the "weird" sizes. > > now i found something more weird...the autovacuum is ON but on > pg_stat_user_tables on this specific table tha last_vacuum and > last_autovacuum are both NULL...how can this happen? Old stats? Try manually running ANALYZE against the table. > > > thx a lot for all the help > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] weird table sizes
On Sun, Jul 24, 2011 at 11:45 AM, Adrian Klaver wrote: > On Saturday, July 23, 2011 3:34:07 pm MirrorX wrote: >> i mentioned the sequences number only b/c it seemed stange and i didnt know >> if it could be related to the "weird" sizes. >> >> now i found something more weird...the autovacuum is ON but on >> pg_stat_user_tables on this specific table tha last_vacuum and >> last_autovacuum are both NULL...how can this happen? > > Old stats? Try manually running ANALYZE against the table. > Are these on the same server or different servers? Is it possible that there is database bloat on the second? How many rows do you (roughly) expect? is that estimate reasonable? Best wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication does not work across datacenter with 20ms latency?
Dne 24.7.2011 14:46, Yan Chunlu napsal(a): > checkpoint_segments = 64 > wal_keep_segments = 128 This information alone is not sufficient - we don't know how much write activity is on the primary system, so we can't say if those number are sufficient or not. You have to tune them according to write activity on the primary server. For example let's suppose the current WAL segment on the primary is "1" and that it's configured with wal_keep_segments = 5 (i.e. about 80MB of data). Before you prepare and start the slave machine, someone writes 100MB of data to the primary database (one big insert/update or a lot of small ones, doesn't matter). 100MB is about 6 WAL segments, so the current WAL segment on the primary is 7, and because of wal_keep_segments there are segments 3,4,5,6,7 available. But when the slave connects, it asks for segment no. 2 and it's not available. It's not possible to skip that segment so the replication fails to start. If the primary only received 60MB of data, it'd probably worked (there'd be enough segments kept on the primary). Those 128 segments is about 2GB of data. How much data is written on the primary between creating a filesystem copy and starting the slave? You don't neet to keep the files on the master, you can set up archiving and keep them somewhere else (on a different system etc.). Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Disallow access from psql, or allow access only from specific client app
Hi everybody: I searched the web trying to find an answer to this, but found none. I have a postgresql server and a database, and I granted access to some users. However I want them to access the data only through some specific client application. I do not want them to have access through psql or some other kind of client app. But, as I created them a user/pass they could use them. What would you do? Thanks in advance.
Re: [GENERAL] Disallow access from psql, or allow access only from specific client app
On Sun, Jul 24, 2011 at 14:36, Mario Puntin wrote: > > Hi everybody: > I searched the web trying to find an answer to this, but found none. I have > a postgresql server and a database, and I granted access to some users. > However I want them to access the data only through some specific client > application. I do not want them to have access through psql or some other > kind of client app. But, as I created them a user/pass they could use them. > What would you do? > Thanks in advance. Make a web front end, and present them with only the queries you want them to have, via a dropdown list and a set of fields for which you sanitize the input. Alternatively, build a GUI app that does the same thing, but if your users are sophisticated and/or malicious you'll also have to build the GUI with some sort of application authentication and encryption. Kurt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow access from psql, or allow access only from specific client app
On Sun, Jul 24, 2011 at 2:46 PM, Kurt Buff wrote: > On Sun, Jul 24, 2011 at 14:36, Mario Puntin wrote: >> >> Hi everybody: >> I searched the web trying to find an answer to this, but found none. I have >> a postgresql server and a database, and I granted access to some users. >> However I want them to access the data only through some specific client >> application. I do not want them to have access through psql or some other >> kind of client app. But, as I created them a user/pass they could use them. >> What would you do? >> Thanks in advance. > > Make a web front end, and present them with only the queries you want > them to have, via a dropdown list and a set of fields for which you > sanitize the input. > > Alternatively, build a GUI app that does the same thing, but if your > users are sophisticated and/or malicious you'll also have to build the > GUI with some sort of application authentication and encryption. Don't mean to butt in, but how does this meet Mario's requirement of blocking access from psql and/or only allowing access from a specific client? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow access from psql, or allow access only from specific client app
On Sun, Jul 24, 2011 at 2:36 PM, Mario Puntin wrote: > > Hi everybody: > I searched the web trying to find an answer to this, but found none. I have > a postgresql server and a database, and I granted access to some users. > However I want them to access the data only through some specific client > application. I do not want them to have access through psql or some other > kind of client app. But, as I created them a user/pass they could use them. > What would you do? > Thanks in advance. > > The key thing is that you are going to have to have some way to restrict access. The protocol itself does not include any way of authenticating the application per se to be an approved one (and that is a challenging task if attempted, probably impossible to do securely). So what that leaves is the ability to restrict access based on incoming connection characteristics. This probably means some sort of middleware serving the app, and other connections being denied in the pg_hba.conf Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow access from psql, or allow access only from specific client app
On Sun, Jul 24, 2011 at 5:36 PM, Mario Puntin wrote: > > Hi everybody: > I searched the web trying to find an answer to this, but found none. I have > a postgresql server and a database, and I granted access to some users. > However I want them to access the data only through some specific client > application. I do not want them to have access through psql or some other > kind of client app. But, as I created them a user/pass they could use them. > What would you do? > Thanks in advance. > I would modify their given username and/or password within the client application. So you tell someone that their username is "chris" and their password is "monkey", but in your app you add "face" to the username and "butt" to the password, so their postgres username is "chrisface" and their postgres password is "monkeybutt". -Chris(face) -- e-Mail is the equivalent of a postcard written in pencil. This message may not have been sent by me, or intended for you. It may have been read or even modified while in transit. e-Mail disclaimers have the same force in law as a note passed in study hall. If your corporate attorney says that you need an disclaimer in your signature, you need a new corporate attorney.
Re: [GENERAL] Disallow access from psql, or allow access only from specific client app
On Sun, Jul 24, 2011 at 14:48, Lonni J Friedman wrote: > On Sun, Jul 24, 2011 at 2:46 PM, Kurt Buff wrote: >> On Sun, Jul 24, 2011 at 14:36, Mario Puntin wrote: >>> >>> Hi everybody: >>> I searched the web trying to find an answer to this, but found none. I have >>> a postgresql server and a database, and I granted access to some users. >>> However I want them to access the data only through some specific client >>> application. I do not want them to have access through psql or some other >>> kind of client app. But, as I created them a user/pass they could use them. >>> What would you do? >>> Thanks in advance. >> >> Make a web front end, and present them with only the queries you want >> them to have, via a dropdown list and a set of fields for which you >> sanitize the input. >> >> Alternatively, build a GUI app that does the same thing, but if your >> users are sophisticated and/or malicious you'll also have to build the >> GUI with some sort of application authentication and encryption. > > Don't mean to butt in, but how does this meet Mario's requirement of > blocking access from psql and/or only allowing access from a specific > client? The way I read OPs requirements is that he doesn't want them to be able to use queries to pgsql directly or through a pgsql client. This requires some other kind of client, which he explicitly stated[1]. The implication is that he wants to limit their ability to manipulate the data in certain ways - either to read data that to which they should not have access, or to modify or delete data. To do that requires an application that presents and enforces the choices that his design requirements dictate. Kurt [1] One thing that is a bit ambiguous is his use of the term 'client'. If by that he means a software application, my comments stand. If by that instead he means a host or set of hosts, then my comments carry even more freight, because he's going to have to validate from which hosts the traffic is coming. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow access from psql, or allow access only from specific client app
Hello, I do not know if your clients are superusers on their machines, one thing you can do is to remove psql client from these machines. In general, for example in a company, the users do not have administration privileges. Regards From: Kurt Buff To: Lonni J Friedman Cc: Mario Puntin ; pgsql-general@postgresql.org Sent: Mon, July 25, 2011 12:31:13 AM Subject: Re: [GENERAL] Disallow access from psql, or allow access only from specific client app On Sun, Jul 24, 2011 at 14:48, Lonni J Friedman wrote: > On Sun, Jul 24, 2011 at 2:46 PM, Kurt Buff wrote: >> On Sun, Jul 24, 2011 at 14:36, Mario Puntin wrote: >>> >>> Hi everybody: >>> I searched the web trying to find an answer to this, but found none. I have >>> a postgresql server and a database, and I granted access to some users. >>> However I want them to access the data only through some specific client >>> application. I do not want them to have access through psql or some other >>> kind of client app. But, as I created them a user/pass they could use them. >>> What would you do? >>> Thanks in advance. >> >> Make a web front end, and present them with only the queries you want >> them to have, via a dropdown list and a set of fields for which you >> sanitize the input. >> >> Alternatively, build a GUI app that does the same thing, but if your >> users are sophisticated and/or malicious you'll also have to build the >> GUI with some sort of application authentication and encryption. > > Don't mean to butt in, but how does this meet Mario's requirement of > blocking access from psql and/or only allowing access from a specific > client? The way I read OPs requirements is that he doesn't want them to be able to use queries to pgsql directly or through a pgsql client. This requires some other kind of client, which he explicitly stated[1]. The implication is that he wants to limit their ability to manipulate the data in certain ways - either to read data that to which they should not have access, or to modify or delete data. To do that requires an application that presents and enforces the choices that his design requirements dictate. Kurt [1] One thing that is a bit ambiguous is his use of the term 'client'. If by that he means a software application, my comments stand. If by that instead he means a host or set of hosts, then my comments carry even more freight, because he's going to have to validate from which hosts the traffic is coming. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow access from psql, or allow access only from specific client app
On 25/07/2011 5:36 AM, Mario Puntin wrote: Hi everybody: I searched the web trying to find an answer to this, but found none. I have a postgresql server and a database, and I granted access to some users. However I want them to access the data only through some specific client application. I do not want them to have access through psql or some other kind of client app. But, as I created them a user/pass they could use them. You have a few options. Possibly the easiest is to set the PostgreSQL server up to require an x.509 client certificate during SSL authentication. Embed that certificate in your application. Users won't have direct access to the certificate and won't be able to get in using any other tool or application. Sniffing the wire protocol with wireshark or the like won't help them, since they can only see the SSL handshake and that doesn't ever transmit the private key part of the certificate. To break this, they'd have to unpack the binary of your application and extract the certificate and private key from your application binary. While not especially hard, it's going to be beyond 99.99% of users. To make it even harder, you can regenerate your certificate with every update and revoke the cert for the update-before-last, thus forcing old clients to update to regain access, and invalidating any extracted certificate. If you use a client certificate approach you can use your own private CA. You don't need to have a CA trusted by anybody but you, so there's no need to pay for official certs from some big name. OpenSSL can make a perfectly good CA and sign server- and client-certificates based on it. There are plenty of instructions on how around the web. If you don't want to deal with client certificates, the password transformation idea given by Chris Curvey makes sense. Rather than simply appending something, I'd probably append something then hash the password though, just to make it less obvious what I was doing. Be aware that a user on the client machine will be able to easily sniff out the transformed password from network traffic using wireshark or the like unless you force use of SSL, so use of SSL is pretty much mandatory if you take this approach. Even then they could extract it, but they'd have to be able to extract the session key from the app's memory, and again that's beyond the vast majority of users. Personally, I'd probably use both of the above approaches, but I'm kind of paranoid. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disallow access from psql, or allow access only from specific client app
On 07/24/11 2:36 PM, Mario Puntin wrote: Hi everybody: I searched the web trying to find an answer to this, but found none. I have a postgresql server and a database, and I granted access to some users. However I want them to access the data only through some specific client application. I do not want them to have access through psql or some other kind of client app. But, as I created them a user/pass they could use them. What would you do? Thanks in advance. I would put ALL the database access in a webservice, running on a secured server, which their client program accesses and ONLY that webservice has the access credentials to connect to the database. so, the user runs a client program, it has credentials to connect to this webservice (tomcat, or whatever), but no direct access to the database at all. the user's workstations are flat denied any access to the database even if they did have the credentials. the webservice has all the business logic that queries the database. so if the client app needs to edit a customer record, for example, it makes a remote procedure call to the webservice "EditClientRecord()", and that in turn connects to SQL and does the dirty work. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication does not work across datacenter with 20ms latency?
checkpoint_segments = 64 wal_keep_segments = 128 this setting seems is for 5GB capacity, I think there is noway I would ever write 5GB data during the rsync progress. I think the problem is still "invalid record length" and "invalid magic number", it start showing right after I complete sync data and start slave. If I stop slave later and restart, yes it could show xlog not found and can not catch master. but why the "invalid" things in the first place? On Mon, Jul 25, 2011 at 4:28 AM, Tomas Vondra wrote: > Dne 24.7.2011 14:46, Yan Chunlu napsal(a): >> checkpoint_segments = 64 >> wal_keep_segments = 128 > > This information alone is not sufficient - we don't know how much write > activity is on the primary system, so we can't say if those number are > sufficient or not. You have to tune them according to write activity on > the primary server. > > For example let's suppose the current WAL segment on the primary is "1" > and that it's configured with wal_keep_segments = 5 (i.e. about 80MB of > data). > > Before you prepare and start the slave machine, someone writes 100MB of > data to the primary database (one big insert/update or a lot of small > ones, doesn't matter). 100MB is about 6 WAL segments, so the current WAL > segment on the primary is 7, and because of wal_keep_segments there are > segments 3,4,5,6,7 available. > > But when the slave connects, it asks for segment no. 2 and it's not > available. It's not possible to skip that segment so the replication > fails to start. > > If the primary only received 60MB of data, it'd probably worked (there'd > be enough segments kept on the primary). > > Those 128 segments is about 2GB of data. How much data is written on the > primary between creating a filesystem copy and starting the slave? > > You don't neet to keep the files on the master, you can set up archiving > and keep them somewhere else (on a different system etc.). > > Tomas > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re : Re : [GENERAL] Update columns in same table from update trigger?
Thank you for our help anyway! De : Alban Hertroys À : Alban Hertroys Cc : Pablo Romero Abiti ; "pgsql-general@postgresql.org" Envoyé le : Dimanche 24 Juillet 2011 4h06 Objet : Re: Re : [GENERAL] Update columns in same table from update trigger? On 24 Jul 2011, at 11:00, Alban Hertroys wrote: > You can't fire a trigger on non-existant data. I just realised the above statement is a little inexact: you can't fire row-level triggers on non-existant data. You can still fire statement-level triggers on them, but with those you won't have information about which row(s) were involved, so it won't help your case. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:1288,4e2be0a412091498931284!
[GENERAL] Would it be possible
Dear all, I am using Postgres-8.4.2 on Windows system. I have 2 databases in my postgres database ( globedatabase (21GB), urldatabase). I restore globedatabase from a .sql file on yesterday morning.I insert some new data in that database. In the evening, by mistake I issued a *drop database globedatabase* command. Today morning, I restore again the same database from backup (.sql) file. My .sql file have data till yesterday morning but I want newly insert data now. Is it possible. Is it possible to get the data back till the state before drop database command. My pglog files is in the E:/data directory & Binary log is also enabled. Please let me know if it is possible. It's urgent. Thanks & Regards Adarsh Sharma
Re: [GENERAL] Would it be possible
* Adarsh Sharma wrote: In the evening, by mistake I issued a *drop database globedatabase* command. Is it possible to get the data back till the state before drop database command. My pglog files is in the E:/data directory & Binary log is also enabled. You do not mention that you have a file-system level backup from before the DROP DATABASE. Assuming you do not have one, then no, it is not possible. You cannot restore WAL to a cluster that is not in the same state it was in when that WAL was generated. If you have the fs-level backup (the kind of backup you need to use pg_start_backup()/pg_stop_backup() to create), as well as all WAL segments from before the time of that backup until the moment you want to go back to, then you can do it (but only for the entire cluster, so you might need to install Postgres somewhere else just for recovery). Look in the manual under "Point In Time Recovery" (PITR). -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Implementing "thick"/"fat" databases
On 07/24/2011 06:58 PM, Chris Travers wrote:
On Sat, Jul 23, 2011 at 11:44 PM, Sim Zacks wrote:
I gave a talk on using postgresql as an application server at PG East in
March.
Basically, we try to implement all business logic using functions, using
plpythonu when necessary.
For example, we have functions that send email, ftp files, sync remote
databases, etc.
It is important to keep your MVC intact and not "php" your function code by
mixing business logic with SQL statements.
It depends on what you mean by "business logic." In general my view
is that some business logic doesn't belong in stored procedures.
In general where I draw the line is between 'data logic' (i.e. logic
necessary to retrieve, process, and store data in a meaningful and
consistent way meeting inherent requirements including security
requirements) and 'application logic' (i.e. logic as to how you
obtain, display, present, and use the data).
If the goal is to provide a consistent set of business logic to
several applications hitting the database, in general you may not want
your database to make assumptions about how the data is going to be
used, or where it is going to come from.
OTOH, if this is a single application database, then I would ask:
What do you gain by putting it in plpythonu on the db server instead
of on the client?
The goal is to make our system client agnostic, Most of our GUI is
written in wxpython, we also have some web functions and even a barcode
terminal function, written in C#. We would like to use an application
server, so that all the code is run on the server but we don't want to
be web-based. I don't want to have the same business logic written into
various clients as that causes maintenance headaches.
The way that postgresql works is that each session is its own process.
That means that from the server perspective (at least in most ways), my
plpython function is not connected to any other call on the database. My
goal is to allow any client to request functionality and have it execute
the same way every time.
Data logic is built in by use of constraints and triggers and some
functions, business logic is built only into functions.
In other words, our postgresql server is a hybrid database/application
server.
I am currently playing with interactive queries, where the function stops in
the middle, and sends a message to the client asking for input. This isn't a
necessarily a good idea in all cases, but there are some functions where you
don't have the ability to ask a question until it is mostly finished
processing (ie you dont have the information needed to ask the question
until it finishes munging the data, which might be a long process).
How does this interface work? You have a separate network socket for
this? Something else?
What happens if a user goes to lunch or leaves early and then the
query hangs pending input? How do you handle this? Is there a
timeout on the input request?
Best Wishes,
Chris Travers
As I mentioned, I am still playing around and testing this, and have
The concept is that each client has a SocketServer thread that listens
on a specific port (8080 in this case).
My database function is called python_prompt pasted below.
It has a timeout of 2 minutes after which the function errors out with a
timeout. The user will get a message that the function did not complete.
The important thing is that it has a timeout. If the timeout is 20
minutes, that is also ok. Depending on the query that calls this
function, there do not have to be any locks being used and as this is
its own process and it is basically sleeping while waiting for the
answer, it doesn't use resources (aside from a connection and the work
mem in the calling query, of course).
Using this, a plpgsql query can prompt the user, "You are about to use
more stock then you have, are you sure you want to do this?"
CREATE OR REPLACE FUNCTION python_prompt(v_question text)
RETURNS boolean AS
$BODY$
import time
import socket
ipaddr=plpy.execute("select inet_client_addr()")[0]["inet_client_addr"]
HOST, PORT = str(ipaddr), 8080
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.settimeout(120)
sock.connect((HOST, PORT))
sock.send(v_question + "\n")
ans=sock.recv(1024)
sock.close()
if ans=="yes":
return true
else:
return false
$BODY$
LANGUAGE plpythonu VOLATILE;
I haven't fully decided to use this yet, but one of my goals is to make
my GUI as dumb as possible.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
