Re: PostgreSQL reference manual

I have another question about GRANT/REVOKE:

grant <privilege[,privilege,...]>
on <rel1>[,...<reln>]
to [public | GROUP <group> | <username>]
^^^^^^^^^^^^^
I don't know how to create a GROUP ?

I believe that you use "CREATE USER groupname", and then can assign
privileges to that pseudo-user/group, and then add users to that
group. Have you tried that?

postgres=> create user grupo;
CREATE USER
postgres=> grant all on tmp to grupo;
CHANGE
create user joe in group grupo;
CREATE USER
postgres=> grant select on tmp to group grupo;
ERROR: non-existent group "grupo"

Can someone tell us how "groups" work? I'm not finding enough clues just
by looking in the parser, and haven't stumbled across it in the docs...

Once a group is manually created with insert into pg_group values ...
it can be used by the create user, alter user and grant to group syntax.
A create group <groupname> is still missing in the grammar,
it would simply need to do the insert. Groups are very helpful with
many users that all need similar grants. You grant rights to the group
and then grant groups to users. The ANSI92 naming is "role",
if we enhance the group stuff maybe it would be good to convert to the
"role" naming of group code, althougth I like "group" more.

Andreas

Zeugswetter Andreas wrote:

Once a group is manually created with insert into pg_group values ...
it can be used by the create user, alter user and grant to group syntax.
A create group <groupname> is still missing in the grammar,

I'm working on this.. Should have something working around the end of
this week (for ALTER USER and CREATE USER).

it would simply need to do the insert. Groups are very helpful with
many users that all need similar grants. You grant rights to the group
and then grant groups to users. The ANSI92 naming is "role",

Oracle calls it "role" and Sybase calls it "group".

--
Stephane Lajeunesse.
Oracle and Sybase DBA

Stephane Lajeunesse writes:

A create group <groupname> is still missing in the grammar,

I'm working on this.. Should have something working around the end of
this week (for ALTER USER and CREATE USER).

Please let me use this to tell you all that I would like to get notice of
each change to gram.y. I am currently modelling ecpg's parser after gram.y
to get good syntax checking. So I have to make these changes, too.

Michael

--
Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10

On Mon, 23 Mar 1998, Zeugswetter Andreas wrote:

I have another question about GRANT/REVOKE:

grant <privilege[,privilege,...]>
on <rel1>[,...<reln>]
to [public | GROUP <group> | <username>]
^^^^^^^^^^^^^
I don't know how to create a GROUP ?

I believe that you use "CREATE USER groupname", and then can assign
privileges to that pseudo-user/group, and then add users to that
group. Have you tried that?

postgres=> create user grupo;
CREATE USER
postgres=> grant all on tmp to grupo;
CHANGE
create user joe in group grupo;
CREATE USER
postgres=> grant select on tmp to group grupo;
ERROR: non-existent group "grupo"

Can someone tell us how "groups" work? I'm not finding enough clues just
by looking in the parser, and haven't stumbled across it in the docs...

Once a group is manually created with insert into pg_group values ...
it can be used by the create user, alter user and grant to group syntax.
A create group <groupname> is still missing in the grammar,
it would simply need to do the insert. Groups are very helpful with
many users that all need similar grants. You grant rights to the group
and then grant groups to users. The ANSI92 naming is "role",
if we enhance the group stuff maybe it would be good to convert to the
"role" naming of group code, althougth I like "group" more.

I created a group as Andreas said but now psql \z doesn't work anymore
It give me always a segmentation fault ... what's wrong...

postgres=> INSERT INTO pg_group VALUES ('tutti',200);
INSERT 318273 1

postgres=> CREATE USER jose IN GROUP tutti;
CREATE USER
SELECT * FROM pg_group;

groname|grosysid|grolist
-------+--------+-------
tutti | 200|
(1 row)

postgres=> GRANT ALL ON temp TO GROUP tutti;
CHANGE

postgres=> \z
$ Segmentation fault
jose'

Stephane Lajeunesse writes:

A create group <groupname> is still missing in the grammar,

I'm working on this.. Should have something working around the end of
this week (for ALTER USER and CREATE USER).

Please let me use this to tell you all that I would like to get notice of
each change to gram.y. I am currently modelling ecpg's parser after gram.y
to get good syntax checking. So I have to make these changes, too.

Good idea on telling you of each change, but I also recommend that every
time you update the ecpg grammer, you save a copy the gram.y that you
used to do it, so later when you need to get it back in sync, you can do
a diff on the old and new one to see each change so you don't miss any.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Bruce Momjian writes:

Stephane Lajeunesse writes:

A create group <groupname> is still missing in the grammar,

I'm working on this.. Should have something working around the end of
this week (for ALTER USER and CREATE USER).

Please let me use this to tell you all that I would like to get notice of
each change to gram.y. I am currently modelling ecpg's parser after gram.y
to get good syntax checking. So I have to make these changes, too.

Good idea on telling you of each change, but I also recommend that every
time you update the ecpg grammer, you save a copy the gram.y that you
used to do it, so later when you need to get it back in sync, you can do
a diff on the old and new one to see each change so you don't miss any.

Consider also not updateing the grammar. The strength of PostgreSQL is that
functions can be added to work inside the server. These functions can often
do whatever is being proposed as new syntax.

So, instead of cluttering up the grammar with non-standard SQLish stuff
to handle things like groups, just create an administrative function to
do this job.

* return create_group('groupname');
* return add_user_to_group('groupname', 'username');
* return drop_group('groupname');

These can be written in C, in SQL, or what ever far more quickly and with
much less risk of destabilizing the system than the parser can be modified.
It also avoids making incompatibility with ecpg.

And, in keeping with the recent anti-bloat thread, these can be loadable
extensions, not part of the core. So if you don't use groups, you don't pay
for them.

-dg

David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
- Linux. Not because it is free. Because it is better.

I have another question about GRANT/REVOKE:

grant <privilege[,privilege,...]>
on <rel1>[,...<reln>]
to [public | GROUP <group> | <username>]
^^^^^^^^^^^^^
I don't know how to create a GROUP ?

I believe that you use "CREATE USER groupname", and then can assign
privileges to that pseudo-user/group, and then add users to that
group. Have you tried that?

postgres=> create user grupo;
CREATE USER

No, do this: insert into pg_group values ('grupo', 100, '{6}');

postgres=> grant all on tmp to grupo;
CHANGE
create user joe in group grupo;

looks like this is ignored ?

CREATE USER
postgres=> grant select on tmp to group grupo;
ERROR: non-existent group "grupo"

Can someone tell us how "groups" work? I'm not finding enough clues just
by looking in the parser, and haven't stumbled across it in the docs...

I have no idea what the grosysid is supposed to be, I only notice, that 100 works while 18204
crashes psql. To be consistent with pg_user I think it should hold the unix group id,
if the group also exists in /etc/groups. If not, from what I see in the sources
it must still be unique, see src/backend/catalog/aclchk.c ** this code is a little mess really **
The field grolist has to be manually maintained currently. It contains an
array of usesysid's of the users in this group. (select usesysid from pg_user where usename='joe';)

Andreas

Bruce Momjian writes:

Good idea on telling you of each change, but I also recommend that every
time you update the ecpg grammer, you save a copy the gram.y that you
used to do it, so later when you need to get it back in sync, you can do
a diff on the old and new one to see each change so you don't miss any.

Tom had the same idea. I wonder if you ever used that before. :-)

Anyway, this is no doubt a very good idea. Thanks.

Michael

--
Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10

David Gould writes:

Consider also not updateing the grammar. The strength of PostgreSQL is that
functions can be added to work inside the server. These functions can often
do whatever is being proposed as new syntax.

So you want me to not check the syntax while parsing the embedded SQL code?

So, instead of cluttering up the grammar with non-standard SQLish stuff
to handle things like groups, just create an administrative function to
do this job.

* return create_group('groupname');
* return add_user_to_group('groupname', 'username');
* return drop_group('groupname');

But this is not embedded SQL.

These can be written in C, in SQL, or what ever far more quickly and with
much less risk of destabilizing the system than the parser can be modified.
It also avoids making incompatibility with ecpg.

Okay, but I should at least check for ANSI SQL.

Michael

--
Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10

Bruce Momjian writes:

Good idea on telling you of each change, but I also recommend that every
time you update the ecpg grammer, you save a copy the gram.y that you
used to do it, so later when you need to get it back in sync, you can do
a diff on the old and new one to see each change so you don't miss any.

Tom had the same idea. I wonder if you ever used that before. :-)

Anyway, this is no doubt a very good idea. Thanks.

I believe I suggested it to the OpenLink people who mirror our libpq
changes, and to the person who keeps the Japanese version of the FAQ.

Really the only way to do it.
-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

David Gould writes:

Consider also not updateing the grammar. The strength of PostgreSQL is that
functions can be added to work inside the server. These functions can often
do whatever is being proposed as new syntax.

So you want me to not check the syntax while parsing the embedded SQL code?

What I think we was suggesting is that we add non-ANSI functionality as
function calls rather than grammer changes with keywords. The only
disadvantage is that it is a little more cumbersom, and less intuitive
for users.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

David Gould writes:

Consider also not updateing the grammar. The strength of PostgreSQL is that
functions can be added to work inside the server. These functions can often
do whatever is being proposed as new syntax.

So you want me to not check the syntax while parsing the embedded SQL code?

What I think we was suggesting is that we add non-ANSI functionality as
function calls rather than grammer changes with keywords. The only
disadvantage is that it is a little more cumbersom, and less intuitive
for users.

but it ** is ** ANSI functionality, look under "role" (with an O)

Andreas

Andreas:

David Gould writes:

Consider also not updateing the grammar. The strength of PostgreSQL is that
functions can be added to work inside the server. These functions can often
do whatever is being proposed as new syntax.

So you want me to not check the syntax while parsing the embedded SQL code?

What I think we was suggesting is that we add non-ANSI functionality as
function calls rather than grammer changes with keywords. The only
disadvantage is that it is a little more cumbersom, and less intuitive
for users.

but it ** is ** ANSI functionality, look under "role" (with an O)

Ok, but are we using the ANSI syntax? If so, then I withdraw my objection.
But, if we are adding ANSI functionality with UNIQUE syntax, then why bother
hacking the parser since the functionality can be added with functions.

-dg

David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
- Linux. Not because it is free. Because it is better.

but it ** is ** ANSI functionality, look under "role" (with an O)

Ok, but are we using the ANSI syntax? If so, then I withdraw my
objection. But, if we are adding ANSI functionality with UNIQUE
syntax, then why bother hacking the parser since the functionality can
be added with functions.

We don't have a goal of implementing unique syntax *just because*,
although it may look that way from time to time. If the syntax can be
made compliant without damaging the functionality, we will make it SQL92
compatible (or compatible with whatever standard makes sense).

btw, this brings up a question:

The MySQL bunch have included some syntax in their "crash-me" test which
is _not_ SQL92 compliant, including hex constants specified as

0x0F

(for decimal 15, assuming I've done the conversion right :). They claim
that this is required by the ODBC standard, whatever that is. What is
the relationship between the two? Isn't ODBC a client interface, not
necessarily dealing with SQL directly but rather with common SQLish
functionality? In cases where SQL92 and ODBC conflict, how do systems
resolve the differences? For this case, SQL92 clearly defines the syntax
as

x'0F'

In this particular case it will be easy to implement this ODBC syntax in
the scanner, but I don't want to jerk it around too much if it a bogus
issue :(

- Tom

Thomas G. Lockhart wrote:

but it ** is ** ANSI functionality, look under "role" (with an O)

Ok, but are we using the ANSI syntax? If so, then I withdraw my
objection. But, if we are adding ANSI functionality with UNIQUE
syntax, then why bother hacking the parser since the functionality can
be added with functions.

We don't have a goal of implementing unique syntax *just because*,
although it may look that way from time to time. If the syntax can be
made compliant without damaging the functionality, we will make it SQL92
compatible (or compatible with whatever standard makes sense).

btw, this brings up a question:

The MySQL bunch have included some syntax in their "crash-me" test which
is _not_ SQL92 compliant, including hex constants specified as

0x0F

(for decimal 15, assuming I've done the conversion right :). They claim
that this is required by the ODBC standard, whatever that is. What is
the relationship between the two? Isn't ODBC a client interface, not
necessarily dealing with SQL directly but rather with common SQLish
functionality? In cases where SQL92 and ODBC conflict, how do systems
resolve the differences? For this case, SQL92 clearly defines the syntax
as

x'0F'

Well, far be it for me to want or suggest that we be exactly like
Sybase, but:

1> select 0x0F
2> go

----
0x0f

(1 row affected)
1> select x'0F'
2> go
Msg 207, Level 16, State 2:
Line 1:
Invalid column name 'x'.
1>

Ocie