Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BE033476392 for ; Tue, 12 Nov 2002 12:16:17 -0500 (EST) Received: from smtp.ualg.pt (smtp.ualg.pt [193.136.224.8]) by postgresql.org (Postfix) with ESMTP id 83DB8476367 for ; Tue, 12 Nov 2002 12:16:13 -0500 (EST) Received: from localhost (smtp.ualg.pt [127.0.0.1]) by smtp.ualg.pt (Postfix) with ESMTP id C20664C395; Tue, 12 Nov 2002 17:16:17 +0000 (WET) Received: from ualg.pt (unknown [10.10.20.125]) by smtp.ualg.pt (Postfix) with ESMTP id C64F34C396; Tue, 12 Nov 2002 17:16:15 +0000 (WET) Message-ID: <3DD13751.1030100@ualg.pt> Date: Tue, 12 Nov 2002 17:16:01 +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: josh@agliodbs.com Cc: pgsql-sql Subject: Re: Permission on insert rules References: <3DCBF47B.7080809@ualg.pt> <200211111212.59912.josh@agliodbs.com> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms020304060706090907020305" X-Virus-Scanned: by AMaViS new-20020517 X-Razor-id: ace21d295eeb73c7e819526b3996762397f1e184 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/190 X-Sequence-Number: 10332 This is a cryptographically signed message in MIME format. --------------ms020304060706090907020305 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Josh Berkus wrote: >Luis, > > > >>Just a question. >>I'm writing some rules to insert/update some data in my database, and I >>gave all the privileges on that view to the user, and only select on the >>tables. >>When that user inserts data using the view, I thought that was user >>postgres that will do the rest ! But I got permission denied on those >>tables. >>The idea was to create a layer, with the views, giving to that user >>permission on views to insert and update, and not to tables. >>Is this possible ? >> >> > >I just checked this. It works fine in 7.2.3. > >I think that you are missing a step. If you want to have an updatable view, >then you need to define a Rule for updating it, such as: > >kitchen=# create rule update_password as on update to user_password >kitchen-# do instead update "user" set "password" = NEW."password" >kitchen-# where user_id = OLD.user_id; > >See the online docs, under Server Programming, for how to use the RULES >system. > > > 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. Luis Sousa --------------ms020304060706090907020305 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 CQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wMjExMTIxNzE2MDFa MCMGCSqGSIb3DQEJBDEWBBSRpi4deuKThkk+b5uBtsfg775c2TBSBgkqhkiG 9w0BCQ8xRTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG 9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0DAgIBKDCBrQYLKoZIhvcNAQkQ AgsxgZ2ggZowgZIxCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENh cGUxEjAQBgNVBAcTCUNhcGUgVG93bjEPMA0GA1UEChMGVGhhd3RlMR0wGwYD VQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxMfUGVyc29uYWwg RnJlZW1haWwgUlNBIDIwMDAuOC4zMAIDByCkMA0GCSqGSIb3DQEBAQUABIIB ADMIHlpXL8170YTM948Go0FT1ju88vYk9u45nUTISd9UfVOMTUCt/tt+zPch hFECR0Q6Nn5BT6BowkVMdVx27J6fs764KA6E59ejIdUTR/ic08QZahy12IgC zfnvM6JyHpklSNd4KwIys8Ojb9E9+jHOWZjwfc6d4IRyxXDZr4+oHXPTWbDu NYIe+LWam6MJbjq7b4ZSDNnEx5QyiMP4HCnb64Okdzrn7FAXThUM0xRDwmb2 K7hMJ9AejSpxXbvwk6p2513uo0QcgcJ5OahN0rPGBJqrFSBJx8d85Kn+Vcfq 3BqEzsx/fUEtegtF89M0pFJrw5IyQLplpou2W4rSvC0AAAAAAAA= --------------ms020304060706090907020305--