php connection failure
Readers, Background: http://news-web.php.net/php.general/327600 Since further configuration, remain unable to connect to the php database. Software system is debian, xfce, postgresql, php. Database created on local machine with directory of web server content in extant normal user account. Then created another normal user: --ingroup [extant normal user] --no-create-home --disabled-login [new normal user of same name as postgresql new role created for this database] Please what is the next investigation to make?
Re: php connection failure
On Saturday, August 7th, 2021 at 8:23 PM, Adrian Klaver wrote: > > Since further configuration, remain unable to connect to the php database. > > Further configuration of what? > The postgresql configuration file: SHOW hba_file; /etc/postgresql/9.6/main/pg_hba.conf SHOW config_file; /etc/postgresql/9.6/main/postgresql.conf > After the configuration change did you restart the server? > Yes; the php web page recognised changes, for example removal of the comment instruction: " Generic CPAC Hello World' /*successful test of configuration of apache http server*/ phpinfo(); ?> " > Is the pg_hba.conf file the correct one for the database instance? > Changes were made to the function 'listen_addressess' to the configuration file shown in above. > What does: > > pg_lsclusters > > show? > pg_lsclusters Ver Cluster Port Status OwnerData directory Log file 9.6 main5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
Re: php connection failure
On Saturday, August 7th, 2021 at 10:17 PM, Adrian Klaver wrote: > On 8/7/21 1:09 PM, ourdiaspora wrote: > > > On Saturday, August 7th, 2021 at 8:23 PM, Adrian Klaver > > adrian.kla...@aklaver.com wrote: > > > Changes were made to the function 'listen_addressess' to the configuration > > file shown in above. > > I should have asked in previous post, what was 'listen_addresses' > > changed to? > The postgresql server was restarted, successfully. listen_addresses = 'localhost'
Re: php connection failure
On Saturday, August 7th, 2021 at 11:20 PM, Adrian Klaver wrote: > > 1. OS and version? Same local machine as described > 2. The contents of /etc/hosts 127.0.0.1 localhost ::1 localhost ip6-localhost ip6-loopback ff02::1 ip6-allnodes ff02::2 ip6-allrouters > 3. What happens if you do?: > > psql -d cpacweb -h 127.0.0.1 -U cpaca > psql -d cpacweb -h 127.0.0.1 -U cpaca psql: could not connect to server: Connection refused Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432? sudo /etc/init.d/apache2 status [ ok ] apache2 is running. sudo service postgresql status 9.6/main (port 5432): online
Re: php connection failure
On Sunday, August 8th, 2021 at 12:11 AM, Tom Lane wrote:
>
> If php is trying to connect to something else, like say the
>
> machine's external IP address, that could be your issue.
Excuse the ignorance, but if the instruction in the php file is:
"
...$dbconn = pg_connect("dbname=cpacweb user=cpaca host=localhost")...
"
why should php try to connect to anything except 'localhost', as instructed?
Re: php connection failure
On Sunday, August 8th, 2021 at 12:26 AM, Tom Lane wrote: > > Hm --- this suggests that you've got a kernel packet filter (i.e. > > software firewall) that is not passing traffic for 5432. Don't know anything about that, so perhaps it can be assumed unchanged from whatever the default system configuration is...
Re: php connection failure
On Sunday, August 8th, 2021 at 12:50 AM, David G. Johnston wrote: > On Saturday, August 7, 2021, ourdiaspora wrote: > > > SHOW hba_file; > > > > /etc/postgresql/9.6/main/pg_hba.conf > > Please post the contents of this file. The one shown at the link is invalid. local all postgrespeer # TYPE DATABASEUSERADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: hostall all 127.0.0.1/32md5 # IPv6 local connections: hostall all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgrespeer #hostreplication postgres127.0.0.1/32md5 #hostreplication postgres::1/128 md5 local cpacweb cpaca trust
Re: php connection failure
On Sunday, August 8th, 2021 at 1:02 AM, Adrian Klaver wrote: > What did you change listen_addresses from? > New line; same line had hash (#) to comment out. > What if you do?: > > psql -d cpacweb -U cpaca > psql -d cpacweb -U cpaca psql: FATAL: Peer authentication failed for user "cpaca"
Re: php connection failure
On Sunday, August 8th, 2021 at 1:51 AM, Adrian Klaver wrote: > 1. Get rid of the first local line . > 2. Then for the remaining local line below change peer to trust and > > restart Postgres. Thank you, but repeat error occurred: sudo sudo service postgresql restart [ ok ] Restarting PostgreSQL 9.6 database server: main. psql -d cpacweb -U cpaca psql: FATAL: Peer authentication failed for user "cpaca"
Re: php connection failure
On Sunday, August 8th, 2021 at 2:15 AM, Tom Lane wrote:
> ourdiaspora ourdiasp...@protonmail.com writes:
>
> > psql -d cpacweb -U cpaca
> >
> > psql: FATAL: Peer authentication failed for user "cpaca"
>
> It's a gold
>
> plated certainty that the default packet filter configuration
>
> wouldn't allow traffic to port 5432 from off-machine.
A quick ddg search revealed:
sudo ss -tulpn
Netid State Recv-Q Send-Q Local Address:Port Peer
Address:Port
tcpLISTEN 0 128 ::1:5432 :::*
users:(("postgres",pid=21794,fd=3))
Is the correct conclusion from this output, that the port 5432 is open?
Re: php connection failure
‐‐‐ Original Message ‐‐‐
On Tuesday, August 10th, 2021 at 2:14 PM, Tom Lane wrote:
> The postgres process is listening, but this has nothing to do with
>
> whether the kernel will allow any packets to arrive there.
>
Installed 'nftables'. According to the documentation
(https://wiki.debian.org/nftables) there are no filter rules by default, so the
assumption is that there is no filtration of packets by the software at the
kernel stage.
The configuration file '/etc/nftables.conf':
#!/usr/sbin/nft -f
flush ruleset
table inet filter {
chain input {
type filter hook input priority 0;
}
chain forward {
type filter hook forward priority 0;
}
chain output {
type filter hook output priority 0;
}
}
Is it correct to interpret this configuration file that filtration is _not_
active?
Re: php connection failure
‐‐‐ Original Message ‐‐‐ On Tuesday, August 10th, 2021 at 3:44 PM, Adrian Klaver wrote: > 1. There is another local line with peer that you missed. > 2. You changed the wrong pg_hba.conf file. > Frow within postgresql 'psql' terminal: SHOW hba_file; /etc/postgresql/9.6/main/pg_hba.conf > What was the path of the file you changed? > SHOW config_file; /etc/postgresql/9.6/main/postgresql.conf # PostgreSQL Client Authentication Configuration File # === # # Refer to the "Client Authentication" section in the PostgreSQL # documentation for a complete description of this file. A short # synopsis follows. # # This file controls: which hosts are allowed to connect, how clients # are authenticated, which PostgreSQL user names they can use, which # databases they can access. Records take one of these forms: # # local DATABASE USER METHOD [OPTIONS] # host DATABASE USER ADDRESS METHOD [OPTIONS] # hostsslDATABASE USER ADDRESS METHOD [OPTIONS] # hostnossl DATABASE USER ADDRESS METHOD [OPTIONS] # # (The uppercase items must be replaced by actual values.) # # The first field is the connection type: "local" is a Unix-domain # socket, "host" is either a plain or SSL-encrypted TCP/IP socket, # "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a # plain TCP/IP socket. # # DATABASE can be "all", "sameuser", "samerole", "replication", a # database name, or a comma-separated list thereof. The "all" # keyword does not match "replication". Access to replication # must be enabled in a separate record (see example below). # # USER can be "all", a user name, a group name prefixed with "+", or a # comma-separated list thereof. In both the DATABASE and USER fields # you can also write a file name prefixed with "@" to include names # from a separate file. # # ADDRESS specifies the set of hosts the record matches. It can be a # host name, or it is made up of an IP address and a CIDR mask that is # an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that # specifies the number of significant bits in the mask. A host name # that starts with a dot (.) matches a suffix of the actual host name. # Alternatively, you can write an IP address and netmask in separate # columns to specify the set of hosts. Instead of a CIDR-address, you # can write "samehost" to match any of the server's own IP addresses, # or "samenet" to match any address in any subnet that the server is # directly connected to. # # METHOD can be "trust", "reject", "md5", "password", "gss", "sspi", # "ident", "peer", "pam", "ldap", "radius" or "cert". Note that # "password" sends passwords in clear text; "md5" is preferred since # it sends encrypted passwords. # # OPTIONS are a set of options for the authentication in the format # NAME=VALUE. The available options depend on the different # authentication methods -- refer to the "Client Authentication" # section in the documentation for a list of which options are # available for which authentication methods. # # Database and user names containing spaces, commas, quotes and other # special characters must be quoted. Quoting one of the keywords # "all", "sameuser", "samerole" or "replication" makes the name lose # its special character, and just match a database or username with # that name. # # This file is read on server startup and when the postmaster receives # a SIGHUP signal. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect. You can # use "pg_ctl reload" to do that. # Put your actual configuration here # -- # # If you want to allow non-local connections, you need to add more # "host" records. In that case you will also need to make PostgreSQL # listen on a non-local interface via the listen_addresses # configuration parameter, or via the -i or -h command line switches. # DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database superuser can access the database using some other method. # Noninteractive access to all databases is required during automatic # maintenance (custom daily cronjobs, replication, and similar tasks). # # Database administrative login by Unix domain socket #local all postgrespeer # TYPE DATABASEUSERADDRESS METHOD # "local" is for Unix domain socket connections only #local all all peer # IPv4 local connections: hostall all 127.0.0.1/32md5 # IPv6 local connections: hostall all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgrespeer #hostreplication postgres127.0.0.1/32
Re: php connection failure
‐‐‐ Original Message ‐‐‐ On Wednesday, August 11th, 2021 at 2:10 PM, rob stone wrote: > > the call to pg_connect as a try . . . catch block so that the exact > Sorry but do not understand "try catch block" > > Are you sure postgres is configured to use port 5432? No, but the impression from previous command terminal output is that this port is being "listened to"
Re: php connection failure
On Wednesday, August 11th, 2021 at 3:09 PM, Adrian Klaver
wrote:
>
> Is the Web server/PHP app on the same machine as the Postgres server?
>
All programs about this question are installed on a single computer; the
intention is to test and learn first on a local machine.
> If it is on the same machine what happens if you do?:
>
> $dbconn = pg_connect("dbname=cpacweb user=cpaca") or die("Could not
>
> connect");
>
Am happy to report that connection looks successful, because the php web page
now reports:
"
Connection status ok
"
Thank you all very much for the help. Hope that other novices benefit too! :)
Re: php connection failure
‐‐‐ Original Message ‐‐‐ On Wednesday, August 11th, 2021 at 3:13 PM, Adrian Klaver wrote: > > Are you using some form of container/VM on the machine for either server? > The machine is a chromebook, GNU/Linux via 'crouton' chroot. Is this the potential cause of blockage of port 5432?
use fopen unknown resource
Readers, Objective, to import a csv file into postgresql database. If understood correctly, the manual section 'fopen' (https://www.php.net/manual/en/function.fopen.php) shows that the file has a "known resource" extant name. Please what is the syntax to assign an unknown file name to the 'fopen' function?
Re: use fopen unknown resource
‐‐‐ Original Message ‐‐‐
On Friday, August 27th, 2021 at 11:10 PM, Adrian Klaver
wrote:
> https://www.php.net/manual/en/pdo.pgsqlcopyfromfile.php
>
"
public PDO::pgsqlCopyFromFile(
string $table_name,
string $filename,
"
Sorry but do not understand; the line does not explain what to write in the php
file.
So far have written:
"
https://www.php.net/manual/en/function.fgetcsv.php)
Instead of:
"
...
fopen("test.csv", "r"))
...
"
it would _not_ be possible to write, correct?:
"
...
fopen("", "r"))
Re: use fopen unknown resource
On Saturday, August 28th, 2021 at 5:52 PM, Adrian Klaver
wrote:
>
> You are asking the user to select a file, so there should be some sort
>
> of file reference at that point, correct?
>
This is what causes confusion. If a user has a file named 'mydatafile.csv', why
does the manual make reference to open 'test.csv'???:
https://www.php.net/manual/en/function.fgetcsv.php
"
...
if (($handle = fopen("test.csv", "r")) !== FALSE) {
...
"
database design with temporary tables
Readers, Some advice would be appreciated about appropriate tables to store temporary data. Scenario: User copies csv file of user data, presumably into some temporary table(s); User selects data (read-only) from extant tables; Web server combines user data with read-only data to produce content visible to user as html and/or pdf document; User does not need to sign in to use web page, only the user e-mail address; User data deleted (including e-mail address) after time (e.g. 24 hours) and/or user selects to receive combined data via e-mail (after which all user data is automatically deleted). What part of the documention would be most relevant to read firstly, please?
Re: database design with temporary tables
‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:24 PM, Adrian Klaver wrote: > > Presumably not. Temporary tables only live at most for the length of a > > session. It would be a really bad idea to hold sessions open for 24 > > hours. Is there an alternative scenario, such as the user is able to create a new table with saves the session data for a maximum time (such as 24 hours), even up to a certain time if the web browser crashes for example? > > The abo > Incomplete response?
Re: database design with temporary tables
‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:30 PM, Adrian Klaver wrote: > On 8/29/21 9:24 AM, Adrian Klaver wrote: > > > Whoops, unfinished thought. What I was going to ask is: > > The above is not clear to me. Are you asking about the Postgres > > documentation? > Yes, wanted to know relevant parts because often the first problem is to know which part of the (extensive) documentation to read...
Re: database design with temporary tables
‐‐‐ Original Message ‐‐‐ On Sunday, August 29th, 2021 at 5:38 PM, Ray O'Donnell wrote: > > I'd save a timestamp with the session data, and then run a cron job > > which deletes sessions older than whatever lifetime you want. > Is it prudent in this scenario for the user to create (a) tables of her/his data, with role as her/his e-mail address (no password)?
Re: database design with temporary tables
On Sunday, August 29th, 2021 at 5:56 PM, Ray O'Donnell wrote: > - or do they connect only to the web server, which then > > uses its own account on the database server? Thank you; was not aware of this consideration and this option seems most appropriate for the simple sql commands envisaged.
csv copy error
Readers, Please could anyone help with the following error produced: " ERROR: invalid input syntax for integer: "1,m " CONTEXT: COPY exampletable, line 1, column examplenumber: "1,m " The database commands: " CREATE TABLE exampletable (examplenumber smallint, exampletitle varchar(500) ); " " \copy exampletable from '/local/path/to/examplefile.csv'; " CSV file contents: " 1,m 2,m 9,t "
Re: csv copy error
On Thursday, December 30th, 2021 at 7:30 AM, Guillaume Lelarge wrote: > Hi, > > Le mer. 29 déc. 2021 à 22:08, ourdiaspora a > écrit : > > > > > \copy exampletable from '/local/path/to/examplefile.csv'; > > > > You should tell the CSV command you're using a CSV file. By default, COPY > thinks it's a TSV file. > Problem solved with: " ...file.csv' with csv; " Thanks to all.
create query with date values
Recipients, How to create a query such that when a date value is entered, the date may be associated with the result of another query. The scenario is that a date value is entered (the plan is these dates should be a separate table), then a query result is joined with the date value. Please advise the correct terminology for appropriate review of the manual.
Visibility of data from table inherits function
Recipients, A table was created: CREATE TABLE exampletable ( name varchar(200) ); Table 'exampletable' _already_ contains data, e.g. 'Jane Bloggs' A new table was created with inheritance of 'exampletable': CREATE TABLE exampletablechild (dates DATE) INHERITS (exampletable); SET DATESTYLE TO 'SQL, EUROPEAN'; \d exampletablechild " ... Inherits: exampletable " SELECT name FROM exampletablechild; " name -- (0 rows) " SELECT name FROM exampletable; " name -- jane bloggs " Please could someone explain why the data in the table 'exampletable' is not visible from the query using the child table? Does not 'INHERIT' function apply to the data of the precedent parent table?
Re: Visibility of data from table inherits function
On Sunday, January 2nd, 2022 at 3:54 PM, Francisco Olarte wrote: > > Inherits does not copy things. LIKE on creation does, but AFAIK it can > > copy nearly everything except data. > Thanks, had read that part of the documentation. So far it seems that functions 'joins' may be used to view data, whilst the functions 'views' is unable to be used with data entry. Please, any suggestions of an alternative function to use?
