Thread: Minimal rights

Minimal rights

From
"Pavel PaJaSoft Janousek"
Date:
Hi,

    I'm begin use the PostgreSQL with aplications, which are connecting to databases from Web. I know many issues about
security,so I want grant only the minimal rights that is needed for operations. I use ECPG for writing my C++ program
togetherwith ESQL. So... 

In C++ program - in binary format - is many good informations - for example DatabaseName, User account and his password
etc...- yes, binary format isn't more good, but this information are there... 

    I know this, that is the way I create 2 PostgreSQL users. One - which create database, tables, sequences etc.. -
thishas full privileges and second - this user can have only Insert privilege.  
    From the Web I need only write some information about accessing the HTML pages to database.
    So, I've one table and 2 sequences... I see rights by '\z' command from psql and see this:
log    - {"-", "pajasoft=arwR", "webwriter=a"}
log_pkey   -
log_seq    -

'log' is the table, 'log_pkey' is index, because table 'log' contains primary key and 'log_seq' is my own sequence...
'pajasoft'is user with full rights and 'webwriter' is user which has only Insert right. 

    And now I have one question - I think, that both users (pajasoft, webwriter) have the same privileges to both
sequences- both have full rights to this... This is bad, because, somebody, who hacked the machine, where the SQL
serverrun can connect as 'webwriter' (information about password read from binary file - CGI script) and now he can
modifysequences - table 'log' don't because users 'webwriter' has only Insert privilege. 

    So I can give minimal privileges to user 'webwriter', but script must run next too.... My question is:

Which privileges must have any user which can only Insert to table, but the primary index in this table is filled as
defaultvalue nextval ('log_seq')? 

    Thanx for your answer....

Pavel PaJaSoft Janousek
FoNet, spol. s r.o.