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
