# [SOLVED]Postgresql-DBI connect 'dbname=template1' Permission

## Joseph_sys

When I try to create dataset in SQL-Ledger I get an error: 

```
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

```
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

```
Available PostgreSQL Slots

  9.1 *                      server-9.1.1 base-9.1.1
```

/etc/init.d/postgresql-9.1 status

```
* 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 

```
# 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"
```

```
# psql -p 5432 -U postgres

psql (9.1.1)

Type "help" for help.

postgres=# 
```

What to look for?

In apache error.log I get:

```
[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
```

Last edited by Joseph_sys on Mon Dec 19, 2011 10:26 pm; edited 4 times in total

----------

## Joseph_sys

I seen another error in apache log:

```
[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?

----------

## destroyedlolo

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

----------

## Joseph_sys

 *destroyedlolo wrote:*   

> Are U sure you're apache user is part of postgres group ?

 

Yes, this is OK 

```
su - postgres

postgres@syscon5 ~ $ groups

postgres
```

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

----------

## Joseph_sys

SOLVED!

Installed Debian stable :-/ and everything is working.

----------

## Joseph_sys

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 :-/

----------

## Ant P.

 *Joseph_sys wrote:*   

>  *destroyedlolo wrote:*   Are U sure you're apache user is part of postgres group ? 
> 
> Yes, this is OK 
> 
> ```
> ...

 

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

----------

## Joseph_sys

 *Ant P. wrote:*   

>  *Joseph_sys wrote:*    *destroyedlolo wrote:*   Are U sure you're apache user is part of postgres group ? 
> 
> Yes, this is OK 
> 
> ```
> ...

 

I think this is the one you are asking about. 

```
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 :-/

----------

## Ant P.

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.

----------

## Joseph_sys

 *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:

```
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: 

```
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
```

----------

## Ant P.

 *Joseph_sys wrote:*   

> Apache is running as "apache:apache"

 

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

----------

## Joseph_sys

 *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?

----------

## pigeon768

 *Joseph_sys wrote:*   

>  *destroyedlolo wrote:*   Are U sure you're apache user is part of postgres group ? Yes, this is OK 
> 
> ```
> su - postgres
> 
> ...

   *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.

----------

