PostgreSQL notes
From DSD Wiki
This is all FreeBSD specific, for now...
Contents |
Install
# portinstall -pL /usr/ports/logs/%s::%s.log postgresql-*-7.4.25
The FreeBSD port has postgresql log to /var/log/syslog by default and not listen on any TCP/IP ports, Unix domain sockets only. It also creates pgsql user for running the database engine - postmaster.
Initial set-up
As root, add
postgresql_enable="YES"
to /etc/rc.conf
As the pgsql user:
% /etc/rc.d/postgresql initdb
Running
Start/stop
# /usr/local/etc/rc.d/postgresql start # /usr/local/etc/rc.d/postgresql stop
To re-read config files:
# /usr/local/etc/rc.d/postgresql reload
Creating databases and DB users
The above initdb command created 1 DB user and a couple of databases within the DB engine (aka 'cluster'). That DB user is given, by default, the same name as the Unix user, here pgsql. (The databases created are template0 and template1 which are behind that curtain you should never look behind.) This makes the pgsql the "root" user within the DB engine.
To create a new database: (as the pgsql (unix) user):
$ createdb test
Connect to that db:
$ psql -d test
This works because you've connected as the pgsql DB user, by virtue of already being the pgsql Unix user. As a different Unix user you'd connect via:
$ psql -d test -U pgsql
Yes, this can (for the moment) be done by any local unix user without a password. We'll change that in a minute...
List databases (from the psql prompt here "test=# "):
test=# \l
Create a new db user (again as the pgsql unix user):
$ createuser tester
Then answer a few questions.
List users (from the psql prompt)
test=# \du
Changing connection permissions
Details here: http://www.postgresql.org/docs/7.4/interactive/client-authentication.html
To prevent anyone but the Unix pgsql user from becoming the pgsql DB user, add the following line before any other configuration line in your ~pgsql/data/pg_hba.conf file:
local all pgsql ident sameuser
Then reload postgreSQL.
