Updates of SE-PostgreSQL 8.4devel patches

The following proposal is idea which I have been considered for several days.

A design of PostgreSQL fine-grained security
--------------------------------------------

* Target

This feature provide a row-level access control feature based on
database acl. Any tuple can have its access control list as table
having, and it is checked when the executor scan the tuple.
The violated tuples are filtered from the result set.

This feature does not provide a column-level access control feature,
because its effort is already in development, and it has SQL standards
to be refered.
But there is no standard for row-level security as far as I know,
so these features should be provided in separated.

* Security architecture

Its access control policy is based on database acl which is a kind of
discretional access control (DAC). It implicitly allows resource owner
or privileged users to change its access rights.
As an existing mechanism doing, privileged database roles can ignore
row-level access controls provided by this feature.

The resource owner of tuple should be a same as table owner, because
we have to massive number of pg_depend entries if individual tuple has
its owner. In addition, here is one more reason related to kinds of
permissions.

Three kind of permissions are provided for tuples. These are SELECT,
UPDATE and DELETE. The violated tuples are filtered out from the result
set of DML statement.
The "INSERT" permission is not provided, because an object does not exist
when the permission should be checked. All insertion of tuples are controled
by database acl of table. Since table owner is always same as tuple's one,
there is no administrative matter.

When we insert a tuple without any explicit acl, an empty acl is assigned.
It allows any kinds of accesses. Only owner can insert a tuple with explicit
acl.

* Implementation

This feature is implemented as a guest of PGACE security framework due to
the following two reasons.
The one is we don't have a standard for row-level security to be refered,
so it is more appropriate to be implemented as an "enhanced" security
mechanism.
The other is it provides several useful foundation to implement enhanced
security feature, like security system column support. We have to store
a database acl for each tuples which have characteristics massive objects
tend to share a small number of acls. The PGACE enables to represent it
with minimum cost.

The following image shows a concept of security system column.

  kaigai=# SELECT pg_tuple_acl, * FROM drink;
  +--------------------------------+----+--------+-------+
  |           pg_tuple_acl         | id |  name  | price |
  +--------------------------------+----+--------+-------+
  | {kaigai=rwd/kaigai,=ar/kaigai} |  1 | coke   |  130  |
  | {}                             |  2 | juice  |  150  |
  | {kaigai=rwd/kaigai,=rw/kaigai} |  3 | coffee |  200  |
  |                :               |  : |    :   |   :   |

The security system column is writable. The owner can set per-tuple acl
with UPDATE or INSERT statement. The acl statement is a bit complicated,
so the following two functions helps to modify acl.

pg_tuple_acl_grant(text original, text role, text permissions)
pg_tuple_acl_revoke(text original, text role, text permissions)

For example:
UPDATE drink SET pg_tuple_acl = pg_tuple_acl_grant(pg_tuple_acl, 'bob', 'select,update');
WHERE id in (5,6,7);

One limitation is we can use this feature exclusively with SE-PostgreSQL.
But, I think user's requirements are different.

* Special cases

This feature does not allow to assign ACLs to tuples within system catalogs
to prevent inconsistency with existing access control mechanism.

When a user tries to insert a tuple with duplicate PK, it is failed
independent from its visibility.

When a user tries to insert a new tuple with FK, the refered PK have to
be visible for the owner of refered table, because FK triggers are invoked
with owner's identifier. In similar case, when a user tries to update or
delete a tuple with PK, the owner of refering table have to be able to
perform pre-defined action (like SET NULL).

* Backup/Restore

I'll add '--enable-tuple-acl' option to pg_dump/pg_dumpall.
It enables to dump tables with defined acls, and they can be restored via
writable security system column.

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

KaiGai Kohei wrote:

The following proposal is idea which I have been considered for several days.

A design of PostgreSQL fine-grained security
--------------------------------------------

* Target

This feature provide a row-level access control feature based on
database acl. Any tuple can have its access control list as table
having, and it is checked when the executor scan the tuple.
The violated tuples are filtered from the result set.

This feature does not provide a column-level access control feature,
because its effort is already in development, and it has SQL standards
to be refered.
But there is no standard for row-level security as far as I know,
so these features should be provided in separated.

* Security architecture

Its access control policy is based on database acl which is a kind of
discretional access control (DAC). It implicitly allows resource owner
or privileged users to change its access rights.
As an existing mechanism doing, privileged database roles can ignore
row-level access controls provided by this feature.

The resource owner of tuple should be a same as table owner, because
we have to massive number of pg_depend entries if individual tuple has
its owner. In addition, here is one more reason related to kinds of
permissions.

Three kind of permissions are provided for tuples. These are SELECT,
UPDATE and DELETE. The violated tuples are filtered out from the result
set of DML statement.
The "INSERT" permission is not provided, because an object does not exist
when the permission should be checked. All insertion of tuples are controled
by database acl of table. Since table owner is always same as tuple's one,
there is no administrative matter.

When we insert a tuple without any explicit acl, an empty acl is assigned.

I will changes this design.
It is useful to provide an interface to set up default row acl, like:

ALTER TABLE t1 DEFAULT_ROW_ACL='{kaigai=rwd/kaigai,ymj=r/kaigai}';

Please any comments,

Thanks

It allows any kinds of accesses. Only owner can insert a tuple with explicit
acl.

* Implementation

This feature is implemented as a guest of PGACE security framework due to
the following two reasons.
The one is we don't have a standard for row-level security to be refered,
so it is more appropriate to be implemented as an "enhanced" security
mechanism.
The other is it provides several useful foundation to implement enhanced
security feature, like security system column support. We have to store
a database acl for each tuples which have characteristics massive objects
tend to share a small number of acls. The PGACE enables to represent it
with minimum cost.

The following image shows a concept of security system column.

kaigai=# SELECT pg_tuple_acl, * FROM drink;
+--------------------------------+----+--------+-------+
|           pg_tuple_acl         | id |  name  | price |
+--------------------------------+----+--------+-------+
| {kaigai=rwd/kaigai,=ar/kaigai} |  1 | coke   |  130  |
| {}                             |  2 | juice  |  150  |
| {kaigai=rwd/kaigai,=rw/kaigai} |  3 | coffee |  200  |
|                :               |  : |    :   |   :   |

The security system column is writable. The owner can set per-tuple acl
with UPDATE or INSERT statement. The acl statement is a bit complicated,
so the following two functions helps to modify acl.

pg_tuple_acl_grant(text original, text role, text permissions)
pg_tuple_acl_revoke(text original, text role, text permissions)

For example:
UPDATE drink SET pg_tuple_acl = pg_tuple_acl_grant(pg_tuple_acl, 'bob', 'select,update');
WHERE id in (5,6,7);

One limitation is we can use this feature exclusively with SE-PostgreSQL.
But, I think user's requirements are different.

* Special cases

This feature does not allow to assign ACLs to tuples within system catalogs
to prevent inconsistency with existing access control mechanism.

When a user tries to insert a tuple with duplicate PK, it is failed
independent from its visibility.

When a user tries to insert a new tuple with FK, the refered PK have to
be visible for the owner of refered table, because FK triggers are invoked
with owner's identifier. In similar case, when a user tries to update or
delete a tuple with PK, the owner of refering table have to be able to
perform pre-defined action (like SET NULL).

* Backup/Restore

I'll add '--enable-tuple-acl' option to pg_dump/pg_dumpall.
It enables to dump tables with defined acls, and they can be restored via
writable security system column.

Thanks,

--
KaiGai Kohei <kaigai@kaigai.gr.jp>

KaiGai Kohei wrote:

I updated the series of patches for SE-PostgreSQL 8.4devel.

[1/5] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1043.patch
[2/5] http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r1043.patch
[3/5] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1043.patch
[4/5] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1043.patch
[5/5] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1043.patch

I looked over the patches listed above. They have line counts listed
below:

10759 sepostgresql-sepgsql-8.4devel-3-r1043.patch
616 sepostgresql-pg_dump-8.4devel-3-r1043.patch
826 sepostgresql-policy-8.4devel-3-r1043.patch
1237 sepostgresql-docs-8.4devel-3-r1043.patch
836 sepostgresql-tests-8.4devel-3-r1043.patch
14274 total

Particularly interesting was the doc patch,
sepostgresql-docs-8.4devel-3-r1043.patch. It explains how SE-PostgreSQL
checks the permission level of the client process (getpeercon) and uses
that to determine what the user should see. Also interesting is how a
new row-level system permission column references a new table
'pg_security', which holds security credentials for the row.

The bulk of the patch is in sepostgresql-sepgsql-8.4devel-3-r1043.patch,
which modifies the backend. About 30% of it or 3k lines modify our
backend, and the rest are indepdendent support routines in their own C
files.

So, I am now reevaluating how we should proceed with this patch.

I think we know we want column-level permissions and that is being
worked on, so it should reduce the size of the 3k part of that patch
slightly.

As far as backend changes the largest part is the row-level permissions.
Do we want row-level permissions to be accessible at the SQL level,
perhaps optionally, by having a role be associated with a row, and only
role members can see it. If we do, and implement it, the 3k part is
reduced significantly.

FYI, SE-PostgreSQL sets _row_ permissions by assigning to the new
permissions system column:

INSERT INTO drink (security_context, id, name, price)
VALUES('system_u:object_r:sepgsql_table_t:SystemHigh', 7, 'tea', 130);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

That string is what is placed in 'pg_security' and a reference to is
placed on the row.

The other conclusion I came to is that the other 11k of patch is really
independent SE-Linux interface code and not likely to change in size no
matter what we implement at the SQL level.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian wrote:

KaiGai Kohei wrote:

I updated the series of patches for SE-PostgreSQL 8.4devel.

[1/5] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1043.patch
[2/5] http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r1043.patch
[3/5] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1043.patch
[4/5] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1043.patch
[5/5] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1043.patch

I looked over the patches listed above. They have line counts listed
below:

10759 sepostgresql-sepgsql-8.4devel-3-r1043.patch
616 sepostgresql-pg_dump-8.4devel-3-r1043.patch
826 sepostgresql-policy-8.4devel-3-r1043.patch
1237 sepostgresql-docs-8.4devel-3-r1043.patch
836 sepostgresql-tests-8.4devel-3-r1043.patch
14274 total

Particularly interesting was the doc patch,
sepostgresql-docs-8.4devel-3-r1043.patch. It explains how SE-PostgreSQL
checks the permission level of the client process (getpeercon) and uses
that to determine what the user should see.

Yes, this is a significant point which tends to be misunderstood.
When two processes with individual security context on operating system
connect to SE-PostgreSQL, they will get individual result, even if they
logged in as a same database role.

Also interesting is how a
new row-level system permission column references a new table
'pg_security', which holds security credentials for the row.

The bulk of the patch is in sepostgresql-sepgsql-8.4devel-3-r1043.patch,
which modifies the backend. About 30% of it or 3k lines modify our
backend, and the rest are indepdendent support routines in their own C
files.

The 3k lines (which is named as PGACE security framework) part was provided
as separated patches, but I was pointed out it requires reviewers to see
two files in same time. So, these were integrated into one.

So, I am now reevaluating how we should proceed with this patch.

I think we know we want column-level permissions and that is being
worked on, so it should reduce the size of the 3k part of that patch
slightly.

As far as backend changes the largest part is the row-level permissions.
Do we want row-level permissions to be accessible at the SQL level,
perhaps optionally, by having a role be associated with a row, and only
role members can see it. If we do, and implement it, the 3k part is
reduced significantly.

FYI, SE-PostgreSQL sets _row_ permissions by assigning to the new
permissions system column:

INSERT INTO drink (security_context, id, name, price)
VALUES('system_u:object_r:sepgsql_table_t:SystemHigh', 7, 'tea', 130);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

That string is what is placed in 'pg_security' and a reference to is
placed on the row.

Yes, it looks like every tuple has text formatted security attribute,
but they hold an Oid value of pg_security system catalog.
Because this feature is provided by common foundation, any other
security features can also use the system column for its purpose
(like storing row-level database acl now in development).

The other conclusion I came to is that the other 11k of patch is really
independent SE-Linux interface code and not likely to change in size no
matter what we implement at the SQL level.

Yes, the primary purpose of this archtecture is to minimize the impact
for the core PostgreSQL implementation, when user choose an enhanced
security mechanism.

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

KaiGai Kohei wrote:

Particularly interesting was the doc patch,
sepostgresql-docs-8.4devel-3-r1043.patch. It explains how SE-PostgreSQL
checks the permission level of the client process (getpeercon) and uses
that to determine what the user should see.

Yes, this is a significant point which tends to be misunderstood.
When two processes with individual security context on operating system
connect to SE-PostgreSQL, they will get individual result, even if they
logged in as a same database role.

Yes, that was very clear in your documentation.

The bulk of the patch is in sepostgresql-sepgsql-8.4devel-3-r1043.patch,
which modifies the backend. About 30% of it or 3k lines modify our
backend, and the rest are indepdendent support routines in their own C
files.

The 3k lines (which is named as PGACE security framework) part was provided
as separated patches, but I was pointed out it requires reviewers to see
two files in same time. So, these were integrated into one.

Ah, OK. I think we need to decide:

1) When are we getting column-level permissions that you can
plug into?
2) Do we want row-level permissions at the SQL level?

The other conclusion I came to is that the other 11k of patch is really
independent SE-Linux interface code and not likely to change in size no
matter what we implement at the SQL level.

Yes, the primary purpose of this archtecture is to minimize the impact
for the core PostgreSQL implementation, when user choose an enhanced
security mechanism.

Yes, you have certainly done that well.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian wrote:

The bulk of the patch is in sepostgresql-sepgsql-8.4devel-3-r1043.patch,
which modifies the backend. About 30% of it or 3k lines modify our
backend, and the rest are indepdendent support routines in their own C
files.

The 3k lines (which is named as PGACE security framework) part was provided
as separated patches, but I was pointed out it requires reviewers to see
two files in same time. So, these were integrated into one.

Ah, OK. I think we need to decide:

1) When are we getting column-level permissions that you can
plug into?

Please note that SE-PostgreSQL checks its column-level permission *after* VIEWs
are expanded, because it focuses on "what" object is accessed, not "how".
Thus, it walks on the query tree just after QueryRewrite() to pick up columns
to be refered in this query.
The term is same, but it's unclear for me whether it can share the code based
on SQL standards, or not.
(In my opinion, it is not a matter, just a difference in security model.)

2) Do we want row-level permissions at the SQL level?

Now I'm working for it and will submit patches due to the end of Oct,
if it is really required to make progress reviewing of SE-PostgreSQL
on the v8.4 development cycle.
However, the scale of its demand is unclear for me.

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

KaiGai Kohei wrote:

1) When are we getting column-level permissions that you can
plug into?

Please note that SE-PostgreSQL checks its column-level permission *after* VIEWs
are expanded, because it focuses on "what" object is accessed, not "how".
Thus, it walks on the query tree just after QueryRewrite() to pick up columns
to be refered in this query.
The term is same, but it's unclear for me whether it can share the code based
on SQL standards, or not.
(In my opinion, it is not a matter, just a difference in security model.)

I understand.

2) Do we want row-level permissions at the SQL level?

Now I'm working for it and will submit patches due to the end of Oct,
if it is really required to make progress reviewing of SE-PostgreSQL
on the v8.4 development cycle.
However, the scale of its demand is unclear for me.

Yes, which is why I would like the community to answer the question
before you have to start coding things. I will say that if we do want
it, the SE-Linux code will be 96% in separate modules and will make it
much easier to accept.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian wrote:

2) Do we want row-level permissions at the SQL level?

Now I'm working for it and will submit patches due to the end of Oct,
if it is really required to make progress reviewing of SE-PostgreSQL
on the v8.4 development cycle.
However, the scale of its demand is unclear for me.

Yes, which is why I would like the community to answer the question
before you have to start coding things. I will say that if we do want
it, the SE-Linux code will be 96% in separate modules and will make it
much easier to accept.

OK, I'll stop my hand today, and wait for pgsql-hacker's opinions.

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

KaiGai Kohei wrote:

Bruce Momjian wrote:

2) Do we want row-level permissions at the SQL level?

Now I'm working for it and will submit patches due to the end of Oct,
if it is really required to make progress reviewing of SE-PostgreSQL
on the v8.4 development cycle.
However, the scale of its demand is unclear for me.

Yes, which is why I would like the community to answer the question
before you have to start coding things. I will say that if we do want
it, the SE-Linux code will be 96% in separate modules and will make it
much easier to accept.

OK, I'll stop my hand today, and wait for pgsql-hacker's opinions.

Here is how I think SQL-level row permissions would work:

We already have an optional OID system column that can be specified
during table creation (WITH OIDS). We could have another optional oid
column (WITH ROW SECURITY) called security_context which would store the
oid of the role that can see the row; if the oid is zero (InvalidOid),
anyone can see it. SE-PostgreSQL would default to WITH ROW SECURITY and
use the oid to look up strings in pg_security.

Is this something we want for non-SE-PostgreSQL builds? SE-PostgreSQL
would still be a compile-time option because of all the additional
SE-Linux support code.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:

Here is how I think SQL-level row permissions would work:

We already have an optional OID system column that can be specified
during table creation (WITH OIDS). We could have another optional oid
column (WITH ROW SECURITY) called security_context which would store the
oid of the role that can see the row; if the oid is zero (InvalidOid),
anyone can see it. SE-PostgreSQL would default to WITH ROW SECURITY and
use the oid to look up strings in pg_security.

This is just a different syntax for KaiGai's label storage
implementation. It doesn't really answer any of the hard questions,
like what the heck is the behavior of foreign keys.

regards, tom lane

This is just a different syntax for KaiGai's label storage
implementation. It doesn't really answer any of the hard questions,
like what the heck is the behavior of foreign keys.

What do you find inadequate about KaiGai's answers to those hard questions?

...Robert

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

Here is how I think SQL-level row permissions would work:

We already have an optional OID system column that can be specified
during table creation (WITH OIDS). We could have another optional oid
column (WITH ROW SECURITY) called security_context which would store the
oid of the role that can see the row; if the oid is zero (InvalidOid),
anyone can see it. SE-PostgreSQL would default to WITH ROW SECURITY and
use the oid to look up strings in pg_security.

This is just a different syntax for KaiGai's label storage
implementation. It doesn't really answer any of the hard questions,
like what the heck is the behavior of foreign keys.

Well, the PGACE documentation says:

http://code.google.com/p/sepgsql/wiki/WhatIsPGACE

Datum pgacePreparePlanCheck(Relation rel)

It is invoked just before calling a function which implements fereign
key constraint.

The major purpose of this hook is to keep consistency in the lowest
level. It enables to notify the guest the beginning of checks in foreign
key constraint. The guest can change the behavior of tuple level access
control between pgacePreparePlanCheck() and pgaceRestorePlanCheck().

--> In SE-PostgreSQL case, access controls in tuple level are normally done
with filtering any violated tuple. However, it can prevent to check
foreign key constraint, because caller cannot recognize whether no tuple
refers the primary relation, or any tuple refering are filtered.
Therefore, SE-PostgreSQL aborts the current transaction if any violated
tuple refering the primary relation.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Here is how I think SQL-level row permissions would work:

We already have an optional OID system column that can be specified
during table creation (WITH OIDS). We could have another optional oid
column (WITH ROW SECURITY) called security_context which would store the
oid of the role that can see the row; if the oid is zero (InvalidOid),
anyone can see it. SE-PostgreSQL would default to WITH ROW SECURITY and
use the oid to look up strings in pg_security.

I like the idea of a WITH ROW SECURITY option to enable row-level
security - that way, tables that don't need it don't have to pay for
it, but I like the idea of storing a full ACL, as KaiGai proposed,
rather than just a single role. Seems much more powerful.

...Robert

Bruce Momjian wrote:

KaiGai Kohei wrote:

Bruce Momjian wrote:

2) Do we want row-level permissions at the SQL level?

Now I'm working for it and will submit patches due to the end of Oct,
if it is really required to make progress reviewing of SE-PostgreSQL
on the v8.4 development cycle.
However, the scale of its demand is unclear for me.

Yes, which is why I would like the community to answer the question
before you have to start coding things. I will say that if we do want
it, the SE-Linux code will be 96% in separate modules and will make it
much easier to accept.

OK, I'll stop my hand today, and wait for pgsql-hacker's opinions.

Here is how I think SQL-level row permissions would work:

We already have an optional OID system column that can be specified
during table creation (WITH OIDS). We could have another optional oid
column (WITH ROW SECURITY) called security_context which would store the
oid of the role that can see the row; if the oid is zero (InvalidOid),
anyone can see it. SE-PostgreSQL would default to WITH ROW SECURITY and
use the oid to look up strings in pg_security.

The above explanation is not correct, as Tom mentioned.
The security system column is declared as TEXT type, however, every tuple
has a Oid value to indicate pg_security system catalog. It enables to
prevent waste of storage. When user tries to read the system column,
it is translated from Oid to text representation.

If the oid is zero, SE-PostgreSQL requires SELinux to provide an alternative
security context called as "unlabeled_t". And SE-PostgreSQL checks client's
right for the tuple which is considered as "unlabeled_t".

The part of implementation is specific for SE-PostgreSQL, so another security
mechanism will able to handle InvalidOid as a mark of "no specific checks".

Is this something we want for non-SE-PostgreSQL builds? SE-PostgreSQL
would still be a compile-time option because of all the additional
SE-Linux support code.

The new system column is now enabled only for SE-PostgreSQL builds.
When we don't enables it, the system column is not appeared and it
does not reuire any additional field in HeapTupleHeader.

We provide it as a common facility for "enhanced" security mechanism.
If the guest of PGACE need the security system column, it has to
define "SECURITY_SYSATTR_NAME" in pg_config.h via configure script.
This constant is the key to activate the security system attribute.

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

Here is how I think SQL-level row permissions would work:

We already have an optional OID system column that can be specified
during table creation (WITH OIDS). We could have another optional oid
column (WITH ROW SECURITY) called security_context which would store the
oid of the role that can see the row; if the oid is zero (InvalidOid),
anyone can see it. SE-PostgreSQL would default to WITH ROW SECURITY and
use the oid to look up strings in pg_security.

This is just a different syntax for KaiGai's label storage
implementation. It doesn't really answer any of the hard questions,
like what the heck is the behavior of foreign keys.

SE-PostgreSQL changes its internal state during foreign key constraint checks.
When user tries to update/delete a PK refered by invisible FK, SE-PostgreSQL
generates an error and prevent inconsistency in FK constraint.
When user tries to insert/update a FK which refers invisible PK, it is failed.
But it does not affect integrity consistency.

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

"Robert Haas" <robertmhaas@gmail.com> writes:

This is just a different syntax for KaiGai's label storage
implementation. It doesn't really answer any of the hard questions,
like what the heck is the behavior of foreign keys.

What do you find inadequate about KaiGai's answers to those hard questions?

Mainly just that they're only one set of possible answers. If we're
thinking of baking a particular set of semantics into SQL syntax,
we'd better be darn sure that they are the right semantics --- both
in terms of widest usefulness, and in terms of not being stuck behind
the eight-ball if the SQL standards committee ever moves to standardize
a similar behavior.

Another point is that the proposed behavior leaks quite a lot of
information, since it will fail operations on the basis of tuples that
supposedly aren't visible to the invoking user. While I admit that it's
hard to see an alternative if we're to preserve FK integrity, I have to
worry that this definition isn't going to satisfy the tin-foil-hat
brigade that are supposed to be the main users of SEPostgres. If the
goal is "you don't know the row is there", this doesn't seem to meet it.

I wonder whether it wouldn't make more sense to pursue restrictions
at some different level. SQL already has the concept of REFERENCE
permission, ie, not just anybody can make a foreign key reference to
your table. Maybe there has to be a rule that you can't make an FK
reference to a table you don't have full read permissions for. The
restrictions in the other direction (what can PK table's owner find out
about FK table's contents) are pretty interesting too.

regards, tom lane

Bruce Momjian wrote:

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

Here is how I think SQL-level row permissions would work:
We already have an optional OID system column that can be specified
during table creation (WITH OIDS). We could have another optional oid
column (WITH ROW SECURITY) called security_context which would store the
oid of the role that can see the row; if the oid is zero (InvalidOid),
anyone can see it. SE-PostgreSQL would default to WITH ROW SECURITY and
use the oid to look up strings in pg_security.

This is just a different syntax for KaiGai's label storage
implementation. It doesn't really answer any of the hard questions,
like what the heck is the behavior of foreign keys.

Well, the PGACE documentation says:

http://code.google.com/p/sepgsql/wiki/WhatIsPGACE

Datum pgacePreparePlanCheck(Relation rel)

In the latest patch, this hooks is replaced by pgaceBeginPerformCheckFK()
and pgaceEndPerformCheckFK(), but its purpose is unchanged.

Sorry for the confusable legacy description.

--> In SE-PostgreSQL case, access controls in tuple level are normally done
with filtering any violated tuple. However, it can prevent to check
foreign key constraint, because caller cannot recognize whether no tuple
refers the primary relation, or any tuple refering are filtered.
Therefore, SE-PostgreSQL aborts the current transaction if any violated
tuple refering the primary relation.

Yes, this behavior keeps FK consistency.

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

"Robert Haas" <robertmhaas@gmail.com> writes:

I like the idea of a WITH ROW SECURITY option to enable row-level
security - that way, tables that don't need it don't have to pay for
it, but I like the idea of storing a full ACL, as KaiGai proposed,
rather than just a single role. Seems much more powerful.

... and even more ill-defined.

Consider the following scenario:

1. User alice is a member of role admin.

2. User bob creates a table BT and puts some rows in it that are
supposedly only visible to role admin. He also grants REFERENCE
permission to alice. (Let's suppose he does that directly, not via
the admin role; though the other case is interesting too.)

3. User alice creates a table AT with an FK dependency on BT and then
makes some entries that depend on the only-visible-to-admin rows in BT.
She is allowed to do this, obviously.

4. User charlie revokes alice's membership in admin.

Now what? Alice's FK constraint is violated, according to the rules
KaiGai proposes. Shall REVOKE have to grovel through every table in the
database looking for possible violations ... and of course locking the
entire DB against writes while it does it? That's not gonna fly. I
also note that the failure would expose knowledge of the contents of BT
and AT to charlie, which might not be thought desirable either.

This problem is bad enough if row visibility is defined just by role
membership. I shudder to think of trying to make it a general ACL.

regards, tom lane

Tom Lane wrote:

4. User charlie revokes alice's membership in admin.

Now what? Alice's FK constraint is violated, according to the rules
KaiGai proposes. Shall REVOKE have to grovel through every table in the
database looking for possible violations ... and of course locking the
entire DB against writes while it does it? That's not gonna fly. I
also note that the failure would expose knowledge of the contents of BT
and AT to charlie, which might not be thought desirable either.

I assume Alice now gets an error on the query that references the
now-invisible foreign key --- that sounds reasonable to me.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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