note 43291 deleted from function.pg-connect by bjori

Note Submitter: u233 at shaw dot ca ---- The suggestion of grheatley is exceedingly dangerous. Because "fred" owns the database and apache is connecting as "fred", if there is an error in your php or it is vulnerable to SQL injection attacks nasty things like "delete from products;" can easily happen. Furthermore, the default to trust all local users has not been overridden, thus anyone who can run arbitrary code (even as "nobody" or "apache") on your server can connect as postmaster and drop the database or insert data. It is vitally important to remember that any user with the createdb or createuser privaleges is considered a "superuser" for ALL databases managed by that postmaster instance. What you should be doing is: fred@localhost ~fred$psql -U postmaster template1 BEGIN WORK; ALTER USER postmaster WITH PASSWORD '(password)'; CREATE USER fred WITH PASSWORD '(password)' CREATEDB CREATEUSER; COMMIT; \q Now, edit your pg_hba.conf to: local all all md5 host all all reject hostssl all all reject Then: fred@localhost ~fred$createdb fred fred@localhost ~fred$psql -U fred fred BEGIN WORK; CREATE USER "webserver" WITH PASSWORD '(password)'; create table public_readable ( rowid serial primary key, mydata varchar(50) ); grant select on public_readable to public; create table webserver_editable ( rowid serial primary key, ourdata varchar(50) ); grant insert on webserver_editable to "webserver"; grant delete on webserver_editable to "webserver"; grant update on webserver_editable to "webserver"; grant select on webserver_editable to "webserver"; create table webserver_write_once( -- this table is for write-only logging rowid serial primary key, logdata varchar(50) ); grant insert on webserver_write_once to "webserver"; COMMIT; Now, your php scripts connect like this: <? $conn = pg_connect("dbname=fred user=webserver password=(password)"); ?> For more flexibility, you can create a postgres user for each user who can log on to your website and use the pg_connect as your website authentication method (cacheing is your friend). By making all the users in the same group, you can grant them all access to the tables with a single command but restrict what rows they may access by using views. Do this like so: fred@localhost ~fred$psql -U fred fred BEGIN WORK; CREATE GROUP "salespeople"; CREATE GROUP "customers"; CREATE TABLE customerAddresses ( customername name references pg_users(usename) primary key; address varchar(50) ) GRANT SELECT ON customerAddresses TO GROUP "salespeople"; GRANT INSERT ON customerAddresses TO GROUP "salespeople"; GRANT UPDATE ON customerAddresses TO GROUP "salespeople"; GRANT DELETE ON customerAddresses TO GROUP "salespeople"; CREATE VIEW myAddress as select address from customerAddresses where customername = current_user; CREATE RULE updateMyAddress as on update to myAddress do instead update customerAddresses set address = NEW.address where customerName=current_user; CREATE RULE insertMyAddress as on insert to myAddress do instead insert into customerAddresses values (current_user, NEW.address); GRANT INSERT ON myAddress to group "customers"; GRANT UPDATE ON myAddress to group "customers"; GRANT SELECT ON myAddress to group "customers"; COMMIT; Now the problem becomes: How do un-privaledged users (ie salespeople or webserver) create all those customer accounts? It's easy to solve with a little PL/pgSQL. BEGIN WORK; CREATE GROUP authorizedtocreatecustomers; create or replace function makeuser (name,text) returns int4 as ' BEGIN execute ''create user '' || quote_ident($1) || '' with password '''''' || quote_literal($2) || '''''' in group customers;''; return 0; END; ' language 'plpgsql' security definer; REVOKE EXECUTE ON function makeuser (name, text) FROM PUBLIC; grant execute on function makeuser (name,text) to group authorizedtocreatecustomers; COMMIT; Now, just make sure webserver and all your salespeople are in the group "authorizedtocreatecustomers"