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.
It seems that functionality that lets a superuser quickly audit the
privileges for a user (including those granted via PUBLIC) would be
really helpful for diagnosing cases where that user can do something
they shouldn't be allowed to. The converse, where they cannot do
something they should, is quickly remedied by granting the privilege.
I could imagine two possible interfaces, one where you can get a list
of all privileges granted to user on an object and why, or another
where you simply submit a statement to the DB and it gives you an
audit trail of why that statement is permitted (EXPLAIN PRIVILEGES?
:))
Thanks for the info!
On Tue, Sep 6, 2016 at 11:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Greg Fodor <gfodor@gmail.com> writes:
>> Apologies in advance about this since it is likely something obvious,
>> but I am seeing some very basic behavior that does not make sense.
>> I've tested this on a fresh build of 9.6rc1 and also 9.1.24 (just to
>> see if it was a regression.) After creating a test database, and a
>> test user that I revoke all privileges on the public schema to, yet
>> that user is still able to create tables in the public schema.
>
> You would need to revoke the default grant of privileges to PUBLIC;
> revokes against any particular user have no effect on his being
> a member of PUBLIC.
>
> IOW, revoke only revokes a previous matching grant, and there was
> no such grant in this case. What there was was a grant to PUBLIC;
> see the relevant bit in initdb.c:
>
> "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
>
> regards, tom lane