revoke permissions - not working as expected

From: Susan Cassidy <scassidy(at)edgewave(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: revoke permissions - not working as expected
Date: 2011-03-30 17:31:48
Message-ID: 3A51F387FE0CC74D80FA60C146987F2501C3D03CA843@oc-exchange1.stbernard.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2011-03-30 18:01:59 Re: revoke permissions - not working as expected
Previous Message Guillaume Lelarge 2011-03-30 16:43:25 Re: -s does not work