Error retrieving PostgreSQL DB information with Coturn

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&gt;
    0: oAuth server name: blackdow.carleon.gov
<http://blackdow.carleon.gov&gt;
    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&gt;
    0: IPv4. TCP listener opened on : 127.0.0.1:3478
<http://127.0.0.1:3478&gt;
    0: IPv4. SCTP listener opened on : 127.0.0.1:3479
<http://127.0.0.1:3479&gt;
    0: IPv4. TCP listener opened on : 127.0.0.1:3479
<http://127.0.0.1:3479&gt;
    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&gt;
    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)

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.klaver@aklaver.com> ha scritto: