Re: [SQL] What are the (various) best practices/opinions for table/column/constraint naming?

[Karsten Hilbert]

Speaking on behalf of the GNUmed schema.

The type of thing comes first for consistency.

primary key: pk

   Some might argue pk_ is preferrable such that
   in joins one ist not forced to use column aliases. We do
   in such cases. The "... where table.pk = ..." just seems
   soo intuitive.

foreign key: fk_

   This then affords fk__
   should that ever be needed (likely an indication of bad design).

indices: idx__

sequences: so far we relied on PG giving us a name

constraints: named by what they *do*, such as ensure_..._consistency

functions: f_

trigger functions: trf_

trigger: tr_

> And other naming conventions suggest using mixed/camel case 
> (quoted-identifiers)
We don't use mixed case as that would *require* quoting which is
liable to being forgotten.

Not much help, just our convention.

Karsten
-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] 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

---(end of broadcast)---
TIP 6: explain analyze is your friend