extension dependencies with 'requires'
Greetings, I'm trying to author an extension and running into troubles with dependencies. The extension depends on the uuid-ossp, pgcrypto and postgres_fdw extensions, but I would like the dependencies to be installed in the public schema, though the extension itself lives in its own schema. Is there a way to use CREATE EXTENSION ... CASCADE and specify in the control file which schema the dependencies are installed in? How does the extension know where to install its dependencies? Thanks, Eric -- Eric Hanson CEO, Aquameta Labs 503-929-1073 www.aquameta.com -- -- Eric Hanson CEO, Aquameta 503-929-1073
Re: extension dependencies with 'requires'
Ok thanks. FWIW, this pretty profoundly limits what you can do with dependencies in extensions that define their own schema: When two extensions that both define their own schema need to share the same dependency, sounds like that is impossible, because the dependency extensions can't be installed in two schemas at the same time. Which ever extension is installed first will create the dep in the target schema, and then the second extension will try to install the dep and fail due to it being already installed. Thanks, Eric -- Eric Hanson CEO, Aquameta Labs 503-929-1073 www.aquameta.com On Tue, May 1, 2018 at 11:00 AM, Tom Lane wrote: > Eric Hanson writes: > > I'm trying to author an extension and running into troubles with > > dependencies. The extension depends on the uuid-ossp, pgcrypto and > > postgres_fdw extensions, but I would like the dependencies to be > installed > > in the public schema, though the extension itself lives in its own > schema. > > Is there a way to use CREATE EXTENSION ... CASCADE and specify in the > > control file which schema the dependencies are installed in? > > Afraid not. CASCADE will pass down the same target-schema option to the > child CREATE EXTENSION operations that appeared in the initial command. > > regards, tom lane >
Re: I do not get the point of the information_schema
You might find Aquameta's meta module helpful, it reimplements information_schema in a more normalized layout, as updatable views: http://blog.aquameta.com/2015/08/29/intro-meta/ https://github.com/aquametalabs/aquameta/tree/master/core/000-meta Best, Eric On Mon, Feb 12, 2018 at 2:02 PM Thiemo Kellner wrote: > I try to implement SCD2 on trigger level and try to generated needed > code on the fly. Therefore I need to read data about the objects in the > database. So far so good. I know of the information_schema and the > pg_catalog. The documentation for the information_schema states that it > 'is defined in the SQL standard and can therefore be expected to be > portable and remain stable'. I can think of a sensible meaning of > portable. One cannot port it to MariaDB, can one? Maybe different > PostreSQL version but then a one fits all implementation would mean only > parts of the catalogue that never ever change can be exposed by the > information_schema. Coming from Oracle I consider the information_schema > the analogy to Oracles data dictionary views giving a stable interface > on the database metadata hiding catalogue structure changes. But I > dearly miss some information therein. I created following query to get > the index columns of an index. I fear breakage when not run on the > specific version I developed it against. Is there a more elegant way by > the information_schema? > > with INDEX_COLUMN_VECTOR as( >select > i.indkey >from > pg_catalog.pg_index i >inner join pg_catalog.pg_class c on > i.indexrelid = c.oid >where > c.relname = 'idiom_hist' > ), > COLUMNS as( >select > a.attname, > a.attnum >from > pg_catalog.pg_attribute a >inner join pg_catalog.pg_class c on > a.attrelid = c.oid >where > c.relname = 'idiom' > ) select >c.attname > from >COLUMNS c > inner join INDEX_COLUMN_VECTOR v on >c.attnum = any(v.indkey) > order by >c.attnum asc; > > An other simpler case. > > select >indexname > from >pg_catalog.pg_indexes > where >schemaname = 'act' > and tablename = i_table_name >and indexname = i_table_name || '_hist'; > > > -- > Öffentlicher PGP-Schlüssel: > http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC > -- -- Eric Hanson CEO, Aquameta 503-929-1073
Re: Fwd: A million users
On Wed, Nov 13, 2024 at 12:02 PM wrote:
> I don't have any benchmarks, but the following data point:
>
> We use PostgREST [1] which connects to the database with one
> "authenticator" role. For each request it handles, it does a SET ROLE to
> a role defined in the Authorization Header (JWT).
>
> Some numbers we are running with currently:
> - 1 authenticator role
> - ~ 127.000 user roles granted to "authenticator"
> - ~ 14.000 "scope" roles granted to the user roles (tenants, groups, ..)
> - ~ 15 "context" roles granted to user roles ("admin", "user", ...)
> - ~ 50 "access" roles granted to context roles ("view_x", "do_y", ...)
>
> Only the access roles have any direct privileges granted.
>
> We currently have ~ 700 RLS policies defined. Those are created TO the
> context roles. The policies check the current role's scope roles to
> select "allowed" rows.
>
> In total, we have ~370.000 roles granted to each other (pg_auth_members).
>
> Except for one thing, we have never had any real problems with this. We
> didn't observe anything getting massively worse with many roles, even
> though we use them extensively. RLS policies need to be carefully
> written to get any performance, though.
>
> The one problem we found is:
>
> The first time the authenticator role does a SET ROLE in a session it's
> **terribly** slow. With fewer users back then it took 6-7 minutes to do
> it. Any SET ROLE afterwards in the same session would be fast. Even more
> annoying - killing the session with SET ROLE running would not work
> properly and leave zombie processes. Giving the authenticator role the
> SUPERUSER privilege avoids the problem and makes it instant. However..
> that's not very desirable.
>
> There were some improvements, IIRC in the 17 cycle (?), in that area,
> but I had not have the time to test it with that. We are still on v15
> and the last time I tested this was ~ two years ago. I still wasn't able
> to put together a simple reproducer either.
>
> You should *probably* be better off with your different LOGIN roles, I
> assume the role cache builds up much quicker in that case.
>
I'm really interested in how this works. Role-per-user or even the ability
to have many roles (370k??) seems like a dream come true. But I always was
wary of it because:
a) A connection-per-role hits the ceiling pretty quickly because
connections can't be pooled and shared between users and take up a lot of
memory etc.
b) One could try to get around this with an authenticator role as you
describe, but isn't it then possible to do a RESET ROLE and then another
SET ROLE to get access to another user? This of course would have to be
through SQL injection or some such, but it seems like that defeats at least
some of the purpose of RLS.
Did you find some way to prevent RESET ROLE? I once advocated for a NO
RESET option on SET ROLE [1] so that RESET ROLE would be impossible for the
rest of the session. Still think it would be helpful.
Thanks,
Eric
[1]
https://www.postgresql.org/message-id/flat/CACA6kxgdzt-oForijaxfXHHhnZ1WBoVGMXVwFrJqUu-Hg3C-jA%40mail.gmail.com
Re: Fwd: A million users
On Fri, Nov 22, 2024 at 6:57 AM wrote: > Yeah, this is still on my list of things to research more about > eventually - currently still unsolved. > > For my use-case the NO RESET would need to apply until the end of the > transaction, not end of the session. > > I imagine something like an extension, that would: > - block any SET SESSION ROLE > - block any RESET ROLE > - only allow SET LOCAL ROLE when CURRENT_USER has the right to do so > > Then the effect of SET LOCAL ROLE would still be reversed at the end of > the transaction, but you could never "escape" a SET LOCAL ROLE that was > set earlier. As things are now, would someone be able to do a RESET ROLE if *any* code/function had a SQL injection vulnerability, or only if there was one in the pooler? Or (ideally) neither. That's what a NO RESET option (or some similar functionality) would provide with certainty. I found this extension: https://github.com/pgaudit/set_user but haven't used it. Seems to address this though, they introduce a set_session_auth(token) function and then reset_role requires the token if session_auth has been set. Thanks, Eric
