PostsreSQL / SQL newbie question

[Mark Phillips]

On Apr 13, 2013, at 11:28 AM, Bob Fiering wrote:

> ERROR	relation "public.fbv" does not exist at 


One possibility is the case sensitivity of postgres. Mixed case names must be quoted, as the default is all lower case. Single case names do not need to be quoted strings.

That error may also result from a permission issue in the dbms. If you log on as root in pgAdmin and create a table, but  then logon as someone else from Omnis, *and* that someone else is not granted permission to use the table a "relation does not exist" error may be returned.

Check how your postgres users and login groups are set up. I know you understand this concept, but in the spirit of stimulating an epiphany here is a hierarchy of logins and group logins I think may be common in postgres.

postgres
	root account, never used for anything but installation and configuration admin

house_dba
    login group for selected user accounts who are sys admins for all dbs and apps
    members: standard logins who are the real system dba's

app_admin
    login group for "master" users in an application typically used for adding new user accounts
    members:  standard logins who are end user administrators in the application domain

app_staff
    login group for "normal" users of an application
    members: standard logins who are all end users of an application

Each login group is granted privileges appropriate to the tasks they need to perform. The group "mophilly_dba" has all the permissions of postgres. The group "app_admin" has all permissions but limited to a specific database. The group "app_staff" has limited permissions like select, update, delete. 

I hope this helps. 

 - Mark Phillips, trundling off for more coffee