Mapping view columns to their source columns
Hi, I'm trying to map view columns to their source columns using the system catalogs and information schema, but not having much luck. It's easy to determine which columns a view *depends *on, but not how those columns are mapped to the columns of the view. It seems like the only way to do is this is to somehow query the pg_node_tree in pg_rewrite.ev_call. I'm trying to do this to provide meta data for an API. For example: - a database design's API is implemented via functions - a function returns a setof a particular view - the web server that is serving the data might want to manipulate the returned data based on the types/constraints of the underlying columns of the view. It seems like mapping view columns to their source columns should be something that's been done before. Is it possible? Matt Andrews 0400 990 131
Re: Mapping view columns to their source columns
After thinking about this one for a while, I imagined even more nightmarish scenarios than what you've just described here, and mapping the source columns no longer seems like a viable idea. Fortunately, there are a few work arounds I can rely on that particular to our database design, which means I will be able to use the system catalogs in a round about way. Incidentally, I have become interested in the pg_node_tree type. I can't find much info on it. Would that be in the source? What would be the first steps for writing an extension for this sort of thing? Matt 0400 990 131 On Wed, Sep 25, 2019 at 2:54 PM Tom Lane wrote: > Matt Andrews writes: > > I'm trying to map view columns to their source columns using the system > > catalogs and information schema, but not having much luck. It's easy to > > determine which columns a view *depends *on, but not how those columns > are > > mapped to the columns of the view. It seems like the only way to do is > this > > is to somehow query the pg_node_tree in pg_rewrite.ev_call. > > Yeah, that's what you'd have to do. The system doesn't track this > any more finely than "does the view as a whole depend on this column", > partly because we don't need to and partly because it's hard to define > reasonably. What do you want to do with, say, > > select a, b+c from tab; > > It gets even more interesting when you think about aggregates and > other advanced features: > > select a, max(b) as mb from tab group by a; > > Any reasonable semantic analysis would have to conclude that the > mb column depends on both a and b. > > Once you had answers to these definitional questions, you could > think about analyzing the view's query tree to get the answers > you want. Unfortunately, doing that from client side is not > supported at all; if you try you'll find yourself maintaining > a lot of messy code that we *will* break on a regular basis. > It would be less hard in a backend extension, but I suspect > you don't want to go there :-( > > regards, tom lane >
Users, Roles and Connection Pooling
Hey all, Here’s a question I’ve been asking for a while and just can’t find an answer to, so I thought I’d ask it here. The answer could be subjective, but here goes... When a web app connects to Postgres via a connection pooler, what is the best way to manage privileges for the connecting user? Especially when their is a complex hierarchy of privileges? Should each signed up user have their own role which inherits from whichever roles they are members of? This means that the connection pool role should then be granted in every user? Does creating a Postgres role for every app user scale? Roles can only have names, is an email address a good name for individual app users? Are their any resources out there that anyone knows of that addresses these questions? There’s plenty of stuff out there on roles and permissions, I’ve read a lot of it, but not much on dealing with individual app users through a connection pool. I’m thinking that any queries should start with SET ROLE ‘username’ and end with RESET ROLE. Is this how it could work? Any help would be greatly appreciated. -- Matt Andrews 0400 990 131
Re: Users, Roles and Connection Pooling
I have little experience in this area, but it seems like having a Postgres role for every application user is the right way to do things. It’s just that it also seems really inconvenient. For example how to map an application’s users/people table to Postgres roles? The pg_role name field is limited to 64 bytes, you can’t create a foreign key to pg_role. What’s the answer? Use UUIDs as usernames or something? There’s very little out there on this topic, but surely this has been done before. On Wed, 2 Oct 2019 at 17:43, Stephen Frost wrote: > Greetings, > > * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > > A couple of pointers: > > I generally agree with these comments. > > > - This is a good setup if you don't have too many users. Metadata > > queries will start getting slow if you get into the tens of thousands > > of users, maybe earlier. > > While this seems plausible- I'd love to hear about exactly what you've > seen start to be a problem when getting up to that many users. Are you > just referring to things like \du? Or..? > > Thanks, > > Stephen > -- Matt Andrews 0400 990 131
Re: Users, Roles and Connection Pooling
Yes, I’ll be more clear with the terminology. When I say user, I mean an individual application user, which most likely is a person. I’m also asking about this in a general sense, being concerned more with implementation details. The Postgres role system is really powerful and versatile, why should it be a problem to create privilege hierarchies and provide individuals with privileges from any branch of the hierarchy? Obviously, designing privileges should be done carefully, but granting roles to users should be easy. I can easily imagine an organisation that would require only a few privileges for many people, but many different privileges for a few people. Does it come down to performance issues when there are many roles to users? On Wed, 2 Oct 2019 at 21:03, Rob Sargent wrote: > > > On Oct 2, 2019, at 3:41 AM, Matt Andrews > wrote: > > I have little experience in this area, but it seems like having a Postgres > role for every application user is the right way to do things. It’s just > that it also seems really inconvenient. > > For example how to map an application’s users/people table to Postgres > roles? The pg_role name field is limited to 64 bytes, you can’t create a > foreign key to pg_role. What’s the answer? Use UUIDs as usernames or > something? > > There’s very little out there on this topic, but surely this has been done > before. > > On Wed, 2 Oct 2019 at 17:43, Stephen Frost wrote: > >> Greetings, >> >> * Laurenz Albe (laurenz.a...@cybertec.at) wrote: >> > A couple of pointers: >> >> I generally agree with these comments. >> >> > - This is a good setup if you don't have too many users. Metadata >> > queries will start getting slow if you get into the tens of thousands >> > of users, maybe earlier. >> >> While this seems plausible- I'd love to hear about exactly what you've >> seen start to be a problem when getting up to that many users. Are you >> just referring to things like \du? Or..? >> >> Thanks, >> >> Stephen >> > The terminology gets a little wonky here since “user” equals “role” in > postgres terms but I’ll apply user to the person using your app. > What are your expected numbers of total distinct users? > Ratio of users to roles (as permissions set) or is every user unique in > access needs? > Do any users need to be in more than one role/group? > When/how will you assign role to user? > I feel these issues will affect your choice of design. > -- Matt Andrews 0400 990 131
