Re: [QUESTIONS] groups of users

Just tried this out, and we have a bug here:

simply not implemented, not a bug.

template1=> create user tester in group pg_user;
CREATE USER

so "pg_user" is supposed to be a new group name (not a good name)
The group "pg_user" must already exist. But since the "in group" clause
is currently ignored, no error shows up.

template1=> select * from pg_group;
groname|grosysid|grolist
-------+--------+-------
(0 rows)

template1=> select * from pg_user;
usename|usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
-------+--------+-----------+--------+--------+---------+--------+----------------------------
pgsql | 1005|t |t |t |t |********|Sat Jan 31 02:00:00 2037 AST
scrappy| 10|t |t |t |t |********|
tester | 1006|f |t |f |t |********|
(3 rows)

template1=> insert into pg_group values ('test',0,'{scrappy}');
ERROR: pg_atoi: error in "scrappy": can't parse "scrappy"
template1=> insert into pg_group values ('test',0,'{10}');
INSERT 18497 1

you created a group "test" with one user ("scrappy") as it's only member.
This is currently the only way to do it.

template1=> create user test in group pg_user;
ERROR: defineUser: user "test" has already been created

I think this is because of the SQL92 spec. that user and group names have to be
distinct. (no user and group with same name)

template1=> create user test in group test;
ERROR: defineUser: user "test" has already been created
template1=> select * from pg_user;
usename|usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
-------+--------+-----------+--------+--------+---------+--------+----------------------------
pgsql | 1005|t |t |t |t |********|Sat Jan 31 02:00:00 2037 AST
scrappy| 10|t |t |t |t |********|
tester | 1006|f |t |f |t |********|
(3 rows)

If I do a different usename ('beater'), it creates fine, but doesn't go
anywhere as far as pg_group...

If nobody is working on this area of the code, I'll use it as my personal
starting point into it...just let me know...

I think a "create group" would be a very valuable contribution.
(Make role an alias for group, to be SQL92 conformant)

Andreas

On Wed, 6 May 1998, Andreas Zeugswetter wrote:

Just tried this out, and we have a bug here:

simply not implemented, not a bug.

Then should generate a NOTICE to that effect...right now, its
misleading unless you go and do a select on pg_group to find that it
wasn't actually performed...

As it stands now, it is a bug...

template1=> create user tester in group pg_user;
CREATE USER

so "pg_user" is supposed to be a new group name (not a good name)

Sorry, just picked the first thing that came into my head :)

The group "pg_user" must already exist. But since the "in group" clause
is currently ignored, no error shows up.

Why? if group doesn't exist do:

insert into pg_group values ('groname',max(grosysid)+1,'{values}');

template1=> select * from pg_user;
usename|usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
-------+--------+-----------+--------+--------+---------+--------+----------------------------
pgsql | 1005|t |t |t |t |********|Sat Jan 31 02:00:00 2037 AST
scrappy| 10|t |t |t |t |********|
tester | 1006|f |t |f |t |********|
(3 rows)

template1=> insert into pg_group values ('test',0,'{scrappy}');
ERROR: pg_atoi: error in "scrappy": can't parse "scrappy"
template1=> insert into pg_group values ('test',0,'{10}');
INSERT 18497 1

you created a group "test" with one user ("scrappy") as it's only member.
This is currently the only way to do it.

Unfortunately, the above test was done at home, but here it is
again:

template1=> select * from pg_group;
groname|grosysid|grolist
-------+--------+----------------
pgsql | 0|{10,1044,65534}
banner | 1|{10,65534}
acctng | 2|{0,99,10}
survey | 3|{10,65534,0,206}
(4 rows)

template1=> create user someone in group agroup;
CREATE USER
template1=> select * from pg_group;
groname|grosysid|grolist
-------+--------+----------------
pgsql | 0|{10,1044,65534}
banner | 1|{10,65534}
acctng | 2|{0,99,10}
survey | 3|{10,65534,0,206}
(4 rows)

template1=> create user some in group agroup;
ERROR: defineUser: user "some" has already been created
template1=>

There is no group 'some'...it almost looks like its doing a '~*'
match:

template1=> select usename from pg_user;
usename
--------
scrappy
neil
nobody
darchell
adrenlin
julie
bigtech
news
acctng
root
salesorg
someone
(12 rows)

Just tried this out, and we have a bug here:

simply not implemented, not a bug.

Then should generate a NOTICE to that effect...right now, its
misleading unless you go and do a select on pg_group to find that it
wasn't actually performed...

As it stands now, it is a bug...

Yes, I think it should scan pg_group for a valid group name. I would not make it
create a group, since this might be a simple typo.

template1=> create user tester in group pg_user;
CREATE USER

< snipped my bad mood comment here :-) >

The group "pg_user" must already exist. But since the "in group" clause
is currently ignored, no error shows up.

Why? if group doesn't exist do:

insert into pg_group values ('groname',max(grosysid)+1,'{values}');

This again I would not do to avoid typo's.
create {role|group} test; -- would be preferable I think

template1=> insert into pg_group values ('test',0,'{10}');
INSERT 18497 1

you created a group "test" with one user ("scrappy") as it's only member.
This is currently the only way to do it.

Unfortunately, the above test was done at home, but here it is
again:

template1=> select * from pg_group;
groname|grosysid|grolist
-------+--------+----------------
pgsql | 0|{10,1044,65534}
banner | 1|{10,65534}
acctng | 2|{0,99,10}
survey | 3|{10,65534,0,206}
(4 rows)

Does anybody know what grosysid is supposed to be ? I think it is checked
against a valid unix group id.
grosysid certainly sounds like in connex with system gid, this could be useful
for "system identified groups", read write permissions on files during load/unload
etc. ...
In the rest of the system tables the pg_group.oid should be used, like for
pg_user or pg_class, or add a field groid.
This certainly needs cleanup.

template1=> create user someone in group agroup;
CREATE USER
template1=> select * from pg_group;
groname|grosysid|grolist
-------+--------+----------------
pgsql | 0|{10,1044,65534}
banner | 1|{10,65534}
acctng | 2|{0,99,10}
survey | 3|{10,65534,0,206}
(4 rows)

template1=> create user some in group agroup;
ERROR: defineUser: user "some" has already been created
template1=>

There is no group 'some'...it almost looks like its doing a '~*'
match:

template1=> select usename from pg_user;
usename
--------
scrappy
neil
nobody
darchell
adrenlin
julie
bigtech
news
acctng
root
salesorg
someone
(12 rows)

This don't sound good. Did you track it down ? Might also be a conflict with
one of the id's.

Andreas