Manual Setup at the Command Line

From PostgreSQL wiki

Revision as of 05:32, 26 November 2009 by Ringerc (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Start with the documentation on creating a cluster. In 8.3 the default of using auth has been removed, it now runs as unsecured for local users by default. Note that some packagers (such as RedHat) still use auth by default in their versions.

That page suggests some options you can pass to initdb to adjust the default security level. Now you want to look at initdb See that if you use -W/--pwprompt (same thing) you can assign a password to the database superuser at cluster creation time. If you do that, you can now change the default authentication scheme to password-based (-A md5 passed to initdb will do that), you'll be secure, and you'll have one user you can login as (postgres) to create more.

To see what other authentication methods are available and to learn what your options are look at Client Authentication The one you really need to dive into is pg_hba.conf which is the magic text file to edit here. A new one of those will be put in the base directory of your new database cluster. Open that file up, look at the documentation, and you'll need to add a line to add network support like one those in the examples. Probably something like

host postgres all 192.168.12.0/24 md5

(allow access to anybody on the 192.168.12 subnet access the database with a password)

The next document you may need is how to add more users: CREATE ROLE

Again look at the examples first and then backtrack to the parameters, will make more sense that way. After that you'll want to create more databases with createdb

And then you should be able to find your away around from there using the psql command line tool.

Personal tools