### [HACKERS] Functions with COPY

Hi, Consider the following input data: 1234,24.50,10-Jan-2003,10/1/03,10-01-2003,hiall The interpretation for the numbers is: 1234 = 12.34, 24.50 = 24.50 The interpretation for the dates is: January 10th, 2003, October 1st, 2003, October 1st, 2003 I don't believe it's possible,

### Re: [HACKERS] Functions with COPY

* Bruno Wolff III ([EMAIL PROTECTED]) wrote: On Thu, Nov 27, 2003 at 09:15:20 -0500, Stephen Frost [EMAIL PROTECTED] wrote: I don't believe it's possible, currently, to correctly import this data with copy. I'm not sure the date fields would even be accepted as date fields. It'd

### Re: [HACKERS] Functions with COPY

* Rod Taylor ([EMAIL PROTECTED]) wrote: How about COPY into a TEMP TABLE for 10k lines, then do an insert into real_table select from temp_table; which converts the data? You could of course thread the load so 2 or 3 processes execute the data import. Sure, this would work, but

### Re: [HACKERS] Functions with COPY

* Dave Cramer ([EMAIL PROTECTED]) wrote: There is a patch floating around for informix load/unload the syntax is load from 'file' insert into , and unload to 'file' select whatever you like Would this solve the problem? I'm not sure. It depends on what you can do with the ''

### Re: [HACKERS] Functions with COPY

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Consider the following input data: 1234,24.50,10-Jan-2003,10/1/03,10-01-2003,hiall The interpretation for the numbers is: 1234 =3D 12.34, 24.50 =3D 24.50 The interpretation for the dates

### Re: [HACKERS] Functions with COPY

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: I guess my example was lacking, I'm sure there are cases where the text-date casting will end up being wrong or some date style won't be accepted. If the above was 'January 10th, 2003, October 1st, 2003

### Re: [HACKERS] Functions with COPY

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: No, I'm interested, as I discussed in my message[1], in the ability to use functions in a copy statement to allow me to specify the conversion from text to the appropriate data type. COPY is not intended

### [HACKERS] Segfault in 7.4.1 (and 7.3.4) during vacuum analyze

Greeting, This is mainly just a heads up, I'm going to be recompiling with symbols and rerunning the test after I get to work, but I'm getting a segfault consistantly when doing a vacuum analyze. The trace is from 7.4.1 but I upgraded to 7.4.1 because the same was happening under

### Re: [HACKERS] Update on PITR

* Tom Lane ([EMAIL PROTECTED]) wrote: Is your timeline based on the assumption of doing all the work yourself? If so, how about farming out some of it? I'd be willing to contribute some effort to PITR. (It's been made clear to me that Red Hat really wants PITR in 7.5 ;-)) Hey, us Debian

### Re: [HACKERS] make == as = ?

* Josh Berkus ([EMAIL PROTECTED]) wrote: Were I teaching a class with a SQL component, using PostgreSQL as a tool, I would be very careful to avoid letting my students use an extensions to SQL; no !=, no SELECT DISTINCT ON and no alias references in the GROUP BY clause. I'd really like

### [HACKERS] MERGE Support (SQL2003)

Greetings, As mentioned in the PostgreSQL Weekly News, SQL2003 has been approved. Looking at one of the recent drafts it appears that MERGE has been added to the spec. MERGE is described as Conditionally update rows of a table, or insert new rows into a table, or both. Support for

### Re: [HACKERS] MERGE Support (SQL2003)

* Greg Stark ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: c) merge into T1 USING VALUES (1,2,4); I'm not happy with the implied use of the primary key. a) some tables can have two effective primary keys, even if only one is explicitly declared as such. and b

### Re: [HACKERS] ALTER TABLE TODO items

* Bruce Momjian ([EMAIL PROTECTED]) wrote: Do we still want this TODO? o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2]; have SELECT * and INSERT honor such ordering I don't think so. As I remember it was part of doing logical attribute numbers as

### Re: [HACKERS] PostgreSQL pre-fork speedup

* Steve Atkins ([EMAIL PROTECTED]) wrote: On Wed, May 05, 2004 at 02:12:58PM -0400, Rod Taylor wrote: Most of it has been. It's the duty cycle. As stated in another email, only about 20% of the work a script does is database related -- which occurs all at one time. Even when all Apache

### Re: [HACKERS] #postgresql report

* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: In line with my idea of keeping the hackers up to date with stuff in the IRC channel, here are the topics of the week: * We have a request for granting on all tables every other day (already in TODO) Hopefully with options to do it

### Re: [HACKERS] #postgresql report

* Jeff ([EMAIL PROTECTED]) wrote: Don't forget we get people nearly daily who are encountering problems because Debian stable ships with 7.2. We've grown accustomed to giving 7.4 (or at least 7.3) advice and often those things don't work on 7.2. (such as information_schema and set

### Re: [HACKERS] #postgresql report

* Jeff ([EMAIL PROTECTED]) wrote: True enough, however it is still a common topic on the channel. It's good to let people who are asking know there's an option though. I'm on the channel too, and do let people know when I see them asking about it but I'm in 20-odd other channels. :) Not

### Re: [HACKERS] Improving postgresql.conf

* Greg Stark ([EMAIL PROTECTED]) wrote: Tom Lane [EMAIL PROTECTED] writes: The only real problem I see is that showing all the values as comments encourages the idea that you can undo a change by undoing your edit. The simple and obvious fix is to not show the values as comments ... Well

### Re: [HACKERS] Nested Transactions, Abort All

* Alvaro Herrera ([EMAIL PROTECTED]) wrote: We could use BEGIN NESTED for starting a subtransaction, roll it back with ROLLBACK NESTED or some such, and commit with COMMIT NESTED. But I like SUBBEGIN etc best, and no one had an opinion when I asked. So the current code has SUBBEGIN,

### Re: [HACKERS] User Quota Implementation

* Jonah H. Harris ([EMAIL PROTECTED]) wrote: Out of necessity, I've implemented user quotas in 7.4.3. What would the process be for having this reviewed and combined? I have a patch for 7.4.3 ready, but wanted to know if you suggest that I patch the latest cvs instead. Below if some

### Re: [HACKERS] User Quota Implementation

* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: Personally, I would love to see this in PostgreSQL. It'd be great if it could get into 7.5. An issue I see with that is that (similar to Oracle...) I think people would want to be able to specify per-tablespace quotas. Perhaps that

### Re: [HACKERS] User Quota Implementation

* Rod Taylor ([EMAIL PROTECTED]) wrote: I would think having would allow us to take advantage of all of the various kernel level filesystem features without needing to implement them directly within PostgreSQL (crypto, quotas, data mirror, etc.). Simply setup a tablespace for a given user

### Re: [HACKERS] User Quota Implementation

* Rod Taylor ([EMAIL PROTECTED]) wrote: Simply setup a tablespace for a given user with permissions to allow only that user to create new objects within it and make it the default location) -- tie their schema to their tablespace? -- then set a kernel level quota on their tablespace.

### Re: [HACKERS] User Quota Implementation

* Rod Taylor ([EMAIL PROTECTED]) wrote: Since the user accessing/writing to the tablespaces would be the postgres user I don't really think this 'solution' works in reality. I had assumed it would be a directory based quota rather than a user based one. It's been a while

### Re: [HACKERS] User Quota Implementation

* Klaus Naumann ([EMAIL PROTECTED]) wrote: On Thu, 8 Jul 2004, Jonah H. Harris wrote: 3. The maximum quota size is (currently) the maximum of int4*1024 bytes. why is this? This is very limiting ... It's 2TB... Using a 64bit value would be a lot more straight foreward. It sounded to me

### Re: [HACKERS] User Quota Implementation

* Rod Taylor ([EMAIL PROTECTED]) wrote: Group quotas should be sufficient. Create directory readable/writable to only the pgsql user, but have the group ownership be representative of the user in question. Rather ugly, and you'll run out of groups if you have alot of users (the

### Re: [HACKERS] User Quota Implementation

* Rod Taylor ([EMAIL PROTECTED]) wrote: On Fri, 2004-07-09 at 11:47, Stephen Frost wrote: * Klaus Naumann ([EMAIL PROTECTED]) wrote: On Thu, 8 Jul 2004, Jonah H. Harris wrote: 3. The maximum quota size is (currently) the maximum of int4*1024 bytes. why is this? This is very

### Re: [HACKERS] Beta5 now Available

* Marc G. Fournier ([EMAIL PROTECTED]) wrote: On Tue, 23 Nov 2004, Peter Eisentraut wrote: The download servers have enough bandwidth to serve any client faster than the client can take. The traffic on the download servers is not reduced, only distributed differently. I don't see any

### Re: [HACKERS] Beta5 now Available

* Marc G. Fournier ([EMAIL PROTECTED]) wrote: we're not talking load issues this time ... the way I understand it, bittorrent has a 'tracker' process that only one can be running on the BT Distributed Network at once ... so, if the bt central server goes down, the whole bt network goes down

### Re: [HACKERS] Schedule for 8.1 feature freeze

* Bruce Momjian (pgman@candle.pha.pa.us) wrote: We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. Bruce, I'd really like to see role support added into 8.1. I've sent Alvaro and Tom

### Re: [HACKERS] Schedule for 8.1 feature freeze

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: I'd really like to see role support added into 8.1. I've sent Alvaro and Tom versions of the patch in the past and I was planning on submitting it to -patches soon. There's a few remaining issue but I

### Re: [HACKERS] Open items

* Alvaro Herrera ([EMAIL PROTECTED]) wrote: Additionally, my shared dependencies patch :-) I'm not sure if I'll be able to do both autovacuum and shared dependencies before freeze, but I'll try. I'm not sure if anybody is working on roles though; if I have to do the three of them I'm pretty

### Re: [HACKERS] Open items

* Tom Lane ([EMAIL PROTECTED]) wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Roles? (Stephen Frost) Additionally, my shared dependencies patch :-) I'm not sure if I'll be able to do both autovacuum and shared dependencies before freeze, but I'll try. I'm not sure if anybody

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Attached please find files and patches associated with moving from the User/Group system currently in place to Roles, as discussed previously. I have cleaned this up a bit and committed it. I normally

### Re: [HACKERS] Role syntax (or, SQL99 versus sanity)

* Tom Lane ([EMAIL PROTECTED]) wrote: The SQL99 spec has for GRANT (REVOKE has the identical issue): grant privilege statement ::= GRANT privileges TO grantee [ { comma grantee }... ] [ WITH HIERARCHY OPTION ] [

### Re: [HACKERS] initdb -W failure with role-capable catalogs

* Michael Fuhr ([EMAIL PROTECTED]) wrote: After the recent role-capable catalog commit, initdb -W fails with the following error: Whoops, sorry about that, didn't know initdb had a -W option. :) Thanks, Stephen signature.asc Description: Digital signature

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

Hi Fabien, * Fabien COELHO ([EMAIL PROTECTED]) wrote: I've looked very quickly at the patch. ISTM that the proposed patch is a reworking of the user/group stuff, which are both unified for a new role concept where a user is a kind of role and a role can be a member of another role. Well,

### Re: [HACKERS] CVS tip build failure (win32)

* Dave Page (dpage@vale-housing.co.uk) wrote: I'm seeing the following failure on win32, post roles patch application: [...] 'InitializeAcl' [...] That's following a cvs update and a make clean. All was fine before I updated :-( Wow. Apparently 'InitializeAcl' is part of the Windows API. My

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Second, looks like I missed fixing an owner check in pg_proc.c Got it. I was wondering if there were more --- might be worth checking all the superuser() calls. Yeah, let's come up with a decision about what

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: That needs a check for superuser though because while the test will pass on the 'pg_class_ownercheck' side, it won't on the 'is_role_member' side Um, right, that was another problem I had with it --- at one point

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Bruno Wolff III ([EMAIL PROTECTED]) wrote: On Tue, Jun 28, 2005 at 14:45:06 -0400, Stephen Frost [EMAIL PROTECTED] wrote: If you are the owner of the object to be changed (following the normal owner checking rules) AND would still be considered the owner of the object *after

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Bruno Wolff III ([EMAIL PROTECTED]) wrote: Thinking about it some more, drops wouldn't be an issue since the owner can always drop objects. Right. Creating objects in particular schemas or databases is not something that all roles may be able to do. Yeah, I'm not entirely sure what I

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Michael Paesold ([EMAIL PROTECTED]) wrote: Stephen Frost wrote: If you're considered the owner of an object then you have access to drop it already. You have to be a member of the role to which you're changing the ownership. That role not having permission to create the object in place

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Bruno Wolff III ([EMAIL PROTECTED]) wrote: Creating objects in particular schemas or databases is not something that all roles may be able to do. Yeah, I'm not entirely sure what I think about this issue

### Re: [HACKERS] Open items

* Bruce Momjian (pgman@candle.pha.pa.us) wrote: Here are our open items. How hard are we going to be about the cutoff date? Do we give people the weekend to complete some items? Changes --- [...] I'm not sure what else Tom's already working on wrt roles, but I plan to send in the

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Tom Lane ([EMAIL PROTECTED]) wrote: I notice that AddRoleMems/DelRoleMems assume that ADMIN OPTION is not inherited indirectly; that is it must be granted directly to you. This seems wrong; SQL99 has under privileges 19) B has the WITH ADMIN OPTION on a role if a role authorization

### Re: [HACKERS] Open items

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: ... We really should also support SET ROLE. Perhaps if I have time I'll go through the SQL spec looking at the specific requirements of 'Basic Role Support' and 'Extended Role Support' and come up with what

### Re: [HACKERS] Open items

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Here's the results of this. I think we're pretty close to having both Basic roles and Extended roles personally. For 'Basic roles' we need SET ROLE and some information schema tables. The information schema

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

Fabien, * Fabien COELHO ([EMAIL PROTECTED]) wrote: This is a very useful feature, and a key idea of the specs IMVVHO. ISTM that the way fuse user and role misses that important point, as I have not seen a set role in the grammar file. 'set role' is coming, sorry it wasn't in my initial

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

Fabien Tom (if you're watching), * Fabien COELHO ([EMAIL PROTECTED]) wrote: Role right resolution starts from the user and then works backwards up the tree, with multi-level resolution. It wouldn't go past the logged in user since that's really where it starts. ISTM that the starting

### Re: [HACKERS] Open items

* Rod Taylor ([EMAIL PROTECTED]) wrote: On Thu, 2005-06-30 at 23:02 +0900, Satoshi Nagayasu wrote: The TODO item is about counting all temporary files, not sorts in particular. Or at least that's what I thought it meant. If the DBA have to improve the performance, DBA will need to

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Tom, if you're watching, are you working on this? I can probably spend some time today on it, if that'd be helpful. I am not; I was hoping you'd deal with SET ROLE. Is it really much different from SET SESSION

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: I think one big issue is that we don't have a 'usage' database check beyond pg_hba and so any user could get the schema definitions for any database, which kind of sucks. Not unless he can connect

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: That's controlled by pg_hba.conf though, isn't it? The idea being that you'd like to give some people the ability to create users/roles, but to limit the databases those created users/roles could connect

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

Fabien, * Fabien COELHO ([EMAIL PROTECTED]) wrote: I really disagree with you here. I feel it makes much more sense to do this in stages, first user/group - roles, then roles-per-catalog, which means you can then have both per-catalog 'users' and per-catalog 'groups', if you want to limit

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

Fabien, * Fabien COELHO ([EMAIL PROTECTED]) wrote: Please outline exactly what you're really looking for. Let's drop the idea of per-cluster users/groups/roles/whatever and instead consider what specific capabilities you're looking for. I think from a conceptual point of view that the

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Bruce Momjian (pgman@candle.pha.pa.us) wrote: Stupid question, but how do roles relate to our existing groups? Uhhh. There are no longer groups, they've been replaced with roles (which can have members). Thanks, Stephen signature.asc Description: Digital signature

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Bruce Momjian (pgman@candle.pha.pa.us) wrote: Thanks, TODO updated. We still support CREATE GROUP? It translates to roles? Yes, CREATE USER too. Stephen Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Stupid question, but how do roles relate to our existing

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Robert Treat ([EMAIL PROTECTED]) wrote: On Friday 01 July 2005 13:07, Stephen Frost wrote: However On Friday 01 July 2005 13:02, Stephen Frost wrote: * Bruce Momjian (pgman@candle.pha.pa.us) wrote: Stupid question, but how do roles relate to our existing groups? Uhhh

### Re: [HACKERS] [PATCHES] Users/Groups - Roles

* Tom Lane ([EMAIL PROTECTED]) wrote: Fabien COELHO [EMAIL PROTECTED] writes: Right, this can be done now. There is the namespace collision issue, and although I might grant a student the privilege to create simple roles, I would not allow them to create new users for a basic

### [HACKERS] Must be owner to truncate?

Greetings, The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out that truncate doesn't call triggers but it seems like that would be

### Re: [HACKERS] Must be owner to truncate?

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: The current permissions checks for truncate seem to be excessive. It requires that you're the owner of the relation instead of requiring that you have delete permissions on the relation. It was pointed out

### Re: [HACKERS] Must be owner to truncate?

* Jim C. Nasby ([EMAIL PROTECTED]) wrote: On Thu, Jul 07, 2005 at 01:48:59PM -0400, Tom Lane wrote: I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree that DELETE permissions should be enough to let you do a TRUNCATE. What about adding a

### Re: [HACKERS] Must be owner to truncate?

* Andrew - Supernews ([EMAIL PROTECTED]) wrote: On 2005-07-07, Stephen Frost [EMAIL PROTECTED] wrote: * truncate is not MVCC-safe. Erm, that's why it gets a stronger lock, so I don't really see what this has to do with it. It's not MVCC-safe even with the AccessExclusive lock

### Re: [HACKERS] Must be owner to truncate?

* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: I'm strongly in favour of this patch. I am currently in this situation: 1. Web db user runs as non-superuser, non-owner. 2. I have a table of a tens of thousands of rows that I must delete entirely and rebuild every day at least (pg_trgm

### Re: [HACKERS] Must be owner to truncate?

* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: There are other reasons for restricting it: * truncate takes a much stronger lock than a plain delete does. * truncate is not MVCC-safe. I don't really agree with the viewpoint that truncate is just a quick DELETE, and so I do not agree

### Re: [HACKERS] Must be owner to truncate?

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Andrew - Supernews ([EMAIL PROTECTED]) wrote: It's not MVCC-safe even with the AccessExclusive lock; This seems like something which should probably be fixed, You've missed the point entirely: this *cannot

### Re: [HACKERS] Documentation on roles

* Alvaro Herrera ([EMAIL PROTECTED]) wrote: Who is working on providing documentation for roles? I was just going to alter the docs on users to remove the SYSID part, but I noticed there is nothing at all for roles ... _Is_ anybody working on it at all? Just to put it out there, I'm not

### Re: [HACKERS] Must be owner to truncate?

* Mike Mascari (mascarm@mascari.com) wrote: Stephen Frost wrote: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate

### Re: [HACKERS] roles question

* Joe Conway ([EMAIL PROTECTED]) wrote: After months of being unable to keep up with what's going on here, I'm trying to educate myself on some of the latest developments. I was playing with roles a bit, and I don't know if I'm doing something wrong, or if I found a hole: Things have

### Re: [HACKERS] another roles related question

* Joe Conway ([EMAIL PROTECTED]) wrote: Roles cause a problem for the information schema view table_privileges. Right. [...] Is this something we should worry about? Or do we just put a warning in the docs? I've already submitted a patch which should correct this. It also adds a new SQL

### Re: [HACKERS] another roles related question

* Joe Conway ([EMAIL PROTECTED]) wrote: Stephen Frost wrote: I've already submitted a patch which should correct this. It also adds a new SQL function which determines if a given user is in a specific role. It also implements SET ROLE, CURRENT_ROLE and SYSTEM_USER. Oh, cool. Sorry

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: After rereading SQL99 4.31, I don't think there is any need to distinguish CURRENT_USER from CURRENT_ROLE, mainly because our implementation does not distinguish users from

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Sorry about the existing applications, but this does go directly against the SQL2003 specification. The spec isn't sufficiently well-designed in this area to make me willing to insert security holes

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: What this says is that when a role A is a member of another role B, A automatically has all of B's privileges. But when a user U is a member of role R, U does *not* have R's privileges automatically. What he has is the right to do SET ROLE R, after which

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Perhaps the specification isn't but I'm pretty sure other implementations follow the SET ROLE - current authorization identifier (and thus dropping other rights granted to the CURRENT_USER). My current reading

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Perhaps the specification isn't but I'm pretty sure other implementations follow the SET ROLE - current authorization identifier (and thus dropping other rights granted to the CURRENT_USER). My current reading

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: Yeah, let's take a look. This wouldn't be the first part of the spec we've come across that is mostly honored in the breach... Oracle appears to mostly follow it, except there's an implicit 'SET ROLE all;', at least in the instance I'm looking at. I'm

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Stephen Frost ([EMAIL PROTECTED]) wrote: Doing this doesn't seem entirely unreasonable but we don't currently have a way of handling 'SET ROLE none;'. We'd need to make some changes but I think we could handle it, and correctly handle a specific 'SET ROLE role', which under Oracle does

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: To support having certain roles turned on and certain roles turned off would be some additional effort. I think we'd need a list of 'ENABLED_ROLES' and then correct recursion based off of that list instead

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Peter Eisentraut ([EMAIL PROTECTED]) wrote: Am Donnerstag, 21. Juli 2005 22:55 schrieb Tom Lane: What this says is that when a role A is a member of another role B, A automatically has all of B's privileges. But when a user U is a member of role R, U does *not* have R's privileges

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Stephen Frost ([EMAIL PROTECTED]) wrote: * Tom Lane ([EMAIL PROTECTED]) wrote: If we don't do it that way then we have a bunch of API that breaks down: all of the has_foo_privilege functions stop working, because they don't have a signature that allows both a user and a role to be passed

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: It sounds like this is essentially if 'SET ROLE all;' is allowed or not. If you disallow 'SET ROLE all;' (and therefore not do it on session start) then you would get this behaviour. I certainly see

### Re: [HACKERS] For review: Server instrumentation patch

* Tom Lane ([EMAIL PROTECTED]) wrote: I'm going to repeat my firm opposition to this patch. Under the innocuous-sounding banner of server instrumentation, you are once again trying to put in generic file access capabilities that will allow remote Postgres superusers full access to the server

### Re: [HACKERS] For review: Server instrumentation patch

* Tom Lane ([EMAIL PROTECTED]) wrote: didn't. One way that the attacker might proceed is to try to make a .so file that he can LOAD into the backend containing the equivalent of a system() function. I believe this is not feasible using COPY in its current form, mainly because you can't write

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: [ getting back to this thread... ] Happy to, was getting worried you'd forgotten or ignored it. ;) * rolinherit = false: role does not automatically have the privileges of roles it is a member of. It must do SET ROLE to gain the privileges of a role it

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: The problem I have with the spec's way is that it creates a disconnect between the privilege environment seen at the outer level and the environment seen within SECURITY DEFINER functions --- unless you want to allow SET ROLE to have the union behavior

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: My understanding of things is that per spec, a SECURITY DEFINER function can be owned by either a user or a role, and so within the function either CURRENT_USER or CURRENT_ROLE would return the owner and the other would return NULL. Emulating this would

### Re: [HACKERS] DROP GROUP leaves permissions a mess ...

* Marc G. Fournier ([EMAIL PROTECTED]) wrote: Should there not be an ERROR returned when you try and drop a user/group that has permissions on a table in the database, to prevent this? That's exactly what the shared-dependency patch that was recently applied to CVS HEAD is supposed to deal

### Re: [HACKERS] Rollback issue with SET ROLE

* Tom Lane ([EMAIL PROTECTED]) wrote: Ideally the ROLLBACK should have restored the ROLE setting that obtained prior to BEGIN. The reason it doesn't is that the ROLLBACK effectively does a SET SESSION AUTHORIZATION prior-auth-value, and that naturally clears the ROLE setting. In this case

### Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated

* Tom Lane ([EMAIL PROTECTED]) wrote: I've committed changes to add a rolinherit flag to pg_authid as per discussion. The pg_has_role function now distinguishes USAGE rights on a role (do you currently have the privileges of that role) from MEMBER rights (do you have the ability to SET ROLE

### Re: [HACKERS] Remote administration functionality

The problem is, pg_hba.conf might be editted via the OS unlike the text version of pg_shadow which is only editted via the server, which would make appropriate locking nigh-on impossible afaics. Alright, sorry to just jump in here in the middle, but I don't see why pg_hba.conf couldn't be

### Re: [HACKERS] Remote administration functionality

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Alright, sorry to just jump in here in the middle, but I don't see why pg_hba.conf couldn't be made to work just like pg_shadow (or rather, pg_authid or whatever it is now :). (1) pg_hba.conf is fundamentally

### Re: [HACKERS] Remote administration functionality

* Dave Page (dpage@vale-housing.co.uk) wrote: This isn't actually an argument against my proposal. The admin doesn't edit pg_shadow using vi because it's understood to be 'owned' by the database. The same would be true of 'pg_hba' in my solution. Only if it were moved to a different

### Re: [HACKERS] Remote administration functionality

* Dave Page (dpage@vale-housing.co.uk) wrote: Alright, sorry to just jump in here in the middle, but I don't see why pg_hba.conf couldn't be made to work just like pg_shadow (or rather, pg_authid or whatever it is now :). Because the admin doesn't edit pg_shadow using vi or some other

### Re: [HACKERS] Bug introduced by recent ALTER OWNER permissions check change

* Tom Lane ([EMAIL PROTECTED]) wrote: Considering I am superuser, it should darn well allow this. Agreed. The problem of course is the test that u1 would have the rights to create t1 in s1, which he doesn't. I think we have to skip that test if superuser. As long as we need an explicit

### Re: [HACKERS] Bug introduced by recent ALTER OWNER permissions check change

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: I don't like this approach to solving the problem. I would rather see the check modified to allow the ownership change provided: the user issueing the command has access to destination role

### Re: [HACKERS] US Census database (Tiger 2004FE)

* Mark Woodward ([EMAIL PROTECTED]) wrote: I just finished converting and loading the US census data into PostgreSQL would anyone be interested in it for testing purposes? It's a *LOT* of data (about 40+ Gig in PostgreSQL) How big dumped compressed? I may be able to host it depending on

### Re: [HACKERS] US Census database (Tiger 2004FE)

* Mark Woodward ([EMAIL PROTECTED]) wrote: How big dumped compressed? I may be able to host it depending on how big it ends up being... It's been running for about an hour now, and it is up to 3.3G. Not too bad. I had 2003 (iirc) loaded into 7.4 at one point. pg_dump tiger | gzip

### Re: [HACKERS] Bug introduced by recent ALTER OWNER permissions check change

* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Does it really? I don't think so. If you have owner privileges on the schema you can grant create rights to the role, then either ALTER OWNER if the patch is kept or just change to the role, create table x