[GENERAL] RAISE with C?
Hi,
Is it possible to do the equivalent of RAISE EXCEPTION inside a C-function?
Like this:
PG_FUNCTION_INFO_V1(check_something);
Datum check_something(PG_FUNCTION_ARGS)
{
// RAISE EXCEPTION 'bla bla'
}
CREATE OR REPLACE FUNCTION do_check() RETURNS text AS $$
BEGIN
PERFORM check_something();
RETURN 'All good';
EXCEPTION
WHEN RAISE EXCEPTION THEN
RETURN 'Something bad';
END;
$$ LANGUAGE plpgsql IMMUTABLE;
That code may not work but you get the idea, I want to throw an exception from
a C function that can be caught in PL/pgSQL. Also is it further possible to
catch the message that RAISE EXCEPTION sends? Like 'Something bad: bla bla'
Thanks,
Elliot
--
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] RAISE with C?
Hello
2010/12/20 Elliot Chance elliotcha...@gmail.com:
Hi,
Is it possible to do the equivalent of RAISE EXCEPTION inside a C-function?
Like this:
sure, there are functions elog and ereport
Regards
Pavel Stehule
PG_FUNCTION_INFO_V1(check_something);
Datum check_something(PG_FUNCTION_ARGS)
{
// RAISE EXCEPTION 'bla bla'
}
CREATE OR REPLACE FUNCTION do_check() RETURNS text AS $$
BEGIN
PERFORM check_something();
RETURN 'All good';
EXCEPTION
WHEN RAISE EXCEPTION THEN
RETURN 'Something bad';
END;
$$ LANGUAGE plpgsql IMMUTABLE;
That code may not work but you get the idea, I want to throw an exception
from a C function that can be caught in PL/pgSQL. Also is it further possible
to catch the message that RAISE EXCEPTION sends? Like 'Something bad: bla bla'
Thanks,
Elliot
--
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
[GENERAL] pg_restore 8.x to postgreSQL 9.x functions and triggers aren't created
Hello, We have two postgreSQL servers that are in the latest 9.x as testing, but when we use pg_dump and pg_restore, our functions and triggers are never copied to postgreSQL Server 9.x. This is how we restore: data=`date +%Y_%m_%d` pg_restore -c -i -h 192.168.0.9 -p 5432 -U postgres -d globalgest -v /Users/montx/documents/BackUp/globalgest/globalgest_$data we have only two warnings: ... pg_restore: dropping FUNCTION rowsaffected() pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 32; 1255 36705 FUNCTION rowsaffected() postgres pg_restore: [archiver (db)] could not execute query: ERROR: function public.rowsaffected() does not exist Command was: DROP FUNCTION public.rowsaffected(); pg_restore: dropping FUNCTION repairassentamentsnumero(integer) pg_restore: dropping FUNCTION process_audit() pg_restore: dropping FUNCTION increment_lock_version() pg_restore: dropping FUNCTION increment(integer) pg_restore: dropping FUNCTION getserialnumber(integer, integer) pg_restore: dropping FUNCTION comptesrepair() pg_restore: dropping FUNCTION rowsaffected() pg_restore: [archiver (db)] Error from TOC entry 31; 1255 36704 FUNCTION rowsaffected() postgres pg_restore: [archiver (db)] could not execute query: ERROR: function menus.rowsaffected() does not exist Command was: DROP FUNCTION menus.rowsaffected(); pg_restore: dropping FUNCTION process_audit() pg_restore: dropping PROCEDURAL LANGUAGE plpgsql pg_restore: dropping COMMENT SCHEMA public pg_restore: dropping SCHEMA public pg_restore: dropping SCHEMA menus pg_restore: dropping SCHEMA audit pg_restore: creating SCHEMA audit pg_restore: creating SCHEMA menus pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating FUNCTION process_audit() pg_restore: creating FUNCTION rowsaffected() pg_restore: creating FUNCTION comptesrepair() pg_restore: creating FUNCTION getserialnumber(integer, integer) pg_restore: creating FUNCTION increment(integer) pg_restore: creating FUNCTION increment_lock_version() pg_restore: creating FUNCTION process_audit() pg_restore: creating FUNCTION repairassentamentsnumero(integer) pg_restore: creating FUNCTION rowsaffected() pg_restore: creating FUNCTION updateallcomptes(integer) pg_restore: creating FUNCTION updatecompte(integer) pg_restore: creating FUNCTION updatecompte11(integer) pg_restore: creating FUNCTION updatecompte3_5(integer) pg_restore: creating TABLE assentaments pg_restore: creating TABLE audit ... when restoring the same file to any of our postgreSQL Servers 8.x we have no problems. thanks, r. -- 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] pg_restore 8.x to postgreSQL 9.x functions and triggers aren't created [solved]
ok, solved. it was a problem with the application that interfaces with pg that has a bug ... sorry, regards, r. On 20dic, 2010, at 09:28 , Raimon Fernandez wrote: Hello, We have two postgreSQL servers that are in the latest 9.x as testing, but when we use pg_dump and pg_restore, our functions and triggers are never copied to postgreSQL Server 9.x. This is how we restore: data=`date +%Y_%m_%d` pg_restore -c -i -h 192.168.0.9 -p 5432 -U postgres -d globalgest -v /Users/montx/documents/BackUp/globalgest/globalgest_$data we have only two warnings: ... pg_restore: dropping FUNCTION rowsaffected() pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 32; 1255 36705 FUNCTION rowsaffected() postgres pg_restore: [archiver (db)] could not execute query: ERROR: function public.rowsaffected() does not exist Command was: DROP FUNCTION public.rowsaffected(); pg_restore: dropping FUNCTION repairassentamentsnumero(integer) pg_restore: dropping FUNCTION process_audit() pg_restore: dropping FUNCTION increment_lock_version() pg_restore: dropping FUNCTION increment(integer) pg_restore: dropping FUNCTION getserialnumber(integer, integer) pg_restore: dropping FUNCTION comptesrepair() pg_restore: dropping FUNCTION rowsaffected() pg_restore: [archiver (db)] Error from TOC entry 31; 1255 36704 FUNCTION rowsaffected() postgres pg_restore: [archiver (db)] could not execute query: ERROR: function menus.rowsaffected() does not exist Command was: DROP FUNCTION menus.rowsaffected(); pg_restore: dropping FUNCTION process_audit() pg_restore: dropping PROCEDURAL LANGUAGE plpgsql pg_restore: dropping COMMENT SCHEMA public pg_restore: dropping SCHEMA public pg_restore: dropping SCHEMA menus pg_restore: dropping SCHEMA audit pg_restore: creating SCHEMA audit pg_restore: creating SCHEMA menus pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating FUNCTION process_audit() pg_restore: creating FUNCTION rowsaffected() pg_restore: creating FUNCTION comptesrepair() pg_restore: creating FUNCTION getserialnumber(integer, integer) pg_restore: creating FUNCTION increment(integer) pg_restore: creating FUNCTION increment_lock_version() pg_restore: creating FUNCTION process_audit() pg_restore: creating FUNCTION repairassentamentsnumero(integer) pg_restore: creating FUNCTION rowsaffected() pg_restore: creating FUNCTION updateallcomptes(integer) pg_restore: creating FUNCTION updatecompte(integer) pg_restore: creating FUNCTION updatecompte11(integer) pg_restore: creating FUNCTION updatecompte3_5(integer) pg_restore: creating TABLE assentaments pg_restore: creating TABLE audit ... when restoring the same file to any of our postgreSQL Servers 8.x we have no problems. thanks, r. -- 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: [GENERAL] FTS phrase searches
On Sun, 19 Dec 2010, Glenn Maynard wrote: 2010/12/19 Oleg Bartunov o...@sai.msu.su: You might be interested in http://www.sai.msu.su/~megera/wiki/2009-08-12 Thanks, that looks pretty much like what I had in mind. Hopefully that'll get merged for 9.0+1; phrases are a major part of all text searches. Several companies interested in phrase search, but actually we got no support for this, so we postpone it. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to guard PostgreSQL from overcharged and/or malicious (read-only) queries?
I'd like to guard postgres from overcharged and/or malicious queries. The queries are strinctly read-only (from a SQL users perspective). For doing this I'd like to code two functions (preferrably pl/pgsql): 1. Filter out all SQL commands which are *not* read-only (no DROP/DELETE/UPDATE/TRUNCATE). 2. Get the estimated time (units) from PostgreSQL planner in a reliable way (if possible standard/ANSI). Can someone give me some hints? Yours, S. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_statsinfo problem
Does any one use pg_statsinfo for Postgresql 9.0.1? Is there any similar tools to collect DB Snapshots? I am facing problem during pg_statsinfo configuration. I set as follows: shared_preload_libraries = 'pg_statsinfo,pg_stat_statements' custom_variable_classes = 'pg_statsinfo' But my Db is not starting.
Re: [GENERAL] pg_statsinfo problem
Hi ! I am facing problem during pg_statsinfo configuration. I set as follows: shared_preload_libraries = 'pg_statsinfo,pg_stat_statements' custom_variable_classes = 'pg_statsinfo' But my Db is not starting. Which version did you use ? And what log-message were generated ? If you used rpm of pg_statsinfo-2.0.0 on RHEL5_x86_64, please try to use pg_statsinfo-2.0.1. ( I mistook to edit the RPM SPEC file for pg90.rhel5.x86_64... So some user reported that No such files error was occured when they used the rpm of pg_statsinfo-2.0.0 with PG90 on RHEL5.x86_64) If you have more questions about pg_statsinfo, please post to ML for pg_statsinfo, or send me directly. ( http://pgfoundry.org/mail/?group_id=1000422 ) Best regards, -- Tatsuhito Kasahara kasahara.tatsuhito _at_ 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] Maximum size for char or varchar with limit
2010/12/19 Jasen Betts ja...@xnet.co.nz I have some values of perhaps 20Mb that I might want to store samples of in a partitioned table. (so I can delete them easily) what's the right way? 20 Mbits or 20 MBytes? how big samples? answer to your question depends on what you want to do with these samples in SQL. if you could give more background information... I guess I could just keep them as disk files and rotate the directories as I rotate partitions. if only reason is to store - then files are probably best idea. PS. why did you post a reply to this thread? It does not seem related
Re: [GENERAL] pg_statsinfo problem
2010/12/20 Devrim GÜNDÜZ dev...@gunduz.org: Sorry for breaking the thread a bit -- but where is that specfile? I cannot find it in the tarball. Also SRPMs are missing on the website. Now, specfiles and SRPMs are not provided on the pg_statsindo project page.. I'll upload these files ASAP. Best regards, -- Tatsuhito Kasahara kasahara.tatsuhito _at_ 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] How to guard PostgreSQL from overcharged and/or malicious (read-only) queries?
On 20 Dec 2010, at 10:05, Stefan Keller wrote: I'd like to guard postgres from overcharged and/or malicious queries. The queries are strinctly read-only (from a SQL users perspective). For doing this I'd like to code two functions (preferrably pl/pgsql): 1. Filter out all SQL commands which are *not* read-only (no DROP/DELETE/UPDATE/TRUNCATE). Most people do this using permissions. 2. Get the estimated time (units) from PostgreSQL planner in a reliable way (if possible standard/ANSI). I don't think there's a way to do that directly, not without hacking the source. What you can do is to have all users go through a SECURITY DEFINER type of function that does this for them. That function can then read the output of EXPLAIN query for its estimates. Those aren't exactly times, but cost estimates. The actual time taken depends on your hardware, you would need to do some measurements to see how planned costs and actual time relate. I'm not sure this is a good idea though. Firstly, testing the query plan adds a little overhead to every query coming in. It's not a lot, but if someone fires a lot of small fast queries it could become a problem. You would be hurting the people who're using your database correctly, instead of the people who're abusing it. Secondly, you could achieve a similar effect by limiting the amount of time a query is allowed to run. I'm pretty sure there are configuration options that cause long-running queries to get killed after a set time. Thirdly... Reliable estimates??? Lol! Seriously, as hard as Postgres tries to keep statistics that make sense, I don't think they can ever be considered entirely reliable. You may not be vacuuming frequently enough, your statistics target may be too small or your data might not be suitable for statistical analysis (random numbers and very unbalanced distributions are good examples). Therefore, if you plan to rely on the estimated time a query takes, you're going to be wrong sometimes. 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,4d0f4181802653553761881! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq ASYNC with PQgetResult and PQisBusy
Hello,
I'm trying to create a plugin using the libpq.
Almost everything is working, and now I want to implememt the asynchronous
issue.
I send the SQL using the PQsendQuery, and my interface is not blocking, great.
Now, everytime I check fot the PQgetResult my interface gets blocked.
So, now I'm using the PQisBusy to check if postgre is still busy and I can
safely call the PQgetResult wihtout blocking, or just wait *some time* before
sending a new PQisBusy.
Before every PQisBusy i call PQconsumeInput to update the status.
So, in pseudo code:
1. PQsendQuery (a really slow select just to check the asyncronous)
2. From a timer every 0.2 seconds, I call:
2.1 PQconsumeInput
2.2 PQisBusy
2.3 evaluate = if it's busy = sleep and start again from 2 ; if it's not
busy, continue
2.4 call PQgetResult
Using PQisBusy it's not working, it's taking really longer to just send the 0
(non-busy) and at this moment the PQgetResult is null.
If I force to call the PQgetResult after just one second of the PQsendQuery I
can get the PQgetResult, without testing the PQisBusy.
here is my montxPG_isBusy
static long montxPG_isBusy()
{ int execStatus;
int consumeeVar;
consumeeVar = PQconsumeInput(gPGconn);
if (consumeeVar == 0) return (long) PGRES_FATAL_ERROR;
execStatus = PQisBusy(gPGconn);
return (long) execStatus;
}
thanks,
regards,
r.
--
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] Copy From suggestion
Thanks, Adrian, Ill try a windows compile of pgloader sometime during the holidays. Its true that I already have a solution (export = 65000 row chunks, import into Excel, export via Excel puts quotes around the text columns), but something faster and more efficient would really help in this case. -Mark _ De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] De la part de Adrian Klaver Envoyé : 18 décembre 2010 18:05 À : pgsql-general@postgresql.org Cc : Mark Watson Objet : Re: [GENERAL] Copy From suggestion On Friday 17 December 2010 7:46:12 am Mark Watson wrote: Hello all, Firstly, I apologise if this is not the correct list for this subject. Lately, I've been working on a data conversion, importing into Postgres using Copy From. The text file I'm copying from is produced from an ancient program and produces either a tab or semi-colon delimited file. One file contains about 1.8M rows and has a 'comments' column. The exporting program, which I am forced to use, does not surround this column with quotes and this column contains cr/lf characters, which I must deal with (and have dealt with) before I can import the file via Copy. Hence to my suggestion: I was envisioning a parameter DELIMITER_COUNT which, if one was 100% confident that all columns are accounted for in the input file, could be used to alleviate the need to deal with cr/lf's in varchar and text columns. i.e., if copy loaded a line with fewer delimiters than delimiter_count, the next line from the text file would be read and the assignment of columns would continue for the current row/column. Just curious as to the thoughts out there. Thanks to all for this excellent product, and a merry Christmas/holiday period to all. Mark Watson A suggestion,give pgloader a look; http://pgloader.projects.postgresql.org/ If I am following you it might already have the solution to the multi-line problem. In particular read the History section of the docs. Thanks, -- 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 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1170 / Virus Database: 426/3325 - Release Date: 12/19/10
Re: [GENERAL] Serial key mismatch in master and slave, while using slony
On Sat, Dec 18, 2010 at 7:12 AM, Vishnu S. vishn...@nestgroup.net wrote: I am using Slony-I slony-I-2.0.2 in windows .I have a master and a slave machine. The replication is working fine. When the master switches to slave , there exists a serial key mismatch in master and slave machines. So insertion fails in the slave machines. I am using the failover command to switch the slave to master. Now the error message shown is ‘Duplicate key violation’. On selecting the next value of the serial key the value shown is different from the actual value. Ie the shown value is very much less than the number of records in the table. Why failover? Is the master dead? Are you syncing your sequences? Is the duplicate a result of trying to insert the primary key based on some sequence? Clearly some data is not fully synchronized when you did your failover. Also, there is a whole mailing list dedicated to slony replication. Take your question over there for best reach of people who can help you. -- 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] Copy From suggestion
With OpenOffice.org that 65K limit goes away as well... I don't know why it is still like that today for MS Office... It is almost 2011 and they still think 64K is enough? :-) -- Jorge Godoy jgo...@gmail.com On Mon, Dec 20, 2010 at 11:49, Mark Watson mark.wat...@jurisconcept.cawrote: Thanks, Adrian, I’ll try a windows compile of pgloader sometime during the holidays. It’s true that I already have a solution (export = 65000 row chunks, import into Excel, export via Excel puts quotes around the text columns), but something faster and more efficient would really help in this case. -Mark -- *De :* pgsql-general-ow...@postgresql.org [mailto: pgsql-general-ow...@postgresql.org] *De la part de* Adrian Klaver *Envoyé :* 18 décembre 2010 18:05 *À :* pgsql-general@postgresql.org *Cc :* Mark Watson *Objet :* Re: [GENERAL] Copy From suggestion On Friday 17 December 2010 7:46:12 am Mark Watson wrote: Hello all, Firstly, I apologise if this is not the correct list for this subject. Lately, I've been working on a data conversion, importing into Postgres using Copy From. The text file I'm copying from is produced from an ancient program and produces either a tab or semi-colon delimited file. One file contains about 1.8M rows and has a 'comments' column. The exporting program, which I am forced to use, does not surround this column with quotes and this column contains cr/lf characters, which I must deal with (and have dealt with) before I can import the file via Copy. Hence to my suggestion: I was envisioning a parameter DELIMITER_COUNT which, if one was 100% confident that all columns are accounted for in the input file, could be used to alleviate the need to deal with cr/lf's in varchar and text columns. i.e., if copy loaded a line with fewer delimiters than delimiter_count, the next line from the text file would be read and the assignment of columns would continue for the current row/column. Just curious as to the thoughts out there. Thanks to all for this excellent product, and a merry Christmas/holiday period to all. Mark Watson A suggestion,give pgloader a look; http://pgloader.projects.postgresql.org/ If I am following you it might already have the solution to the multi-line problem. In particular read the History section of the docs. Thanks, -- 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 -- No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1170 / Virus Database: 426/3325 - Release Date: 12/19/10
Re: [GENERAL] pg_restore 8.x to postgreSQL 9.x functions and triggers aren't created
On Mon, Dec 20, 2010 at 3:28 AM, Raimon Fernandez co...@montx.com wrote: when restoring the same file to any of our postgreSQL Servers 8.x we have no problems. which version of pg_dump/restore are you using? The one from 8.x or 9.0? Try using the ones from 9.0. -- 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] Postgres 9.0 Hiding CONTEXT string in Logs
No reply... I guess its not possible then to switch that off. Hi, I occasionally output information from my PL/Perl functions to show a progres or a debug info like this elog(NOTICE, Table some_table analyzed); which generated the NOTICE: Table some_table analyzed however since upgrading to version 9.0 I also get this annoying string CONTEXT: PL/Perl function func_some_fun Is there any way to suppress this as I don't really need it? Thanks Alex
Re: [GENERAL] Copy From suggestion
On Monday 20. December 2010 15.24.58 Jorge Godoy wrote: With OpenOffice.org that 65K limit goes away as well... I don't know why it is still like that today for MS Office... It is almost 2011 and they still think 64K is enough? :-) Maybe there's an uncrippled «Professional» or «Enterprise» version costing an arm and a leg? ;) regards, Leif B. Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What Programs Do You Use For PG?
I'm just wondering what programs you guys / girls are using PostgreSQL for. So far I've installed PG 9 on my Debian Linux server and manually created all my databases, schema's, and tables for my personal email / address book. It's very basic and small but I was wondering if you guys know of any programs that I can install that I could beef up my PG database with? Just looking for basic applications that use PG as a back-end so I can see how different programs are developed to create tables and schema's. I tried looking on line for an open source email address book that would use PostgreSQL as a back end but couldn't find one. Anyone have any recommendations? -- 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] Copy From suggestion
On Monday 20 December 2010 7:09:23 am Leif Biberg Kristensen wrote: On Monday 20. December 2010 15.24.58 Jorge Godoy wrote: With OpenOffice.org that 65K limit goes away as well... I don't know why it is still like that today for MS Office... It is almost 2011 and they still think 64K is enough? :-) Maybe there's an uncrippled «Professional» or «Enterprise» version costing an arm and a leg? ;) regards, Leif B. Kristensen FYI with Office 2007 that limit went to a little over 1 million rows. -- 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
[GENERAL] Role Membership
I was looking at my users and realized none of my users are members of
a specific group or role. Not sure if there's a difference between the
two (role / group) in PostgreSQL, is there?
easports=# \du
List of roles
Role name |Attributes | Member of
---+---+---
carlos | Superuser | {}
postgres | Superuser, Create role, Create DB | {}
Now my question is about the section 'Member of' and how this is
commonly utilized by most PG administrators. Is this the same thing as
explained here:
http://www.postgresql.org/docs/8.2/interactive/role-membership.html
If I am not mistaken I can simply create a role called 'accounting'
and add several users to the 'accounting' group rather than juggling
several dozen user grants, correct? If anyone can please tell me if
I'm hot or cold on this issue as well as anything you would think is
helpful for me to know that is not in the documentation that you
learned as a PG administrator.
Thanks so much!
--
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] Role Membership
On Monday 20 December 2010 7:27:19 am Carlos Mennens wrote:
I was looking at my users and realized none of my users are members of
a specific group or role. Not sure if there's a difference between the
two (role / group) in PostgreSQL, is there?
easports=# \du
List of roles
Role name |Attributes | Member of
---+---+---
carlos | Superuser | {}
postgres | Superuser, Create role, Create DB | {}
Now my question is about the section 'Member of' and how this is
commonly utilized by most PG administrators. Is this the same thing as
explained here:
http://www.postgresql.org/docs/8.2/interactive/role-membership.html
If I am not mistaken I can simply create a role called 'accounting'
and add several users to the 'accounting' group rather than juggling
several dozen user grants, correct? If anyone can please tell me if
I'm hot or cold on this issue as well as anything you would think is
helpful for me to know that is not in the documentation that you
learned as a PG administrator.
Thanks so much!
Roles = users/groups. In older versions there where users and groups, that has
been consolidated into the concept of a role. If it makes it easier I use the
concept of roles with login privileges as a users and roles without as groups.
You are on the right track.
--
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] Role Membership
On Mon, Dec 20, 2010 at 10:41 AM, Adrian Klaver adrian.kla...@gmail.com wrote: Roles = users/groups. In older versions there where users and groups, that has been consolidated into the concept of a role. If it makes it easier I use the concept of roles with login privileges as a users and roles without as groups. So I did a \dg a \du according to '\?' I can't see the difference between the two commands. One is listed as showing 'users' and the other for 'groups' but the output looks identical to me. Perhaps my database is not that robust yet. So if I wanted to create a group / role for accounting, would I simply just do: CREATE ROLE accounting; My question is I can't find: 1. How to view all previously created roles on my database. '\dg' doesn't show me the new role I created above. 2. How to see which 'users' are all members of 'accounting'? Would that be done simply with '\dg'? -- 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] INSERT INTO...RETURNING with partitioned table based on trigger function
On 2010-12-16, pgsql(dot)30(dot)miller_2555(at)spamgourmet(dot)com pgsql(dot)30(dot)miller_2555(at)spamgourmet(dot)com wrote: --0015174c1e4aaf077604977d7e62 Content-Type: text/plain; charset=ISO-8859-1 Hi - Issue: How to return a sequence value generated upon INSERT of records into a partitioned table using trigger functions (without having to insert into the child table directly). partitioning doesn't work with INSERT ... RETURNING ... and trigger based partitioning. use a rule instead have the rule FOR EACH ROW DO INSTEAD SELECT insertfunc(NEW) and have insertfunc do the insert and return the id column. for declaring the function the type of NEW is table_name%ROWTYPE Thanks. I had attempted to use rules prior to the trigger implementation, but opted for the trigger-based implementation due to easier maintenance (specific to this particular database, at least). 2) multiple instances of the application may be running, so generation of the sequence number in the application is not feasible (moreover, the application is multi-threaded and additional summary data insertions may occur between the insertion of summary data and detailed data in the two partitioned tables. another option is the application could call nextval itself or call lastval after the insert. both of these SQL functions are thread safe. This is a great idea, and the one I'll probably end up implementing. Many thanks for the suggestion! 3) is there a technical reason as to why the return values of trigger functions are ignored when TG_OP=AFTER for INSERT/ UPDATE operations, because you can't change history. I agree that handling the return value of a trigger function when TG_OP=AFTER in such a way that alters the database itself does not make sense on its face, though I think that allowing trigger-defined return values to pass back to the trigger caller (and so on trough the call stack back to the external application) is a reasonable behaviour.
Re: [GENERAL] Role Membership
On Mon, Dec 20, 2010 at 9:12 AM, Carlos Mennens
carlos.menn...@gmail.com wrote:
So if I wanted to create a group / role for accounting, would I simply just
do:
CREATE ROLE accounting;
My question is I can't find:
1. How to view all previously created roles on my database. '\dg'
doesn't show me the new role I created above.
Odd, mine does. Got a complete example of creating a role and not seeing it?
Here's mine:
smarlowe=# create role stans;
CREATE ROLE
smarlowe=# \dg
List of roles
Role name | Attributes | Member of
---+--+---
postgres | Superuser| {}
: Create role
: Create DB
smarlowe | Superuser| {}
: Create role
: Create DB
stans | Cannot login | {}
2. How to see which 'users' are all members of 'accounting'? Would
that be done simply with '\dg'?
Yeah.
--
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] Role Membership
On Mon, Dec 20, 2010 at 12:05 PM, Scott Marlowe scott.marl...@gmail.com wrote:
Odd, mine does. Got a complete example of creating a role and not seeing it?
Here's mine:
smarlowe=# create role stans;
CREATE ROLE
smarlowe=# \dg
List of roles
Role name | Attributes | Member of
---+--+---
postgres | Superuser | {}
: Create role
: Create DB
smarlowe | Superuser | {}
: Create role
: Create DB
stans | Cannot login | {}
I guess I am still confused by role / group user accounts. If you
create a role / group called 'finance', it then shows up as a user
when I do \dg? Then how do I make users a member of the 'finance' role
/ group if they're listed just like regular users are?
easports=# CREATE ROLE finance;
CREATE ROLE
easports=# \dg
List of roles
Role name |Attributes | Member of
---+---+---
cmennens | Superuser | {}
finance | Cannot login | {}
postgres | Superuser, Create role, Create DB | {}
From the above listing, I would expect 'finance' to not be listed with
my users since finance is a role / group, not a single user. I want to
make specific users members of 'finance'. Am I missing something or
just slow today?
2. How to see which 'users' are all members of 'accounting'? Would
that be done simply with '\dg'?
Yeah.
--
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] Role Membership
On 12/20/2010 08:12 AM, Carlos Mennens wrote: On Mon, Dec 20, 2010 at 10:41 AM, Adrian Klaveradrian.kla...@gmail.com wrote: Roles = users/groups. In older versions there where users and groups, that has been consolidated into the concept of a role. If it makes it easier I use the concept of roles with login privileges as a users and roles without as groups. So I did a \dg a \du according to '\?' I can't see the difference between the two commands. One is listed as showing 'users' and the other for 'groups' but the output looks identical to me. Perhaps my database is not that robust yet. There is no difference. Let me see if I can explain better. In older versions of Postgres(cannot remember when the switch was made) there where indeed USERS and GROUPS. They where distinct and different commands where used to create and view them. At some point the functions served by the them where all consolidated into the concept of a ROLE. For backwards comparability the terms USER and GROUP are still with us, as well as the creation and view commands. At this point though they point to ROLES. ROLES have the attributes of both USERS and GROUPS and this is where my suggestion above came from. When creating a ROLE with login privileges it is useful to think of that as a USER. A ROLE you create without login privileges can be thought of as a GROUP. These are only mental shortcuts and are not reflected in the \d commands. So if I wanted to create a group / role for accounting, would I simply just do: CREATE ROLE accounting; Yes My question is I can't find: 1. How to view all previously created roles on my database. '\dg' doesn't show me the new role I created above. Did the command above return successfully? 2. How to see which 'users' are all members of 'accounting'? Would that be done simply with '\dg'? Yes it would once you assign users to that ROLE. To really understand you need to go over the SQL commands CREATE ROLE GRANT and go through the below several times: http://www.postgresql.org/docs/9.0/interactive/user-manag.html I ended up setting up a test database and creating roles in it. I then worked through a variety of scenarios to see how the ROLE mechanism worked, especially the INHERIT attribute. -- 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] Postgres 9.0 Hiding CONTEXT string in Logs
On Mon, Dec 20, 2010 at 10:04 AM, Alex - ainto...@hotmail.com wrote: No reply... I guess its not possible then to switch that off. Hi, I occasionally output information from my PL/Perl functions to show a progres or a debug info like this elog(NOTICE, Table some_table analyzed); which generated the NOTICE: Table some_table analyzed however since upgrading to version 9.0 I also get this annoying string CONTEXT: PL/Perl function func_some_fun Is there any way to suppress this as I don't really need it? see: log_error_verbosity http://www.postgresql.org/docs/current/static/runtime-config-logging.html merlin -- 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] libpq ASYNC with PQgetResult and PQisBusy
Raimon Fernandez co...@montx.com writes:
Almost everything is working, and now I want to implememt the asynchronous
issue.
I send the SQL using the PQsendQuery, and my interface is not blocking, great.
Now, everytime I check fot the PQgetResult my interface gets blocked.
Well, yes. PQgetResult says wait for a result and return it.
So, now I'm using the PQisBusy to check if postgre is still busy and I can
safely call the PQgetResult wihtout blocking, or just wait *some time* before
sending a new PQisBusy.
Your proposed code is still a busy-wait loop. What you should be doing
is waiting for some data to arrive on the socket. Once you see
read-ready on the socket, call PQconsumeInput, then check PQisBusy to
see if the query is complete or not. If not, go back to waiting on the
socket. Typically you'd use select() or poll() to watch for both data
on libpq's socket and whatever other events your app is interested in.
here is my montxPG_isBusy
static long montxPG_isBusy()
{ int execStatus;
int consumeeVar;
consumeeVar = PQconsumeInput(gPGconn);
if (consumeeVar == 0) return (long) PGRES_FATAL_ERROR;
execStatus = PQisBusy(gPGconn);
return (long) execStatus;
}
This code seems a bit confused. PQisBusy returns a bool (1/0), not a
value of ExecStatusType.
regards, tom lane
--
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] Role Membership
On Mon, Dec 20, 2010 at 10:12 AM, Carlos Mennens
carlos.menn...@gmail.com wrote:
On Mon, Dec 20, 2010 at 12:05 PM, Scott Marlowe scott.marl...@gmail.com
wrote:
Odd, mine does. Got a complete example of creating a role and not seeing it?
Here's mine:
smarlowe=# create role stans;
CREATE ROLE
smarlowe=# \dg
List of roles
Role name | Attributes | Member of
---+--+---
postgres | Superuser | {}
: Create role
: Create DB
smarlowe | Superuser | {}
: Create role
: Create DB
stans | Cannot login | {}
I guess I am still confused by role / group user accounts. If you
No user, no group, they're al roles. Roles are both / either.
create a role / group called 'finance', it then shows up as a user
when I do \dg? Then how do I make users a member of the 'finance' role
Yep, it shows up as a ROLE.
/ group if they're listed just like regular users are?
You grant them that:
grant rolename to username;
Then you only ever have to grant / revoke a role to change
permissions, no need to do a million grants all over the place on each
table. Just grant it once to the role, grant the role to the user,
viola, you're done.
easports=# CREATE ROLE finance;
CREATE ROLE
easports=# \dg
List of roles
Role name | Attributes | Member of
---+---+---
cmennens | Superuser | {}
finance | Cannot login | {}
postgres | Superuser, Create role, Create DB | {}
From the above listing, I would expect 'finance' to not be listed with
my users since finance is a role / group, not a single user. I want to
make specific users members of 'finance'. Am I missing something or
just slow today?
2. How to see which 'users' are all members of 'accounting'? Would
that be done simply with '\dg'?
Yeah.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
To understand recursion, one must first understand recursion.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL Trusted Startup
Hello, I am investigating security requirements for configuring a PostgreSQL database on a Linux system. One of the security requirements our organization would like to implement is trusted startup, in that PostgreSQL would verify the authenticity of the binaries and configuration files before making the database available to users. This would enable the database to detect if the system has possibly been compromised. Since this is a Linux system, I could keep a list of known good MD5 checksums and compare the checksums prior to startup by editing the init script. The list would of course need to be updated any time I make a configuration change or apply a patch. Is there an alternative method of implementing such a requirement? Possibly one already incorporated into PostgreSQL? Thanks, Ken Buckler
Re: [GENERAL] Role Membership
On Mon, Dec 20, 2010 at 1:32 PM, Scott Marlowe scott.marl...@gmail.com wrote: No user, no group, they're al roles. Roles are both / either. Ah now I understand. Thank you! You grant them that: grant rolename to username; Then you only ever have to grant / revoke a role to change permissions, no need to do a million grants all over the place on each table. Just grant it once to the role, grant the role to the user, viola, you're done. OK I now understand: postgres=# \l List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+---+--+-+-+--- caldega | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | tiburon | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | ide | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | orlando | jmadeline | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | software | mwilshaw | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (8 rows) postgres=# ALTER DATABASE ide OWNER TO it; ALTER DATABASE postgres=# \l List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+---+--+-+-+--- caldega | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | tiburon | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | ide | it| UTF8 | en_US.UTF-8 | en_US.UTF-8 | orlando | jmadeline | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | software | mwilshaw | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres Thanks for helping me out! -Carlos -- 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] Role Membership
On Monday 20 December 2010 11:46:29 am Carlos Mennens wrote: On Mon, Dec 20, 2010 at 1:32 PM, Scott Marlowe scott.marl...@gmail.com wrote: No user, no group, they're al roles. Roles are both / either. Ah now I understand. Thank you! You grant them that: grant rolename to username; Then you only ever have to grant / revoke a role to change permissions, no need to do a million grants all over the place on each table. Just grant it once to the role, grant the role to the user, viola, you're done. OK I now understand: Now I don't:) What you show is changing the ownership of an object. I thought you where asking about how to add members to a role and the relationship between 'users' and 'groups'? What Scott showed was a good start, but I would suggest some more experimenting. ROLES are powerful but there is a learning curve, especially when you start using SET ROLE and SET SESSION AUTHORIZATION. Thanks for helping me out! -Carlos -- 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] PostgreSQL Trusted Startup
On Mon, Dec 20, 2010 at 12:12 PM, Kenneth Buckler kenneth.buck...@gmail.com wrote: Hello, I am investigating security requirements for configuring a PostgreSQL database on a Linux system. One of the security requirements our organization would like to implement is trusted startup, in that PostgreSQL would verify the authenticity of the binaries and configuration files before making the database available to users. This would enable the database to detect if the system has possibly been compromised. But, if the script is run on the same machine as postgresql is on, the scripts that check for changes could be compromised as well and then you'd never know. Since this is a Linux system, I could keep a list of known good MD5 checksums and compare the checksums prior to startup by editing the init script. The list would of course need to be updated any time I make a configuration change or apply a patch. Is there an alternative method of implementing such a requirement? Possibly one already incorporated into PostgreSQL? pgsql doesn't do any of that, but I'm sure you can roll your own so to speak. I would tend to write some kind of nagios plugin that could be called remotely that would notify you whenever it changes so you would know as soon as a change occurred rather than later when trying to restart the database during a midday outage while the boss screams get the system back up now! We're losing money! Generally, if the db's been compromised, someone's already gotten to an app server or two, and might be sniffing traffic anyway, so it's likely a lost cause by then. -- 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] PostgreSQL Trusted Startup
On 12/20/10 11:12 AM, Kenneth Buckler wrote: Hello, I am investigating security requirements for configuring a PostgreSQL database on a Linux system. One of the security requirements our organization would like to implement is trusted startup, in that PostgreSQL would verify the authenticity of the binaries and configuration files before making the database available to users. This would enable the database to detect if the system has possibly been compromised. Since this is a Linux system, I could keep a list of known good MD5 checksums and compare the checksums prior to startup by editing the init script. The list would of course need to be updated any time I make a configuration change or apply a patch. Is there an alternative method of implementing such a requirement? Possibly one already incorporated into PostgreSQL? I would look into selinux. lock it down with this, and it will be much harder to compromise. -- 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] PostgreSQL Trusted Startup
On Mon, Dec 20, 2010 at 1:43 PM, John R Pierce pie...@hogranch.com wrote: I would look into selinux. lock it down with this, and it will be much harder to compromise. I agree. By the time you've got compromised binaries / config files on the system, you've already lost. -- 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] libpq ASYNC with PQgetResult and PQisBusy
On 20dic, 2010, at 18:48 , Tom Lane wrote:
So, now I'm using the PQisBusy to check if postgre is still busy and I can
safely call the PQgetResult wihtout blocking, or just wait *some time*
before sending a new PQisBusy.
Your proposed code is still a busy-wait loop.
This is how are made all the examples I've found.
Last year I was implementing the FE/BE protocol v3 and there I was using what
you proposed, a TCP/Socket with events, no loops and no waits, just events.
What you should be doing is waiting for some data to arrive on the socket.
where I have to wait, in a function inside my plugin or from the framework that
uses my plugin ?
Once you see
read-ready on the socket, call PQconsumeInput, then check PQisBusy to
see if the query is complete or not. If not, go back to waiting on the
socket. Typically you'd use select() or poll() to watch for both data
on libpq's socket and whatever other events your app is interested in.
Here is what I've found:
extern int PQsocket(const PGconn *conn);
There are some examples in the postgreSQL documentation:
/*
* Sleep untilsomething happens on the connection. We use select(2)
* to wait for input, but you could also use poll() or similar
* facilities.
*/
int sock;
fd_set input_mask;
sock = PQsocket(conn);
if (sock 0) break; /* shouldn’t happen */
FD_ZERO(input_mask);
FD_SET(sock, input_mask);
if (select(sock + 1, input_mask, NULL, NULL, NULL) 0) {
fprintf(stderr, select() failed: %s\n, strerror(errno));
exit_nicely(conn);
}
/* Now check for input */
PQconsumeInput(conn);
while ((notify = PQnotifies(conn)) != NULL) {
fprintf(stderr, ASYNC NOTIFY of ’%s’ received from backend pid %d\n,
notify-relname, notify-be_pid);
PQfreemem(notify);
}
The select(2) that says that are using for wait is this line ? if (select(sock
+ 1, input_mask, NULL, NULL, NULL) 0) {
I can't see where is 'sleeping'
and the approach you are refering, is the only way to non-block the plugin
calls and postgreSQL ?
here is my montxPG_isBusy
static long montxPG_isBusy()
{int execStatus;
int consumeeVar;
consumeeVar = PQconsumeInput(gPGconn);
if (consumeeVar == 0) return (long) PGRES_FATAL_ERROR;
execStatus = PQisBusy(gPGconn);
return (long) execStatus;
}
This code seems a bit confused. PQisBusy returns a bool (1/0), not a
value of ExecStatusType.
yes, here the execStatus is the name of the int, and yes, I know, a bad name ...
thanks again,
regards,
r.
--
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] PostgreSQL Trusted Startup
On Mon, Dec 20, 2010 at 3:31 PM, Scott Marlowe scott.marl...@gmail.com wrote: But, if the script is run on the same machine as postgresql is on, the scripts that check for changes could be compromised as well and then you'd never know. I agree, if the system has been compromised, nothing will prevent the scripts from being compromised. Hence why I am looking for alternatives. I consider the md5 script approach a poor approach, but it does meet the letter of the requirements set forth by the organization. Is there an alternative method of implementing such a requirement? Possibly one already incorporated into PostgreSQL? pgsql doesn't do any of that, but I'm sure you can roll your own so to speak. I would tend to write some kind of nagios plugin that could be called remotely that would notify you whenever it changes so you would know as soon as a change occurred rather than later when trying to restart the database during a midday outage while the boss screams get the system back up now! We're losing money! Thanks for clarifying that for me. Part of the requirement I'm working with requires vendor documentation stating if such a feature exists. Since there is no vendor documentation, they'll have to settle for my own documentation, backed up with a mailing list post. Writing my own plugin/module hasn't been ruled out. I wanted to make sure that I'm not re-inventing the wheel. In any approach to this, I will be including an override which will allow PostgreSQL to start despite failing the trusted files check. Generally, if the db's been compromised, someone's already gotten to an app server or two, and might be sniffing traffic anyway, so it's likely a lost cause by then. Agreed. Unfortunately, I've been given specific requirements and I am obligated to fulfill those requirements, even if I don't agree those requirements are necessary. This is all in addition to an extensive OS lockdown script, as well as additional lockdown requirements for the database. I appreciate the help. I believe this is an excellent starting point to try and address this requirement. Ken -- 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] libpq ASYNC with PQgetResult and PQisBusy
On 20 Dec 2010, at 21:49, Raimon Fernandez wrote:
The select(2) that says that are using for wait is this line ? if
(select(sock + 1, input_mask, NULL, NULL, NULL) 0) {
I can't see where is 'sleeping'
See man 2 select.
If you're on an OS without manual pages (Windows is pretty much the only
exception I know of), there are plenty of online versions of man pages
available. I'd suggest looking at the FreeBSD ones, as in my experience they
tend to be pretty keen on proper documentation.
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,4d0fed20802654673819298!
--
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] Postgres forums ... take 2
On 29 November 2010 04:31, Elliot Chance elliotcha...@gmail.com wrote: Discussion will continue on the wiki page: http://wiki.postgresql.org/wiki/Forums_at_postgresql.com.au Even though the discussion has moved I still intent to keep pushing the issues to a resolution. The wiki is not shelving the idea for another time. I know this topic has gone quiet, I still think it's worth investing time and resources in. I don't expect any progress to be made until the new year now, but I hope we can continue this after the Christmas period is over. And nice work collating the discussions so far onto the wiki. That should make it easier for everyone to keep up with developments :) -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Setting connection parameters via some kind of client configuration file @ the command line
Hello all- I am using psql from the command line in an Ubuntu environment, and I'd like to setup (if possible) some sort of client configuration for myself that sets some environment variables, mainly client_min_messages. Is there a way to do this? Thanks. -- Wells Oliver Developer, Baseball Systems San Diego Padres | 100 Park Boulevard | San Diego CA 92101
Re: [GENERAL] Setting connection parameters via some kind of client configuration file @ the command line
On Monday 20 December 2010 4:42:57 pm Wells Oliver wrote: Hello all- I am using psql from the command line in an Ubuntu environment, and I'd like to setup (if possible) some sort of client configuration for myself that sets some environment variables, mainly client_min_messages. Is there a way to do this? Thanks. -- Wells Oliver Developer, Baseball Systems San Diego Padres | 100 Park Boulevard | San Diego CA 92101 From here: http://www.postgresql.org/docs/9.0/interactive/app-psql.html Unless it is passed an -X or -c option, psql attempts to read and execute commands from the system-wide psqlrc file and the user's ~/.psqlrc file before starting up. (On Windows, the user's startup file is named %APPDATA%\postgresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information on setting up the system-wide file. It could be used to set up the client or the server to taste (using the \set and SET commands). -- 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
[GENERAL] Problems With -9.0.1
I built and installed -9.0.1 on my Slackware-13.1 server and it ran for a while. Now, without warning, one of my applications dependent upon postgres won't run. While trying to restart postgres I learned the .pid file did not exist so I removed /tmp/.s.PGSQL* and tried to restart postgres. Here's what I see: postg...@salmo:$ postgres -D /usr/local/pgsql/data [1] 16044 postg...@salmo:/var/log/httpd$ LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory WARNING: could not open directory /share/timezonesets: No such file or directory HINT: This may indicate an incomplete PostgreSQL installation, or that the file /bin/postgres has been moved away from its proper location. FATAL: invalid value for parameter timezone_abbreviations: Default The files and directories are in /usr/local/pgsql/share: postg...@salmo:$ ls /usr/local/pgsql/share/ conversion_create.sql postgres.bkisql_features.txt doc/postgres.descriptionsystem_views.sql information_schema.sql postgres.shdescription timezone/ man/postgresql.conf.sample timezonesets/ pg_hba.conf.sample psqlrc.sample tsearch_data/ pg_ident.conf.samplerecovery.conf.sampleunknown.pltcl pg_service.conf.sample snowball_create.sql What do I do to overcome this impasse and get postgres running again? Rich -- 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] Problems With -9.0.1
On Monday 20 December 2010 5:09:24 pm Rich Shepard wrote: I built and installed -9.0.1 on my Slackware-13.1 server and it ran for a while. Now, without warning, one of my applications dependent upon postgres won't run. While trying to restart postgres I learned the .pid file did not exist so I removed /tmp/.s.PGSQL* and tried to restart postgres. Here's what I see: postg...@salmo:$ postgres -D /usr/local/pgsql/data [1] 16044 postg...@salmo:/var/log/httpd$ LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory WARNING: could not open directory /share/timezonesets: No such file or directory HINT: This may indicate an incomplete PostgreSQL installation, or that the file /bin/postgres has been moved away from its proper location. FATAL: invalid value for parameter timezone_abbreviations: Default The files and directories are in /usr/local/pgsql/share: postg...@salmo:$ ls /usr/local/pgsql/share/ conversion_create.sql postgres.bkisql_features.txt doc/postgres.descriptionsystem_views.sql information_schema.sql postgres.shdescription timezone/ man/postgresql.conf.sample timezonesets/ pg_hba.conf.sample psqlrc.sample tsearch_data/ pg_ident.conf.samplerecovery.conf.sampleunknown.pltcl pg_service.conf.sample snowball_create.sql What do I do to overcome this impasse and get postgres running again? Rich As I remember it there was more than one version of Postgres on this machine. Are you sure you are using the correct postgres binary? While I am it is there a reason you are not using the system start scripts or pg_ctl:)? -- 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] Problems With -9.0.1
On 12/20/10 5:09 PM, Rich Shepard wrote: I built and installed -9.0.1 on my Slackware-13.1 server and it ran for a while. Now, without warning, one of my applications dependent upon postgres won't run. While trying to restart postgres I learned the .pid file did not exist so I removed /tmp/.s.PGSQL* and tried to restart postgres. Here's what I see: postg...@salmo:$ postgres -D /usr/local/pgsql/data [1] 16044 postg...@salmo:/var/log/httpd$ LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory WARNING: could not open directory /share/timezonesets: No such file or directory HINT: This may indicate an incomplete PostgreSQL installation, or that the file /bin/postgres has been moved away from its proper location. FATAL: invalid value for parameter timezone_abbreviations: Default The files and directories are in /usr/local/pgsql/share: if you built and installed postgres in /usr/local/pgsql, then all those errors should have referred to /usr/local/pgsql/share/timezone is there a different postgres binary in /bin/postgres ? execute... postg...@salmo:$ /usr/local/pgsql/bin/pg_config and reply to the list with the output... also, postg...@salmo:$ which postgres -- 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] Setting connection parameters via some kind of client configuration file @ the command line
On Mon, Dec 20, 2010 at 5:42 PM, Wells Oliver woli...@padres.com wrote: Hello all- I am using psql from the command line in an Ubuntu environment, and I’d like to setup (if possible) some sort of client configuration for myself that sets some environment variables, mainly client_min_messages. Is there a way to do this? alter user smarlowe set client_min_messages='debug'; -- 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] PostgreSQL Trusted Startup
On 12/21/2010 06:12 AM, Kenneth Buckler wrote: Hello, I am investigating security requirements for configuring a PostgreSQL database on a Linux system. One of the security requirements our organization would like to implement is trusted startup, in that PostgreSQL would verify the authenticity of the binaries and configuration files before making the database available to users. Do you have a trusted boot path from BIOS to bootloader to kernel to init core userspace, where everything is digitally signed (by you or someone else) and verified before execution? Do you disable kernel module loading? If not, you're wasting your time, because a compromise by malicious kernel module, modified init, modified md5 command, etc will render your precautions totally pointless. If your BIOS can't verify the bootloader, which is likely on an x86 / x64 system, then you can still get some protection by signing your kernels and using a bootloader that checks signatures. If someone messes with the bootloader you lose, but it'll help protect you against obvious automated attacks. You might be able to use the Trusted Platform Module (TPM) on your machine to get a fully verified chain of trust, though, by using Trusted GRUB. http://trousers.sourceforge.net/grub.html If you can reasonably trust that the kernel you loaded is OK, you can have it verify signatures on binaries before executing them. There was a DigSig project for that (http://disec.sourceforge.net/) but it seems to have stopped recently. I'm not sure if there's any replacement. Without kernel-level signature verification, all you can really do is have a custom initrd/initramfs (signed and verified by grub during boot) that checks the signatures on init, md5, gpg, libc, etc etc (any binary root runs, including scripts) before switching to the real root FS during boot. Then you can have your Pg startup scripts (which you signed on a separate, trusted machine) verify GnuPG signatures of the Pg binaries before execution. All in all, it's a painful, clumsy way to do things, and AFAIK there's little support in mainline Linux systems for trusted boot and trusted-binary systems. You might find out more with a search for linux trusted computing, linux trusted boot, linux tpm, linux signed binaries, etc. Personally, I'd be using existing system- and network-level intrusion detection tools like tripwire and snort to try to spot intrusion if and when it happens. I'm not confident that a chain-of-trust approach is workable on Linux systems at present, though I'd love to be proved wrong by being pointed at existing support I've missed. -- Craig Ringer -- 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] Problems With -9.0.1
On Mon, 20 Dec 2010, Adrian Klaver wrote: As I remember it there was more than one version of Postgres on this machine. Are you sure you are using the correct postgres binary? While I am it is there a reason you are not using the system start scripts or pg_ctl:)? Adrian, There _was_ a library not removed when I removed -8.3.3, but that's been gone a while. This had been running until yesterday when I could not log in to my accounting software that uses postgres as the back end. The reason I don't use /etc/rc.d/rc.postgresql is that it doesn't work. While it supposedly su's to user postgres, it actually does not do so. Ergo, nothing starts. Looking for running postgres processes I find none: [r...@salmo ~]# ps ax | grep postgres 17168 pts/2S+ 0:00 grep postgres And when I try to start the postmaster as user postgres it fails (as reported in my original message): postg...@salmo:/home/rshepard$ LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory LOG: could not open directory /share/timezone: No such file or directory WARNING: could not open directory /share/timezonesets: No such file or directory HINT: This may indicate an incomplete PostgreSQL installation, or that the file /bin/postgres has been moved away from its proper location. FATAL: invalid value for parameter timezone_abbreviations: Default [1]+ Exit 1 postgres -D /usr/local/pgsql/data This has not happened before. Rich -- 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] Problems With -9.0.1 [SOLVED]
On Mon, 20 Dec 2010, John R Pierce wrote: if you built and installed postgres in /usr/local/pgsql, then all those errors should have referred to /usr/local/pgsql/share/timezone John, I assumed the reference to share/ was relative to /usr/local/pgsql/ is there a different postgres binary in /bin/postgres ? Aha! I had a copy of /usr/local/pgsql/bin/postgres in /bin rather than a soft link. I just fixed that and postgres starts. Still cannot log in to my accounting software, even after restarting httpd. That brings me back to my original problem: server error that won't allow me to log in. There's a problem with the perl DBD::Pg module that prevents it from loading. Sigh. I'll focus on that now. Thanks, Rich -- 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] Problems With -9.0.1
On Monday 20 December 2010 7:12:52 pm Rich Shepard wrote: On Mon, 20 Dec 2010, Adrian Klaver wrote: As I remember it there was more than one version of Postgres on this machine. Are you sure you are using the correct postgres binary? While I am it is there a reason you are not using the system start scripts or pg_ctl:)? Adrian, There _was_ a library not removed when I removed -8.3.3, but that's been gone a while. This had been running until yesterday when I could not log in to my accounting software that uses postgres as the back end. The reason I don't use /etc/rc.d/rc.postgresql is that it doesn't work. While it supposedly su's to user postgres, it actually does not do so. Ergo, nothing starts. Well that would be a problem. Still, using pg_ctl would be an improvement. From the docs (http://www.postgresql.org/docs/9.0/interactive/app-pg-ctl.html): pg_ctl is a utility for initializing a PostgreSQL database cluster, starting, stopping, or restarting the PostgreSQL backend server (postgres), or displaying the status of a running server. Although the server can be started manually, pg_ctl encapsulates tasks such as redirecting log output and properly detaching from the terminal and process group. It also provides convenient options for controlled shutdown. Looking for running postgres processes I find none: [r...@salmo ~]# ps ax | grep postgres 17168 pts/2S+ 0:00 grep postgres The issue is not so much another running postgres but more than one in your PATH and you picking up the wrong one. A locate or find would help here. Also per Johns post you should run: /usr/local/pgsql/bin/pg_config That may help untangle this. And when I try to start the postmaster as user postgres it fails (as Rich -- 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] Problems With -9.0.1
On Mon, 20 Dec 2010, Adrian Klaver wrote: Well that would be a problem. Still, using pg_ctl would be an improvement. From the docs (http://www.postgresql.org/docs/9.0/interactive/app-pg-ctl.html): pg_ctl is a utility for initializing a PostgreSQL database cluster, starting, stopping, or restarting the PostgreSQL backend server (postgres), or displaying the status of a running server. Although the server can be started manually, pg_ctl encapsulates tasks such as redirecting log output and properly detaching from the terminal and process group. It also provides convenient options for controlled shutdown. The script stops the running postgres. Since the only time I should need to re-start it is after a re-boot (a distribution upgrade with a new kernel), starting manually is not a big deal: postg...@salmo:/home/rshepard$ postgres -D /usr/local/pgsql/data [1] 17318 postg...@salmo:/home/rshepard$ LOG: database system was shut down at 2010-12-20 19:04:58 PST LOG: database system is ready to accept connections LOG: autovacuum launcher started [rshep...@salmo ~]$ ps ax | grep postgres 17318 pts/1S 0:00 postgres -D /usr/local/pgsql/data 17320 ?Ss 0:00 postgres: writer process 17321 ?Ss 0:00 postgres: wal writer process 17322 ?Ss 0:00 postgres: autovacuum launcher process 17323 ?Ss 0:00 postgres: stats collector process 17405 pts/1S+ 0:00 grep postgres All processes running. Thanks, Adrian, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Understanding PG9.0 streaming replication feature
Hi, I am having some trouble trying to figure out how to configure this particular scenario.. I have a pair of pg servers that I want to put in a Master/Standby configuration. Currently a script dumps the master db every hour, copies it to the standby, restores, and restarts the server. The aim is to replace the dumps/restores with streaming replication and ensure the standby is always up to date. In this case writes are infrequent, but reads are constant, and I only need high availability for reads. I would ideally like both master and standby to be available simultaneously to allow load-balancing. My confusion seems to be around the fact I don't need failover - my applications will detect a master down and immediately start using the standby, so there is no need to allow writes on the standby, they will just wait for the master to be available again - I am not sure what the minimum config needed for this scenario is.. cheers, Ben
Re: [GENERAL] What Programs Do You Use For PG?
You can have a look at my project on sourceforge: http://sourceforge.net/projects/proghelp builds applications with PG as a backend automatically. It uses a modified create table sql grammar as an input. 1. It automatically generates stored procedures to insert and retrieve data 2. a database api layer to access the data in C++ 3. a user interface using the Wt C++ library i. search keys marked in the input file automatically become search keys in the user interface which correctly invoke the generated stored procedures. ii. Foreign key references automatically become dialog boxes. iii. Any search keys in the referenced tables become will become search keys in the dialog box - again with correct invocations. iv. Any tables marked as references multi in the input file automatically become master details tables - using a tabview widget for details tables. The details tables automatically use the primary key from the master table to do their inserts/retrieval of data from the system. 4. Random data for testing. 5. scripts to create tables in topological order (when there are foreign key references the referenced tables are output first, for the master/details tables the master tables are output first), and drop tables and stored procedures ( when you want to prevent clutter). All the scripts are collected in a unified script generator to save you the trouble of running them individually. 6. Automatic creation of authorization and authentication modules - with fine grained control on view, add, edit and view summary. The input file is about 200 lines, the generated code is about 15,000 lines of c++, and about 12,000 lines of stored procedures. Whats not yet implemented 1. Paging of records. 2. Automatic edit for records. 3. Currently the search is properly implemented for varchar fields, need to fix it for dates and int/float data 4. A better random data generator. 5. god knows what else You can see a sample application developed using the code generator here: http://173.230.133.34:8080/ User logins and passwords are below. user_login_code | user_login_name | user_password | employee_code -+-+---+--- 1 | zenond | zenond123 | 1 2 | atuld | atul123 | 2 3 | michaeld| michael123| 3 4 | nxd | nxd123| 4 5 | veerad | veerad123 | 5 6 | chanchud| chanchud123 | 6 7 | wilburd | wilburd123| 7 8 | abhishekc | abhishekc123 | 8 If it crashes - please mail me and I will restart it. Kind Regards, Neil On Mon, Dec 20, 2010 at 8:39 PM, Carlos Mennens carlos.menn...@gmail.comwrote: I'm just wondering what programs you guys / girls are using PostgreSQL for. So far I've installed PG 9 on my Debian Linux server and manually created all my databases, schema's, and tables for my personal email / address book. It's very basic and small but I was wondering if you guys know of any programs that I can install that I could beef up my PG database with? Just looking for basic applications that use PG as a back-end so I can see how different programs are developed to create tables and schema's. I tried looking on line for an open source email address book that would use PostgreSQL as a back end but couldn't find one. Anyone have any recommendations? -- 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] libpq ASYNC with PQgetResult and PQisBusy
On 21dic, 2010, at 00:56 , Alban Hertroys wrote:
On 20 Dec 2010, at 21:49, Raimon Fernandez wrote:
The select(2) that says that are using for wait is this line ? if
(select(sock + 1, input_mask, NULL, NULL, NULL) 0) {
I can't see where is 'sleeping'
See man 2 select.
If you're on an OS without manual pages (Windows is pretty much the only
exception I know of), there are plenty of online versions of man pages
available. I'd suggest looking at the FreeBSD ones, as in my experience they
tend to be pretty keen on proper documentation.
thanks, I didn't know this was from the OS level.
I'm reading the documentation now ...
regards,
r.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
