Re: 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>
>      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