Hi!

0. I think access to other databases is really important. There was
a discussion about that. Using a dot operator to specify a
database (schema) seems to be very standard and elegant.
But there is another way to implement it. Here is my
suggestion.

1. First, some syntax:

CREATE [ SHARED ] [ TRUSTED ] CONNECTION conn_name
  USING 'conn_string'
  [ CONNECT ON { LOGIN | USE } ]
  [ DISCONNECT ON { LOGOUT | COMMIT } ];

Description
  Creates a connection definition (Oracle: database link) to
  a remote database.

SHARED
  Means only one instance of connection exists and is accessible
  to all qualified users.

TRUSTED
  Only superusers can use this connection (like TRUSTED modifier
  in CREATE LANGUAGE).

conn_name
  Just an identifier.

'conn_string'
  Connect string in standard form accepted by libpq
  'PQconnectdb' function.

CONNECT ON { LOGIN | USE }
  Defines whether connection should be established when
  user logs in, or when references remote object for the
  first time (default).

DISCONNECT ON { LOGOUT | COMMIT }
  Defines whether connection should be closed when
  user logs out (default), or when transaction is ended (COMMIT,
  ROLLBACK, but also exiting).

2. Additional commands

ALTER CONNECTION conn_name
  USING 'conn_string'
  [ CONNECT ON { LOGIN | USE } ]
  [ DISCONNECT ON { LOGOUT | COMMIT } ];

Description
  Changes behaviour of a defined connection (same parameters
  as for CREATE CONNECTION).


DROP CONNECTION conn_name;

Description
  Hmm... drop the connection definition?


Also a new privilege CONNECT should be added, so
GRANT CONNECT ON remote_database TO SCOTT;
can be processed.


3. How to use this?

SELECT local.id, remote.name
  FROM orders local, emp@remote_database remote
  WHERE local.emp_id = remote.id;

SELECT give_a_raise_proc@rempte_database(1000);


4. Some notes (in random order)

If a 'conn_string' does not contain a user/password information,
connection is performed using current user identity. But, for SHARED
connection always use a 'nobody' account (remeber to create
'nobody' user on remote database). For security reasons
'conn_string' must be stored in encrypted form.

When CONNECT ON LOGIN is used, connection is etablished
only if user has CONNECTprivilege granted on this. For TRUSTED
connection also superuser rights must be checked.

If first remote object is accessed within a transaction, a remote
transaction should be started. When trancaction ends, remote
transaction should also be ended same way (commit or rollback).

SHARED connection should be established when first user logs in
or uses remote object (depends on CONNECT ON clause) and
terminated when last user ends transaction or disconnects
(depens on DISCONNECT ON clause). Of course no remote
transaction can be performed for SHARED connection.

Of course it would require lot of work, but can be parted. The
minimum  IMHO can be a SHARED connection with
CONNECT ON USE and DISCONNECT ON LOGOUT behaviour.

5. Conclusion

I know it is much easier to 'invent' a new functionality than
to implement it. I also realize this proposal is not complete
nor coherent. Still want to listen/read your opinions about it.

Regards,

Mariusz Czulada

P.S.: Is it planned to add 'auto_transaction' parameter on server
or database levels, so events like login, commit or rolback
automaticly start a new transaction without 'BEGIN WORK'
(like Oracle does)?


Wed, 03 Oct 2001 00:38:44 +0200
<manieq@idea.net.pl>
RFD: access to remore databases: altername suggestion
You are attacking here two things: 

a) schemas, which should be done in 7.3, thus multiple databases on same
host would be unnecessary.

b) connections to remote host' databases, which is partially implemented
already (in a ugly way, but...) see contrib/dblink

What you described is a syntactic sugar to implement b) which isn't a bad
idea, but just consider, it is already done. sorta. 

On Wed, 3 Oct 2001 manieq@idea.net.pl wrote:

> Hi!
> 
> 0. I think access to other databases is really important. There was
> a discussion about that. Using a dot operator to specify a
> database (schema) seems to be very standard and elegant.
> But there is another way to implement it. Here is my
> suggestion.
> 
> 1. First, some syntax:
> 
> CREATE [ SHARED ] [ TRUSTED ] CONNECTION conn_name
>   USING 'conn_string'
>   [ CONNECT ON { LOGIN | USE } ]
>   [ DISCONNECT ON { LOGOUT | COMMIT } ];
> 
> Description
>   Creates a connection definition (Oracle: database link) to
>   a remote database.
> 
> SHARED
>   Means only one instance of connection exists and is accessible
>   to all qualified users.
> 
> TRUSTED
>   Only superusers can use this connection (like TRUSTED modifier
>   in CREATE LANGUAGE).
> 
> conn_name
>   Just an identifier.
> 
> 'conn_string'
>   Connect string in standard form accepted by libpq
>   'PQconnectdb' function.
> 
> CONNECT ON { LOGIN | USE }
>   Defines whether connection should be established when
>   user logs in, or when references remote object for the
>   first time (default).
> 
> DISCONNECT ON { LOGOUT | COMMIT }
>   Defines whether connection should be closed when
>   user logs out (default), or when transaction is ended (COMMIT,
>   ROLLBACK, but also exiting).
> 
> 2. Additional commands
> 
> ALTER CONNECTION conn_name
>   USING 'conn_string'
>   [ CONNECT ON { LOGIN | USE } ]
>   [ DISCONNECT ON { LOGOUT | COMMIT } ];
> 
> Description
>   Changes behaviour of a defined connection (same parameters
>   as for CREATE CONNECTION).
> 
> 
> DROP CONNECTION conn_name;
> 
> Description
>   Hmm... drop the connection definition?
> 
> 
> Also a new privilege CONNECT should be added, so
> GRANT CONNECT ON remote_database TO SCOTT;
> can be processed.
> 
> 
> 3. How to use this?
> 
> SELECT local.id, remote.name
>   FROM orders local, emp@remote_database remote
>   WHERE local.emp_id = remote.id;
> 
> SELECT give_a_raise_proc@rempte_database(1000);
> 
> 
> 4. Some notes (in random order)
> 
> If a 'conn_string' does not contain a user/password information,
> connection is performed using current user identity. But, for SHARED
> connection always use a 'nobody' account (remeber to create
> 'nobody' user on remote database). For security reasons
> 'conn_string' must be stored in encrypted form.
> 
> When CONNECT ON LOGIN is used, connection is etablished
> only if user has CONNECTprivilege granted on this. For TRUSTED
> connection also superuser rights must be checked.
> 
> If first remote object is accessed within a transaction, a remote
> transaction should be started. When trancaction ends, remote
> transaction should also be ended same way (commit or rollback).
> 
> SHARED connection should be established when first user logs in
> or uses remote object (depends on CONNECT ON clause) and
> terminated when last user ends transaction or disconnects
> (depens on DISCONNECT ON clause). Of course no remote
> transaction can be performed for SHARED connection.
> 
> Of course it would require lot of work, but can be parted. The
> minimum  IMHO can be a SHARED connection with
> CONNECT ON USE and DISCONNECT ON LOGOUT behaviour.
> 
> 5. Conclusion
> 
> I know it is much easier to 'invent' a new functionality than
> to implement it. I also realize this proposal is not complete
> nor coherent. Still want to listen/read your opinions about it.
> 
> Regards,
> 
> Mariusz Czulada
> 
> P.S.: Is it planned to add 'auto_transaction' parameter on server
> or database levels, so events like login, commit or rolback
> automaticly start a new transaction without 'BEGIN WORK'
> (like Oracle does)?
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 
> 


Tue, 2 Oct 2001 18:52:41 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: RFD: access to remore databases: altername suggestion