Having problems with PostgreSQL on OpenBSD

Cromulent

Cromulent

Cromulent

Soldato
Joined
1 Nov 2007
Posts
7,000
Location
England
I'm having problems getting PostgreSQL setup on my OpenBSD box. I have installed it from the ports tree for OpenBSD 4.3 i386 and that all went fine. Then I initialised the database which also went fine. Then I tried to create a database user and it asked me for a password but I have no idea what password it wants. I've tried the root password, the password for the account I logged in to originally and even the only other user account on the system.

It looks like the port sets the password for _postgresql and postgres but does not tell you what they are. Neither does the README.OpenBSD file.

Anyone have any experience with this?
 
I'd assume the postgres user doesn't have a password at the moment.
Try:
Code: 
su
##Root Password##
passwd postgres
##Set new password for the postgres user##

(PostgreSQL will have created a new user to run its stuff under, pretty basic stuff really)

Cheers

-Leezer-
 
leezer3 said:
I'd assume the postgres user doesn't have a password at the moment.
Try:
Code: 
su
##Root Password##
passwd postgres
##Set new password for the postgres user##
(PostgreSQL will have created a new user to run its stuff under, pretty basic stuff really)

Cheers

-Leezer-
Click to expand...

Ah, didn't think of that. Thanks. I always assumed that if a user was created then it must have a password associated with it. Guess I was wrong :).
 
Just had a chance to try this out and it still does not work. I'm pretty stumped on this one.

This is basically the log of what I have done:
[Sun Oct 26 16:20:48 simon@messerschmitt:~]sudo su -
[Sun Oct 26 16:20:52 root@messerschmitt:~]passwd _postgresql
Changing local password for _postgresql.
New password:
Retype new password:
[Sun Oct 26 16:21:12 root@messerschmitt:~]logout
[Sun Oct 26 16:21:16 simon@messerschmitt:~]su - _postgresql
Password:
$ mkdir /var/postgresql/data
$ initdb -D /var/postgresql/data -U postgres -A md5 -W
The files belonging to this database system will be owned by user "_postgresql".
This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers/max_fsm_pages ... 400kB/20000
creating configuration files ... ok
creating template1 database in /var/postgresql/data/base/1 ... FATAL: could not create shared memory segment: Cannot allocate memory
DETAIL: Failed system call was shmget(key=1, size=1646592, 03600).
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1646592 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 10).
The PostgreSQL documentation contains more information about shared memory configuration.
child process exited with exit code 1
initdb: removing contents of data directory "/var/postgresql/data"
$ logout
sh: logout: not found
$ exit
[Sun Oct 26 16:23:32 simon@messerschmitt:~]sudo shutdown -r now
Shutdown NOW!
shutdown: [pid 30708]
[Sun Oct 26 16:23:44 simon@messerschmitt:~]
*** FINAL System shutdown message from [email protected]l ***
System going down IMMEDIATELY



System shutdown time has arrived
Connection to 192.168.1.15 closed by remote host.
Connection to 192.168.1.15 closed.
typhoon:~ simon$ ssh [email protected]
ssh: connect to host 192.168.1.15 port 22: Connection refused
typhoon:~ simon$ ssh [email protected]
[email protected]'s password:
Last login: Sun Oct 26 16:22:14 2008 from typhoon.local
OpenBSD 4.3 (GENERIC) #2: Wed Oct 22 22:43:28 BST 2008

Welcome to OpenBSD: The proactively secure Unix-like operating system.

Please use the sendbug(1) utility to report bugs in the system.
Before reporting a bug, please try to reproduce it with the latest
version of the code. With bug reports, please try to ensure that
enough information to reproduce the problem is enclosed, and if a
known fix for it exists, include that as well.

[Sun Oct 26 16:25:14 simon@messerschmitt:~]top
[Sun Oct 26 16:25:36 simon@messerschmitt:~]su - _postgresql
Password:
$ initdb -D /var/postgresql/data -U postgres -A md5 -W
The files belonging to this database system will be owned by user "_postgresql".
This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 40
selecting default shared_buffers/max_fsm_pages ... 28MB/179200
creating configuration files ... ok
creating template1 database in /var/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
Enter new superuser password:
Enter it again:
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

Success. You can now start the database server using:

postgres -D /var/postgresql/data
or
pg_ctl -D /var/postgresql/data -l logfile start

$ pg_ctl -D /var/postgresql/data -l logfile start
server starting
$ createuser simon
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
Password:
createuser: could not connect to database postgres: FATAL: password authentication failed for user "_postgresql"
$ createuser simon
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
Password:
createuser: could not connect to database postgres: FATAL: password authentication failed for user "_postgresql"
$ createuser simon
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
Password:
Password:
Password:
createuser: could not connect to database postgres: FATAL: password authentication failed for user "_postgresql"
$ createuser simon
Shall the new role be a superuser? (y/n) y
Password:
createuser: could not connect to database postgres: FATAL: password authentication failed for user "_postgresql"
$ createuser simon
Shall the new role be a superuser? (y/n) y
Password:
createuser: could not connect to database postgres: FATAL: password authentication failed for user "_postgresql"
$ exit
Click to expand...
I've probably missed something stupid, so if you spot it let me know :).
 
I think access permissions are your current problem :)
Try this in /etc/pg_hba.conf:
Code: 
local all all password
host all all 127.0.0.1 255.255.255.255 password
host all all 0.0.0.0 255.255.255.255 reject
This'll enable DB access to any valid local user.

Don't ask me on security etc. implications, that's not my field of knowledge, but I think it'll fix the immediate problem.

-Leezer-
 
You must log in or register to reply here.
Back
Top Bottom