PostsreSQL / SQL newbie question
mark.phillips at mophilly.com
Sun Apr 14 13:35:00 EDT 2013
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.
root account, never used for anything but installation and configuration admin
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
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
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
More information about the omnisdev-en