RLS without leakproof restrictions?
Hi all I'm currently researching different strategies for retrofitting some multi-tenant functionality into our existing Postgres-backed application. One of the options is using RLS policies to do row filtering. This is quite attractive as I dread the maintenance and auditing burden of adding filtering clauses to the majority of our queries. I'm somewhat concerned though about getting unexpected query plans based on the planner avoiding non-leakproof functions until row filtering has occurred - warning about this seems common in articles on RLS. Our application is the only "user" of the database, and we do not pass database errors through to the user interface, so for our case leakproof plans are overkill - we'd just like the implicit filtering clauses added based on some session GUCs that we set. Is there any way to get what we're looking for here? I don't see anything documented on CREATE POLICY, ALTER TABLE or any GUCs. Alternatively, are the concerns about changed plans unfounded? For example we don't use many expression indexes or exotic types, it's mostly btrees on text and ints. We do use tsearch a certain amount, but constructing tsvectors and tsqueries manually rather than through stemmers etc. Thanks Tom
Re: RLS without leakproof restrictions?
Hi Martin On Wed, 22 Feb 2023 at 13:12, Martin L. Buchanan wrote: > > Anyway, Tom if it is feasible to put each tenant into its own database on > the same server instance, that is what I recommend. > It is not, unfortunately. For brevity I skipped over some details - the "tenants" are possibly hundreds or thousands of different parts of large organisations and a single application user may be granted access to some or all of them. So strategies involving separate databases or schemas are mostly out. Thanks Tom
Re: RLS without leakproof restrictions?
Hi Tom! On Wed, 22 Feb 2023 at 14:16, Tom Lane wrote: > If you're happy allowing the application to decide if the filters will > be enforced, maybe just create some views embodying those filters, and > query those views when you want restrictions? > Yeah, thanks very much for the suggestion. It's more maintenance than RLS policies as we'll need to update views as tables are modified, and we'll need to ensure that the app never selects from the underlying table, but it still beats needing to add filter clauses across the codebase. Thanks Tom
Converting timestamp to timestamptz without rewriting tables
Hi all
We have generally been using timestamps without timezones in our system. As
both our app servers and db server were set to UTC it so far hasn't been an
issue. However, that may not always be the case, so we want to tighten
things up a bit. We are also needing to do things like get the particular
date that a timestamp in the db refers to, and thus need to be explicit
about which timezone everything is in.
All of the timestamps in our system represent an instant in time, not a
clock date/time, so timestamp with time zone is more appropriate. All of
the data that is currently on disk in timestamp columns was inserted in a
db session in UTC, and represents that timestamp in UTC.
We'd really like to convert all of our timestamps to timestamp with time
zones. We'd prefer not to have to rewrite all of our tables as a result
though.
My understanding from reading the docs is that a timestamp in UTC will have
the same on-disk representation as a timestamptz. This suggested simply
tweaking the system catalogs in some way. It looks like the preferred way
to do it would be creating a cast with WITHOUT FUNCTION between the two and
then doing an ALTER TABLE for each table affected, but a cast between the
two already exists.
I ended up trying out just tweaking the system catalogs directly, changing
the type associated with the table columns and the opclasses associated
with the related indexes.
UPDATE pg_attribute
SET atttypid = 'timestamp with time zone'::regtype
FROM pg_class
WHERE attrelid = pg_class.oid
AND relnamespace = current_schema()::regnamespace
AND atttypid = 'timestamp'::regtype
AND relname NOT IN ('some', 'excluded_tables');
-- These (3127, 3128) are hardcoded in pg sourcecode
UPDATE pg_index
SET indclass = array_to_string(array_replace(indclass::oid[], 3128::oid,
3127::oid), ' ')::oidvector
FROM pg_class
WHERE indrelid = pg_class.oid
AND relnamespace = current_schema()::regnamespace
AND indclass::oid[] @> ARRAY[3128::oid];
If we do this for real we can shut out other clients while it happens so we
don't need to worry about other backends having cached plans with the wrong
type etc.
It.. seems to work. Data is returned as expected and queries using the
indexes seem to work. I dropped and recreated functions and views that had
any reference to the affected columns.
Questions:
1. Is there a safer way to change these types without this hackery?
2. If not, is there anything else that I need to adjust?
3. Is there anything that could go wrong?
Many thanks
Tom
Re: Converting timestamp to timestamptz without rewriting tables
On 18 December 2017 at 18:43, Thomas Kellerer wrote: > > All of the timestamps in our system represent an instant in time, not > > a clock date/time, so timestamp with time zone is more appropriate. > > All of the data that is currently on disk in timestamp columns was > > inserted in a db session in UTC, and represents that timestamp in > > UTC. > > > Are you aware of the fact that "timestamp with time zone" does NOT > actually store the time zone? > Yes. > A timestamptz stores everything as UTC and the value is converted to the > session time zone upon retrieval. > Which is exactly what we want.
Converting timestamp to timestamptz without rewriting tables
Hi all
We have generally been using timestamps without timezones in our system. As
both our app servers and db server were set to UTC it so far hasn't been an
issue. However, that may not always be the case, so we want to tighten
things up a bit. We are also needing to do things like get the particular
date that a timestamp in the db refers to, and thus need to be explicit
about which timezone everything is in.
All of the timestamps in our system represent an instant in time, not a
clock date/time, so timestamp with time zone is more appropriate. All of
the data that is currently on disk in timestamp columns was inserted in a
db session in UTC, and represents that timestamp in UTC.
We'd really like to convert all of our timestamps to timestamp with time
zones. We'd prefer not to have to rewrite all of our tables as a result
though.
My understanding from reading the docs is that a timestamp in UTC will have
the same on-disk representation as a timestamptz. This suggested simply
tweaking the system catalogs in some way. It looks like the preferred way
to do it would be creating a cast with WITHOUT FUNCTION between the two and
then doing an ALTER TABLE for each table affected, but a cast between the
two already exists.
I ended up trying out just tweaking the system catalogs directly, changing
the type associated with the table columns and the opclasses associated
with the related indexes.
UPDATE pg_attribute
SET atttypid = 'timestamp with time zone'::regtype
FROM pg_class
WHERE attrelid = pg_class.oid
AND relnamespace = current_schema()::regnamespace
AND atttypid = 'timestamp'::regtype
AND relname NOT IN ('some', 'excluded_tables');
-- These (3127, 3128) are hardcoded in pg sourcecode
UPDATE pg_index
SET indclass = array_to_string(array_replace(indclass::oid[], 3128::oid,
3127::oid), ' ')::oidvector
FROM pg_class
WHERE indrelid = pg_class.oid
AND relnamespace = current_schema()::regnamespace
AND indclass::oid[] @> ARRAY[3128::oid];
If we do this for real we can shut out other clients while it happens so we
don't need to worry about other backends having cached plans with the wrong
type etc.
It.. seems to work. Data is returned as expected and queries using the
indexes seem to work. I dropped and recreated functions and views that had
any reference to the affected columns.
Questions:
1. Is there a safer way to change these types without this hackery?
2. If not, is there anything else that I need to adjust?
3. Is there anything that could go wrong?
Many thanks
Tom
