Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"
> rjuju...@gmail.com wrote: > >> adrian.kla...@aklaver.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> I s'pose that I can interpret this output in the light of the "miriam" >>> example by guessing than an empty LHS means "public" and that the initial >>> "X" means "execute". It looks like what follows the slash is the owner of >>> the object (a denormalization of what the "Owner" column shows.) Where is >>> this notation, "miriam=arwdDxt/miriam", explained? >> >> Here: >> Table 5.1. ACL Privilege Abbreviations >> https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE > > You might also be interested in aclexplode() function, see: > 9.26. System Information Functions and Operators > https://www.postgresql.org/docs/current/functions-info.html Thanks, Adrian. I'm embarrassed that I didn't notice Table 5.1. Thanks, Julien. I tried a little test. I created a function "s.q()" with owner "x" and then granted "execute" on it to user "z". But I didn't yet revoke "execute" on "s.q()" from "public". This is whet the "Access privilege" column in the \df+ report for "s.q()" shows: =X/x x=X/x z=X/x I.e. three facts per row: grantee, privilege, and grantee. Then I did this: with c as ( select proname::text as name, pronamespace::regnamespace::text as schema, aclexplode(proacl)as "aclexplode(proacl)" from pg_catalog.pg_proc) select "aclexplode(proacl)" from c where name = 'q' and schema = 's'; This is the result: aclexplode(proacl) - (1494148,0,EXECUTE,f) (1494148,1494148,EXECUTE,f) (1494148,1494150,EXECUTE,f) This is consistent with the doc that says the array is exploded to records with this signature: (grantor oid, grantee oid, privilege_type text, is_grantable boolean ) This is the perfect starting point for the table function that I was after that would list all user-defined functions and procedures that have "execute" granted to "public". A little bit of perfectly manageable effort will be needed for the special case that when "proacl" is "null", it means that "public" has "execute"—and also to translate the "oid" values" to text. I'll try this presently and report back.
Re: Can we go beyond the standard to make Postgres radically better?
On 2022-02-12 01:18:04 +0100, Andreas 'ads' Scherbaum wrote: > On 10/02/2022 18:22, Peter J. Holzer wrote: > > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote: > > > Examples of small things Postgres could have: > > > > > >• SELECT * - b.a_id from a natural join b > > > > My use case for such a feature are tables which contain one column (or a > > small number of columns) which you usually don't want to select: A bytea > > column or a very wide text column. In a program I don't mind (in fact I > > prefer) listing all the columns explicitely, but exploring a database > > interactively with psql typing lots of column names is tedious > > (especially since autocomplete doesn't work here). > > Maybe for this specific use case it's easier to teach psql how to do that, > instead of trying to amend the SQL implementation? Example: > > SELECT * \- col1 \- col2 FROM table > > psql looks up the columns, translates * into the actual list minus these two > columns and lets you continue entering the query. I think the easiest way to get the columns would be to EXPLAIN(verbose) the query. Otherwise psql (or whatever your shell is) would have to completely parse the SQL statement to find the columns. (On a tangent, I'm wondering if this could work for autocomplete. The problem with autocomplete is of course that you probably don't have a syntactically correct query at the time you need it. So the editor would have to patch that up before sending it to the database.) hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Babelfish for PostgreSQL
Hi! There is a method of simulating SQL Server by using PostgreSQL. The extension is called "Babelfish": https://babelfishpg.org/ However, this extension requires modifications to the PostgreSQL source and building the PostgreSQL with Babelfish modifications from source. While it is a rather seamless process and goes on without much trouble, it is still much more complicated than installing the RPM or DEB packages, depending on your Linux distro of choice. Is there any chance that the Babelfish modifications will be adopted in the PostgreSQL source proper? From my DBA point of view, I didn't find any differences in the functionality of the PostgreSQL database itself. Amazon RDS incorporates that modification and allows creating Aurora PostgreSQL database with the Babelfish extension from the menu. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Can we go beyond the standard to make Postgres radically better?
On 12/02/2022 20:50, Peter J. Holzer wrote: On 2022-02-12 01:18:04 +0100, Andreas 'ads' Scherbaum wrote: On 10/02/2022 18:22, Peter J. Holzer wrote: On 2022-02-09 21:14:39 -0800, Guyren Howe wrote: Examples of small things Postgres could have: • SELECT * - b.a_id from a natural join b My use case for such a feature are tables which contain one column (or a small number of columns) which you usually don't want to select: A bytea column or a very wide text column. In a program I don't mind (in fact I prefer) listing all the columns explicitely, but exploring a database interactively with psql typing lots of column names is tedious (especially since autocomplete doesn't work here). Maybe for this specific use case it's easier to teach psql how to do that, instead of trying to amend the SQL implementation? Example: SELECT * \- col1 \- col2 FROM table psql looks up the columns, translates * into the actual list minus these two columns and lets you continue entering the query. I think the easiest way to get the columns would be to EXPLAIN(verbose) the query. Otherwise psql (or whatever your shell is) would have to completely parse the SQL statement to find the columns. (On a tangent, I'm wondering if this could work for autocomplete. The problem with autocomplete is of course that you probably don't have a syntactically correct query at the time you need it. So the editor would have to patch that up before sending it to the database.) I was thinking about this problem for a while, and it's not easy to solve. Hence I came up with the idea that psql could - once the table is known and very specific psql syntax is there (\- as example) replace the * with the actual columns. All of this before the query is run, and as a user you can edit the column list further. The main concern listed further upstream is "surfing the database", in interactive mode. Not the first time I hear this problem. Solving this specific case might reduce the need for a SQL extenson. Note: the attempt above is just an idea, not an actual proposal how to implement this. Regards, -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project
Re: Can we go beyond the standard to make Postgres radically better?
On 2022-02-12 20:50:57 +0100, Peter J. Holzer wrote: > On 2022-02-12 01:18:04 +0100, Andreas 'ads' Scherbaum wrote: > > On 10/02/2022 18:22, Peter J. Holzer wrote: > > > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote: > > > > Examples of small things Postgres could have: > > > > > > > >• SELECT * - b.a_id from a natural join b > > > > > > My use case for such a feature are tables which contain one column (or a > > > small number of columns) which you usually don't want to select: A bytea > > > column or a very wide text column. In a program I don't mind (in fact I > > > prefer) listing all the columns explicitely, but exploring a database > > > interactively with psql typing lots of column names is tedious > > > (especially since autocomplete doesn't work here). > > > > Maybe for this specific use case it's easier to teach psql how to do that, [...] > I think the easiest way to get the columns would be to EXPLAIN(verbose) > the query. Otherwise psql (or whatever your shell is) would have to > completely parse the SQL statement to find the columns. A shell could also provide an "expand select list" function using explain. In fact, you can sort of do that manually: 1) Prefix your query with explain(verbose) 2) Copy the "Output:" line of the top node. 3) Edit your query, remove the explain(verbose) and replace the select list with the content of the clipboard 4) (optional) remove any unwanted columns A bit cumbersome but less cumbersome than typing/copying lots of column names from the result of a previous query or \d. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Can we go beyond the standard to make Postgres radically better?
On 2/12/22 13:17, Peter J. Holzer wrote: On 2022-02-12 20:50:57 +0100, Peter J. Holzer wrote: On 2022-02-12 01:18:04 +0100, Andreas 'ads' Scherbaum wrote: On 10/02/2022 18:22, Peter J. Holzer wrote: On 2022-02-09 21:14:39 -0800, Guyren Howe wrote: Examples of small things Postgres could have: • SELECT * - b.a_id from a natural join b My use case for such a feature are tables which contain one column (or a small number of columns) which you usually don't want to select: A bytea column or a very wide text column. In a program I don't mind (in fact I prefer) listing all the columns explicitely, but exploring a database interactively with psql typing lots of column names is tedious (especially since autocomplete doesn't work here). Maybe for this specific use case it's easier to teach psql how to do that, [...] I think the easiest way to get the columns would be to EXPLAIN(verbose) the query. Otherwise psql (or whatever your shell is) would have to completely parse the SQL statement to find the columns. A shell could also provide an "expand select list" function using explain. In fact, you can sort of do that manually: 1) Prefix your query with explain(verbose) 2) Copy the "Output:" line of the top node. 3) Edit your query, remove the explain(verbose) and replace the select list with the content of the clipboard 4) (optional) remove any unwanted columns Or: \pset format csv select * from cell_per limit 0; line_id,category,cell_per,ts_insert,ts_update,user_insert,user_update,plant_type,season,short_category Longer version: \pset format unaligned \pset fieldsep ',' select * from cell_per limit 0; line_id,category,cell_per,ts_insert,ts_update,user_insert,user_update,plant_type,season,short_category (0 rows) A bit cumbersome but less cumbersome than typing/copying lots of column names from the result of a previous query or \d. hp -- Adrian Klaver adrian.kla...@aklaver.com
Re: Can we go beyond the standard to make Postgres radically better?
On 2022-02-12 22:09:25 +0100, Andreas 'ads' Scherbaum wrote: > On 12/02/2022 20:50, Peter J. Holzer wrote: > > On 2022-02-12 01:18:04 +0100, Andreas 'ads' Scherbaum wrote: > > > On 10/02/2022 18:22, Peter J. Holzer wrote: > > > > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote: > > > > > Examples of small things Postgres could have: > > > > > > > > > > • SELECT * - b.a_id from a natural join b [...] > > > Maybe for this specific use case it's easier to teach psql how to do that, [...] > > I think the easiest way to get the columns would be to EXPLAIN(verbose) > > the query. Otherwise psql (or whatever your shell is) would have to > > completely parse the SQL statement to find the columns. > > > > (On a tangent, I'm wondering if this could work for autocomplete. The > > problem with autocomplete is of course that you probably don't have > > a syntactically correct query at the time you need it. So the editor > > would have to patch that up before sending it to the database.) > > I was thinking about this problem for a while, and it's not easy to solve. > Hence I came up with the idea that psql could - once the table is known > and very specific psql syntax is there (\- as example) replace the * with > the actual columns. All of this before the query is run, and as a user you > can edit the column list further. Yeah, but the problem is that it isn't that easy for psql to figure out which table is involved. The query may involve joins, subquerys, CTEs (and possibly other stuff I forgot). So it would have to parse the query (which it currently has no need to do - it can just send it as it is to the server) to find out which tables are involved, what columns they have, how those columns are transformed, etc. Quite a bit of work and it has to do it in the same way as the server (psql has a bit of advantage there because it's in the same code base so it could probably borrow some code from the server, but think of other shells like PgAdmin, which aren't even in the same programming language). So that was my first idea but I discarded that as too complicated. Then I thought about running the query with «limit 0» to get the list of columns. But that's unsafe - the query might change some data; you don't want that to happen automatically. So my third idea was to use explain to get the list of columns. I think that's safe in that the code is never actually run. But it is a query that can fail - which aborts the transaction. So you probably don't want your shell to do that automatically, either. (OTOH, the query would very likely have failed anyway.) hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Can we go beyond the standard to make Postgres radically better?
On 2022-02-12 13:23:39 -0800, Adrian Klaver wrote: > On 2/12/22 13:17, Peter J. Holzer wrote: > > A shell could also provide an "expand select list" function using > > explain. > > > > In fact, you can sort of do that manually: > > > > 1) Prefix your query with explain(verbose) > > 2) Copy the "Output:" line of the top node. > > 3) Edit your query, remove the explain(verbose) and replace the select > > list with the content of the clipboard > > 4) (optional) remove any unwanted columns > > Or: > > \pset format csv > > select * from cell_per limit 0; > > line_id,category,cell_per,ts_insert,ts_update,user_insert,user_update,plant_type,season,short_category Good idea. Even better: select * from cell_per limit 0 \g (format=csv) Saves you having to stash the query somewhere. (You still have to look out for duplicate column names, though) hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Can we go beyond the standard to make Postgres radically better?
On 12/02/2022 22:34, Peter J. Holzer wrote: On 2022-02-12 22:09:25 +0100, Andreas 'ads' Scherbaum wrote: On 12/02/2022 20:50, Peter J. Holzer wrote: On 2022-02-12 01:18:04 +0100, Andreas 'ads' Scherbaum wrote: On 10/02/2022 18:22, Peter J. Holzer wrote: On 2022-02-09 21:14:39 -0800, Guyren Howe wrote: Examples of small things Postgres could have: • SELECT * - b.a_id from a natural join b [...] Maybe for this specific use case it's easier to teach psql how to do that, [...] I think the easiest way to get the columns would be to EXPLAIN(verbose) the query. Otherwise psql (or whatever your shell is) would have to completely parse the SQL statement to find the columns. (On a tangent, I'm wondering if this could work for autocomplete. The problem with autocomplete is of course that you probably don't have a syntactically correct query at the time you need it. So the editor would have to patch that up before sending it to the database.) I was thinking about this problem for a while, and it's not easy to solve. Hence I came up with the idea that psql could - once the table is known and very specific psql syntax is there (\- as example) replace the * with the actual columns. All of this before the query is run, and as a user you can edit the column list further. Yeah, but the problem is that it isn't that easy for psql to figure out which table is involved. The complaint is not about complex queries, or CTEs, or Joins. This is about simple queries where a user wants to discover - surf - the database and look into specific tables, but exclude certain columns. More specifically, this is when the user types in interactive queries. Today psql tries to do autocomplete for certain scenarios, this too does not work in complex queries, but nevertheless is a useful help if someone tries to run simple, interactive queries. Same can be true for the reserve situation where someone wants to exclude specific columns, basically expanding * to a column list. Regards, -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project
Re: Can we go beyond the standard to make Postgres radically better?
On 2/12/22 19:11, Andreas 'ads' Scherbaum wrote: The complaint is not about complex queries, or CTEs, or Joins. This is about simple queries where a user wants to discover - surf - the database and look into specific tables, but exclude certain columns. More specifically, this is when the user types in interactive queries. There is already something very similar to what you are describing: https://www.psycopg.org/docs/cursor.html Each cursor has its description, which consists of the column descriptions. Basically, it's like doing \d on a cursor. Unfortunately, it's not interactive, one has to do some pythong programming in order do to that. Unfortunately, it is not possible to just "describe the cursor", the description becomes available after the "execute" call. Hopefully, I understood you correctly. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
