Thread: Privileges on public schema can't be revoked?
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. Revokes on other schemas work as expected, it seems the public schema is treated specially. https://gist.github.com/gfodor/c360683f25f55497c8c657255fd0e0f8 Any help appreciated! -Greg
Greg, * Greg Fodor (gfodor@gmail.com) wrote: > 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. Revokes > on other schemas work as expected, it seems the public schema is > treated specially. > > https://gist.github.com/gfodor/c360683f25f55497c8c657255fd0e0f8 > > Any help appreciated! The privilege on the public schema was granted to "public" and therefore must be revoked from "public" to remove that privilege. Revoking from "guy" doesn't have any effect. Note that if you revoke all privielges from 'public' then only users who have been explicitly granted access will be able to create or *use* any objects in the public schema. Generally, I revoke CREATE rights from the public schema, but leave USAGE rights, as I then put trusted extensions and other tools into the public schema. Thanks! Stephen
Attachment
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
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
Gregm * Greg Fodor (gfodor@gmail.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
Attachment
Stephen Frost <sfrost@snowman.net> writes:
> \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.
Possibly Greg was thinking of the information_schema views. Grants to
PUBLIC do show up in those. The problem is that there's no view there
that covers schema privileges, probably because the SQL standard doesn't
really have any such thing as grantable schema privileges. (Or at least
it didn't last I heard, I might be out of date.)
regards, tom lane
On 9/6/16 3:16 PM, Greg Fodor wrote: > 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. That's actually relatively easy to do today; see the has_*_privilege() functions. You might also find http://pgxn.org/dist/pg_acl useful. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461