Postgres Pro
Downloads
Home   >   mailing lists

Thread: What is wrong here?

What is wrong here?

From
"Tumurbaatar S."
Date:
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
'^.+@.+\..+$')),
  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;

Re: What is wrong here?

From
"Stijn Vanroye"
Date:
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
> '^.+@.+\..+$')),
>   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
>

Re: What is wrong here?

From
"Stijn Vanroye"
Date:
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
rightson 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@datacom.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.vanroye@farcourier.com>
> To: "Tumurbaatar S." <tumurbaatar@datacom.mn>;
> <pgsql-general@postgresql.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
> > '^.+@.+\..+$')),
> >   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
> >
>
>

Re: What is wrong here?

From
Tom Lane
Date:
"Tumurbaatar S." <tumurbaatar@datacom.mn> writes:
> 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?

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.

            regards, tom lane

Re: What is wrong here?

From
Mike Nolan
Date:
> 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.

Permissions problems can take a bit of detective work to nail down.

Are you using schemas?  If so, is that table in the public schema or
in a user schema?  Is the function in the public schema or in a user
schema?
--
Mike Nolan

Re: What is wrong here?

From
"Stijn Vanroye"
Date:
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
thatperspective it's no different from the user calling the insert directly. Except that you do some extra checks in
yourfunctions.  

About the triggers: I don't have much experience with triggers myself, but from what I know you can achieve these
thingsusing triggers. Escpecialy check/modify values. If I'm not mistaken there has been a thread earlier in one of the
mailinglists 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@datacom.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.vanroye@farcourier.com>
> To: "Tumurbaatar S." <tumurbaatar@datacom.mn>
> Cc: <pgsql-general@postgresql.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@datacom.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.vanroye@farcourier.com>
> > To: "Tumurbaatar S." <tumurbaatar@datacom.mn>;
> > <pgsql-general@postgresql.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
> > > '^.+@.+\..+$')),
> > >   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
> > >
> >
> >
>
>