POSTGRESQL on localnetwork

[loulou2522]

In my local network
I install POSTGRESQL on localhost port 5432

I put in pg_hba_conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local & remote connections:
host all all localhost trust
host all all 127.0.0.1/32 trust
host all all 192.168.1.xx/24 trust (which is the adress of the computer where i want to access to database
IPv6 local connections:
host all all ::1/128 trust
and in Postgres.con
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)

But when i try to access to the database from 192.168.1;XX
the system refuse the connection

[infratec]

Linux?
iptables?

Check first if it really listen on your port:

netstat -anp|grep 5432

Bernd

[loulou2522]

Linux?
iptables?

Check first if it really listen on your port:

netstat -anp|grep 5432

Bernd

No windows 10 when typing netstat -anp the systéme respond me no active connection
What is iptables

[Marc56us]

First of all: typing errors ?

I put in pg_hba_conf

pg_hba.conf

and in Postgres.con

Postgres.conf

[Marc56us]

what is the result of DataBaseError()

With
host=localhost
host=192.168.1.100

(sample. Replace with your ip dans databasesuser/password)

Code: Select all

UsePostgreSQLDatabase()

If OpenDatabase(0, "host=192.168.1.100 port=5432", "postgres", "postgres")
     Debug "Connected PostgreSQL"
Else
     Debug "DataBase Error: " + DatabaseError()
EndIf

If good config: 127.0.0.1 can connect
if wrong config: your IP

could not connect to server: Connection timed out (0x0000274C/10060)
Is the server running on host "192.168.1.100" and accepting
TCP/IP connections on port 5432?
or (french version)
DataBase Error: FATAL: authentification par mot de passe échouée pour l'utilisateur « xxx »

(at leat 1 min before server respond)

[Marc56us]

I didn't even see that

Code: Select all

host all all 192.168.1.xx/24

Code: Select all

host all all 192.168.1.0/24

(No need to restart server of course)

https://docs.postgresql.fr/9.6/client-a ... ation.html

[loulou2522]

In fact after testing
It works with 192.168.1.38 (IP of server)
but not works with localhost
Is-it normal or i have to reinstall my servers ?

[Marc56us]

localhost is alias of 127.0.0.1 (lookback ip)
So connection using 'localhost' server name is only possible if client is on the same machine as server.

[loulou2522]

Thanks Marc56us,
Did you know a book for POSTGRESQL ? Because i began with postgresql.
I have another problem when I launch server with use postgres and a affected the create database to a specific usr with role, create table affect CREATE table to postgres Database and not to my own database how can i do ?

I find a solution but it is not clean

Code: Select all

ouverture.l = OpenDatabase(#PB_Any, "host=localhost port=5432", "postgres", "postgres",#PB_Database_PostgreSQL)
        sql$= "CREATE USER toto With PASSWORD 'toto';CREATE DATABASE totoessai With OWNER=toto;"
        DatabaseUpdate(ouverture,sql$)
        CloseDatabase(ouverture)
        ouverture.l = OpenDatabase(#PB_Any, "host=192.168.1,100 port=5432 dbname=totoessai", "toto", "toto",#PB_Database_PostgreSQL) 

Have you n idea about that ?

[Marc56us]

when I launch server with use postgres and a affected the create database to a specific usr with role, create table affect CREATE table to postgres Database and not to my own database

I'm not sure to understand what you want to do (create table or database ?)
You cannot switch to another database in SQL command

In command line (psql.exe) you can change active connection with
\c database
or
\connect database

---
Fr
Je ne suis pas sûr de comprendre ce que tu veux faire (créer une table ou une base de données?)
Tu ne peux pas changer la connexion active par une commande SQL (il faut ouvrir une autre connexion)
C'est ce que tu as bien fait.

Dans le shell PostgreSQL (psql. exe) tu peux, par contre changer la connexion active avec la base de donnée
\c database
ou
\connect database

[normeus]

you need to select the database when you open:

Code: Select all

    If OpenDatabase(1,"host=localhost port=5432 dbname=schoolmgmt","drugstorecowboy","password") = 0
      Debug "cannot open"
    Else
;      DISCO with the database
endif

Code: Select all

ouverture.l = OpenDatabase(#PB_Any, "host=localhost port=5432 dbname=NameofYourDataBase", "postgres", "postgres",#PB_Database_PostgreSQL)

@BluezNL has a nice PureBasic tutorial with some explanation of Databases:
http://ninelizards.com/purebasic/pureba ... e_to_mysql

Norm.

[Paul]

I made a small module that I use myself for doing all the work behind the scenes.
Maybe it will help here...

Module... save as "_Postgres.pbi"

Code: Select all

DeclareModule Postgres
  UsePostgreSQLDatabase()
  Declare.i pDB_Connect(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s)
  Declare   pDB_Disconnect(dbID.i)
  Declare.s pDB_CreateDB(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,dbnew.s,dbowner.s)
  Declare.s pDB_DeleteDB(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,dbdelete.s)
  Declare.s pDB_CreateUser(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,newuser.s,newpassword.s)
  Declare.s pDB_DeleteUser(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,userdelete.s)
EndDeclareModule

Module Postgres
  Procedure.s DBQuery(dbID.i,Query.s)
    Protected result$=""
    If DatabaseQuery(dbID,Query)
      result$="OK: "+Query
      FinishDatabaseQuery(dbID)
      Else
      result$="Error: "+DatabaseError()
    EndIf
    ProcedureReturn result$
  EndProcedure

  Procedure.i pDB_Connect(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s)
    ProcedureReturn OpenDatabase(#PB_Any, "host="+dbhost+" port="+dbport+" dbname="+dbname,dbuser,dbpassword,#PB_Database_PostgreSQL)
  EndProcedure

  Procedure pDB_Disconnect(dbID.i)
    CloseDatabase(dbID)
  EndProcedure

  Procedure.s pDB_CreateDB(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,dbnew.s,dbowner.s)
    Protected qry.s,result$
    hDB=pDB_Connect(dbhost,dbport,dbname,dbuser,dbpassword)
    If hDB  
      qry="create database "+dbnew+" owner="+dbowner+";"
      result$=DBQuery(hDB,qry)
      pDB_Disconnect(hDB)
    EndIf
    ProcedureReturn result$
  EndProcedure 
  
  Procedure.s pDB_DeleteDB(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,dbdelete.s)
    Protected qry.s,result$
    hDB=pDB_Connect(dbhost,dbport,dbname,dbuser,dbpassword)
    If hDB
      qry="drop database "+dbdelete+";"
      result$=DBQuery(hDB,qry)
      pDB_Disconnect(hDB)
    EndIf
    ProcedureReturn result$
  EndProcedure 
  
  Procedure.s pDB_CreateUser(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,newuser.s,newpassword.s)
    Protected qry.s,result$
    hDB=pDB_Connect(dbhost,dbport,dbname,dbuser,dbpassword)
    If hDB
      qry="create user "+newuser+" With CREATEDB PASSWORD '"+newpassword+"';"
      result$=DBQuery(hDB,qry)
      pDB_Disconnect(hDB)
    EndIf
    ProcedureReturn result$
  EndProcedure

  Procedure.s pDB_DeleteUser(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,userdelete.s)
    Protected qry.s,result$
    hDB=pDB_Connect(dbhost,dbport,dbname,dbuser,dbpassword)
    If hDB
      qry="drop user "+userdelete+";"
      result$=DBQuery(hDB,qry)
      pDB_Disconnect(hDB)
    EndIf
    ProcedureReturn result$
  EndProcedure   
EndModule

UseModule Postgres

Example code...

Code: Select all

XIncludeFile "_Postgres.pbi"


;Example #1
;Create User & Database --- must use Admin Account & Password to create New User 
;and we'll say my Postgres Server is located at 192.168.0.101
Debug pDB_CreateUser("192.168.0.101",5432,"postgres","postgres","password","jack","black")
Debug pDB_CreateDB("192.168.0.101",5432,"postgres","jack","black","mydatabase","jack")  


;Example #2
;Put Data into Database
dbID=pDB_Connect("192.168.0.101",5432,"mydatabase","jack","black")
If dbID
  If DatabaseQuery(dbID,"CREATE TABLE software (app varchar(50),price double precision);")
    DatabaseQuery(dbID,"INSERT INTO software (app,price)values('Pac Man',4.99);")
    DatabaseQuery(dbID,"INSERT INTO software (app,price)values('Defender',2.49);")
    DatabaseQuery(dbID,"INSERT INTO software (app,price)values('Asteroids',1.87);")
  EndIf    
  pDB_Disconnect(dbID)
EndIf


;Example #3
;Read Database
dbID=pDB_Connect("192.168.0.101",5432,"mydatabase","jack","black")
If dbID
  If DatabaseQuery(dbID,"SELECT app,price from software ORDER BY price asc;")
    While NextDatabaseRow(dbID)
      Debug GetDatabaseString(dbID,0)+"  $"+GetDatabaseDouble(dbID,1)
    Wend
  EndIf   
  pDB_Disconnect(dbID)
EndIf