Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
[SOLVED]Postgresql-DBI connect 'dbname=template1' Permission
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Networking & Security
View previous topic :: View next topic  
Author Message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2571
Location: Edmonton, AB

PostPosted: Thu Dec 15, 2011 2:22 am    Post subject: [SOLVED]Postgresql-DBI connect 'dbname=template1' Permission Reply with quote

When I try to create dataset in SQL-Ledger I get an error:
Code:
ERROR - could not connect to server: Permission denied
Is the server running locally and accepting connection to Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?


pg_hba.conf
Code:
local   all    all     trust


On the SQL-Ledger Accounting / Database Admin.

When I enter Host = localhost (host should be blank)
and click "create dataset" I can connect.

This is new installation.

eselect postgresql list
Code:
Available PostgreSQL Slots
  9.1 *                      server-9.1.1 base-9.1.1

/etc/init.d/postgresql-9.1 status
Code:
* Reloading PostgreSQL configuration ...
pg_ctl: server is running (PID: 2377)
/usr/lib64/postgresql-9.1/bin/postgres "-D" "/var/lib/postgresql/9.1/data" "-D" "/etc/postgresql-9.1/" "--data-directory=/var/lib/postgresql/9.1/data" "--silent-mode=true"                                            OK

$ groups
wheel mail audio cdrom postgres cdrw usb users joseph

cat /etc/conf.d/postgresql-9.1
Code:
# Which port and socket to bind PostgreSQL
PGPORT="5432"

# Allow *_TIMEOUT to run its course.
# Disable timeouts by changing to '-W' (capital W)
WAIT_FOR_START="-w"
WAIT_FOR_STOP="-w"

# How long to wait for server to start in seconds
START_TIMEOUT=60

# Ignore new connections and wait for clients to disconnect from server before
# shutting down.
# Set NICE_QUIT to "NO" to disable. NICE_TIMEOUT in seconds.
NICE_QUIT="YES"
NICE_TIMEOUT=60

# Forecfully disconnect clients from server and shut down. This is performed
# after NICE_QUIT. Terminating a client results in a rollback of open
# transactions for that client.
# Set RUDE_QUIT to "NO" to disable. RUDE_TIMEOUT in seconds.
RUDE_QUIT="YES"
RUDE_TIMEOUT=30

# If the server still fails to shutdown, you can force it to quit by setting
# this to yes and a recover-run will execute on the next startup.
# Set FORCE_QUIT to "YES" to enable. FORCE_TIMEOUT in seconds.
FORCE_QUIT="NO"
FORCE_TIMEOUT=2

# Extra options to run postmaster with, e.g.:
# -N is the maximal number of client connections
# -B is the number of shared buffers and has to be at least 2x the value for -N
# Please read the man-page to postmaster for more options. Many of
# these options can be set directly in the configuration file.
#PGOPTS="-N 512 -B 1024"

# Pass extra environment variables. If you have to export environment
# variables for the database process, this can be done here.
# Don't forget to escape quotes.
#PG_EXTRA_ENV="PGPASSFILE=\"/path/to/.pgpass\""

##############################################################################
#
# The following values should not be arbitrarily changed.
# emerge --config dev-db/postgresql-server:9.1 uses these values to
# determine where to create the data directory, where to place the
# configuration files and any additional options you'd like to pass to initdb.
#
# The init script also uses these variables to inform pg_ctl where to find
# the same data and configuration files.
#
##############################################################################

# Location of configuration files
PGDATA="/etc/postgresql-9.1/"

# Where the data directory is located/to be created
DATA_DIR="/var/lib/postgresql/9.1/data"

# Additional options to pass to initdb.
# See 'man initdb' for available options.
PG_INITDB_OPTS="--locale=en_US.UTF-8"
Code:
# psql -p 5432 -U postgres
psql (9.1.1)
Type "help" for help.

postgres=#


What to look for?
In apache error.log I get:
Code:
[error] [client 127.0.0.1] \tconnections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? at SL/User.pm line 253, referer: http://localhost/sql-ledger/admin.pl

_________________
#Thelma


Last edited by Joseph_sys on Mon Dec 19, 2011 10:26 pm; edited 4 times in total
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2571
Location: Edmonton, AB

PostPosted: Thu Dec 15, 2011 3:22 am    Post subject: Reply with quote

I seen another error in apache log:
Code:
[client 127.0.0.1] DBI connect('dbname=template1','sql-ledger',...) failed: could not connect to server: Permission denied, referer: http://localhost/sql-ledger/admin.pl


What to do with it?
_________________
#Thelma
Back to top
View user's profile Send private message
destroyedlolo
l33t
l33t


Joined: 17 Jun 2011
Posts: 758
Location: Close to Annecy (France)

PostPosted: Thu Dec 15, 2011 9:00 am    Post subject: Reply with quote

Are U sure you're apache user is part of postgres group ?
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2571
Location: Edmonton, AB

PostPosted: Thu Dec 15, 2011 1:02 pm    Post subject: Reply with quote

destroyedlolo wrote:
Are U sure you're apache user is part of postgres group ?


Yes, this is OK
Code:
su - postgres
postgres@syscon5 ~ $ groups
postgres

This is the same setup as on my other systems that are working.
_________________
#Thelma
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2571
Location: Edmonton, AB

PostPosted: Sat Dec 17, 2011 4:41 am    Post subject: Reply with quote

SOLVED!

Installed Debian stable :-/ and everything is working.
_________________
#Thelma
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2571
Location: Edmonton, AB

PostPosted: Sun Dec 18, 2011 6:03 pm    Post subject: Reply with quote

I reinstall Gentoo from standard installation CD and I'm having the same problem?
Run out of ideas what else to try besides jumping the distros :-/
_________________
#Thelma
Back to top
View user's profile Send private message
Ant P.
Watchman
Watchman


Joined: 18 Apr 2009
Posts: 6714

PostPosted: Sun Dec 18, 2011 8:40 pm    Post subject: Reply with quote

Joseph_sys wrote:
destroyedlolo wrote:
Are U sure you're apache user is part of postgres group ?


Yes, this is OK
Code:
su - postgres
postgres@syscon5 ~ $ groups
postgres

This is the same setup as on my other systems that are working.

You're running apache as the user "postgres" then? What are the permissions on the socket it can't connect to?
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2571
Location: Edmonton, AB

PostPosted: Sun Dec 18, 2011 8:48 pm    Post subject: Reply with quote

Ant P. wrote:
Joseph_sys wrote:
destroyedlolo wrote:
Are U sure you're apache user is part of postgres group ?


Yes, this is OK
Code:
su - postgres
postgres@syscon5 ~ $ groups
postgres

This is the same setup as on my other systems that are working.

You're running apache as the user "postgres" then? What are the permissions on the socket it can't connect to?


I think this is the one you are asking about.
Code:
ll /var/run/postgresql/.s.PGSQL.5432
srwxrwxrwx 1 postgres postgres 0 Dec 18 20:08 /var/run/postgresql/.s.PGSQL.5432

The sql-ledger script is OK as it is working on my other Gentoo boxes and I tired to install Debian and the setup is working without any problem.
So it must be something to do with new Gentoo installation. I reinstall Gentoo for the second time on this box and the same problem :-/
_________________
#Thelma
Back to top
View user's profile Send private message
Ant P.
Watchman
Watchman


Joined: 18 Apr 2009
Posts: 6714

PostPosted: Mon Dec 19, 2011 8:07 pm    Post subject: Reply with quote

The process using the database needs read access on the directory as well. If the apache httpd is actually running as postgres:postgres then the default permissions (drwxr-x--- postgres:postgres) would mean it should have already been working.
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2571
Location: Edmonton, AB

PostPosted: Mon Dec 19, 2011 8:26 pm    Post subject: Reply with quote

Ant P. wrote:
The process using the database needs read access on the directory as well. If the apache httpd is actually running as postgres:postgres then the default permissions (drwxr-x--- postgres:postgres) would mean it should have already been working.


Apache is running as "apache:apache" and directory permission and owner ship is the same one as on my working system, so I don't think this is an issue:

Working system:
Code:
ll /var/lib/postgresql/9.1/
total 4
drwx------ 13 postgres postgres 4096 Dec 14 19:57 data
syscon4 joseph # ll /var/lib/postgresql/9.1/data/
total 64
drwx------ 9 postgres postgres 4096 Dec  7 22:57 base
drwx------ 2 postgres postgres 4096 Dec 14 19:58 global
drwx------ 2 postgres postgres 4096 Nov 27 23:23 pg_clog
drwx------ 4 postgres postgres 4096 Nov 27 23:23 pg_multixact
drwx------ 2 postgres postgres 4096 Dec 14 19:57 pg_notify
drwx------ 2 postgres postgres 4096 Nov 27 23:23 pg_serial
drwx------ 2 postgres postgres 4096 Dec 19 13:25 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Dec  7 23:07 pg_subtrans
drwx------ 2 postgres postgres 4096 Nov 27 23:23 pg_tblspc
drwx------ 2 postgres postgres 4096 Nov 27 23:23 pg_twophase
-rw------- 1 postgres postgres    4 Nov 27 23:23 PG_VERSION
drwx------ 3 postgres postgres 4096 Dec 12 21:17 pg_xlog
-rw------- 1 postgres postgres 4818 Dec 14 19:57 postmaster.log
-rw------- 1 postgres postgres  172 Dec 14 19:57 postmaster.opts
-rw------- 1 postgres postgres  101 Dec 14 19:57 postmaster.pid


NOT working system:
Code:
ll /var/lib/postgresql/9.1/
total 4
drwx------ 13 postgres postgres 4096 Dec 19 13:21 data
syscon7 joseph # ll /var/lib/postgresql/9.1/data/
total 64
drwx------ 8 postgres postgres 4096 Dec 18 22:06 base
drwx------ 2 postgres postgres 4096 Dec 19 13:22 global
drwx------ 2 postgres postgres 4096 Dec 18 22:30 pg_clog
drwx------ 4 postgres postgres 4096 Dec 18 22:30 pg_multixact
drwx------ 2 postgres postgres 4096 Dec 19 13:21 pg_notify
drwx------ 2 postgres postgres 4096 Dec 18 22:30 pg_serial
drwx------ 2 postgres postgres 4096 Dec 19 13:26 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Dec 18 18:07 pg_subtrans
drwx------ 2 postgres postgres 4096 Dec 18 22:30 pg_tblspc
drwx------ 2 postgres postgres 4096 Dec 18 22:30 pg_twophase
-rw------- 1 postgres postgres    4 Dec 18 22:30 PG_VERSION
drwx------ 3 postgres postgres 4096 Dec 18 18:07 pg_xlog
-rw------- 1 postgres postgres 6922 Dec 19 13:21 postmaster.log
-rw------- 1 postgres postgres  172 Dec 19 13:21 postmaster.opts
-rw------- 1 postgres postgres  100 Dec 19 13:21 postmaster.pid

_________________
#Thelma
Back to top
View user's profile Send private message
Ant P.
Watchman
Watchman


Joined: 18 Apr 2009
Posts: 6714

PostPosted: Mon Dec 19, 2011 10:13 pm    Post subject: Reply with quote

Joseph_sys wrote:
Apache is running as "apache:apache"

And what does "ls -ld /var/run/postgresql/" say?
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2571
Location: Edmonton, AB

PostPosted: Mon Dec 19, 2011 10:25 pm    Post subject: Reply with quote

Ant P. wrote:
Joseph_sys wrote:
Apache is running as "apache:apache"

And what does "ls -ld /var/run/postgresql/" say?


Yes, you are correct; THANK YOU and this is a big THANK YOU!

On working server I had:
drwxrwx--x 2 postgres postgres 4096 Dec 14 19:57 /var/run/postgresql/

On new installation it was:
drwxrwx--- 2 postgres postgres 4096 Dec 19 13:21 /var/run/postgresql/

Now it is working.
Damn, such a small permission and I waisted 2-days :-/
This was a hard lesson.

Is it a but in installation?
_________________
#Thelma
Back to top
View user's profile Send private message
pigeon768
l33t
l33t


Joined: 02 Jan 2006
Posts: 679

PostPosted: Wed Dec 21, 2011 4:58 pm    Post subject: Reply with quote

Joseph_sys wrote:
destroyedlolo wrote:
Are U sure you're apache user is part of postgres group ?
Yes, this is OK
Code:
su - postgres
postgres@syscon5 ~ $ groups
postgres
This is the same setup as on my other systems that are working.
Joseph_sys wrote:
Apache is running as "apache:apache"
Note that you checked the group membership of the postgres user, not the group membership of the apache user.
Joseph_sys wrote:
Is it a but in installation?
No. If the apache user was a member of the postgres group, which it should have been, you would not have had this problem. The postgresql-server ebuild explicitly tells you to add the users who need access to the postgres server to the postgres group.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Networking & Security All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum