Re: [PATCHES] [PATCH] Re: Why READ ONLY transactions?

> >> 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