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.
Did not find any relations. when logged in as the api user.
\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 |
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
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.