The bootstrap is not really hard when you've done it once, just jot it down somewhere.
In my experience most people stumble because the network security in postgres is pretty tight by default.
This is easily fixed and needs to be done only once.
First: Find your pg_hba.conf. It's in the database-directory, that's often linked to /etc/postgresql.
# Backup the original
$ cp pg_hba.conf pg_hba.conf_orig
# Now replace it with our desired security settings
$ cat >pg_hba.conf <<EOF
# Require password auth from remote hosts
# localhost and local socket are trusted
host all all 0.0.0.0/0 md5
local all all trust
host all all 127.0.0.1/32 trust
EOF
# restart
$ /etc/init.d/postgresql restart
From now on you can connect as any user from localhost without a password. Thus, we can now just go about our business.
# connect as user postgres (the super-user)
$ psql -U postgres
postgres=# create database dummy;
postgres=# create user bob with password 'pony';
postgres=# grant all on database dummy to bob;
postgres=# ^D
# Now you have a database that user bob can use.
# From remote he will have to use the password 'pony'.
# From localhost no password needed because of our pg_hba settings above.
$ psql -U bob dummy
bob=> create table ...
I would be wary about trusting all connections from localhost. As vulnerabilities are many, getting a local unprivileged shell isn't exactly hard and as opening a socket connection isn't exactly a privileged operation.. you could expose yourself to a nasty bootstrap attack. Probably better to trust the local unix domain socket, and make it accessible by root only... if you really need it.
You make a good point. Conversely, a part of basic security policies is to present as little attack surface as possible. Ensuring that you're not trusting local connections with root equivalent access to your database is a good way of doing that. Modern GNU/Linux distributions have many thousands of packages installed, some of which with fairly open Internet access. Could you put up a default deny firewall to prevent that? Yes. Given that, in most systems there will always be holes punched in those firewalls which allow attacks through as those machines are typically used to serve traffic and do actual work. If you are exploited by a vulnerability in some random package you have installed then you /will/ have potentially multiple /untrusted/ and /hostile/ users on your system. Now, assuming there isn't a local privilege escalation attack to which your kernel is vulnerable, they'll be looking for services which treat local sockets as trusted and attempt to bootstrap from there. A vital concept of basic security precautions is that one doesn't just prevent attacks through secure defaults and front-line security precautions (i.e. firewalls), but one must both contain and detect successful penetrations when they do occur. You must have defence in depth, and not just a hard shell of security, because you can never really tell which one of those protective safeguards will fail due to bugs, human error, or technical incompetence.
I believe the null hypothesis should be that you assume there are vulnerabilities in those systems. Your assertion that it's extremely unlikely that local privilege escalations exist is demonstrably false. In fact a quick query of the CVE database shows a large number of /known/ vulnerabilities ( http://web.nvd.nist.gov/view/vuln/search-results?cves=true&#... ). Regarding the statement that if you can't trust localhost then your defences have failed... well, duh. The point is to limit the damage by having multiple layers of defence. If a script kiddie manages to use UltraPWN2000 to get an unprivileged shell on your box, you don't want them to be able to easily just drop all tables if they're not smart enough to use a local privilege escalation (assuming they can compile locally anyway). You won't be able to prevent all damage from a skilled attacker but you should attempt to mitigate it and give yourself time to respond to it. You don't trust your locked front door to protect your valuables, you put them in a safe.
Your assertion that it's extremely unlikely that local privilege escalations exist
You misread me. I meant the opposite.
Your argument was based on the premise "assuming there isn't a local privilege escalation attack to which your kernel is vulnerable". I said this premise is invalid (as you just confirmed yourself).
You don't trust your locked front door to protect your valuables, you put them in a safe.
The front door is your firewall. The safe is your host. When someone breaks into your host then it's game over. When you have too much spare time you can attempt to layer further at the host-level but that's usually an exercise in futility.
I can see that we don't fundamentally disagree on many issues. Attempting to protect from all on-host attacks is pointless. Though I don't disagree with you in the majority of what we've discussed, I will always stand up for simple changes to settings that make it just that much harder for unauthorised users to cause trouble. That said those changes must not add too many onerous access requirements for authorised users. Of which, I don't believe asking people to have sudo access to your machine to have super-user access to your database is one.
Alternately, you can use sameuser privs at the local socket level, which will allow unix user foo access to Postgres user foo without a password, but only on the domain socket. That's probably the best combination of ease of use and moderate security for a dev system.
Yes, the docs could use indeed a few lighter tutorials for people starting out.
However, once you're over that initial learning hump and start looking for more specific things you'll quickly notice why the postgres manual is often cited as being one of the best documentations ever written (inside and outside the OSS world). It's really that good - once you're familiar with a few basics.
However, once you're over that initial learning hump...you'll quickly notice why the postgres manual is often cited as being one of the best documentations ever written
"It's really that good - once you're familiar with a few basics."
By realizing that sometimes it takes a little investment for a payoff.
Outside the official docs you can find the same number of newbie tutorials for postgres that you'll find for MySQL. The difference is: Postgres has a stellar documentation once you're leaving the newbie stage. What MySQL calls documentation, let's just not talk about it...
I'm sorry if in this comment I spoke out of frustration, but I do stand by the thoughts about what a gold standard of a manual has to at a minimum provide. We're not talking about a "little investment". Read his original comment again.
if someone has enough experience with sql and enough mysql experience that they're really hitting limitations in mysql, then a merely "good" or even "ok" manual should at a minimum be able to enable that person's good-faith, dedicated attempt to start using this alternative to succeed. This is a pure horizontal transition. Postgresql is not only "also a database" but "also sql" and "a direct horizontal competitor to mysql" -- the technologies substitute each other other, often on a drop-in basis (in LAMP-type and modern stacks, especially with a framework that uses an ORM mapper that can use either). If someone is familiar with SQL and familiar with an alternative, the manual should at a minimum help the person transition to using this alternative. If nothing else. I would say this is the absolute minimum that a manual would have to provide to meet its basic mission.
At an absolute minimum, a manual MUST let you move horizontally from a drop-in competitor you have some competency with.
(I feel like, even historically, this has been missed by a lot of people.)
This is why I took umbrage with your attitude "why the postgres manual is often cited as being one of the best documentations ever written" after saying: "Yes, the docs could use indeed a few lighter tutorials for people starting out" in response to the guy who simply could not get past the installation hurdle.
This is like a stereo that someone can't turn on. They've used other stereos, but they just can't figure it out. They read the manual, but still can't figure it out. I would argue that, unless it's a real "duh, I was such an idiot" moment (happens to everyone) and isolated case, and it sounds like this isn't what you're saying, then in such a case the manual failed to do its primary mission. If the guy could have turned it on, he would have figured everything else out by himself.
If this guy can't do what he's trying to do after knowing mysql the way he's talking about and having the experience with attempting postgresql the way he talks about, I'd say that while the manual might be a fine technical reference, it is not yet complete and must not be held up as a great manual until this hole is filled.
that's fair enough. But let's be clear. Just because I always skip the first chapter of my stereo's documentation ("turning on your stereo") since it's so obvious how to connect things and turn it on, all the ports are very clearly labeled, etc, that does not mean that any stereo manual can be considered good if it does not include that chapter :)
I think I'm basically not winning any friends with this line of thinking, but I just see this as a long, recurring problem. It would be the same with a certain open source movement's long-standing alternative to a certain very well entranched operating system: they are horizontal substitutes for each other, and yet people who are expert on the entrenched system (power users, even) have given up on the open source alternative, because they "could not turn it on". (Get to the same place they get to after a fresh install of their entrenched operating system).
I'm not going to say more because these are (whatever those-things-that-are-landmines-floating-in-water are called)-filled waters.
In my experience most people stumble because the network security in postgres is pretty tight by default.
This is easily fixed and needs to be done only once.
First: Find your pg_hba.conf. It's in the database-directory, that's often linked to /etc/postgresql.
From now on you can connect as any user from localhost without a password. Thus, we can now just go about our business.