5 ms·
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 pos
by moe 14y ago
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 ...
- jeltz 14y agoNote: You need not restart PostgreSQL after changing pg_hba.conf. This is enough: /etc/init.d/postgresql reload
- boyter 14y agoThat looks too be exactly what I have been missing. I have yet to see details like that not spread out over 5 pages. Thank you!
- moe 14y agoYes, 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.
- its_so_on 14y agoHow can I take this seriously. 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."
- moe 14y agoBy 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...
- its_so_on 14y agoEDIT after a downvote. 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.
- justinjlynn 14y agoI 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.
- wiredfool 14y agoAlternately, 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.
- moe 14y agoI would be wary about trusting all connections from localhost. The only place where this is an issue would be hosts with multiple untrusted users, and these are becoming very rare. getting a local unprivileged shell isn't exactly hard Sorry, that's nonsense. The entire internet relies on the fact that this is relatively hard, unless you neglect basic security precautions.
- justinjlynn 14y agoYou 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.