Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    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......
    or
    SELECT * FROM pg_tables;
    etc

    How can I forbid it to him?

  2. #2
    Join Date
    May 2009
    Posts
    6
    Try a "REVOKE CREATE".

    Also, I'm not sure if it will work, but I would try revoking everything except execute to a procedure which will pull the data you want (that you will have to, ... say it isn't so, ... write).

    It wouldn't surprise me if you can't block access to selects on the system tables, though I find it diffucult to believe someone is finding that kind of information senistive.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •