Re: Can we go beyond the standard to make Postgres radically better?

[Ron]

On 2/10/22 10:33 PM, Raymond Brinzer wrote:
[snip]
Here's one that I think is simple:  why would we want a language where the 
clauses must come in a particular order?  `FROM mytable SELECT column` is 
as clear an expression as `SELECT column FROM mytable`, and probably 
better, in that it starts with the source and winnows from there.  
Likewise, the order of WHERE, ORDER BY, and so on don't change what is 
being said.


I believe the "why" is,  "because parsing SQL is hard enough already", but 
that's a problem unto itself.  A language with a more regular syntax is 
easier to work with and improve.


The answer is obvious to every grey beard: SQL was developed from SEQUEL, 
Structured *ENGLISH* Query Language at a company that loved English-style 
programming languages.


"SELECT column FROM mytable WHERE condition" is a perfect declarative 
English sentence that any middle school grammar teacher would be proud of.


"FROM mytable SELECT column"... not so much.

--
Angular momentum makes the world go 'round.

Re: Can we go beyond the standard to make Postgres radically better?

[Daniel Verite]

Peter J. Holzer wrote:

> > 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).
> 
> Forgot to add: I think that the syntax would have to be more explicit.
> It's too easy to mix up
>SELECT * - b.a_id FROM ...
> and
>SELECT *, - b.a_id FROM ...
> 
> Maybe
>SELECT * EXCEPT b.a_id FROM ...

The solution to this by the SQL standard might be that it can be done with
a Polymorphic Table Function, introduced in SQL:2016.

https://webstore.iec.ch/preview/info_isoiec19075-7%7Bed1.0%7Den.pdf

A practical example with the Oracle implementation can be seen here:
https://blogs.oracle.com/sql/post/select-star-except-queries-in-oracle-database

Excluding all columns of a given type from a relation is also possible,
with both the relation and the type as parameters of the PTF.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite

Re: Can we go beyond the standard to make Postgres radically better?

[Imre Samu]

> Give me a couple million bucks, and I’ll hire some of the Postgres devs
to build a new database.
> We could crib some of the low-level code from Postgres, but everything
above the low level would need to be rewritten.

You can check the EdgeDB experiments:https://www.edgedb.com/
*"What is EdgeDB? EdgeDB is a new kind of database that takes the best
parts of relational databases, graph databases, and ORMs. We call it a
graph-relational database."*
*"EdgeDB uses PostgreSQL as its data storage and query execution engine,
benefitting from its exceptional reliability."*
-  (2022) https://www.edgedb.com/blog/edgedb-1-0
-  (2019) We Can Do Better Than SQL:
https://www.edgedb.com/blog/we-can-do-better-than-sql
"The questions we often hear are “Why create a new query language?” and
“What’s wrong with SQL?”. This post contains answers to both. ... "
- EdgeQL:   https://www.edgedb.com/docs/edgeql/index *"EdgeQL is a
spiritual successor to SQL designed with a few core principles in mind."*
- GraphQL:  https://www.edgedb.com/docs/graphql/index   EdgeDB supports
GraphQL queries natively out of the box.
- Github: https://github.com/edgedb/edgedb

EdgeQL example from the blog;

select
  Movie {
title,
rating := math::mean(.ratings.score)
actors: {
  name
} order by @credits_order
  limit 5,
  }
filter
  "Zendaya" in .actors.name


Regards,
 Imre



Guyren Howe  ezt írta (időpont: 2022. febr. 11., P, 7:43):

> I get all this. Give me a couple million bucks, and I’ll hire some of the
> Postgres devs to build a new database. We could crib some of the low-level
> code from Postgres, but everything above the low level would need to be
> rewritten.
>
> I was proposing more that we at least provide higher-level, more general,
> orthogonal etc features in the SQL we have now. eg first-class functions
> could be added to SQL reasonably easily.
> On Feb 10, 2022, 22:32 -0800, Tom Lane , wrote:
>
> Raymond Brinzer  writes:
>
> Will it be accepted here? I don't know; I'm not an insider, or in a
> position to say. But it'd be a much better pitch than a pep talk, or
> speaking in generalities about SQL. And that's coming from someone who
> actually agrees with you. I'm 100% on board with the idea that something
> better is (badly) needed. But is the idea, here, really to talk a highly
> successful project into doing a 180 based on this sort of argument? If
> only the people writing the code saw the light, they'd go read the Datomic
> site, and start overhauling PostgreSQL?
>
>
> Nah, probably not. I mean, not only are we implementing SQL, but
> we're implementing it in C. I used better languages than C back
> in the seventies ... but here we are. Practical acceptance is
> all about infrastructure and compatible tooling, which SQL and C
> both have in spades, while academic designs really don't.
>
> Also, I fear this discussion underestimates the difficulty of
> putting some other query language on top of Postgres. I know
> you'll say "but the Berkeley guys pasted SQL onto a QUEL engine
> back when, so how hard can it be?" In the first place, that
> was done on top of maybe ten years worth of work, but now there's
> another twenty-five years of development agglomerated on top of
> that. So moving things would be more than 3X harder, even if
> you make the very-naive assumption that the difficulty is merely
> linear. In the second place, QUEL and SQL aren't that far apart
> conceptually, and yet we've still had lots of problems that can
> be traced to their incompatibilities. Something that was really
> different from SQL would be a nightmare to embed into PG. I'll
> just point out one example: if you don't like SQL's semantics for
> NULL (which no I don't much like either), changing that would
> probably require touching tens of thousands of lines of code just
> in the PG core, never mind breaking every API used by extensions.
>
> So for better or worse, Postgres is a SQL engine now. If you
> want Datalog or $other_language, you'd be better off starting
> or contributing to some other project.
>
> That's not to say that we can't do stuff around the margins.
> The idea of "select all columns except these", for instance,
> has been discussed quite a bit, and would probably happen if
> we could get consensus on the syntax. But we're not going to
> throw away thirty-five years' worth of work to chase some
> blue-sky ideas.
>
> regards, tom lane
>
>

Re: Can we go beyond the standard to make Postgres radically better?

[Mladen Gogala]

On 2/10/22 23:56, Guyren Howe wrote:

On Feb 10, 2022, at 17:06 , Mladen Gogala  wrote:



But SQL is a terrible, no good, very bad language.



I cannot accept such a religious persecution of SQL without a 
detailed explanation.




I feel like anyone who is defending SQL here isn’t aware of how much 
better the alternatives are, and how bad SQL really is.


I mean: it’s hard to write, hard to read. It’s hard to generate. But 
that’s just the starting point.


OK. If there are better alternatives, I am sure you will be able to sell 
them to the world. Establish a company and have a go at it.





One of the worst things about it that I don’t see much discussed is 
that it imposes assumptions about the storage model that aren’t part 
of the relational model. Like heavyweight, hard to change tables with 
transactional guarantees and such. Don’t get me wrong, those things 
are great to have available, but I don’t need them all the time.


Storage model and implementation are not part of SQL for good reason. 
Database vendors have different implementations. MySQL and Postgres are 
different. MySQL storage engines differ among themselves. Both of them 
are different from SQL Server which is in turn different from Oracle and 
DB2. Storage model determines the implementation. When there is only a 
single relational database vendor left in the market, then they can burn 
their storage implementation into the language standard. Until then, the 
more, the merrier.






The whole NoSQL movement has been such a tragedy. Having diagnosed a 
problem with SQL databases, they threw out the relational model and 
very often reimplemented a form of SQL when they should have done the 
opposite. There is no reason you can’t have a relational database with 
an on-demand schema, with eventual consistency, with all those fun 
things that various NoSQL databases provide. Those storage models have 
their place, but the SQL standard says you can’t use them.


But the biggest issue is the verbose, terrible, very bad query 
language. In the web development community where I spend my time, it 
is almost holy writ to treat the database as a dumb data bucket, and 
do everything in the application layer (even things like validations, 
even when that is a provably incorrect approach). Why? I think it’s 
because they’re used to working in a pleasant language like Ruby or 
Python, and they want to do everything there. And who can blame them?


As a database architect who has successfully bridged two very different 
database systems, I can tell you that the application programmers put 
the business logic into the application because they frequently don't 
know what the options are. They know Java or Python and that's what they 
do, period. That has nothing to do with SQL.




But this is bad. Proper relational design can take over much (most!) 
of the design of a typical business app, with significant efficiency 
gains the result. But no *community* is going to choose that when most 
of the members of the community don’t want to learn SQL and who can 
blame them?
Business community which hires them to make efficient applications can 
blame them. And frequently does so.


Another issue: everyone thinks “relational” is the same thing as 
“SQL”. If we could get folks to break that association, then relations 
should be a standard feature of high-level programming languages, just 
as arrays and hashes are.


Heck, give me a functional programming language with a good relational 
model, and I can implement OOP in that relational language without 
breaking a sweat.


Software *should* be designed around a logical/relational layer with 
minimal occasional forays into Turing completeness where necessary. 
But no-one is even thinking about software like that because 
relational is SQL and SQL is awful.


There is such thing as "free market". If you offer them a better 
alternative, people will come. You may be the next Larry Ellison. And 
then again, you may be not. There is only one way to tell, and that's 
not proselytizing on the Postgres mailing list.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Can we go beyond the standard to make Postgres radically better?

[Benedict Holland]

So to summarize, people are bad programmers who refuse to learn SQL So SQL
is the problem? Common. You cannot bring that to a postgres list serve.

Look. It's not perfect. It's a pain. It is hard to generate queries (oh my
God why are you doing this?) and it's hard to work with. You are describing
c++ to Python programmers and arguing why no one should ever use c++. And
look, there are other languages that are way better like Python. So why
would anyone ever chose c++?

Because the application is already in c++. Companies store petabytes of
data in SQL databases. With very simple commands, you can ensure that a
table with a billion rows has 10-way combination of unique values. Is FROM
X select Y better? Probably. Is print(x) better than cout << x.p() << endl;
? Yep. But it would take some serious guts to wander over to a C++ list and
explain how it is the worst language and needs to be scrapped and there are
way better languages out there.

Also, no one really argues for sql. We know it isn't great but we also know
why it likely can't change and it works.


On Fri, Feb 11, 2022, 9:26 AM Mladen Gogala  wrote:

> On 2/10/22 23:56, Guyren Howe wrote:
>
> On Feb 10, 2022, at 17:06 , Mladen Gogala  wrote:
>
>
> But SQL is a terrible, no good, very bad language.
>
>
> I cannot accept such a religious persecution of SQL without a detailed
> explanation.
>
>
> I feel like anyone who is defending SQL here isn’t aware of how much
> better the alternatives are, and how bad SQL really is.
>
> I mean: it’s hard to write, hard to read. It’s hard to generate. But
> that’s just the starting point.
>
> OK. If there are better alternatives, I am sure you will be able to sell
> them to the world. Establish a company and have a go at it.
>
>
>
> One of the worst things about it that I don’t see much discussed is that
> it imposes assumptions about the storage model that aren’t part of the
> relational model. Like heavyweight, hard to change tables with
> transactional guarantees and such. Don’t get me wrong, those things are
> great to have available, but I don’t need them all the time.
>
> Storage model and implementation are not part of SQL for good reason.
> Database vendors have different implementations. MySQL and Postgres are
> different. MySQL storage engines differ among themselves. Both of them are
> different from SQL Server which is in turn different from Oracle and DB2.
> Storage model determines the implementation. When there is only a single
> relational database vendor left in the market, then they can burn their
> storage implementation into the language standard. Until then, the more,
> the merrier.
>
>
>
>
> The whole NoSQL movement has been such a tragedy. Having diagnosed a
> problem with SQL databases, they threw out the relational model and very
> often reimplemented a form of SQL when they should have done the opposite.
> There is no reason you can’t have a relational database with an on-demand
> schema, with eventual consistency, with all those fun things that various
> NoSQL databases provide. Those storage models have their place, but the SQL
> standard says you can’t use them.
>
> But the biggest issue is the verbose, terrible, very bad query language.
> In the web development community where I spend my time, it is almost holy
> writ to treat the database as a dumb data bucket, and do everything in the
> application layer (even things like validations, even when that is a
> provably incorrect approach). Why? I think it’s because they’re used to
> working in a pleasant language like Ruby or Python, and they want to do
> everything there. And who can blame them?
>
> As a database architect who has successfully bridged two very different
> database systems, I can tell you that the application programmers put the
> business logic into the application because they frequently don't know what
> the options are. They know Java or Python and that's what they do, period.
> That has nothing to do with SQL.
>
>
> But this is bad. Proper relational design can take over much (most!) of
> the design of a typical business app, with significant efficiency gains the
> result. But no *community* is going to choose that when most of the members
> of the community don’t want to learn SQL and who can blame them?
>
> Business community which hires them to make efficient applications can
> blame them. And frequently does so.
>
>
> Another issue: everyone thinks “relational” is the same thing as “SQL”. If
> we could get folks to break that association, then relations should be a
> standard feature of high-level programming languages, just as arrays and
> hashes are.
>
> Heck, give me a functional programming language with a good relational
> model, and I can implement OOP in that relational language without breaking
> a sweat.
>
> Software *should* be designed around a logical/relational layer with
> minimal occasional forays into Turing completeness where necessary. But
> no-one is even thinking about software like that be

table not found on publisher

[Radoslav Nedyalkov]

Hello All,
It is a bit specific logical replication setup where we try to replicate
a partitioned table (pg14.1) to a non-partitioned one (pg11.14)

After establishing everything the subscriber fails on the initial copy with
ERROR:  table "public.tab01" not found on publisher

If the subscription is created with (copy_data=false) changes are
propagated okay.

So I'm puzzled. Any comments ?

Thanks a lot
Rado

Here is the example:
SOURCE:
test[14.1]=# CREATE TABLE tab01 (id int PRIMARY KEY) PARTITION BY RANGE(id);
CREATE TABLE
test[14.1]=# CREATE TABLE tab01_10 PARTITION OF tab01 FOR VALUES FROM (0)
TO (10);
CREATE TABLE
test[14.1]=# CREATE TABLE tab01_20 PARTITION OF tab01 FOR VALUES FROM (10)
TO (20);
CREATE TABLE
test[14.1]=# insert into tab01 values (generate_series(1,15));
INSERT 0 15
test[14.1]=# CREATE PUBLICATION pub01 FOR TABLE public.tab01 WITH
(publish_via_partition_root = true);
CREATE PUBLICATION
test[14.1]=#

TARGET:
test[11.14]=# CREATE TABLE tab01 (id int PRIMARY KEY);
CREATE TABLE
test[11.14]=# create subscription sub01
connection 'host=/var/run/postgresql port=5435 dbname=test user=postgres'
publication pub01 ;
NOTICE:  created replication slot "sub01" on publisher
CREATE SUBSCRIPTION
test[11.14]=#

Re: Can we go beyond the standard to make Postgres radically better?

[Mladen Gogala]

On 2/11/22 09:48, Benedict Holland wrote:
So to summarize, people are bad programmers who refuse to learn SQL So 
SQL is the problem? Common. You cannot bring that to a postgres list 
serve.


Look. It's not perfect. It's a pain. It is hard to generate queries 
(oh my God why are you doing this?) and it's hard to work with. You 
are describing c++ to Python programmers and arguing why no one should 
ever use c++. And look, there are other languages that are way better 
like Python. So why would anyone ever chose c++?


There is probably a misunderstanding here. I haven't talked about 
people, I leave that to politicians. Second, I was defending SQL. You've 
got me confused with somebody else. Last but not least, I didn't bring 
anything to this list, I was just responding to the posts.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Can we go beyond the standard to make Postgres radically better?

[Rob Sargent]

On 2/11/22 09:12, Mladen Gogala wrote:

On 2/11/22 09:48, Benedict Holland wrote:
So to summarize, people are bad programmers who refuse to learn SQL 
So SQL is the problem? Common. You cannot bring that to a postgres 
list serve.


Look. It's not perfect. It's a pain. It is hard to generate queries 
(oh my God why are you doing this?) and it's hard to work with. You 
are describing c++ to Python programmers and arguing why no one 
should ever use c++. And look, there are other languages that are way 
better like Python. So why would anyone ever chose c++?


There is probably a misunderstanding here. I haven't talked about 
people, I leave that to politicians. Second, I was defending SQL. 
You've got me confused with somebody else. Last but not least, I 
didn't bring anything to this list, I was just responding to the posts.




Can we get back to discussing the code of conduct now?

Re: Can we go beyond the standard to make Postgres radically better?

[Raymond Brinzer]

On Fri, Feb 11, 2022 at 3:16 AM Ron  wrote:

> On 2/10/22 10:33 PM, Raymond Brinzer wrote:
>


> The answer is obvious to every grey beard: SQL was developed from SEQUEL,
> Structured *ENGLISH* Query Language at a company that loved English-style
> programming languages.
>
> "SELECT column FROM mytable WHERE condition" is a perfect declarative
> English sentence that any middle school grammar teacher would be proud of.
>
> "FROM mytable SELECT column"... not so much.
>

They're both perfectly good English; the order just changes the emphasis.
That's the particularly annoying bit:  we get all the bad things about
English grammar, and none of the flexibility or beauty.

First thing that came to mind was the beginning of Marcus Aurelius'
Meditations:  "From my grandfather Verus I learned to relish the beauty of
manners, and to restrain all anger."  That's a translation of course, but
into solid English.  Putting what he learned first would not only be dull,
it would obscure the fact that he's giving credit.

-- 
Ray Brinzer

"grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Bryn Llewellyn]

*Summary*

If user "x" owns function "s.f()", and if you want user "z" to be able to 
execute it, then this alone is insufficient:

grant execute on function s.f() to z;

The attempt by "z" to execute "s.f()" this draws the 42501 error, "permission 
denied for schema s". But this _is_ sufficient:

grant usage on schema s to z;
revoke execute on function s.f() from z; -- Yes, really!

*This surprises me*

The PG doc on, in the "5.7. Privileges" section at 
https://www.postgresql.org/docs/current/ddl-priv.html 
 (under "USAGE" 
following "The available privileges are"), says this:

«
For schemas, allows access to objects contained in the schema (assuming that 
the objects' own privilege requirements are also met). Essentially this allows 
the grantee to “look up” objects within the schema...
»

Notice « assuming that the objects' own privilege requirements are also met ». 
I read this to mean that in my use case I must _both_ grant "usage" on the 
schema in question _and_ grant "execute" on the function in question—and this 
would make sense as part of a sound functional spec for the privileges model.

But my self-contained test-case, copied below, shows an outcome that's at odds 
with the doc. It does a bit more than what my summary describes because it 
creates two functions "s.f()" and "s.g()" owned, respectively, by "x" and "y". 
(It finishes silently because I use "assert" statements in PL/pgSQL to 
demonstrate the outcomes.)

There seems, then, to be no fine-grained control. I didn't type up other legs 
to the test (for example, to test selecting from a table) but it looks on its 
face as if "grant usage on schema" confers the ability to operate on every 
single object in the schema no matter, what the owner and the object type are.

Is the design of my test-case faulty? Have I found a bug? Or is the doc wrong?

*Test-case*



\c postgres postgres
set client_min_messages = warning;
drop database if exists db;
create database db owner postgres;

\c db postgres
set client_min_messages = warning;
drop schema if exists public cascade;
create schema s authorization postgres;

drop user if exists x;
create user x login password 'p';
drop user if exists y;
create user y login password 'p';
drop user if exists z;
create user z login password 'p';

create function s.f(i in int)
  returns int
  language plpgsql
as $body$
begin
  return i*2;
end;
$body$;

alter function s.f(int) owner to x;

create function s.g(i in int)
  returns int
  language plpgsql
as $body$
begin
  return i*3;
end;
$body$;

alter function s.g(int) owner to y;

select
  pronameas "Name",
  pronamespace::regnamespace as "Schema",
  proowner::regrole  as "Owner"
from pg_catalog.pg_proc
where pronamespace::regnamespace::text = 's';

/*
 Name | Schema | Owner 
--++---
 f| s  | x
 g| s  | y
*/;

-- Fom the PG doc:
-- If a superuser issue a GRANT or REVOKE command,
-- the command is performed as though it were issued
-- by the owner of the affected object.
grant execute on function s.f(int) to z;
grant execute on function s.g(int) to z;



\c db z
set client_min_messages = warning;

do $body$
declare
  msg text not null := '';
begin
  begin
assert s.f(17) = 34;
assert false, 'Should not get here';
  exception when insufficient_privilege then
get stacked diagnostics msg  = message_text;
assert msg = 'permission denied for schema s', 'bad message';
  end;
  begin
assert s.g(17) = 51;
assert false, 'Should not get here';
  exception when insufficient_privilege then
get stacked diagnostics msg  = message_text;
assert msg = 'permission denied for schema s', 'bad message';
  end;
end;
$body$;



\c db postgres
set client_min_messages = warning;

grant usage on schema s to z;
revoke execute on function s.f(int) from z;
revoke execute on function s.g(int) from z;



\c db z
set client_min_messages = warning;

-- The "execute" privilege isn't needed.
do $body$ begin assert s.f(17) = 34; end; $body$;
do $body$ begin assert s.g(17) = 51; end; $body$;

Re: Can we go beyond the standard to make Postgres radically better?

[Peter J. Holzer]

On 2022-02-10 16:13:33 -0500, Bruce Momjian wrote:
> On Thu, Feb 10, 2022 at 06:25:45PM +0100, Peter J. Holzer wrote:
> > On 2022-02-10 18:22:29 +0100, Peter J. Holzer wrote:
> > > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
> > > >   • 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).
> > 
> > Forgot to add: I think that the syntax would have to be more explicit.
[...]
> > Maybe
> > SELECT * EXCEPT b.a_id FROM ...
> 
> Yes, this was proposed on hackers a few months ago and a patch was
> proposed:
> 
>   
> https://www.postgresql.org/message-id/flat/892708.1634233481%40sss.pgh.pa.us#1f17923ad50a1442867162991c54ead9

Interesting idea, but quite different, actually: That puts the exclusion
into the table definition instead of the query.

But I think if I want to bake that into my data model I'll just use a
view.

But that thread led me back to a discussion on this list from almost
exactly 2 years ago ...

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: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Tom Lane]

Bryn Llewellyn  writes:
> If user "x" owns function "s.f()", and if you want user "z" to be able to 
> execute it, then this alone is insufficient:

> grant execute on function s.f() to z;

> The attempt by "z" to execute "s.f()" this draws the 42501 error, "permission 
> denied for schema s". But this _is_ sufficient:

> grant usage on schema s to z;
> revoke execute on function s.f() from z; -- Yes, really!

> *This surprises me*

It shouldn't.  Per the docs, the default permissions on a function
include GRANT EXECUTE TO PUBLIC.  Revoking the never-granted-in-the-
first-place permission to z doesn't remove the PUBLIC permission.

So, if you want to be selective about who can use your functions,
you should revoke the PUBLIC permission and then grant out
permissions to individual roles.

regards, tom lane

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[David G. Johnston]

On Fri, Feb 11, 2022 at 1:46 PM Bryn Llewellyn  wrote:

>
>
> *grant usage on schema s to z;revoke execute on function s.f() from z; --
> Yes, really!*
>
> **This surprises me**
>
> The PG doc on, in the "5.7. Privileges" section at
> https://www.postgresql.org/docs/current/ddl-priv.html (under "USAGE"
> following "The available privileges are"), says this:
>
> «
> For schemas, allows access to objects contained in the schema (assuming
> that the objects' own privilege requirements are also met). Essentially
> this allows the grantee to “look up” objects within the schema...
> »
>


> *Is the design of my test-case faulty? Have I found a bug? Or is the doc
> wrong?*
>

WADaD

https://www.postgresql.org/docs/current/ddl-priv.html

"No privileges are granted to PUBLIC by default on tables, table columns,
sequences, foreign data wrappers, foreign servers, large objects, schemas,
or tablespaces. For other types of objects, the default privileges granted
to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables)
privileges for databases; ***EXECUTE privilege for functions and procedures
***; and USAGE privilege for languages and data types (including domains)."

David J.

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> grant usage on schema s to z;
>> revoke execute on function s.f() from z; -- Yes, really!
>> 
>> *This surprises me*
>> 
>> The PG doc on, in the "5.7. Privileges" section at 
>> https://www.postgresql.org/docs/current/ddl-priv.html (under "USAGE" 
>> following "The available privileges are"), says this:
>> 
>> «
>> For schemas, allows access to objects contained in the schema (assuming that 
>> the objects' own privilege requirements are also met). Essentially this 
>> allows the grantee to “look up” objects within the schema...
>> »
>> 
>> Is the design of my test-case faulty? Have I found a bug? Or is the doc 
>> wrong?
> 
> WADaD
> 
> https://www.postgresql.org/docs/current/ddl-priv.html
> 
> "PostgreSQL grants privileges on some types of objects to PUBLIC by default 
> when the objects are created. No privileges are granted to PUBLIC by default 
> on tables, table columns, sequences, foreign data wrappers, foreign servers, 
> large objects, schemas, or tablespaces. For other types of objects, the 
> default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY 
> (create temporary tables) privileges for databases; EXECUTE privilege for 
> functions and procedures; and USAGE privilege for languages and data types 
> (including domains). The object owner can, of course, REVOKE both default and 
> expressly granted privileges. (For maximum security, issue the REVOKE in the 
> same transaction that creates the object; then there is no window in which 
> another user can use the object.) Also, these default privilege settings can 
> be overridden using the ALTER DEFAULT PRIVILEGES command."

Ah... so that's it. Thanks to David J, Tom Lane, and Jeremy Smith who all told 
me the same thing. And thanks especially for the super-fast responses. I'm 
pleased to learn that the design of my test-case was faulty. It's corrected now.

I confess that I'm surprised by the choice of the default behavior. It seems to 
be at odds with the principle of least privilege that insists that  you 
actively opt in to any relevant privilege.

I'll read up on ALTER DEFAULT PRIVILEGES and test it.

p.s., What's "WADaD", David. Internet search doesn't find me the translation 
except for, maybe, the Muslim name "Wadad" meaning "Love, friendship.

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Tom Lane]

Bryn Llewellyn  writes:
> I confess that I'm surprised by the choice of the default behavior. It seems 
> to be at odds with the principle of least privilege that insists that  you 
> actively opt in to any relevant privilege.

I'd be the first to agree that this behavior sacrifices security
principles for convenience.  However, it's not that big a deal
in practice, because functions that aren't SECURITY DEFINER can't
do anything that the caller couldn't do anyway.  You do need to
be careful about the default PUBLIC grant if you're making a
SECURITY DEFINER function, but that's a minority use-case.

(I wonder if it'd be practical or useful to emit a warning when
granting permissions on an object that already has a grant of
the same permissions to PUBLIC.  That would at least cue people
who don't understand about this behavior that they ought to look
more closely.)

regards, tom lane

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[David G. Johnston]

On Fri, Feb 11, 2022 at 2:44 PM Bryn Llewellyn  wrote:

>
> p.s., What's "WADaD", David. Internet search doesn't find me the
> translation except for, maybe, the Muslim name "Wadad" meaning "Love,
> friendship.
>
>
Working as designed, and documented.

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[David G. Johnston]

On Fri, Feb 11, 2022 at 3:05 PM Tom Lane  wrote:

> (I wonder if it'd be practical or useful to emit a warning when
> granting permissions on an object that already has a grant of
> the same permissions to PUBLIC.  That would at least cue people
> who don't understand about this behavior that they ought to look
> more closely.)
>

We did something similar a while ago where we now warn if you try to revoke
a privilege on a role that is actually inherited from PUBLIC and so the
revoke on the role doesn't actually do anything.  The inverse seems
reasonable, and consistent that, at first blush.

David J.

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> t...@sss.pgh.pa.us wrote:
>> 
>> (I wonder if it'd be practical or useful to emit a warning when granting 
>> permissions on an object that already has a grant of
>> the same permissions to PUBLIC.  That would at least cue people who don't 
>> understand about this behavior that they ought to look more closely.)
> 
> We did something similar a while ago where we now warn if you try to revoke a 
> privilege on a role that is actually inherited from PUBLIC and so the revoke 
> on the role doesn't actually do anything.  The inverse seems reasonable, and 
> consistent that, at first blush.

I'll wait with interest to see what might get implemented.

There's another common approach in this general space—when a fear arises that 
privileges have been granted too liberally so that vulnerabilities might have 
(presumably unintentionally) been exposed. It's to write various report 
generators—sometimes extended to become recommendation generators.

I looked at Chapter 52, "System Catalogs" at 
https://www.postgresql.org/docs/current/catalogs.html 
. It lists 97 relations. 
I'll have to defer reading about every one of these to another day. I searched 
the page for likely names looking for ones with "priv" and "rol". There's just 
a small number of hits. I drilled down on these. But none seemed to help 
finding out which objects, of which kinds, have which privileges (or roles) 
granted to which grantees.

Which catalog relations are sufficient to support a query that lists out, for 
example, every user-defined function and procedure with its (at least 
first-level) grantees?

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Adrian Klaver]

On 2/11/22 15:14, Bryn Llewellyn wrote:

/david.g.johns...@gmail.com  wrote:/



I looked at Chapter 52, "System Catalogs" at 
https://www.postgresql.org/docs/current/catalogs.html 
. It lists 97 
relations. I'll have to defer reading about every one of these to 
another day. I searched the page for likely names looking for ones with 
"priv" and "rol". There's just a small number of hits. I drilled down on 
these. But none seemed to help finding out which objects, of which 
kinds, have which privileges (or roles) granted to which grantees.


Which catalog relations are sufficient to support a query that lists 
out, for example, every user-defined function and procedure with its (at 
least first-level) grantees?


Tip if you do:

psql -d test -U postgres -h localhost -E

the -E will get you the queries for the \ meta-commands.

So:

\df+ tag_changeset_fnc

Yields:

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END as "Type",
 CASE
  WHEN p.provolatile = 'i' THEN 'immutable'
  WHEN p.provolatile = 's' THEN 'stable'
  WHEN p.provolatile = 'v' THEN 'volatile'
 END as "Volatility",
 CASE
  WHEN p.proparallel = 'r' THEN 'restricted'
  WHEN p.proparallel = 's' THEN 'safe'
  WHEN p.proparallel = 'u' THEN 'unsafe'
 END as "Parallel",
 pg_catalog.pg_get_userbyid(p.proowner) as "Owner",
 CASE WHEN prosecdef THEN 'definer' ELSE 'invoker' END AS "Security",
 pg_catalog.array_to_string(p.proacl, E'\n') AS "Access privileges",
 l.lanname as "Language",
 p.prosrc as "Source code",
 pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
FROM pg_catalog.pg_proc p
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE p.proname OPERATOR(pg_catalog.~) '^(tag_changeset_fnc)$' COLLATE 
pg_catalog.default

  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;

The parts you would be interested in are "Owner", "Security" and "Access 
privileges". You could modify the query to slim the results down some.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Adrian Klaver]

On 2/11/22 15:48, Adrian Klaver wrote:

On 2/11/22 15:14, Bryn Llewellyn wrote:

/david.g.johns...@gmail.com  wrote:/







Tip if you do:

psql -d test -U postgres -h localhost -E

the -E will get you the queries for the \ meta-commands.

So:

\df+ tag_changeset_fnc



Should have mentioned, if you want to include system functions then it 
would be:


\dfS+


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Can we go beyond the standard to make Postgres radically better?

[Andreas 'ads' Scherbaum]

On 10/02/2022 18:22, Peter J. Holzer wrote:

On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:

Postgres has since the outset gone beyond the SQL standard in many ways :
types, inheritance, programmability, generality are all well beyond what SQL
used to mandate and still well beyond the current standard.

There are huge developer benefits available to focusing more on making a great
relational programming environment, well outside the SQL standard.

Examples of small things Postgres could have:

   • SELECT * - b.a_id from a natural join b
   □ let me describe a select list by removing fields from a relation. In
 the example, I get all fields in the join of  a  and b other than the
 shared key, which I only get once.

Natural join already does this.

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.


Regards,

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Bryn Llewellyn]

>> b...@yugabyte.com wrote:
>> 
>> Which catalog relations are sufficient to support a query that lists out, 
>> for example, every user-defined function and procedure with its (at least 
>> first-level) grantees?
> 
> adrian.kla...@aklaver.com wrote:
> 
> Tip if you do:
> 
> psql -d test -U postgres -h localhost -E
> 
> the -E will get you the queries for the \ meta-commands. So:
> 
> \df+ my_function
> 
> Should have mentioned, if you want to include system functions then it would 
> be:
> 
> \dfS+

Thanks, Adrian. Ah, yes… I have used that technique before. The query that's 
shown is quite a mouthful. I could prune it down to what I need, of course. 
But, for now, looking at what the \df+ metacommand outputs will do. I made a 
new small test-case and copied it at the end.

The "Access privileges" column in the \df+ output for "s.f()", at its first 
use, is empty. I read this in the section "5.7 Privileges" that we've already 
mentioned:

«
If the “Access privileges” column is empty for a given object, it means the 
object has default privileges (that is, its privileges entry in the relevant 
system catalog is null). Default privileges always include all privileges for 
the owner, and can include some privileges for PUBLIC depending on the object 
type, as explained above. The first GRANT or REVOKE on an object will 
instantiate the default privileges (producing, for example, 
miriam=arwdDxt/miriam) and then modify them per the specified request.
»

The \df+ output at its second use shows this:

  =X/x
  x=X/x
  z=X/x

The \df+ output at its third use shows this:

  x=X/x
  z=X/x

And finally, the \df+ output at its fourth use shows this (again):

  =X/x
  x=X/x
  z=X/x

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?

I does seem, then, that with enough effort, what I've learned here would be 
enough to allow writing (say) a table function that reports owner, schema, 
name, and arg signature for every user defined function and procedure that has 
"execute" never revoked from, or re-granted to, "public".

I wonder if such a thing, if written and reviewed carefully, could find its way 
into a future PG release.



\c postgres postgres
set client_min_messages = warning;
drop database if exists db;
create database db owner postgres;

\c db postgres
set client_min_messages = warning;
drop schema if exists public cascade;
create schema s authorization postgres;

drop user if exists x;
create user x login password 'p';
drop user if exists y;
create user y login password 'p';
drop user if exists z;
create user z login password 'p';

create function s.f(i in int)
  returns int
  language plpgsql
as $body$
begin
  return i*2;
end;
$body$;

alter function s.f(int) owner to x;
\df+ s.f

grant execute on function s.f(int) to z;
\df+ s.f

revoke execute on function s.f(int) from public;
\df+ s.f

grant execute on function s.f(int) to public;
\df+ s.f

Microsoft Report Builder

[Kim Foltz]

  
  
I am trying to configure
  Microsoft Report Builder to run against a mainframe PostgreSQL
  database. The ODBC connection tests as good and manually
  entered SQL Select statements properly return data. The
  problem is the Query Designer in the software doesn't support
  the normal graphical display of available tables and fields
  with PostgreSQL. All I find in Microsoft's documentation is
  the statement some data sources don't support graphical tools
  in Report Builder.
More than likely there are
  better report generators than Microsoft's tool but our agency
  is very fond of Microsoft solutions. Anyone know of a way to
  get the graphical tools working in Report Builder? In the
  alternative, is there an alternative Windows report generator
  to try if Microsoft's software isn't repairable?

  

Re: Microsoft Report Builder

[Mladen Gogala]

On 2/11/22 22:22, Kim Foltz wrote:


I am trying to configure Microsoft Report Builder to run against a 
mainframe PostgreSQL database. The ODBC connection tests as good and 
manually entered SQL Select statements properly return data. The 
problem is the Query Designer in the software doesn't support the 
normal graphical display of available tables and fields with 
PostgreSQL. All I find in Microsoft's documentation is the statement 
some data sources don't support graphical tools in Report Builder.


More than likely there are better report generators than Microsoft's 
tool but our agency is very fond of Microsoft solutions. Anyone know 
of a way to get the graphical tools working in Report Builder? In the 
alternative, is there an alternative Windows report generator to try 
if Microsoft's software isn't repairable?



Well, there is a fish to achieve just that:

https://babelfishpg.org/

The problem is that you will have to build PostgreSQL from source with 
some specific hooks for Babelfish. On the other hand, this extension is 
supported by AWS:


https://aws.amazon.com/blogs/database/migrate-sql-server-to-amazon-aurora-postgresql-using-best-practices-and-lessons-learned-from-the-field/

That means that it will continue to be developed and that you will not 
waste all that effort.


Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Adrian Klaver]

On 2/11/22 17:24, Bryn Llewellyn 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:
https://www.postgresql.org/docs/current/ddl-priv.html

From:

Table 5.1. ACL Privilege Abbreviations


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Microsoft Report Builder

[Дмитрий Иванов]

I use Valentina Studio for some tasks.
https://www.valentina-db.com/ru/valentina-studio-overview
--
Regards, Dmitry!


сб, 12 февр. 2022 г. в 08:23, Kim Foltz :

> I am trying to configure Microsoft Report Builder to run against a
> mainframe PostgreSQL database. The ODBC connection tests as good and
> manually entered SQL Select statements properly return data. The problem is
> the Query Designer in the software doesn't support the normal graphical
> display of available tables and fields with PostgreSQL. All I find in
> Microsoft's documentation is the statement some data sources don't support
> graphical tools in Report Builder.
>
> More than likely there are better report generators than Microsoft's tool
> but our agency is very fond of Microsoft solutions. Anyone know of a way to
> get the graphical tools working in Report Builder? In the alternative, is
> there an alternative Windows report generator to try if Microsoft's
> software isn't repairable?
>

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

[Julien Rouhaud]

On Fri, Feb 11, 2022 at 09:07:16PM -0800, Adrian Klaver wrote:
> On 2/11/22 17:24, Bryn Llewellyn 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:
> https://www.postgresql.org/docs/current/ddl-priv.html
> 
> From:
> 
> Table 5.1. ACL Privilege Abbreviations

You might also be interested in aclexplode() function, see
https://www.postgresql.org/docs/current/functions-info.html.