Re: What is wrong here?

I don't think there's a difference. In an earlier mail of this thread Tom Lane wrote the following:
/*qoute*/
By default functions execute with the permissions of the calling user.
If that's not what you want, see the SECURITY DEFINER option of CREATE
FUNCTION.
/*end quote*/
I derive from this that any insert (or other) query is run with the permissions of the user calling the function. So in that perspective it's no different from the user calling the insert directly. Except that you do some extra checks in your functions.

About the triggers: I don't have much experience with triggers myself, but from what I know you can achieve these things using triggers. Escpecialy check/modify values. If I'm not mistaken there has been a thread earlier in one of the mailing lists about column permissions and triggers. Maybe check the logs?

You could consider doing these things in your application code if that poses no "moral" objections.

Regards,

Stijn Vanroye

Once upon a time Tumurbaatar S. [mailto:tumurbaatar(at)datacom(dot)mn] wrote:
> I think there's a bit difference in direct and indirect
> inserting/updating. For example, on inserting I want
> to check/modify some values (e.g. lower(aEmail)). Or,
> because Postgre does not offer a column permission
> feature, I want to deny some column updates thru
> my functions.
> But all above things can (and should) be achieved thru
> triggers. So I have to use some triggers. Yes?
>
>
> ----- Original Message -----
> From: "Stijn Vanroye" <s(dot)vanroye(at)farcourier(dot)com>
> To: "Tumurbaatar S." <tumurbaatar(at)datacom(dot)mn>
> Cc: <pgsql-general(at)postgresql(dot)org>
> Sent: Thursday, April 22, 2004 20:01
> Subject: RE: [GENERAL] What is wrong here?
>
>
> I don't think so. I don't see why there should be a
> difference in executing
> an insert statement direct, or trought a function. You would
> still be simply
> executing an insert on a table, wich implies that the user has to have
> sufficient rights on that table.
> Should anyone think I'm wrong (I'm still more or less a newbie with
> PostGres), please speak up.
>
> Regards,
>
> Stijn Vanroye
>
> > -----Original Message-----
> > From: Tumurbaatar S. [mailto:tumurbaatar(at)datacom(dot)mn]
> > Sent: donderdag 22 april 2004 12:32
> > To: Stijn Vanroye
> > Subject: Re: [GENERAL] What is wrong here?
> >
> >
> > Yes, the user doesn't have INSERT right on this table.
> > Because I planned to force users to use the function
> > instead of direct INSERT. So it is not possible?
> >
> >
> > ----- Original Message -----
> > From: "Stijn Vanroye" <s(dot)vanroye(at)farcourier(dot)com>
> > To: "Tumurbaatar S." <tumurbaatar(at)datacom(dot)mn>;
> > <pgsql-general(at)postgresql(dot)org>
> > Sent: Thursday, April 22, 2004 19:08
> > Subject: RE: [GENERAL] What is wrong here?
> >
> >
> > My guess is that the user has no (insert) rights on the table
> > Customers.
> > Try something like this for your table:
> > GRANT [your options] ON TABLE Customers TO SomeCustomer; (or
> > to everyone if
> > that's easyer)
> > where your options best includes SELECT and INSERT
> >
> > Regards,
> >
> > Stijn Vanroye
> >
> >
> > > The following function returns this error:
> > >
> > > pg_query(): Query failed: ERROR: permission denied for
> > > relation customers
> > > CONTEXT: PL/pgSQL function "newprofile" line 8 at SQL statement
> > >
> > > What is wrong here?
> > >
> > >
> > > CREATE SEQUENCE CustomerID;
> > > CREATE TABLE Customers
> > > (
> > > CustomerID INTEGER NOT NULL DEFAULT nextval('CustomerID'),
> > > IsActive BOOLEAN NOT NULL DEFAULT TRUE,
> > > Email VARCHAR(64) NOT NULL CHECK (Email = substring(Email from
> > > '^(dot)+(at)(dot)+\(dot)(dot)+$')),
> > > Password VARCHAR(15) NOT NULL CHECK (Password =
> > > substring(Password from
> > > '^[0-9_A-Za-z]{5,15}$')),
> > > FullName VARCHAR(50) NOT NULL,
> > > Address VARCHAR(100) NOT NULL,
> > > Phone VARCHAR(15) NOT NULL,
> > > Created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
> > > Accessed TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
> > >
> > > PRIMARY KEY (CustomerID),
> > > UNIQUE (Email)
> > > );
> > >
> > > CREATE FUNCTION NewProfile(VARCHAR, VARCHAR, VARCHAR,
> > > VARCHAR, VARCHAR)
> > > RETURNS INTEGER AS '
> > > DECLARE
> > > aEmail ALIAS FOR $1;
> > > aPassword ALIAS FOR $2;
> > > aName ALIAS FOR $3;
> > > aAddr ALIAS FOR $4;
> > > aPhone ALIAS FOR $5;
> > > BEGIN
> > > INSERT INTO Customers(Email, Password, FullName, Address, Phone)
> > > VALUES(lower(aEmail), aPassword, aName, aAddr, aPhone);
> > > RETURN currval(''CustomerID'');
> > > END;
> > > ' LANGUAGE plpgsql;
> > >
> > > GRANT EXECUTE ON FUNCTION NewProfile(VARCHAR, VARCHAR,
> > > VARCHAR, VARCHAR,
> > > VARCHAR) TO SomeCustomer;
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faqs/FAQ.html
> > >
> >
> >
>
>