Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4CED747685C for ; Wed, 13 Nov 2002 05:46:33 -0500 (EST) Received: from smtp.ualg.pt (smtp.ualg.pt [193.136.224.8]) by postgresql.org (Postfix) with ESMTP id E52BE476874 for ; Wed, 13 Nov 2002 05:46:25 -0500 (EST) Received: from localhost (smtp.ualg.pt [127.0.0.1]) by smtp.ualg.pt (Postfix) with ESMTP id 2AC8E4C3F2; Wed, 13 Nov 2002 10:46:26 +0000 (WET) Received: from ualg.pt (unknown [10.10.20.125]) by smtp.ualg.pt (Postfix) with ESMTP id 497754BF0F; Wed, 13 Nov 2002 10:44:33 +0000 (WET) Message-ID: <3DD22D03.4090505@ualg.pt> Date: Wed, 13 Nov 2002 10:44:19 +0000 From: Luis Sousa User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020623 Debian/1.0.0-0.woody.1 MIME-Version: 1.0 To: Robert Treat Cc: Josh Berkus , pgsql-sql Subject: Re: Permission on insert rules References: <1037137215.27220.64.camel@camel> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms020203030104030202070209" X-Virus-Scanned: by AMaViS new-20020517 X-Razor-id: ac9f7d8187453c4154ce4b9a068c3dd722eab524 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/203 X-Sequence-Number: 10345 This is a cryptographically signed message in MIME format. --------------ms020203030104030202070209 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi again, I already know what's the problem. Actually, everything works fine in the example posted by Robert. Part of my rule is as simple as that example, but I'm also calling functions inside the rule. I have a table, whose primary key is a serial, that is connected to a few tables. In this view, I want to insert data, in the main table, and also in the "child" tables. My idea was to create a rule, that first inserts in the parent table, and some functions, that will select the parent table returning the id created, and will insert some data on child table (I'm open for sugestions to do this !!!). I don't know exactly how this works if more than one user at the same time !!!! When inserting, using the rule, the insert that's defined on the rule works fine, but the insert defined inside the function, doesn't (that's the one that gives permssion denied). Suppose these definitions: -- Tables definition CREATE TABLE "pessoal" ( "idPessoal" serial, "titulo" text default '', "nome" text NOT NULL, PRIMARY KEY ("idPessoal") ); CREATE TABLE "pessoalGabinete" ( "idPessoal" int4, edificio text, sala text, PRIMARY KEY ("idPessoal",edificio,sala), FOREIGN KEY("idPessoal") REFERENCES pessoal ON UPDATE CASCADE ); -- View definition CREATE VIEW "pessoalInfo_v" AS SELECT p.titulo, p.nome, pg.edificio, pg.sala FROM pessoal p LEFT OUTER JOIN "pessoalGabinete" pg USING ("idPessoal"); -- Function definition CREATE FUNCTION "pessoalInfoGab_f_insert"(text,text) RETURNS boolean AS ' DECLARE f_edificio ALIAS FOR $1; f_sala ALIAS FOR $2; pessoal RECORD; BEGIN SELECT MAX("idPessoal") AS max INTO pessoal FROM pessoal; INSERT INTO "pessoalGabinete" ("idPessoal",edificio,sala) VALUES (pessoal.max,f_edificio,f_sala); RETURN 1; END; ' LANGUAGE 'plpgsql'; -- Rule definition CREATE RULE "pessoalInfo_r_insert" AS ON INSERT TO "pessoalInfo_v" DO INSTEAD ( INSERT INTO pessoal (titulo,nome) VALUES (NEW.titulo,NEW.nome); SELECT "pessoalInfoGab_f_insert"(NEW.edificio,NEW.sala) AS ok; ); GRANT SELECT,INSERT,UPDATE on "pessoalInfo_v" to nobody; GRANT INSERT,UPDATE on "pessoal_idPessoal_seq" to nobody; INSERT INTO "pessoalInfo_v" (titulo,nome,edificio,sala) VALUES ('Dr.','Robert','A',5); And I got this message: NOTICE: Error occurred while executing PL/pgSQL function pessoalInfoGab_f_insert NOTICE: line 10 at SQL statement ERROR: pessoalGabinete: Permission denied. But, suppose that I use this rule instead and that already exists in table pessoal "idPessoal"=1: -- Rule definition CREATE RULE "pessoalInfo_r_insert" AS ON INSERT TO "pessoalInfo_v" DO INSTEAD ( INSERT INTO pessoal (titulo,nome) VALUES (NEW.titulo,NEW.nome); INSERT INTO "pessoalGabinete" ("idPessoal",edificio,sala) VALUES (1,NEW.edificio,NEW.sala); ); In this case everything works fine, but this doesn't solve my problem, because I need to know whats the number created by the sequence in pessoal. Any ideas ?? Thanks in advance. Luis Sousa Robert Treat wrote: >This should be a test case for what Luis wants, although it works in >7.2.1 so maybe not. Luis, if this isn't what your trying to do, you'll >need to post some code: > >create table parent (id int, name text, misc text); > >create view child as select id,name from parent; > >create rule jammasterjay as on insert to child do instead insert into >parent values (new.id,new.name); > >insert into parent values (1,'one','wahad'); >insert into parent values (2,'two','ithnain'); >insert into parent values (3,'three','thalata'); > >select * from parent; >select * from child; > >insert into child (4,'four'); > >select * from parent; > >create user mellymel; >grant select on child to mellymel; >grant insert on child to mellymel; > >** reconnect as mellymel ** > >select * from parent; (generates error) >select * from child; > >insert into child values (5,'five'); > >select * from child; (has all 5 rows) > > >Robert Treat > >On Tue, 2002-11-12 at 12:29, Josh Berkus wrote: > > >>Luis, >> >> >> >>>That's what I already made. The problem is when I do the update, I >>>permission denied in all the tables for update and insert. The user >>>that's making this operation only have select privilege. >>>Any way, I'm using version 7.2.1-2 for debian. >>> >>> >>I can't reproduce the problem, and permissions did not get fixed >>between 7.2.1 and 7.2.3. So I'm pretty sure that you're missing >>something, somewhere. >> >>Please post: >> >>1) The table definitions for the tables being updated. >>2) The view definition and permissions >>3) The Rules statements defined on the view >>4) A copy of your database session where your update is denied, >>including the exact error message received. >> >>Without that information, no futher help is available. >> >>-Josh Berkus >> >> >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> >> > > > > > > > --------------ms020203030104030202070209 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIJPDCCAvwwggJloAMCAQICAwcgpDANBgkqhkiG9w0BAQQFADCBkjEL MAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJ Q2FwZSBUb3duMQ8wDQYDVQQKEwZUaGF3dGUxHTAbBgNVBAsTFENlcnRpZmlj YXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9QZXJzb25hbCBGcmVlbWFpbCBSU0Eg MjAwMC44LjMwMB4XDTAyMDQwMjEzNDYxNloXDTAzMDQwMjEzNDYxNlowQTEf MB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEeMBwGCSqGSIb3DQEJ ARYPbGxzb3VzYUB1YWxnLnB0MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIB CgKCAQEArq+CLmsIQV5pSRtuNd9sptT22gTUwt0UReWfSlCTApnNcGDQCiS8 nluhr5AWbJWhhIVHQETSKWlT+y09KsYf7AeW1zBQWSUJjhpCDYd8n+T0SxFl 24ZZJhZQ57kVJ7iNzJ7WPYQGk06zoRWgtnFcvVuA1az0vhwWHNk8+1JaCTeW lwjPRptJZ78CNiN2prAoEoI9m7/jUW6iTJmpNinzkIa1qpKEdPrCQM/dhuk6 JDl4qmiQ2a87I/aiRJCPtTBIl/Zt2Vjde5Br2qR8pWisjaoVl9ntJJvni9cl XJGk/uO4iwza7IL2K6PsRvbGAoswWKeEStK+A/WXoh1UXbHEyQIDAQABoyww KjAaBgNVHREEEzARgQ9sbHNvdXNhQHVhbGcucHQwDAYDVR0TAQH/BAIwADAN BgkqhkiG9w0BAQQFAAOBgQCee996SyMsQgWwQUrnHsmltoIMnkkLuWvWfWwq 3fqe8ZLpfvXvFg6Z52EtPZ8vlzp41KM1HQErW2dMe2DyUfbhaUqqNQBvf67V G8STIA+83VEKFK2njzIKpwLXOOjL0estNpcrE1azR/MTfvvu3adO6j5syXd6 pfm4elI2W0yLMzCCAvwwggJloAMCAQICAwcgpDANBgkqhkiG9w0BAQQFADCB kjELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UE BxMJQ2FwZSBUb3duMQ8wDQYDVQQKEwZUaGF3dGUxHTAbBgNVBAsTFENlcnRp ZmljYXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9QZXJzb25hbCBGcmVlbWFpbCBS U0EgMjAwMC44LjMwMB4XDTAyMDQwMjEzNDYxNloXDTAzMDQwMjEzNDYxNlow QTEfMB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEeMBwGCSqGSIb3 DQEJARYPbGxzb3VzYUB1YWxnLnB0MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A MIIBCgKCAQEArq+CLmsIQV5pSRtuNd9sptT22gTUwt0UReWfSlCTApnNcGDQ CiS8nluhr5AWbJWhhIVHQETSKWlT+y09KsYf7AeW1zBQWSUJjhpCDYd8n+T0 SxFl24ZZJhZQ57kVJ7iNzJ7WPYQGk06zoRWgtnFcvVuA1az0vhwWHNk8+1Ja CTeWlwjPRptJZ78CNiN2prAoEoI9m7/jUW6iTJmpNinzkIa1qpKEdPrCQM/d huk6JDl4qmiQ2a87I/aiRJCPtTBIl/Zt2Vjde5Br2qR8pWisjaoVl9ntJJvn i9clXJGk/uO4iwza7IL2K6PsRvbGAoswWKeEStK+A/WXoh1UXbHEyQIDAQAB oywwKjAaBgNVHREEEzARgQ9sbHNvdXNhQHVhbGcucHQwDAYDVR0TAQH/BAIw ADANBgkqhkiG9w0BAQQFAAOBgQCee996SyMsQgWwQUrnHsmltoIMnkkLuWvW fWwq3fqe8ZLpfvXvFg6Z52EtPZ8vlzp41KM1HQErW2dMe2DyUfbhaUqqNQBv f67VG8STIA+83VEKFK2njzIKpwLXOOjL0estNpcrE1azR/MTfvvu3adO6j5s yXd6pfm4elI2W0yLMzCCAzgwggKhoAMCAQICEGZFcrfMdPXPY3ZFhNAukQEw DQYJKoZIhvcNAQEEBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3Rl IENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMg RGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBD QTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNv bTAeFw0wMDA4MzAwMDAwMDBaFw0wNDA4MjcyMzU5NTlaMIGSMQswCQYDVQQG EwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRv d24xDzANBgNVBAoTBlRoYXd0ZTEdMBsGA1UECxMUQ2VydGlmaWNhdGUgU2Vy dmljZXMxKDAmBgNVBAMTH1BlcnNvbmFsIEZyZWVtYWlsIFJTQSAyMDAwLjgu MzAwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAN4zMqZjxwklRT7Sbngn Z4HF2ogZgpcO40QpimM1Km1wPPrcrvfudG8wvDOQf/k0caCjbZjxw0+iZdsN +kvx1t1hpfmFzVWaNRqdknWoJ67Ycvm6AvbXsJHeHOmr4BgDqHxDQlBRh4M8 8Dm0m1SKE4f/s5udSWYALQmJ7JRr6aFpAgMBAAGjTjBMMCkGA1UdEQQiMCCk HjAcMRowGAYDVQQDExFQcml2YXRlTGFiZWwxLTI5NzASBgNVHRMBAf8ECDAG AQH/AgEAMAsGA1UdDwQEAwIBBjANBgkqhkiG9w0BAQQFAAOBgQAxsUtHXfkB ceX1U2xdedY9mMAmE2KBIqcS+CKV6BtJtyd7BDm6/ObyJOuR+r3sDSo491BV qGz3Da1MG7wD9LXrokefbKIMWI0xQgkRbLAaadErErJAXWr5edDqLiXdiuT8 2w0fnQLzWtvKPPZE6iZph39Ins6ln+eE2MliYq0FxjGCAycwggMjAgEBMIGa MIGSMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYD VQQHEwlDYXBlIFRvd24xDzANBgNVBAoTBlRoYXd0ZTEdMBsGA1UECxMUQ2Vy dGlmaWNhdGUgU2VydmljZXMxKDAmBgNVBAMTH1BlcnNvbmFsIEZyZWVtYWls IFJTQSAyMDAwLjguMzACAwcgpDAJBgUrDgMCGgUAoIIBYTAYBgkqhkiG9w0B CQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wMjExMTMxMDQ0MTla MCMGCSqGSIb3DQEJBDEWBBRQXDfNA4FVwBpZZAyYNr4+AKmdgTBSBgkqhkiG 9w0BCQ8xRTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG 9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0DAgIBKDCBrQYLKoZIhvcNAQkQ AgsxgZ2ggZowgZIxCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENh cGUxEjAQBgNVBAcTCUNhcGUgVG93bjEPMA0GA1UEChMGVGhhd3RlMR0wGwYD VQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxMfUGVyc29uYWwg RnJlZW1haWwgUlNBIDIwMDAuOC4zMAIDByCkMA0GCSqGSIb3DQEBAQUABIIB AFRu1Y61DCgcF0HTkllxJRSI3OReLJM8/+PoOefgyZxNiqLRb3r6GcABvKNz iGB6y/gCTN6ajEKvEwukkQ8OIKi8Yv1T+63CZPXE/p44cUdFtWhN4prd6E9l UR1rw5kcuy/BE7++paTAlp1DTMIRI32wvohnm3EMl00i6kHqthaLxGb8GdO5 h/P+gvThLhIgUwgEAjKum8UmTjh58SJQusN9if3vP1NKru2u4ls+Yre3wS0K j9wVWBB0SCHz5RmqhEMYFlSqlsuQrMnwFqsLnEhIk8MZ0gVg0WzPIfQA0XS4 0fMOZki69VuwtBxFRcwYZWvcRSF+hJg/zhxcZ+BCXFEAAAAAAAA= --------------ms020203030104030202070209--