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@xxxxxxxxxxxxx> wrote: > Greg Fodor <gfodor@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
