Re: [GENERAL] 1-Click installer problems
The 8.4.2 documentation says: The default user name is your Unix user name, as is the default database name. Not so much. My one-click installer creates a user 'postgres' who becomes the default user name...as well as the owner of the data file. Is postgres arguing with itself here? Or at least in its one-click incarnation on the Mac. In Nikhil's world, I would say that the clients are pretty carefully tightened down in terms of privileges. And apparently Vista has enabled more tightening down. John On Apr 2, 2010, at 6:43 AM, Craig Ringer wrote: I log in as an Administrator-enabled user, but have UAC turned on. This means that in fact I'm using non-admin rights unless/until I accept a UAC prompt. -- 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] 1-Click installer problems
John Gage wrote: The 8.4.2 documentation says: The default user name is your Unix user name, as is the default database name. when you as a user connect to the database server the commands like psql, pg_dump, etc all use your unix username as the default for the database username, and your username as teh default for the database name, unless you specify a different user and/or database on hte command line. -- 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] 1-Click installer problems
Then I don't understand why the installer doesn't do the same thing. Or, in the alternative, why it doesn't ask you what you want these parameters to be. I would say that, typically, someone installing postgres does it, conceivably, as root or, more likely, as a user. What he or she doesn't do is install it as user 'postgres'. Yet, that is what the one-click installer does. I do not believe that this is intuitive. What is more, gratuitiously adding a user to the system doesn't seem to make a whole lot of sense. In addition, all other one-click installations on the Mac either don't ask for root privileges, because they don't need them, or ask for them, but still install under the current user. Some installations will even ask whether you want the application usable by all users of the machine or just you. But none, repeat none, create a new user. What is more, through standard unix commands such as who or cat / etc/passwd, I cannot find the user 'postgres' on my machine...even though he is the owner of the Postgres data files...on my machine. There's the rub. 'postgres' owns files...my files...on my machine, yet he is not on my machine. Not good. I should add that I am an accolyte of Postgres and am only raising this (possible) issue in the most positive spirit I am capable of. In addition, I think that the people on this list are superb, and the responses are unbelievably helpful and accurate. John On Apr 2, 2010, at 8:29 AM, John R Pierce wrote: John Gage wrote: The 8.4.2 documentation says: The default user name is your Unix user name, as is the default database name. when you as a user connect to the database server the commands like psql, pg_dump, etc all use your unix username as the default for the database username, and your username as teh default for the database name, unless you specify a different user and/or database on hte command line. -- 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] 1-Click installer problems
There is a CLI option --serviceaccount username which a user can use to make any user the owner of postgres service and data files. Also, if you choose 'postgres' as the service account and the 'postgres' user doesn't exist. The installer will create postgres as a 'locked' user account. Thats the reason you dont see 'postgres' listed as any other normal user. These steps were taken to enhance the security of the data folder. Again, anytime a user is free to use any account as the service account and not use 'postgres'. On 4/2/10 12:37 PM, John Gage wrote: Then I don't understand why the installer doesn't do the same thing. Or, in the alternative, why it doesn't ask you what you want these parameters to be. I would say that, typically, someone installing postgres does it, conceivably, as root or, more likely, as a user. What he or she doesn't do is install it as user 'postgres'. Yet, that is what the one-click installer does. I do not believe that this is intuitive. What is more, gratuitiously adding a user to the system doesn't seem to make a whole lot of sense. In addition, all other one-click installations on the Mac either don't ask for root privileges, because they don't need them, or ask for them, but still install under the current user. Some installations will even ask whether you want the application usable by all users of the machine or just you. But none, repeat none, create a new user. What is more, through standard unix commands such as who or cat /etc/passwd, I cannot find the user 'postgres' on my machine...even though he is the owner of the Postgres data files...on my machine. There's the rub. 'postgres' owns files...my files...on my machine, yet he is not on my machine. Not good. I should add that I am an accolyte of Postgres and am only raising this (possible) issue in the most positive spirit I am capable of. In addition, I think that the people on this list are superb, and the responses are unbelievably helpful and accurate. John On Apr 2, 2010, at 8:29 AM, John R Pierce wrote: John Gage wrote: The 8.4.2 documentation says: The default user name is your Unix user name, as is the default database name. when you as a user connect to the database server the commands like psql, pg_dump, etc all use your unix username as the default for the database username, and your username as teh default for the database name, unless you specify a different user and/or database on hte command line. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company.
Re: [GENERAL] 1-Click installer problems
John Gage wrote: Then I don't understand why the installer doesn't do the same thing. Or, in the alternative, why it doesn't ask you what you want these parameters to be. I would say that, typically, someone installing postgres does it, conceivably, as root or, more likely, as a user. What he or she doesn't do is install it as user 'postgres'. but, thats exactly how most all data base servers operate. the server daemon run as their own private user. Oracle runs under whatever DBA account you configure it to run as (usually the user 'oracle'). Microsoft installs SQL Server's services with its own user account. Apache HTTP is generally run as http or apache or webuser on unix systems, not as one of the regular interactive users. etc etc. Yet, that is what the one-click installer does. I do not believe that this is intuitive. What is more, gratuitiously adding a user to the system doesn't seem to make a whole lot of sense. maybe the documentation needs some more explanations, then. In addition, all other one-click installations on the Mac either don't ask for root privileges, because they don't need them, or ask for them, but still install under the current user. Some installations will even ask whether you want the application usable by all users of the machine or just you. so on a mac, any server daemons you install run with your user credentials? really? But none, repeat none, create a new user. What is more, through standard unix commands such as who or cat /etc/passwd, I cannot find the user 'postgres' on my machine...even though he is the owner of the Postgres data files...on my machine. There's the rub. 'postgres' owns files...my files...on my machine, yet he is not on my machine. Not good. thats not even possible unless the macos doesn't use /etc/passwd as its user database.I dunno much about macosx, but everything I hear about it sounds like they took unix and twisted it all around. -- 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] 1-Click installer problems
I lied. The Unix id command produces: JohnGage:~ johngage$ id postgres uid=502(postgres) gid=1(daemon) groups=1(daemon) The one-click installer should be very clear about all this. I think we are very close to Steve Jobs Chain of Pain here. And, once again, I am absolutely dedicated to Postgres. John On Apr 2, 2010, at 8:29 AM, John R Pierce wrote: John Gage wrote: The 8.4.2 documentation says: The default user name is your Unix user name, as is the default database name. when you as a user connect to the database server the commands like psql, pg_dump, etc all use your unix username as the default for the database username, and your username as teh default for the database name, unless you specify a different user and/or database on hte command line. -- 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] 1-Click installer problems
If I do cat /etc/passwd, I get the following, which does not include 'postgres'. Yet id knows about 'postgres'. And 'postgres' owns the data. nobody:*:-2:-2:Unprivileged User:/var/empty:/usr/bin/false root:*:0:0:System Administrator:/var/root:/bin/sh daemon:*:1:1:System Services:/var/root:/usr/bin/false _uucp:*:4:4:Unix to Unix Copy Protocol:/var/spool/uucp:/usr/sbin/uucico _lp:*:26:26:Printing Services:/var/spool/cups:/usr/bin/false _postfix:*:27:27:Postfix Mail Server:/var/spool/postfix:/usr/bin/false _mcxalr:*:54:54:MCX AppLaunch:/var/empty:/usr/bin/false _pcastagent:*:55:55:Podcast Producer Agent:/var/pcast/agent:/usr/bin/ false _pcastserver:*:56:56:Podcast Producer Server:/var/pcast/server:/usr/ bin/false _serialnumberd:*:58:58:Serial Number Daemon:/var/empty:/usr/bin/false _devdocs:*:59:59:Developer Documentation:/var/empty:/usr/bin/false _sandbox:*:60:60:Seatbelt:/var/empty:/usr/bin/false _mdnsresponder:*:65:65:mDNSResponder:/var/empty:/usr/bin/false _ard:*:67:67:Apple Remote Desktop:/var/empty:/usr/bin/false _www:*:70:70:World Wide Web Server:/Library/WebServer:/usr/bin/false _eppc:*:71:71:Apple Events User:/var/empty:/usr/bin/false _cvs:*:72:72:CVS Server:/var/empty:/usr/bin/false _svn:*:73:73:SVN Server:/var/empty:/usr/bin/false _mysql:*:74:74:MySQL Server:/var/empty:/usr/bin/false _sshd:*:75:75:sshd Privilege separation:/var/empty:/usr/bin/false _qtss:*:76:76:QuickTime Streaming Server:/var/empty:/usr/bin/false _cyrus:*:77:6:Cyrus Administrator:/var/imap:/usr/bin/false _mailman:*:78:78:Mailman List Server:/var/empty:/usr/bin/false _appserver:*:79:79:Application Server:/var/empty:/usr/bin/false _clamav:*:82:82:ClamAV Daemon:/var/virusmails:/usr/bin/false _amavisd:*:83:83:AMaViS Daemon:/var/virusmails:/usr/bin/false _jabber:*:84:84:Jabber XMPP Server:/var/empty:/usr/bin/false _xgridcontroller:*:85:85:Xgrid Controller:/var/xgrid/controller:/usr/ bin/false _xgridagent:*:86:86:Xgrid Agent:/var/xgrid/agent:/usr/bin/false _appowner:*:87:87:Application Owner:/var/empty:/usr/bin/false _windowserver:*:88:88:WindowServer:/var/empty:/usr/bin/false _spotlight:*:89:89:Spotlight:/var/empty:/usr/bin/false _tokend:*:91:91:Token Daemon:/var/empty:/usr/bin/false _securityagent:*:92:92:SecurityAgent:/var/empty:/usr/bin/false _calendar:*:93:93:Calendar:/var/empty:/usr/bin/false _teamsserver:*:94:94:TeamsServer:/var/teamsserver:/usr/bin/false _update_sharing:*:95:-2:Update Sharing:/var/empty:/usr/bin/false _installer:*:96:-2:Installer:/var/empty:/usr/bin/false _atsserver:*:97:97:ATS Server:/var/empty:/usr/bin/false _unknown:*:99:99:Unknown User:/var/empty:/usr/bin/false John On Apr 2, 2010, at 9:29 AM, John R Pierce wrote: thats not even possible unless the macos doesn't use /etc/passwd as its user database.I dunno much about macosx, but everything I hear about it sounds like they took unix and twisted it all around. -- 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] 1-Click installer problems
There is a CLI option where? Forgive my ignorance, please. Does it appear in the one-click installer? John On Apr 2, 2010, at 9:19 AM, Sachin Srivastava wrote: There is a CLI option --serviceaccount username which a user can use to make any user the owner of postgres service and data files. Also, if you choose 'postgres' as the service account and the 'postgres' user doesn't exist. The installer will create postgres as a 'locked' user account. Thats the reason you dont see 'postgres' listed as any other normal user. These steps were taken to enhance the security of the data folder. Again, anytime a user is free to use any account as the service account and not use 'postgres'. On 4/2/10 12:37 PM, John Gage wrote: Then I don't understand why the installer doesn't do the same thing. Or, in the alternative, why it doesn't ask you what you want these parameters to be. I would say that, typically, someone installing postgres does it, conceivably, as root or, more likely, as a user. What he or she doesn't do is install it as user 'postgres'. Yet, that is what the one-click installer does. I do not believe that this is intuitive. What is more, gratuitiously adding a user to the system doesn't seem to make a whole lot of sense. In addition, all other one-click installations on the Mac either don't ask for root privileges, because they don't need them, or ask for them, but still install under the current user. Some installations will even ask whether you want the application usable by all users of the machine or just you. But none, repeat none, create a new user. What is more, through standard unix commands such as who or cat / etc/passwd, I cannot find the user 'postgres' on my machine...even though he is the owner of the Postgres data files...on my machine. There's the rub. 'postgres' owns files...my files...on my machine, yet he is not on my machine. Not good. I should add that I am an accolyte of Postgres and am only raising this (possible) issue in the most positive spirit I am capable of. In addition, I think that the people on this list are superb, and the responses are unbelievably helpful and accurate. John On Apr 2, 2010, at 8:29 AM, John R Pierce wrote: John Gage wrote: The 8.4.2 documentation says: The default user name is your Unix user name, as is the default database name. when you as a user connect to the database server the commands like psql, pg_dump, etc all use your unix username as the default for the database username, and your username as teh default for the database name, unless you specify a different user and/or database on hte command line. -- Regards, Sachin Srivastava EnterpriseDB, the Enterprise Postgres company.
Re: [GENERAL] 1-Click installer problems
Thats what i get: edbs-MacBook:~ sachin$ hdiutil attach postgresql-8.4.3-1-osx.dmg expected CRC32 $F9B026D4 /dev/disk1 Apple_partition_scheme /dev/disk1s1Apple_partition_map /dev/disk1s2Apple_HFS /Volumes/PostgreSQL 8.4.3-1 edbs-MacBook:~ sachin$ sudo /Volumes/PostgreSQL\ 8.4.3-1/postgresql-8.4.3-1-osx.app/Contents/MacOS/installbuilder.sh --help Password: PostgreSQL 8.4 Usage: --help Display the list of valid options --version Display product information --optionfile optionfile Installation option file Default: --unattendedmodeui unattendedmodeui Unattended Mode UI Default: none Allowed: none minimal minimalWithDialogs --debuglevel debuglevel Debug information level of verbosity Default: 2 Allowed: 0 1 2 3 4 --mode mode Installation mode Default: qt Allowed: qt osx text unattended --debugtrace debugtrace Debug filename Default: --installer-language installer-language Language selection Default: Allowed: en es --extract-only extract-only Default: 0 --superaccount superaccount Sets the user name of the database superuser. Defaults to 'postgres'. Default: postgres --servicename servicenameservicename.description Default: postgresql-8.4 --serviceaccount serviceaccount Sets the operating system user account that owns the server process. Defaults to 'postgres'. Default: postgres --create_shortcuts create_shortcuts Specifies whether or not menu shortcuts should be created. Default: 1 --prefix prefix Installation Directory Default: /Library/PostgreSQL/8.4 --datadir datadirData Directory Default: /Library/PostgreSQL/8.4/data --superpassword superpassword Password Default: --serverport serverport Port Default: 5432 --locale locale Locale Default: --install_plpgsql install_plpgsql Install pl/pgsql in template1 database? Default: 1 On 4/2/10 1:14 PM, John Gage wrote: There is a CLI option where? Forgive my ignorance, please. Does it appear in the one-click installer? John On Apr 2, 2010, at 9:19 AM, Sachin Srivastava wrote: There is a CLI option --serviceaccount username which a user can use to make any user the owner of postgres service and data files. Also, if you choose 'postgres' as the service account and the 'postgres' user doesn't exist. The installer will create postgres as a 'locked' user account. Thats the reason you dont see 'postgres' listed as any other normal user. These steps were taken to enhance the security of the data folder. Again, anytime a user is free to use any account as the service account and not use 'postgres'. On 4/2/10 12:37 PM, John Gage wrote: Then I don't understand why the installer doesn't do the same thing. Or, in the alternative, why it doesn't ask you what you want these parameters to be. I would say that, typically, someone installing postgres does it, conceivably, as root or, more likely, as a user. What he or she doesn't do is install it as user 'postgres'. Yet, that is what the one-click installer does. I do not believe that this is intuitive. What is more, gratuitiously adding a user to the system doesn't seem to make a whole lot of sense. In addition, all other one-click installations on the Mac either don't ask for root privileges, because they don't need them, or ask for them, but still install under the current user. Some installations will even ask whether you want the application usable by all users of the machine or just you. But none, repeat none, create a new user. What is more, through standard unix commands such as who or cat /etc/passwd, I cannot find the user 'postgres' on my machine...even though he is the owner of the Postgres data files...on my machine. There's the rub. 'postgres' owns files...my files...on my machine, yet he is not on my machine. Not good. I should add that I am an accolyte of Postgres and am only raising this (possible) issue in the most positive spirit I am capable of. In addition, I think that the people on this list are superb, and the responses are unbelievably helpful and accurate. John On Apr 2, 2010, at 8:29 AM, John R Pierce wrote: John Gage wrote: The 8.4.2 documentation
Re: [GENERAL] 1-Click installer problems
On 2/04/2010 3:07 PM, John Gage wrote: Yet, that is what the one-click installer does. I do not believe that this is intuitive. What is more, gratuitiously adding a user to the system doesn't seem to make a whole lot of sense. This is absolutely standard practice on UNIX systems, and on Windows systems for secure server installations too. It allows the server to isolate its self from the rest of the system, protecting both the system and the server. For example, every Windows XP system with the .NET framework 3.0 installed will have an ASPNET user on it. This user is used to run any ASP.NET service processes so that Internet attackers can't overwrite system files if they successfully exploit the asp.net services. If PostgreSQL didn't add a user to the system, it'd have to: a) Run as root. This is DANGEROUS as any security problem in PostgreSQL that allows an attacker to force Pg to run code gets them root access. b) Run as your user. What if you remove the user later - crunch, your database just broke. If Pg was attacked successfully, the attacker wouldn't get root ... but they would get the ability to access and delete all your files. Arguably (b) is an acceptable non-admin-install option for Mac OS X systems for non-production use with unimportant test data you can afford to lose. I'm not convinced it's a good idea, though. Perhaps the PostgreSQL installer needs to inform users of this, though (say a help button when asked about user account details). But none, repeat none, create a new user. Most server products that attempt even the vaguest kind of security should. Some even do ;-) PostgreSQL isn't just a program, remember, it's a running database service that might be network acecssible. What is more, through standard unix commands such as who or cat /etc/passwd, I cannot find the user 'postgres' on my machine...even though he is the owner of the Postgres data files...on my machine. Mac OS X isn't standard unix. Look in (depending on the Mac OS X version) the NetInfo database, OpenDirectory, or whereever Apple hides the user database this week. You'll find that your own user account isn't in /etc/passwd either. The postgres user *is* recognised by standard unix commands. id postgresql will report its existence and details about it. It's just not stored in /etc/passwd, because that's not how Mac OS X stores account information (though there's some legacy stuff still in there). There's the rub. 'postgres' owns files...my files...on my machine, yet he is not on my machine. Not good. Well, it's good for security. It also helps prevent people from unwittingly going in and butchering the data directory - they're not *meant* to be deleting things in there. This way they at least need admin rights to do it. What actual problem does it cause? Does the postgres user show up as an additional login option on the login screen? Other than the notional issue of not owning the files, what's the problem? -- 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] 1-Click installer problems
On 2/04/2010 3:42 PM, John Gage wrote: If I do cat /etc/passwd, I get the following, which does not include 'postgres'. Yet id knows about 'postgres'. And 'postgres' owns the data. Try: sudo dscl localhost -list NetInfo/Users Apple don't use the usual UNIX utilities, they've got their own user database. It's based on LDAP (as of 10.??'s OpenDirectory) and before that was a more NIS-like service. See dscl, netinfo, opendirectory documentation for more info. -- 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] 1-Click installer problems
On Fri, Apr 2, 2010 at 5:55 AM, John Gage jsmg...@numericable.fr wrote: I will bet a bucket of day-old squid that this is a user rights problem. They usually are, so I'm not taking that bet. We do see one regular issue where Microsoft's cacl's command gives an unhelpful error for no apparent reason: The data is invalid. (usually when the user has chosen a data directory *outside* of Program Files), but pretty much everything else is in some way rights related. We just need to understand what is different in each case... FYI, as of 8.4.3/8.3.10, we know we do function correctly in the face of a system secured according to the Federal Desktop Core Configuration (FDCC) mandate (http://www.microsoft.com/industry/government/federal/fdccdeployment.mspx), but frankly Windows offers a million and one ways to tighten/screw up security. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] 1-Click installer problems
On Apr 2, 2010, at 10:28 AM, Craig Ringer wrote: b) Run as your user. What if you remove the user later - crunch, your database just broke. If Pg was attacked successfully, the attacker wouldn't get root ... but they would get the ability to access and delete all your files. Arguably (b) is an acceptable non-admin-install option for Mac OS X systems for non-production use with unimportant test data you can afford to lose. I'm not convinced it's a good idea, though. First, I ask forgiveness for ignorance. Second, the characterization in your second quoted paragraph is near- sighted. Mac OS X systems for non-production use means that I don't run a car rental company. I don't. But non-production? Well, I use postgres for things that are extremely important to me. What's more, I intend, in the very near future, to have postgres as the back-end to an internet system that will hopefully be in use by 85,000 French nursing students, which I suppose is a form of production. And when I load the tables into the postgres implementation of whatever ISP I choose, all the meshugas around permissions will disappear as far as I'm concerned. But unimportant test data you can afford to lose? Please. Anyone who uses any database system for more than 10 minutes regards his or her data as important and definitely not affordable to lose. I have triply redundant back-up for my data. And the only reason I know that 'postgres' owns my data (or did) is that I wanted to back up the files. Why else would I know? Apple has a database product which is intended for individuals and their data. It is called Bento. It has a charming interface and it does what it does well. No chain of pain. But there is one teeny, tiny problem. It's a ridiculous ersatz iTunes clone that has nothing to do with databases. And, like everything else in modern interfaces, the back-end is sqlite which doesn't cut it one little bit. Bento files are sqlite files accessible by sqlite. So you might as well run sqlite in the first place and get it over with, but that's only if you're not really interested in a database. Postgres, on the other hand, fully supports regular expressions, sql, etc. etc. etc. etc. Postgres' clients psql and pgAdmin are perfectly extraordinary. And finally the support in the embodiment of this list is unbelievable. Incredible. I don't think that b) is necessarily acceptable. But if it isn't, then I really and truly wish that the very traditional way that postgres wants to set itself up were more transparent and controllable. It is a wish. Perhaps a fantasy. But a fantasy is a wish (S. Freud). -- 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] 1-Click installer problems
I'm not quite as brain-dead as this statement makes me sound. I use posgres' back-up system to back up the databases. I don't copy the files. On Apr 2, 2010, at 12:15 PM, John Gage wrote: And the only reason I know that 'postgres' owns my data (or did) is that I wanted to back up the files. Why else would I know? -- 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] 1-Click installer problems
I am incredibly interested in this. In the first place, I did not load postgres from the command line as you do here. I double-clicked. I also do not remember seeing the usage options. That being said, now that I have downloaded and installed the system, how can I change: --serviceaccount serviceaccount Sets the operating system user account that owns the server process. Defaults to 'postgres'. Default: postgres Or, in fact, must I re-install to change this? It looks like I have to re-install. Thank you very much for responding to my questions. I truly appreciate it. Your support is welcome and superb. John On Apr 2, 2010, at 9:52 AM, Sachin Srivastava wrote: Thats what i get: edbs-MacBook:~ sachin$ hdiutil attach postgresql-8.4.3-1-osx.dmg expected CRC32 $F9B026D4 /dev/disk1 Apple_partition_scheme /dev/disk1s1Apple_partition_map /dev/disk1s2Apple_HFS /Volumes/ PostgreSQL 8.4.3-1 edbs-MacBook:~ sachin$ sudo /Volumes/PostgreSQL\ 8.4.3-1/ postgresql-8.4.3-1-osx.app/Contents/MacOS/installbuilder.sh --help Password: PostgreSQL 8.4 Usage: --help Display the list of valid options --version Display product information --optionfile optionfile Installation option file Default: --unattendedmodeui unattendedmodeui Unattended Mode UI Default: none Allowed: none minimal minimalWithDialogs --debuglevel debuglevel Debug information level of verbosity Default: 2 Allowed: 0 1 2 3 4 --mode mode Installation mode Default: qt Allowed: qt osx text unattended --debugtrace debugtrace Debug filename Default: --installer-language installer-language Language selection Default: Allowed: en es --extract-only extract-only Default: 0 --superaccount superaccount Sets the user name of the database superuser. Defaults to 'postgres'. Default: postgres --servicename servicenameservicename.description Default: postgresql-8.4 --serviceaccount serviceaccount Sets the operating system user account that owns the server process. Defaults to 'postgres'. Default: postgres --create_shortcuts create_shortcuts Specifies whether or not menu shortcuts should be created. Default: 1 --prefix prefix Installation Directory Default: /Library/PostgreSQL/8.4 --datadir datadirData Directory Default: /Library/PostgreSQL/8.4/data --superpassword superpassword Password Default: --serverport serverport Port Default: 5432 --locale locale Locale Default: --install_plpgsql install_plpgsql Install pl/pgsql in template1 database? Default: 1 On 4/2/10 1:14 PM, John Gage wrote: There is a CLI option where? Forgive my ignorance, please. Does it appear in the one-click installer? John On Apr 2, 2010, at 9:19 AM, Sachin Srivastava wrote: There is a CLI option --serviceaccount username which a user can use to make any user the owner of postgres service and data files. Also, if you choose 'postgres' as the service account and the 'postgres' user doesn't exist. The installer will create postgres as a 'locked' user account. Thats the reason you dont see 'postgres' listed as any other normal user. These steps were taken to enhance the security of the data folder. Again, anytime a user is free to use any account as the service account and not use 'postgres'. On 4/2/10 12:37 PM, John Gage wrote: Then I don't understand why the installer doesn't do the same thing. Or, in the alternative, why it doesn't ask you what you want these parameters to be. I would say that, typically, someone installing postgres does it, conceivably, as root or, more likely, as a user. What he or she doesn't do is install it as user 'postgres'. Yet, that is what the one-click installer does. I do not believe that this is intuitive. What is more, gratuitiously adding a user to the system doesn't seem to make a whole lot of sense. In addition, all other one-click installations on the Mac either don't ask for root privileges, because they don't need them, or ask for them, but still install under the current user. Some installations will even ask whether you want the application
Re: [GENERAL] 1-Click installer problems
Yes you need to re-install.. (uninstall and install again). You can point the new installation to the old data directory if you want.. On 4/2/10 4:25 PM, John Gage wrote: I am incredibly interested in this. In the first place, I did not load postgres from the command line as you do here. I double-clicked. I also do not remember seeing the usage options. That being said, now that I have downloaded and installed the system, how can I change: --serviceaccount serviceaccount Sets the operating system user account that owns the server process. Defaults to 'postgres'. Default: postgres Or, in fact, must I re-install to change this? It looks like I have to re-install. Thank you very much for responding to my questions. I truly appreciate it. Your support is welcome and superb. John On Apr 2, 2010, at 9:52 AM, Sachin Srivastava wrote: Thats what i get: edbs-MacBook:~ sachin$ hdiutil attach postgresql-8.4.3-1-osx.dmg expected CRC32 $F9B026D4 /dev/disk1 Apple_partition_scheme /dev/disk1s1Apple_partition_map /dev/disk1s2Apple_HFS /Volumes/PostgreSQL 8.4.3-1 edbs-MacBook:~ sachin$ sudo /Volumes/PostgreSQL\ 8.4.3-1/postgresql-8.4.3-1-osx.app/Contents/MacOS/installbuilder.sh --help Password: PostgreSQL 8.4 Usage: --help Display the list of valid options --version Display product information --optionfile optionfile Installation option file Default: --unattendedmodeui unattendedmodeui Unattended Mode UI Default: none Allowed: none minimal minimalWithDialogs --debuglevel debuglevel Debug information level of verbosity Default: 2 Allowed: 0 1 2 3 4 --mode mode Installation mode Default: qt Allowed: qt osx text unattended --debugtrace debugtrace Debug filename Default: --installer-language installer-language Language selection Default: Allowed: en es --extract-only extract-only Default: 0 --superaccount superaccount Sets the user name of the database superuser. Defaults to 'postgres'. Default: postgres --servicename servicenameservicename.description Default: postgresql-8.4 --serviceaccount serviceaccount Sets the operating system user account that owns the server process. Defaults to 'postgres'. Default: postgres --create_shortcuts create_shortcuts Specifies whether or not menu shortcuts should be created. Default: 1 --prefix prefix Installation Directory Default: /Library/PostgreSQL/8.4 --datadir datadirData Directory Default: /Library/PostgreSQL/8.4/data --superpassword superpassword Password Default: --serverport serverport Port Default: 5432 --locale locale Locale Default: --install_plpgsql install_plpgsql Install pl/pgsql in template1 database? Default: 1 On 4/2/10 1:14 PM, John Gage wrote: There is a CLI option where? Forgive my ignorance, please. Does it appear in the one-click installer? John On Apr 2, 2010, at 9:19 AM, Sachin Srivastava wrote: There is a CLI option --serviceaccount username which a user can use to make any user the owner of postgres service and data files. Also, if you choose 'postgres' as the service account and the 'postgres' user doesn't exist. The installer will create postgres as a 'locked' user account. Thats the reason you dont see 'postgres' listed as any other normal user. These steps were taken to enhance the security of the data folder. Again, anytime a user is free to use any account as the service account and not use 'postgres'. On 4/2/10 12:37 PM, John Gage wrote: Then I don't understand why the installer doesn't do the same thing. Or, in the alternative, why it doesn't ask you what you want these parameters to be. I would say that, typically, someone installing postgres does it, conceivably, as root or, more likely, as a user. What he or she doesn't do is install it as user 'postgres'. Yet, that is what the one-click installer does. I do not believe that this is intuitive. What is more, gratuitiously adding a user to the system doesn't seem to make a whole lot of sense. In addition, all other one-click installations on the Mac either don't ask for root privileges, because they don't
[GENERAL] Advice on webbased database reporting
I need to make certain views from the database visible online (on our webpage) and I wonder if there is any reasonably quick solution for this that works with Postgres? At best, a query should be specified and the user should be able to select the layout on certain columns (like stepped, or outlined). I don't mind running a whole CMS on our Apache server as long as it allows me to make reports and is free to use. Has anyone any suggestions? Kind regards, Davor -- 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] 1-Click installer problems
See attached attached OneClick_PG_Installer notes. Igor Neyman -Original Message- From: John Gage [mailto:jsmg...@numericable.fr] Sent: Friday, April 02, 2010 3:44 AM To: sachin.srivast...@enterprisedb.com Cc: pgsql-general@postgresql.org Subject: Re: 1-Click installer problems There is a CLI option where? Forgive my ignorance, please. Does it appear in the one-click installer? John On Apr 2, 2010, at 9:19 AM, Sachin Srivastava wrote: There is a CLI option --serviceaccount username which a user can use to make any user the owner of postgres service and data files. Also, if you choose 'postgres' as the service account and the 'postgres' user doesn't exist. The installer will create postgres as a 'locked' user account. Thats the reason you dont see 'postgres' listed as any other normal user. These steps were taken to enhance the security of the data folder. Again, anytime a user is free to use any account as the service account and not use 'postgres'. On 4/2/10 12:37 PM, John Gage wrote: Then I don't understand why the installer doesn't do the same thing. Or, in the alternative, why it doesn't ask you what you want these parameters to be. I would say that, typically, someone installing postgres does it, conceivably, as root or, more likely, as a user. What he or she doesn't do is install it as user 'postgres'. Yet, that is what the one-click installer does. I do not believe that this is intuitive. What is more, gratuitiously adding a user to the system doesn't seem to make a whole lot of sense. In addition, all other one-click installations on the Mac either don't ask for root privileges, because they don't need them, or ask for them, but still install under the current user. Some installations will even ask whether you want the application usable by all users of the machine or just you. But none, repeat none, create a new user. What is more, through standard unix commands such as who or cat /etc/passwd, I cannot find the user 'postgres' on my machine...even though he is the owner of the Postgres data files...on my machine. There's the rub. 'postgres' owns files...my files...on my machine, yet he is not on my machine. Not good. I should add that I am an accolyte of Postgres and am only raising this (possible) issue in the most positive spirit I am capable of. In addition, I think that the people on this list are superb, and the responses are unbelievably helpful and accurate. John On Apr 2, 2010, at 8:29 AM, John R Pierce wrote: John Gage wrote: The 8.4.2 documentation says: The default user name is your Unix user name, as is the default database name. when you as a user connect to the database server the commands like psql, pg_dump, etc all use your unix username as the default for the database username, and your username as teh default for the database name, unless you specify a different user and/or database on hte command line. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com , the Enterprise Postgres http://www.enterprisedb.com company. PostgreSQL 8.4 Usage: --help Display the list of valid options --version Display product information --optionfile optionfile Installation option file Default: --unattendedmodeui unattendedmodeui Unattended Mode UI Default: none Allowed: none minimal minimalWithDialogs --debuglevel debuglevel Debug information level of verbosity Default: 2 Allowed: 0 1 2 3 4 --mode mode Installation mode Default: win32 Allowed: win32 unattended --debugtrace debugtrace Debug filename Default: --installer-language installer-language Language selection Default: Allowed: en es --extract-only extract-only Default: 0 --superaccount
Re: [GENERAL] 1-Click installer problems
Thanks very, very much for this. I am truly grateful. On Apr 2, 2010, at 2:44 PM, Sachin Srivastava wrote: Yes you need to re-install.. (uninstall and install again). You can point the new installation to the old data directory if you want.. On 4/2/10 4:25 PM, John Gage wrote: I am incredibly interested in this. In the first place, I did not load postgres from the command line as you do here. I double-clicked. I also do not remember seeing the usage options. That being said, now that I have downloaded and installed the system, how can I change: --serviceaccount serviceaccount Sets the operating system user account that owns the server process. Defaults to 'postgres'. Default: postgres Or, in fact, must I re-install to change this? It looks like I have to re-install.
[GENERAL] HW and performances
I'm using apache and postgres to make an ecommerce website work on an old xeon box processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 4 model name : Intel(R) Xeon(TM) CPU 3.20GHz It looks it has 4 cores but I think they are 2 cores + HT 4Gb RAM 3 disks RAID5 [sic] free total used free sharedbuffers cached Mem: 40480243984896 63128 0 2083042645748 -/+ buffers/cache:11308442917180 Swap: 78131124927812620 Swap get slightly hit after long period of uptime. Most of the work happens in the items table ~1M rows. The queries that actually impact on usability are: - full text searches on a gin index on a tsvector - updates of the catalogue (and related gin index) in batches of 20K items updated/inserted. It *seems* (not really sure) that the bottleneck for full text searches are the CPU. I'll have the chance to split the load over a newer box: 2 socket 5120 Xeon box 4GB RAM 2 disks RAID0 (single disk io seems faster than the box above) I'm planning to move the DB on the newer box and leave apache on the old one. This should free enough ram on the DB box so that the swap will be untouched. I'm hoping to halve the average page generation time. I could add ram or disks to the newer box. Changing CPU doesn't look a good investment. Did I assign the task to the various boxes correctly? Do I have any chance of getting near to my target even adding some more HW to the newer box? If not I'd think to buy a brand new box. Any suggestion? By comparison I've a 2 socket x 4 cores Xeon E5310 @ 1.60GHz 8 Gb RAM 4 disks SATA RAID10 with apache and pg running inside a vserver (2.6.26-2-vserver-amd64) that looks it can handle both jobs (apache and pg) at a reasonable speed (roughly twice faster than needed) but that is still a bit slower than what I'd like on catalogue updates. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Advice on webbased database reporting
On Friday 2. April 2010 14.43.48 Davor J. wrote: I need to make certain views from the database visible online (on our webpage) and I wonder if there is any reasonably quick solution for this that works with Postgres? At best, a query should be specified and the user should be able to select the layout on certain columns (like stepped, or outlined). I don't mind running a whole CMS on our Apache server as long as it allows me to make reports and is free to use. Has anyone any suggestions? PHP works great with Postgres and Apache. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- 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] Lifekeeper
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Is anyone using Lifekeeper for Linux availability with Postgres? If so, what are your thoughts on it? Work as advertised? Any dangerous gotchas? Works well. May need to change the way it kills Postgres though, IIRC it defaults to a too-nice mode when you really want immediate. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201004021435 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAku2OS8ACgkQvJuQZxSWSsjDlwCfcwEoN6n6AyXTlqTc0nTZRaRv eq0AoNRdLZbt/xLnwZ30oOJpZwvuEoIZ =QIhY -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table space question
Hi all - I have created dump of a database where I have a tablespace (data_tblspace ) associated with relations. I have restored to a different server. I created the same table space. Now I need to create an other database on the same server using the same dump. but I need to have a different tablespace for all the relations. Can you please suggest? Do I have to do the restore the database to the same tablespace first? and then alter the tablespace? Regards
Re: [GENERAL] Table space question
On Friday 02 April 2010 12:44, akp geek wrote: Hi all - I have created dump of a database where I have a tablespace (data_tblspace ) associated with relations. I have restored to a different server. I created the same table space. Now I need to create an other database on the same server using the same dump. but I need to have a different tablespace for all the relations. Can you please suggest? Do I have to do the restore the database to the same tablespace first? and then alter the tablespace? Regards Maybe something like this (assuming the dump file is in text/sql format): sed s/data_tblspace/new_tblspace/g dump_file new dump file -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] any built-in function to get time in seconds?
I have been using this one liner c function that I call my_now() to get the number of seconds since some fixed point in the past. I find it more convenient than built-in now() and if I want abstime I do abstime(my_now()). Thing is everytime I do a major version upgrade I had to recompile this and it's a pain in the neck. I feel there must be something built-in with pg to get the same thing since I can get abstime from it like that. -- 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] any built-in function to get time in seconds?
On Friday 02 April 2010 5:41:09 pm zhong ming wu wrote: I have been using this one liner c function that I call my_now() to get the number of seconds since some fixed point in the past. I find it more convenient than built-in now() and if I want abstime I do abstime(my_now()). Thing is everytime I do a major version upgrade I had to recompile this and it's a pain in the neck. I feel there must be something built-in with pg to get the same thing since I can get abstime from it like that. http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html epoch For date and timestamp values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for interval values, the total number of seconds in the interval SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08'); Result: 982384720 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Result: 442800 Here is how you can convert an epoch value back to a time stamp: SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second'; -- 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] warm standby possible with 8.1?
On Sun, Mar 28, 2010 at 8:20 PM, Yar Tykhiy y...@barnet.com.au wrote: Guys, I'm afraid there may be some confusion here. I've got a warm standby happily running with simple home-made archive and restore scripts on a legacy Postgresql installation as old as 8.0. And yes, I did failover multiple times (I posted a report or two on that to this list.) What Zhong isn't going to get is converting the master node to a warm standby node as easily as by just stopping it and renaming recovery.done to recovery.conf. The way to go here is to take a file-level DB backup from the master node and bootstrap a new warm standby node from it, then let it catch up with the master node WAL-wise. Yar Greg confirmed the capability of 8.1 for me. While I am still sticking with 8.1, I think what I am doing is the same as Yar but I don't completely understand his terminology. What I do is every now and then, existing archive files on standby server are wiped out and the whole data directory on standby server has to refreshed from the master db and WAL starts to accumulate again on the standy server. Two things can force you to refresh like that: 1) archive data on standby server can get very big and you can easily run out of disk space. 2) if you don't want to play lots of wal files and wait a very long time on actual recovery you will need to refresh it. In my case wal files accumulate quickly on standby server because I am also sending fake traffic (as suggested by a website) frequently because I am not supposed to lose no more than five minutes of transaction data. -- 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] Advice on webbased database reporting
On 02/04/2010, Leif Biberg Kristensen l...@solumslekt.org wrote: On Friday 2. April 2010 14.43.48 Davor J. wrote: I need to make certain views from the database visible online (on our webpage) and I wonder if there is any reasonably quick solution for this that works with Postgres? At best, a query should be specified and the user should be able to select the layout on certain columns (like stepped, or outlined). I don't mind running a whole CMS on our Apache server as long as it allows me to make reports and is free to use. Has anyone any suggestions? PHP works great with Postgres and Apache. take a look at The Karoo Project: http://www.zwartberg.com/tutorial_1.html regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general