Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
I installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition using package manager: sudo apt-get install postgresql-11. But now I'm not able to call psql: (base) marco@pc:~$ sudo su -l postgres [sudo] password for marco: No directory, logging in with HOME=/ postgres@pc:/$ psql Warning: No existing local cluster is suitable as a default target. Please see man pg_wrapper(1) how to specify one. Error: You must install at least one postgresql-client- package postgres@pc:/$ I tried to specify the port to use but it asks for a postgresql-client: (base) marco@pc:~$ sudo -u postgres /usr/bin/psql -p 5432 Error: You must install at least one postgresql-client- package But the latest postgresql-client is already installed: (base) marco@pc:~$ sudo apt-get install postgresql-client-11 Reading package lists... Done Building dependency tree Reading state information... Done postgresql-client-11 is already the newest version (11.5-1.pgdg18.04+1). 0 to upgrade, 0 to newly install, 0 to remove and 0 not to upgrade. How to solve the problem? Looking forward to your kind help. Marco
Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
Thanks Matthias. Followed these steps (indicated here: https://www.postgresql.org/docs/11/creating-cluster.html ) root# *chown postgres /usr/local/pgsql* root# *su postgres* postgres$ *initdb -D /usr/local/pgsql/data* postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locales COLLATE: en_GB.UTF-8 CTYPE:en_GB.UTF-8 MESSAGES: en_GB.UTF-8 MONETARY: C.UTF-8 NUMERIC: C.UTF-8 TIME: C.UTF-8 The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. creating directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default timezone ... Europe/Rome selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start But now permission denied: postgres@pc:/home/marco$ sudo /usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start [sudo] password for postgres: postgres is not in the sudoers file. This incident will be reported. postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start waiting for server to start/bin/sh: 1: cannot create logfile: Permission denied stopped waiting pg_ctl: could not start server Examine the log output. postgres@pc:/home/marco$ Il giorno mer 18 set 2019 alle ore 12:03 Matthias Apitz ha scritto: > El día Wednesday, September 18, 2019 a las 11:50:45AM +0200, Marco > Ippolito escribió: > > > I installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition using package > > manager: sudo apt-get install postgresql-11. But now I'm not able to call > > psql: > > > > (base) marco@pc:~$ sudo su -l postgres > > [sudo] password for marco: > > No directory, logging in with HOME=/ > > postgres@pc:/$ psql > > Warning: No existing local cluster is suitable as a default target. > > Please > > see man pg_wrapper(1) how to specify one. > > Error: You must install at least one postgresql-client- > package > > postgres@pc:/$ > > > > I tried to specify the port to use but it asks for a postgresql-client: > > > > (base) marco@pc:~$ sudo -u postgres /usr/bin/psql -p 5432 > > Error: You must install at least one postgresql-client- > package > > > > But the latest postgresql-client is already installed: > > > > (base) marco@pc:~$ sudo apt-get install postgresql-client-11 > > Reading package lists... Done > > Building dependency tree > > Reading state information... Done > > postgresql-client-11 is already the newest version > (11.5-1.pgdg18.04+1). > > 0 to upgrade, 0 to newly install, 0 to remove and 0 not to upgrade. > > > > How to solve the problem? Looking forward to your kind help. > > Marco > > Have you setup and started any PG cluster? See > https://www.postgresql.org/docs/11/runtime.html > (chapter 18. Server Setup and Operation). > > matthias > > -- > Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ > +49-176-38902045 > Public GnuPG key: http://www.unixarea.de/key.pub > May, 9: Спаси́бо освободители! Thank you very much, Russian liberators! > > >
Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
Hi Andreas, if I understand correctly, this is what I've done afterwards: postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start waiting for server to start/bin/sh: 1: cannot create logfile: Permission denied stopped waiting pg_ctl: could not start server Examine the log output. postgres@pc:/home/marco$ What am I doing wrong? Il giorno mer 18 set 2019 alle ore 12:22 Andreas Joseph Krogh < andr...@visena.com> ha scritto: > På onsdag 18. september 2019 kl. 12:13:24, skrev Marco Ippolito < > ippolito.ma...@gmail.com>: > > Thanks Matthias. > > Followed these steps (indicated here: > https://www.postgresql.org/docs/11/creating-cluster.html ) > > root# *chown postgres /usr/local/pgsql* > root# *su postgres* > postgres$ *initdb -D /usr/local/pgsql/data* > > > postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/initdb -D > /usr/local/pgsql/data > The files belonging to this database system will be owned by user > "postgres". > This user must also own the server process. > > The database cluster will be initialized with locales > COLLATE: en_GB.UTF-8 > CTYPE:en_GB.UTF-8 > MESSAGES: en_GB.UTF-8 > MONETARY: C.UTF-8 > NUMERIC: C.UTF-8 > TIME: C.UTF-8 > The default database encoding has accordingly been set to "UTF8". > The default text search configuration will be set to "english". > > Data page checksums are disabled. > > creating directory /usr/local/pgsql/data ... ok > creating subdirectories ... ok > selecting default max_connections ... 100 > selecting default shared_buffers ... 128MB > selecting default timezone ... Europe/Rome > selecting dynamic shared memory implementation ... posix > creating configuration files ... ok > running bootstrap script ... ok > performing post-bootstrap initialization ... ok > syncing data to disk ... ok > > WARNING: enabling "trust" authentication for local connections > You can change this by editing pg_hba.conf or using the option -A, or > --auth-local and --auth-host, the next time you run initdb. > > Success. You can now start the database server using: > > /usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l logfile > start > > But now permission denied: > > postgres@pc:/home/marco$ sudo /usr/lib/postgresql/11/bin/pg_ctl -D > /usr/local/pgsql/data -l logfile start > [sudo] password for postgres: > postgres is not in the sudoers file. This incident will be reported. > postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D > /usr/local/pgsql/data -l logfile start > waiting for server to start/bin/sh: 1: cannot create logfile: > Permission denied > stopped waiting > pg_ctl: could not start server > Examine the log output. > postgres@pc:/home/marco$ > > > Start pg_ctl as postgres user, no need to sudo. > > -- > *Andreas Joseph Krogh* > CTO / Partner - Visena AS > Mobile: +47 909 56 963 > andr...@visena.com > www.visena.com > <https://www.visena.com> > >
Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
thanks Andreas. After changing ownership of /usr/lib/postgresql to postgres user, postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/lib/postgresql/11/log/logfile start waiting for server to start done server started postgres@pc:/home/marco$ But now... how to call and use psql? postgres@pc:/home/marco$ psql Warning: No existing local cluster is suitable as a default target. Please see man pg_wrapper(1) how to specify one. Error: You must install at least one postgresql-client- package postgres@pc:/home/marco$ I guess I'm still missing something... Il giorno mer 18 set 2019 alle ore 12:28 Andreas Joseph Krogh < andr...@visena.com> ha scritto: > På onsdag 18. september 2019 kl. 12:25:05, skrev Marco Ippolito < > ippolito.ma...@gmail.com>: > > Hi Andreas, > if I understand correctly, this is what I've done afterwards: > > postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D > /usr/local/pgsql/data -l logfile start > waiting for server to start/bin/sh: 1: cannot create logfile: > Permission denied > stopped waiting > pg_ctl: could not start server > Examine the log output. > postgres@pc:/home/marco$ > > What am I doing wrong? > > > You don't have permissions to create the logfile (named "logfile" in your > command) in CWD (/home/macro). Specify absolute path to somewhere writable > for user "postgres". > > -- > *Andreas Joseph Krogh* > CTO / Partner - Visena AS > Mobile: +47 909 56 963 > andr...@visena.com > www.visena.com > <https://www.visena.com> > >
Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
Thanks Matthias, very kind. Hopefully I will get some hints on how to solve my problem. Marco Il giorno mer 18 set 2019 alle ore 15:11 Matthias Apitz ha scritto: > El día Wednesday, September 18, 2019 a las 12:44:20PM +0200, Marco > Ippolito escribió: > > > thanks Andreas. > > > > After changing ownership of /usr/lib/postgresql to postgres user, > > > > postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D > > /usr/local/pgsql/data -l /usr/lib/postgresql/11/log/logfile start > > waiting for server to start done > > server started > > postgres@pc:/home/marco$ > > > > But now... how to call and use psql? > > Marco, I compiled 11.4 on Linux for our needs and wrote a Confluence > page about this and the cluster setup. I could send you this pages in PDF > off-list. > > matthias > -- > Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ > +49-176-38902045 > Public GnuPG key: http://www.unixarea.de/key.pub > May, 9: Спаси́бо освободители! Thank you very much, Russian liberators! > > >
Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
Hi Adrian, after purging from Ubuntu 18.04.02 everything related to postgresql-11, I re-installed it and made it working, yes...finally working... with this useful and good guidelines: https://pgdash.io/blog/postgres-11-getting-started.html. Marco Il giorno mer 18 set 2019 alle ore 16:41 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto: > On 9/18/19 6:13 AM, Marco Ippolito wrote: > > Thanks Matthias, > > very kind. Hopefully I will get some hints on how to solve my problem. > > You should probably take a look at: > > https://help.ubuntu.com/lts/serverguide/postgresql.html > > FYI, you should set the permission son /usr/lib/postgresql back to root > and use: > > sudo pg_ctl > > > > > Marco > > > > Il giorno mer 18 set 2019 alle ore 15:11 Matthias Apitz > > mailto:g...@unixarea.de>> ha scritto: > > > > El día Wednesday, September 18, 2019 a las 12:44:20PM +0200, Marco > > Ippolito escribió: > > > > > thanks Andreas. > > > > > > After changing ownership of /usr/lib/postgresql to postgres user, > > > > > > postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D > > > /usr/local/pgsql/data -l /usr/lib/postgresql/11/log/logfile start > > > waiting for server to start done > > > server started > > > postgres@pc:/home/marco$ > > > > > > But now... how to call and use psql? > > > > Marco, I compiled 11.4 on Linux for our needs and wrote a Confluence > > page about this and the cluster setup. I could send you this pages > > in PDF off-list. > > > > matthias > > -- > > Matthias Apitz, ✉ g...@unixarea.de <mailto:g...@unixarea.de>, > > http://www.unixarea.de/ +49-176-38902045 > > Public GnuPG key: http://www.unixarea.de/key.pub > > May, 9: Спаси́бо освободители! Thank you very much, Russian > liberators! > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
How to safely remove a corrupted cluster?
If the saying "to learn you have to make mistakes", I'm "learning"...: I removed a cluster's folder without using pg_dropcluster... sio now I have a corrupted cluster. How can I safely remove it? (base) marco@pc:~$ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 11 fabmnet 5433 down/var/lib/postgresql/11/fabmnet /var/log/postgresql/postgresql-11-fabmnet.log 11 main5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log (base) marco@pc:~$ pg_dropcluster 11 fabmnet Warning: corrupted cluster: data directory does not exist Warning: systemd was not informed about the removed cluster yet. Operations like "service postgresql start" might fail. To fix, run: sudo systemctl daemon-reload (base) marco@pc:~$ sudo systemctl daemon-reload [sudo] password for marco: (base) marco@pc:~$ pg_dropcluster 11 fabmnet Warning: corrupted cluster: data directory does not exist Warning: systemd was not informed about the removed cluster yet. Operations like "service postgresql start" might fail. To fix, run: sudo systemctl daemon-reload Marco
Re: How to safely remove a corrupted cluster?
Solved by repeating the execution of (base) postgres@pc:~$ pg_dropcluster --stop 11 fabmnet : (base) postgres@pc:~$ pg_dropcluster --stop 11 fabmnet Error: specified cluster does not exist Marco Il giorno gio 19 set 2019 alle ore 13:02 Marco Ippolito < ippolito.ma...@gmail.com> ha scritto: > If the saying "to learn you have to make mistakes", I'm "learning"...: > > I removed a cluster's folder without using pg_dropcluster... sio now I > have a corrupted cluster. How can I safely remove it? > > (base) marco@pc:~$ pg_lsclusters > Ver Cluster Port Status Owner Data directory Log file > 11 fabmnet 5433 down/var/lib/postgresql/11/fabmnet > /var/log/postgresql/postgresql-11-fabmnet.log > 11 main5432 online postgres /var/lib/postgresql/11/main > /var/log/postgresql/postgresql-11-main.log > > (base) marco@pc:~$ pg_dropcluster 11 fabmnet > Warning: corrupted cluster: data directory does not exist > Warning: systemd was not informed about the removed cluster yet. > Operations like "service postgresql start" might fail. To fix, run: > sudo systemctl daemon-reload > (base) marco@pc:~$ sudo systemctl daemon-reload > [sudo] password for marco: > (base) marco@pc:~$ pg_dropcluster 11 fabmnet > Warning: corrupted cluster: data directory does not exist > Warning: systemd was not informed about the removed cluster yet. > Operations like "service postgresql start" might fail. To fix, run: > sudo systemctl daemon-reload > > Marco >
could not accept SSL connection: sslv3 alert bad certificate
Following the indications here:
https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database
I'm trying to understand how to correctly set Fabric-CA with a
PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.
I created a postgresql-11 db to which I can connect with SSL:
(base) marco@pc:~$ psql --cluster 11/fabmnet -h 127.0.0.1 -d fabmnetdb
-U fabmnet_admin
Password for user fabmnet_admin:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
bits: 256, compression: off)
Type "help" for help.
fabmnetdb=> \l
List of databases
Name| Owner | Encoding | Collate | Ctype | Access
privileges
---+---+--+-+-+---
fabmnetdb | fabmnet_admin | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
(4 rows)
fabmnetdb=>
but when trying to start a fabric-ca-server :
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw
2019/09/25 20:56:57 [INFO] Configuration file location:
/home/marco/fabric
/fabric-ca/fabric-ca-server-config.yaml
2019/09/25 20:56:57 [INFO] Starting server in home directory:
/home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Server Version: 1.4.4
2019/09/25 20:56:57 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/25 20:56:57 [INFO] The CA key and certificate already exist
2019/09/25 20:56:57 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/25 20:56:57 [INFO] The certificate is at: /home/marco/fabric
/fabric-ca/ca-cert.pem
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'postgres'
2019/09/25 20:56:57 [WARNING] Failed to connect to database 'template1'
2019/09/25 20:56:57 [ERROR] Error occurred initializing database:
Failed
to connect to Postgres database. Postgres requires connecting to a
specific database, the following databases were tried: [fabmnetdb
postgres
template1]. Please create one of these database before continuing
2019/09/25 20:56:57 [INFO] Home directory for default CA: /home/marco
/fabric/fabric-ca
2019/09/25 20:56:57 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/25 20:56:57 [INFO] Listening on http://0.0.0.0:7054
This is the corresponding part in
/var/log/postgresql/postgresql-11-fabmnet.log :
2019-09-25 20:51:52.655 CEST [1096] LOG: listening on IPv6 address
"::1",
port 5433
2019-09-25 20:51:52.673 CEST [1096] LOG: listening on IPv4 address
"127.0.0.1", port 5433
2019-09-25 20:51:52.701 CEST [1096] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5433"
2019-09-25 20:51:52.912 CEST [1171] LOG: database system was
interrupted;
last known up at 2019-09-25 09:50:30 CEST
2019-09-25 20:51:53.001 CEST [1171] LOG: database system was not
properly
shut down; automatic recovery in progress
2019-09-25 20:51:53.011 CEST [1171] LOG: redo starts at 0/1668238
2019-09-25 20:51:53.011 CEST [1171] LOG: invalid record length at
0/1668318: wanted 24, got 0
2019-09-25 20:51:53.011 CEST [1171] LOG: redo done at 0/16682E0
2019-09-25 20:51:53.043 CEST [1096] LOG: database system is ready to
accept connections
2019-09-25 20:51:53.569 CEST [1206] [unknown]@[unknown] LOG:
incomplete
startup packet
2019-09-25 20:56:57.540 CEST [4620] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.543 CEST [4622] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-25 20:56:57.544 CEST [4623] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
This is how I set the pg_hba.conf file in the fabmnet postgresql cluster :
(base) marco@pc:~$ sudo -su postgres
(base) postgres@pc:~$ nano /etc/postgresql/11/fabmnet/pg_hba.conf
Unable to create directory /home/marco/.local/share/nano/: Permission
denied
It is required for saving/loading search history or cursor positions.
Press Enter to continue
# TYPE DATABASEUSERADDRESS METHOD
# Database administrative login by Unix domain socket
local all postgrespeer
# TYPE DATABASEUSERADDRE
Re: could not accept SSL connection: sslv3 alert bad certificate
Hi Adrian,
putting in /fabric/fabric-ca/fabric-ca-server-config.yaml :
db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin
password=fabmnet1971 dbname=fabmnetdb sslmode=verify-ca
this is the result:
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/26 09:44:39 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 09:44:39 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca
2019/09/26 09:44:39 [INFO] Server Version: 1.4.4
2019/09/26 09:44:39 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 09:44:39 [INFO] The CA key and certificate already exist
2019/09/26 09:44:39 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/26 09:44:39 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/26 09:44:39 [ERROR] Error occurred initializing database: Failed to
create Postgres database: Failed to execute create database query: pq:
permission denied to create database
2019/09/26 09:44:39 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/26 09:44:39 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 09:44:39 [INFO] Listening on http://0.0.0.0:7054
and the corresponding log in /var/log/postgresql/postgresql-11-fabmnet.log :
2019-09-26 09:21:11.605 CEST [1132] LOG: received fast shutdown request
2019-09-26 09:21:11.613 CEST [1132] LOG: aborting any active transactions
2019-09-26 09:21:11.615 CEST [1132] LOG: background worker "logical
replication launcher" (PID 1169) exited with exit code 1
2019-09-26 09:21:11.616 CEST [1161] LOG: shutting down
2019-09-26 09:21:11.643 CEST [1132] LOG: database system is shut down
2019-09-26 09:21:57.370 CEST [1077] LOG: listening on IPv6 address "::1",
port 5433
2019-09-26 09:21:57.370 CEST [1077] LOG: listening on IPv4 address
"127.0.0.1", port 5433
2019-09-26 09:21:57.372 CEST [1077] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5433"
2019-09-26 09:21:57.426 CEST [1124] LOG: database system was shut down at
2019-09-26 09:21:11 CEST
2019-09-26 09:21:57.446 CEST [1077] LOG: database system is ready to
accept connections
2019-09-26 09:21:58.040 CEST [1147] [unknown]@[unknown] LOG: incomplete
startup packet
2019-09-26 09:44:39.374 CEST [2902] fabmnet_admin@fabmnetdb ERROR:
permission denied to create database
2019-09-26 09:44:39.374 CEST [2902] fabmnet_admin@fabmnetdb STATEMENT:
CREATE DATABASE fabmnetdb
Putting in /home/marco/fabric/fabric-ca/fabric-ca-serve-config.yaml :
db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin
password=fabmnet1971 dbname=fabmnetdb sslmode=require
which, according to
https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database
means: Always SSL (skip verification)
again the same error message:
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/26 10:08:27 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 10:08:27 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca
2019/09/26 10:08:27 [INFO] Server Version: 1.4.4
2019/09/26 10:08:27 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 10:08:27 [INFO] The CA key and certificate already exist
2019/09/26 10:08:27 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/26 10:08:27 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/26 10:08:27 [ERROR] Error occurred initializing database: Failed to
create Postgres database: Failed to execute create database query: pq:
permission denied to create database
2019/09/26 10:08:27 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/26 10:08:27 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 10:08:27 [INFO] Listening on http://0.0.0.0:7054
and the corresponding portion of the log file:
2019-09-26 10:08:27.947 CEST [3728] fabmnet_admin@fabmnetdb ERROR:
permission denied to create database
2019-09-26 10:08:27.947 CEST [3728] fabmnet_admin@fabmnetdb STATEMENT:
CREATE DATABASE fabmnetdb
I do not undertand...
Marco
Il giorno gio 26 set 2019 alle ore 02:07 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:
> On 9/25/19 12:34 PM, Marco Ippolito wrote:
> > Following the indications here:
> >
> https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database
> > I'm trying to understand how to correctly set Fabric-CA with a
> > PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.
> >
>
> > This is the corresponding part in
> > /var/log/postgresql/postgresql-11-fabmnet.log :
> >
> > 2019-09-25 20:51:52.655 CEST [10
Re: could not accept SSL connection: sslv3 alert bad certificate
Thanks Martin. I need to check these important aspects as well.
What do you mean as "disable hardcoded BCCSP Provider"?
Marco
Il giorno gio 26 set 2019 alle ore 00:43 Martin Gainty
ha scritto:
> Hi Marco
>
> not necessarily with PG but with all other servers i secure when i see
> that error
> it means the certificate and key your provider is referencing are already
> stored in storage (in my case "truststore")
> I would clean all storage locations of certificate and key
> then I would allow BCCSP provider to push your cert and key into stores
> (identified by BCCSP config)
>
> if that doesnt work I would disable hardcoded BCCSP Provider then manually
> import your certs and keys into your truststore
>
> YMMV
> martin
> --
> *From:* Marco Ippolito
> *Sent:* Wednesday, September 25, 2019 3:34 PM
> *To:* pgsql-general@lists.postgresql.org <
> pgsql-general@lists.postgresql.org>
> *Subject:* could not accept SSL connection: sslv3 alert bad certificate
>
> Following the indications here:
> https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database
> I'm trying to understand how to correctly set Fabric-CA with a
> PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.
>
> I created a postgresql-11 db to which I can connect with SSL:
>
> (base) marco@pc:~$ psql --cluster 11/fabmnet -h 127.0.0.1 -d
> fabmnetdb -U fabmnet_admin
> Password for user fabmnet_admin:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
> bits: 256, compression: off)
> Type "help" for help.
>
> fabmnetdb=> \l
> List of databases
>Name| Owner | Encoding | Collate | Ctype | Access
> privileges
>
> ---+---+--+-+-+---
> fabmnetdb | fabmnet_admin | UTF8 | C.UTF-8 | C.UTF-8 |
> postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> =c/postgres +
>| | | | |
> postgres=CTc/postgres
> template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> =c/postgres +
>| | | | |
> postgres=CTc/postgres
> (4 rows)
>
> fabmnetdb=>
>
>
> but when trying to start a fabric-ca-server :
>
> (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
> admin:adminpw
> 2019/09/25 20:56:57 [INFO] Configuration file location:
> /home/marco/fabric
> /fabric-ca/fabric-ca-server-config.yaml
> 2019/09/25 20:56:57 [INFO] Starting server in home directory:
> /home/marco
> /fabric/fabric-ca
> 2019/09/25 20:56:57 [INFO] Server Version: 1.4.4
> 2019/09/25 20:56:57 [INFO] Server Levels: &{Identity:2 Affiliation:1
> Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> 2019/09/25 20:56:57 [INFO] The CA key and certificate already exist
> 2019/09/25 20:56:57 [INFO] The key is stored by BCCSP provider 'SW'
> 2019/09/25 20:56:57 [INFO] The certificate is at: /home/marco/fabric
> /fabric-ca/ca-cert.pem
> 2019/09/25 20:56:57 [WARNING] Failed to connect to database 'fabmnetdb'
> 2019/09/25 20:56:57 [WARNING] Failed to connect to database 'postgres'
> 2019/09/25 20:56:57 [WARNING] Failed to connect to database 'template1'
> 2019/09/25 20:56:57 [ERROR] Error occurred initializing database:
> Failed
> to connect to Postgres database. Postgres requires connecting to a
> specific database, the following databases were tried: [fabmnetdb
> postgres
> template1]. Please create one of these database before continuing
> 2019/09/25 20:56:57 [INFO] Home directory for default CA: /home/marco
> /fabric/fabric-ca
> 2019/09/25 20:56:57 [INFO] Operation Server Listening on
> 127.0.0.1:9443
> 2019/09/25 20:56:57 [INFO] Listening on http://0.0.0.0:7054
>
> This is the corresponding part in
> /var/log/postgresql/postgresql-11-fabmnet.log :
>
> 2019-09-25 20:51:52.655 CEST [1096] LOG: listening on IPv6 address
> "::1",
> port 5433
> 2019-09-25 20:51:52.673 CEST [1096] LOG: listening on IPv4 address
> "127.0.0.1", port 5433
> 2019-09-25 20:51:52.701 CEST [1096] LOG: listening on Unix socket
> "/var/run/postgresql/.s.PGSQL.5433"
> 2019-09-25 20:51:52.912 CEST [1171] LOG: database system was
> interrupted;
> last known up at 2019-09-25 09:50:30 CES
Re: could not accept SSL connection: sslv3 alert bad certificate
Affer removing the previous cert and key files, I started again the
fabric-ca server discovering that new cert and key files were created:
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/26 11:56:18 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/26 11:56:18 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca
2019/09/26 11:56:18 [INFO] Server Version: 1.4.4
2019/09/26 11:56:18 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/26 11:56:18 [WARNING] &{69 The specified CA certificate file
/home/marco/fabric/fabric-ca/ca-cert.pem does not exist}
2019/09/26 11:56:18 [INFO] generating key: &{A:ecdsa S:256}
2019/09/26 11:56:18 [INFO] encoded CSR
2019/09/26 11:56:18 [INFO] signed certificate with serial number
542755587310273579559145444277178107021548224556
2019/09/26 11:56:18 [INFO] The CA key and certificate were generated for CA
2019/09/26 11:56:18 [INFO] The key was stored by BCCSP provider 'SW'
2019/09/26 11:56:18 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'fabmnetdb'
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'postgres'
2019/09/26 11:56:18 [WARNING] Failed to connect to database 'template1'
2019/09/26 11:56:18 [ERROR] Error occurred initializing database: Failed to
connect to Postgres database. Postgres requires connecting to a specific
database, the following databases were tried: [fabmnetdb postgres
template1]. Please create one of these database before continuing
2019/09/26 11:56:18 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/26 11:56:18 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/26 11:56:18 [INFO] Listening on http://0.0.0.0:7054
but, again, the corresponding log says "bad certificate" :
2019-09-26 11:55:04.514 CEST [4837] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:55:04.517 CEST [4839] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:55:04.518 CEST [4840] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.967 CEST [4862] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.969 CEST [4865] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
2019-09-26 11:56:18.971 CEST [4866] [unknown]@[unknown] LOG: could not
accept SSL connection: sslv3 alert bad certificate
So..how could it be "bad certificate" if it's just been created brand new
by the execution of fabric-ca-server start?
Marco
Il giorno gio 26 set 2019 alle ore 00:43 Martin Gainty
ha scritto:
> Hi Marco
>
> not necessarily with PG but with all other servers i secure when i see
> that error
> it means the certificate and key your provider is referencing are already
> stored in storage (in my case "truststore")
> I would clean all storage locations of certificate and key
> then I would allow BCCSP provider to push your cert and key into stores
> (identified by BCCSP config)
>
> if that doesnt work I would disable hardcoded BCCSP Provider then manually
> import your certs and keys into your truststore
>
> YMMV
> martin
> --
> *From:* Marco Ippolito
> *Sent:* Wednesday, September 25, 2019 3:34 PM
> *To:* pgsql-general@lists.postgresql.org <
> pgsql-general@lists.postgresql.org>
> *Subject:* could not accept SSL connection: sslv3 alert bad certificate
>
> Following the indications here:
> https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#configuring-the-database
> I'm trying to understand how to correctly set Fabric-CA with a
> PostgreSQL-11 database in Ubuntu 18.04.02 Server Edition.
>
> I created a postgresql-11 db to which I can connect with SSL:
>
> (base) marco@pc:~$ psql --cluster 11/fabmnet -h 127.0.0.1 -d
> fabmnetdb -U fabmnet_admin
> Password for user fabmnet_admin:
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
> bits: 256, compression: off)
> Type "help" for help.
>
> fabmnetdb=> \l
> List of databases
>Name| Owner | Encoding | Collate | Ctype | Access
> privileges
>
> ---+---+--+-+-+---
> fabmnetdb | fabmnet_admin | UTF8 | C.UTF-8 | C.UTF-8 |
> postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
> temp
"Failed to connect to Postgres database"
In order to restart from a clean situation and configuration, I removed the
previous fabric-ca folder, created a new one, and then initiated the
fabric-ca-server. With the default SQLite everything seem working fine. But
one I try to use the PostgreSQL-11 db I created before, errors appear:
(base) marco@pc:~/fabric$ rm -rf fabric-ca(base) marco@pc:~/fabric$
mkdir fabric-ca(base) marco@pc:~/fabric$ cd fabric-ca/(base)
marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b
admin:adminpw(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw2019/09/26 15:48:54 [INFO] Created default configuration
file at /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml2019/09/26
15:48:54 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca2019/09/26 15:48:54 [INFO] Server Version:
1.4.42019/09/26 15:48:54 [INFO] Server Levels: &{Identity:2
Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}2019/09/26 15:48:54
[WARNING] &{69 The specified CA certificate file
/home/marco/fabric/fabric-ca/ca-cert.pem does not exist}2019/09/26
15:48:54 [INFO] generating key: &{A:ecdsa S:256}2019/09/26 15:48:54
[INFO] encoded CSR2019/09/26 15:48:54 [INFO] signed certificate with
serial number 1625953039820960683388734809875126848203422536642019/09/26
15:48:54 [INFO] The CA key and certificate were generated for
CA 2019/09/26 15:48:54 [INFO] The key was stored by BCCSP provider
'SW'2019/09/26 15:48:54 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem2019/09/26 15:48:54 [INFO]
Initialized sqlite3 database at
/home/marco/fabric/fabric-ca/fabric-ca-server.db2019/09/26 15:48:54
[INFO] The issuer key was successfully stored. The public key is at:
/home/marco/fabric/fabric-ca/IssuerPublicKey, secret key is at:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey2019/09/26
15:48:54 [INFO] Idemix issuer revocation public and secret
keys were generated for CA ''2019/09/26 15:48:54 [INFO] The revocation
key was successfully stored.
The public key is at: /home/marco/fabric/fabric-
ca/IssuerRevocationPublicKey, private key is at:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey2019/09/26
15:48:54 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca2019/09/26 15:48:54 [INFO] Operation
Server Listening on 127.0.0.1:94432019/09/26 15:48:54 [INFO] Listening
on http://0.0.0.0:7054
I set the brand-new fabric-ca-server-config.yaml in this way:
#db:# type: sqlite3# datasource: fabric-ca-server.db# tls:#
enabled: false# certfiles:# client:#certfile:#
keyfile:
db:
type: postgres
datasource: host=localhost port=5433 user=fabmnet_admin
password=password dbname=fabmnetdb sslmode=verify-full
and in /etc/postgresql/11/fabmnet/postgresql.conf :
ssl = on
ssl_cert_file = '/home/marco/fabric/fabric-ca/ca-cert.pem'
ssl_key_file = '/home/marco/fabric/fabric-ca/msp/keystore
/IssuerSecretKey'
After systemctl restart postgresql, I tried to start the fabric-ca-server:
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw2019/09/26 15:56:50 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml2019/09/26
15:56:50 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca2019/09/26 15:56:50 [INFO] Server Version:
1.4.42019/09/26 15:56:50 [INFO] Server Levels: &{Identity:2
Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}2019/09/26 15:56:50 [INFO]
The CA key and certificate already exist2019/09/26 15:56:50 [INFO] The
key is stored by BCCSP provider 'SW'2019/09/26 15:56:50 [INFO] The
certificate is at: /home/marco/fabric/fabric-ca/ca-cert.pem2019/09/26
15:56:50 [WARNING] Failed to connect to database 'fabmnetdb'2019/09/26
15:56:50 [WARNING] Failed to connect to database 'postgres'2019/09/26
15:56:50 [WARNING] Failed to connect to database 'template1'2019/09/26
15:56:50 [ERROR] Error occurred initializing database: Failedto
connect to Postgres database. Postgres requires connecting to a
specific database, the following databases were tried: [fabmnetdb
postgres template1]. Please create one of these database before
continuing2019/09/26 15:56:50 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca2019/09/26 15:56:50 [INFO] Operation
Server Listening on 127.0.0.1:94432019/09/26 15:56:50 [INFO] Listening
on http://0.0.0.0:7054
Before I also removed all the previous content of
/var/log/postgresql/postgresql-11-fabmnet.log to have a clean situation.
But strangely now I do not get any new logging information in
postgresql-11-fabmnet.log
So. I think there must be something to fix in the interface between
fabric-ca-server and PostgreSQL-11 db. In fabric-ca-server-config.yaml, in
postgresql.conf, in both or somewhere else.
Il giorno gio 26 set 2019 alle ore 12:05 Marco Ippolito <
ippolito.ma...@gmail.com> ha scritto:
> Affer removing the pre
Re: "Failed to connect to Postgres database"
Hi Adrian,
I removed the previous fabmentdb and created a new one whose owner is
postgres:
(base) postgres@pc:/usr/local/pgsql$ psql --cluster 11/fabmnet
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.
postgres=# \l
List of databases
Name| Owner | Encoding | Collate | Ctype | Access privileges
---+--+--+-+-+---
fabmnetdb | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#
*First objective: enable and make it working ssl connection for fabmnetd*b :
Following the indications found here:
https://vibhork.blogspot.com/2011/07/how-to-enable-ssl-in-postgresqlppas.html
I created .pem and .req files in /var/lib/postgresql/11/fabmnet/ where,
according to nano /etc/postgresql/11/fabmnet/postgresql.conf, the data
folder is located:
data_directory = '/var/lib/postgresql/11/fabmnet'
ssl = on
(base) postgres@pc:/var/lib/postgresql/11/fabmnet$ chmod 600 privkey.pem
(base) postgres@pc:/var/lib/postgresql/11/fabmnet$ chmod 600 cert.req
But now, testing the ssl connection :
(base) marco@pc:/usr/local/pgsql$ psql -h 127.0.0.1 -d fabmnetdb -U postgres
Password for user postgres:
psql: FATAL: database "fabmnetdb" does not exist
What am I missing?
Thanks again for your kind help.
Marco
Il giorno gio 26 set 2019 alle ore 16:50 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:
> On 9/26/19 7:21 AM, Marco Ippolito wrote:
> > In order to restart from a clean situation and configuration, I removed
> > the previous fabric-ca folder, created a new one, and then initiated the
> > fabric-ca-server. With the default SQLite everything seem working fine.
> > But one I try to use the PostgreSQL-11 db I created before, errors
> appear:
> >
> > |(base)marco@pc:~/fabric$rm -rf fabric-ca (base)marco@pc:~/fabric$mkdir
> > fabric-ca (base)marco@pc:~/fabric$cd
> > fabric-ca/(base)marco@pc:~/fabric/fabric-ca$fabric-ca-server init -b
> > admin:adminpw (base)marco@pc:~/fabric/fabric-ca$fabric-ca-server
> start-b
> > admin:adminpw 2019/09/2615:48:54[INFO]Created defaultconfiguration
> > fileat /home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
> > 2019/09/2615:48:54[INFO]Starting server inhome
> > directory:/home/marco/fabric/fabric-ca 2019/09/2615:48:54[INFO]Server
> > Version:1.4.42019/09/2615:48:54[INFO]Server
> >
> Levels:&{Identity:2Affiliation:1Certificate:1Credential:1RAInfo:1Nonce:1}2019/09/2615:48:54[WARNING]&{69The
>
> > specified CA certificate file/home/marco/fabric/fabric-ca/ca-cert.pem
> > does notexist}2019/09/2615:48:54[INFO]generating key:&{A:ecdsa
> > S:256}2019/09/2615:48:54[INFO]encoded CSR 2019/09/2615:48:54[INFO]signed
> > certificate withserial number
> >
> 1625953039820960683388734809875126848203422536642019/09/2615:48:54[INFO]The
> > CA keyandcertificate were generated forCA 2019/09/2615:48:54[INFO]The
> > keywas stored byBCCSP provider 'SW'2019/09/2615:48:54[INFO]The
> > certificate isat:/home/marco/fabric /fabric-ca/ca-cert.pem
> > 2019/09/2615:48:54[INFO]Initialized sqlite3 databaseat /home/marco
> > /fabric/fabric-ca/fabric-ca-server.db 2019/09/2615:48:54[INFO]The issuer
> > keywas successfully stored.The
> > publickeyisat:/home/marco/fabric/fabric-ca/IssuerPublicKey,secret
> > keyisat:/home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
> > 2019/09/2615:48:54[INFO]Idemix issuer revocation publicandsecret keys
> > were generated forCA ''2019/09/2615:48:54[INFO]The revocation keywas
> > successfully stored.The publickeyisat:/home/marco/fabric/fabric-
> > ca/IssuerRevocationPublicKey,private keyisat:/home/marco/fabric
> > /fabric-ca/msp/keystore/IssuerRevocationPrivateKey
> > 2019/09/2615:48:54[INFO]Home directory fordefaultCA:/home/marco
> > /fabric/fabric-ca 2019/09/2615:48:54[INFO]Operation Server Listening
> > on127.0.0.1:94432019/09/2615:48:54[INFO]Listening onhttp://0.0.0.0:7054|
> >
> > I set the brand-new fabric-ca-server-config.yaml in this way:
> >
> > |#db:#type:sqlite3 #datasource:fabric-ca-server.db #tls:#enabled:false
> > #certfiles:#client:#certfile:#keyfile:db:type:postgres
> > datasource:host=localhost port=5433user=fabmnet_admin password=password
> > dbname=fabmnetdb sslmode=verify-full|
>
> Shouldn't the TLS info also be there for the Postgres da
Re: "Failed to connect to Postgres database"
2 postgres postgres 4.0K Sep 27 11:13 global
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_commit_ts
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_dynshmem
drwx-- 4 postgres postgres 4.0K Sep 27 11:45 pg_logical
drwx-- 4 postgres postgres 4.0K Sep 27 10:58 pg_multixact
drwx-- 2 postgres postgres 4.0K Sep 27 10:59 pg_notify
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_replslot
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_serial
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_snapshots
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_stat
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_stat_tmp
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_subtrans
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_tblspc
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_twophase
drwx-- 3 postgres postgres 4.0K Sep 27 10:58 pg_wal
drwx-- 2 postgres postgres 4.0K Sep 27 10:58 pg_xact
-rw--- 1 postgres postgres 88 Sep 27 10:58 postgresql.auto.conf
-rw--- 1 postgres postgres 136 Sep 27 10:59 postmaster.opts
-rw--- 1 postgres postgres 111 Sep 27 10:59 postmaster.pid
-rw--- 1 root root 4.4K Sep 27 11:26 server.crt
-rw--- 1 root root 1.7K Sep 27 11:26 server.key
I created the server.crt and server.key files following the indications
found here:
https://vibhork.blogspot.com/2011/07/how-to-enable-ssl-in-postgresqlppas.html
(base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost
Password for user postgres:
psql: FATAL: password authentication failed for user "postgres"
FATAL: password authentication failed for user "postgres"
So, before passing to the fabric-ca side , I need to solve this aspect.
Marco
Il giorno ven 27 set 2019 alle ore 04:28 rob stone
ha scritto:
> Hi,
>
> On Thu, 2019-09-26 at 16:21 +0200, Marco Ippolito wrote:
> >
> > db:
> > type: postgres
> > datasource: host=localhost port=5433 user=fabmnet_admin
> > password=password dbname=fabmnetdb sslmode=verify-full
> >
>
> >
> > (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
> > admin:adminpw
> > 2019/09/26 15:56:50 [INFO] Configuration file location: /home/marco
> > /fabric/fabric-ca/fabric-ca-server-config.yaml
> > 2019/09/26 15:56:50 [INFO] Starting server in home directory:
> > /home/marco/fabric/fabric-ca
> > 2019/09/26 15:56:50 [INFO] Server Version: 1.4.4
> > 2019/09/26 15:56:50 [INFO] Server Levels: &{Identity:2 Affiliation:1
> > Certificate:1 Credential:1 RAInfo:1 Nonce:1}
> > 2019/09/26 15:56:50 [INFO] The CA key and certificate already exist
> > 2019/09/26 15:56:50 [INFO] The key is stored by BCCSP provider 'SW'
> > 2019/09/26 15:56:50 [INFO] The certificate is at: /home/marco/fabric
> > /fabric-ca/ca-cert.pem
> > 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> > 'fabmnetdb'
> > 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> > 'postgres'
> > 2019/09/26 15:56:50 [WARNING] Failed to connect to database
> > 'template1'
> > 2019/09/26 15:56:50 [ERROR] Error occurred initializing database:
> > Failed
> > to connect to Postgres database. Postgres requires connecting to a
> > specific database, the following databases were tried: [fabmnetdb
> > postgres template1]. Please create one of these database before
> > continuing
>
>
> Why is it trying to connect to *any* database?
>
> In the fabric-ca docs it shows the connection string as a single line
> but your configuration file has it split over two lines.
> My uneducated guess is that it is ignoring the 'password=password
> dbname=fabmnetdb sslmode=verify-full'
> line and thus unable to connect to fabmnetdb.
>
> Cheers,
> Robert
>
>
>
Re: "Failed to connect to Postgres database"
Thanks Daniel. After adding the password, now ssh connection to the cluster fabmnet works: postgres=# \l List of databases Name| Owner | Encoding | Collate | Ctype | Access privileges +--+--+-+-+--- fabmnet_ca | postgres | UTF8 | C.UTF-8 | C.UTF-8 | postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) postgres=# \password Enter new password: Enter it again: postgres=# \q (base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost Password for user postgres: psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=# And may be the fact the it's compulsory to add a password is testified also by the fact that changing the ownership of the database while adding a password, lets connect with ssh to the database: postgres=# CREATE USER fabmnet_admin; CREATE ROLE postgres=# ALTER USER fabmnet_admin WITH PASSWORD 'A'; ALTER ROLE postgres=# ALTER DATABASE fabmnet_ca OWNER TO fabmnet_admin; ALTER DATABASE postgres=# \l List of databases Name| Owner | Encoding | Collate | Ctype | Access privileges +---+--+-+-+--- fabmnet_ca | fabmnet_admin | UTF8 | C.UTF-8 | C.UTF-8 | postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet Password for user postgres: psql: FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" (base) postgres@pc:~$ psql -h localhost --cluster 11/fabmnet -d fabmnet_ca -U fabmnet_admin Password for user fabmnet_admin: psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. fabmnet_ca=> Now I have to fix the interface between fabric-ca and postgresql-11 on both sides. And I will let you know how it is going Marco Il giorno ven 27 set 2019 alle ore 13:34 Daniel Verite < dan...@manitou-mail.org> ha scritto: > Marco Ippolito wrote: > > > (base) postgres@pc:~$ psql --cluster 11/fabmnet -h localhost > > Password for user postgres: > > psql: FATAL: password authentication failed for user "postgres" > > FATAL: password authentication failed for user "postgres" > > Did you set a password for the postgres user in that newly created > cluster? > If not, try psql --cluster 11/fabmnet (without -h localhost), > it should connect you without a password, > then set a password with the \password command in psql, > then try again with -h localhost. > > > Best regards, > -- > Daniel Vérité > PostgreSQL-powered mailer: http://www.manitou-mail.org > Twitter: @DanielVerite >
Re: "Failed to connect to Postgres database"
Hi Adrian,
thanks to your kind explanation I discovered that I can connect to the db
without explicitly calling the belonging cluster:
(base) postgres@pc:~$ psql -p5433 -d fabmnet_ca
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.
fabmnet_ca=# \conninfo
You are connected to database "fabmnet_ca" as user "postgres" via socket in
"/var/run/postgresql" at port "5433".
fabmnet_ca=# \l
List of databases
Name| Owner | Encoding | Collate | Ctype | Access privileges
+--+--+-+-+---
fabmnet_ca | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
(4 rows)
Now I have to understand how to "tranfer" this ssh-capability to connect
with the fabmnet_ca db of cluster fabmnet without explicitly call the
cluster to the fabric-ca-server :
This is what I set in fabric-ca-server-config.yaml :
#db:
# type: sqlite3
# datasource: fabric-ca-server.db
# tls:
# enabled: false
# certfiles:
# client:
#certfile:
#keyfile:
db:
type: postgres
datasource: host=localhost port=5433 user=postgres password=pwd
dbname=fabmnet_ca sslmode=verify-full
tls:
enabled: false
certfiles:
client:
certfile:
keyfile:
Initializing the fabric-ca-server gives "Failed to connect to Postgres
database" and in postgresql-11-fabmnet.log : sslv3 alert bad certificate
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
2019/09/27 17:07:27 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/27 17:07:27 [INFO] Server Version: 1.4.4
2019/09/27 17:07:27 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/27 17:07:27 [INFO] The CA key and certificate already exist
2019/09/27 17:07:27 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/27 17:07:27 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/27 17:07:27 [WARNING] Failed to connect to database 'fabmnet_ca'
2019/09/27 17:07:27 [WARNING] Failed to connect to database 'postgres'
2019/09/27 17:07:27 [WARNING] Failed to connect to database 'template1'
2019/09/27 17:07:27 [ERROR] Error occurred initializing database: Failed to
connect to Postgres database. Postgres requires connecting to a specific
database, the following databases were tried: [fabmnet_ca postgres
template1]. Please create one of these database before continuing
2019/09/27 17:07:27 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/27 17:07:27 [INFO] Initialization was successful
/var/log/postgresql/postgresql-11-fabmnet.log : 2019-09-27 17:07:27.159
CEST [6626] [unknown]@[unknown] LOG: could not accept SSL connection:
sslv3 alert bad certificate
Why it says "sslv3 alert bad certificate" if it's exactly the same
certificate used when connecting to the same database with ssl in postgres
environment as shown above?
Marco
Il giorno ven 27 set 2019 alle ore 16:38 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:
> On 9/27/19 5:58 AM, Marco Ippolito wrote:
> > Thanks Daniel.
> > After adding the password, now ssh connection to the cluster fabmnet
> works:
>
> You might want to take a look at:
>
> https://help.ubuntu.com/lts/serverguide/postgresql.html
>
>
> > And may be the fact the it's compulsory to add a password is testified
> > also by the fact that changing the ownership of the database while
> > adding a password, lets connect with ssh to the database:
>
> First it is SSL.
> Second password and SSL are two different things. This is covered in the
> auth file pg_hba.conf:
>
> https://www.postgresql.org/docs/11/auth-pg-hba-conf.html
>
> What you are seeing below is dependent on whether you connect using a
> host(-h localhost) or a socket(no -h). That behavior is in turn
> determined by the settings in pg_hba.conf.
>
> Also to help down the road when you are setting up the fabric-ca server
> you need to remember you are now running two Postgres servers:
>
> Ver Cluster Port Status OwnerData directory Log file
> 11 fabmnet 5433 online postgres /var/lib/postgresql/11/fabmnet
> /var/log/postgresql/postgresql-11-fabmnet.log
> 11 main5432 online postgres /var/lib/postgresql/11/main
> /var/log/postgresql/postgresql-11-main.log
>
> The most import
Re: "Failed to connect to Postgres database"
Correction of my previous email :
This is the correct ssl connection, not the one before via socket:
(base) postgres@pc:~$ psql -p5433 -h localhost
Password for user postgres:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.
postgres=# \l
List of databases
Name| Owner | Encoding | Collate | Ctype | Access privileges
+--+--+-+-+---
fabmnet_ca | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
(4 rows)
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host
"localhost" at port "5433".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
postgres=# \q
(base) postgres@pc:~$ psql -p5433 -h localhost -d fabmnet_ca
Password for user postgres:
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.
fabmnet_ca=# \conninfo
You are connected to database "fabmnet_ca" as user "postgres" on host
"localhost" at port "5433".
fabmnet_ca=# \l
List of databases
Name| Owner | Encoding | Collate | Ctype | Access privileges
+--+--+-+-+---
fabmnet_ca | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
(4 rows)
fabmnet_ca=#
Anyway, I'm still struggling in understanding how to configure the ssh
connection of fabric-ca-server to fabmnet_ca database:
This is what I set in fabric-ca-server-config.yaml :
#db:
# type: sqlite3
# datasource: fabric-ca-server.db
# tls:
# enabled: false
# certfiles:
# client:
#certfile:
#keyfile:
db:
type: postgres
datasource: host=localhost port=5433 user=postgres password=pwd
dbname=fabmnet_ca sslmode=verify-full
tls:
enabled: false
certfiles:
client:
certfile:
keyfile:
Initializing the fabric-ca-server gives "Failed to connect to Postgres
database" and in postgresql-11-fabmnet.log : sslv3 alert bad certificate
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
2019/09/27 17:07:27 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/27 17:07:27 [INFO] Server Version: 1.4.4
2019/09/27 17:07:27 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/27 17:07:27 [INFO] The CA key and certificate already exist
2019/09/27 17:07:27 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/27 17:07:27 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/27 17:07:27 [WARNING] Failed to connect to database 'fabmnet_ca'
2019/09/27 17:07:27 [WARNING] Failed to connect to database 'postgres'
2019/09/27 17:07:27 [WARNING] Failed to connect to database 'template1'
2019/09/27 17:07:27 [ERROR] Error occurred initializing database: Failed to
connect to Postgres database. Postgres requires connecting to a specific
database, the following databases were tried: [fabmnet_ca postgres
template1]. Please create one of these database before continuing
2019/09/27 17:07:27 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/27 17:07:27 [INFO] Initialization was successful
/var/log/postgresql/postgresql-11-fabmnet.log : 2019-09-27 17:07:27.159
CEST [6626] [unknown]@[unknown] LOG: could not accept SSL connection:
sslv3 alert bad certificate
Why it says "sslv3 alert bad certificate" if it's exactly the same
certificate used when connecting to the same database with ssl in postgres
environment as shown above?
Marco
Il giorno ven 27 set 2019 alle ore 17:10 Marco Ippolito <
ippolito.ma...@gmail.com> ha scritto:
> Hi Adrian,
>
> thanks to your kind explanation I discovered that I can connect to the db
> without explicitly calling the belonging cluster:
>
> (base) postgres@pc:~$ psql -p5433 -d fabmnet_ca
> psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> Type "help" for help.
>
>
Re: "Failed to connect to Postgres database"
09-27 19:55:03.691 CEST [3313] postgres@fabmnet_ca ERROR:
database "fabmnet_ca" already exists
2019-09-27 19:55:03.691 CEST [3313] postgres@fabmnet_ca STATEMENT:
CREATE DATABASE fabmnet_ca
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b
admin:adminpw
2019/09/27 19:57:58 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/27 19:57:58 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca
2019/09/27 19:57:58 [INFO] Server Version: 1.4.4
2019/09/27 19:57:58 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/27 19:57:58 [INFO] The CA key and certificate already exist
2019/09/27 19:57:58 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/27 19:57:58 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/27 19:57:58 [INFO] Initialized postgres database at
host=localhost port=5433 user= password= dbname=fabmnet_ca
sslmode=disable
2019/09/27 19:57:58 [INFO] The Idemix issuer public and secret key
files already exist
2019/09/27 19:57:58 [INFO]secret key file location:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
2019/09/27 19:57:58 [INFO]public key file location:
/home/marco/fabric/fabric-ca/IssuerPublicKey
2019/09/27 19:57:58 [INFO] The Idemix issuer revocation public and
secret key files already exist
2019/09/27 19:57:58 [INFO]private key file location:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
2019/09/27 19:57:58 [INFO]public key file location:
/home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
2019/09/27 19:57:58 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/27 19:57:58 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/27 19:57:58 [INFO] Listening on http://0.0.0.0:7054
Does it mean that in order to use postgresql-11 with fabric-ca I have to
use only socket connection?
And if this is the case, why?
Marco
Il giorno ven 27 set 2019 alle ore 18:37 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:
> On 9/27/19 8:20 AM, Marco Ippolito wrote:
> > Correction of my previous email :
> >
> > This is the correct ssl connection, not the one before via socket:
>
> A tip, when troubleshooting be as explicit as possible in your command
> line usage. So for below explicitly state the -d postgres -U postgres.
> This will save you issues with default values and environment values
> that you don't know about changing the command. This is not the issue
> here, just a heads up for future use.
>
> More below.
>
> >
> > (base) postgres@pc:~$ psql -p5433 -h localhost
> > Password for user postgres:
> > psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
> > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> > 256, compression: off)
>
> > fabmnet_ca=#
> >
> > Anyway, I'm still struggling in understanding how to configure the ssh
> > connection of fabric-ca-server to fabmnet_ca database:
> >
> > This is what I set in fabric-ca-server-config.yaml :
> >
> > #db:
> > # type: sqlite3
> > # datasource: fabric-ca-server.db
> > # tls:
> > # enabled: false
> > # certfiles:
> > # client:
> > #certfile:
> > #keyfile:
> >
> >
> > db:
> >type: postgres
> >datasource: host=localhost port=5433 user=postgres password=pwd
> > dbname=fabmnet_ca sslmode=verify-full
>
> For now I would drop the sslmode or set it to require.
> If I am following correctly, if you are cert authentication with fabric-ca:
>
>
> https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#postgresql
>
> Then you need to fill in the certfile(s) sections. I know you have
> tls.enabled: false. I think that the server is taking the datasource as
> priority and trying a verify-full without the necessary cert
> information. That is why I suggested backing off on the SSL requirements
> to see if you can make a connection. For what the sslmode options means
> go here:
>
> https://www.postgresql.org/docs/11/libpq-connect.html#LIBPQ-PARAMKEYWORDS
>
> and search in page for sslmode.
>
> Plan B would be to fill in the certfile(s) information.
>
> As to your question below as to why the psql connection works. You are
> not specifying an sslmode to the connection so it defaults to a sslmode of:
>
> prefer (default)
>
> first try an SSL connection; if that fails, try a non-SSL connection
>
> There is no cert authentication going on in that case, so you connect.
> The connection is done using SSL, it just does not verify the cert
Re: "Failed to connect to Postgres database"
Sorry again,
I was cheering up too quickly.
With this configuration in fabric-ca-server-config.yaml :
db:
type: postgres
datasource: host=localhost port=5433 user=postgres password=1234
dbname=fabmnet_ca sslmode=disable
tls:
enabled: false
certfiles:
client:
certfile:
keyfile:
the output of starting fabric-ca-server at first glance seems ok:
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server start -b admin:adminpw
2019/09/27 20:11:43 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/27 20:11:44 [INFO] Starting server in home directory:
/home/marco/fabric/fabric-ca
2019/09/27 20:11:44 [INFO] Server Version: 1.4.4
2019/09/27 20:11:44 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/27 20:11:44 [INFO] The CA key and certificate already exist
2019/09/27 20:11:44 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/27 20:11:44 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/27 20:11:44 [INFO] Initialized postgres database at host=localhost
port=5433 user= password= dbname=fabmnet_ca sslmode=disable
2019/09/27 20:11:44 [INFO] The Idemix issuer public and secret key files
already exist
2019/09/27 20:11:44 [INFO]secret key file location:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerSecretKey
2019/09/27 20:11:44 [INFO]public key file location:
/home/marco/fabric/fabric-ca/IssuerPublicKey
2019/09/27 20:11:44 [INFO] The Idemix issuer revocation public and secret
key files already exist
2019/09/27 20:11:44 [INFO]private key file location:
/home/marco/fabric/fabric-ca/msp/keystore/IssuerRevocationPrivateKey
2019/09/27 20:11:44 [INFO]public key file location:
/home/marco/fabric/fabric-ca/IssuerRevocationPublicKey
2019/09/27 20:11:44 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/27 20:11:44 [INFO] Operation Server Listening on 127.0.0.1:9443
2019/09/27 20:11:44 [INFO] Listening on http://0.0.0.0:7054
but the /var/log/postgresql/postgresql-11-fabmnet.log gives us a different,
not so bright, perspective:
2019-09-27 20:11:44.012 CEST [3450] postgres@fabmnet_ca ERROR: database
"fabmnet_ca" already exists
2019-09-27 20:11:44.012 CEST [3450] postgres@fabmnet_ca STATEMENT: CREATE
DATABASE fabmnet_ca
2019-09-27 20:11:44.015 CEST [3451] postgres@fabmnet_ca ERROR: duplicate
key value violates unique constraint "properties_pkey"
2019-09-27 20:11:44.015 CEST [3451] postgres@fabmnet_ca DETAIL: Key
(property)=(identity.level) already exists.
2019-09-27 20:11:44.015 CEST [3451] postgres@fabmnet_ca STATEMENT: INSERT
INTO properties (property, value) VALUES ('identity.level', '0'),
('affiliation.level', '0'), ('certificate.level', '0'), ('cred$
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca ERROR: duplicate
key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca DETAIL: Key
(name)=(org2) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca ERROR: duplicate
key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca DETAIL: Key
(name)=(org2.department1) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca ERROR: duplicate
key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca DETAIL: Key
(name)=(org1) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca ERROR: duplicate
key value violates unique constraint "affiliations_name_key"
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca DETAIL: Key
(name)=(org1.department1) already exists.
2019-09-27 20:11:44.017 CEST [3451] postgres@fabmnet_ca STATEMENT:
INSERT INTO affiliations (name, prekey, level)
VALUES ($1, $2, $3)
What do these continuous attempts to duplicate key value mean? It doesn't
look so good this fabric-ca-server connection with postgresql-11 's db ...
Marco
Il giorno ven 27 set 2019 alle ore 20:02 Marco Ippolito <
ippolito.ma...@gmail.com> ha scritto:
> Thank you very much Adrian.
> Two things:
>
> 1)
> Why if I just specify through port the cluster and the host connection I
> connect correctl
Re: "Failed to connect to Postgres database"
Hi Adrian, Il giorno ven 27 set 2019 alle ore 21:39 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto: > On 9/27/19 11:02 AM, Marco Ippolito wrote: > > Thank you very much Adrian. > > Two things: > > > > 1) > > Why if I just specify through port the cluster and the host connection > > I connect correctly with SSL, > > but if I specify also the database and the user it connects it doesn't > > usel SSL connection, or at least it doesn't say it uses SSL? : > > > Can you show the contents of pg_hba.conf file for the 11/fabmnet > cluster. The file will be in: > > /etc/postgresql/11/fabmnet/ > > > /etc/postgresql/11/fabmnet/pg_hba.conf : # 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 # Allow connections from localhost only to fabmnet_ca for postgres user hostssl fabmnet_ca postgreslocalhost cert # IPv6 local connections: hostall all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer hostreplication all 127.0.0.1/32md5 hostreplication all ::1/128 md5 > More below. > > > > > 2) > > In fabric-ca-server-config.yaml > > > >a) if I set: > > > > db: > >type: postgres > >datasource: host=localhost port=5433 user=postgres password=1234 > > dbname=fabmnet_ca sslmode=allow > > According to the fabric-ca docs, allow is not one of the valid values: > > > https://hyperledger-fabric-ca.readthedocs.io/en/release-1.4/users-guide.html#postgresql > > "Specifying sslmode configures the type of SSL authentication. Valid > values for sslmode are: > > ModeDescription > disable No SSL > require Always SSL (skip verification) > verify-ca Always SSL (verify that the certificate presented by the > server was signed by a trusted CA) > verify-full Same as verify-ca AND verify that the certificate > presented > by the server was signed by a trusted CA and the server hostname matches > the one in the certificate > > " > > > >tls: > >enabled: false > >certfiles: > >client: > > certfile: > > keyfile: > > > > where sslmode=allow means "first try a non-SSL connection; if that > > fails, try an SSL connection" > > > > > /var/log/postgresql/postgresql-11-fabmnet.log : > > 2019-09-27 19:43:14.194 CEST [3213] postgres@fabmnet_ca FATAL: > > client certificates can only be checked if a root certificate store is > > available > > The above tells me that the start is ignoring sslmode=allow and rolling > over into a verification mode and there are no certs specified. Please > do as requested as try sslmode=require. > > More below. > > > > >b) if I set: > > db: > >type: postgres > >datasource: host=localhost port=5433 user=postgres password=1234 > > dbname=fabmnet_ca sslmode=disable > >tls: > > enabled: false > > certfiles: > > client: > >certfile: > >keyfile: > > > > > > > > > /var/log/postgresql/postgresql-11-fabmnet.log : > > 2019-09-27 19:55:03.691 CEST [3313] postgres@fabmnet_ca ERROR: > > database "fabmnet_ca" already exists > > 2019-09-27 19:55:03.691 CEST [3313] postgres@fabmnet_ca > > STATEMENT: CREATE DATABASE fabmnet_ca > > The fabmnet_ca database has already been created. > > > > > Does it mean that in order to use postgresql-11 with fabric-ca I have to > > use only socket connection? > > And if this is the case, why? > > No you connected to localhost, though without SSL. Try again with > sslmode=require and I am pretty sure you will connect with SSL, but no > cert verification. > > > > > Marco > > > > > > fabric-ca-server-config.yaml : sslmode=require db: type: postgres datasource: host=localhost port=5433 user=postgres password=1234 dbname=fabmnet_ca sslmode=require tls: enable
Re: "Failed to connect to Postgres database"
ome/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/30 17:56:22 [INFO] Server Version: 1.4.4
2019/09/30 17:56:22 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/30 17:56:22 [INFO] The CA key and certificate already exist
2019/09/30 17:56:22 [INFO] The key is stored by BCCSP provider 'SW'
2019/09/30 17:56:22 [INFO] The certificate is at:
/home/marco/fabric/fabric-ca/ca-cert.pem
2019/09/30 17:56:22 [WARNING] Failed to connect to database 'fabmnet_ca'
2019/09/30 17:56:22 [WARNING] Failed to connect to database 'postgres'
2019/09/30 17:56:22 [WARNING] Failed to connect to database 'template1'
2019/09/30 17:56:22 [ERROR] Error occurred initializing database: Failed to
connect to Postgres database. Postgres requires connecting to a specific
database, the following databases were tried: [fabmnet_ca postgres
template1]. Please create one of these database before continuing
2019/09/30 17:56:22 [INFO] Home directory for default CA:
/home/marco/fabric/fabric-ca
2019/09/30 17:56:22 [INFO] Initialization was successful
/var/log/postgresql/postgresql-11-fabmnet.log :
2019-09-30 17:56:22.760 CEST [10651] [unknown]@[unknown] LOG: incomplete
startup packet
2019-09-30 17:56:22.760 CEST [10650] [unknown]@[unknown] LOG: incomplete
startup packet
2019-09-30 17:56:22.760 CEST [10649] [unknown]@[unknown] LOG: incomplete
startup packet
What could it mean?
Marco
Il giorno sab 28 set 2019 alle ore 23:49 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:
> On 9/28/19 12:07 AM, Marco Ippolito wrote:
> > Hi Adrian,
> >
> > Il giorno ven 27 set 2019 alle ore 21:39 Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> ha
> scritto:
> >
> > On 9/27/19 11:02 AM, Marco Ippolito wrote:
> > > Thank you very much Adrian.
> > > Two things:
> > >
> > > 1)
> > > Why if I just specify through port the cluster and the host
> > connection
> > > I connect correctly with SSL,
> > > but if I specify also the database and the user it connects it
> > doesn't
> > > usel SSL connection, or at least it doesn't say it uses SSL? :
> >
> >
> > Can you show the contents of pg_hba.conf file for the 11/fabmnet
> > cluster. The file will be in:
> >
> > /etc/postgresql/11/fabmnet/
> >
> >
> >
> >
> > /etc/postgresql/11/fabmnet/pg_hba.conf :
> >
> > # 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/32 <http://127.0.0.1/32>
>
> > md5
> >
> > # Allow connections from localhost only to fabmnet_ca for postgres user
> > hostssl fabmnet_ca postgreslocalhost cert
> >
> > # IPv6 local connections:
> > hostall all ::1/128 md5
> > # Allow replication connections from localhost, by a user with the
> > # replication privilege.
> > local replication all peer
> > hostreplication all 127.0.0.1/32 <http://127.0.0.1/32>
>
> > md5
> > hostreplication all ::1/128 md5
> >
>
> > fabric-ca-server-config.yaml : sslmode=require
> > db:
> >type: postgres
> >datasource: host=localhost port=5433 user=postgres password=1234
> > dbname=fabmnet_ca sslmode=require
> >tls:
> >enabled: false
> >certfiles:
> >client:
> > certfile:
> > keyfile:
>
> You are not including the certs or setting tls.enabled: true. Not sure
> that is the root cause at the moment.
>
> I would try just going through psql for the time being to take the
> fabric server out of the loop. Something like:
>
> psql "host=localhost port=5433 dbname=fabmnet_ca user=postgres
> sslmode=require"
>
> From below I am guessing you do not have the SSL certs setup properly
> for the fabmnet Postgres instance(the one on port 5433) and/or on the
> client. Take a look at:
>
> https://www.postgresql.org/docs/11/libpq-ssl.html
>
> >
> >
> > (base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b
> admin:adminpw
> > 2019/09/28 09:00:08 [INFO] Configuration f
Re: "Failed to connect to Postgres database" : No usage specified for certificate (update)
Hi Adrian,
important update.
After adding in fabric-ca-server-config.yaml
ca:
# Name of this CA
name: fabric_ca
# Key file (is only used to import a private key into BCCSP)
keyfile: /etc/ssl/private/fabric_ca.key
# Certificate file (default: ca-cert.pem)
certfile: /etc/ssl/certs/fabric_ca.pem
# Chain file
chainfile:
Now I get this message:
(base) marco@pc:~/fabric/fabric-ca$ fabric-ca-server init -b admin:adminpw
2019/09/30 18:10:41 [INFO] Configuration file location:
/home/marco/fabric/fabric-ca/fabric-ca-server-config.yaml
2019/09/30 18:10:41 [INFO] Server Version: 1.4.4
2019/09/30 18:10:41 [INFO] Server Levels: &{Identity:2 Affiliation:1
Certificate:1 Credential:1 RAInfo:1 Nonce:1}
2019/09/30 18:10:41 [INFO] The CA key and certificate files already exist
2019/09/30 18:10:41 [INFO] Key file location: /etc/ssl/private/fabric_ca.key
2019/09/30 18:10:41 [INFO] Certificate file location:
/etc/ssl/certs/fabric_ca.pem
2019/09/30 18:10:41 [FATAL] Initialization failure: Validation of
certificate and key failed: Invalid certificate in file
'/etc/ssl/certs/fabric_ca.pem': No usage specified for certificate
This is the start of /etc/ssl/certs/fabric_ca.pem:
-BEGIN CERTIFICATE-
MIIDlTCCAn2gAwIBAgIUCm243lybs0PNfAEdgbuw0chmjWkwDQYJKoZIhvcNAQEL
and this is its end:
xNItFJulgsA1
-END CERTIFICATE-
What does it mean "No usage specified for certificate" ?
Il giorno lun 30 set 2019 alle ore 18:01 Marco Ippolito <
ippolito.ma...@gmail.com> ha scritto:
> Following the indications found here:
> https://joelonsql.com/2013/04/27/securing-postgresql-using-hostssl-cert-clientcert1/
> I created and modified these files:
> CA:
>
> root@pc:/home/marco# ls -lah /etc/ssl/private/fabric_ca.key
> -rw-r- 1 root ssl-cert 1.8K Sep 30 14:50 /etc/ssl/private/fabric_ca.key
>
> (base) marco@pc:~$ ls -lah /usr/local/share/ca-certificates/fabric_ca.crt
> -rw-r--r-- 1 root root 1.3K Sep 30 15:43
> /usr/local/share/ca-certificates/fabric_ca.crt
>
> (base) marco@pc:~$ ls -lah /etc/ssl/certs/fabric_ca.pem
> lrwxrwxrwx 1 root root 46 Sep 30 15:45 /etc/ssl/certs/fabric_ca.pem ->
> /usr/local/share/ca-certificates/fabric_ca.crt
> (base) marco@pc:~$
>
> PostgreSQL-Server:
>
> (base) postgres@pc:~$ ls -lah /var/lib/postgresql/11/fabmnet/server.key
> -r 1 postgres postgres 1.7K Sep 30 16:05
> /var/lib/postgresql/11/fabmnet/server.key
>
> (base) postgres@pc:~$ ls -lah /var/lib/postgresql/11/fabmnet/server.crt
> -rw-r--r-- 1 postgres postgres 1.2K Sep 30 16:34
> /var/lib/postgresql/11/fabmnet/server.crt
>
> (base) postgres@pc:~$ ls -lah /var/lib/postgresql/11/fabmnet/root.crt
> -rw--- 1 postgres postgres 1.4K Sep 30 13:39
> /var/lib/postgresql/11/fabmnet/root.crt
>
> (base) marco@pc:~$ ls -ltr /usr/local/share/ca-certificates/fabric_ca.crt
> -rw-r--r-- 1 root root 1302 Sep 30 15:43
> /usr/local/share/ca-certificates/fabric_ca.crt
>
> (base) marco@pc:~$ ls -ltr
> /usr/local/share/ca-certificates/fabric_ca_postgresql.crt
> -rw--- 1 root root 1354 Sep 30 17:12
> /usr/local/share/ca-certificates/fabric_ca_postgresql.crt
>
> (base) marco@pc:~$ ls -ltr /etc/ssl/certs/fabric_ca.pem
> lrwxrwxrwx 1 root root 46 Sep 30 15:45 /etc/ssl/certs/fabric_ca.pem ->
> /usr/local/share/ca-certificates/fabric_ca.crt
>
> (base) marco@pc:~$ ls -ltr /etc/ssl/certs/fabric_ca_postgresql.pem
> lrwxrwxrwx 1 root root 57 Sep 30 17:12
> /etc/ssl/certs/fabric_ca_postgresql.pem ->
> /usr/local/share/ca-certificates/fabric_ca_postgresql.crt
>
>
> I set /etc/postgresql/11/fabmnet/pg_hba.conf in this way:
>
>
> # 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
>
> # Allow connections from localhost only to fabmnet_ca for postgres user
> clientcert
> hostssl fabmnet_ca +ssl_fabric_ca_certusers192.168.1.0/24
> certclientcert=1
>
> # IPv6 local connections:
> hostall all ::1/128 md5
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> local replication all peer
> hostreplication all 127.0.0.1/32md5
> hostreplication all ::1/128 md5
>
> PostgreSQL-client :
>
> (base) marco@pc:~$ ls -ltr ~/.postgresql/root.crt
> -rw--- 1 postgres postgres 1354 Sep 30 17:22
> /h
Error retrieving PostgreSQL DB information with Coturn
In order to understand how to use Postgresql-11 with Coturn, https://github.com/coturn/coturn , I created a postgresql-11 dabatase using /usr/local/share/turnserver/schema.sql : CREATE TABLE turnusers_lt ( realm varchar(127) default '', name varchar(512), hmackey char(128), PRIMARY KEY (realm,name) ); CREATE TABLE turn_secret ( realm varchar(127) default '', value varchar(256), primary key (realm,value) ); CREATE TABLE allowed_peer_ip ( realm varchar(127) default '', ip_range varchar(256), primary key (realm,ip_range) ); CREATE TABLE denied_peer_ip ( realm varchar(127) default '', ip_range varchar(256), primary key (realm,ip_range) ); CREATE TABLE turn_origin_to_realm ( origin varchar(127), realm varchar(127), primary key (origin) ); CREATE TABLE turn_realm_option ( realm varchar(127) default '', opt varchar(32), value varchar(128), primary key (realm,opt) ); CREATE TABLE oauth_key ( kid varchar(128), ikm_key varchar(256), timestamp bigint default 0, lifetime integer default 0, as_rs_alg varchar(64) default '', realm varchar(127), primary key (kid) ); But when trying to execute secure_relay_with_db_psql.sh : (base) marco@marco-U36SG:~/turnserver-4.5.0.8/examples/scripts/longtermsecuredb$ ./secure_relay_with_db_psql.sh 0: WARNING: Cannot find config file: turnserver.conf. Default and command-line settings will be used. 0: Listener address to use: 127.0.0.1 0: Listener address to use: ::1 0: Relay address to use: 127.0.0.1 0: Relay address to use: ::1 0: 300 bytes per second allowed per session 0: WARNING: Cannot find config file: turnserver.conf. Default and command-line settings will be used. 0: RFC 3489/5389/5766/5780/6062/6156 STUN/TURN Server Version Coturn-4.5.0.8 'dan Eider' 0: Max number of open files/sockets allowed for this process: 4096 0: Due to the open files/sockets limitation, max supported number of TURN Sessions possible is: 2000 (approximately) 0: Show him the instruments, Practical Frost: 0: TLS supported 0: DTLS supported 0: DTLS 1.2 supported 0: TURN/STUN ALPN supported 0: Third-party authorization (oAuth) supported 0: GCM (AEAD) supported 0: OpenSSL compile-time version: OpenSSL 1.1.1 11 Sep 2018 (0x1010100f) 0: SQLite is not supported 0: Redis supported 0: PostgreSQL supported 0: MySQL supported 0: MongoDB is not supported 0: 0: Default Net Engine version: 3 (UDP thread per CPU core) = 0: Domain name: 0: Default realm: north.gov 0: oAuth server name: blackdow.carleon.gov 0: WARNING: cannot find certificate file: turn_server_cert.pem (1) 0: WARNING: cannot start TLS and DTLS listeners because certificate file is not set properly 0: WARNING: cannot find private key file: turn_server_pkey.pem (1) 0: WARNING: cannot start TLS and DTLS listeners because private key file is not set properly Cannot create pid file: /var/run/turnserver.pid: Permission denied 0: Cannot create pid file: /var/run/turnserver.pid 0: pid file created: /var/tmp/turnserver.pid 0: IO method (main listener thread): epoll (with changelist) 0: Wait for relay ports initialization... 0: relay 127.0.0.1 initialization... 0: relay 127.0.0.1 initialization done 0: relay ::1 initialization... 0: relay ::1 initialization done 0: Relay ports initialization done 0: IO method (general relay thread): epoll (with changelist) 0: turn server id=0 created 0: IPv4. SCTP listener opened on : 127.0.0.1:3478 0: IPv4. TCP listener opened on : 127.0.0.1:3478 0: IPv4. SCTP listener opened on : 127.0.0.1:3479 0: IPv4. TCP listener opened on : 127.0.0.1:3479 0: IPv6. SCTP listener opened on : ::1:3478 0: IPv6. TCP listener opened on : ::1:3478 0: IPv6. SCTP listener opened on : ::1:3479 0: IPv6. TCP listener opened on : ::1:3479 0: IO method (general relay thread): epoll (with changelist) 0: turn server id=1 created 0: IPv6. TCP listener opened on : ::1:3479 0: IPv6. UDP listener opened on: ::1:3479 0: Total General servers: 3 0: IO method (auth thread): epoll (with changelist) 0: IO method (auth thread): epoll (with changelist) 0: IO method (admin thread): epoll (with changelist) 0: IPv4. CLI listener opened on : 127.0.0.1:5766 0: PostgreSQL DB connection success: host=localhost dbname=coturn user=turn password=turn connect_timeout=30 0: ERROR: Error retrieving PostgreSQL DB information: ERROR: permission denied for
Re: Error retrieving PostgreSQL DB information with Coturn
Thank you very much Adrian! I feel ashamed for such a silly mistake coturn=# \dt List of relations Schema | Name | Type | Owner +--+---+--- public | admin_user | table | turn public | allowed_peer_ip | table | turn public | denied_peer_ip | table | turn public | oauth_key| table | turn public | turn_origin_to_realm | table | turn public | turn_realm_option| table | turn public | turn_secret | table | turn public | turnusers_lt | table | turn (8 rows) 1: IPv4. CLI listener opened on : 127.0.0.1:5766 1: PostgreSQL DB connection success: host=localhost dbname=coturn user=turn password=turn connect_timeout=30 Marco Il giorno mar 14 gen 2020 alle ore 17:53 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto: > On 1/14/20 8:33 AM, Marco Ippolito wrote: > > In order to understand how to use Postgresql-11 with Coturn, > > https://github.com/coturn/coturn , > > I created a postgresql-11 dabatase using > > /usr/local/share/turnserver/schema.sql : > > > > CREATE TABLE turnusers_lt ( > > realm varchar(127) default '', > > name varchar(512), > > hmackey char(128), > > PRIMARY KEY (realm,name) > > ); > > > > CREATE TABLE turn_secret ( > > realm varchar(127) default '', > > value varchar(256), > > primary key (realm,value) > > ); > > > > CREATE TABLE allowed_peer_ip ( > > realm varchar(127) default '', > > ip_range varchar(256), > > primary key (realm,ip_range) > > ); > > > > CREATE TABLE denied_peer_ip ( > > realm varchar(127) default '', > > ip_range varchar(256), > > primary key (realm,ip_range) > > ); > > > > CREATE TABLE turn_origin_to_realm ( > > origin varchar(127), > > realm varchar(127), > > primary key (origin) > > ); > > > > CREATE TABLE turn_realm_option ( > > realm varchar(127) default '', > > opt varchar(32), > > value varchar(128), > > primary key (realm,opt) > > ); > > > > CREATE TABLE oauth_key ( > > kid varchar(128), > > ikm_key varchar(256), > > timestamp bigint default 0, > > lifetime integer default 0, > > as_rs_alg varchar(64) default '', > > realm varchar(127), > > primary key (kid) > > ); > > > > > > But when trying to execute secure_relay_with_db_psql.sh : > > > > (base) > > marco@marco-U36SG > :~/turnserver-4.5.0.8/examples/scripts/longtermsecuredb$ > > ./secure_relay_with_db_psql.sh > > 0: WARNING: Cannot find config file: turnserver.conf. Default and > > command-line settings will be > > used. > > 0: Listener address to use: 127.0.0.1 > > 0: Listener address to use: ::1 > > 0: Relay address to use: 127.0.0.1 > > 0: Relay address to use: ::1 > > 0: 300 bytes per second allowed per session > > 0: WARNING: Cannot find config file: turnserver.conf. Default and > > command-line settings will > > be used. > > 0: RFC 3489/5389/5766/5780/6062/6156 STUN/TURN Server > > Version Coturn-4.5.0.8 'dan Eider' > > 0: Max number of open files/sockets allowed for this process: 4096 > > 0: Due to the open files/sockets limitation, > > max supported number of TURN Sessions possible is: 2000 > (approximately) > > 0: Show him the instruments, Practical Frost: > > > > 0: TLS supported > > 0: DTLS supported > > 0: DTLS 1.2 supported > > 0: TURN/STUN ALPN supported > > 0: Third-party authorization (oAuth) supported > > 0: GCM (AEAD) supported > > 0: OpenSSL compile-time version: OpenSSL 1.1.1 11 Sep 2018 > > (0x1010100f) > > 0: SQLite is not supported > > 0: Redis supported > > 0: PostgreSQL supported > > 0: MySQL supported > > 0: MongoDB is not supported > > 0: > > 0: Default Net Engine version: 3 (UDP thread per CPU core) > > > > = > > > > 0: Domain name: > > 0: Default realm: north.gov &l
Unable to connect to the database: TypeError: net.Socket is not a constructor
I'm trying to connect to a postgres database (Postgresql-11) within my
nodejs-vue.js app, but in console I'm getting this error:
[HMR] Waiting for update signal from WDS...
pg.js?c8c2:27 Unable to connect to the database: TypeError: net.Socket
is not a constructor
at new Connection
(webpack-internal:///./node_modules/pg/lib/connection.js:22:34)
at new Client
(webpack-internal:///./node_modules/pg/lib/client.js:55:37)
at Promise.tap.query
(webpack-internal:///./node_modules/sequelize/lib/dialects/postgres
/connection-manager.js:124:26)
at ConnectionManager.connect
(webpack-internal:///./node_modules/sequelize/lib/dialects
/postgres/connection-manager.js:121:12)
at eval
(webpack-internal:///./node_modules/sequelize/lib/dialects/abstract/connection-
manager.js:318:50)
From previous event:
at ConnectionManager._connect
(webpack-internal:///./node_modules/sequelize/lib/dialects
/abstract/connection-manager.js:318:8)
at ConnectionManager.getConnection
(webpack-internal:///./node_modules/sequelize/lib/dialects
/abstract/connection-manager.js:254:46)
at eval
(webpack-internal:///./node_modules/sequelize/lib/sequelize.js:640:36)
From previous event:
at eval
(webpack-internal:///./node_modules/sequelize/lib/sequelize.js:631:53)
at eval
(webpack-internal:///./node_modules/retry-as-promised/index.js:70:21)
at new Promise ()
at retryAsPromised
(webpack-internal:///./node_modules/retry-as-promised/index.js:60:10)
at eval
(webpack-internal:///./node_modules/sequelize/lib/sequelize.js:631:30)
From previous event:
at Sequelize.query
(webpack-internal:///./node_modules/sequelize/lib/sequelize.js:580:23)
at Sequelize.authenticate
(webpack-internal:///./node_modules/sequelize/lib/sequelize.js:892:17)
at eval (webpack-internal:///./src/plugins/db/pg.js:23:11)
at Object../src/plugins/db/pg.js (https://ggc.world/js/app.js:1128:1
)
at __webpack_require__ (https://ggc.world/js/app.js:785:30)
at fn (https://ggc.world/js/app.js:151:20)
at eval (webpack-internal:///./src/main.js:16:72)
at Module../src/main.js (https://ggc.world/js/app.js:1083:1)
at __webpack_require__ (https://ggc.world/js/app.js:785:30)
at fn (https://ggc.world/js/app.js:151:20)
at Object.1 (https://ggc.world/js/app.js:1141:18)
at __webpack_require__ (https://ggc.world/js/app.js:785:30)
at checkDeferredModules (https://ggc.world/js/app.js:46:23)
at https://ggc.world/js/app.js:861:18
at https://ggc.world/js/app.js:864:10
In /src/main.js :
import '@/plugins/db/pg';
(base) /src/plugins/db$ ls -lah
total 28K
drwxr-xr-x 6 marco marco 4,0K apr 20 15:42 .
drwxr-xr-x 3 marco marco 4,0K apr 20 15:41 ..
drwxr-xr-x 2 marco marco 4,0K apr 20 17:20 config
drwxr-xr-x 2 marco marco 4,0K apr 20 15:48 migrations
drwxr-xr-x 2 marco marco 4,0K apr 20 15:48 models
-rw-r--r-- 1 marco marco 819 apr 20 08:53 pg.js
drwxr-xr-x 2 marco marco 4,0K apr 20 17:21 seeders
nano pg.js
const { Pool } = require('pg');
const { Sequelize } = require('sequelize');
const pool = new Pool();
const sequelize = new Sequelize('pusers', 'postgres', 'pwd', {
host: 'localhost',
dialect: 'postgres',
pool: {
max: 5,
min: 0,
acquire: 3,
idle: 1
}
});
sequelize
.authenticate()
.then(() => {
console.log('Connection has been established successfully.');
})
.catch(err => {
console.log('Unable to connect to the database:', err);
});
module.exports = {
query: (text, params, callback) => {
return pool.query(text, params, callback);
},
}
Populated the sample database with a row:
pusers=# SELECT schemaname,relname,n_live_tup
pusers-# FROM pg_stat_user_tables
pusers-# ORDER BY n_live_tup DESC;
schemaname |relname| n_live_tup
+---+
public | pusers| 1
public | SequelizeMeta | 1
(2 rows)
I read here:
https://stackoverflow.com/questions/40599069/node-js-net-socket-is-not-a-constructor
that
"there are no plain TCP sockets in the browser, so that is why trying to
use `net.Socket` in the browser (via webpack, browserify, etc.) won't work"
Environment Info:
System:
OS: Linux 5.3 Ubuntu 18.04.4 LTS (Bionic Beaver)
CPU: (8) x64 Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz
Binaries:
Node: 12.15.0 - ~/.nvm/versions/node/v12.15.0/bin/node
Yarn: 1.22.4 - ~/.nvm/versions/node/v12.15.0/bin/yarn
npm: 6.14.4 - ~/.nvm/versions/node/v12.15.0/bin/npm
Browsers:
Chrome: 81.0.4044.92
Firefox: 75.0
npmGlobalPackages:
@vue/cli: 4.2.3
So... how to solve the problem?
Looking for
Re: Unable to connect to the database: TypeError: net.Socket is not a constructor
Il giorno lun 20 apr 2020 alle ore 20:11 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto: > On 4/20/20 10:50 AM, Marco Ippolito wrote: > > I'm trying to connect to a postgres database (Postgresql-11) within my > > nodejs-vue.js app, but in console I'm getting this error: > > > > [HMR] Waiting for update signal from WDS... > > pg.js?c8c2:27 Unable to connect to the database: TypeError: > > net.Socket is not a constructor > > > Environment Info: > > > >System: > > OS: Linux 5.3 Ubuntu 18.04.4 LTS (Bionic Beaver) > > CPU: (8) x64 Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz > >Binaries: > > Node: 12.15.0 - ~/.nvm/versions/node/v12.15.0/bin/node > > Yarn: 1.22.4 - ~/.nvm/versions/node/v12.15.0/bin/yarn > > npm: 6.14.4 - ~/.nvm/versions/node/v12.15.0/bin/npm > >Browsers: > > Chrome: 81.0.4044.92 > > Firefox: 75.0 > >npmGlobalPackages: > > @vue/cli: 4.2.3 > > > > So... how to solve the problem? > > From what I can gather the issue is that the code is running in the > context of the browser and net.Socket is not supported there. It seems > that the fix is to run the database code in node.js and pass the data > from there to vue.js. Honestly, this whole reactive code thing is still > something I'm wrapping my head around. I did find some tutorials > searching on 'node.js vue.js Sequelize postgresql'. Most seemed to use > express.js as the glue between node and vue. > > Thank you Adrian for your very kind response. express.js is the server framework for node.js So...two questions come in mind: am I obliged to use express.js as glue between node and vue, if I already use nginx as web server? And if I'm obliged to use another web server apart from nginx to glue node and vue, would it be possible to use a golang web-server, which could be a further complication but would have its own positive sides? Marco > > > Looking forward to your kind help. > > Marco > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Unable to connect to the database: TypeError: net.Socket is not a constructor
Thank you very much Tim and Adrian for your very kind and valuable
information and suggestions.
Marco
Il giorno mar 21 apr 2020 alle ore 00:47 Tim Cross
ha scritto:
>
> Marco Ippolito writes:
>
> > I'm trying to connect to a postgres database (Postgresql-11) within my
> > nodejs-vue.js app, but in console I'm getting this error:
> >
> > [HMR] Waiting for update signal from WDS...
> > pg.js?c8c2:27 Unable to connect to the database: TypeError:
> net.Socket
> > is not a constructor
> > at new Connection
> > (webpack-internal:///./node_modules/pg/lib/connection.js:22:34)
> > at new Client
> > (webpack-internal:///./node_modules/pg/lib/client.js:55:37)
> > at Promise.tap.query
> > (webpack-internal:///./node_modules/sequelize/lib/dialects/postgres
> > /connection-manager.js:124:26)
> > at ConnectionManager.connect
> > (webpack-internal:///./node_modules/sequelize/lib/dialects
> > /postgres/connection-manager.js:121:12)
> > at eval
> >
> (webpack-internal:///./node_modules/sequelize/lib/dialects/abstract/connection-
> > manager.js:318:50)
> > From previous event:
> > at ConnectionManager._connect
> > (webpack-internal:///./node_modules/sequelize/lib/dialects
> > /abstract/connection-manager.js:318:8)
> > at ConnectionManager.getConnection
> > (webpack-internal:///./node_modules/sequelize/lib/dialects
> > /abstract/connection-manager.js:254:46)
> > at eval
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:640:36)
> > From previous event:
> > at eval
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:631:53)
> > at eval
> > (webpack-internal:///./node_modules/retry-as-promised/index.js:70:21)
> > at new Promise ()
> > at retryAsPromised
> > (webpack-internal:///./node_modules/retry-as-promised/index.js:60:10)
> > at eval
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:631:30)
> > From previous event:
> > at Sequelize.query
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:580:23)
> > at Sequelize.authenticate
> > (webpack-internal:///./node_modules/sequelize/lib/sequelize.js:892:17)
> > at eval (webpack-internal:///./src/plugins/db/pg.js:23:11)
> > at Object../src/plugins/db/pg.js (
> https://ggc.world/js/app.js:1128:1
> > )
> > at __webpack_require__ (https://ggc.world/js/app.js:785:30)
> > at fn (https://ggc.world/js/app.js:151:20)
> > at eval (webpack-internal:///./src/main.js:16:72)
> > at Module../src/main.js (https://ggc.world/js/app.js:1083:1)
> > at __webpack_require__ (https://ggc.world/js/app.js:785:30)
> > at fn (https://ggc.world/js/app.js:151:20)
> > at Object.1 (https://ggc.world/js/app.js:1141:18)
> > at __webpack_require__ (https://ggc.world/js/app.js:785:30)
> > at checkDeferredModules (https://ggc.world/js/app.js:46:23)
> > at https://ggc.world/js/app.js:861:18
> > at https://ggc.world/js/app.js:864:10
> >
> > In /src/main.js :
> >
> > import '@/plugins/db/pg';
> >
> > (base) /src/plugins/db$ ls -lah
> > total 28K
> > drwxr-xr-x 6 marco marco 4,0K apr 20 15:42 .
> > drwxr-xr-x 3 marco marco 4,0K apr 20 15:41 ..
> > drwxr-xr-x 2 marco marco 4,0K apr 20 17:20 config
> > drwxr-xr-x 2 marco marco 4,0K apr 20 15:48 migrations
> > drwxr-xr-x 2 marco marco 4,0K apr 20 15:48 models
> > -rw-r--r-- 1 marco marco 819 apr 20 08:53 pg.js
> > drwxr-xr-x 2 marco marco 4,0K apr 20 17:21 seeders
> >
> >
> > nano pg.js
> >
> > const { Pool } = require('pg');
> > const { Sequelize } = require('sequelize');
> > const pool = new Pool();
> > const sequelize = new Sequelize('pusers', 'postgres', 'pwd', {
> > host: 'localhost',
> > dialect: 'postgres',
> > pool: {
> > max: 5,
> > min: 0,
> > acquire: 3,
> > idle: 1
> > }
> > });
> > sequelize
> > .authenticate()
> > .then(() => {
> > console.log('Connection has been established successfully.');
> > })
> > .catch(err => {
> > console.log('Unable to connect to the database:', err
