Thread: About revoking large number of privileges; And the PUBLIC role.
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
Dominique Devienne <ddevienne@gmail.com> writes: > 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. You should not really have to revoke those manually. The normal process for that is to use DROP OWNED BY. https://www.postgresql.org/docs/current/role-removal.html regards, tom lane
On Thu, Jul 7, 2022 at 3:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Dominique Devienne <ddevienne@gmail.com> writes: > > 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. > > You should not really have to revoke those manually. > The normal process for that is to use DROP OWNED BY. Except we already went through that, that DROP OWNED BY acquires too many locks. Increasing max_locks_per_transaction when it fails is just not an option IMHO. One user had to raise it to 32K for his particular DB, which is not even that large. Or are you saying setting it to 1M or 1B is "safe", and should be required setup for users? Is revoking privileges taking locks? Is dropping a DB taking locks? If neither are, then I can work around the limitations of DROP OWNED BY. So will the community help me figure this out? BTW, I'm also hoping revoking privs, and dropping roles and dbs will be faster than DROP OWNED BY. That DB took 30min to 1h to get rid of, via DROP OWNED BY, which seems to long to delete a bunch of files, no?
On Thu, 2022-07-07 at 16:36 +0200, Dominique Devienne wrote: > > > 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. > > > > You should not really have to revoke those manually. > > The normal process for that is to use DROP OWNED BY. > > Except we already went through that, that DROP OWNED BY acquires too many locks. > Increasing max_locks_per_transaction when it fails is just not an option IMHO. > One user had to raise it to 32K for his particular DB, which is not > even that large. > > Or are you saying setting it to 1M or 1B is "safe", and should be > required setup for users? If you want to do this on a routine basis, you are doing something wrong. Never grant a user privileges if the user could be removed. Use groups in that case. For a one-time cleanup operation, increasing "max_locks_per_transaction" and restarting is painful, but not impossible. See it as down time. > Is revoking privileges taking locks? Yes. > Is dropping a DB taking locks? Not a lot. That should never be a problem. > If neither are, then I can work around the limitations of DROP OWNED BY. > > So will the community help me figure this out? > > BTW, I'm also hoping revoking privs, and dropping roles and dbs will > be faster than DROP OWNED BY. > That DB took 30min to 1h to get rid of, via DROP OWNED BY, which seems > to long to delete a bunch of files, no? As I wrote, avoid getting there in the first place. Yours, Laurenz Albe
On 2022-07-07 09:47:38 +0200, Dominique Devienne wrote:
> Hi. I'm struggling to delete databases because of grants to roles on
> objects of those DBs.
I don't understand this. You can drop objects (and databases containing
objects) with grants to existing roles. It would be very inconvenient if
that wasn't possible.
Do you have an example on where a grant prevents dropping an object?
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment
Greetings, * Dominique Devienne (ddevienne@gmail.com) wrote: > So my goal is to delete all those "db specific" ROLEs, then the DB > with all its schemas. If you want to drop the database anyway.. then why not simply do that first? Nothing can be connected to a DB that's being dropped and we don't actually try to lock all the objects in a to-be-dropped DB. Thanks, Stephen