I want to have an owner of a database that can create roles and do all administrative tasks for that db. All roles created by the db owner must have select on all tables in the public schema as well as having their own schemas in which they have all privileges.
So I’m trying:
\connect postgres postgres;
create role db_owner with createrole password 'passwd' login;
create database db with owner db_owner;
\connect db db_owner;
grant select on all tables in schema public to public;
create table t (i int);
create role s1 with password 's1' login;
grant s1 to db_owner;
create schema authorization s1;
Now when I try to select from public.t as user s1 it is denied:
\connect db s1;
db=> select * from t;
ERROR: permission denied for table t
If the grant select is issued by postgres it works:
db=> \connect db postgres
You are now connected to database "db" as user "postgres".
db=# grant select on all tables in schema public to public;
GRANT
db=# \connect db s1
You are now connected to database "db" as user "s1".
db=> select * from t;
i
---
(0 rows)
Why cant the db owner grant select in the public schema? How to do it?
>Solution :
I think the problem is that granting permissions only applies to existing items (all tables in public that existed when you called grant). You are creating the test table afterwards which inherit the permission from the
DEFAULT PRIVILEGES.
You can edit the DEFAULT PRIVILEGES as documented here: https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html
As a first test just create the table first, grant all permissions, try selecting it with the db_owner account and see if the issue persists.