Обсуждение: Serial not nulla

Поиск
Список
Период
Сортировка

Serial not nulla

От
"Shavonne Marietta Wijesinghe"
Дата:
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
 
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 dell’art. 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 all’indirizzo 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.

Postgres roles

От
"Pascal Tufenkji"
Дата:

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

Re: Serial not nulla

От
Shane Ambler
Дата:
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

Re: Postgres roles

От
Shane Ambler
Дата:
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

Re: Postgres roles

От
"Pascal Tufenkji"
Дата:
<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>

Re: Postgres roles

От
Shane Ambler
Дата:
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

Re: Serial not nulla

От
"Shavonne Marietta Wijesinghe"
Дата:
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