DaemonForums  

Go Back   DaemonForums > OpenBSD > OpenBSD Packages and Ports

OpenBSD Packages and Ports Installation and upgrading of packages and ports on OpenBSD.

Reply
 
Thread Tools Display Modes
  #1   (View Single Post)  
Old 10th January 2013
ershiba ershiba is offline
Loading, please wait...
 
Join Date: Dec 2012
Posts: 34
Default postgresql - log into psql without password problem

hi bsd members,

i got the following issue that somehow i think kinda weird,

i pkg_add postgresql-server
Code:
$ sudo su - _postgresql
$ /usr/local/bin/initdb -D /var/postgresql/data
// start the postgresql server
$ /usr/local/bin/postgres -D /var/postgresql/data

$ whoami
$ _postgresql
$ psql postgres
postgres=# create role pgoem login password 'pgoempasswod' createdb valid until 'infinity';

// restart openbsd
// i use pgAdmin III to connect postgresql
it asks for password when i use pgoem user to connect ( ok, this should be how things done )

// using putty, i log into OpenBSD using my OpenBSD username 'oem'
// then i tried the following
$ psql -U pgoem postgres
psql (9.1.4)
Type "help" for help.
postgres=> CREATE DATABASE testing;
CREATE DATABASE
i use pgAdmin III to check, a database "testing" was created with owner set to pgoem,

but how could pgoem without using password, able to access postgres database and able to allow create a database? isn't it weird?
Reply With Quote
  #2   (View Single Post)  
Old 10th January 2013
ershiba ershiba is offline
Loading, please wait...
 
Join Date: Dec 2012
Posts: 34
Default

i think maybe this line is important from
$ vi /var/postgresql/data/pg_hba.conf
Code:
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             192.168.56.0/24         md5

$ vi /var/postgresql/data/postgresql.conf
Code:
# - Connection Settings -
listen_addresses = '192.168.56.200,127.0.0.1'
these are the only 2 files i modified,
ifconfig as below
Code:
em0: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> mtu 1500
        lladdr 08:00:27:78:0b:93
        priority: 0
        groups: egress
        media: Ethernet autoselect (1000baseT full-duplex)
        status: active
        inet6 fe80::a00:27ff:fe78:b93%em0 prefixlen 64 scopeid 0x1
        inet 10.0.2.15 netmask 0xffffff00 broadcast 10.0.2.255
em1: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> mtu 1500
        lladdr 08:00:27:c8:40:b9
        priority: 0
        media: Ethernet autoselect (1000baseT full-duplex)
        status: active
        inet 192.168.56.200 netmask 0xffffff00 broadcast 192.168.56.255
        inet6 fe80::a00:27ff:fec8:40b9%em1 prefixlen 64 scopeid 0x2
Reply With Quote
  #3   (View Single Post)  
Old 10th January 2013
ershiba ershiba is offline
Loading, please wait...
 
Join Date: Dec 2012
Posts: 34
Default

i change /var/postgresql/data/pg_hba.conf
previously, (default pg_hba.conf auth-method)
Code:
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             192.168.56.0/24         md5
i change to below
Code:
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.56.0/24         md5
i change all the trust to md5,
now it seems psql will auto prompt password,
there are a few things that i don't get it,

1. to be secure out of box (OpenBSD concept), shouldn't it be no trust in pg_hba.conf?

2. should there be ( i suggest ) a script that after initdb to prompt user to change _postgresql database user password? (something like the /usr/local/bin/mysql_secure_installation) ?

3. i assume while using putty from 192.168.56.X connect to OpenBSD and run psql, psql connect to database using bsd socket, so the "trust" there, if that is the case, which mean, any user who is able to guess postgresql database username would able to connect to pgdb.
Reply With Quote
  #4   (View Single Post)  
Old 10th January 2013
jggimi's Avatar
jggimi jggimi is offline
More noise than signal
 
Join Date: May 2008
Location: USA
Posts: 7,975
Default

Quote:
3. i assume while using putty from 192.168.56.X connect to OpenBSD and run psql, psql connect to database using bsd socket, so the "trust" there, if that is the case, which mean, any user who is able to guess postgresql database username would able to connect to pgdb.
You are correct. "Trust" means that you trust all users.

It is up to you, the administrator, to ensure you have configured your applications and database systems with appropriate security. For example, in my PostgreSQL environment:
  • Only "md5" authentication method is used for all connections in pg_hba.conf. "Trust" is never used.
  • The only SQL connections permitted are from the application servers and a local Unix socket for administration.
  • The socket file is chmod 044, chown dba:_postgresql and the dba group contains only _postgresql and my userid.
  • The permitted network connections are restricted by both pg_hba.conf and by PF configuration.
Reply With Quote
  #5   (View Single Post)  
Old 10th January 2013
ershiba ershiba is offline
Loading, please wait...
 
Join Date: Dec 2012
Posts: 34
Default

i seriously don't expect people would allow such "trust" configuration in default conf, kinda blow your mind sometime,
Reply With Quote
  #6   (View Single Post)  
Old 10th January 2013
jggimi's Avatar
jggimi jggimi is offline
More noise than signal
 
Join Date: May 2008
Location: USA
Posts: 7,975
Default

The postgresql.conf and pg_hba.conf files are deployed as delivered by the PostgreSQL Global Development Group. They state (in their FAQ), "By default, new clusters are created with the 'trust' scheme, where any local user is allowed to connect to the database." It is my assumption that they do this for ease of deployment.

Some packagers of PostgreSQL change this default. The OpenBSD port does not. Instead, the port's $MAINTAINER includes a README-server file that includes some discusson of security, authentication, and encryption among other advice.

You may, if you wish, contact the port $MAINTAINER to discuss altering the defaults. Or you may contact the PostgreSQL Global Development Group and recommend changing their default distribution.

In my case, I would review and revise access and authentication controls to meet my needs in any case, and not assume defaults meet my security requirements.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
postgresql VS mysql .. when which is better ? daemonfowl General software and network 3 12th September 2012 05:30 AM
Why do we need password to shutdown the box ? sw2wolf FreeBSD General 3 22nd May 2012 04:35 PM
Set password for Folder mfaridi FreeBSD Security 6 5th September 2008 10:49 PM
PostgreSql rc.d script fridder FreeBSD Ports and Packages 1 1st July 2008 01:12 AM
postgresql chinese character problem gosha OpenBSD Packages and Ports 2 8th June 2008 09:37 PM


All times are GMT. The time now is 08:49 AM.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Content copyright © 2007-2010, the authors
Daemon image copyright ©1988, Marshall Kirk McKusick