PostgreSQL Discussion - Access Problem After Version Upgrade

Author

Access Problem After Version Upgrade

Rich Shepard

2006-01-26, 4:57 pm

Last week I upgraded from -7.4.3 to -8.1.2. I had some problems moving data
because of both my ignorance of the proper syntax and the move from
/usr/local/pgsql to /var/lib/pgsql. Now I cannot access the server:

[rshepard@salmo ~]$ psql -l
psql: could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

This also prevents me from logging in to SQL-Ledger and other applications.
Now, I don't know that the server is accepting connections, but
srwxr-xr-x 1 root root 0 2006-01-21 14:53 /tmp/.s.PGSQL.5432=
exists as a socket.

What do I do to trace the source of this problem and fix it?

TIA,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sean Davis

2006-01-26, 4:57 pm

On 1/26/06 11:53 AM, "Rich Shepard" <rshepard@appl-ecosys.com> wrote:

> Last week I upgraded from -7.4.3 to -8.1.2. I had some problems moving data
> because of both my ignorance of the proper syntax and the move from
> /usr/local/pgsql to /var/lib/pgsql. Now I cannot access the server:
>
> [rshepard@salmo ~]$ psql -l
> psql: could not connect to server: Permission denied
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
>
> This also prevents me from logging in to SQL-Ledger and other applications.
> Now, I don't know that the server is accepting connections, but
> srwxr-xr-x 1 root root 0 2006-01-21 14:53 /tmp/.s.PGSQL.5432=
> exists as a socket.
>
> What do I do to trace the source of this problem and fix it?

You did start the server? Did you fix the pg_hba.conf file? Does your
postgresql.conf file allow tcp connections? Those are the places I would
look.

Sean

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Rich Shepard

2006-01-26, 4:57 pm

On Thu, 26 Jan 2006, Sean Davis wrote:

> You did start the server? Did you fix the pg_hba.conf file? Does your
> postgresql.conf file allow tcp connections? Those are the places I would
> look.

Sean,

Thank you.

Yes, the server is running:
21839 ? S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data

I don't know what needs fexing in /var/lib/pgsql/data/pg_hba.conf. It has:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust

which should cover both socket and tcp connections, if I understand
correctly.

The header of postgresql.conf says that the commented options are defaults,
and the connections section has:

#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
#port = 5432
max_connections = 100
# note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transa
ction). You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_
connections = 2
#unix_socket_directo
ry = ''
#unix_socket_group = ''
#unix_socket_permiss
ions = 0777 # octal
#bonjour_name = '' # defaults to the computer name

# - Security & Authentication -

#authentication_time
out = 60 # 1-600, in seconds
#ssl = off
#password_encryption
= on
#db_user_namespace = off

Is this correct?

Thanks again,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2006-01-26, 4:57 pm

Rich Shepard <rshepard@appl-ecosys.com> writes:
> Now, I don't know that the server is accepting connections, but
> srwxr-xr-x 1 root root 0 2006-01-21 14:53 /tmp/.s.PGSQL.5432=
> exists as a socket.

> What do I do to trace the source of this problem and fix it?

That's got the wrong ownership (should be postgres not root) and
the wrong permissions (should be world-writable). Did somebody
do something silly like chown everything in /tmp?

You could manually fix the socket file's ownership and permissions,
or just remove it and restart the postmaster.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Rich Shepard

2006-01-26, 4:57 pm

On Thu, 26 Jan 2006, Tom Lane wrote:

> That's got the wrong ownership (should be postgres not root)

Tom,

I wondered about that.

> and the wrong permissions (should be world-writable). Did somebody do
> something silly like chown everything in /tmp?

Not me ... intentionally.

> You could manually fix the socket file's ownership and permissions, or just
> remove it and restart the postmaster.

I did the permissions before and just did the ownership.

Still no happiness with SL.

Thanks very much,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links