Re: revoke permissions - not working as expected - Mailing list pgsql-general

The most important thing to remember about REVOKE is that it can only revoke a permission that was explicitly granted.  Every database has GLOBAL permissions not tied to any specific schema and granted to PUBLIC.  These permissions are inherited by all ROLES as long as they (the permissions) are in effect.

I’ll it to others or the documentation to provide further examples or syntax/commands but what the above means is that unless you had previously issued a “GRANT ALL PRIVILEGES ON SCHEMA public TO usera” the attempt to “REVOKE ALL PRIVILEGES ON SCHEMA public TO usera” will not work since the privileges usera has on public come from the GLOBAL/PUBLIC ROLE and not usera itself.

I can see where a “DENY ALL PRIVILEGES ON SCHEMA public TO usera” would come in handy in these situations – though you’d really just be masking the problem of not properly understanding how permissions work – but alas there is no such beast.

Given the complexity I’d suggest learning about some the meta-data/information schema tables and functions related to permissions that you can query the permissions of all relevant database objects and check for permission exceptions quickly.  There is a “does role has permission on object” set of functions where you can establish an expected true/false response for each object and then compare those expectations against reality and show the ones that are different.

I’ve just started looking into this myself, using 9.0, so please forgive if some of the above thoughts are not applicable for 8.3

David J.

OK, I must be doing something wrong.  I’m trying to create a user with only limited access to certain tables.  The db is large, complicated, and has tons of users with some complex interactions of permission using groups, etc.  I don’t dare revoke any exist permissions, for fear of messing up a production db.

Version is 8.3.9.

I’m currently testing this on the test version of the db, though, which is a clone of the production db.  I’ve edited the actual usernames, table names, db names, etc.

I created a role usera.

I revoked everything I could think of (e.g. REVOKE ALL PRIVILEGES ON SCHEMA public FROM usera cascade;, revoke all privileges on database maindb from usera cascade;  I also did individual tables:

urldb=# revoke select, update, delete on table1 from usera;             ( I tried this with and without CASCADE – no difference)

REVOKE

urldb=# \q

sb-dev-testdb:~# psql -U usera maindb

Welcome to psql 8.3.9, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help with psql commands

       \g or terminate with semicolon to execute query

       \q to quit

urldb=> select * from table1;

All rows display anyway.

\dp table1 shows:

                                           Access privileges for database "maindb"

 Schema |   Name    | Type  |                                       Access privileges

--------+-----------+-------+-----------------------------------------------------------------------------------------------

 public | languages | table | {postgres=arwdxt/postgres,=arwdxt/postgres,group1=r/postgres,group2=arwdxt/postgres}

(1 row)

I’m sure it’s something simple that I’m doing wrong.

Advice?

Thanks,

Susan