# running postgresql (is hell)

## zambizzi

I emerged postgresql 7.4.5-r2 (latest stable) just fine and performed the post-install configuration, and the server is running.

however, I have *no* idea what the password for the "postgres" user is and I can't go any further w/o it.

I'm trying to do this, for example:

```

createuser -P system

Enter password for new user:

Enter it again:

Shall the new user be allowed to create databases? (y/n) y

Shall the new user be allowed to create more new users? (y/n) n

Password:

createuser: could not connect to database template1: FATAL:  Password authentication failed for user "postgres"

```

I looked through the post install docs at postgresql.org but I can't find what I need...any ideas?

Thanks!

----------

## bfrackie

become root

su - postgres

----------

## >Octoploid<

# su postgres 

should help  :Very Happy: 

----------

## zambizzi

Yeah, I had done that already, sorry, forgot to mention.

Here's a full rundown on what I did:

```

root@homer vjenks # su - postgres

postgres@homer postgresql $ createuser -P system

Enter password for new user:

Enter it again:

Shall the new user be allowed to create databases? (y/n) y

Shall the new user be allowed to create more new users? (y/n) n

Password:

createuser: could not connect to database template1: FATAL:  Password authentication failed for user "postgres"

```

----------

## bfrackie

Check your pg_hba.conf which should allow a local connect, mine looks like:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD

local   all         all                                             trust

# IPv4-style local connections:

host    all         all         127.0.0.1         255.255.255.255   trust

----------

## zambizzi

No luck, here's my conf file:

```

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD

local   all         all                                             trust

host    crx-CRX     system      10.0.0.0          255.255.255.0     password

host    all         postgres    10.0.0.0          255.255.255.0     password

# IPv4-style local connections:

host    all         all         127.0.0.1         255.255.255.255   trust

# IPv6-style local connections:

#host    all         all         ::1               ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff        trust

```

I'm trying to install openCRX (www.opencrx.org) and that's what the "system" user is for.

----------

## kazakhan

This may be a lame question, but have you run initdb yet?

----------

## zambizzi

 *kazakhan wrote:*   

> This may be a lame question, but have you run initdb yet?

 

well, I ran this command after installing postgres:

"ebuild /var/db/pkg/dev-db/postgresql-7.4.5-r2/postgresql-7.4.5-r2.ebuild config"

What else should I be doing?

----------

## zambizzi

Can anyone help me on this?

I just unmerged postgres and started all over from the top and I'm still having the same problem.

Here's the steps I performed:

 - emerged postgresql (latest version 7.4.5-r2)

 - ran initdb:

```

postgres@homer postgresql $ initdb -D /var/lib/postgresql/data

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory /var/lib/postgresql/data... ok

creating directory /var/lib/postgresql/data/base... ok

creating directory /var/lib/postgresql/data/global... ok

creating directory /var/lib/postgresql/data/pg_xlog... ok

creating directory /var/lib/postgresql/data/pg_clog... ok

selecting default max_connections... 100

selecting default shared_buffers... 1000

creating configuration files... ok

creating template1 database in /var/lib/postgresql/data/base/1... ok

initializing pg_shadow... ok

enabling unlimited row size for system tables... ok

initializing pg_depend... ok

creating system views... ok

loading pg_description... ok

creating conversions... ok

setting privileges on built-in objects... ok

creating information schema... ok

vacuuming database template1... ok

copying template1 to template0... ok

Success. You can now start the database server using:

    /etc/init.d/postgresql start

```

 - created PGDATA env. variable and ran the postmaster thing:

```

postgres@homer postgresql $ PGDATA=/var/lib/postgresql/data

postgres@homer postgresql $ export PGDATA

postgres@homer postgresql $ echo $PGDATA

/var/lib/postgresql/data

postgres@homer postgresql $ pg_ctl -o -i -l postmaster.log start

postmaster successfully started

```

...and now when I try to create a database:

```

root@homer vjenks # su - postgres

postgres@homer postgresql $ createdb test

Password:

createdb: could not connect to database template1: FATAL:  Password authentication failed for user "postgres"

```

...of course I'm guessing the password, I have no idea what it is and what I'm supposed to do here.  I thought I wasn't even supposed to be prompted for this, I'm not when I "su - postgres", it just logs me in!

Now, here's my pg_hba.conf file:

```

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD

local   all         all                                             trust

host    crx-CRX     system      10.0.0.0          255.255.255.0     password

host    all         postgres    10.0.0.0          255.255.255.0     password

# IPv4-style local connections:

host    all         all         127.0.0.1         255.255.255.255   trust

```

Now, a thought just occurred to me, but it may be stupid   :Smile:    I'm doing this over SSH on a remote server, could that have anything to do w/ the authentication problems?

Thanks for all of your help everyone!!

----------

## kazakhan

I would check the permissions on /var/lib/postgresql.

----------

## beandog

The default password for user postgres is ..... (drum roll) ... blank.  :Smile: 

anyway, here's what you need to do:

login to the server with postgres running it

run psql -U postgres

create a new user:

```
CREATEUSER 'sdibb' WITH PASSWORD 'gentoo' CREATEDB CREATEUSER;
```

psql in as that user, and run DROPUSER or delete user or something like that to ditch postgres user account.  then fix pg_hba.conf, restart postgres and you're good to go.

Steve

----------

## beandog

That last post of mine probably wasn't too helpful... so here we go:

Postgres is an awesome DB, but it really is a pain the arse to get it setup.  There's a few basic things you gotta do:

- setup the database

- turn on tcp_ip so scripts and remote users can connect

- setup the users

- setup the configuration files

Setting up the db is easy -- just emerge, then run ebuild /var/db/pkg/dev-db/postgresql-whatever/postgresql-whatever.ebuild config

That creates the users it uses and fixes permissions all that stuff.  If you already have /var/lib/postgresql and it's foobared, delete it, and run that ebuild config to make things happy.  Oh, and make sure postgresql is stopped if you're "fixing" it, too.

Postgres is unlike all the others where it puts its config files in /var/lib/postgresql/data instead of in /etc/

You can be lazy like me an make symlinks for the only two conf files you'll ever really need to edit:

```
# ln -s /var/lib/postgresql/data/pg_hba.conf /etc/pg_hba.conf

# ln -s /var/lib/postgresql/data/postgresql.conf /etc/postgresql.conf
```

Now, postgres by default does some other weird stuff -- it turns off tcp_ip support so that the only way anyone can connect is locally through psql.  That means scripts wont work either (no phpPgAdmin), even if they are on that same server.

To turn it ON, just edit postgresql.conf and add

```
tcpip_socket = true
```

But I figure you already figured that part out.

Next you gotta setup your users.  Like I said, one user is already created (postgres) who can connect (with unmodified settings) with no password authentication.  If it asks for a password, just hit enter (I think).

Anyway, connect to template1 database, and make your user changes in there.

```
psql -U postgres template1
```

Add your user, with create user and create database privilegess:

```
# CREATE USER 'username' WITH PASSWORD 'password' CREATEUSER CREATEDB;
```

Then .... once that's done, NOW you can edit pg_hba.conf and change the authentication settings.  Also, un-like mysql, you don't need to restart the server or anything for the new user to have effect.

Here's my settings for pg_hba.conf on my desktop.  It hashes the password for lightweight security, but its better than plaintext.  You can also turn on ssl in postgresql.conf if you want and figure out how to do it over ssl if you're logging in remotely.

```
# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD

local   all         all                                             md5

# IPv4-style local connections:

host    all         all         127.0.0.1         255.255.255.255   md5
```

Now restart postgresql (/etc/init.d/postgresql restart) and those config settings will take effect.  Then login with your new user (psql -U my_new_user) and enter the password when prompted.  Create a new database (CREATE DATABASE 'some_database_name') and you're good to go.

That should help.  Best of luck.  :Smile: 

Steve

----------

