rfd/0181-pg-database-access-webui.md
Engineering: (@r0mant || @smallinsky) && @greedy52
Product: @klizhentas || @xinding33
This RFD proposes a feature that allows users to access PostgreSQL databases through the Web UI as an alternative to using the CLI. The feature includes an interactive shell for simplified query execution and database management.
While other protocols, such as SSH and Kubernetes Pod exec, already offer Web UI access alongside CLI options, PostgreSQL users have been restricted to terminal-based management. Providing a way to access databases through Web UI will bring an alternative to CLI usage and cover scenarios where users don't have access to a terminal or don't want to swap from Web UI to the terminal.
Alice is new to Teleport, but she's experienced with PostgreSQL and has spent
a considerable time using psql, the PostgreSQL CLI.
Before her access, a system administrator had already enrolled a PostgreSQL instance and created a set of users the Teleport users could use. Those database users and the existent databases of the instance are listed on the role assigned to Alice.
She first logs into Teleport's Web UI and then searches for the desired database on the resources list. After locating it, she clicks on the "Connect" button.
After clicking, she is presented with a connect modal with options to connect using Teleport's CLI or through Web UI.
After clicking the "Connect" button on the modal, a new tab opens with a form containing connection information. In that modal, she needs to select which database and database user she'll be using. Teleport already fills this information based on her permissions, so she doesn't need to find this information somewhere else or ask someone. Also, this will prevent her from inputting the information incorrectly and being unable to connect.
After selecting the required information and clicking on the "Connect" button,
the modal closes, and the terminal is focused with an interactive shell, similar
to psql, where she can type her queries.
After interacting with the database, she closes the tab, and her database session ends.
This is the same scenario, but the PostgreSQL instance was configured with user provisioning enabled. This change implies which information Alice sees on the connection information modal. She doesn't need to select the database user, as it will default to her username. The select is then disabled, and a new now select will be presented where she can select database roles attached to their user.
Bob is familiar with Teleport web UI and is experienced with PostgreSQL,
spending considerable time using psql, the PostgreSQL CLI.
The system administrators configured this Teleport cluster to use MFA per
session (require_session_mfa: true). They also have granted Bob permission to
connect to multiple PostgreSQL databases. The role he'll be using has a short
session TTL.
He logs into Teleport web UI, searches for the desired database, and clicks "Connect". He's presented with a connect modal with options to connect using Teleport's CLI or Web UI. After clicking on the "Connect" button, a new tab opens with the connection information form with the information: database name, database roles, and database user pre-filled. He confirms the values and clicks "Connect" to start a new session.
The modal closes, and he's prompted with the MFA modal (the same as SSH sessions). After completing the authentication, he starts performing multiple queries using the interactive shell, but after some time, he exceeds his session TTL, the connection is dropped, and he is redirected to the login page. His database session is terminated.
At the beginning of each session, we'll include an informative banner that will consist of the following:
psql).Teleport PostgreSQL interactive shell (v16.2.0-dev)
Connected to "pg" instance as "alice" user.
Type "help" or \? for help.
In addition to executing queries, the interactive shell will implement some
backlash (\) commands from psql. Users can fetch the list of supported
commands by calling the help command (\? or help):
postgres=> \?
General:
\q Terminate the session.
\teleport Show Teleport interactive shell information, such as execution limitations.
Informational:
\d List tables, views, and sequences.
Connection/Session:
\session Display information about the current session, like user, roles, and database instance.
Supported list of commands and output examples:
\q: No output. Terminates the session.
\teleport: Presents the shell information.
postgres=> \teleport
Teleport PostgreSQL interactive shell (v16.2.0-dev)
\d: Describes the tables, views and sequences.
Schema Name Type Owner
------ ---------------- -------- --------
public employees table alice
public employees_id_seq sequence alice
public event table postgres
public events table postgres
\session: Presents session information.
Connected to "pg" instance as "alice" user.
Teleport PostgreSQL interactive shell will not be a complete feature pair with
psql. Those limitations will be due to security measures or the shell's
simplicity. Given those limitations, messages will be shown to the users,
displaying a description and direction on executing the desired command
(if applicable).
Unsupported backslash (\) commands from psql will only display a failure message:
postgres=> \set a "hello"
Invalid command \set.
Try "help" or "\?" for the list of supported commands.
postgres=>
Other limitations, such as query size limit, will display a more complete message:
postgres=> INSERT INTO ... # Long query.
ERROR: Unable to execute query. Max query size limit (2048 characters) exceeded.
For long queries, execute it using `tsh db` commands.
postgres=>
To track PostgreSQL databases access through Web UI, we need to differentiate
them from direct sessions. To do so, we'll add a new field to the
SessionStartDatabaseMetadata
event to include this information:
// SessionStartDatabaseMetadata contains additional information about database session.
message SessionStartDatabaseMetadata {
// database type.
string db_type = 1;
// database protocol.
string db_protocol = 2;
// database origin source.
string db_origin = 3;
+ // access_through describes whether the database was accessed by speaking to
+ // the proxy service or using Web UI interactive shell.
+ string access_through = 4;
}
Initially, the supported values for this field with be:
proxy_service: Database was accessed directly on the proxy services. This is
the current behavior for databases.webui: Databases was accessed using the Web UI interactive shell.Older events without this new field should be considered the same as
proxy_service.
Note: UI changes won't affect Teleport Connect.
All the new interactions will be done on the Web UI. First, the database resource returned by the API will include new fields:
// Defined at lib/web/ui/server.go
type Database struct {
// omitted
// DatabaseRoles is the list of allowed database roles that the user can
// select.
DatabaseRoles []string `json:"database_roles"`
// SupportsWebUISession is a flag to indicate the database supports Web UI
// sessions.
SupportsWebUISession bool `json:"support_webui_session"`
}
These new fields are used to:
The connection options will be retrieved as the following (from Access checker):
DatabaseRoles: CheckDatabaseRoles.DatabaseNames: EnumerateDatabaseNames.DatabaseUsers: EnumerateDatabaseUsers.Note that the DatabaseNames and DatabaseUsers fields are currently filled
with different functions. The main difference is that the enumerate functions
consider auto-provisioning options instead of just relying on the roles.
In addition, a new page will be added to handle the database session
console/terminal (/web/cluster/:clusterId/console/db/:sid). This page will be
very similar to the SSH session console, except for the file transfer/upload
icons bar, which will be removed.
A new web handler will be added to handle the interactive session socket
connection: /webapi/sites/:site/databases/term. We cannot provide the session
parameters since web socket connections are started as GET requests. Instead,
when the web socket connection is established, the front end will forward a
message containing the session parameters. This is the same flow used by
Kubernetes exec sessions.
The following struct represents the session request:
// DatabaseSessionRequest describes a request to create a web-based terminal
// database session.
type DatabaseSessionRequest struct {
// DatabaseResourceID is the database resource ID the user will be connected.
DatabaseResourceID string `json:"db_resource_id"`
// DatabaseName is the database name the session will use.
DatabaseName string `json:"db_name"`
// DatabaseUser is the database user used on the session.
DatabaseUser string `json:"db_user"`
// DatabaseRoles are database roles that will be attached to the user when connecting to the database.
DatabaseRoles []string `json:"db_roles"`
}
After the connection is established and the first message is received, the proxy
will generate a user certificate containing RouteToDatabase using one of
PerformMFACeremony or GenerateUserCerts.
Note: The MFA flows will follow the existing functions (used by Kubernetes execs, desktops, and SSH sessions).
Once the certificates are generated, the proxy will connect to the target database server. This flow resembles what the database proxy does: Choose a database server among a list of available servers to forward the connection. After the connection is established, a new instances of the Teleport's PostgreSQL REPL is created, and the session starts.
The proxy will include a PostgreSQL Read-Eval-Print Loop (REPL) that interacts with the databases served by database servers. The REPL will read the user input per line and decide whether or not to execute the command (as the REPL will support multi-line commands). The rule follows:
;).If the line read doesn't meet the above criteria, it is added to an internal buffer and is accumulated until it is ready to be executed.
The REPL will interact with the WebSocket connection using the existent
terminal (lib/web/terminal/terminal.go) structs.
A few other cases to consider:
SELECT \d 1;). This execution
invalid SQL syntax errors (validate by the PostgreSQL instance).Backslash commands are executed at the proxy, and results are forwarded to the client's websocket. Depending on the command executed, there is no interaction with the database server, and the command won't be included in the session recording.
There are two categories of backslash commands: those that don't require database interaction (for example, printing the help menu) and those that expand into SQL queries forwarded into the database.
For the latter, we'll keep a list of pre-written queries based on the queries
used at psql.
To execute commands and queries into the target PostgreSQL, the REPL will use a
pgconn.PgConn instead of manually generating PostgreSQL protocol messages.
This will simplify the protocol interaction and cover most use cases.
The pgconn.PgConn will be started during the REPL initialization process.
Query cancellation won't be covered in the initial version. The executions will use the session context, meaning that if the session is closed, the queries will get canceled.
Once the query is executed, the interactive shell will parse command tag results and decide how to render it:
SELECT commands: Generate an ASCII table using the query results with
the same format as psql. The field information (table head) will be fetched
using the result field descriptors.We'll rely on the existent ASCII table package (lib/asciitable) to generate
the tables. This reduces the amount of code necessary and avoids additional
dependencies.
postgres=# SELECT * FROM events;
id name salary
-- ----------- --------
1 John Doe 50000.00
2 Jane Smith 60000.00
3 Bob Johnson 55000.00
4 Alice Brown 65000.00
5 Mike Davis 58000.00
(5 rows affected)
postgres=# INSERT INTO events (name) VALUES ('end');
INSERT 0 1
Errors will be shown with ERROR: prefix.
postgres=> SELECT err;
ERROR: column "err" does not exist
Users could intentionally send large, complex queries to consume excessive memory or processing power, potentially leading to service degradation or outages.
The interactive shell will limit the command/query size regarding character count. It includes the shell internal buffer (used for multi-line commands) and the line reader.
In addition, the command results might include large sets. pgconn will handle
this when reading a query's results. The interactive shell will iterate over
each row, avoiding loading all at once in memory.
Regarding users trying to bypass Teleport's audit system, the same risks and solutions from CLI access will apply to the newly introduced Web UI access.
This is because the database server still handles all the auditing capabilities, and the Web UI interactive shell acts as a regular database client.
Given that the database connection and user's input is handled by the proxy, multiple users could connect using the same REPL instance. This would require proxy-peering to be enabled given that users might not be using the same proxy server.
Depending on the REPL usage, we can introduce advanced features to enhance the user's experience. Here is a non-extensive list of what can be implemented using the current solution:
psql does).Some discovered databases require additional setup within the database before the users can connect using Teleport. Given that the REPL is not specific to Teleport and internally uses a regular PostgreSQL connection, it is able to connect directly to databases. We could extend the REPL connection parameters to include the database address and credentials so that it can initialize the connection.