Thread
-
RFD: access to remore databases: altername suggestion
Mariusz Czułada <manieq@idea.net.pl> — 2001-10-02T22:38:44Z
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)? -
Re: RFD: access to remore databases: altername suggestion
Alex Pilosov <alex@pilosoft.com> — 2001-10-02T22:52:41Z
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 > >