Thread: Synonyms in PostgreSQL 9.2.4

Panneerselvam Posangu wrote:
> I am currently working on a conversion project. We plan to make our application PostgreSQL compliant.
> And for this we have chosen PosgreSQL 9.2.4.
> 
> 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.
> 
> Is there any work-around you could suggest in PostgreSQL?

In the following I use "schema" in the PostgreSQL meaning of
the word.

I can think of two solutions:

1) Set the search_path of the users so that it contains the schemas
   that contain the needed objects and grant them privileges on the
   objects and schemas.

2) If for some reason that seems undesirable, you can use a
   view as substitute for an Oracle schema object.

Yours,
Laurenz Albe

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