Re: [ADMIN] RPMs for Red Hat 8.0
I read your response and grinned sheepishly - I should have known that because I've run across the same situation before. :) I've installed all the RPMs and everything seems to be working fine. Thanks. Lamar Owen wrote: On Friday 18 October 2002 11:02 am, jburski wrote: I've just installed Red Hat 8.0. The distro include PostgreSQL version 7.2.2, but only the *base* parts - no "devel", "odbc", "perl", etc. RPMs. They should be on one of the three CD's. You won't find the postgresql-tk or postgresql-test ones, though. Barring that, if you have all the necessary development languages, tools, and libraries installed, you can grab the source RPM (on one of the two source CD's) and rpm --rebuild it, looking in /usr/src/redhat/RPMS/i386 for it. Optimizing the compile for anything but i386 doesn't produce significantly better results in my experience. I welcome benchmark results to prove me wrong :-). -- John Burski @home S.I.M.U. (Well, sometimes I am) ... and still searching for new cheese ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] security rights per database DDL/DML connect
I've been searching the web for a solution on this but no luck . I'm administering a webserver w/ PHP apps on . I had an idea to create an application database and give access to certain users to maintain the data within the database tables , but also I'd like to make sure only certain users are able to perform DDL commands. Or simply to revoke access for connecting to this db. I haven't found any clues. , any help is appreciated . many thanks, Gabor Faludi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] security rights per database DDL/DML connect
On Tue, Oct 22, 2002 at 21:01:59 +0200, Gabor Faludi <[EMAIL PROTECTED]> wrote: > I've been searching the web for a solution on this but no luck . > > I'm administering a webserver w/ PHP apps on . I had an idea to create an > application database and give access to certain users to maintain the data > within the database tables , but also I'd like to make sure only certain > users are able to perform DDL commands. Or simply to revoke access for > connecting to this db. In 7.3 you can get a pretty good handle on this. The database owner can restrict who can create schemas and schema owners can control who can create objects in that schema. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Newbie question about pg_xlog
"Stein, Jochen" <[EMAIL PROTECTED]> writes: > I just set up a little test database and i asked myself why this > pg_xlog-directory has already 33MBs and is still growing. Because the xlog is allocated in units of 16Mb. If you're talking about a low-traffic database, the xlog probably won't get larger than a couple of files. (You can tweak the WAL configuration parameters to determine this, see the Admin Guide.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Upgrade to new version
On Thu, Oct 10, 2002 at 09:46:14AM +0200, Reiner Dassing wrote: > Hello all! > > I would like to ask what would be the best procedure to upgrade to a > new version of PostgreSQL in a production system when there is a 'initdb' > necessary? > > Situation: > We are running PostgreSQL in a production system. There are about 1000 > selects and about 100 inserts per minute. If you're upgrading to 7.2.3 from 7.2.2, no initdb is required. But anyway, here's a general strategy: If you are using replication (and if not, this might be a good time to start looking at it), you can use it to minimise downtime. Here's how: 1. Add an additional replication target. Put up the new back end on another port, and start replicating into it. 2. When you are totally caught up (i.e. replication is merely sending in its most recent changes in each cycle), shut off the applications. 3. Reconfigure your client applications to use the new back end. 4. Check that replication has really caught up. Shut off the old back end (for safety). 5. Re-start your applications. I've done this. It works. You still have to shut off for a brief period, but it's minutes, and not hours. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Using oid with RServ w/ Postgresql 7.2
On Fri, Oct 18, 2002 at 09:44:46AM -0400, Tom Lane wrote: > Will LaShell <[EMAIL PROTECTED]> writes: > > Does anyone know why rserve doesn't support/use multi-field keys for the > > replication id? > > Well, the one in contrib/ is only a prototype. The version that > PostgreSQL Inc sells commercially might have support for multi-field > keys by now. You'd have to ask them though ... It doesn't. The best answer really is to use a sequence and a key field just for this. (In fact, the rserv key field _must_ be unique, or the whole thing breaks horribly. Don't ask me how I know ;-) I understand that dbmirror uses the primary key on each table, but it can't handle multi-column primary keys either, so it's no help. For what it's worth, the additional overhead on our tables hasn't been enough for me to regret the implementation of rserv. We use the commercial version, by the way. The prototype version isn't terribly fast. Maybe dbmirror is a better free choice, but I don't know, as I haven't tested it. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] Quickie about Database locations
Title: Gareth Stationery Hey all! I'm muddled up about something. I'm creating a new database, and absolute paths are disallowed. Therefore I su to postgres ( the owner of the service ) and run the following: export PGDATA2=/bla/bla/bla initlocation PGDATA2 initlocation returns fine - telling me to create the database as per usual. I therefore do: createdb v2 -D 'PGDATA2' But I get: ERROR: Postmaster environment variable 'PGDATA2' not setcreatedb: database creation failed I echo it - to test it - it works. I put it in the bash_profile of the user, restart postgres... nothing... Any ideas? Thanks everyone Gareth KirwanProgramming & Development,Thermeon Europe Ltd,[EMAIL PROTECTED] Tel: +44 (0) 1293 864 303Thermeon Europe e-Card: gbjk
Re: [ADMIN] Quickie about Database locations
Title: Gareth Stationery Quick response too: Shortly after sending the email I tried exactly what I said, with the following alteration: I put the lines in the .bash_profile first, then initlocation'd then I exited and restarted postgres, then I su'd back in - and tried the create - and bingo - it worked. Sorry if I wasted anyone's time :-) Regards Gareth Kirwan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gareth KirwanSent: 22 October 2002 16:14To: [EMAIL PROTECTED]Subject: [ADMIN] Quickie about Database locations Hey all! I'm muddled up about something. I'm creating a new database, and absolute paths are disallowed. Therefore I su to postgres ( the owner of the service ) and run the following: export PGDATA2=/bla/bla/bla initlocation PGDATA2 initlocation returns fine - telling me to create the database as per usual. I therefore do: createdb v2 -D 'PGDATA2' But I get: ERROR: Postmaster environment variable 'PGDATA2' not setcreatedb: database creation failed I echo it - to test it - it works. I put it in the bash_profile of the user, restart postgres... nothing... Any ideas? Thanks everyone Gareth KirwanProgramming & Development,Thermeon Europe Ltd,[EMAIL PROTECTED] Tel: +44 (0) 1293 864 303Thermeon Europe e-Card: gbjk
Re: [ADMIN] Quickie about Database locations
Title: Gareth Stationery Hey all! I'm muddled up about something. I'm creating a new database, and absolute paths are disallowed. Therefore I su to postgres ( the owner of the service ) and run the following: export PGDATA2=/bla/bla/bla initlocation PGDATA2 initlocation returns fine - telling me to create the database as per usual. I therefore do: createdb v2 -D 'PGDATA2' But I get: ERROR: Postmaster environment variable 'PGDATA2' not setcreatedb: database creation failed I echo it - to test it - it works. I put it in the bash_profile of the user, restart postgres... nothing... Any ideas export PGDATA2=/bla/bla/bla ... how about restart postmaster ?
[ADMIN] 7.2 date/time format function problems
I have just installed redhat 8.0. It comes with postgresql rpms for 7.2.2. I have been trying to get an application I have written to work, but my queries fail whenever I have queries that use internal date/time formatting functions. example: SELECT a.assignmentid AS "id", u.lastname || ', ' || u.firstname || ' ' || u.middleint AS "assigned", j.name AS "job_name", extract(DOW FROM TIMESTAMP TIMESTAMP(a.startdate)) AS "dow", TO_CHAR(TO_TIMESTAMP(a.starttime, 'HH:MI:SS'), 'HH12:MI am') AS "starttime", TO_CHAR(TO_TIMESTAMP(a.stoptime, 'HH:MI:SS'), 'HH12:MI am') AS "stoptime", ((extract(HOUR FROM TIME (a.stoptime - a.starttime)) + (extract(MINUTE FROM TIME (a.stoptime - a.starttime))/60)) - a.break) AS "hrs", a.break AS "break" FROM assignment a LEFT JOIN users u USING(userid), schedule s, job j, account ac, location l, groups g WHERE s.scheduleid = 1 AND s.scheduleid = a.scheduleid AND s.accountid = 3 AND s.accountid = ac.accountid AND s.locationid = 1 AND s.locationid = l.locationid AND s.groupid = g.groupid AND s.scheduleid = s.scheduleid AND a.jobid = j.jobid ORDER BY j.name ASC, a.starttime ASC, u.lastname ASC; ERROR: parser: parse error at or near "TIMESTAMP" The problem areas are the timestamp() and extract(hour from time) functions. If anyone would be so kind as to help me with this issue, it would be greatly appreciated. I don't know if I have to enable something for these functions to work or if the format changed for 7.2.2. I checked the documentation and it was exactly like 7.1. Thanks for your help, Nicholas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Setting DateStyle
I'm trying to set my datestyle automatically in postgresql.conf this way: datestyle = 'iso, us' But Postgresql won't start when I do this. I get the following message in my logfile: FATAL 1: 'datestyle' is not a valid option name >From other examples of postgresql.conf I've looked at, this should work. What's going on? -- Jeff Self Information Technology Analyst City of Newport News Personnel Department Suite 200 700 Town Center Drive Newport News, VA 23606 757-926-1810 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Setting DateStyle
Jeff Self <[EMAIL PROTECTED]> writes: > I'm trying to set my datestyle automatically in postgresql.conf this > way: > datestyle = 'iso, us' That will work as of PG 7.3, but not in prior releases. (Datestyle and a couple of other slightly-weird SET variables didn't get folded into GUC until 7.3.) The workaround in old releases is to set PGDATESTYLE in the postmaster's environment, instead. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Drop and reload table to clear corruption?
We have a few tables that seem to have suffered some kind of corruption in their TOAST files. Symptoms include: + Runaway CPU usage during VACUUM ANALYZE (load average spikes of 20x normal for 10-15 minutes at a time) and more rarely during other operations + Recurring messages of "ERROR: Parent tuple was not found" during VACUUM FULL We have upgraded from 7.2.1 to 7.2.3 without apparent effect. Now what I would like to try is: 1. pg_dump the table 2. truncate the table 3. VACUUM FULL (is this necessary?) 4. reload from pg_dump file However, due to the very large number of foreign key constraints into this table, I really don't want to drop the table, nor do I want to trigger cascading deletes or updates. Can I use the same snippet of code generated by pg_dump to disable the triggers? Are there any other precautions I should take? Please CC me on any responses, as I read this list via NNTP, and news.postgresql.org seems to be on the blink this week. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Drop and reload table to clear corruption?
[EMAIL PROTECTED] (Jeff Boes) writes:
> We have a few tables that seem to have suffered some kind of
> corruption in their TOAST files. Symptoms include:
> + Runaway CPU usage during VACUUM ANALYZE (load average
>spikes of 20x normal for 10-15 minutes at a time) and
>more rarely during other operations
This seems odd. Can you attach to one of the runaway backends with a
debugger and get a stack trace? That might give some clue what the
problem is.
> + Recurring messages of "ERROR: Parent tuple was not found"
>during VACUUM FULL
The cause of this (or a cause of this, anyway) is known and fixed for
7.3. If you like you could try the attached back-patch for 7.2.3,
which fixes the most common case. (Plug: this patch is straight out of
the RHDB sources; if you are on a Red Hat platform I'd suggest trying
7.2.3-RH as soon as it's out.)
If that doesn't seem to help then you can go on with plan B:
> what I would like to try is:
> 1. pg_dump the table
> 2. truncate the table
> 3. VACUUM FULL (is this necessary?)
No, not if you truncated the table.
> 4. reload from pg_dump file
This should work to remove any data corruption in the table, assuming
you are able to get a clean dump. (You want a data-only dump of
course.)
> Can I use the same snippet of code generated by pg_dump to disable the
> triggers?
Should work. I think pg_dump may provide that for free in a data-only
dump, anyway.
BTW, there is an oversight in 7.2's TRUNCATE code: it only truncates the
given relation and not the TOAST table for same. But fortunately it
also fails to check just what you're truncating, so you can manually
issue a TRUNCATE against the TOAST table after truncating the main
table. (But I think you might have to run a standalone postgres with
-O -P to be allowed to do the latter.)
regards, tom lane
*** src/backend/commands/vacuum.c.orig Mon Sep 30 15:45:57 2002
--- src/backend/commands/vacuum.c Sat Oct 12 14:21:29 2002
***
*** 187,192
--- 187,196
if (IsTransactionBlock())
elog(ERROR, "%s cannot run inside a BEGIN/END block", stmttype);
+ /* Running VACUUM from a function would free the function context */
+ if (!MemoryContextContains(QueryContext, vacstmt))
+ elog(ERROR, "%s cannot be executed from a function", stmttype);
+
/*
* Send info about dead objects to the statistics collector
*/
***
*** 1320,1326
usable_free_size = 0;
}
! if (usable_free_size > 0 && num_vtlinks > 0)
{
qsort((char *) vtlinks, num_vtlinks, sizeof(VTupleLinkData),
vac_cmp_vtlinks);
--- 1324,1331
usable_free_size = 0;
}
! /* don't bother to save vtlinks if we will not call repair_frag */
! if (fraged_pages->num_pages > 0 && num_vtlinks > 0)
{
qsort((char *) vtlinks, num_vtlinks, sizeof(VTupleLinkData),
vac_cmp_vtlinks);
***
*** 1602,1608
*/
if ((tuple.t_data->t_infomask & HEAP_UPDATED &&
!TransactionIdPrecedes(tuple.t_data->t_xmin, OldestXmin)) ||
! (!(tuple.t_data->t_infomask & HEAP_XMAX_INVALID) &&
!(ItemPointerEquals(&(tuple.t_self),
&(tuple.t_data->t_ctid)
{
--- 1607,1614
*/
if ((tuple.t_data->t_infomask & HEAP_UPDATED &&
!TransactionIdPrecedes(tuple.t_data->t_xmin, OldestXmin)) ||
! (!(tuple.t_data->t_infomask & (HEAP_XMAX_INVALID |
!
HEAP_MARKED_FOR_UPDATE)) &&
!(ItemPointerEquals(&(tuple.t_self),
&(tuple.t_data->t_ctid)
{
***
*** 1633,1639
* If this tuple is in the begin/middle of the chain
then
* we have to move to the end of chain.
*/
! while (!(tp.t_data->t_infomask & HEAP_XMAX_INVALID) &&
!(ItemPointerEquals(&(tp.t_self),
&(tp.t_data->t_ctid
{
--- 1639,1646
* If this tuple is in the begin/middle of the chain
then
* we have to move to the end of chain.
*/
!
Re: [ADMIN] Drop and reload table to clear corruption?
On Tue, 2002-10-22 at 17:02, Tom Lane wrote: > [EMAIL PROTECTED] (Jeff Boes) writes: > > We have a few tables that seem to have suffered some kind of > > corruption in their TOAST files. Symptoms include: > > > + Runaway CPU usage during VACUUM ANALYZE (load average > >spikes of 20x normal for 10-15 minutes at a time) and > >more rarely during other operations > > This seems odd. Can you attach to one of the runaway backends with a > debugger and get a stack trace? That might give some clue what the > problem is. No; we can't do *anything* while this is happening. The load average (on a Linux system) runs up to 30+ within a few minutes. By the time we notice it happening, no command prompts are responsive. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] security rights per database DDL/DML connect
On Tue, 22 Oct 2002, Bruno Wolff III wrote: > In 7.3 you can get a pretty good handle on this. The database owner can > restrict who can create schemas and schema owners can control who can > create objects in that schema. Will I be able to give people the right to execute a function, but not view it? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] security rights per database DDL/DML connect
Dan Langille <[EMAIL PROTECTED]> writes: > Will I be able to give people the right to execute a function, but not > view it? Write it in C. Short of that, you're asking for unenforceable restrictions on the ability to read pg_proc. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] security rights per database DDL/DML connect
On Wed, 23 Oct 2002, Tom Lane wrote: > Dan Langille <[EMAIL PROTECTED]> writes: > > Will I be able to give people the right to execute a function, but not > > view it? > > Write it in C. Short of that, you're asking for unenforceable > restrictions on the ability to read pg_proc. I like that approach. Given that the existing functions are written in plpgsql, is there a good guide to converting to C? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Security question : Database access control
Is there any way to prevent superuser to acces the database ? I mean something like "GRANT / REVOKE CONNECT" MECHANISM I have no idea how to prevent root from access data in one of this ways : root @ linux:~#su - postgres postgres @ linux:/usr/local/pgsql/bin$pg_dump or edit pg_hba.conf # Allow any user on the local system to connect to any # database under any username, but only via an IP connection: host all 127.0.0.1 255.255.255.255 trust # The same, over Unix-socket connections: local all trustor my nightmare a cygwin on Win 98 everybody can can access everything :-
Re: [ADMIN] Security question : Database access control
edit *pg_hba.conf * # Allow any user on the local system to connect to any # database under any username, but only via an IP connection: host all 127.0.0.1 255.255.255.255trust # The same, over Unix-socket connections: localall trust what about reading pg_hba.conf comments? localall md5 or *my nightmare *a cygwin on Win 98 everybody can can access everything :- /me shrugs i don't use cygwin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Security question : Database access control
On Tue, Oct 22, 2002 at 17:05:38 +0200, Igor Georgiev <[EMAIL PROTECTED]> wrote: > Is there any way to prevent superuser to acces the database ? > I mean something like "GRANT / REVOKE CONNECT" MECHANISM > > I have no idea how to prevent root from access data in one of this ways : > root @ linux:~#su - postgres > postgres @ linux:/usr/local/pgsql/bin$pg_dump > or > edit pg_hba.conf > # Allow any user on the local system to connect to any > # database under any username, but only via an IP connection: > host all 127.0.0.1 255.255.255.255trust > # The same, over Unix-socket connections: > localall trust > or my nightmare a cygwin on Win 98 everybody can can access everything :- They can just read the raw database files as well. You have to be able to trust whoever has root access to the system, as well as anyone who has physical access to the system. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Security question : Database access control
> > edit *pg_hba.conf *> > # Allow any user on the local system to connect to any> > # database under any username, but only via an IP connection:> > host all 127.0.0.1 255.255.255.255 trust > > # The same, over Unix-socket connections:> > local all trust > what about reading pg_hba.conf comments?> local all md5> Ok, but my question actually isn't about pg_hba.conf comments, i read enough but what will stop root from adding this lines or doing su - postgres ??
Re: [ADMIN] Security question : Database access control
On Tue, 22 Oct 2002, Igor Georgiev wrote: > > > edit *pg_hba.conf * > > > # Allow any user on the local system to connect to any > > > # database under any username, but only via an IP connection: > > > host all 127.0.0.1 255.255.255.255trust > > > # The same, over Unix-socket connections: > > > localall trust > > what about reading pg_hba.conf comments? > >localall md5 > > > > Ok, but my question actually isn't about pg_hba.conf comments, i read enough > but what will stop root from adding this lines or doing su - postgres ?? Not much really. But given that they have access to the raw data files, preventing them access to the server doesn't gain you that much if they really want to get the data. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Security question : Database access control
> They can just read the raw database files as well. wow I'm not sure how about this edit pg_hba.conf # Allow any user on the local system to connect to any # database under any username local all trust su - posgres psql test -U dba or pg_dump test > You have to be able to trust whoever has root access to the system, as well as anyone who has> physical access to the system.
Re: [ADMIN] Security question : Database access control
"Igor Georgiev" <[EMAIL PROTECTED]> writes: > Ok, but my question actually isn't about pg_hba.conf comments, i read enough > but what will stop root from adding this lines or doing su - postgres ?? As somebody already pointed out, you *must* trust the people with root access to your machine; there is not anything you can do to defend yourself against them. If you can't trust the admins on the machine you're using, better get your own machine. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] [HACKERS] Security question : Database access control
> Next your going to ask what will stop root from stopping your> PostgreSQL, compiling a second copy with authentication disabled and> using your data directory as it's source :) He he i'm enough paranoic :)) > If you want to prevent root from accomplishing these things, you're> going to have to look to your kernel for help. The kernel must prevent> root from changing users, starting / stopping applications, or touching> certain filesystems.> > PostgreSQL will let you put a password on the data. But that only works> if they actually try to use PostgreSQL to get at the data. use PostgreSQL to get at the data - Yeah this will be enough i want just only REVOKE CONNECT PRIVILEGES on database
