From: robert_hiltibidal_at_cms08405@ccmailgw.state.il.us
Message-Id: <9906259303.AA930325869@ccmailgw.state.il.us>
Date: Fri, 25 Jun 99 10:50:40 -0600
To: <pgsql-interfaces@postgresql.org>
Subject: Permissions problem
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit


     Morning,
     
     I have an interesting problem... I'm running postgres 6.4.2 on a linux 
     2.2.6 system. 64mg ram, 130mg swap.
     
     I have a distributed network system here. I have some people who are 
     not local that need the root password to the system. Access97 is 
     favored here so I thought I'd create a table with the password and 
     limit the access to only to those people. The idea is these and only 
     these people can see what the root password is without having to page 
     me.
     
     I installed the postgres odbc driver on 1 nt machine and the rest are 
     win95 machines. The driver was installed to prompt for the username 
     and password. I did take out the read only flags. There are other 
     tables these people have access to and need to be able to edit.
     
     The main Access97 database is stored on a novell system a couple of 
     floors away. That database is called WebAdmin. I linked the root table 
     between the linux server and the WebAmin.mdb file stored on the Novell 
     drive. I did not link the tables as root. I linked them as user1. Each 
     of the people involved can see the tables. So far so good.
     
     The problem is I created the table as root on the linux server. I 
     limited the perms to SELECT for the people involved. Yet, we found out 
     the people can do INSERT,UPDATE, and DELETE queries. 
     
     I had thought since I was logged on as user1 and then su'd to root 
     that the server attached my real user id and the root uid, to the 
     table. This does not seem to be the case. My www user cannot access 
     tables not assigned to it.
     
     I created the WebAdmin.mdb file from my NT box using my user1 name and 
     password. Access97 tends to keep things stored in the system tables 
     that allows a networked office to share the same information. As long 
     as the DSN is the same for all the computers Access97 will let all the 
     users see the same stuff. I thought perhaps Access97 was trapping my 
     user1 name and password in the system tables but I did not find them. 
     The DSN connection does not show my username and password.
     
     Another factor is I can log on to the linux system as anyone of the 
     users and *still* change the data in the user table. This argues for 
     the hba not being set up right. But the server will not accept IDENT 
     in the auth portion. When they are on the linux server I want the 
     postgres server to use the users actual uid assigned in the passwd 
     file.
     
     If these people can alter the root table unimpeded then there's a good 
     possibility some novell admin can do the same. In short, I'm being 
     justifiably paranoid.
     
     Attached is the root.sql file used to create the table and the 
     restrictions  I set on it. When I created this table I was su'd to 
     root. I was not the postgres user. Is it possible I didn't set up the 
     perms right? I've also included a portion of the hba table. Any ideas? 
     
     -Rob 
     
     -- Y2K Project Postgres SQL Server Data Dictionary
     -- Created by Robert Hiltibidal
     -- 25-JUN-99
     
     
     DROP TABLE root;
     
     
     CREATE TABLE root (
        password varchar (10),
             recnum varchar (5)
     );
     
     GRANT SELECT on root to user1;
     GRANT SELECT on root to user2;
     GRANT SELECT on root to user3;
     GRANT SELECT on root to user4;
     GRANT SELECT on root to user5;
     GRANT SELECT on root to user6;
     REVOKE INSERT,UPDATE,DELETE on root from user2;
     REVOKE INSERT,UPDATE,DELETE on root from user3;
     REVOKE INSERT,UPDATE,DELETE on root from user4;
     REVOKE INSERT,UPDATE,DELETE on root from user5;
     REVOKE INSERT,UPDATE,DELETE on root from user6;
     REVOKE INSERT,UPDATE,DELETE on root from user1;
     
     ##############HBA Table
     
     local        all                                          trust
     host         all          0.0.0.0       0.0.0.0           password   
     y2k