Re: [HACKERS] XLogInsert

[Jaime Casanova]

On Thu, Dec 10, 2009 at 3:58 PM, Alvaro Herrera
 wrote:
> Jaime Casanova escribió:
>> On Wed, Dec 9, 2009 at 9:39 AM, Tom Lane  wrote:
>> > so I'd like some independent confirmation that it does.
>> >
>>
>> what kind of tests could show that? or simply running pgbench several
>> times for 15 minutes each run could show any benefit?
>
> Isn't the supposed performance improvement in this patch linked strongly
> to backup blocks?  If that's really the case, I think it would show more
> prominently if you had very frequent checkpoints.
>

Ah! Ok, i was only following the logic that it was eliminating the
need of executing a loop twice...
But you are right while the loop executes always it only do something
meaningful after a checkpoint and the for statement only make 3 loops
each, because XLR_MAX_BKP_BLOCKS is defined as 3 in
src/include/access/xlog.h

looked that way seems like the benefit could be only marginal

to prove that i compile with and without the patch, and change
checkpoint_segments = 1 and checkpoint_timeout = 1min to force
frequent checkpoints (actually they ocurred a few seconds apart)

initialize the pgbench database in each installation with:
pgbench -i -s 200 -F 90 test

and executed 6 times with:
pgbench -n -c 50 -j 5 -l -T 900 test

Results are:

Min (tps)
Unpatched - including connections establishing 133.046069 excluding it
133.085274
Patched - including connections establishing 139.567284 excluding
it 139.591229

Max (tps)
Unpatched - including connections establishing 147.082181 excluding
it  147.108752
Patched- including connections establishing 151.276416 excluding
it  151.311745

Avg (tps)
Unpatched - including connections establishing 140.750998 excluding
it  140.790336
Patched - including connections establishing 146.383735 excluding
it 146.411039

So in this extreme case avg tps is just 6 transactions better

PS: i'm attaching the files i use for the tests

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157


test_patched.sh
Description: Bourne shell script


test_unpatched.sh
Description: Bourne shell script

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Winflex

[Andrew Dunstan]

Magnus Hagander wrote:

On Sat, Dec 12, 2009 at 21:19, Andrew Dunstan  wrote:
  

Magnus Hagander wrote:


Using
http://www.postgresql.org/ftp/misc/winflex/

on a 64-bit windows, but in 32-bit mode (this certainly used to work),
trying to build HEAD.

first of all, it returns error code 128 when running "flex -V", which
means our script claims it doesn't work. Commenting out that check, it
crashes along the line of:
Running flex on src\backend\utils\misc\guc-file.l
14 [main] flex 2372 _cygtls::handle_exceptions: Exception:
STATUS_ACCESS_VIOLATION

Sometimes it doesn't crash, but do this instead:
Running flex on src\backend\parser\scan.l
c:\gnuwin32\bin\m4.exe:stdin:16769: ERROR: end of file in string
Project : error PRJ0002 : Error result 128 returned from
'C:\WINDOWS\SysWow64\cm
d.exe'.


If I run it a couple of times, it eventually completes. But then bombs
out because the generated files aren't complete.

Anybody else seen this? Am I forgetting something typical?

  

Hmm.

I don't have a 64bit Windows box, so I doubt I can test this. I can set up a
64bit VM but I'd need to get my hands on a 64bit version of Windows.



I use Amazon EC2 for this. Trivial to get up, and almost free when you
use it for very short periods of time.
  


Yes. I spent a few cents and a few hours wrestling with it. AFAICT your 
are hosed on 64bit Windows. I can't get flex built and Cygwin is 
behaving very oddly. There are indications that the problem could be 
fairly deep - see 



I can try again with Cygwin 1.7. and see if that improves matters, but I 
bet it doesn't.



cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Largeobject Access Controls and pg_migrator

[KaiGai Kohei]

(2009/12/13 11:31), Bruce Momjian wrote:

Bruce Momjian wrote:

KaiGai Kohei wrote:

lo_import() has an another prototype which takes second argument to
specify LOID. Isn't it available to restore a large object with
correct LOID? For example, lo_import('/etc/profile', 1234)


I can't use that because the migration has already brought over the
pg_largeobject file which has the data.


Or, if you intend to restore metadata in the second lo_import(),
ALTER LAEGE OBJECT and GRANT LARGE OBJECT enable to set up metadata
of a certain large object.


Yes, that will work cleanly.  The file might be large because I need a
GRANT for every large object, but I suppose that is OK.


Uh, I tested pg_migrator and found a problem with this approach:

test=>  select loid from pg_largeobject;
 loid
---
 16385
 16385
 16386
(3 rows)

test=>  grant all ON LARGE OBJECT 16385 to public;
ERROR:  large object 16385 does not exist

I am wondering if the missing pg_largeobject_metadata row is causing
this, and again I have no way of creating one with the specified oid.


Can SELECT lo_create(16385); help this situation?

It create an entry into pg_largeobject_metadata, but does not touch
pg_largeobject because it is empty in the initial state.
But, in this case, pg_migrator already bring only data chunks to
pg_largeobject. So, this operation enables to recombine orphan chunks
and a metadata entry.

I'm not clear whether we also check pg_largeobejct has chunks with same
LOID on lo_create(). In the regular operation, it shall never happen.

--
KaiGai Kohei 

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Row-Level Security

[Robert Haas]

On Sat, Dec 12, 2009 at 7:41 PM, Josh Berkus  wrote:
> Stephen,
>
>> Please comment, update the wiki, let us know you're interested in this..
>
> I blogged about this some time ago.  One issue I can see is that I
> believe that the RLS which many users want is different from the RLS
> which SEPostgres implements.
>
> Links:
>
> http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-1-30732
> http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-2-30757
>
> --Josh Berkus

I read these blog entries a while ago but had forgotten about them.
They're very good, and summarize a lot of my thinking on this topic as
well.  I think that we can design a framework for row-level security
which can encompass both constraint-based security and label-based
security.  Both seem to me to be be based around doing essentially the
following things:

1. Adding columns to the table to store access control information.
2. Populating those columns with additional information (owner, ACL,
security label, etc.) which can be used to make access control
decisions.
3. Injecting logic into incoming queries which uses the information
inserted by (1) to filter out rows to which the access control policy
does not wish to allow access.

Waving my hands in the air, #1 and #2 seem pretty straightforward.
For constraint-based security, one can imagine just adding a column to
the table and then adding a BEFORE INSERT OR UPDATE FOR EACH ROW
trigger that populates that column.  For label-based MAC, that's not
going to be quite sufficient, because the system needs to ensure that
the trigger that populates the security column must run last; if it
doesn't, some other trigger can come along afterwards and slip in a
value that isn't supposed to be there; plus, it might be inconvenient
to need to define this trigger for every table that needs RLS.

However, those problems don't seem insurmountable.  Suppose we provide
a hook function that essentially acts like a global BEFORE INSERT OR
UPDATE trigger but which fires after all of the regular triggers.
SE-PostgreSQL can gain control at that point and search through the
columns of the target relation for a column called, say,
sepg_security_label.  If it finds such a column and that column is of
the appropriate type, then (1) if an explicit security label is
provided, it checks whether the specified label is permissible, (2)
otherwise, if the operation is insert, it determines the appropriate
default label for the current security context and inserts it, (3)
otherwise, it just leaves the current label alone.  This might not be
quite the right behavior but the point is whatever behavior you want
to have in terms of assigning/disallowing values for that column
should be possible to implement here.  The upshot is that if the
system administrator creates an sepg_security_label column of the
correct type, row-level security will be enabled for that table.
Otherwise, it will not.

#3 seems a little bit trickier.  I don't think the GRANT ... WHERE
syntax is going to be very easy to use.  For constraint-based
row-security, I think we should have something more like:

ALTER TABLE table ADD ROW FILTER filtername USING othertable [, ...]
WHERE where-clause

(This suffers from the same problem as DELETE ... USING, namely that
sometimes you want an outer join between table and othertable.)

This gives the user a convenient way to insert a join against one or
more side tables if they are so inclined.

For security frameworks like SE-PostgreSQL, we might just provide a
hook allowing the incoming query tree to be modified, and let the hook
function check whether each table in the query has row-level security
enabled, and if so perform a modification equivalent to the above.

None of this addresses the issue of doing RLS on system catalogs,
which seems like a much harder problem, possibly one that we should
just ignore for the first phase of this project.

Thoughts?

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Largeobject Access Controls and pg_migrator

[Bruce Momjian]

Bruce Momjian wrote:
> KaiGai Kohei wrote:
> > lo_import() has an another prototype which takes second argument to
> > specify LOID. Isn't it available to restore a large object with
> > correct LOID? For example, lo_import('/etc/profile', 1234)
> 
> I can't use that because the migration has already brought over the
> pg_largeobject file which has the data.
> 
> > Or, if you intend to restore metadata in the second lo_import(),
> > ALTER LAEGE OBJECT and GRANT LARGE OBJECT enable to set up metadata
> > of a certain large object.
> 
> Yes, that will work cleanly.  The file might be large because I need a
> GRANT for every large object, but I suppose that is OK.

Uh, I tested pg_migrator and found a problem with this approach:

test=> select loid from pg_largeobject;
 loid
---
 16385
 16385
 16386
(3 rows)

test=> grant all ON LARGE OBJECT 16385 to public;
ERROR:  large object 16385 does not exist

I am wondering if the missing pg_largeobject_metadata row is causing
this, and again I have no way of creating one with the specified oid.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Largeobject Access Controls and pg_migrator

[Bruce Momjian]

KaiGai Kohei wrote:
> lo_import() has an another prototype which takes second argument to
> specify LOID. Isn't it available to restore a large object with
> correct LOID? For example, lo_import('/etc/profile', 1234)

I can't use that because the migration has already brought over the
pg_largeobject file which has the data.

> Or, if you intend to restore metadata in the second lo_import(),
> ALTER LAEGE OBJECT and GRANT LARGE OBJECT enable to set up metadata
> of a certain large object.

Yes, that will work cleanly.  The file might be large because I need a
GRANT for every large object, but I suppose that is OK.

> > You might remember that INSERT cannot set the oid of a row, so I don't
> > see how pg_migrator can migrate this?  Is there a reason we used 'oid'
> > in pg_largeobject_metadata but 'lobj' in pg_largeobject?  Why did't we
> > add the lomowner and lomacl columns to pg_largeobject?
> 
> A large object consists of multiple tuples within pg_largeobject.
> If we added lomowner and lomacl into pg_largeobject, we have to manage
> all the pages in a large object to keep consistent state.

Ah, good point.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Largeobject Access Controls and pg_migrator

[KaiGai Kohei]

(2009/12/13 10:39), Bruce Momjian wrote:

Bruce Momjian wrote:

KaiGai Kohei wrote:

What happens when
there is no entry in pg_largeobject_metadata for a specific row?


In this case, these rows become orphan.
So, I think we need to create an empty large object with same LOID on
pg_migrator. It makes an entry on pg_largeobject_metadata without
writing anything to the pg_largeobject.
I guess rest of migrations are not difference. Correct?


Agreed.  I have modified pg_migrator with the attached patch which
creates a script that adds default permissions for all large object
tables.


Oops, seem like I have a problem in getting pg_migrator to populate
pg_largeobject_metadata:

test=>  select lo_import('/etc/profile');
 lo_import
---
 16385
(1 row)

test=>  select lo_import('/etc/profile.env');
 lo_import
---
 16386
(1 row)

test=>  select oid,* from pg_largeobject_metadata;
  oid  | lomowner | lomacl
---+--+
 16385 |   10 |
 16386 |   10 |
(2 rows)


lo_import() has an another prototype which takes second argument to
specify LOID. Isn't it available to restore a large object with
correct LOID? For example, lo_import('/etc/profile', 1234)

Or, if you intend to restore metadata in the second lo_import(),
ALTER LAEGE OBJECT and GRANT LARGE OBJECT enable to set up metadata
of a certain large object.

Or, am I missing the problem?


You might remember that INSERT cannot set the oid of a row, so I don't
see how pg_migrator can migrate this?  Is there a reason we used 'oid'
in pg_largeobject_metadata but 'lobj' in pg_largeobject?  Why did't we
add the lomowner and lomacl columns to pg_largeobject?


A large object consists of multiple tuples within pg_largeobject.
If we added lomowner and lomacl into pg_largeobject, we have to manage
all the pages in a large object to keep consistent state.

--
KaiGai Kohei 

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Largeobject Access Controls and pg_migrator

[Bruce Momjian]

Bruce Momjian wrote:
> KaiGai Kohei wrote:
> > > What happens when
> > > there is no entry in pg_largeobject_metadata for a specific row?
> > 
> > In this case, these rows become orphan.
> > So, I think we need to create an empty large object with same LOID on
> > pg_migrator. It makes an entry on pg_largeobject_metadata without
> > writing anything to the pg_largeobject.
> > I guess rest of migrations are not difference. Correct?
> 
> Agreed.  I have modified pg_migrator with the attached patch which
> creates a script that adds default permissions for all large object
> tables.

Oops, seem like I have a problem in getting pg_migrator to populate
pg_largeobject_metadata:

test=> select lo_import('/etc/profile');
 lo_import
---
 16385
(1 row)

test=> select lo_import('/etc/profile.env');
 lo_import
---
 16386
(1 row)

test=> select oid,* from pg_largeobject_metadata;
  oid  | lomowner | lomacl
---+--+
 16385 |   10 |
 16386 |   10 |
(2 rows)

You might remember that INSERT cannot set the oid of a row, so I don't
see how pg_migrator can migrate this?  Is there a reason we used 'oid'
in pg_largeobject_metadata but 'lobj' in pg_largeobject?  Why did't we
add the lomowner and lomacl columns to pg_largeobject?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Largeobject Access Controls (r2460)

[Bruce Momjian]

KaiGai Kohei wrote:
> > What happens when
> > there is no entry in pg_largeobject_metadata for a specific row?
> 
> In this case, these rows become orphan.
> So, I think we need to create an empty large object with same LOID on
> pg_migrator. It makes an entry on pg_largeobject_metadata without
> writing anything to the pg_largeobject.
> I guess rest of migrations are not difference. Correct?

Agreed.  I have modified pg_migrator with the attached patch which
creates a script that adds default permissions for all large object
tables.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
? tools
? log
? src/pg_migrator
Index: src/info.c
===
RCS file: /cvsroot/pg-migrator/pg_migrator/src/info.c,v
retrieving revision 1.25
diff -c -r1.25 info.c
*** src/info.c	10 Dec 2009 23:14:25 -	1.25
--- src/info.c	13 Dec 2009 01:17:37 -
***
*** 480,486 
  		"SELECT DISTINCT probin "
  		"FROM	pg_catalog.pg_proc "
  		"WHERE	prolang = 13 /* C */ AND "
! 		"		probin IS NOT NULL");
  		totaltups += PQntuples(ress[dbnum]);
  
  		PQfinish(conn);
--- 480,488 
  		"SELECT DISTINCT probin "
  		"FROM	pg_catalog.pg_proc "
  		"WHERE	prolang = 13 /* C */ AND "
! 		"		probin IS NOT NULL AND "
! 		"		oid >= "
! 		STRINGIFY(FirstNormalObjectId) ";");
  		totaltups += PQntuples(ress[dbnum]);
  
  		PQfinish(conn);
Index: src/pg_migrator.c
===
RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.c,v
retrieving revision 1.69
diff -c -r1.69 pg_migrator.c
*** src/pg_migrator.c	10 Dec 2009 14:34:19 -	1.69
--- src/pg_migrator.c	13 Dec 2009 01:17:37 -
***
*** 92,97 
--- 92,100 
  			sequence_script_file_name =
  v8_3_create_sequence_script(&ctx, CLUSTER_OLD);
  	}
+ 	if (GET_MAJOR_VERSION(ctx.old.pg_version) <= 804 &&
+ 		GET_MAJOR_VERSION(ctx.new.pg_version) >= 805)
+ 		v8_4_populate_pg_largeobject_metadata(&ctx, true, CLUSTER_OLD);
  
  	/* Looks okay so far.  Prepare the pg_dump output */
  	generate_old_dump(&ctx);
***
*** 294,299 
--- 297,309 
  		v8_3_invalidate_bpchar_pattern_ops_indexes(&ctx, false, CLUSTER_NEW);
  		stop_postmaster(&ctx, false, true);
  	}
+ 	if (GET_MAJOR_VERSION(ctx.old.pg_version) <= 804 &&
+ 		GET_MAJOR_VERSION(ctx.new.pg_version) >= 805)
+ 	{
+ 		start_postmaster(&ctx, CLUSTER_NEW, true);
+ 		v8_4_populate_pg_largeobject_metadata(&ctx, false, CLUSTER_NEW);
+ 		stop_postmaster(&ctx, false, true);
+ 	}
  	
  	pg_log(&ctx, PG_REPORT, "\n*Upgrade complete*\n");
  
***
*** 416,422 
  	char		new_clog_path[MAXPGPATH];
  
  	/* copy old commit logs to new data dir */
! 	prep_status(ctx, "Deleting old commit clogs");
  
  	snprintf(old_clog_path, sizeof(old_clog_path), "%s/pg_clog", ctx->old.pgdata);
  	snprintf(new_clog_path, sizeof(new_clog_path), "%s/pg_clog", ctx->new.pgdata);
--- 426,432 
  	char		new_clog_path[MAXPGPATH];
  
  	/* copy old commit logs to new data dir */
! 	prep_status(ctx, "Deleting new commit clogs");
  
  	snprintf(old_clog_path, sizeof(old_clog_path), "%s/pg_clog", ctx->old.pgdata);
  	snprintf(new_clog_path, sizeof(new_clog_path), "%s/pg_clog", ctx->new.pgdata);
***
*** 424,430 
  		pg_log(ctx, PG_FATAL, "Unable to delete directory %s\n", new_clog_path);
  	check_ok(ctx);
  
! 	prep_status(ctx, "Copying commit clogs");
  	/* libpgport's copydir() doesn't work in FRONTEND code */
  #ifndef WIN32
  	exec_prog(ctx, true, SYSTEMQUOTE "%s \"%s\" \"%s\"" SYSTEMQUOTE,
--- 434,440 
  		pg_log(ctx, PG_FATAL, "Unable to delete directory %s\n", new_clog_path);
  	check_ok(ctx);
  
! 	prep_status(ctx, "Copying old commit clogs to new server");
  	/* libpgport's copydir() doesn't work in FRONTEND code */
  #ifndef WIN32
  	exec_prog(ctx, true, SYSTEMQUOTE "%s \"%s\" \"%s\"" SYSTEMQUOTE,
Index: src/pg_migrator.h
===
RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.h,v
retrieving revision 1.75
diff -c -r1.75 pg_migrator.h
*** src/pg_migrator.h	12 Dec 2009 16:56:23 -	1.75
--- src/pg_migrator.h	13 Dec 2009 01:17:37 -
***
*** 395,400 
--- 395,402 
  			bool check_mode, Cluster whichCluster);
  void		v8_3_invalidate_bpchar_pattern_ops_indexes(migratorContext *ctx,
  			bool check_mode, Cluster whichCluster);
+ void		v8_4_populate_pg_largeobject_metadata(migratorContext *ctx,
+ 			bool check_mode, Cluster whichCluster);
  char 		*v8_3_create_sequence_script(migratorContext *ctx,
  			Cluster whichCluster);
  void		check_for_composite_types(migratorContext *ctx,
Index: src/version.c
===

Re: [HACKERS] Row-Level Security

[Josh Berkus]

Stephen,

> Please comment, update the wiki, let us know you're interested in this.. 

I blogged about this some time ago.  One issue I can see is that I
believe that the RLS which many users want is different from the RLS
which SEPostgres implements.

Links:

http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-1-30732
http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-2-30757

--Josh Berkus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Row-Level Security

[Stephen Frost]

KaiGai,

* KaiGai Kohei (kai...@kaigai.gr.jp) wrote:
> > Please comment, update the wiki, let us know you're interested in this..
> 
> Good start, however, could you defer the discussion after the Feb-15?
> My hands are now full in the security framework and SE-PgSQL/Lite. :(

While I'm glad you're enthusiastic and interested in this too, I don't
believe we need to delay this initial discussion.  To be honest, I think
we really need to get some input and interest from others as well.  I'll
do my best to make sure the wiki is updated with information and links
to any signifigant threads on the lists.  I don't expect to be writing
any serious code by Feb 15th on this anyway.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] XML schemas and PG column names

[Andrew Dunstan]

Peter Eisentraut wrote:

On lör, 2009-12-12 at 11:51 -0500, Andrew Dunstan wrote:
  
It is certainly legal per XML and XSD specs, and the SQL/XML spec has 
annotations using appinfo elements. It would be rather surprising if
the 
SQL/XML spec forbade annotations such as I propose. The spec is 
mind-bogglingly impenetrable, though. Perhaps Peter or Nicholas might

know.



I think we can of course add our own annotations.  It would be good to
go through the SQL/XML standard document and check what style they use
for their annotations so that we can structure and name ours similarly
and have room for future work, in case someone also wants annotations
for table names, schema names, etc. (Or was that part of your project as
well?)

  


Well, the standard has an element specifically for annotations 
concerning certain objects: sqlxml:sqlname. However, I am not sure if it 
can be used in this context. Can you try reading the standard 
() and 
tell me? :-) If it's not comprehended by the standard then we should at 
least use a different namespace, and probably a different element name. 
The style can be made to match, though.


I certainly think we could do more of this, although the column names 
are what matter to me right now. We can also do it bit by bit.


cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Row-Level Security

[KaiGai Kohei]

(2009/12/13 5:30), Stephen Frost wrote:
> Greetings,
> 
>> I'll start a new thread on this specific topic to hopefully pull out
>> anyone who's focus is more on that than on SEPG.
> 
> Row-Level security has been implemented in a number of existing
> commercial databases.  There exists an implementation of row-level
> security for PostgreSQL today in the form of SEPostgres.
> I believe there is a signfigant user base who would like RLS without
> SELinux (or perhaps with some other security manager).  As it is a
> useful feature indepenent of SELinux, it should be implemented in a way
> which doesn't depend on SELinux in any way.

Yes, it is also my plan.
If once PostgreSQL gets row-level granularity in access controls,
it is quite easy to add SELinux support as a security provider.


> I've started a wiki page to discuss this here:
> http://wiki.postgresql.org/wiki/RLS
> 
> I'd like to start a discussion about RLS for PG- design, user-interface,
> syntax, capabilities, on-disk format changes, etc.  For starters, I
> think we shoud review the existing RLS implementations.  To that end,
> I've added a number of articles about them to the wiki.  I think the
> next step is to start summarizing how those operate and important
> similarities and differences between them.  Our goal, of course, is to
> take the best of what's out there.
> 
> Please comment, update the wiki, let us know you're interested in this..

Good start, however, could you defer the discussion after the Feb-15?
My hands are now full in the security framework and SE-PgSQL/Lite. :(

Thanks,
-- 
KaiGai Kohei 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] XML schemas and PG column names

[Peter Eisentraut]

On lör, 2009-12-12 at 11:51 -0500, Andrew Dunstan wrote:
> It is certainly legal per XML and XSD specs, and the SQL/XML spec has 
> annotations using appinfo elements. It would be rather surprising if
> the 
> SQL/XML spec forbade annotations such as I propose. The spec is 
> mind-bogglingly impenetrable, though. Perhaps Peter or Nicholas might
> know.

I think we can of course add our own annotations.  It would be good to
go through the SQL/XML standard document and check what style they use
for their annotations so that we can structure and name ours similarly
and have room for future work, in case someone also wants annotations
for table names, schema names, etc. (Or was that part of your project as
well?)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] LDAP where DN does not include UID attribute

[Magnus Hagander]

On Sat, Dec 12, 2009 at 12:24, Magnus Hagander  wrote:
> On Sat, Dec 12, 2009 at 02:41, Robert Haas  wrote:
>> On Sun, Dec 6, 2009 at 11:29 PM, Greg Smith  wrote:
>>> Magnus Hagander wrote:
>>>
>>> On Sun, Nov 29, 2009 at 13:05, Magnus Hagander  wrote:
>>>
>>>
>>> I'll be happy to work on this to get it ready for commit, or do you
>>> want to do the updates?
>>>
>>>
>>> Here's a patch with my work so far. Major points missing is the
>>> sanitizing of the username and the docs.
>>>
>>>
>>> It looks like you did an initial review here already.  Since we haven't
>>> heard from Robert in a while and you seem interested in the patch, I just
>>> updated this one to list you as the committer and marked it "ready for
>>> committer".  You can commit it or bounce it from there, but it's obvious
>>> none of our other reviewers are going to be able to do anything with it.
>>
>> I think we should mark this returned with feedback, since it sounds
>> like there are still open items.
>
> I plan to clean up those open item smyself since there has been no
> further feedback from the OP. Hopefully in time before the CF ends. So
> please leave it for a few days more :-)

I have applied the attached, rather heavily reworked, patch. It also
includes the required documentation.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


ldap.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication and non-blocking I/O

[Heikki Linnakangas]

Tom Lane wrote:
> Heikki Linnakangas  writes:
>> Changing the finish_time argument to pqWaitTimed into timeout_ms changes
>> the behavior connect_timeout option to PQconnectdb. It should wait for
>> max connect_timeout seconds in total, but now it is waiting for
>> connect_timeout seconds at each step in the connection process: opening
>> a socket, authenticating etc.
> 
> Refresh my memory as to why this patch is touching any of that code at
> all?

Walreceiver wants to wait for data to arrive from the master or a
signal. PQgetXLogData(), which is the libpq function to read a piece of
WAL, takes a timeout argument to support that. Walreceiver calls
PQgetXLogData() in an endless loop, checking for a received sighup or
death of postmaster at every iteration.

In the synchronous replication mode, I presume it's also going to listen
for a signal from the startup process, so that it can send a
acknowledgment to the master as soon as a COMMIT record has been
replayed that a backend on the master is waiting for.

To implement the timeout in PQgetXLogData(), pqWaitTimed() was changed
to take a timeout instead of finishing_time argument. Which is a mistake
because it breaks PQconnectdb, and as I said I don't think
PQgetXLogData(9 should have a timeout argument to begin with. Instead,
it should have a boolean 'async' argument to return immediately if
there's no data, and walreceiver main loop should call poll()/select()
to wait. Ie. just like PQgetCopyData() works.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Row-Level Security

[Stephen Frost]

Greetings,

> I'll start a new thread on this specific topic to hopefully pull out
> anyone who's focus is more on that than on SEPG.

Row-Level security has been implemented in a number of existing
commercial databases.  There exists an implementation of row-level
security for PostgreSQL today in the form of SEPostgres.
I believe there is a signfigant user base who would like RLS without
SELinux (or perhaps with some other security manager).  As it is a
useful feature indepenent of SELinux, it should be implemented in a way
which doesn't depend on SELinux in any way.

I've started a wiki page to discuss this here:
http://wiki.postgresql.org/wiki/RLS

I'd like to start a discussion about RLS for PG- design, user-interface,
syntax, capabilities, on-disk format changes, etc.  For starters, I
think we shoud review the existing RLS implementations.  To that end,
I've added a number of articles about them to the wiki.  I think the
next step is to start summarizing how those operate and important
similarities and differences between them.  Our goal, of course, is to
take the best of what's out there.

Please comment, update the wiki, let us know you're interested in this.. 

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Winflex

[Magnus Hagander]

On Sat, Dec 12, 2009 at 21:19, Andrew Dunstan  wrote:
>
>
> Magnus Hagander wrote:
>>
>> Using
>> http://www.postgresql.org/ftp/misc/winflex/
>>
>> on a 64-bit windows, but in 32-bit mode (this certainly used to work),
>> trying to build HEAD.
>>
>> first of all, it returns error code 128 when running "flex -V", which
>> means our script claims it doesn't work. Commenting out that check, it
>> crashes along the line of:
>> Running flex on src\backend\utils\misc\guc-file.l
>>     14 [main] flex 2372 _cygtls::handle_exceptions: Exception:
>> STATUS_ACCESS_VIOLATION
>>
>> Sometimes it doesn't crash, but do this instead:
>> Running flex on src\backend\parser\scan.l
>> c:\gnuwin32\bin\m4.exe:stdin:16769: ERROR: end of file in string
>> Project : error PRJ0002 : Error result 128 returned from
>> 'C:\WINDOWS\SysWow64\cm
>> d.exe'.
>>
>>
>> If I run it a couple of times, it eventually completes. But then bombs
>> out because the generated files aren't complete.
>>
>> Anybody else seen this? Am I forgetting something typical?
>>
>
> Hmm.
>
> I don't have a 64bit Windows box, so I doubt I can test this. I can set up a
> 64bit VM but I'd need to get my hands on a 64bit version of Windows.

I use Amazon EC2 for this. Trivial to get up, and almost free when you
use it for very short periods of time.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Winflex

[Andrew Dunstan]

Magnus Hagander wrote:

Using
http://www.postgresql.org/ftp/misc/winflex/

on a 64-bit windows, but in 32-bit mode (this certainly used to work),
trying to build HEAD.

first of all, it returns error code 128 when running "flex -V", which
means our script claims it doesn't work. Commenting out that check, it
crashes along the line of:
Running flex on src\backend\utils\misc\guc-file.l
 14 [main] flex 2372 _cygtls::handle_exceptions: Exception:
STATUS_ACCESS_VIOLATION

Sometimes it doesn't crash, but do this instead:
Running flex on src\backend\parser\scan.l
c:\gnuwin32\bin\m4.exe:stdin:16769: ERROR: end of file in string
Project : error PRJ0002 : Error result 128 returned from 'C:\WINDOWS\SysWow64\cm
d.exe'.


If I run it a couple of times, it eventually completes. But then bombs
out because the generated files aren't complete.

Anybody else seen this? Am I forgetting something typical?
  


Hmm.

I don't have a 64bit Windows box, so I doubt I can test this. I can set 
up a 64bit VM but I'd need to get my hands on a 64bit version of Windows.


cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] XML schemas and PG column names

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan  writes:
  

Tom Lane wrote:


2. What happens when the column name contains characters that would have
to be escaped, such as "<" --- haven't you just replaced one de-escaping
problem with another?
  


  
But the difference is that the XML processor will automatically unescape 
this value (and re-escape it on output if necessary). The user won't 
have to do anything (or shouldn't if their XML processor is worth 
anything at all).



OK, so your argument is that this is a standard escaping rule and the
one in the SQL standard is, um, not standard.  I wonder why the SQL
committee felt compelled to invent their own, then?


  


They are two different things. An XML-escaped text value is by no means 
necessarily a legal XML tag name (e.g. an XML name can't have spaces). 
Possibly what they really did wrong was to try to map SQL column names 
to XML tags at all. It might have been better to do something like:


   some value

instead of what we produce, which I assume is in the standard:

   somevalue

which I think is just plain ugly. OTOH, then it would have been far 
harder (maybe impossible) to create an XML schema for such a mechanism, 
so I assume that's why they did it this way.


Anyway, It would be nice to have a way of providing the non-mangled 
names - I think what I have suggested should meet the case.


cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding support for SE-Linux security

[Stephen Frost]

* Stephen Frost (sfr...@snowman.net) wrote:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > I assume he's talking about the object reference representation used in
> > pg_depend, which is actually class OID + object OID + sub-object ID.
> > The only object type that has sub-objects at the moment is tables,
> > wherein the sub-objects are columns and the sub-object IDs are column
> > numbers.  The sub-object ID is zero for all other cases.
> 
> You're right, of course, but for some reason I thought that there was
> another usage of it besides just the table/column case.

Ah, I realize what I was thinking about now..  The dependency system has
two flavors (pg_depend and pg_shdepend).  We had SubIDs for columns in
pg_depend but not pg_shdepend- until column-level privs were added which
meant we could have roles depend on columns (due to privileges on that
column).  To clarify- pg_depend is for database dependencies while
pg_shdepend is for cluster dependencies.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] XML schemas and PG column names

[Tom Lane]

Andrew Dunstan  writes:
> Tom Lane wrote:
>> 2. What happens when the column name contains characters that would have
>> to be escaped, such as "<" --- haven't you just replaced one de-escaping
>> problem with another?

> But the difference is that the XML processor will automatically unescape 
> this value (and re-escape it on output if necessary). The user won't 
> have to do anything (or shouldn't if their XML processor is worth 
> anything at all).

OK, so your argument is that this is a standard escaping rule and the
one in the SQL standard is, um, not standard.  I wonder why the SQL
committee felt compelled to invent their own, then?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding support for SE-Linux security

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Robert Haas  writes:
> > What exactly do you mean by a SubOID?  I'm not really following that part.
> 
> I assume he's talking about the object reference representation used in
> pg_depend, which is actually class OID + object OID + sub-object ID.
> The only object type that has sub-objects at the moment is tables,
> wherein the sub-objects are columns and the sub-object IDs are column
> numbers.  The sub-object ID is zero for all other cases.

You're right, of course, but for some reason I thought that there was
another usage of it besides just the table/column case.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Adding support for SE-Linux security

[Stephen Frost]

* Robert Haas (robertmh...@gmail.com) wrote:
> > Allow me to assist- y is never in a structure once you're out of the
> > parser:
> 
> Well this is why you're writing the patch and not me.  :-)

Sure, just trying to explain why your suggestion isn't quite the
direction that probably makes the most sense..  At least for args which
come from the parser.

> What exactly do you mean by a SubOID?  I'm not really following that part.

Sorry, it's basically things like attnum, check the dependency code for
example usage.  It's "this is something we need to track due to
dependencies but it's at a level below OID", or perhaps "a component of
an object which has an OID"- specifically: a column of a table.  The
table has an OID, but the column does not.  To uniquely identify the
column you need both the OID and the 'SubOID'/attnum.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] BUG #5237: strange int->bit and bit->int conversions

[Roman Kononov]

The bitfromint8() and bitfromint4() are hosed. They produce wrong 
results when the BIT size is more than 64 and 32 respectively, and the 
BIT size is not multiple of 8, and the most significant byte of the 
integer value is not 0x00 or 0xff.


For example:

test=# select (11::int4<<23 | 11::int4)::bit(32);
 010110001011

test=# select (11::int4<<23 | 11::int4)::bit(33);
 0101110001011

test=# select (11::int4<<23 | 11::int4)::bit(39);
 010110110001011

test=# select (11::int4<<23 | 11::int4)::bit(40);
 010110001011

The ::bit(33) and ::bit(39) conversions are wrong.

The patch re-implements the conversion functions.
diff -urp -x '*.o' -x '*.txt' -x '*.so' postgresql-8.4.1-orig/src/backend/utils/adt/varbit.c postgresql-8.4.1/src/backend/utils/adt/varbit.c
--- postgresql-8.4.1-orig/src/backend/utils/adt/varbit.c	2009-12-12 09:19:13.0 -0600
+++ postgresql-8.4.1/src/backend/utils/adt/varbit.c	2009-12-12 10:29:59.0 -0600
@@ -1321,8 +1321,8 @@ bitfromint4(PG_FUNCTION_ARGS)
 	VarBit	   *result;
 	bits8	   *r;
 	int			rlen;
-	int			destbitsleft,
-srcbitsleft;
+	int const	srcbits=sizeof(a)*BITS_PER_BYTE;
+	int i;
 
 	if (typmod <= 0)
 		typmod = 1;/* default bit length */
@@ -1333,32 +1333,21 @@ bitfromint4(PG_FUNCTION_ARGS)
 	VARBITLEN(result) = typmod;
 
 	r = VARBITS(result);
-	destbitsleft = typmod;
-	srcbitsleft = 32;
-	/* drop any input bits that don't fit */
-	srcbitsleft = Min(srcbitsleft, destbitsleft);
-	/* sign-fill any excess bytes in output */
-	while (destbitsleft >= srcbitsleft + 8)
-	{
-		*r++ = (bits8) ((a < 0) ? BITMASK : 0);
-		destbitsleft -= 8;
-	}
-	/* store first fractional byte */
-	if (destbitsleft > srcbitsleft)
-	{
-		*r++ = (bits8) ((a >> (srcbitsleft - 8)) & BITMASK);
-		destbitsleft -= 8;
-	}
-	/* Now srcbitsleft and destbitsleft are the same, need not track both */
-	/* store whole bytes */
-	while (destbitsleft >= 8)
-	{
-		*r++ = (bits8) ((a >> (destbitsleft - 8)) & BITMASK);
-		destbitsleft -= 8;
+	rlen=(typmod+BITS_PER_BYTE-1)/BITS_PER_BYTE;
+
+	if (srcbits>=typmod) {
+		a<<=srcbits-typmod;
+for (i=0; i!=rlen; ++i, a<<=BITS_PER_BYTE) r[i]=a>>(srcbits-BITS_PER_BYTE);
+	} else {
+		int sh=typmod%BITS_PER_BYTE;
+		int32 h=a>>sh;
+		int32 l=a<<(srcbits-sh);
+		size_t const zsize=rlen-sizeof(a)-(sh!=0);
+		bits8 sx=(a>=0)-1;
+		memset(r,sx,zsize);
+for (i=0; i!=sizeof(a); ++i, h<<=8) r[zsize+i]=h>>(srcbits-BITS_PER_BYTE);
+		if (sh!=0) r[zsize+sizeof(a)]=l>>(srcbits-BITS_PER_BYTE);
 	}
-	/* store last fractional byte */
-	if (destbitsleft > 0)
-		*r = (bits8) ((a << (8 - destbitsleft)) & BITMASK);
 
 	PG_RETURN_VARBIT_P(result);
 }
@@ -1396,8 +1385,8 @@ bitfromint8(PG_FUNCTION_ARGS)
 	VarBit	   *result;
 	bits8	   *r;
 	int			rlen;
-	int			destbitsleft,
-srcbitsleft;
+	int const	srcbits=sizeof(a)*BITS_PER_BYTE;
+	int			i;
 
 	if (typmod <= 0)
 		typmod = 1;/* default bit length */
@@ -1408,36 +1397,21 @@ bitfromint8(PG_FUNCTION_ARGS)
 	VARBITLEN(result) = typmod;
 
 	r = VARBITS(result);
-	destbitsleft = typmod;
-#ifndef INT64_IS_BUSTED
-	srcbitsleft = 64;
-#else
-	srcbitsleft = 32;			/* don't try to shift more than 32 */
-#endif
-	/* drop any input bits that don't fit */
-	srcbitsleft = Min(srcbitsleft, destbitsleft);
-	/* sign-fill any excess bytes in output */
-	while (destbitsleft >= srcbitsleft + 8)
-	{
-		*r++ = (bits8) ((a < 0) ? BITMASK : 0);
-		destbitsleft -= 8;
-	}
-	/* store first fractional byte */
-	if (destbitsleft > srcbitsleft)
-	{
-		*r++ = (bits8) ((a >> (srcbitsleft - 8)) & BITMASK);
-		destbitsleft -= 8;
-	}
-	/* Now srcbitsleft and destbitsleft are the same, need not track both */
-	/* store whole bytes */
-	while (destbitsleft >= 8)
-	{
-		*r++ = (bits8) ((a >> (destbitsleft - 8)) & BITMASK);
-		destbitsleft -= 8;
+	rlen=(typmod+BITS_PER_BYTE-1)/BITS_PER_BYTE;
+
+	if (srcbits>=typmod) {
+		a<<=srcbits-typmod;
+for (i=0; i!=rlen; ++i, a<<=BITS_PER_BYTE) r[i]=a>>(srcbits-BITS_PER_BYTE);
+	} else {
+		int sh=typmod%BITS_PER_BYTE;
+		int64 h=a>>sh;
+		int64 l=a<<(srcbits-sh);
+		size_t const zsize=rlen-sizeof(a)-(sh!=0);
+		bits8 sx=(a>=0)-1;
+		memset(r,sx,zsize);
+for (i=0; i!=sizeof(a); ++i, h<<=8) r[zsize+i]=h>>(srcbits-BITS_PER_BYTE);
+		if (sh!=0) r[zsize+sizeof(a)]=l>>(srcbits-BITS_PER_BYTE);
 	}
-	/* store last fractional byte */
-	if (destbitsleft > 0)
-		*r = (bits8) ((a << (8 - destbitsleft)) & BITMASK);
 
 	PG_RETURN_VARBIT_P(result);
 }

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Winflex

[Magnus Hagander]

Using
http://www.postgresql.org/ftp/misc/winflex/

on a 64-bit windows, but in 32-bit mode (this certainly used to work),
trying to build HEAD.

first of all, it returns error code 128 when running "flex -V", which
means our script claims it doesn't work. Commenting out that check, it
crashes along the line of:
Running flex on src\backend\utils\misc\guc-file.l
 14 [main] flex 2372 _cygtls::handle_exceptions: Exception:
STATUS_ACCESS_VIOLATION

Sometimes it doesn't crash, but do this instead:
Running flex on src\backend\parser\scan.l
c:\gnuwin32\bin\m4.exe:stdin:16769: ERROR: end of file in string
Project : error PRJ0002 : Error result 128 returned from 'C:\WINDOWS\SysWow64\cm
d.exe'.


If I run it a couple of times, it eventually completes. But then bombs
out because the generated files aren't complete.

Anybody else seen this? Am I forgetting something typical?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] XML schemas and PG column names

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan  writes:
  
I propose that we annotate the schema section RowType elements with the 
original names, so we would have something like this in the schema section:

 
1. Is that legal per the SQL/XML spec?
  


It is certainly legal per XML and XSD specs, and the SQL/XML spec has 
annotations using appinfo elements. It would be rather surprising if the 
SQL/XML spec forbade annotations such as I propose. The spec is 
mind-bogglingly impenetrable, though. Perhaps Peter or Nicholas might know.



2. What happens when the column name contains characters that would have
to be escaped, such as "<" --- haven't you just replaced one de-escaping
problem with another?


  


No. say the name is "foo & bar < baz". Then the annotation would be:

   foo & bar < baz

But the difference is that the XML processor will automatically unescape 
this value (and re-escape it on output if necessary). The user won't 
have to do anything (or shouldn't if their XML processor is worth 
anything at all). So in a stylesheet, I'd be able to do something like:


   
  
   

and it would just Do The Right Thing. (If we didn't want the output 
re-escaped, say when the otuput format was not XML or HTML, we could 
make it do that too).


cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] XML schemas and PG column names

[Tom Lane]

Andrew Dunstan  writes:
> I propose that we annotate the schema section RowType elements with the 
> original names, so we would have something like this in the schema section:

1. Is that legal per the SQL/XML spec?

2. What happens when the column name contains characters that would have
to be escaped, such as "<" --- haven't you just replaced one de-escaping
problem with another?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] XML schemas and PG column names

[Andrew Dunstan]

I'm doing some work with the output of query_to_xml_and_xmlschema(). The 
output is a bit unfortunate in my opinion when the column names in the 
query are not legal XML names. We quite reasonably translate the names 
so that legal XML names result, but we don't actually put the original 
name anywhere in the output, meaning that the end processor has some 
work to do to recover the original. Here are two snippets from the 
output when the column names are "z" and "25 %ile":



  


  



  1
  <_x0032_5_x0020__x0025_ile>2


Of course, we can recover the original name by using something like perl 
to do operations like this:


   $column_name =~ s/_x([[:xdigit:]]{4})_/pack("U",hex($1))/ge;

but that's ugly and not as simply available in many XSL processors (I am 
using XSL to transform the XML.)


I propose that we annotate the schema section RowType elements with the 
original names, so we would have something like this in the schema section:


   http://www.postgresql.org/schemas/column-names";>
  
  
   


z
   
*
   * 

   

25 %ile
   
**
   
  


While it might be a bit longwinded, it's not going to add to the per-row 
output, just the schema section.


Thoughts?

cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication and non-blocking I/O

[Tom Lane]

Heikki Linnakangas  writes:
> Changing the finish_time argument to pqWaitTimed into timeout_ms changes
> the behavior connect_timeout option to PQconnectdb. It should wait for
> max connect_timeout seconds in total, but now it is waiting for
> connect_timeout seconds at each step in the connection process: opening
> a socket, authenticating etc.

Refresh my memory as to why this patch is touching any of that code at
all?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Hot Standby, deferred conflict resolution for cleanup records (v2)

[Simon Riggs]

I think I've found a better way of doing deferred conflict resolution
for WAL cleanup records. (This does not check for conflicts at block
level).

When a cleanup arrives, check *lock* conflicts to see who is accessing
the relation about to be cleaned.

If there are any lock conflicts, then wait, if requested.

If we waited, re-check *lock* conflicts to see who is accessing the
relation about to be cleaned. While holding lock, set latestRemovedXid
for the relation (protected by the partition lock).

Anyone acquiring a lock on a table should check the latestRemovedXid for
the table and abort if their xmin is too old. This prevents new lockers
from accessing a cleaned relation immediately after we decide to abort
anyone looking at that table. (Anyone queuing for the existing locks
would be caught by this).

We then cancel the list of current lock conflicts using the
latestRemovedXid (if there is one) as a cross-check to see if we can
avoid cancelling the query.

So if latestRemovedXid advances on a table you have locked, you will
have your xmin re-checked. If you access a table that has been or is
about to be cleaned then you will check xmin also.

Taken together this will mean that far fewer queries get cancelled,
since we check on both relid and latestRemovedXid. Reasonably simple
queries that take locks on a small number of relations at the start of
their execution will continue processing for long periods if they do not
access fast changing relations.

In particular, IMHO, this will cure about 90% of the btree delete issue,
since only users accessing a particularly busy index will need to cancel
themselves. Since many longer running queries don't use indexes at all
that trait alone will ensure that queries survive longer.

We need to keep track of latestRemovedXids for various relations in
shared memory. ISTM we can track top 8? common relids per lock partition
using a trivial LRU and then have a catch-all value for others. That
will allow us to track more than 100 relations without sweating too
much. All the fuss is handled during hot standby, so if you choose not
to use it, you have no impact.

-- 
 Simon Riggs   www.2ndQuadrant.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SE-PostgreSQL/Lite Review

[KaiGai Kohei]

(2009/12/12 21:51), Robert Haas wrote:

2009/12/12 KaiGai Kohei:

I'd like to summary about the framework.


I am not necessarily in agreement with many of the points listed in
this email.


* Functionalities

The ACE framework hosts both of the default PG checks and upcoming
enhanced securities. We can build a binary with multiple enhanced
security features, but user can choose one from them at most due
to the security label management.


Just yesterday, Stephen Frost and I were discussing whether to stick a
layer of interdirection into the proposed interface layer using
function pointers.  We agreed to defer that decision to a later date.
If, however, we eventually decide to do that, it will clearly mean
that multiple security frameworks are possible simultaneously and that
they can be implemented as loadable modules.  On the other hand, we
might decide that that design does NOT make sense, in which case we
might end up with something more like the code snippet you proposed
below, although I'm not sure you have the details right.


I don't intend to include #ifdef ... #endif block in the patches which
I'll submit in the next week. OK, it can be decided later.

My opinion is enhanced security provider should be inlined.


We have also not decided whether to support a single security label or
multiple security labels per object.  I tend to think that the case
for multiple labels is pretty thin, but on the other hand it's not
unmangeably difficult to do so.  It could be coded up using an array
representation, just as we now do for reloptions.  Again, I want to
defer this decision until later.  Right now, I want to focus on seeing
whether it is all possible for us to get community buy-in on just
centralizing the existing checks, without making any commitment to
what may come after that.


OK, it to be later.


* Namespace

The framework is stored in the src/backend/security/ace.
As a general rule, Each source file has this naming convension:
  src/backend/security/ace/_ace.c


Maybe.  Generally I think if there's a common prefix it should go at
the beginning of the filename, not the end, but I don't want to get
into source code organization too much at this point.  It's too early
to make those decisions.


But we have to deploy source code somewhere. :(
At the moment, "e" of "ace" might not necessary, but it is not productive
to review a patch to replace all the "ac_" by "ace_" later.
So, I'll use this name just as a name at the moment.


* Documentation

I don't plan to provide SGML documentation about ACE itself, except for
internal section, because it is an internal infrastructure.
For developers, src/backend/security/ace/README will provide a brief
overview of the ACE framework, and every security hooks have source
code comments to introduce the specification of itself.


That's probably about right, but I don't want to prejudge that without
further discussion in the community.


If something are necessary, I'll also describe SGML documentation later.
At this moment, I'll include README and source code comments in the
first patches in the next week.


* Patches

As a general rule, a patch is organized for each object classes, except
for very tiny object classes.
e.g)
  pgsql-ace-02-database.patch
  pgsql-ace-03-schema.patch
 :

I'll try to submit a set of patches related to the following object classes
and functionalities by the 12/16.


Stephen Frost is working on a patch for just one object type.  I think
that is a better approach than writing code for everything and then
trying to review it all at once.  We need to try to get consensus on
the basic design before we write a lot of code.  I do agree however
that **if** we are able to get consensus on one object type, we should
probably design the whole thing as a stack of patches that apply on
top of each other, one per object type, for easier review.  That's not
an approach I usually favor, but in this case I think it makes sense.


What I wanted to say is that about a dozen of patches at once are not
happy for us, so I'd like to submit a limited number of patches earlier.
Are you saying that I should submit one-by-one and more rapidly?
I don't have any reason to oppose it.

Thanks,
--
KaiGai Kohei 

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SE-PostgreSQL/Lite Review

[KaiGai Kohei]

(2009/12/12 15:42), "Ing . Marcos Lui's Orti'z Valmaseda" wrote:
> KaiGai Kohei escribio':
>> Stephen Frost wrote:
>>
>>> Josh,
>>>
>>> * Joshua Brindle (met...@manicmethod.com) wrote:
>>>
 Stephen Frost wrote:

> I do think that, technically, there's no reason we couldn't allow for
> multiple "only-more-restrictive" models to be enabled and built in a
> single binary for systems which support it.  As such, I would make those
> just "#if defined()" rather than "#elif".  Let it be decided at runtime
> which are actually used, otherwise it becomes a much bigger problem for
> packagers too.
>
 It isn't just a case of using #if and it magically working. You'd need a
 system to manage multiple labels on each object that can be addressed by
 different systems. So instead of having an object mapped only to
 "system_u:object_r:mydb_t:s15" you'd also have to have it mapped to,
 eg., "^" for Smack.

>>> I'm not sure I see that being a problem..  We're going to have
>>> references in our object managers which make sense to us (eg: table
>>> OIDs) and then a way of mapping those to some label (or possibly a set
>>> of labels, as you describe).  We might want to reconsider the catalog
>>> structure a bit if we want to support more than one at a time, but I
>>> don't see it as a huge problem to support more than one label existing
>>> for a given object.
>>>
>>
>> If we allow multiple security labels on a database object, we have to
>> expand the structure of system catalog whenever a new security feature
>> will come in. I think it against to the purpose of the framework.
>>
>> Even if we store them external relations to reference the object by OID,
>> we have to provide multiple interface to import/export a security label
>> for each enhanced securities. For example, it requires much complex patch
>> to the pg_dump.
>>
>> My preference is all the enhanced securities shares a common facility
>> to manage security label, a common statement support and a common
>> backup/restore support.
>>
>> Thanks,
>>
> I'm worried with the performance implications that have this patch on
> the core of the system.
> If you are aggregating more security layers, There is not a database
> degradation? I don't know how you attack these issues.
> Regards.

If you don't enable any enhanced security providers, the cost to make
access control decision is much the same with existing permission
checks, because it just applies same checks.

If you enable an enhanced security providers (such as SELinux), here
is a trade-off between additional restriction and maximum performance.
In my past measurement, SE-PostgreSQL with full functionalities had
2-4% of performance trade-off in pgbench test. (Note that it also
enables row-level checks in my local branch.)

See the page.16 of this slides:
  http://sepgsql.googlecode.com/files/JLS2009-KaiGai-LAPP_SELinux.pdf

We assume the user of SE-PgSQL gives security the first priority, so
it will be enough acceptable penalty.

Thanks,
-- 
KaiGai Kohei 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SE-PostgreSQL/Lite Review

[Robert Haas]

2009/12/12 KaiGai Kohei :
> I'd like to summary about the framework.

I am not necessarily in agreement with many of the points listed in
this email.

> * Functionalities
>
> The ACE framework hosts both of the default PG checks and upcoming
> enhanced securities. We can build a binary with multiple enhanced
> security features, but user can choose one from them at most due
> to the security label management.

Just yesterday, Stephen Frost and I were discussing whether to stick a
layer of interdirection into the proposed interface layer using
function pointers.  We agreed to defer that decision to a later date.
If, however, we eventually decide to do that, it will clearly mean
that multiple security frameworks are possible simultaneously and that
they can be implemented as loadable modules.  On the other hand, we
might decide that that design does NOT make sense, in which case we
might end up with something more like the code snippet you proposed
below, although I'm not sure you have the details right.

We have also not decided whether to support a single security label or
multiple security labels per object.  I tend to think that the case
for multiple labels is pretty thin, but on the other hand it's not
unmangeably difficult to do so.  It could be coded up using an array
representation, just as we now do for reloptions.  Again, I want to
defer this decision until later.  Right now, I want to focus on seeing
whether it is all possible for us to get community buy-in on just
centralizing the existing checks, without making any commitment to
what may come after that.

> So, it basically has the following structure:
>
>  Value *
>  ac_database_create([arguments ...])
>  {
>      Value *retval = NULL;
>
>      /*
>       * The default PG checks here. It is never disabled.
>       */
>
>      switch (ace_feature)
>      {
>  #ifdef HAVE_SELINUX
>      case ACE_SELINUX_SUPPORT:
>          if (sepgsql_is_enabled())
>              retval = sepgsql_database_create(...);
>          break;
>  #endif
>  #ifdef HAVE_SMACK
>      case ACE_SMACK_SUPPORT:
>          if (smack_is_enabled())
>              retval = smack_database_create(...);
>          break;
>  #endif
>      default:
>           break;
>      }
>      return retval;
>  }

Again, if we decided on a function-pointer interface, it would be up
to the loaded security module whether or not to still apply the
default PG checks.  We have made no decision on that one way or the
other.

> * Namespace
>
> The framework is stored in the src/backend/security/ace.
> As a general rule, Each source file has this naming convension:
>  src/backend/security/ace/_ace.c

Maybe.  Generally I think if there's a common prefix it should go at
the beginning of the filename, not the end, but I don't want to get
into source code organization too much at this point.  It's too early
to make those decisions.

> Uncategolized hooks (such as cascaded-deletion called from dependency
> system) are stored in ace/sysobj_ace.c. It is an exception.

The uncategorized hooks are one of the big design decisions we need to
deal with.  Let's put our energy into thinking about how to make a
tight, clean interface there, rather than worrying about where we put
the code.

> All the framework functions (except for static) have "ace_" prefix
> to distinguish from any other internal routines.

I'm not convinced.  Probably there will be a prefix, but since I'm
envisioning the first phase of this as strictly code cleanup, it
doesn't seem right to give it a prefix that means access control
*extensions*.

> The security providers (SELinux, upcoming SMACK, ...) specific files
> are stored in a certain directory in src/backend/security/ace/.
> SE-PgSQL shall use "sepgsql" directory.

It's way too early to decide this, IMO.

> * Documentation
>
> I don't plan to provide SGML documentation about ACE itself, except for
> internal section, because it is an internal infrastructure.
> For developers, src/backend/security/ace/README will provide a brief
> overview of the ACE framework, and every security hooks have source
> code comments to introduce the specification of itself.

That's probably about right, but I don't want to prejudge that without
further discussion in the community.

> * Patches
>
> As a general rule, a patch is organized for each object classes, except
> for very tiny object classes.
> e.g)
>  pgsql-ace-02-database.patch
>  pgsql-ace-03-schema.patch
>     :
>
> I'll try to submit a set of patches related to the following object classes
> and functionalities by the 12/16.

Stephen Frost is working on a patch for just one object type.  I think
that is a better approach than writing code for everything and then
trying to review it all at once.  We need to try to get consensus on
the basic design before we write a lot of code.  I do agree however
that **if** we are able to get consensus on one object type, we should
probably design the whole thing as a stack of patches that apply on
top of each other

Re: [HACKERS] SE-PostgreSQL/Lite Review

[Ing . Marcos Lui's Orti'z Valmaseda]

KaiGai Kohei escribio':
> Stephen Frost wrote:
>   
>> Josh,
>>
>> * Joshua Brindle (met...@manicmethod.com) wrote:
>> 
>>> Stephen Frost wrote:
>>>   
 I do think that, technically, there's no reason we couldn't allow for
 multiple "only-more-restrictive" models to be enabled and built in a
 single binary for systems which support it.  As such, I would make those
 just "#if defined()" rather than "#elif".  Let it be decided at runtime
 which are actually used, otherwise it becomes a much bigger problem for
 packagers too.
 
>>> It isn't just a case of using #if and it magically working. You'd need a  
>>> system to manage multiple labels on each object that can be addressed by  
>>> different systems. So instead of having an object mapped only to  
>>> "system_u:object_r:mydb_t:s15" you'd also have to have it mapped to,  
>>> eg., "^" for Smack.
>>>   
>> I'm not sure I see that being a problem..  We're going to have
>> references in our object managers which make sense to us (eg: table
>> OIDs) and then a way of mapping those to some label (or possibly a set
>> of labels, as you describe).  We might want to reconsider the catalog
>> structure a bit if we want to support more than one at a time, but I
>> don't see it as a huge problem to support more than one label existing
>> for a given object.
>> 
>
> If we allow multiple security labels on a database object, we have to
> expand the structure of system catalog whenever a new security feature
> will come in. I think it against to the purpose of the framework.
>
> Even if we store them external relations to reference the object by OID,
> we have to provide multiple interface to import/export a security label
> for each enhanced securities. For example, it requires much complex patch
> to the pg_dump.
>
> My preference is all the enhanced securities shares a common facility
> to manage security label, a common statement support and a common
> backup/restore support.
>
> Thanks,
>   
I'm worried with the performance implications that have this patch on
the core of the system.
If you are aggregating more security layers, There is not a database
degradation? I don't know how you attack these issues.
Regards.



-- 
-
"TIP 4: No hagas 'kill -9' a postmaster"
Ing. Marcos Lui's Orti'z Valmaseda
PostgreSQL System DBA 
Centro de Tecnologi'as de Almacenamiento y Ana'lis de Datos (CENTALAD)
Universidad de las Ciencias Informa'ticas(http://www.uci.cu)

Linux User # 418229
http://www.postgresql-es.org
http://www.postgresql.org
http://www.planetpostgresql.org




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:
> - When replaying b-tree deletions, we currently wait out/cancel all
> running (read-only) transactions. We take the ultra-conservative
> stance because we don't know how recent the tuples being deleted are.
> If we could store a better estimate for latestRemovedXid in the WAL
> record, we could make that less conservative.

I think I can improve on the way we do this somewhat.

When we GetConflictingVirtualXIDs() with InvalidTransactionId we include
all backends.

If a query can only see currently-running xacts then it cannot see any
data that is being cleaned up because its xmin > latestCompletedXid.

Put another way, Assert(latestRemovedXid <= latestCompletedXid)

-- 
 Simon Riggs   www.2ndQuadrant.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] LDAP where DN does not include UID attribute

[Magnus Hagander]

On Sat, Dec 12, 2009 at 02:41, Robert Haas  wrote:
> On Sun, Dec 6, 2009 at 11:29 PM, Greg Smith  wrote:
>> Magnus Hagander wrote:
>>
>> On Sun, Nov 29, 2009 at 13:05, Magnus Hagander  wrote:
>>
>>
>> I'll be happy to work on this to get it ready for commit, or do you
>> want to do the updates?
>>
>>
>> Here's a patch with my work so far. Major points missing is the
>> sanitizing of the username and the docs.
>>
>>
>> It looks like you did an initial review here already.  Since we haven't
>> heard from Robert in a while and you seem interested in the patch, I just
>> updated this one to list you as the committer and marked it "ready for
>> committer".  You can commit it or bounce it from there, but it's obvious
>> none of our other reviewers are going to be able to do anything with it.
>
> I think we should mark this returned with feedback, since it sounds
> like there are still open items.

I plan to clean up those open item smyself since there has been no
further feedback from the OP. Hopefully in time before the CF ends. So
please leave it for a few days more :-)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

[Dimitri Fontaine]

Hi,

Le 11 déc. 2009 à 01:43, Bruce Momjian a écrit :
>> Would you be up for writing the extension facility?
> 
> Uh, well, I need to help with the patch commit process at this point ---
> if I find I have extra time, I could do it.   I will keep this in mind.

If you ever find the time to do it, that would be excellent!
The extension facility is on the top list of Josh Berkus missing things we'll 
have to provide soon (or something) and a very annoying missing feature, the 
last stone of the high praised extensibility of PostgreSQL.
  
http://it.toolbox.com/blogs/database-soup/postgresql-development-priorities-31886

It could also means that pg_migrator would have an easier time handling user 
data types etc, if extension authors are able to implement the hooks to call to 
migrate their data from previous to next major version ondisk format.

Anyway, thanks for considering it!
-- 
dim

PS: of course I've developed some ideas of how I'd like this to work and some 
use cases too, so bug me on IRC or whatever for details etc :)
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] explain output infelicity in psql

[Peter Eisentraut]

On tor, 2009-12-10 at 22:02 -0500, Robert Haas wrote:
> On Thu, Dec 10, 2009 at 8:11 PM, Bruce Momjian  wrote:
> > One idea would be to change the column _separator_ for newlines --- that
> > way, they don't show up for single-column output.
> 
> Hilariously enough, that's exactly what we used to do.

Well, the only reason why that "worked" for this case is that it didn't
work at all when the column in question was the first one.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] new CommitFest states

[Greg Smith]

I didn't really get any feedback on my proposal to add a new "Discussing 
review" state to help out the reviewers and CF manager.  To show how 
adding it helps track the common flow of patches through the system, I 
turned the whole CF into a big state machine and marked how the 
transitions should normally happen at 
http://wiki.postgresql.org/wiki/Running_a_CommitFest


If you carefully read the "Followup" section, which Robert wrote 
initially, you can see it implies such a state exists via the "Bogged 
down in discussion" comments.  I'd just like to have a way to flag 
patches that are entering discussion because the reviewer isn't sure 
what happens next as such directly, to make this whole process more 
mechanical, fair, and predictable to the bulk of the participants 
(reviewers and authors).  I wasn't able to figure out how to do that 
without adding this additional state to the system.


Robert's main objection to this idea, that at any point there should be 
one obvious person with the next action to take and authors should take 
more responsibility for their patch, is a good ideal.  But since the CF 
manager ends up being the backstop for breakdowns in the process, 
they're always a second possible source for transitions.  I'd rather 
them be a more predictable such source for a number of reasons.


Note that a major secondary goal here is to make it easier to bring 
on-board new CF managers and have them hit the ground running, by having 
near deterministic suggestions for much of what they need to do.  It's 
also not a coincidence that some of the more boring parts of that job 
step toward being specified well enough that one might start automating 
them too.  How to construct a simple "nag bot" that mails 
pgsql-rrreviewers to perform most of the CF actions listed here is 
pretty obvious working from this table.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication and non-blocking I/O

[Heikki Linnakangas]

Fujii Masao wrote:
> On Thu, Dec 10, 2009 at 12:00 AM, Tom Lane  wrote:
>>> The OS buffer is expected to be able to store a large number of
>>> XLogRecPtr messages, because its size is small. So it's also OK
>>> to just drop it.
>> It certainly seems to be something we could improve later, when and
>> if evidence emerges that it's a real-world problem.  For now,
>> simple is beautiful.
> 
> I just dropped the backend libpq changes related to non-blocking I/O.
> 
>   git://git.postgresql.org/git/users/fujii/postgres.git
>   branch: replication

Thanks, much simpler now.

Changing the finish_time argument to pqWaitTimed into timeout_ms changes
the behavior connect_timeout option to PQconnectdb. It should wait for
max connect_timeout seconds in total, but now it is waiting for
connect_timeout seconds at each step in the connection process: opening
a socket, authenticating etc.

Could we change the API of PQgetXLogData to be more like PQgetCopyData?
I'm thinking of removing the timeout argument, and instead looping with
select/poll and PQconsumeInput in the caller. That probably means
introducing a new state analogous to PGASYNC_COPY_IN. I haven't thought
this fully through yet, but it seems like it would be good to have a
consistent API.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers