Panneerselvam Posangu, 05.06.2013 11:30:
> Currently our application works along with Oracle 11g. In Oracle
> schema we have created Synonyms. The context is this: In Oracle
> schema we have couple of users. They own tables, views, and other
> objects. We create synonyms for a given object ,grant needed
> privilege and provide those synonyms to the users other than the
> owners.
>
Postgres doesn't have synonyms. For basic table "synonyms" you could use views.
> Is there any work-around you could suggest in PostgreSQL?
Not a workaround really, but something better :)
I think you don't need synonyms in Postgres the way you need them in Oracle because unlike Oracle there is a clear
distinctionbetween a user and a schema in Postgres. It's not a 1:1 relationship and multiple users can easily use
multipleschemas without the need to fully qualify the objects by exploiting Postgres' "search path" feature.
As far as I understand your description you would probably create several schemas inside a database, if the schema is
namedthe same as the user, Postgres automatically searches objects in the public schema and the "user" schema.
If you then setup the search_path for each user to include the schemas of the others, you only need to grant the needed
privilegesand you don't even need the synonyms any more.
For example
You could have user_1, user_2 and user_3
Your database can have the schemas: public, accounting, management and orders.
Tables in the schemas are owned by the users: appuser, acct, mgmtm and ord
You can setup user_1 to allow access to all tables in public and accounting and then define its search_path to list
thoseschemas.
user_2 would then get a search_path that lists public,accounting,orders
user_3 might only have management,orders
Once the search paths are properly defined no prefixing is necessary and you'd have the same situation as with Oracle
synonyms.
Thomas