Remote table authorization has changed in the upcoming August 2018 MonetDB release. Previously any physical execution plan that was accessing data in remote tables, was using the hardcoded strings “monetdb”/“monetdb” for username and password in order to connect to the server where the actual data was residing. Besides the obvious security implications, this behavior means that the remote table feature cannot be used if the DBA has changed the password for the user monetdb.
We have implemented a new way to define remote tables, where a user needs to specify the credentials of the remote server at the table creation. The full form of the remote table creation syntax is as follows (new syntax highlighted):
CREATE REMOTE TABLE [ IF NOT EXISTS ] [ <schema name> . ] <name>
( <column definition(s)> )
ON <remote table URL>
[WITH [USER 'username'] [[ENCRYPTED] PASSWORD 'password']]
The best way to explain the changes is with examples:
A user has the option to leave the credentials unspecified for the server. In this case MonetDB will use the credentials of the user who issued the CREATE command, in order to connect to the remote server:
CREATE REMOTE TABLE rmt_tbl (id int, name varchar(20))
ON 'mapi:monetdb://remote.host.url:50000/dbname/scm1/l_tbl';
The above is exactly the same syntax we had until now, but as we mentioned above the semantics have changed slightly. Specifically in versions prior to Aug2018 the above SQL command would create a remote table named rmt_tbl that was pointing to the table l_tbl in the schema scm1 in the database dbname on a MonetDB server running on host remote.host.url, and it would use the credentials “monetdb”/“monetdb” to connect to it.
Starting from version Aug2018 the above command will create the same remote table, pointing to the same table on the remote machine. The difference is that it will now use the credentials of the user that issued the command in order to login to the remote server, and so it expects that these credentials are valid for the MonetDB server running at remote.host.url.
CREATE REMOTE TABLE rmt_tbl (id int, name varchar(20))
ON 'mapi:monetdb://remote.host.url:50000/dbname/scm1/l_tbl' WITH PASSWORD 'secret';
In this case the user specifies the password to the remote server that will be used in order to establish a connection. The username of the user that issued the CREATE REMOTE TABLE command will be used for the connection.
There is also the option to provide a SHA512 hash of the password using the ‘ENCRYPTED’ keyword, in exactly the same way as when creating a user.
CREATE REMOTE TABLE rmt_tbl (id int, name varchar(20))
ON 'mapi:monetdb://remote.host.url:50000/dbname/scm1/l_tbl'
WITH ENCRYPTED PASSWORD 'bd2b1aaf7ef4f09be9f52ce2d8d599674d81aa9d6a4421696dc4d93dd0619d682ce56b4d64a9ef097761ced99e0f67265b5f76085e5b0ee7ca4696b2ad6fe2b2';
The case where the user specifies the remote username is straightforward:
CREATE REMOTE TABLE rmt_tbl (id int, name varchar(20))
ON 'mapi:monetdb://remote.host.url:50000/dbname/scm1/l_tbl'
WITH USER 'remote_user' PASSWORD 'secret';
One important note is that the remote username should be specified as a string, and not as an identifier.
Again the password can be given as a SHA512 hash:
CREATE REMOTE TABLE rmt_tbl (id int, name varchar(20))
ON 'mapi:monetdb://remote.host.url:50000/dbname/scm1/l_tbl'
WITH USER 'remote_user'
ENCRYPTED PASSWORD 'bd2b1aaf7ef4f09be9f52ce2d8d599674d81aa9d6a4421696dc4d93dd0619d682ce56b4d64a9ef097761ced99e0f67265b5f76085e5b0ee7ca4696b2ad6fe2b2';
The syntax of the DROP statement remains the unchanged, but if a remote table is dropped, the credentials associated with it are removed.
The ALTER statement is not currently implemented for remote tables. This would be useful for changing the credentials used to connect to the remote server. This functionality can however be simulated by dropping and recreating the table. Since no data is moved, or copied during dropping or creating a remote table, there is no performance penalty.
Since this feature provides access to a remote server, please be aware of the security implications and potential risks. For example consider the following:
User A creates a remote table tbl1 in schema schema1 of database db1. This table accesses table tbl2 in schema schema2 of database db2. Any other user that can access schema1, has access to table tbl2 even if she doesn’t explicitly have permission to use schema2 or even db2.
Please note that in all cases MonetDB will not check the credentials at table creation time, but only when data from the table need to be accessed. At that time the local MonetDB server will try to connect to the remote server with the credentials specified for the remote table and will return an error if it is unable to do so.