> >> It's not intended to be a security measure, and I would strongly > >> resist any attempt to make it so along the lines you propose. > > > Intended or not, it does work. > > No, you just haven't thought of a way to get around it yet. When > you do think of one, you'll be wanting us to contort the GUC system > to plug the loophole. We've already got a horrid mess in there for > the LOG_XXX variables, and I don't want to add more. > > I'm not objecting to the idea of being able to make users read-only. > I'm objecting to using GUC for it. Send in a patch that, say, adds > a bool column to pg_shadow, and I'll be happy.
How is that any different than ALTER USER [username] SET jail_read_only_transactions TO true? It sets something in pg_shadow.useconfig column, which is permanent. Ultimately, the XactReadOnly variable is going to be used and the assign_transaction_read_only() function in guc.c will be necessary too. Would a different GUC that required only one ALTER USER statement make you happier? If so, then how about: ALTER USER [username] SET readonly TO TRUE; ALTER USER [username] SET read_only TO TRUE; ALTER USER [username] SET readonly_user TO TRUE; ALTER USER [username] SET read_only_user TO TRUE; When "read_only_user" is set to true, it changes transaction_read_only, default_transaction_read_only, and jail_read_only_transactions all to TRUE. The read_only_user GUC does nothing if set to FALSE and can only be set by the superuser. If I were to add a specific syntax for this, what would you like? ALTER USER [username] [READONLY|NOTREADONLY]; ?? Use of the GUC infrastructure makes much more sense and is loads cleaner, IMHO. Use of GUC is also going to be much more lightweight than adding a new syntax for making accounts read only, esp since the read only transaction infrastructure is already GUC backed. Is your objection that GUC doesn't scale well? If so, it shouldn't too hard for me to change GUC to use a hash lookup instead of a linear scan (that'd be something I'd do for 7.5). If it's that GUC is a flat namespace, I'm very inclined agree that it's limited in that regard and a full MIB tree would be preferred. Ex: ALTER USER [username] SET user.readonly = TRUE; ALTER USER [username] SET user.dba = TRUE; ALTER USER [username] SET user.create_database = TRUE; ALTER USER [username] SET user.create_user = TRUE; ALTER USER [username] SET user.security.ssl.require = TRUE; ALTER USER [username] SET user.security.ssl.rsa_cert = "text cert authenticating this user"; ALTER USER [username] SET user.security.ssl.sslv2_enable = FALSE; ALTER USER [username] SET user.security.ssl.sslv3_require = TRUE; ALTER USER [username] SET user.schema = [schema1, schema2, schema3, public]; ALTER USER [username] SET user.security.see_other_schemas = FALSE; ALTER USER [username] SET database.name = "some non-existent dbname"; New databases, once created, would also show up in the MIB hierarchy, allowing things like: ALTER USER [username] SET database.[dbname].readonly TO TRUE; That last option, for example, would let users connect to a centrally hosted database, but would spoof the dbname that the user would see via CURRENT_DATABASE. I could imagine it being useful for hosted DB environments wherein you want to give the user the illusion of a private database. Same with user.security.see_other_schemas. Where the textual OIDs would be converted to their numeric counterparts and then stored with their value in useconfig. Now that PostgreSQL has slick array support (thanks Joe!), the various user options could be stored as array elements in the pg_catalog.pg_shadow.useconfig column. Imagine adding a syntax for every feature that a user could have vs. setting user features via a GUC/MIB system. I'd take the MIB system any day of the week and twice on Friday given the resulting reduction of bloat to gram.y. -sc -- Sean Chittenden ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster