Thread: Serial not nulla
Hello
I am working with a database that has a Index number defined as Serial NOT NULL
I used this because,
1. I want to make sure that when many users login at the same time the Index number won't be repeated.
2. I don't have to increment it by programming (I use ASP)
But now i have a situation that i need to index numbers. For Example i could have a structure like this
INDEX1 - N_SHEET - TOT_SHEET
1 - 1 - 1
2 - 1 - 3
2 - 2 - 3
2 - 3 - 3
N_SHEET and TOT_SHEET are filled by the user (via web) but i need to hold on to the INDEX. And while userA is filling the 3 row if userB loggs in i need to provide the INDEX1 with 3.
Any idea??
Thanks
I am working with a database that has a Index number defined as Serial NOT NULL
I used this because,
1. I want to make sure that when many users login at the same time the Index number won't be repeated.
2. I don't have to increment it by programming (I use ASP)
But now i have a situation that i need to index numbers. For Example i could have a structure like this
INDEX1 - N_SHEET - TOT_SHEET
1 - 1 - 1
2 - 1 - 3
2 - 2 - 3
2 - 3 - 3
N_SHEET and TOT_SHEET are filled by the user (via web) but i need to hold on to the INDEX. And while userA is filling the 3 row if userB loggs in i need to provide the INDEX1 with 3.
Any idea??
Thanks
Shavonne
Shavonne Wijesinghe
www.studioform.it
Le informazioni contenute nella presente comunicazione e i relativi allegati possono essere riservate e sono, comunque destinate esclusivamente alle persone o alla Società sopra indicati.
La diffusione, distribuzione e/o copiature del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dellart. 616 c.p., che ai sensi del D. Lgs. n. 196/2003.
Se avete ricevuto questo messaggio per errore, Vi preghiamo di distruggerlo e di informarci immediatamente per telefono allo 0039362595044 o inviando un messaggio allindirizzo e-mail
amministrazione@studioform.it
The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only.
Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the sender.
www.studioform.it
Le informazioni contenute nella presente comunicazione e i relativi allegati possono essere riservate e sono, comunque destinate esclusivamente alle persone o alla Società sopra indicati.
La diffusione, distribuzione e/o copiature del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dellart. 616 c.p., che ai sensi del D. Lgs. n. 196/2003.
Se avete ricevuto questo messaggio per errore, Vi preghiamo di distruggerlo e di informarci immediatamente per telefono allo 0039362595044 o inviando un messaggio allindirizzo e-mail
amministrazione@studioform.it
The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only.
Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the sender.
Hi,
I’d like to ask you a question about users, groups and roles.
In older versions of Postgres we had users and groups as two separate entities
Our IT department at work is called “sti”, that’s why:
- I had a user “sti” (the username of our IT manager)
- I had a group “sti” (the group containing all the users of our department: user1, user2…)
When we upgraded to PostgreSQL 8.2.4, came up the concept of roles; users and groups became one entity
So now I have in my database, only one role, called “sti”
My questions are:
1. how do I identify the users assigned to this role :
(in the older version)
SELECT grolist from pg_group where groname = 'sti';
2. how do I differ granting permissions on a table to the user sti from the whole members of the group sti
(in the older version)
GRANT SELECT ON table TO group sti;
GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti;
I’d appreciate any help regarding this issue
Pascal Tufenkji
Shavonne Marietta Wijesinghe wrote:
> Hello
>
> I am working with a database that has a Index number defined as Serial NOT NULL
>
> I used this because,
> 1. I want to make sure that when many users login at the same time the Index number won't be repeated.
> 2. I don't have to increment it by programming (I use ASP)
>
> But now i have a situation that i need to index numbers. For Example i could have a structure like this
>
> INDEX1 - N_SHEET - TOT_SHEET
> 1 - 1 - 1
> 2 - 1 - 3
> 2 - 2 - 3
> 2 - 3 - 3
>
> N_SHEET and TOT_SHEET are filled by the user (via web) but i need to hold on to the INDEX. And while userA is filling
the3 row if userB loggs in i need to provide the INDEX1 with 3.
>
> Any idea??
>
As well as using the "Serial NOT NULL" you have also defined this column
as PRIMARY KEY (or a unique index) which is what is preventing the
duplicates in that column. (A primary key is enforced with a unique index)
From the sample shown you can use all three columns as the primary key
with something similar to -
ALTER TABLE my_user_sheets DROP CONSTRAINT my_user_sheets_pkey;
ALTER TABLE my_user_sheets ADD PRIMARY KEY ("INDEX1", "N_SHEET",
"TOT_SHEET");
(this implies that for each user they will have only one row for each
combination of N_SHEET and TOT_SHEET) If you need to allow them to
select the same 2 sheet numbers more than once then I would suggest you
have an extra column for a primary key and redefine INDEX1 as the
user_id. (or just add a user_id column and leave the INDEX1 as it is)
It's not recommended but you could also have the table without a primary
key allowing duplicate value combinations. This would prevent you
updating a single row though.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
Pascal Tufenkji wrote: > My questions are: > > > > 1. how do I identify the users assigned to this role : > (in the older version) > SELECT grolist from pg_group where groname = 'sti'; > "The view pg_group exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows the names and members of all roles that are marked as not rolcanlogin, which is an approximation to the set of roles that are being used as groups." Use pg_roles to get the user and group info. Use pg_auth_members to get the list of members that belong to each group role. (any role can be used for a group but it is usually a role that has rolcanlogin set to false, and has members recorded in pg_auth_members) http://www.postgresql.org/docs/8.2/interactive/user-manag.html can explain it better - or more specifically http://www.postgresql.org/docs/8.2/interactive/role-membership.html > > 2. how do I differ granting permissions on a table to the user sti from > the whole members of the group sti > (in the older version) > GRANT SELECT ON table TO group sti; > GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti; > Use a more descriptive name for the group or simply sti_group. I am guessing that you have an issue because you now have one role called sti - that has carried the group members from the old version - this is the admin userid used to login but because it is used as a group it passes it's privileges to all members of sti. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
<div class="Section1"><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">Hi Shane,</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">Youare exactly right.</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">Myissue is that, I now have one role called sti - that has carried the group members from the oldversion - </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">So whatdo you think my options are, so I can separate them? </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt">I have only one option in my mind:</span></font><p class="MsoPlainText" style="margin-left:.25in;text-indent:-.25in;mso-list: l0 level1 lfo1"><font face="Verdana" size="2"><span style="font-size:10.0pt"><span style="mso-list:Ignore">-<font face="TimesNew Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></span></font><spandir="LTR">Revoke the members from the role sti</span><p class="MsoPlainText" style="margin-left:.25in;text-indent:-.25in;mso-list: l0 level1 lfo1"><font face="Verdana" size="2"><span style="font-size:10.0pt"><span style="mso-list:Ignore">-<font face="TimesNew Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></span></font><spandir="LTR">Create a new role (that has rolcanlogin set to false) called sti_group</span><pclass="MsoPlainText" style="margin-left:.25in;text-indent:-.25in;mso-list: l0 level1 lfo1"><font face="Verdana" size="2"><span style="font-size:10.0pt"><span style="mso-list:Ignore">-<font face="TimesNew Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></span></font><spandir="LTR">Assign the members to it</span><p class="MsoPlainText" style="margin-left:.25in;text-indent:-.25in;mso-list: l0 level1 lfo1"><font face="Verdana" size="2"><span style="font-size:10.0pt"><span style="mso-list:Ignore">-<font face="TimesNew Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></span></font><spandir="LTR">Finally, fix all the permissions for all the tables (add the permissionsto the new group sti_group)<br /> which seems like a huge amount of work… </span><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">In that case I’ll be able to give permissionssuch as :</span></font><p class="MsoPlainText"><i><font face="Verdana" size="2"><span style="font-size:10.0pt; font-style:italic">GRANT SELECT ON table TO sti_group;<br /> GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti;</span></font></i><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">Isthere a better solution ?</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">Pascal</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">-----OriginalMessage-----<br /> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Shane Ambler<br /> Sent: Friday, February 08, 2008 3:54 PM<br /> To:ptufenkji@usj.edu.lb<br /> Cc: pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] Postgres roles</span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">Pascal Tufenkji wrote:</span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> My questions are:</span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> 1. how do I identify the usersassigned to this role :</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">>(in the older version)</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt">> SELECT grolist from pg_group where groname = 'sti'; </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">"The view pg_group exists for backwardscompatibility: it emulates a </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">catalogthat existed in PostgreSQL before version 8.1. It shows the </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">names and members of all roles that aremarked as not rolcanlogin, which </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">isan approximation to the set of roles that are being used as groups."</span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">Use pg_roles to get the user and group info.Use pg_auth_members to get </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">thelist of members that belong to each group role.</span></font><p class="MsoPlainText"><font face="Verdana"size="2"><span style="font-size:10.0pt">(any role can be used for a group but it is usually a role that has</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">rolcanlogin set tofalse, and has members recorded in pg_auth_members)</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">http://www.postgresql.org/docs/8.2/interactive/user-manag.html</span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">can explain it better - or more specifically</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">http://www.postgresql.org/docs/8.2/interactive/role-membership.html</span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> 2. how do I differ granting permissionson a table to the user sti from</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">>the whole members of the group sti</span></font><p class="MsoPlainText"><font face="Verdana"size="2"><span style="font-size:10.0pt">> (in the older version)</span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">> GRANT SELECT ON table TO group sti;</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">> GRANT SELECT,INSERT,UPDATE,DELETEON table TO sti;</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">></span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">Usea more descriptive name for the group or simply sti_group.</span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">I am guessing that you have an issue becauseyou now have one role </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">calledsti - that has carried the group members from the old version - </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">this is the admin userid used to login butbecause it is used as a group </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">itpasses it's privileges to all members of sti.</span></font><p class="MsoPlainText"><font face="Verdana"size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">--</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">ShaneAmbler</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">pgSQL(at) Sheeky (dot) Biz</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">GetSheeky @ http://Sheeky.Biz</span></font><p class="MsoPlainText"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt">---------------------------(endof broadcast)---------------------------</span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt">TIP 3: Have you checked our extensive FAQ?</span></font><pclass="MsoPlainText"><font face="Verdana" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoPlainText"><fontface="Verdana" size="2"><span style="font-size:10.0pt"> http://www.postgresql.org/docs/faq</span></font></div>
Pascal Tufenkji wrote: > Hi Shane, > > > > You are exactly right. > > My issue is that, I now have one role called sti - that has carried the > group members from the old version - > > So what do you think my options are, so I can separate them? > > I have only one option in my mind: > > - Revoke the members from the role sti > > - Create a new role (that has rolcanlogin set to false) called > sti_group > > - Assign the members to it > > - Finally, fix all the permissions for all the tables (add the > permissions to the new group sti_group) > which seems like a huge amount of work. > > > > In that case I'll be able to give permissions such as : > > GRANT SELECT ON table TO sti_group; > GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti; > > > > Is there a better solution ? > That is the solution and it does seem like a lot if you have lots of users and/or tables - I can think of a couple of ways to make it easy - 1. Use pgAdmin - it has a Grant wizard that will generate the sql for the grants and revokes on all the tables/functions etc for you. It can do an entire schema in a few clicks. 2. Generate the list of commands yourself - fill a text file with them and send them to psql. "REVOKE sti FROM "+username+";" "GRANT sti_group TO "+username+";" "GRANT SELECT ON "+tablename+" TO sti_group;" ... ... The second may be the way to go at least for the removing and adding group memberships from sti to sti_group as I don't see any helpers in pgAdmin for that. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
Thanks alot Shane..Just what I wanted.. Didn't think of a solution with 3
primary keys.
Have a nice day ^___^
Shavonne Wijesinghe
www.studioform.it
----- Original Message -----
From: "Shane Ambler" <pgsql@Sheeky.Biz>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, February 08, 2008 2:17 PM
Subject: Re: [SQL] Serial not nulla
> Shavonne Marietta Wijesinghe wrote:
>> Hello
>>
>> I am working with a database that has a Index number defined as Serial
>> NOT NULL
>>
>> I used this because,
>> 1. I want to make sure that when many users login at the same time the
>> Index number won't be repeated.
>> 2. I don't have to increment it by programming (I use ASP)
>>
>> But now i have a situation that i need to index numbers. For Example i
>> could have a structure like this
>>
>> INDEX1 - N_SHEET - TOT_SHEET
>> 1 - 1 - 1
>> 2 - 1 - 3
>> 2 - 2 - 3
>> 2 - 3 - 3
>>
>> N_SHEET and TOT_SHEET are filled by the user (via web) but i need to hold
>> on to the INDEX. And while userA is filling the 3 row if userB loggs in i
>> need to provide the INDEX1 with 3.
>>
>> Any idea??
>>
>
> As well as using the "Serial NOT NULL" you have also defined this column
> as PRIMARY KEY (or a unique index) which is what is preventing the
> duplicates in that column. (A primary key is enforced with a unique index)
>
>
> From the sample shown you can use all three columns as the primary key
> with something similar to -
>
> ALTER TABLE my_user_sheets DROP CONSTRAINT my_user_sheets_pkey;
> ALTER TABLE my_user_sheets ADD PRIMARY KEY ("INDEX1", "N_SHEET",
> "TOT_SHEET");
>
> (this implies that for each user they will have only one row for each
> combination of N_SHEET and TOT_SHEET) If you need to allow them to select
> the same 2 sheet numbers more than once then I would suggest you have an
> extra column for a primary key and redefine INDEX1 as the user_id. (or
> just add a user_id column and leave the INDEX1 as it is)
>
>
>
> It's not recommended but you could also have the table without a primary
> key allowing duplicate value combinations. This would prevent you updating
> a single row though.
>
>
>
> --
>
> Shane Ambler
> pgSQL (at) Sheeky (dot) Biz
>
> Get Sheeky @ http://Sheeky.Biz