Обсуждение: 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)
);
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)
);
(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: 3000000 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 table
allowed_peer_ip
But these are the tables of coturndb :
coturn=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | admin_user | table | postgres
public | allowed_peer_ip | table | postgres
public | denied_peer_ip | table | postgres
public | oauth_key | table | postgres
public | turn_origin_to_realm | table | postgres
public | turn_realm_option | table | postgres
public | turn_secret | table | postgres
public | turnusers_lt | table | postgres
(8 rows)
coturn=# \d allowed_peer_ip
Table "public.allowed_peer_ip"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+-----------------------
realm | character varying(127) | | not null | ''::character varying
ip_range | character varying(256) | | not null |
Indexes:
"allowed_peer_ip_pkey" PRIMARY KEY, btree (realm, ip_range)
coturn=# \d denied_peer_ip
Table "public.denied_peer_ip"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+-----------------------
realm | character varying(127) | | not null | ''::character varying
ip_range | character varying(256) | | not null |
Indexes:
"denied_peer_ip_pkey" PRIMARY KEY, btree (realm, ip_range)
coturn=# \d turn_secret
Table "public.turn_secret"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------
realm | character varying(127) | | not null | ''::character varying
value | character varying(256) | | not null |
Indexes:
"turn_secret_pkey" PRIMARY KEY, btree (realm, value)
Why it says "probably, the tables 'allowed_peer_ip' and/or 'denied_peer_ip' have to be upgraded to include the realm column" ?
Why it says "permission denied" for table turn_secret and for table allowed_peer_ip?
How to solve the problem?
Marco
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: 3000000 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 <http://north.gov> > 0: oAuth server name: blackdow.carleon.gov > <http://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 > <http://127.0.0.1:3478> > 0: IPv4. TCP listener opened on : 127.0.0.1:3478 > <http://127.0.0.1:3478> > 0: IPv4. SCTP listener opened on : 127.0.0.1:3479 > <http://127.0.0.1:3479> > 0: IPv4. TCP listener opened on : 127.0.0.1:3479 > <http://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 > <http://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 table > allowed_peer_ip > > But these are the tables of coturndb : > > coturn=# \dt > List of relations > Schema | Name | Type | Owner > --------+----------------------+-------+---------- > public | admin_user | table | postgres > public | allowed_peer_ip | table | postgres > public | denied_peer_ip | table | postgres > public | oauth_key | table | postgres > public | turn_origin_to_realm | table | postgres > public | turn_realm_option | table | postgres > public | turn_secret | table | postgres > public | turnusers_lt | table | postgres > (8 rows) > > coturn=# \d allowed_peer_ip > Table "public.allowed_peer_ip" > Column | Type | Collation | Nullable | > Default > > ----------+------------------------+-----------+----------+----------------------- > realm | character varying(127) | | not null | > ''::character varying > ip_range | character varying(256) | | not null | > Indexes: > "allowed_peer_ip_pkey" PRIMARY KEY, btree (realm, ip_range) > > coturn=# \d denied_peer_ip > Table "public.denied_peer_ip" > Column | Type | Collation | Nullable | > Default > > ----------+------------------------+-----------+----------+----------------------- > realm | character varying(127) | | not null | > ''::character varying > ip_range | character varying(256) | | not null | > Indexes: > "denied_peer_ip_pkey" PRIMARY KEY, btree (realm, ip_range) > > coturn=# \d turn_secret > Table "public.turn_secret" > Column | Type | Collation | Nullable | > Default > > --------+------------------------+-----------+----------+----------------------- > realm | character varying(127) | | not null | > ''::character varying > value | character varying(256) | | not null | > Indexes: > "turn_secret_pkey" PRIMARY KEY, btree (realm, value) > > Why it says "probably, the tables 'allowed_peer_ip' and/or > 'denied_peer_ip' have to be upgraded to include the realm column" ? Have no idea, probably need to see if someone answers that in response to your issue: https://github.com/coturn/coturn/issues/484 > Why it says "permission denied" for table turn_secret and for table > allowed_peer_ip? 0: PostgreSQL DB connection success: host=localhost dbname=coturn user=turn password=turn connect_timeout=30 List of relations Schema | Name | Type | Owner --------+----------------------+-------+---------- ... public | allowed_peer_ip | table | postgres ... public | turn_secret | table | postgres You are connecting as user turn and trying to access tables owned by user postgres. > How to solve the problem? > > Marco -- Adrian Klaver adrian.klaver@aklaver.com
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)
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
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.klaver@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: 3000000 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 <http://north.gov>
> 0: oAuth server name: blackdow.carleon.gov
> <http://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
> <http://127.0.0.1:3478>
> 0: IPv4. TCP listener opened on : 127.0.0.1:3478
> <http://127.0.0.1:3478>
> 0: IPv4. SCTP listener opened on : 127.0.0.1:3479
> <http://127.0.0.1:3479>
> 0: IPv4. TCP listener opened on : 127.0.0.1:3479
> <http://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
> <http://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 table
> allowed_peer_ip
>
> But these are the tables of coturndb :
>
> coturn=# \dt
> List of relations
> Schema | Name | Type | Owner
> --------+----------------------+-------+----------
> public | admin_user | table | postgres
> public | allowed_peer_ip | table | postgres
> public | denied_peer_ip | table | postgres
> public | oauth_key | table | postgres
> public | turn_origin_to_realm | table | postgres
> public | turn_realm_option | table | postgres
> public | turn_secret | table | postgres
> public | turnusers_lt | table | postgres
> (8 rows)
>
> coturn=# \d allowed_peer_ip
> Table "public.allowed_peer_ip"
> Column | Type | Collation | Nullable |
> Default
>
> ----------+------------------------+-----------+----------+-----------------------
> realm | character varying(127) | | not null |
> ''::character varying
> ip_range | character varying(256) | | not null |
> Indexes:
> "allowed_peer_ip_pkey" PRIMARY KEY, btree (realm, ip_range)
>
> coturn=# \d denied_peer_ip
> Table "public.denied_peer_ip"
> Column | Type | Collation | Nullable |
> Default
>
> ----------+------------------------+-----------+----------+-----------------------
> realm | character varying(127) | | not null |
> ''::character varying
> ip_range | character varying(256) | | not null |
> Indexes:
> "denied_peer_ip_pkey" PRIMARY KEY, btree (realm, ip_range)
>
> coturn=# \d turn_secret
> Table "public.turn_secret"
> Column | Type | Collation | Nullable |
> Default
>
> --------+------------------------+-----------+----------+-----------------------
> realm | character varying(127) | | not null |
> ''::character varying
> value | character varying(256) | | not null |
> Indexes:
> "turn_secret_pkey" PRIMARY KEY, btree (realm, value)
>
> Why it says "probably, the tables 'allowed_peer_ip' and/or
> 'denied_peer_ip' have to be upgraded to include the realm column" ?
Have no idea, probably need to see if someone answers that in response
to your issue:
https://github.com/coturn/coturn/issues/484
> Why it says "permission denied" for table turn_secret and for table
> allowed_peer_ip?
0: PostgreSQL DB connection success: host=localhost dbname=coturn
user=turn password=turn
connect_timeout=30
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
...
public | allowed_peer_ip | table | postgres
...
public | turn_secret | table | postgres
You are connecting as user turn and trying to access tables owned by
user postgres.
> How to solve the problem?
>
> Marco
--
Adrian Klaver
adrian.klaver@aklaver.com