All three databases are connected the same way: a "Host", a "Port", a "Database", a "User name" and a "Password". See Connect to Your Data for where to create a connection and how to use it.
PostGIS
| Field | What to enter |
|---|---|
| "Host", "Port" | Where the database answers. The port is usually 5432. |
| "Database" | The database name. PostGIS must be enabled in it. |
| "User name", "Password" | A user that can read the data. |
| "Default schema" | The schema used when a table is named without one. public unless you change it. |
| "Encryption" | "Always" unless your database cannot encrypt. |
- Reading. A table, a view or one query. The read is one consistent snapshot of the database.
- Geometry.
geometryandgeographycolumns are both read. Data in another coordinate system is converted to longitude and latitude by the database. - Writing. "Create a new table", "Add rows", "Replace the table" and "Update matching rows, add the rest".
- Stopping. Stopping a read also stops the query in your database.
SQL Server
| Field | What to enter |
|---|---|
| "Host", "Port" | Where the server answers. The port is usually 1433. |
| "Database" | The database name. |
| "User name", "Password" | A SQL Server login. Windows sign-in is not supported. |
| "Default schema" | dbo unless you change it. |
| "Encryption" | "Always" encrypts the session. "Always, and check the certificate and host name" also verifies the server, which needs a certificate from a public authority. |
- Reading. A table, a view or one query. A query must be a single
SELECTwith no comments, no semicolons and noWITHat the start, and every column needs a name. - Geometry.
geographyandgeometrycolumns are both read. SQL Server cannot convert between coordinate systems, so only data in WGS 84 (SRID 4326) is read. A column in another system is refused with a message that names the system. - Writing. "Create a new table", "Add rows" and "Replace the table". Shapes are written to a
geometrycolumn with SRID 4326. - Curves. Curved shapes are not read. Convert them to lines in a query first.
Amazon Redshift
| Field | What to enter |
|---|---|
| "Host" | The cluster or workgroup endpoint. |
| "Port" | 5439 unless you changed it. |
| "Database" | The database name. |
| "User name", "Password" | A database user that can read the data. |
| "Default schema" | public unless you change it. |
- Reading. A table, a view or one query. A query is a single
SELECTorWITH, with no semicolon inside it. - Geometry.
GEOMETRYandGEOGRAPHYcolumns are both read. Data in another coordinate system is converted to longitude and latitude by Redshift. - Writing. "Create a new table", "Add rows" and "Replace the table". Shapes are written to a
GEOMETRYcolumn with SRID 4326. - Stopping. Stopping a read also cancels the query in Redshift.
- Reaching it. The cluster or workgroup must be publicly accessible, and its security group must allow Tamaat.
What the user needs to be allowed to do
- To read: connect to the database, use the schema, and
SELECTon the tables and views. - To write from a flow: also create tables in the schema being written to. Tamaat stages the rows in a temporary table beside yours and removes it when the write finishes.
A user that can only read is the right choice for a connection that is only used for layers and readers.
Statements you can copy
These create a user that reads the gis schema and writes results to a schema named results. Change the names to yours, and leave out the last statement of each block for a user that only reads.
PostGIS
CREATE ROLE tamaat LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE city TO tamaat;
GRANT USAGE ON SCHEMA gis TO tamaat;
GRANT SELECT ON ALL TABLES IN SCHEMA gis TO tamaat;
GRANT USAGE, CREATE ON SCHEMA results TO tamaat;
SQL Server
CREATE LOGIN tamaat WITH PASSWORD = '...';
CREATE USER tamaat FOR LOGIN tamaat;
GRANT SELECT ON SCHEMA::gis TO tamaat;
GRANT CREATE TABLE TO tamaat;
GRANT ALTER, SELECT, INSERT, DELETE ON SCHEMA::results TO tamaat;
Amazon Redshift
CREATE USER tamaat PASSWORD '...';
GRANT USAGE ON SCHEMA gis TO tamaat;
GRANT SELECT ON ALL TABLES IN SCHEMA gis TO tamaat;
GRANT USAGE, CREATE ON SCHEMA results TO tamaat;
Next steps