What is wrong here?

Started by Tumurbaatar S.almost 22 years ago6 messagesgeneral

tumurbaatar@datacom.mn

almost 22 years ago

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;

s.vanroye@farcourier.com

almost 22 years ago

In reply to: Tumurbaatar S. (#1)

Re: 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

Show quoted text

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

Import Notes

Resolved by subject fallback

s.vanroye@farcourier.com

almost 22 years ago

In reply to: Stijn Vanroye (#2)

Re: 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

Show quoted text

-----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

Import Notes

Resolved by subject fallback

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Tumurbaatar S. (#1)

Re: What is wrong here?

"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

nolan@gw.tssi.com

almost 22 years ago

In reply to: Stijn Vanroye (#3)

Re: 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.

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

s.vanroye@farcourier.com

almost 22 years ago

In reply to: Mike Nolan (#5)

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@datacom.mn] wrote:

Show quoted text

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

Import Notes

Resolved by subject fallback