Permission denied for relation on PostgreSQL 9.6 database

Question

I am having problems creating new objects (specifically, views) in a database that I have created. Although there are many similar issues reported here on SO, see permission denied for relation and permission denied for sequence, but despite studying the accepted answers (and in some cases, following up to the PostgreSQL documentation), I have not been able to solve this problem.

Here is a snippet of the script I'm running to create the database, new role and then changing the default permissions of the database:

-- # Uncomment line below for debugging purposes
SET client_min_messages TO debug1;

CREATE DATABASE mydatabase WITH ENCODING 'UTF8' TEMPLATE template1;

-- Create user foo
CREATE ROLE foo LOGIN ENCRYPTED PASSWORD '29829932499gd' NOINHERIT;


\c mydatabase


--- this grants privileges on new objects generated in new database "mydatabase"
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO foo; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO foo;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO foo;


--- import DDL from SQL files (Ommitted for brevity sake)

When I try to create a materialized view, I realised that there was a permission error on one of the tables (table_1) I was attempting to SELECT from.

I tried a simple query directly at the CLI:

mydatabase=> select * from table_1;
ERROR:  permission denied for relation table_1

I then manually typed this at the psql CLI (connected to the mydatabase database):

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO foo;

I then get the response:

ERROR: permission denied for relation table_2

Here is the schema for table_1 and table_2:

CREATE TABLE IF NOT EXISTS table_1 (
                        td                  DATE NOT NULL,

                        f1                  REAL CHECK (f1 > 0) NOT NULL ,
                        f2                  REAL CHECK (f2 > 0 and f2 >= f1 and f2 >= f3 and f2 >= f4) NOT NULL ,
                        f3                  REAL CHECK (f3 > 0 and f3 <= f1 and f3 <= f2 and f3 <= f4) NOT NULL ,
                        f4                  REAL CHECK (f4 > 0) NOT NULL,
                        f5                  BIGINT CHECK (f5 > -1) DEFAULT 0 NOT NULL,
                        f6                  BIGINT CHECK (f6 > -1) DEFAULT 0 NOT NULL,

                        moff                SMALLINT DEFAULT 0 CHECK (moff > -1) NOT NULL,

                        flg_03_h              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_03_l              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_05_h              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_05_l              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_10_h              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_10_l              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_20_h              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_20_l              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_60_h              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_60_l              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_52w_h             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_52w_l             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_at_h              BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_at_l              BOOLEAN DEFAULT FALSE NOT NULL,

                        flg_03v_h             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_03v_l             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_05v_h             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_05v_l             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_10v_h             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_10v_l             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_20v_h             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_20v_l             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_60v_h             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_60v_l             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_52wv_h            BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_52wv_l            BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_atv_h             BOOLEAN DEFAULT FALSE NOT NULL,
                        flg_atv_l             BOOLEAN DEFAULT FALSE NOT NULL,

                        ar_1w                 REAL DEFAULT -99999 NOT NULL,
                        ar_2w                 REAL DEFAULT -99999 NOT NULL,
                        ar_1m                 REAL DEFAULT -99999 NOT NULL,
                        ar_3m                 REAL DEFAULT -99999 NOT NULL,


                        ar_1w_mzs             REAL DEFAULT -99999 NOT NULL,
                        ar_2w_mzs             REAL DEFAULT -99999 NOT NULL,
                        ar_1m_mzs             REAL DEFAULT -99999 NOT NULL,
                        ar_3m_mzs             REAL DEFAULT -99999 NOT NULL,

                        PRIMARY KEY           (td, moff)
                        );

CREATE INDEX idx_tb1_flg03f2  ON table_1 (td, flg_03_h);
CREATE INDEX idx_tb1_flg03f3  ON table_1 (td, flg_03_l);
CREATE INDEX idx_tb1_flg05f2  ON table_1 (td, flg_05_h);
CREATE INDEX idx_tb1_flg05f3  ON table_1 (td, flg_05_l);
CREATE INDEX idx_tb1_flg10f2  ON table_1 (td, flg_10_h);
CREATE INDEX idx_tb1_flg10f3  ON table_1 (td, flg_10_l);
CREATE INDEX idx_tb1_flg20f2  ON table_1 (td, flg_20_h);
CREATE INDEX idx_tb1_flg20f3  ON table_1 (td, flg_20_l);
CREATE INDEX idx_tb1_flg60f2  ON table_1 (td, flg_60_h);
CREATE INDEX idx_tb1_flg60f3  ON table_1 (td, flg_60_l);
CREATE INDEX idx_tb1_flg52wf2 ON table_1 (td, flg_52w_h);
CREATE INDEX idx_tb1_flg52wf3 ON table_1 (td, flg_52w_l);
CREATE INDEX idx_tb1_flgatf2  ON table_1 (td, flg_at_h);
CREATE INDEX idx_tb1_flgatf3  ON table_1 (td, flg_at_l);

CREATE INDEX idx_tb1_flg03f5f2  ON table_1 (td, flg_03v_h);
CREATE INDEX idx_tb1_flg03f5f3  ON table_1 (td, flg_03v_l);
CREATE INDEX idx_tb1_flg05f5f2  ON table_1 (td, flg_05v_h);
CREATE INDEX idx_tb1_flg05f5f3  ON table_1 (td, flg_05v_l);
CREATE INDEX idx_tb1_flg10f5f2  ON table_1 (td, flg_10v_h);
CREATE INDEX idx_tb1_flg10f5f3  ON table_1 (td, flg_10v_l);
CREATE INDEX idx_tb1_flg20f5f2  ON table_1 (td, flg_20v_h);
CREATE INDEX idx_tb1_flg20f5f3  ON table_1 (td, flg_20v_l);
CREATE INDEX idx_tb1_flg60f5f2  ON table_1 (td, flg_60v_h);
CREATE INDEX idx_tb1_flg60f5f3  ON table_1 (td, flg_60v_l);
CREATE INDEX idx_tb1_flg52f5wf2 ON table_1 (td, flg_52wv_h);
CREATE INDEX idx_tb1_flg52f5wf3 ON table_1 (td, flg_52wv_l);
CREATE INDEX idx_tb1_flgatf5f2  ON table_1 (td, flg_atv_h);
CREATE INDEX idx_tb1_flgatf5f3  ON table_1 (td, flg_atv_l);

CREATE INDEX idx_tb1_ar_1w  ON table_1 (td, ar_1w);
CREATE INDEX idx_tb1_ar_2w  ON table_1 (td, ar_2w);
CREATE INDEX idx_tb1_ar_1m  ON table_1 (td, ar_1m);
CREATE INDEX idx_tb1_ar_3m  ON table_1 (td, ar_3m);

CREATE INDEX idx_tb1_ar_1w_mz  ON table_1 (td, ar_1w_mzs);
CREATE INDEX idx_tb1_ar_2w_mz  ON table_1 (td, ar_2w_mzs);
CREATE INDEX idx_tb1_ar_1m_mz  ON table_1 (td, ar_1m_mzs);
CREATE INDEX idx_tb1_ar_3m_mz  ON table_1 (td, ar_3m_mzs);


CREATE TABLE IF NOT EXISTS table_2  (
                                    id                  SERIAL PRIMARY KEY NOT NULL,
                                    name                TEXT NOT NULL
                            );
CREATE UNIQUE INDEX idxu_table2_nm ON table_2 (name);

As can be seen, there is NO relationship between table_1 and table_2, so I suspect that the error messages given by postgreSQL are actually red herrings (i.e. misleading).

One last thing. I am using Docker* to deploy my postgreSQL service - so the above scripts are being run by docker. The database is created when I run docker run - however, when I log in as user foo, and I attempt to create new views (or even run a simple SELECT on any of the tables), I get the error:

ERROR: permission denied for relation $table_name

Another strange thing I noticed (when I run \z at the psql command) is that it appears none of the tables in the database have any permissions:

                                                        Access privileges
 Schema |                           Name                            |   Type   | Access privileges | Column privileges | Policies 
--------+-----------------------------------------------------------+----------+-------------------+-------------------+----------
 public | mdb_company_financials_balsheet                           | table    |                   |                   | 
 public | mdb_company_financials_cashflow                           | table    |                   |                   | 
 public | mdb_company_financials_income_stmt                        | table    |                   |                   | 
 public | mdb_ccy_group_member                                      | table    |                   |                   | 

So, what is causing this permissioning problem, and how do I resolve it?

*(not sure if it's relevant information).

[[Addendum]]

                                         version                                          
------------------------------------------------------------------------------------------
 PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Answer 1

This is working for me after some research. psql (9.6.12)

-- login to postgres database server as postgres user.
ssh <user>>@hostname.<domain>>.com
sudo su postgres
id
psql
postgres=# create user sentry_read with login password 'sentry_read';
postgres=# du+
postgres=# \l+
-- **very important: make sure we should connect required database before we grant to user.**
postgres=# \c sentry
postgres=# \dt
sentry=# \dp SENTRY_VERSION
sentry=# select * from "SENTRY_VERSION";
sentry=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO sentry_read;
postgres=# \q

[user@hostname ~]$ psql -U sentry_read -d sentry -h hostname.<domain>>.com -W
sentry=> select * from "SENTRY_VERSION";