[BUGS] BUG #1832: Can't create function in plpgsql which has more than 10 parameters
The following bug has been logged online:
Bug reference: 1832
Logged by: Bohdan Linda
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.3.4
Operating system: Solaris
Description:Can't create function in plpgsql which has more than 10
parameters
Details:
I have found if I will create functions as:
CREATE OR REPLACE FUNCTION addSyslog (
VARCHAR
,VARCHAR(50)
,INET
,TIMESTAMP
,VARCHAR(255)
,NUMERIC(5)
,NUMERIC
,INET
,INTEGER
,INET
,INTEGER
) RETURNS INTEGER AS '
DECLARE
BEGIN
RETURN 1;
END
' LANGUAGE 'plpgsql';
and I will try to call:
SELECT
addSyslog('StrA','Strb','100.100.100.100','2005-08-17
12:24:37.545024','a log
message',555,87923463,'192.168.0.1',0,'192.168.0.2',0);
I will get:
WARNING: plpgsql: ERROR during compile of addsyslog near line 3
ERROR: syntax error at or near ","
But if I call:
SELECT
addSyslog('StrA','Strb','100.100.100.100','2005-08-17
12:24:37.545024','a log
message',555,87923463,'192.168.0.1',0,'192.168.0.2');
everything goes well. I shall be able to pass more than 10 parameters.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] [GENERAL] pgsql-bugs
Redirecting this to pgsql-bugs where it belongs.
Robert Treat
On Wed, 2005-08-17 at 01:29, Lee Hyun soon wrote:
> From: "Lee Hyun soon" <[EMAIL PROTECTED]>
> To: pgsql-bugs@postgresql.org
> Date: Wed, 17 Aug 2005 05:36:23 +0100 (BST)
> Subject: BUG #1826: pgsql odbc & ADO.NET
>
> The following bug has been logged online:
>
> Bug reference: 1826
> Logged by: Lee Hyun soon
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.0.2
> Operating system: Windows XP Professional SP1
> Description:pgsql odbc & ADO.NET
> Details:
>
> I'm a Corean Coder.
>
> During C# Coding, I Found it.
>
> -
> http://www.windows.or.kr/zboard/bbs/view.php?id=app1data&page=1&sn1=&divpage
> =1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=6
> ---
>
> Download #1 : RichEditBoxTest.zip (18.6 KB)
> Download #2 : input_data.txt (3.2 KB)
>
> RichEditBoxTest.zip is Test Program(C#.net)
> input_data.txt is input Data
>
> i use latest odbc provider, and it's database scheme is
> ==
> CREATE TABLE nmsdata
> (
> orgin_code char(17) NOT NULL DEFAULT to_char(now(),
> 'mmddHH24MISSMS'::text),
> data text,
> datetime timestamp DEFAULT now(),
> bigo1 varchar(50),
> "year" char(4),
> data_gubun numeric DEFAULT 0
> )
> WITHOUT OIDS;
> ALTER TABLE nmsdata OWNER TO postgres;
> GRANT ALL ON TABLE nmsdata TO postgres;
> GRANT ALL ON TABLE nmsdata TO public;
> ==
> and, odbc dsn is "remote"
>
>
>
>
> the problem is short string is "insert" DML processing completely.
> and long string is also.
>
> but,
> after shot string "select" DML, data's tail is broken.
>
> u see my source(if u know C# Language and have .Net Comfiler), u catch this
> problem.
>
>
> -
> i cannot use english T^T well.
> i hope that u catch my problem. and this problem will be solved.
>
>
> - Corean(Korean) Lee.
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] BUG #1831: plperl gives error after reconnect.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane asked: > I could not duplicate this in either 8.0 or HEAD branches. It looks > a bit like an old bug that we had in plperl, though. Are you sure your > plperl.so is up to date? Looks like Michael is already far along, but yes, my plperl.so was up to date. This is on a Red Hat Linux box, using --with-perl and --with-gnu-ld as the only compile options. It's a very subtle bug: on my box, simply leaving out the trigger definition, or having the function not do a spi_exec_query will not raise the error. I've worked around this locally by not using plperlu (hence the original reason to switch to another user), but I sure miss being able to do "use strict" :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200508181050 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEARECAAYFAkMEoFkACgkQvJuQZxSWSsjTpwCgmt9kLApba6xDygvgl5qb/vdc Zh4AoPx1or9LLWSTUZQDcDjxJCfNBb08 =5Jt7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1832: Can't create function in plpgsql which has more than 10 parameters
"Bohdan Linda" <[EMAIL PROTECTED]> writes: > Description:Can't create function in plpgsql which has more than 10 > parameters Your example works fine for me. Maybe something broken about your local copy of Postgres? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a file
Bruno and interested list members I want to follow what is suggested here. How are STDIN and STDOUT addressed when using the JDBC driver? Or in other words where can I write or receive megabytes of data? I would not want to append this to the String of a SQL Statement in Java because that is a String in memory. Thanks Bernard On Wed, 17 Aug 2005 06:51:12 -0500, you wrote: >On Wed, Aug 17, 2005 at 09:22:16 +0100, > Bernard <[EMAIL PROTECTED]> wrote: >> >> The following bug has been logged online: > >This isn't a bug and you really should have asked this question on >another list. I am moving the discussion over to the general list. > >> >> Bug reference: 1830 >> Logged by: Bernard >> Email address: [EMAIL PROTECTED] >> PostgreSQL version: 8.0.3 >> Operating system: Linux RedHat 9 >> Description:Non-super-user must be able to copy from a file >> Details: >> >> On the attempt to bulk load a table from a file that is owned by the >> non-superuser current database user, the following error message is >> printed: >> >> "must be superuser to COPY to or from a file" >> >> What is the reason for this limitation? > >This is described in the documentation for the copy command. > >> >> It can't justifiably be for security reasons because if a web application >> such as tomcat requires to bulk load tables automatically on a regular basis >> then one would be forced to let the web application connect as superuser, >> which is very bad for security. > >No, because you can have the app read the file and then pass the data to >the copy command. To do this you use STDIN as the file name. > >> >> In MySQL bulk loading works for all users. > >You can use the \copy command in psql to load data from files. > >> >> We need a Postgresql solution. >> >> We have a web application where both MySQL and Postresql are supported. With >> Postgresql, the application would have to connect as user postgres. We have >> to explain this security risk to our clients very clearly. >> >> ---(end of broadcast)--- >> TIP 2: Don't 'kill -9' the postmaster > >---(end of broadcast)--- >TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #1834: Non-super-user must be able to copy from a file through JDBC
The following bug has been logged online: Bug reference: 1834 Logged by: Bernard Email address: [EMAIL PROTECTED] PostgreSQL version: 8.03 Operating system: Linux RedHat 9 Description:Non-super-user must be able to copy from a file through JDBC Details: On the attempt to bulk load a table from a file that is owned by the non-superuser current database user, the following error message is printed: "must be superuser to COPY to or from a file" Following this advice would force the application to connect as superuser which is a severe security risk. The postgres-specific workaround to use STDIN with COPY is not supported by the Postgres JDBC driver. In comparison MySQL bulk loading works for all users with its JDBC driver. We need a Postgresql solution to this security issue that is as simple as the MySQL version. We have a web application where both MySQL and Postresql are supported. With Postgresql, the application would have to connect as user postgres. We have to explain this security risk to our clients very clearly. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > I want to follow what is suggested here. How are STDIN and STDOUT > addressed when using the JDBC driver? The current JDBC driver doesn't support this mode of COPY. There was some work done in the past to support this but it never got to the point of making it into the official driver; see the pgsql-jdbc archives for details. -O ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Oliver and interested list members: Thanks for the related information. The majority of JDBC users trying to bulk load tables would not want to send the data through their connection. This connection is designed to send commands and to transfer only as much data as necessary and as little as possible. In other words, COPY, and its corresponding commands in other datbase engines are designed to transfer data using the fastest possible method, which is typically to and from files. For the majority JDBC users, there is no real need for a STDIN/STDOUT option because they can always write to a file. The need is only created by the limitations of the Postgres COPY command. I can't see why a workaround should be developed instead of or before fixing the COPY command. It works in other DB engines. Regards Bernard On Fri, 19 Aug 2005 11:10:42 +1200, you wrote: >Bernard wrote: > >> I want to follow what is suggested here. How are STDIN and STDOUT >> addressed when using the JDBC driver? > >The current JDBC driver doesn't support this mode of COPY. > >There was some work done in the past to support this but it never got to >the point of making it into the official driver; see the pgsql-jdbc >archives for details. > >-O > >---(end of broadcast)--- >TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > The majority of JDBC users trying to bulk load tables would not want > to send the data through their connection. This connection is designed > to send commands and to transfer only as much data as necessary and as > little as possible. I don't understand why this is true at all -- for example, our application currently does bulk INSERTs over a JDBC connection, and moving to COPY has been an option I looked at in the past. Importing lots of data from a remote machine is hardly an uncommon case. > The need is only created by the limitations of the Postgres COPY > command. > > I can't see why a workaround should be developed instead of or before > fixing the COPY command. > > It works in other DB engines. I guess that other DB engines don't care about unprivileged DB users reading any file that the backend can access. -O ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Oliver and interested list members: I was referring to the majority of users wanting to "bulk" load tables not to the majority of all or whatever users who may or may not know or care about the difference in performance between INSERT and COPY. This difference of performance is the main reason for the COPY command, and this is also the reason why bulk loading through the JDBC interface will never match the performance of the COPY fith files command. The COPY command with STDIN or STDOUT is a speciality that the majority of users would not normally ask for because they usually think in terms of files and rightly so. Comparable with a STDIN/STDOUT workaround would be to pipe input and output to and from SQL stored procedures. What I mean to say is that we want this to be strictly server side for best performance and we don't want to get the client involved in the raw processing which is in violation of any 3 tier client-server architecture. In addition to this, not only will the client and network be loaded with additional processing demand, but the server load will also increase because it has to service the JDBC interface for I/O. The whole architectural setup for such "bulk" loading is a mess. Regards, Bernard On Fri, 19 Aug 2005 12:27:01 +1200, you wrote: >Bernard wrote: > >> The majority of JDBC users trying to bulk load tables would not want >> to send the data through their connection. This connection is designed >> to send commands and to transfer only as much data as necessary and as >> little as possible. > >I don't understand why this is true at all -- for example, our >application currently does bulk INSERTs over a JDBC connection, and >moving to COPY has been an option I looked at in the past. Importing >lots of data from a remote machine is hardly an uncommon case. > >> The need is only created by the limitations of the Postgres COPY >> command. >> >> I can't see why a workaround should be developed instead of or before >> fixing the COPY command. >> >> It works in other DB engines. > >I guess that other DB engines don't care about unprivileged DB users >reading any file that the backend can access. > >-O > >---(end of broadcast)--- >TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1831: plperl gives error after reconnect.
On Thu, Aug 18, 2005 at 02:52:02PM -, Greg Sabino Mullane wrote: > Tom Lane asked: > > I could not duplicate this in either 8.0 or HEAD branches. It looks > > a bit like an old bug that we had in plperl, though. Are you sure your > > plperl.so is up to date? > > Looks like Michael is already far along, but yes, my plperl.so was up to date. > This is on a Red Hat Linux box, using --with-perl and --with-gnu-ld as the > only compile options. It's a very subtle bug: on my box, simply leaving out > the trigger definition, or having the function not do a spi_exec_query will > not raise the error. Tom Lane once mentioned that "Valgrind is fairly useless for debugging postgres," but has anybody tried it for this problem? I tried using the FreeBSD port but it's having trouble (first I had to hack in support for a system call, now it's terminating the postmaster with SIBGUS on a call to setproctitle). -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > This difference of performance is the main reason for the COPY > command, and this is also the reason why bulk loading through the JDBC > interface will never match the performance of the COPY fith files > command. In some admittedly unscientific tests I see less than 10% difference between server-side COPY and client-side COPY FROM STDIN (psql's \copy command) on a 28mb input file. That's down in the per-run noise. Doing it via JDBC will undoubtably add some extra overhead, but I'd estimate that it's about the same sort of overhead as writing your data out to a file from Java in the first place takes. If you've already got the data in a file, why not just use psql's \copy command? This uses COPY FROM STDIN, reads the file as the user running psql, and does not require superuser permissions. > The whole architectural setup for such "bulk" loading is a mess. Do you have a concrete suggestion for improving bulk loading that doesn't open security holes? -O ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > Oliver and interested list members: [...] And please fix your anti-spam system so it doesn't send me a "you must jump through these hoops to send me email" message every time please! (usual cc: to poster removed for that reason) -O ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a file
Oliver and interested list members: In the majority of bulk load cases, the input exists as a file already and cannot be had without reading from that file. So the writing of the file does not count as an additional processing overhead. The use of psql in our case requires the launching of an external process from within the running Java application, which is an overhead in processing and code maintenance that must not be under-estimated. My suggestions for improving the COPY command so it can be used by non-superuser users would be as follows: 1) Add optional Postgresql user permission to use the COPY command with files. or 2) Split up security risk calculations between the two directions "TO" and "FROM" and relax security. Look at MySQL for clues. The application developer can manage security on file system permission level. or 3) Close the ident loop in such a way that if a Postgresql user was granted access via ident as an operating system user then the COPY command is executed as a process with that user ID and not as postgres superuser. Option 2) would possibly be the easiest and my personal preference. Option 1) would possibly the most politically correct one and would fully satisfy my requirements. Option 3) would possibly open a can of worms, especially because there are already unresolved authentication issues with ident and Java. There are systems where a database user cannot possibly be a lose cannon type of user. For example, in a 3 tier client-server application or in most web applications the end users never get their hands on a database connection. The connections are owned by the server and the users cannot write server code. In these cases there is the question why not use superuser postgres for the connections? I will do it but I have unspecified reservations - just a strange feeling. I hope that this discussion was not entirely useless and will lead to an improvement of the current status, whatever it may be. Oliver, my apologies regarding the spam filter - that part of my ISP's service is currently unaccessible for configuration. Regrads Bernard On Fri, 19 Aug 2005 14:11:38 +1200, you wrote: >Bernard wrote: > >> This difference of performance is the main reason for the COPY >> command, and this is also the reason why bulk loading through the JDBC >> interface will never match the performance of the COPY fith files >> command. > >In some admittedly unscientific tests I see less than 10% difference >between server-side COPY and client-side COPY FROM STDIN (psql's \copy >command) on a 28mb input file. That's down in the per-run noise. > >Doing it via JDBC will undoubtably add some extra overhead, but I'd >estimate that it's about the same sort of overhead as writing your data >out to a file from Java in the first place takes. > >If you've already got the data in a file, why not just use psql's \copy >command? This uses COPY FROM STDIN, reads the file as the user running >psql, and does not require superuser permissions. > >> The whole architectural setup for such "bulk" loading is a mess. > >Do you have a concrete suggestion for improving bulk loading that >doesn't open security holes? > >-O > >---(end of broadcast)--- >TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
"Oliver Jowett" <[EMAIL PROTECTED]> wrote:[EMAIL PROTECTED] > Bernard wrote: > >> The majority of JDBC users trying to bulk load tables would not want >> to send the data through their connection. This connection is designed >> to send commands and to transfer only as much data as necessary and as >> little as possible. > > I don't understand why this is true at all -- for example, our > application currently does bulk INSERTs over a JDBC connection, and > moving to COPY has been an option I looked at in the past. Importing > lots of data from a remote machine is hardly an uncommon case. When exporting and importing data from other data sources, there maybe many rows to be moved. In this special case, should COPY be faster than INSERTs? Have pgsql-jdbc supported COPY? If I read correctly, what Bernard want is COPY from/to server-side files. That is actually a security risk for non-superuser. They may read or overwrite any files can read/write by postgres server process. > >> The need is only created by the limitations of the Postgres COPY >> command. >> >> I can't see why a workaround should be developed instead of or before >> fixing the COPY command. >> >> It works in other DB engines. > > I guess that other DB engines don't care about unprivileged DB users > reading any file that the backend can access. > > -O > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a file
On Fri, Aug 19, 2005 at 10:16:29AM +1200, Bernard wrote: > Bruno and interested list members > > I want to follow what is suggested here. How are STDIN and STDOUT > addressed when using the JDBC driver? > > Or in other words where can I write or receive megabytes of data? I don't know how JDBC does it, but as an example the Perl Pg module has these functions: $ret = $conn->putline($string) Sends a string to the backend. The application must explicitly send the two characters "\." to indicate to the backend that it has finished sending its data. $ret = $conn->putnbytes($buffer, $nbytes) Sends n bytes to the backend. Returns 0 if OK, EOF if not. $ret = $conn->endcopy This function waits until the backend has finished the copy. It should either be issued when the last string has been sent to the backend using putline or when the last string has been received from the backend using getline. endcopy returns 0 on success, 1 on failure. Looking at google I see the following: http://archives.postgresql.org/pgsql-jdbc/2004-10/msg00166.php saying it's not supported but there is a patch. That was a year ago though. Some later emails suggest it is possible. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpNenyMAlSDR.pgp Description: PGP signature
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > 2) Split up security risk calculations between the two directions "TO" > and "FROM" and relax security. Look at MySQL for clues. The > application developer can manage security on file system permission > level. I looked at MySQL's docs briefly and its behaviour seems almost the same as PostgreSQL's with some minor differences: - the equivalent to COPY is "LOAD DATA INFILE" - the equivalent to FROM STDIN is "LOCAL" - for non-LOCAL loads, the DB user must have FILE privilege which is "file access on server host". Given FILE privilege in MySQL, you can read existing files and create new files based on the access the server user has. It sounds like what you really want is the ability to grant something like FILE access without granting all superuser rights? Sounds like a feature request, not a bug, to me :-) Also, you better hope that there's no sensitive information readable by the server user that could be used to gain superuser access.. such as .pgpass files or info from pg_hba.conf, for example. -O ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
On Fri, 19 Aug 2005, Bernard wrote: > My suggestions for improving the COPY command so it can be used by > non-superuser users would be as follows: If you want to do this without switching to a different UNIX user, can't you already write a small SECURITY DEFINER function as a superuser that does the copy from file based on arguments and then give permissions to that function to the appropriate non-superusers? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a file
On 2005-08-19, Bernard <[EMAIL PROTECTED]> wrote: > Oliver and interested list members: > > In the majority of bulk load cases, the input exists as a file already But not necessarily on the server. > The use of psql in our case requires the launching of an external > process from within the running Java application, which is an overhead > in processing and code maintenance that must not be under-estimated. Certainly supporting COPY via STDIN within the java code seems preferable. > My suggestions for improving the COPY command so it can be used by > non-superuser users would be as follows: > > 1) Add optional Postgresql user permission to use the COPY command > with files. Not acceptable, since the ability to copy from a file permits you to read from the internals of the database itself bypassing security restrictions; in particular, if there is a password for the postgres superuser, then it would be trivially exposed by this method. A user with permission to use COPY thus becomes security-equivalent to a superuser in any case. > or > > 2) Split up security risk calculations between the two directions "TO" > and "FROM" and relax security. Look at MySQL for clues. The > application developer can manage security on file system permission > level. Same problem as above. COPY FROM is not in any sense less of a security risk than COPY TO. > or > > 3) Close the ident loop in such a way that if a Postgresql user was > granted access via ident as an operating system user then the COPY > command is executed as a process with that user ID and not as postgres > superuser. Postgres does not itself run as root, therefore it lacks the ability to spawn a program that runs under a different userid to itself. Over the local socket, which is the only context in which ident auth is at all trustable, it would in theory be possible to implement COPY to a file descriptor opened by the client and passed through the socket. I personally think it is unlikely that this would be worth the (not inconsiderable) amount of work needed to implement it, since the performance overhead of copying the data via the socket instead is not a large factor in the overall cost of a large copy. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a
[ A bit off topic, but... ] Oliver Jowett <[EMAIL PROTECTED]> writes: > And please fix your anti-spam system so it doesn't send me a "you must > jump through these hoops to send me email" message every time please! It's standard policy on the PG lists that we boot subscribers who auto-reply to list mail like that. If you find yourself getting unsolicited 'bot replies from list postings, let Marc know. (Note: if you replied To: somebody and cc: to the list, and you got the antispam challenge due to the To: copy, that's not grounds for list removal. But it's still a sign of a jerk. If I take the time to answer someone's question, I'm not going to look very favorably on a demand to confirm that I'm a human before they'll deign to read my answer.) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Oliver Jowett <[EMAIL PROTECTED]> writes: > It sounds like what you really want is the ability to grant something > like FILE access without granting all superuser rights? Sounds like a > feature request, not a bug, to me :-) AFAICT, the complaint really boils down to there not being any support for COPY-from-client in the JDBC driver. Which is definitely a feature request, but not one directed to the server geeks ;-) What is the story on JDBC COPY support, anyway? I'm aware that there's an unofficial patch for that, but I'm not clear about why it's not made it into the accepted version. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Tom Lane wrote: > What is the story on JDBC COPY support, anyway? I'm aware that there's > an unofficial patch for that, but I'm not clear about why it's not made > it into the accepted version. I didn't like the whole "here is an undifferentiated stream of data" approach -- there were some JDBC interfaces we could adapt to read/write typed data. That never happened, though. I suppose we could apply a patch similar to the original one, given that there doesn't seem like much interest in a typed version, but it's likely to need rework as there's been at least one overhaul of the driver's protocol handling layer since then. -O ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a
Tom Lane wrote: > Oliver Jowett <[EMAIL PROTECTED]> writes: > >>It sounds like what you really want is the ability to grant something >>like FILE access without granting all superuser rights? Sounds like a >>feature request, not a bug, to me :-) > > > AFAICT, the complaint really boils down to there not being any support > for COPY-from-client in the JDBC driver. Bernard was also objecting to the overhead of pushing the data down a TCP pipe when it's already available locally, I think.. I didn't find any real difference there when I compared the two methods, though. -O ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1830: Non-super-user must be able to copy from a file
Andrew On Fri, 19 Aug 2005 04:17:16 -, you wrote: >> In the majority of bulk load cases, the input exists as a file already > >But not necessarily on the server. True. But I am concerned with the server, and there I want that things are handled on the server, not on the client. > >> The use of psql in our case requires the launching of an external >> process from within the running Java application, which is an overhead >> in processing and code maintenance that must not be under-estimated. > >Certainly supporting COPY via STDIN within the java code seems preferable. Why do you say that? That option does not exist because the Postgresql JDBC driver does not support it. > >> My suggestions for improving the COPY command so it can be used by >> non-superuser users would be as follows: >> >> 1) Add optional Postgresql user permission to use the COPY command >> with files. > >Not acceptable, since the ability to copy from a file permits you to >read from the internals of the database itself bypassing security >restrictions; in particular, if there is a password for the postgres >superuser, then it would be trivially exposed by this method. A user >with permission to use COPY thus becomes security-equivalent to a >superuser in any case. May be. Not acceptable by whom? If the owner of an application owning the connections trusts the application and gets the postgres superuser to grant it the right to read from files, then it is obviously acceptable to the owner of the application and to the postgres superuser. There is no doubt about that and the owner of the application is not concerned with 3rd party acceptability. This would be a solution even if Postgres system files were totally exposed. Better than nothing. But we can take this one step further so that we don't even need to trust ourselves: The logical next step is that for a non-postgresql-superuser user, COPY FROM files have to be world-readable and COPY TO files and directories have to be world-writable. The server checks the file attributes and grants copy permission depending on them. Obviously any Postrgres system files must not be world-readable and world-writable. Problem solved. One doesn't need to be a genius to figure this out. Not having at least this primitive solution is quite powerless. Simply rejecting this command when the user is not superuser can only be considered a temporary workaround solution. It is long overdue for replacement. And trust me, it is quite frustrating having to hit such a barrier after having seen this feature implemented in MySQL for the last ten years. I am not talking about myself only. Just do a google groups search "jdbc postgres COPY STDIN" and you will see what I mean. Lots of frustration, improvised stuff but no generic solution to this simple problem. > >> or >> >> 2) Split up security risk calculations between the two directions "TO" >> and "FROM" and relax security. Look at MySQL for clues. The >> application developer can manage security on file system permission >> level. > >Same problem as above. COPY FROM is not in any sense less of a security >risk than COPY TO. There is obviously a difference between the permission to read system files and to destroy them. But this was only a suggestion. The distinction might not be required at all. > >> or >> >> 3) Close the ident loop in such a way that if a Postgresql user was >> granted access via ident as an operating system user then the COPY >> command is executed as a process with that user ID and not as postgres >> superuser. > >Postgres does not itself run as root, therefore it lacks the ability to >spawn a program that runs under a different userid to itself. I did not know the internals of whether this could be done or not. It is just a conceptual idea where somehow the server may be able to utilise file permission information. I have my own reservations, too. > >Over the local socket, which is the only context in which ident auth is >at all trustable, it would in theory be possible to implement COPY to a >file descriptor opened by the client and passed through the socket. I >personally think it is unlikely that this would be worth the (not >inconsiderable) amount of work needed to implement it, since the performance >overhead of copying the data via the socket instead is not a large factor >in the overall cost of a large copy. I agree one has to compare costs carefully. Regards, Bernard ---(end of broadcast)--- TIP 6: explain analyze is your friend
