Thread: New Privilege model purposal
Here it is:
Proposal for a new PostgreSQL Privilege System
July 2000, Jan Wieck
Introduction
The existing permission checking system, implemented in the PostgreSQL database, has alot of
missing capabilities. Users complained about it in the past. With some new features (referential integrity
for example), this system isn't flexible enough any more and need to be expanded or replaced soon.
This document is a draft for implementing a completely new, object/functionality based permission
concept. It defines a fine grained, expandable, general purpose permission checking functionality.
Itshould cover DML- and DDL-statements all at once.
Object Privileges
Object Privileges can be GRANTed or REVOKEed to a particular user or group. The possible
Privilegesare:
ALL [PRIVILEGES] Synonym for any of the privileges, applicaple to the object in
question.
ALTER Permission to alter the schema WRT the object in question.
INSERT Permission to INSERT rows into the named relation.
UPDATE Permission to UPDATE rows in the named relation.
DELETE Permission to DELETE rows from the named relation.
SELECT Permission to SELECT rows from the named relation or sequence.
EXECUTE Permission to call the named function or procedure.
LOCK Permission to exclusively lock the named relation.
REFERENCES Permission to create a foreign key reference to the named
relation.
TRUNCATE Permission to truncate the named relation.
System Privileges
System Privileges are to grant permission to execute DDL- statements or for database wide Object
permissions(valid for all objects of a particular kind).
SUPERUSER A special System Privilege, superseding any other
rights.What the holder of this right want's to do, he does. It
isthe same as now, usesuper in pg_shadow.
----------
CREATE SESSION Permission to login. Checked after general hba.conf access
had been granted. Not having this right will cause the new
backendto immediately terminate.
ALTER SESSION Permission to change session specific attributes like
character set localization.
----------
CREATE TABLE Permission to create new table in a database.
ALTER ANY TABLE Permission to alter any table of the database. Includes rights
to create or drop rules, triggers, references etc.
DROP ANY TABLE Permission to drop any table of the database.
INSERT ANY TABLE Permission to INSERT rows into any table of the database.
UPDATE ANY TABLE Permission to UPDATE rows in any table of the database.
DELETE ANY TABLE Permission to DELETE rows from any table of the database.
SELECT ANY TABLE Permission to SELECT rows from any relation of the database.
LOCK ANY TABLE Permission to explicitly LOCK any relation of the database.
REFERENCE ANY TABLE Permission to use any table of the database in referential
integrity constraints. ----------
CREATE SEQUENCE Permission to create a new sequence.
ALTER ANY SEQUENCE Permission to readjust all sequences.
DROP ANY SEQUENCE Permission to drop any sequence in the database.
----------
CREATE VIEW Permission to create views in the database.
ALTER ANY VIEW Permission to alter any view of the database.
DROP ANY VIEW Permission to drop any view of the database.
----------
CREATE FUNCTION Permission to create new functions in the database.
ALTER ANY FUNCTION Permission to alter any function of the database.
DROP ANY FUNCTION Permission to drop any function of the database.
----------
CREATE TYPE Permission to create a new type.
ALTER ANY TYPE Permission to alter any type of the database.
DROP ANY TYPE Permission to drop any type of the database.
----------
CREATE OPERATOR Permission to create a new operator.
ALTER ANY OPERATOR Permission to alter any operator of the database.
DROP ANY OPERATOR Permission to drop any operator of the database.
----------
CREATE AGGREGATE Permission to create a new aggregate.
ALTER ANY AGGREGATE Permission to alter any aggregate of the database.
DROP ANY AGGREGATE Permission to drop any aggregate of the database.
----------
CREATE OBJECT Permission to create a new table, sequence, type,
operator or aggregate.
ALTER ANY OBJECT Permission to alter any table, sequence, type, operator or
aggregate of the database.
DROP ANY OBJECT Permission to drop any table, sequence, type, operator or
aggregate of the database.
----------
TRUNCATE ANY Permission to truncate any relation of the database.
Implementation
New privilege check funciton
A new function
bool pg_check_priv( int32 privtype, Oid privobject,
int32 privobjowner);
will be called in the appropriate places. The privtype is a #define constant
from a new "utils/privileges.h" header file. Privobject is the OID of the object (relation,
function,aggregate etc.) to check for. Privobjowner is the owner of the object (relowner,
proowner,aggowner etc).
The function will know about privilege relationships. So only one call like
pg_check_priv(PRIV_ALTER_TABLE, Relation->rd_id,
Relation->rd_rel->relowner);
pg_check_priv(PRIV_EXEC_FUNCTION, finfo->fn_oid, finfo->fn_owner);
would be sufficient to check whether the actual caller is permitted to do that.
System catalog changes
Pg_proc is extended by two new bool fields. Prosetuid and procheckperm. These two and the
proowner are held in the fmgr_info struct.
If a function is called through the fmgr (any user defined function is), the function manager
honours these flags. Prosetuid will cause the function manager to switch to another
effectiveuser id, used during pg_check_perms() for the time of the function invocation.
Procheckpermcauses the function manager to check whether the actual (effective) user is
allowed to execute the function (by calling pg_check_perms()).
Pg_shadow is extended with an array, holding all the groups the user belongs to. So after looking
up the user, all group relationships are known.
Two new system catalogs, pg_userprivilege and pg_groupprivilege are created to hold
the actual privileges. They are members of the system cache for fast lookup.
Pg_class will loose it's relacl attribute.
All the (security relevant) information in pg_shadow, pg_group, pg_userprivilege and
pg_groupprivilegeis only modified during GRANT, REVOKE or CREATE/DROP/ALTER
statements. So it's IMHO not an issue to performance questions.
Related details
The system will manage a stack, remembering nested states of the effective user id. Calls
throughthe function manager can switch for- and backward to another one, so prosetuid
functionswill inherit the effective permissions of the function (trigger) owner. The stack
is reinitialized at transaction aborts.
For special purposes, there will be another function pg_check_realperms() checking against the
realuser id allways (don't know what it'll be good for, but in case...).
Comments?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
I not sure, but if I good remember nobody said somethig bad about
PetreE proposal for this, why you prepare new? IMHO Peter's proposal
was good.
And small suggestion, we need the "GRANT ... WITH ADMIN OPTION" or
something like this.
Karel
At 15:27 25/07/00 +0200, Jan Wieck wrote:
>
> Object Privileges
>
> Object Privileges can be GRANTed or REVOKEed to a
> particular user or group. The possible Privileges are:
This sounds great, and you may want to consider extending it to the COLUMN
level in tables & views.
>
...
>
> LOCK Permission to exclusively lock the
> named relation.
This one worries me a little. I think I can see where you are coming from,
but you might be better off defining it as the ability to 'use the LOCK
statement to lock the object exclusively'. The reason I say this is that a
person altering a table's metadata and/or name, may well need an exclusive
lock, and it seems cumbersome to have to grant two privileges.
...etc...
You may also want to consider:
SHOW Permission to view the definition of the named
object. (this is from Dec/Rdb)
RENAME Permission to rename the named relation (gets past my objection
above,but probably best left as part of ALTER)
INHERIT Do you need this?
DBA/OPERATOR/ADMIN Permission to access the database when it is
'closed' (Dec/Rdb call it DBADMIN, I think)
I know we don't have the concept of a 'closed' database yet, but it is very
useful for performing tasks like renaming storage files, restoring backups
etc etc. The idea being that a DBA can close a database, then only DBA
users can connect to it.
> System Privileges
>
> System Privileges are to grant permission to execute DDL-
> statements or for database wide Object permissions (valid
> for all objects of a particular kind).
>
> SUPERUSER A special System Privilege,
> superseding any other rights. What
> the holder of this right want's to
> do, he does. It is the same as now,
> usesuper in pg_shadow.
I suspect this is good grounds for a religious war, but I like a priv
system where I have to 'turn on' a super privilege before I get it. If I am
a superuser, I don't want my cape flapping in the breeze *all* the time.
Can you add some kind of 'CLARK_KENT' priv (ie. 'can become superuser')?
And have SUPERUSER off at the beginning of all sessions?
There are two reasons I think this is important: 1) I am accident prone,
and 2) it's good to live like a mortal most of the time - you get to see
problems before a user complains.
> CREATE TABLE
> ALTER ANY TABLE
> DROP ANY TABLE
> INSERT ANY TABLE
> UPDATE ANY TABLE
> DELETE ANY TABLE
> SELECT ANY TABLE
> LOCK ANY TABLE
> REFERENCE ANY TABLE
> CREATE SEQUENCE
> ALTER ANY SEQUENCE
> DROP ANY SEQUENCE
This seems like overkill; you will need a new priv for every object type.
It is also not clear how 'ALTER ANY TABLE' should interact with 'ALTER
TABLE (specific table)', but I assume the more specific priv rules.
It seems that this is just a way of defining 'default' privs for an object
that does not have an ACL, and if that is the case, why not define a
default protection at both the database level and the object-type level
(perhaps in the relevant pg_* table?). Certainly it seems that 'CREATE
TABLE' could be represented as 'INSERT' priv on the pg_class table etc.
>
> CREATE OBJECT
> ALTER ANY OBJECT
> DROP ANY OBJECT
Back to my previous comments - these seem to be more proerly defined as
'defaults' at the database level, but perhaps I am missing something.
>
> System catalog changes
>
> Pg_proc is extended by two new bool fields. Prosetuid
> and procheckperm. These two and the proowner are
> held in the fmgr_info struct.
>
> If a function is called through the fmgr (any user
> defined function is), the function manager honours
> these flags. Prosetuid will cause the function
> manager to switch to another effective user id, used
> during pg_check_perms() for the time of the function
> invocation.
Wonderful! I've been hoping for this for a while.
> Related details
>
> The system will manage a stack, remembering nested
> states of the effective user id. Calls through the
> function manager can switch for- and backward to
> another one, so prosetuid functions will inherit the
> effective permissions of the function (trigger)
> owner. The stack is reinitialized at transaction
> aborts.
I assume this means that if function f1 running under uid 'fred' calls
function f2 (with no uid specified), then f2 will also still run as 'fred'?
> For special purposes, there will be another function
> pg_check_realperms() checking against the real user
> id allways (don't know what it'll be good for, but in
> case...).
We'll also need to implement another kind of CURRENT_USER (I *think* SQL
defines one). You need to get the 'real' user as well as the 'effective'
user from within SQL.
I hope this is helpful, and I really look forward to this being implemented!
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
Karel Zak wrote: > > I not sure, but if I good remember nobody said somethig bad about > PetreE proposal for this, why you prepare new? IMHO Peter's proposal > was good. Seems I missed that discussion. Sometimes I start to drop incoming eMails by subject. If then the discussion moves to something different without changing the subject, you won't see me on that. Anyway, I haven't found a complete proposal in the ML archive. Consider my proposal "derived work" from hisone, if it is similar and let's combine all the ideas into one complete thing. Peter, have you done anything into that direction already? > And small suggestion, we need the "GRANT ... WITH ADMIN OPTION" or > something like this. What should that do? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Philip Warner wrote: > At 15:27 25/07/00 +0200, Jan Wieck wrote: > > > > Object Privileges > > > > Object Privileges can be GRANTed or REVOKEed to a > > particular user or group. The possible Privileges are: > > This sounds great, and you may want to consider extending it to the COLUMN > level in tables & views. So someone could prevent a user from overriding a DEFAULT column value by revoking the INSERT and UPDATE rightsto the column. Good idea. > > > > > ... > > > > LOCK Permission to exclusively lock the > > named relation. > > This one worries me a little. I think I can see where you are coming from, > but you might be better off defining it as the ability to 'use the LOCK > statement to lock the object exclusively'. The reason I say this is that a > person altering a table's metadata and/or name, may well need an exclusive > lock, and it seems cumbersome to have to grant two privileges. Of course. > You may also want to consider: > > SHOW Permission to view the definition of the named > object. > (this is from Dec/Rdb) As you say it, I see it. The default for all system catalogs would be, that a normal user cannot SELECT from them. So we would need some kind of DESCRIBE command since psql wouldn't be able to grab these definitions any more. I see, this project is becoming bigger the more people really think about the side effects. > RENAME Permission to rename the named relation > (gets past my objection above, but probably > best left as part of ALTER) Let's leave it as part of ALTER TABLE. > INHERIT Do you need this? What other rights must a user have on the inherited relations to work properly with them? > > DBA/OPERATOR/ADMIN Permission to access the database when it is > 'closed' > (Dec/Rdb call it DBADMIN, I think) > > I know we don't have the concept of a 'closed' database yet, but it is very > useful for performing tasks like renaming storage files, restoring backups > etc etc. The idea being that a DBA can close a database, then only DBA > users can connect to it. I like that. And the concept of 'closed' databases should be added to this project. > > System Privileges > > > > System Privileges are to grant permission to execute DDL- > > statements or for database wide Object permissions (valid > > for all objects of a particular kind). > > > > SUPERUSER A special System Privilege, > > superseding any other rights. What > > the holder of this right want's to > > do, he does. It is the same as now, > > usesuper in pg_shadow. > > I suspect this is good grounds for a religious war, but I like a priv > system where I have to 'turn on' a super privilege before I get it. If I am > a superuser, I don't want my cape flapping in the breeze *all* the time. > Can you add some kind of 'CLARK_KENT' priv (ie. 'can become superuser')? > And have SUPERUSER off at the beginning of all sessions? > > There are two reasons I think this is important: 1) I am accident prone, > and 2) it's good to live like a mortal most of the time - you get to see > problems before a user complains. If you don't need DBA privileges, don't log on as a DBA. Have a separate account for that (IMHO). > > CREATE TABLE > > ALTER ANY TABLE > > DROP ANY TABLE > > INSERT ANY TABLE > > UPDATE ANY TABLE > > DELETE ANY TABLE > > SELECT ANY TABLE > > LOCK ANY TABLE > > REFERENCE ANY TABLE > > CREATE SEQUENCE > > ALTER ANY SEQUENCE > > DROP ANY SEQUENCE > > This seems like overkill; you will need a new priv for every object type. > It is also not clear how 'ALTER ANY TABLE' should interact with 'ALTER > TABLE (specific table)', but I assume the more specific priv rules. As I said, it should be fine grained. If a DBA wants some user to be able to create views, but not his owntables, functions etc., how could he if there aren't separate privileges for the single actions? The interactions will be hardwired in the pg_check_priv() function. Since the requested privilege is a #define'd constant, it'll be more or less a big switch statement, calling a single privilege lookup helper oncein a while. > It seems that this is just a way of defining 'default' privs for an object > that does not have an ACL, and if that is the case, why not define a > default protection at both the database level and the object-type level > (perhaps in the relevant pg_* table?). Certainly it seems that 'CREATE > TABLE' could be represented as 'INSERT' priv on the pg_class table etc. No. They are meant as user or group specific privileges. By default, only the owner has access to his tables. He (or a superuser) must explicitly GRANT other users or groupsaccess to it. But a user with SELECT ANY TABLE can do so from the start, because the DBA decided that thisuser act's like a superuser if issuing some SELECT database wide. > > CREATE OBJECT > > ALTER ANY OBJECT > > DROP ANY OBJECT > > Back to my previous comments - these seem to be more proerly defined as > 'defaults' at the database level, but perhaps I am missing something. These aren't separate privileges. CREATE OBJECT is the same as CREATE TABLE and CREATE VIEW and CREATE FUNCTION and... > > System catalog changes > > > > Pg_proc is extended by two new bool fields. Prosetuid > > and procheckperm. These two and the proowner are > > held in the fmgr_info struct. > > > > If a function is called through the fmgr (any user > > defined function is), the function manager honours > > these flags. Prosetuid will cause the function > > manager to switch to another effective user id, used > > during pg_check_perms() for the time of the function > > invocation. > > Wonderful! I've been hoping for this for a while. You never walk alone. > > Related details > > > > The system will manage a stack, remembering nested > > states of the effective user id. Calls through the > > function manager can switch for- and backward to > > another one, so prosetuid functions will inherit the > > effective permissions of the function (trigger) > > owner. The stack is reinitialized at transaction > > aborts. > > I assume this means that if function f1 running under uid 'fred' calls > function f2 (with no uid specified), then f2 will also still run as 'fred'? Right. The euid switching will only be done at entering and leaving a prosetuid function or trigger. Anything doneinside of that uses the new euid until a (however deeply nested) call to another setuid function happens. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Something is seriously broken with your mailer or the listserv, your messages have about 10,000 blank lines at the end of them.
Hmm, I thunk I was working on that. I put out a proposal on May 22, and we came to a pretty good understanding about the details and I was working on a new specification. Your approach seems to be missing column-level privileges and grant options, and is in general not very close to SQL. > With > some new features (referential integrity for example), > this system isn't flexible enough any more and need to be > expanded or replaced soon. I think the RI snafu at least is not the fault of the privilege system at all. I could blame the implementation, but short of that it's actually the fault of the function manager in that it cannot execute functions with the permissions of the creator, as mandated by SQL. That's the thing that needs to be fixed (but it reaches throughout the code of course). > ALTER SESSION Permission to change session specific > attributes like character set > localization. GRANT USAGE ON CHARACTER SET > CREATE TABLE Permission to create new table in a > database. This needs to be tied in with schemas. > Pg_shadow is extended with an array, holding all the > groups the user belongs to. So after looking up the > user, all group relationships are known. We ought to think in terms of SQL roles. Those are nestable, so arrays need to be out of the picture. > The system will manage a stack, remembering nested > states of the effective user id. Calls through the > function manager can switch for- and backward to > another one, so prosetuid functions will inherit the > effective permissions of the function (trigger) > owner. The stack is reinitialized at transaction > aborts. This is definitely necessary, but it needs to be more general. There is a command SET SESSION AUTHORIZATION, which is essentially `su', that could make use of this also. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut wrote: > Hmm, I thunk I was working on that. I put out a proposal on May 22, and we > came to a pretty good understanding about the details and I was working on > a new specification. I apologize. Karel already told me so and it seems I missed that discussion somehow. Anyway, it's good to hear you're still on it. What's the estimated time you think it'll be ready to get patchedin? > > The system will manage a stack, remembering nested > > states of the effective user id. Calls through the > > function manager can switch for- and backward to > > another one, so prosetuid functions will inherit the > > effective permissions of the function (trigger) > > owner. The stack is reinitialized at transaction > > aborts. > > This is definitely necessary, but it needs to be more general. There is a > command SET SESSION AUTHORIZATION, which is essentially `su', that could > make use of this also. I see - a session level userid switch. Should this one affect the setting of realuid as well or not? And must it be possible from inside a function (or whatever) and then get rolled back if the function returns? Shouldit stay permanent otherwise if issued from inside a function? The thing users actually complain about is the requirement of UPDATE permissions to REFERENCE a table. This could be fixed with making RI triggers setuid functions for 7.1 and check that the user at least has SELECT permission on the referenced table during constraint creation. This would also remove the actual DOS problem, thata user could potentiall create a referencing table and not giving anyone who can update the referenced oneupdate permissions on it too. I think it's worth doing it now, and couple it later with your general access control things. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
At 20:41 25/07/00 +0200, Jan Wieck wrote: >Philip Warner wrote: >> At 15:27 25/07/00 +0200, Jan Wieck wrote: >> > >> INHERIT Do you need this? > > What other rights must a user have on the inherited relations > to work properly with them? > I have no idea, I only suggested this because it's a feature that is easily overlooked, and not part of most DBs, so we may need to thin about it... The SQL standard also has a 'GRANT...WITH HIERARCHY' option that grants access on all subtables. FWIW, the SQL standard also defines a 'USAGE' priv that grants access to domains, character sets, UDTs etc. >> > System Privileges >> > >> > System Privileges are to grant permission to execute DDL- >> > statements or for database wide Object permissions (valid >> > for all objects of a particular kind). >> > >> > SUPERUSER A special System Privilege, >> > superseding any other rights. What >> > the holder of this right want's to >> > do, he does. It is the same as now, >> > usesuper in pg_shadow. >> >> I suspect this is good grounds for a religious war, but I like a priv >> system where I have to 'turn on' a super privilege before I get it. If I am >> a superuser, I don't want my cape flapping in the breeze *all* the time. >> Can you add some kind of 'CLARK_KENT' priv (ie. 'can become superuser')? >> And have SUPERUSER off at the beginning of all sessions? >> >> There are two reasons I think this is important: 1) I am accident prone, >> and 2) it's good to live like a mortal most of the time - you get to see >> problems before a user complains. > > If you don't need DBA privileges, don't log on as a DBA. Have > a separate account for that (IMHO). I guess if there is an 'ALTER SESSION SET AUTHORIZATION <superuser-name>' or alternatively, 'SET ROLE <rolename>', then I'll be happy, since I could define a 'superuser/dba' role. I think there is a need for one or more users to have superuser-like access to a single DB, but have little or no access to other ones. The suggestion above would allow a normal user to be superuser for single database, without having to set up (potentially) a separate DBA account for each database. >> > CREATE TABLE >> > ALTER ANY TABLE >> > DROP ANY TABLE >> > INSERT ANY TABLE >> > UPDATE ANY TABLE >> > DELETE ANY TABLE >> > SELECT ANY TABLE >> > LOCK ANY TABLE >> > REFERENCE ANY TABLE >> > CREATE SEQUENCE >> > ALTER ANY SEQUENCE >> > DROP ANY SEQUENCE >> >> This seems like overkill; you will need a new priv for every object type. >> It is also not clear how 'ALTER ANY TABLE' should interact with 'ALTER >> TABLE (specific table)', but I assume the more specific priv rules. > > As I said, it should be fine grained. If a DBA wants some > user to be able to create views, but not his own tables, > functions etc., how could he if there aren't separate > privileges for the single actions? > > The interactions will be hardwired in the pg_check_priv() > function. Since the requested privilege is a #define'd > constant, it'll be more or less a big switch statement, > calling a single privilege lookup helper once in a while. Maybe I was confusing implementation with interface, but what I was suggesting (in a confused sort of way) was that you could define 'objects': TABLE, TRIGGER, SEQUENCE, COLUMN..., and 'privileges': ALTER, DROP, CREATE, UPDATE, DELETE, SELECT etc etc. Then privs can be granted on objects, so the number of #defines only equals the number of separate privs, not the number of privs times the number of object types. Maybe I just misunderstood your plans? Did you mean that 'LOCK ANY TABLE' would be a priv granted at the database level, schema level, or really at the system level? >> It seems that this is just a way of defining 'default' privs for an object >> that does not have an ACL, and if that is the case, why not define a >> default protection at both the database level and the object-type level >> (perhaps in the relevant pg_* table?). Certainly it seems that 'CREATE >> TABLE' could be represented as 'INSERT' priv on the pg_class table etc. > > No. They are meant as user or group specific privileges. > > By default, only the owner has access to his tables. He (or a > superuser) must explicitly GRANT other users or groups access > to it. But a user with SELECT ANY TABLE can do so from the > start, because the DBA decided that this user act's like a > superuser if issuing some SELECT database wide. So to use the above object/priv model, 'GRANT "CREATE ANY TABLE" on database <dbname> to fred' might be equivalent to 'GRANT "INSERT" on OBJECT TABLES to fred'. I'm not particularly attached to my suggestion, but you can achieve granularity without lots of priv named to remember. >> > >> > If a function is called through the fmgr (any user >> > defined function is), the function manager honours >> > these flags. Prosetuid will cause the function >> > manager to switch to another effective user id, used >> > during pg_check_perms() for the time of the function >> > invocation. >> >> Wonderful! I've been hoping for this for a while. > > You never walk alone. That's what I tell my dog. Bye for now, Philip. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
This is an alpha version of NetBSD 1.5. System is a fairly stock PowerMac 7500 with a 604 CPU upgrade, original disk drive. The Perl is from the NetBSD package system, version 5.004_04. egcs-1.1.2 (as modified by BSD). postgresql 7.0.2 ./configure --prefix=/usr/local --with perl --with-x First problem: ODBC doesn't compile. |-( Second problem: with elf binaries the location of shared libraries is supposed to be compiled in so each program can reference its own set of shared objects without conflict. This doesn't seem to jibe with how the build/install makefiles do things. I get missing shared libraries when I try to access the DB as a normal user, and the mechanism I would have used to fix this on an xcoff system is deleted, or at least deprecated, on an elf system. There is a package for postgres and it seems to do something different w.r.t. shared libraries. OTOH I can't get the regression tests to run with the package even though psql works fine. (The error is 'installing PL/pgSQL .. createlang: cannot find the file /usr/pkg/share/postgresql/plpgsql.so'.) Third problem: well actually the regression tests seem to work, mostly. ;-) Regression results:geometry failed with a number of roundoff differences and a -0 difference.opr_sanity, type_sanity, oidjoins, and rules all failed with the build having error messages where some kind of results were expected.All other tests passed (including random?!?!). If Tom L. wants to beat on this I can give him an account on the machine. I've used up the time I have to devote to it. I'm pretty much stuck with 6.4.0 on Solaris 2.5 for real work. Signature failed Preliminary Design Review. Feasibility of a new signature is currently being evaluated. h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu
> This is an alpha version of NetBSD 1.5.
> System is a fairly stock PowerMac 7500 with a 604 CPU upgrade,
> original disk drive. The Perl is from the NetBSD package system,
> version 5.004_04. egcs-1.1.2 (as modified by BSD).
> postgresql 7.0.2
> ./configure --prefix=/usr/local --with perl --with-x
> First problem: ODBC doesn't compile. |-(
What is the symptom?
> Second problem: with elf binaries the location of shared libraries
> is supposed to be compiled in so each program can reference its own
> set of shared objects without conflict. This doesn't seem to jibe
> with how the build/install makefiles do things. I get missing shared
> libraries when I try to access the DB as a normal user, and the
> mechanism I would have used to fix this on an xcoff system is
> deleted, or at least deprecated, on an elf system.
There was some discussion recently about whether -rpath is A Good Thing,
with Debian voting that it is One Step From Hell. I've always just
modified my ld.config and/or my LD_LIBRARY_PATH environment to get to
the libraries.
Either of these should solve your regression test problem.
> Third problem: well actually the regression tests seem to work, mostly. ;-)
> If Tom L. wants to beat on this I can give him an account on the
> machine. I've used up the time I have to devote to it. I'm pretty
> much stuck with 6.4.0 on Solaris 2.5 for real work.
Well, 7.0.x *will* run on Solaris, but afaik it should do fine on your
NetBSD box too. Try the above fixups and let me know if you need more.
- Thomas
>> If Tom L. wants to beat on this I can give him an account on the
>> machine. I've used up the time I have to devote to it. I'm pretty
>> much stuck with 6.4.0 on Solaris 2.5 for real work.
> Well, 7.0.x *will* run on Solaris, but afaik it should do fine on your
> NetBSD box too. Try the above fixups and let me know if you need more.
I don't claim to be either a Solaris or NetBSD guru --- I run HPUX
myself and have access to bog-standard Sun BSD systems. But if you
want some help I'll see what I can do.
The details of the regress differences would help, to start with...
regards, tom lane
PS: Lockhart might be more nearly on-site for you, though. JPL is
a long way from Pittsburgh.
> PS: Lockhart might be more nearly on-site for you, though. JPL is
> a long way from Pittsburgh.
*rolf*
Henry and I work at the same place, as you probably noticed. I had
helped a bit with another machine of his some time ago (he seems to have
a fondness for old Macs; it reminded me that a Centris/Quadra machine is
*really slow* by today's standards, and that it would be best to leave
mine in the closet).
And he has been around Postgres long enough to think that "Tom L." is
someone other than Tom Lane. I had been tempted to mention this in my
previous reply, but thought it might make it past unnoticed :)
- Thomas
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> And he has been around Postgres long enough to think that "Tom L." is
> someone other than Tom Lane.
Um, good point. I've kind of internalized our Tom/Thomas convention
but I suppose not everybody has.
(I had second thoughts about getting involved in this project when
I realized there was already a "tgl" hanging about the premises ;-).
Confusion is pretty much guaranteed...)
regards, tom lane
On Tue, 25 Jul 2000, Jan Wieck wrote: > Karel Zak wrote: > > > > I not sure, but if I good remember nobody said somethig bad about > > PetreE proposal for this, why you prepare new? IMHO Peter's proposal > > was good. > > Seems I missed that discussion. Sometimes I start to drop > incoming eMails by subject. If then the discussion moves to > something different without changing the subject, you won't > see me on that. > > Anyway, I haven't found a complete proposal in the ML I (mostly) have found nothing in PG's mail lists archive :-( better is use: http://www.deja.com/[ST_rn=fs]/group/mailing.database.pgsql-hackers > archive. Consider my proposal "derived work" from his one, > if it is similar and let's combine all the ideas into one > complete thing. I mean will good if Peter re-posts his proposal. IMHO is not a problem select feature for GRANT, a problem is implement it and implement it like SQL92. > > And small suggestion, we need the "GRANT ... WITH ADMIN OPTION" or > > something like this. > > What should that do? --- See the chapter "11.36 <grant statement>" in the SQL92 (and others parts of this standard). SQL92: <grant statement> ::= GRANT <privileges> ON <object name> TO <grantee> [ { <comma> <grantee>}... ] [ WITH GRANT OPTION ] --- "WITH ADMIN OPTION" is Oracle matter, and Oracle's manual say: ".. allows the grantee to grant the object privileges to the other user and role..."other words you can create "sub-admin"for the object, and this user can GRANT privilege to the other standard users.It is pretty well implement-able if all privilege will in one system table (pg_privilege). I mean that is not good "dirty" other system tables. The other point --- we must keep open a door to others SQL administration features like ROLE, PROFILE. IMHO final proposal should be contain some idea for group/shadow rewriting and some idea about ROLE. Ops.. I forget, we *must* in new ACL have columns privilege. It is realy needful in large multi-user applications. A crash point will seed :-) Karel
I just had a go on NetBSD-1.5C/i386 with a fresh cvs - first time since the
new autoconf stuff..
On Wed, Jul 26, 2000 at 04:02:33AM +0000, Thomas Lockhart wrote:
> > First problem: ODBC doesn't compile. |-(
I didn't try to compile odbc..
> > Second problem: with elf binaries the location of shared libraries
> > is supposed to be compiled in so each program can reference its own
> > set of shared objects without conflict. This doesn't seem to jibe
> > with how the build/install makefiles do things. I get missing shared
> > libraries when I try to access the DB as a normal user, and the
> > mechanism I would have used to fix this on an xcoff system is
> > deleted, or at least deprecated, on an elf system.
>
> There was some discussion recently about whether -rpath is A Good Thing,
> with Debian voting that it is One Step From Hell. I've always just
> modified my ld.config and/or my LD_LIBRARY_PATH environment to get to
> the libraries.
>
> Either of these should solve your regression test problem.
As it happens my LD_LIBRARY_PATH is always empty and there is no ldconfig on
my system, the standard ld.so.conf file on i386 (elf) being
libm.so.0 machdep.fpu_present 1:libm387.so.0,libm.so.0
So, any chance of putting -rpath in? Without it you end up with:
% ldd psql
psql: -lpq.2 => not found -lz.0 => /usr/lib/libz.so.0 -lcrypt.0 => /usr/lib/libcrypt.so.0
-lm.0=> /usr/lib/libm387.so.0 -lm.0 => /usr/lib/libm.so.0 -lutil.5 => /usr/lib/libutil.so.5
-ltermcap.0=> /usr/lib/libtermcap.so.0 -lcurses.3 => /usr/lib/libcurses.so.3 -lc.12 =>
/usr/lib/libc.so.12
Then I need to relink psql with -Wl,-R/usr/local/pgsql/lib ...
> > Third problem: well actually the regression tests seem to work, mostly. ;-)
Where they the parallel regression tests? Does "unlimit maxproc" help? (I
usually forget to do this and maxproc=80 isn't enough for me)
All the tests bar geometry work for me, but the latter has:
--- results/geometry.out Wed Jul 26 16:01:12 2000
***************
*** 294,307 **** (8 rows) SELECT '' AS four, path(f1) FROM POLYGON_TBL;
! four | path
! ------+---------------------
! | ((2,0),(2,4),(0,0))
! | ((3,1),(3,3),(1,0))
! | ((0,0))
! | ((0,1),(0,1))
! (4 rows)
! -- translation SELECT '' AS eight, p1.f1 + point '(10,10)' AS dist_add FROM PATH_TBL p1;
--- 294,300 ---- (8 rows) SELECT '' AS four, path(f1) FROM POLYGON_TBL;
! ERROR: parser: parse error at or near "(" -- translation SELECT '' AS eight, p1.f1 + point '(10,10)' AS dist_add
FROMPATH_TBL p1;
***************
in amongst the rounding errors..
Cheers,
Patrick
> So, any chance of putting -rpath in?
istm that it would be acceptable to have --enable-rpath or something
similar available in ./configure.
I recall one big problem with this on my Linux boxen: g++ (and
presumably gcc) checks for the *existance* of the rpath directory during
the linking stage, and barfs if it does not exist. This is incompatible
with building in one place, then installing in another, which is
probably why package builders in particular conclude that it is The
Wrong Thing.
> > > Third problem: well actually the regression tests seem to work, mostly. > SELECT '' AS four, path(f1) FROM
POLYGON_TBL;
> ! ERROR: parser: parse error at or near "("
Ah, I've been wrestling with upgrading my home machine (note: Mandrake
falls down if you have a SCSI-only system and try *any* kind of upgrade
:(( but I do have patches to fix this. Will commit asap.
- Thomas
On Wed, Jul 26, 2000 at 03:55:27PM +0000, Thomas Lockhart wrote: > > So, any chance of putting -rpath in? > > istm that it would be acceptable to have --enable-rpath or something > similar available in ./configure. > > I recall one big problem with this on my Linux boxen: g++ (and > presumably gcc) checks for the *existance* of the rpath directory during > the linking stage, and barfs if it does not exist. This is incompatible > with building in one place, then installing in another, which is > probably why package builders in particular conclude that it is The > Wrong Thing. *existance* before installation! wow! I would have expected -Lnot_yet_installed_directory -Wl,-Rwhere_the_libs_will_end_up -lfoo Anyway, some sort of flag would be good.. Cheers, Patrick
At 4:25 PM +0100 7/26/00, Patrick Welche wrote:
>I just had a go on NetBSD-1.5C/i386 with a fresh cvs - first time since the
>new autoconf stuff..
They cut 1.5 alpha less than a month ago and they are already up to
1.5C on the development branch? Geesh!
>On Wed, Jul 26, 2000 at 04:02:33AM +0000, Thomas Lockhart wrote:
> > > First problem: ODBC doesn't compile. |-(
>
>I didn't try to compile odbc..
I'll redo this one and email Tom L., er, TGL, er, Thomas the compile log.
> > > Second problem: with elf binaries the location of shared libraries
> > > is supposed to be compiled in so each program can reference its own
> > > set of shared objects without conflict. This doesn't seem to jibe
> > > with how the build/install makefiles do things. I get missing shared
> > > libraries when I try to access the DB as a normal user, and the
> > > mechanism I would have used to fix this on an xcoff system is
> > > deleted, or at least deprecated, on an elf system.
> >
> > There was some discussion recently about whether -rpath is A Good Thing,
> > with Debian voting that it is One Step From Hell. I've always just
> > modified my ld.config and/or my LD_LIBRARY_PATH environment to get to
> > the libraries.
> >
> > Either of these should solve your regression test problem.
>
>As it happens my LD_LIBRARY_PATH is always empty and there is no ldconfig on
>my system, the standard ld.so.conf file on i386 (elf) being
>libm.so.0 machdep.fpu_present 1:libm387.so.0,libm.so.0
On port-macppc there is no ld.so.conf file. Maybe I should post this
question on the NetBSD lists, though it seems more related to ELF
than to *BSD.
>So, any chance of putting -rpath in? Without it you end up with:
>
>% ldd psql
>psql:
> -lpq.2 => not found
> -lz.0 => /usr/lib/libz.so.0
> -lcrypt.0 => /usr/lib/libcrypt.so.0
> -lm.0 => /usr/lib/libm387.so.0
> -lm.0 => /usr/lib/libm.so.0
> -lutil.5 => /usr/lib/libutil.so.5
> -ltermcap.0 => /usr/lib/libtermcap.so.0
> -lcurses.3 => /usr/lib/libcurses.so.3
> -lc.12 => /usr/lib/libc.so.12
>
>Then I need to relink psql with -Wl,-R/usr/local/pgsql/lib ...
FWIW:
macbsd: {3} ldd /usr/pkg/bin/psql
/usr/pkg/bin/psql: -lcrypt.0 => /usr/lib/libcrypt.so.0 -lc.12 => /usr/lib/libc.so.12 -lpq.2 =>
/usr/pkg/lib/libpq.so.2 -lm.0 => /usr/lib/libm.so.0 -lutil.5 => /usr/lib/libutil.so.5
-lreadline.4=> /usr/pkg/lib/libreadline.so.4 -ltermcap.0 => /usr/lib/libtermcap.so.0
macbsd: {4} ldd /usr/local/bin/psql
/usr/local/bin/psql: -lpq.2 => not found -lcrypt.0 => /usr/lib/libcrypt.so.0 -lm.0 =>
/usr/lib/libm.so.0 -lutil.5 => /usr/lib/libutil.so.5 -ltermcap.0 => /usr/lib/libtermcap.so.0
-lcurses.3=> /usr/lib/libcurses.so.3 -lc.12 => /usr/lib/libc.so.12
We could go through the package patches to see what they changed to
"fix" the problem if necessary. When built directly into /usr/local
gmake runcheck works and gmake runtest fails (as does initdb, etc).
When built using the package system most normal operations seem OK,
but gmake runtest and runcheck both fail.
=============== installing languages... =================
installing PL/pgSQL .. createlang: missing required argument PGLIB directory
(This is the directory where the interpreter for the procedural
language is stored. Traditionally, these are installed in whatever
'lib' directory was specified at configure time.)
failed
> > > Third problem: well actually the regression tests seem to
>work, mostly. ;-)
>
>Where they the parallel regression tests? Does "unlimit maxproc" help? (I
>usually forget to do this and maxproc=80 isn't enough for me)
Tried it. No change. I did have to stop the running postmaster to
do a gmake runcheck or it fails because it can't get enough shared
memory. I'm running an unoptimized generic kernel.
>All the tests bar geometry work for me, but the latter has:
>! ERROR: parser: parse error at or near "("
>in amongst the rounding errors..
I don't get that error. I admit I didn't look exhaustively for
non-rounding errors in the diff.
Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu
On Wed, Jul 26, 2000 at 02:20:11PM -0700, Henry B. Hotz wrote:
> At 4:25 PM +0100 7/26/00, Patrick Welche wrote:
>
> >As it happens my LD_LIBRARY_PATH is always empty and there is no ldconfig on
> >my system, the standard ld.so.conf file on i386 (elf) being
> >libm.so.0 machdep.fpu_present 1:libm387.so.0,libm.so.0
>
> On port-macppc there is no ld.so.conf file. Maybe I should post this
> question on the NetBSD lists, though it seems more related to ELF
> than to *BSD.
>
> >So, any chance of putting -rpath in? Without it you end up with:
> >
> >% ldd psql
> > -lpq.2 => not found
...
> >Then I need to relink psql with -Wl,-R/usr/local/pgsql/lib ...
... abridged
> macbsd: {3} ldd /usr/pkg/bin/psql
> /usr/pkg/bin/psql:
> -lpq.2 => /usr/pkg/lib/libpq.so.2
> /usr/local/bin/psql:
> -lpq.2 => not found
>
> We could go through the package patches to see what they changed to
> "fix" the problem if necessary. When built directly into /usr/local
> gmake runcheck works and gmake runtest fails (as does initdb, etc).
> When built using the package system most normal operations seem OK,
> but gmake runtest and runcheck both fail.
The package probably slaps the -Wl,-R/usr/pkg/lib in. The point of the
above is "use LD_LIBRARY_PATH or ldconfig" isn't a solution for us lot,
the -Wl,-R (or -rpath) is needed (say configure --not-Debian or something ;)
> > > > Third problem: well actually the regression tests seem to
> >work, mostly. ;-)
> >
> >Where they the parallel regression tests? Does "unlimit maxproc" help? (I
> >usually forget to do this and maxproc=80 isn't enough for me)
>
> Tried it. No change. I did have to stop the running postmaster to
> do a gmake runcheck or it fails because it can't get enough shared
> memory. I'm running an unoptimized generic kernel.
Fair enough - same here. The maxproc business for me would be that a new
backend can't be forked for a given test => it doesn't output anything
other than an error message => the test fails. What sort of errors are
you getting?
Cheers,
Patrick
At 10:46 AM +0100 7/27/00, Patrick Welche wrote: >The package probably slaps the -Wl,-R/usr/pkg/lib in. The point of the >above is "use LD_LIBRARY_PATH or ldconfig" isn't a solution for us lot, >the -Wl,-R (or -rpath) is needed (say configure --not-Debian or something ;) Shouldn't there be an option to disable shared libraries altogether? I don't see that they buy us much in most real scenarios. Every copy of the postmaster already shares the same complete executable image by default. (Actually that bit of smarts was built into old Unix Version 6 or earlier.) That's actually a bigger savings and you get it without the run-time overhead of shared libraries. If we have large numbers of clients on the same machine they probably share the same executable image as well. > > > > > Third problem: well actually the regression tests seem to > > >work, mostly. ;-) > > > > > >Where they the parallel regression tests? Does "unlimit maxproc" help? (I > > >usually forget to do this and maxproc=80 isn't enough for me) > > > > Tried it. No change. I did have to stop the running postmaster to > > do a gmake runcheck or it fails because it can't get enough shared > > memory. I'm running an unoptimized generic kernel. > >Fair enough - same here. The maxproc business for me would be that a new >backend can't be forked for a given test => it doesn't output anything >other than an error message => the test fails. What sort of errors are >you getting? I emailed the results files for the failed tests to Thomas L. (but not Tom L.). If anyone else wants copies just ask. Signature failed Preliminary Design Review. Feasibility of a new signature is currently being evaluated. h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu
> Karel Zak wrote: > > > > I not sure, but if I good remember nobody said somethig bad about > > PetreE proposal for this, why you prepare new? IMHO Peter's proposal > > was good. > > Seems I missed that discussion. Sometimes I start to drop > incoming eMails by subject. If then the discussion moves to > something different without changing the subject, you won't > see me on that. > > > > > > You know, I have started dropping e-mails with lots of blank lines on the end. :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Jan Wieck writes: > Anyway, it's good to hear you're still on it. What's the > estimated time you think it'll be ready to get patched in? Next release. I would hope we can get the current stuff into beta in a month or so, whereas this project would break open a lot of things. > The thing users actually complain about is the requirement of > UPDATE permissions to REFERENCE a table. This could be fixed > with making RI triggers setuid functions for 7.1 and check > that the user at least has SELECT permission on the > referenced table during constraint creation. This would also > remove the actual DOS problem, that a user could potentiall > create a referencing table and not giving anyone who can > update the referenced one update permissions on it too. > > I think it's worth doing it now, and couple it later with > your general access control things. True. I had already looked into this, it's not fundamentally difficult, but there's a lot of code that will need to be touched. If you want to go for it, be my guest; I agree that it is fairly orthogonal to the rest of the privilege system. I'll put it on my priority list if no one's taking it. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden