Unanswered: How to create guest user which can SELECT only from one table?
I'm new to PostgreSQL and I think that I miss some very important features.
I have a database with several tables e. g data, data1, data2 which are filled in by one user. I need to create a guest user who can only SELECT from one table e. g. "data". He must absolutely have no other rights except this SELECT.
What I'm doing:
-- guest creation
CREATE USER guest WITH PASSWORD '1234' NOCREATEDB NOCREATEUSER;
-- destroy all rights of the "guest" user
REVOKE ALL PRIVILEGES ON DATABASE database FROM guest;
-- add rights for "guest" to select from data table
GRANT SELECT ON data TO guest;
-- add connect
GRANT CONNECT ON DATABASE database TO guest;
After that I'm logging in as guest
SELECT * FROM data; --works perfectly
SELECT * FROM data1; --doesn't work, and that's good
But I've found that this guest can do queries like:
CREATE TABLE xyz......
SELECT * FROM pg_tables;