Re: revoke permissions - not working as expected

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.

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Susan Cassidy
Sent: Wednesday, March 30, 2011 1:32 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] revoke permissions - not working as expected

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/p
ostgres}

(1 row)

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

Advice?

Thanks,

Susan