sql: Support user/role-level defaults for session variables #21151
Comments
knz
added
C-enhancement
|
Another use case that comes up is being able to configure |
|
The lack of flexibility in settings these on a user/role basis has come back to limit us a few times now. |
|
Another example is in the recurring debate between @andy-kimball and myself about how disruptive it is to close a client connection. One reason that postgres drivers don't have good ways to reestablish session variables after a connection reset is because in postgres you can set these variables on the user/role. |
rafiss
added
the
E-starter
|
copy-pasting my comment from elsewhere: When looking at this the importance of role-level (or db-level, or application_name-level) settings should not be underestimated. An implementation restricted to user-level configs would not be sufficient. This is important to consider, because the security best practice would be to use different SQL user accounts within the same client app, to distinguish privilege groups, and a lack of shared configurability dicincentivizes that. |
knz
changed the title
|
Yes, it should cover both users and roles (I don't think we supported roles at the time this issue was created). I'm not sure about db or appname-level settings. I could see it being useful, but it brings us farther away from postgres compatibility (while user and role settings bring us closer). The more places we have to define settings, the trickier it becomes to define precedence and avoid surprises. |
|
The Postgres docs are quiet on how defaults for session variables get inherited. It's a tough problem because a user may be granted many roles. How would we decide which setting to use if there are conflicts. My guess (haven't tested) is that Postgres doesn't try. |
rafiss
added
the
T-sql-foundations
|
It looks like there is a I'll check out the behavior with inherited roles / conflicts. |
knz
removed
the
E-starter
|
Postgres does not implement inheritance of session variable defaults. However, there is an order of precedence for "all role" defaults versus "specific role" defaults. See https://github.com/postgres/postgres/blob/d9809bf8694c17e05537c5dd96cde3e67c02d52a/src/backend/utils/init/postinit.c#L1166-L1170 SQL Experience will start work on this in the June milestone. |
67955: roachtest: remove passing test from ruby-pg blocklist r=RichardJCai a=rafiss fixes #67912 Release note: None 68001: sql/parser: add syntax for ALTER ROLE ... SET r=RichardJCai a=rafiss touches #21151 Note that the telemetry re-parsing logic does not work. This is consistent with the existing ALTER ROLE statement, and should be fixed separately. Release note (sql change): Add syntax for `ALTER ROLE ... SET` statements. The business logic for these statements is not implemented yet, but a later commit will add it. The following forms are supported: - ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET var { TO | = } { value | DEFAULT } - ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET var - ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET ALL As with other statements, the keywords ROLE and USER are interchangeable. This matches the PostgreSQL syntax: https://www.postgresql.org/docs/13/sql-alterrole.html Co-authored-by: Rafi Shamim <[email protected]>
68030: sql: implement write path for ALTER ROLE ... SET r=RichardJCai a=rafiss Touches #21151 ALTER ROLE ... SET will now persist the default settings in the system.database_role_settings table. The behavior matches Postgres in the following ways: - RESET does not validate the setting name. - SET validates both the name and the proposed default value. - The defaults are stored in a string array, in key=val format. The privilege checking is slightly different: we diverge from Postgres by prohibiting users from modifying their own defaults unless they have CREATEROLE. This is analogous to our restriction that prevents a user from modifying their own password. There is no release note since this does not yet affect any user-behavior. Everything in this commit is write-only Release note: None Co-authored-by: Rafi Shamim <[email protected]>
68245: sql implement the pg_db_role_setting table r=RichardJCai a=rafiss touches #21151 Release note (sql change): The pg_db_role_setting table of the pg_catalog is now implemented. When `ALTER ROLE ... SET var` is used to configure per-role defaults, these default settings will be populated in pg_db_role_setting. This table contains the same data no matter which database the current session is using. See https://www.postgresql.org/docs/13/catalog-pg-db-role-setting.html Co-authored-by: Rafi Shamim <[email protected]>
In postgres, any session variable can be set at the user/role level so it will be set automatically when that user connects. For example, this could be used to set a user-scope default time zone without changing the global default from UTC or dealing with the raciness of cluster settings.
This is especially useful because postgres drivers don't typically have the facility to automatically issue a
SET TIME ZONEcommand at the start of each session (a feature which is commonly found in mysql drivers, for example).Epic CRDB-2507
The text was updated successfully, but these errors were encountered: