Re: Privileges on public schema can't be revoked?

Gregm

* Greg Fodor (gfodor(at)gmail(dot)com) wrote:
> Ahhhh, I wasn't aware of the PUBLIC meta-role. Not sure if it's useful
> feedback, I spent a lot of time digging around the web for solutions
> that would basically let me query the database to see all of the
> effective privileges for a user, and none of the solutions I found
> were able to get me to a point where this was clear, since they all
> queried against the various information schema tables that I think
> neglect to take into account the PUBLIC meta-role.

\dn+ in psql will give you the access privileges for all schemas.

I'd have to look at the "other solutions" you're referring to, but, in
general, we do not exclude the public role in any way from the access
privilege system. The \dn+ above should return something like:

postgres=UC/postgres+
=UC/postgres

for the Access Privileges column for the public schema, which shows that
the 'postgres' role and the '' role (aka, 'public') have been granted
both USAGE and CREATE on that schema.

Thanks!

Stephen