Cannot connect to postgresql on server!

[mustafa]

I installed alfresco 4.2.c bundle ona linux64 machine. I want to connect to postgre by pgadmin 3 and manipulate the tables but I cannot. I need help.


IP of the machine which alfresco bundle is installed is 10.10.17.72, tomcat, postgre and alfresco are running good and healthy 😃
My ip is 10.10.43.65

dbname : alfresco
host :   10.10.17.72
port:    5432
ssl :
Maintanence db   :  postgres

username : alfresco
password : alfresco
DB restriction :
Service :

I entered these as connection properties, but getting this error

Access to database denied
The server doesn't grant access to the database: the server reports 
FATAL: no pg_hba.conf entry for host "10.10.43.65", user "alfresco", database "postgres", SSL off 
To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated. 
The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings. You'll probably want to add something like 
host all all 192.168.0.0/24 md5 
This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24. 
You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file. After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process. 
‍‍‍‍‍‍‍‍‍‍

Thanks in advance…

[bopolissimus]

if your bundle is installed at /opt/alfresco-4.2.c

cd /opt/alfresco-4.2.c/alf_data/postgresql
edit (as sudo or as the owner of the file) pg_hba.conf

go to the end of the file and add:

host   postgres    alfresco    10.10.43.65/32     md5

and restart postgres (or just restart alfresco, simpler that way).

[mustafa]

Bopolissimus

I did that. Now I have this error ;

An Error has occured:
Error connecting to the server: FATAL: password authentication failed for user "alfresco"

‍‍‍‍‍‍

is there a different default password?

[bopolissimus]

Sorry, I had a typo. The pg_hba.conf line should instead be:

host alfresco alfresco 10.10.43.65/32 md5

after a restart, you should now be able to connect to postgres as the alfresco user.  try this:

  cd /opt/alfresco-4.2.c/postgresql/bin/
  psql -h localhost -U alfresco alfresco

If that succeeds, alfresco will be able to connect to postgresql.

alternatively, if this is just for testing, you can do this:

host alfresco alfresco 10.10.43.65/32 trust

and restart alfresco.

that way, it'll always let you in regardless of password.

[mustafa]

Thanks Bopolissimus.=)
I can connect now. But still getting errors.

ERROR: column "datconfig" does not exist
LINE 1: …b.dattablespace AS spcoid, spcname, datallowconn, datconfig, …

‍‍‍‍‍

And the solution is to update pgadmin 1.8  to pgadmin 1.6.1
I have no idea why 1.6.1 is more up to date but it is =/

Now I can do anything . Thanks again Bopo 😃