Re: Synonyms in PostgreSQL 9.2.4

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 distinction between a user and a schema in Postgres. It's not a 1:1 relationship and multiple users can easily use multiple schemas 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 named the 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 privileges and 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 those schemas.
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