GRANT ALL ON ALL TABLES IN SCHEMA does not allow user to see tables

Question

I have a pretty simple script to create a new database and to create a new user to access this database. This is run by the default postgres user. After granting access to the database and schema to the user, the newly created user is still not able to see the tables using \dt in psql. Here are some snippets of the script to show what I've tried so far:

CREATE USER api WITH ENCRYPTED PASSWORD 'password';

ALTER DEFAULT PRIVILEGES 
    FOR USER api
    IN SCHEMA public
    GRANT ALL ON ALL TABLES TO api;


DROP DATABASE IF EXISTS new_db;
CREATE DATABASE new_db;


CREATE TABLE addresses (
    address_id INTEGER,
    address_line_1 VARCHAR(50) NOT NULL,
    address_line_2 VARCHAR(50),
    city VARCHAR(50) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zipcode VARCHAR(12) NOT NULL,
    PRIMARY KEY (address_id)
);

-- Create more tables....

-- Added these in for good measure at the end:
GRANT all PRIVILEGES on DATABASE new_db to api;
GRANT ALL ON DATABASE new_db TO api;
GRANT ALL ON SCHEMA public to api;
GRANT ALL ON ALL TABLES IN SCHEMA public TO api;

After running this, \dt shows all the tables in psql when logged in as the postgres user. \dt shows Did not find any relations. when logged in as the api user.

Running \dn+ with the postgres user gives me this information:

  Name  |  Owner   |   Access privileges   |      Description       
--------+----------+-----------------------+------------------------
 public | postgres | postgres=UC/postgres +| standard public schema
        |          | =UC/postgres         +| 
        |          | api=UC/postgres       | 

Version information: PostgreSQL 10.5 (Ubuntu 10.5-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

Other SO questions I've encountered so far:

• Granting access to all tables for a user
• https://stackoverflow.com/questions/7866572/why-can-i-only-see-postgresql-relations-or-tables-when-im-logged-in-as-the-post
• https://stackoverflow.com/questions/7758533/postgresql-database-owner-cant-access-database-no-relations-found
• https://stackoverflow.com/questions/37543078/cant-see-tables-in-postgresql

I've looked at countless SO questions and tried everything suggested, yet still no luck. Is there anything else I can check or try? My hunch is that I'm missing something obvious.

Answer 1

Create a group, which means a ROLE with NOLOGIN.

CREATE ROLE api_group NOLOGIN;

Create (or drop first if you want) your database. And then connect to that database.

CREATE DATABASE new_db;
\c new_db

Add default privileges for your group. The code below says if postgres role creates an object grant ALL to the role api_group. Instead of ALL you can be more specific (e.g. SELECT, INSERT, USAGE etc).

ALTER DEFAULT PRIVILEGES FOR ROLE postgres ALL ON TABLES TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres ALL  ON SEQUENCES TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL  ON FUNCTIONS TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL  ON TYPES TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL  ON SCHEMAS TO api_group ;

Now you can use postgres user to create new objects.

CREATE TABLE addresses (
    address_id INTEGER,
    address_line_1 VARCHAR(50) NOT NULL,
    address_line_2 VARCHAR(50),
    city VARCHAR(50) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zipcode VARCHAR(12) NOT NULL,
    PRIMARY KEY (address_id)
);

-- Create more objects....

The setup is ready. If you add new user (lets say) api even after you create all the objects the user will have all the privileges. The INHERIT keyword there is for inheriting the privileges of roles it is a member of. The codes below create 2 users as a member of api_group.

CREATE ROLE api INHERIT WITH ENCRYPTED PASSWORD 'pwd' IN ROLE api_group;
CREATE ROLE another_api INHERIT WITH ENCRYPTED PASSWORD 'pwd' IN ROLE api_group;

Suggested sections of the documentation;

• ALTER DEFAULT PRIVILEGES
• CREATE ROLE

Answer 2

When I follow the steps you give, the table created is visible to the role "api". But, the table is not in the database "new_db", because you did not change into that database after creating the database but before creating the table. Also, the table is not owned by "api", because you did not change into that role after creating it but before creating the table. The table is located in, and so visible in, the same database you originally connected to (presumably "postgres"), and is owned by the "postgres" role.

The alter default privileges does not accomplish anything because it applies to tables created by "api", but you did not create the table as "api". Also, it wouldn't do anything anyway because a table's owner already has privileges on their own table by default.

If you can see the table when you log in as "postgres" but not when you log in as "api", it must be because you are not logging into the same database as each user. If you log into database "new_db" as role "postgres" you will not see the table as it is not there. If you log into database "postgres" as role "api", you will see it.