About revoking large number of privileges; And the PUBLIC role. - Mailing list pgsql-general

Hi. I'm struggling to delete databases because of grants to roles on
objects of those DBs.

These DBs can have a large'ish number of schemas, 100-300 is typical.
and define a bunch of ROLEs "specific" to those schemas. Normally "login user"
ROLEs are never granted explicit access to objects, instead only the
"db specific" ROLEs
get those grants, and regular users are granted some of those ROLEs.

So my goal is to delete all those "db specific" ROLEs, then the DB
with all its schemas.
Which implies REVOKE'ing grants on all those "db specific" ROLEs first.
(and "login users" just implicitly lose membership in "db specific"
roles when the latter are dropped)

OK, so to help me achieve that "mass revoking", I thought I'd use pg_shdepend,
but turns out to not be that easy to figure this shared catalog out...
I'm made some
progress on that, but then trying various manual REVOKEs, I often don't see any
changes in pg_shdepend, and I'm not sure whether it's because of PUBLIC, or
because of DEFAULT PRIVILEGES, or because I'm not always revoking using the
same ROLE as the one that did the grants, or something else I don't
yet know about...

So I'd thought I'd seek clarifications here, and go back to asking a
few basic basic-principle questions.

The way the GRANTs are made right now is via:
`ALTER DEFAULT PRIVILEGES IN SCHEMA $schema GRANT $priv TO $role`

With (so far) $priv taking all these values:
- "SELECT ON TABLES",
- "USAGE, SELECT ON SEQUENCES",
- "EXECUTE ON ROUTINES",
- "USAGE ON TYPES".

the default privileges are updated *before* schema objects are created.
those all "db specific" (and "schema specific" too) roles are getting
their object grants via DEFAULT PRIVILEGES.

So my first question is whether revoking from the DEFAULT PRIVILEGES
is enough to "ungrant" all those object privileges?

My reading of the doc seem to imply it does, but after for example

ALTER DEFAULT PRIVILEGES IN SCHEMA $schema REVOKE SELECT ON TABLES FROM $role

nothing in pg_shdepend changed.  Could it be related to the PUBLIC role?

Which bring me to questions on PUBLIC. I suspect there's something I
don't understand here.
From my reading, all ROLEs are implicitly members of PUBLIC, and you
cannot avoid that?
And it seems many GRANTs are implicitly made to public, w/o my realizing it?

Just recently, I discovered any user could connect to new databases I
created, when I didn't want that.
I've started to explicitly `revoke all on database {} from public`
everytime I create a new DB. But I just
don't quite understand why I can't seem to avoid PUBLIC having
implicit access. Am I missing something?

So similarly, is PUBLIC getting implicit access to my DEFAULT PRIVILEGES too?
My pg_shdepend results seem to indicate so, although I'm not 100% sure
(and why I'm here now).

What steps do I need to take to ensure PUBLIC gets "nothing" on the
DBs and SCHEMAs I create?
I always want grants to be made explicitly, on specific ROLEs I
control, never implicitly.

Right now, I'm doing most of my experimentation as a SUPERUSER, which
could be skewing my (non-conclusive) results.
That's unlikely to be the case in the "real-world". I suppose it
matters who revokes privileges?

How can I translate rows in pg_shdepend into the proper REVOKE call?
```
select c.relnamespace::regnamespace::text,
   r.rolname,
   count(*)
  from pg_database db
  join pg_shdepend dep on db.oid = dep.dbid
  join pg_roles r on dep.refobjid = r.oid
  join pg_class c on dep.objid = c.oid
 where datname = current_database()
   and refclassid::regclass::text = 'pg_authid'
   and classid::regclass::text = 'pg_class'
 group by 1, 2
```
So far, my (naive?) attempt above tells me how my dependencies I have
between ROLEs and SCHEMAs.
That tells me how my dependencies I have between them (I think at
least!), but I don't know how to turn
what the aggregation is hiding into the proper REVOKE calls, from
what's inside pg_shdepend.
Can somehow help with that? Should it be a separate thread, just on that?

Finally, a tangentially related question, to end this first email,
related to what is transactional and what isn't.
The doc doesn't seem to say much on this subject.
- And all GRANT/REVOKE transactional?
- Is DB create/drop transactional?

I'm trying to get to grasp with dropping DBs and ROLEs, and would
appreciate this list's expert opinions.
I suspect this is only the first message of a thread, I tried to put
just enough info I thought relevant to my first questions.

Thanks, --DD