RFD: access to remore databases: altername suggestion

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
remoteobject for the first time (default).
 

DISCONNECT ON { LOGOUT | COMMIT } Defines whether connection should be closed when user logs out (default), or when
transactionis 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)?