Re: [ADMIN] pg_hba.conf file review
Hi dan IF possible i suggest to put a restriction from where your users come with netwotk and mask. I think that your solution is to complicate and suggest that all database is owned by postgres with strong password and give your user full grant for a single database. so you haven't to manage a multiples users-passwords files What do you think about that? Fouad -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part de Dan MacNeil Envoyé : jeudi 21 novembre 2002 19:17 À : [EMAIL PROTECTED] Objet : [ADMIN] pg_hba.conf file review It would be great if somebody could review our pg_hha.conf file below to see if it does what we want it to, no more and no less. The goals for our pg_hba.conf file are: 1) In an emergency allow somebody with operatating system root privs access to all the databases with full privs. 2) Allow local shell users to connect to databases named after themselves wit no authentification. a) As we run suexec, this helps novice cgi scripters avoid putting passwords in the script. b) We also set the environment variable PGDATABASE to LOGNAME so people can just run psql w/o prarams and get to their database 3) Allow remote users to connect to a particular database with a password 4) Allow superuser (postgres) to attach to any database 5) Deny everything else. A related question: Right now we give a user full privledges to the database, named after the user by 1) creating the user with CREATEDB 2) Give the user the right to connect to the database dummy 3) creating a database as that user 4) taking away the users rights to CREATEDB Is there a less kludgy way? GRANTing ALL on a database? Changing ownership of a database? ## # line below removes almost all security any local user can connect as # any user including the postgres database superuser UNCOMMENT WITH # CAUTION #local all trust # kludge to allow users to connect to server to create database # w/o giving everyone access to postgres account local dummy passworddummy_users.list # Users can locally connect to databases named after their OS login # names For example, felicia can connect to a database called felicia # but not to a database called john. Requires apt-get install ident2 # configure #connectdatabaseIP numbermask auth meth auth arg local sameuserident sameuser hostsameuser127.0.0.1 255.0.0.0 ident sameuser # If above method does not apply (felicia tries to connect # to database john) method below will be tried and user # will be asked for a password that doesn't exist and # there will be FAILURE (a good thing) #connect database security password file local utec md5 utec_local_users.list # Give admin types ability to connect to ALL databases locally # Get list of admins from FILE: admin_users.list in DIR $PGDATA # # Admin user must have password in database system catalog # table pg_shadow. See docs for ALTER USER to set password # entry in pg_shadow # # Only super user postgres has automatic privs (grants) to # objects in database. #connectdatabasesecurity method file with users local all password admin_users.list #LOCAL ACCESS WITH PASSWORD # ---if user is in password file for database # ---and has password in file (bad) #or in pg_shadow table via ALTER USER #REMOTE_ACCESS # It is possible to allow access only from certain IP numbers #connect database IP nummask to xor host acarvalh 0.0.0.0 0.0.0.0 md5 acarvalh_users.list host dsiegal 0.0.0.0 0.0.0.0 md5 dsiegal_users.list host omacneil 0.0.0.0 0.0.0.0 md5 omacneil_users.list host felicia 0.0.0.0 0.0.0.0 md5 felicia_users.list host john 0.0.0.0 0.0.0.0 md5 john_users.list host calendarlctc 0.0.0.0 0.0.0.0 md5 calendarlctc_users.list host utec 0.0.0.0 0.0.0.0 md5 utec_remote_users.list # reject all connections from all hosts not granted above hostall 0.0.0.0 0.0.0.0 reject ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[ADMIN] Database backup and restore
Am I missing something? There seems to be no way to dump all databases and then restore them (as you'd have to do on a version change) if the databases contain blobs. pg_dump supports dumping of blobs with the -b -Ft switch, but using pg_dumpall with those switches doesn't seem to work. And in any case, pg_restore seems to be missing its equivalent pg_restoreall. Other than (painfully) manually pg_dumping and pg_restoring each database, how else could it be done? I would search the mailing lists but the site's search engine is down. Thanks, Colin -Colin StearmanPresidentAshdown Technologies119 E. Main St.Milford, MA 01757 USATel: +1 508-478-1234Fax: +1 508-478-1244www.ashdowntech.com
[ADMIN] Inquiry From Form [pgsql]
i am install psql and createuser db, how i can see the user howmany i can creaded?? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Inquiry From Form [pgsql]
i had postgresql installed with linux. now iam unable to work with postgresql.i couls create a database, but when i issue pg_ctl -D database location command, it is giving the following error: \can\'t lock the file /tmp/.s.PGSQL.5432.lock:permission denied.\ when issue any command, it is giving user \myname\ not in system catalog,undefined. please help me in this regard. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Database backup and restore
Am I missing something? There seems to be no way to dump all databases and then restore them (as you'd have to do on a version change) if the databases contain blobs. pg_dump supports dumping of blobs with the -b -Ft switch, but using pg_dumpall with those switches doesn't seem to work. And in any case, pg_restore seems to be missing its equivalent pg_restoreall. Other than (painfully) manually pg_dumping and pg_restoring each database, how else could it be done? I would search the mailing lists but the site's search engine is down. i wrote a script in perl which dumps all the DBs but template* i can mail it to you if you wish ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] ram drive
My client would like to test using a ram drive to hold some of our postgre files. Does anybody have any suggestions for how to go about doing this? Details are below: http://www.3dretreat.com/reviews/rocketdrive/ This is a review that explains the benefits of a ram drive: it's a pci card that holds several sticks of SDRAM, but reports it as another filesystem to linux. So the idea is that we could put several postgre tables on this card, and we will hopefully see a huge performance increase by being able to access these files from the ram drive instead of a hard drive. Our /usr/local/pgsql/data dir is nearly 4GB in size, but our current ramdrive only holds 2GB of ram, so we can't put the whole data dir on the ramdrive. Which files would be ideal for loading onto the ram drive? Are there specific files that contain the table indices? This ram drive is nascent ground for us, so all ideas are welcome. Thanks, Tim -- Dyrect Media Group P.O. Box 486 6000 Goodrich Rd Clarence Center, NY 14032-0486 OFFICE: 716-504-1141 ext 208 CELL: 716-510-2451 AIM: somecallmetim100 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Database backup and restore
On 22 Nov 2002 at 19:07, dima wrote: i wrote a script in perl which dumps all the DBs but template* i can mail it to you if you wish Why not post it to the list? That way it's available for everyone. I'm guessing it'll be less than 2K or so... -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Database backup and restore
In bash, I use: == script fragment == ~!/bin/bash $BackupDir=whereever $LogFile=whichever for db_name in $(/usr/local/pgsql/bin/psql -U postgres -d template1 -n -t -c select datname from pg_database where datistemplate='f';); do /usr/local/pgsql/bin/pg_dump -U postgres -Fc -Z1 -b $db_name -f $BackupDir/$db_name.pgdump 2 $LogFile.err \ echo $(date +%c): Successfully dumped database $db_name $LogFile done == script fragment == Cheers, Murthy -Original Message- From: dima [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 11:07 To: Colin Stearman Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] Database backup and restore Am I missing something? There seems to be no way to dump all databases and then restore them (as you'd have to do on a version change) if the databases contain blobs. pg_dump supports dumping of blobs with the -b -Ft switch, but using pg_dumpall with those switches doesn't seem to work. And in any case, pg_restore seems to be missing its equivalent pg_restoreall. Other than (painfully) manually pg_dumping and pg_restoring each database, how else could it be done? I would search the mailing lists but the site's search engine is down. i wrote a script in perl which dumps all the DBs but template* i can mail it to you if you wish ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Database backup and restore
Dan, On first glance at dima's script it does not seem to deal with blobs, although could easily me made to do so. Also it does not address automated reloading. In light of the fact that blobs must be output by -Ft or -Fc in pg_dump, which are tar and custom respectively, the result of an entire db set dump would be one file of this type per database. Maybe the name could be used in a similar reverse-direction script to send each to pg_restore to reload things. I'll be working on it some more and will share what I come up with. It seems a glaring omission that it is impossible to upgrade PostgreSQL across major versions if your database(s) contain blobs (at least, not without a lot of work). Colin - Original Message - From: Dan Langille [EMAIL PROTECTED] To: dima [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, November 22, 2002 2:05 PM Subject: Re: [ADMIN] Database backup and restore On 22 Nov 2002 at 19:07, dima wrote: i wrote a script in perl which dumps all the DBs but template* i can mail it to you if you wish Why not post it to the list? That way it's available for everyone. I'm guessing it'll be less than 2K or so... -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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] Database backup and restore
On 22 Nov 2002 at 14:41, Colin Stearman wrote: Dan, On first glance at dima's script it does not seem to deal with blobs, although could easily me made to do so. I think that would be a very good addition to the PostgreSQL toolkit. FWIW, I do not use blobs and consequently do not think of them when backups are required. I'll be working on it some more and will share what I come up with. I'm sure it will be added to the contrib directory. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[ADMIN] PGAdmin schema tracking a bit off...
Hi All, Don't know if anyone else has had this problem (I'm hoping someone else has had this problem:) If I create a second or third schema (PG7.3) in PgAdminII and then create a view or table in the new schema and attempt to view the data the following message results: 22/11/2002 07:51:05 - Error in pgAdmin II:frmMain.mnuPopupViewData_Click: -2147467259 - ERROR: Relation test does not exist Looking at the logs it has executed: 22/11/2002 07:51:05 - SQL (fsd): SELECT count(*) AS count FROM test When is should have executed: 22/11/2002 07:51:04 - SQL (fsd): SELECT count(*) AS rows FROM sch_parking.test .. which works fine. If I use the generic 'public' schema everything is fine... (but ofcourse why would I want to use schema's and put everthing into public? :) I have enabled and disabled the auto row count on tables and views to no success although I don't know how much effect this has (haven't bothered looking at the source code yet...) This happens regardless of whether it's a view or table. Is this a common problem until 7.3 is at release stage? Or does the issue lie with PGAdminII? (You guys have done a great job of this by the way!) I'm using PGAdminII 1.4 and PostgreSQL 7.3 RC1. Cheers, Jason ---(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] crash help, pgsql 7.2.1 on RH7.3
okay, argh, after messing around with /etc/security/limits.conf, it would have been nice to know that limits.conf doesn't change the default ulimit rather the limits of user ulimit changes! mean to say, pam_limits.so and limits.conf do not change the default ulimit, just the bounds, so then the user can ulimit -c unlimited. i expect regular user to never be able to increase their ulimits - call me old fasioned... what's next, regular user negative renice?!? anyways... but, uh, what am i going to do with a core file? i would need a non-stripped postgres binary first, right? i checked out the cwd in /proc, it is /var/lib/pgsql (actally i symlinked it into another fs) which is postgres:postgres mode 700. - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Tim Lynch [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 20, 2002 8:31 PM Subject: Re: [ADMIN] crash help, pgsql 7.2.1 on RH7.3 : I said: : Tim Lynch [EMAIL PROTECTED] writes: : i don't see a core file. : : Check that you are starting the postmaster with ulimit -c unlimited; : this is not the default on most Linuxen, so you may have to add that to : the start script. Also note that the postmaster never does a chdir, : so if it drops core it will be in the same directory the start script : was running in. : : Drat, I forgot to mention an important corollary: make sure the : postmaster is started in a directory that's writable by the postgres : user, else you'll get no corefile. : : (For completeness I'll mention here that when individual backends dump : core, it's in the $PGDATA/base/nnn/ directory of the database they're : connected to. So you can easily distinguish a postmaster core from : a backend core, just by where it was dropped.) : : 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] DB management tool for linux
http://ems-hitech.com/pgsqlutils/download.phtml http://gborg.postgresql.org/browse.php?81 C. Benjamin Stewart wrote, On 11/21/2002 11:40 PM: Greetings, After a few suggestions for a good DB managmen application for linux. I have found a few and they are a little average. Appears that there are some good applicaitons in windows, but not linux. Thanks Ben ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] DB management tool for linux
Greetings, After a few suggestions for a good DB managmen application for linux. I have found a few and they are a little average. Appears that there are some good applicaitons in windows, but not linux. Thanks Ben ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] pg_hba.conf file
It would be great if somebody could review our pg_hha.conf file below to see if it does what we want it to, no more and no less. The goals for our pg_hba.conf file are: 1) In an emergency allow somebody with operatating system root privs access to all the databases with full privs. 2) Allow local shell usersto connect to databases named after themselves wit no authentification. a) As we run suexec, this helps novice cgi scripters avoid putting passwords in the script. b) We also set the environment variablePGDATABASE to LOGNAME so people can just run psql w/o prarams and get to their database 3) Allow remote users to connect to a particular database with a password 4) Allow superuser (postgres) to attach to any database 5) Deny everything else. A related question: Right now we give a user full privledges to the database, named after the user by 1) creating the user with CREATEDB 2) Give the user the right to connect to the database dummy 3) creating a database as that user 4) taking away the users rights to CREATEDB Is there a less kludgy way? GRANTing ALL on a database? Changing ownership of a database? ## # line below removes almost all security any local user can connect as# any user including the postgres database superuser UNCOMMENT WITH# CAUTION #local all trust # kludge to allow users to connect to server to create database# w/o giving everyone access to postgres accountlocal dummy password dummy_users.list # Users can locally connect to databases named after their OS login# names For example, felicia can connect to a database called felicia# but not to a database called john. Requires apt-get install ident2# configure #connect database IP number mask auth meth auth arglocal sameuser ident sameuserhost sameuser 127.0.0.1 255.0.0.0 ident sameuser # If above method does not apply (felicia tries to connect# to database john) method below will be tried and user# will be asked for a password that doesn't exist and# there will be FAILURE (a good thing) #connect database security password filelocal utec md5 utec_local_users.list # Give admin types ability to connect to ALL databases locally# Get list of admins from FILE: admin_users.list in DIR $PGDATA## Admin user must have password in database system catalog# table pg_shadow. See docs for ALTER USER to set password# entry in pg_shadow## Only super user postgres has automatic privs (grants) to# objects in database. #connect database security method file with userslocal all password admin_users.list #LOCAL ACCESS WITH PASSWORD# ---if user is in password file for database# ---and has password in file (bad)# or in pg_shadow table via ALTER USER #REMOTE_ACCESS # It is possible to allow access only from certain IP numbers #connect database IP num mask to xor host acarvalh 0.0.0.0 0.0.0.0md5acarvalh_users.listhost dsiegal 0.0.0.0 0.0.0.0md5dsiegal_users.listhost omacneil 0.0.0.0 0.0.0.0md5omacneil_users.listhost felicia 0.0.0.0 0.0.0.0md5felicia_users.listhost john 0.0.0.0 0.0.0.0md5john_users.listhost calendarlctc 0.0.0.0 0.0.0.0md5 calendarlctc_users.list host utec 0.0.0.0 0.0.0.0md5utec_remote_users.list # reject all connections from all hosts not granted abovehost all 0.0.0.0 0.0.0.0 reject
[ADMIN] db restore
Hi guys I had just recreate database that deleted some useful data in one of teh table. There's no backup dump badly. But as i know there a transaction log taht will log everything from beginning, anyone know how to do a rollback or roll forward?
[ADMIN] sh: `-c' requires an argument
I ran into this problem when running almost any command using psql, createdb or even trying to goto the shell using \! The problem is that /bin/sh points to /bin/bash and my bash version is: $ bash -version GNU bash, version 1.14.7(1) Here are the other errors: $ createdb foo PQexec() -- Request was sent to backend, but backend closed the channel before responding. This probably means the backend terminated abnormally before or while processing the request. createdb: database creation failed on foo. (in the log file it has the sh: `-c' requires an argument error) Has anyone run into this problem? Or could give me tips to fix it? Thanks Girish
[ADMIN] sh: `-c' requires an argument
I ran into this problem when running almost any command using psql, createdb or even trying to goto the shell using \! The problem is that /bin/sh points to /bin/bash and my bash version is: $ bash -version GNU bash, version 1.14.7(1) Here are the other errors: $ createdb foo PQexec() -- Request was sent to backend, but backend closed the channel before responding. This probably means the backend terminated abnormally before or while processing the request. createdb: database creation failed on foo. (in the log file it has the sh: `-c' requires an argument error) Has anyone run into this problem? Or could give me tips to fix it? Thanks Girish
Re: [ADMIN] H/W RAID 5 on slower disks versus no raid on faster HDDs
raid 0 (striping) spreads the load over multiple spindels, the same way raid 5 does. but raid 5 always needs to calculate parity and write that to it's parity drive. RPM isn't that critical, a lot depends on the machine, the processor and the memory (and the spped with which the processor can get to the memory). I have recently tested a lot of systems with some database benchmarks we wrote here at work. We're not running Postgres here at work, sorry, these benchmarks are of no use to Postgres ... We we found is that a lot depends on motherboard design, not so much on drive speed. We got to stages where we allocated 1.8 GB of RAM to shared memory for the database server process, resulting in the entire database being sucked into memory. When doing reads, 100% of the data is coming out the that menory, and drive speed becomes irrelevant. From tests I did with Postgres on my boxes at home, I can say: The more shared memory you can throw at the server process, the better. Under MacOS X I wasn't able to allocate more than 3 MB, Under Linux, I can allocate anything I want to, so I usually start up the server with 256 MB. The difference? A process which takes 4 minutes under Linux, takes 6 hours under MacOS - same hardware, same drives, different memory settings. Best regards, Chris On Thursday 21 November 2002 12:02, you wrote: Thanks Chris, does raid0 enhances both read/write both? does rpms not matter that much? regds mallah. On Thursday 21 November 2002 22:27, you wrote: RAID 5 gives you pretty bad performance, a slowdown of about 50%. For pure performance, I'd use the 3 18 GB drives with RAID 0. If you need fault tolerance, you could use RAID 0+1 or 1+0 but you'd need an even number of drives for that, of which half would become 'usable space'. Best regards, Chris On Thursday 21 November 2002 11:45, you wrote: Hi folks, I have two options: 3*18 GB 10,000 RPM Ultra160 Dual Channel SCSI controller + H/W Raid 5 and 2*36 GB 15,000 RPM Ultra320 Dual Channel SCSI and no RAID Does anyone opinions *performance wise* the pros and cons of above two options. please take in consideration in latter case its higher RPM and better SCSI interface. Regds Mallah. -- Network Grunt and Bit Pusher extraordinaire ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Inquiry From Form [pgsql]
On Mon, 18 Nov 2002, Chris Schneider wrote: Hi Chris, PostgreSQL has something very similar to what Oracle does, although still different: pg_databases - Stores general information for each database. pg_shadow - Stores information on the users, such as access levels, passwords, etc. pg_indexes - stores a list of all the active indecies, you can also use this to get a listing of most of the other pg_tables... and a bunch more, but these are the ones that are used the most.. Where can I find a listing/explanation of all the system views for the PostgreSQL db. In other words, on Oracle I would query DBA_TABLES for all table definitions, or DBA_USERS for all db users. Would like to know the PostgreSQL equivalents. Thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Chris Bowlby, - [EMAIL PROTECTED] www.pgsql.com 1-902-542-0713 - ---(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] crash help, pgsql 7.2.1 on RH7.3
On Thursday 21 November 2002 20:05, Tim Lynch wrote: increase their ulimits - call me old fasioned... what's next, regular user negative renice?!? anyways... Actually yes. but, uh, what am i going to do with a core file? i would need a non-stripped postgres binary first, right? If you have the RPM, you have no debugging symbols. You can rebuild it with debugging -- the PGDG RPMset's can have debugging symbols enabled with a simple macro define close to the top of the spec file. i checked out the cwd in /proc, it is /var/lib/pgsql (actally i symlinked it into another fs) which is postgres:postgres mode 700. That's the standard place for PGDATA in Red Hat. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] How are postgreSQL database files structured?
Hey folks: I am mounting the learning curve from MySQL to postgreSQL. It seemed so straight forward before. On my Win98 box, everything was clearly named as I had designated in the c:/mysql/data directory. On the Debian server, the /var/lib/mysql directory is similarly and intuitively laid out in an understandable way. But when I take a look at /usr/local/pgsql/data/base, I am baffled by its contents. I hear that the directory at: /usr/local/pgsql/data/base/1 is the template1 database. But I do not know where the template0 database is. And I certainly do not understand how testtest gets named 16555, much less what the numbered directories and files subordinate to that directory have to do with anything real in the world. Can someone please refer me to a document (preferably off the net, as my book buying budget is non-existent, these days) which will explain it all for me? I would certainly appreciate that. Thanks. -- Hugh Esco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
