write an analyze_function for own type

[黄宁]

I now have some custom data like:
[0x1 0x22 0x365]
It has a level attribute, that is, the level of 0x1 is 1, and the level of
0x22 is 2. How should I count the minimum level in a table? I want to use
this statistic in GIN index.

Unnecessary locks for partitioned tables

[n.kobzarev]

Hello!

 

Recently I`ve been pushing into life a new project and immediately
experienced an Out of shared memory error while querying partitioned tables.

 

Imagine a huge busy table that you want to split into hundreds of partitions
by list. Partition key is a kind of surrogate key that can be calculated
outside of database and can be provided as parameter.

 

create table t (a int) partition by list (a);

 

select format('create table %I partition of t for values in (%s)', 'p'||x,
x)

from generate_series(0,20) x

 

Query is executed inside stored procedure or as a prepared statement by,
let's say, Spring JPA.

 

prepare s (int) as select * from t where a = $1;

explain (analyze) execute s(0);

 

At the beginning database (12,13,14, or 15 versions) will try to create a
custom execution plan like this one: 

 

Query Text: prepare s (int) as select * from t where a = $1;

Seq Scan on p0 t  (cost=0.00..41.88 rows=13 width=4) (actual
time=0.011..0.011 rows=0 loops=1)

  Filter: (a = 0)

 

We have only one partition examined. But suddenly database decides to create
more generic query plan to deal with all range of partitions. 

 

begin;

set local plan_cache_mode = force_generic_plan;

 

Now all the queries looks like:

 

Query Text: prepare s (int) as select * from t where a = $1;

Append  (cost=0.00..880.74 rows=273 width=4) (actual time=0.014..0.015
rows=0 loops=1)

  Subplans Removed: 20

  ->  Seq Scan on p0 t_1  (cost=0.00..41.88 rows=13 width=4) (actual
time=0.013..0.013 rows=0 loops=1)

Filter: (a = $1)

  

Here the most important part is a count of removed plans. Database realizes
that all partitions can be skipped except interested one. But under load we
start receiving errors:

 

ERROR: out of shared memory

  Hint: You might need to increase max_locks_per_transaction.

  

Ok, let`s increase max_locks_per_transaction, but why this type of query
produces so much locks? Looks like DB issues locks for all the partitioned
objects involved in query and ONLY AFTER THAT it does partition pruning.

 

Here are locks :

 

select relation::regclass, mode

from   pg_locks 

where  pid = pg_backend_pid() and

   locktype = 'relation' and relation::regclass <> 'pg_locks'::regclass;

  

 

"p14" "AccessShareLock" 

"p20" "AccessShareLock"

"p17" "AccessShareLock"

"pg_type_oid_index"   "AccessShareLock"

"p19" "AccessShareLock"

... etc

 

But for real life there are also indexes, keys, up to 5000 locks in my case
for single select.

 

Setting plan_cache_mode = force_custom_plan resolves issue and also makes DB
more performant (*5 in my case, literally from 140 to 650 executions per
second on my development pc).

 

I`m calling to PG authors, is there any way to optimize prepared queries and
minimize unnecessary locks in generic query plan? For instance, lock parent
table, eliminate unnecessary partitions, lock remining objects. This may
help much in any place where partition pruning possible.

 

Meanwhile I`m using force_custom_plan - no more huge peaks, all queries are
at the lowest seen execution time. As a drawback there must be an overhead
to compile plans every time.

 

Thanks,

Nikolay

Q: documentation improvement re collation version mismatch

[Karsten Hilbert]

Dear all,

regarding changed collation versions this

https://www.postgresql.org/docs/devel/sql-altercollation.html

says:

The following query can be used to identify all
collations in the current database that need to be
refreshed and the objects that depend on them:

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS 
"Collation",
   pg_describe_object(classid, objid, objsubid) AS "Object"
  FROM pg_depend d JOIN pg_collation c
   ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
  WHERE c.collversion <> pg_collation_actual_version(c.oid)
  ORDER BY 1, 2;

I feel the result of that query can be slightly surprising
because it does not return (to my testing) any objects
depending on the database default collation, nor the database
itself (as per a collation version mismatch in pg_database).

Now, there is a line

For the database default collation, there is an analogous
command ALTER DATABASE ... REFRESH COLLATION VERSION.

right above that query but the query comment does not really
make it clear that the database default collation is _not_
identified to be in mismatch, if so. IOW, the database
default collation may still need to be refreshed even if the
query does not return any rows.

Perhaps this query (taken from the net)

SELECT  -- get collation-change endangered indices
indrelid::regclass::text,
indexrelid::regclass::text,
collname,
pg_get_indexdef(indexrelid)
FROM (
SELECT
indexrelid,
indrelid,
indcollation[i] coll
FROM
pg_index, generate_subscripts(indcollation, 1) 
g(i)
) s
JOIN pg_collation c ON coll=c.oid
WHERE
collprovider IN ('d', 'c')
AND
collname NOT IN ('C', 'POSIX');

could be added to the paragraph (or it could be folded into
the first query by a UNION or some such) ?

Or perhaps one could move the "ALTER DATABASE ... REFRESH
..." hint _below_ the query paragraph and add "Note: you may
need to refresh the default collation even if the query above
does not show any objects directly affected by a collation
version change" ?

Thanks for considering.

Best,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: Unnecessary locks for partitioned tables

[Laurenz Albe]

On Wed, 2022-11-09 at 14:11 +0300, n.kobza...@aeronavigator.ru wrote:
> Recently I`ve been pushing into life a new project and immediately 
> experienced an
> Out of shared memory error while querying partitioned tables.
>  
> ERROR: out of shared memory
>   Hint: You might need to increase max_locks_per_transaction.
>   
> Ok, let`s increase max_locks_per_transaction, but why this type of query 
> produces so much locks?
> Looks like DB issues locks for all the partitioned objects involved in query 
> and ONLY AFTER THAT
> it does partition pruning.

Yes, of course.  It needs an ACCESS SHARE lock when it looks at metadata
like the partition constraint, and locks are held until the end of the
transaction.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

RE: Unnecessary locks for partitioned tables

[n.kobzarev]

> > On Wed, 2022-11-09 at 14:11 +0300, n.kobza...@aeronavigator.ru wrote:
> > Recently I`ve been pushing into life a new project and immediately 
> > experienced an Out of shared memory error while querying partitioned tables.
> >  
> > ERROR: out of shared memory
> >   Hint: You might need to increase max_locks_per_transaction.
> >   
> > Ok, let`s increase max_locks_per_transaction, but why this type of query 
> > produces so much locks?
> > Looks like DB issues locks for all the partitioned objects involved in 
> > query and ONLY AFTER THAT it does partition pruning.

> Yes, of course.  It needs an ACCESS SHARE lock when it looks at metadata like 
> the partition constraint, and locks > are held until the end of the 
> transaction.

> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com

Thank you for reply!

But why it is not necessary in case of custom plan? 
Oh, I did not explicitly write that, in case of custom plan (first attempts or 
with force_custom_plan) database holds only a couple of locks! Why in this case 
it is sufficient to lock only one partition and parent table ?

>From my perspective, if exists the case of custom plan that produces locks for 
>only one partition and parent, we can make the same optimization in case of 
>generic plan too. Especially when database already identifies possibility of 
>partition pruning. 
Also, to query partitioned table metadata it is not required to lock all 
partitions, but parent only. Isn't it? 

Nikolay

Re: Unnecessary locks for partitioned tables

[Tom Lane]

 writes:
> Oh, I did not explicitly write that, in case of custom plan (first attempts 
> or with force_custom_plan) database holds only a couple of locks! Why in this 
> case it is sufficient to lock only one partition and parent table ?

Because partition routing is done at planning time in that case, based
on the actual values of the plan's parameters.  A generic plan
doesn't have the parameter values available, so it has to build
plan nodes for every partition that could conceivably be accessed.
So for queries of this kind (ie point queries against heavily partitioned
tables) the generic plan is pretty much always going to lose.  That
doesn't bother me enormously --- there are other query patterns
with similar behavior.

If you know that your queries always need custom plans, I question
the value of using PREPARE at all.

regards, tom lane

RE: Unnecessary locks for partitioned tables

[n.kobzarev]

>  writes:
> > Oh, I did not explicitly write that, in case of custom plan (first
attempts or with force_custom_plan) database 
> > holds only a couple of locks! 
> > Why in this > case it is sufficient to lock only one partition and
parent table ?

> Because partition routing is done at planning time in that case, based on
the actual values of the plan's  parameters.  
> A generic plan doesn't have the parameter values available, so it has to
build plan nodes for every partition that could conceivably be accessed.
> So for queries of this kind (ie point queries against heavily partitioned
> tables) the generic plan is pretty much always going to lose.  
> That doesn't bother me enormously --- there are other query patterns with
similar behavior.

> If you know that your queries always need custom plans, I question the
value of using PREPARE at all.
>
>   regards, tom lane


Thank you for your time, Tom.
PREPARE is not mandatory, it is mostly for reproducing purposes. Queries in
stored procedures behaves like prepared statements too, that is expected. 
If someone would create delayed locking for generic plans, after parameters
are known and partition pruning occurs, I believe generic plan will be on
pars with custom.
So, I`m sticking with plan cache parameter for feature development, that was
clear.


Thanks,
Nikolay

Re: copy file from a client app to remote postgres isntance

[Peter J. Holzer]

On 2022-11-07 19:57:04 +0300, Вадим Самохин wrote:
> I have an application that must copy a local file in csv format to a postgres
> table on a remote host. The closest solution is this one (https://
> stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy
> meta-command in a psql command:
> 
> psql -U %s -p %s -d %s -f - < 
> and executing it. But it's quite an unnatural way to write database
> code.

This looks like something you would use in another programming language
(maybe C or Python?) to construct a shell command.

Do you want do this once (from the shell) or from code?

If the former, starting psql and typing

\copy table_name from 'filename.csv' ...

Doesn't seem that unnatural to me.
(That just invokes COPY ... FROM STDIN on the server and feeds data to
it over the existing SQL connection.)

If it's the latter, your programming language's postgresql library
probably has a method for invoking copy.


> Has anything changed in the last ten years? Or, is there a
> better way to copy file contents in a remote database?

COPY is the fastest way to load data.

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: copy file from a client app to remote postgres isntance

[Peter J. Holzer]

On 2022-11-07 14:40:40 -0600, Ron wrote:
> On 11/7/22 10:57, Вадим Самохин wrote:
> I have an application that must copy a local file in csv format to a
> postgres table on a remote host. The closest solution is this one 
> (https://
> stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy
> meta-command in a psql command:
> 
> psql -U %s -p %s -d %s -f - < 
> 
> and executing it. But it's quite an unnatural way to write database code. 
> Has anything changed in the last ten years? Or, is there a better way to copy 
> file contents in a remote database?
> 
> 
> I'd write a small Python script, using the csv module to read the data and
> psycopg2 to load it.

If you use insert statements it will be significantly slower (which may
not matter for small files or one-off actions). If you use copy_from()
you don't have to parse it (but then why use Python at all?)

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: Unnecessary locks for partitioned tables

[David Rowley]

On Thu, 10 Nov 2022 at 04:11,  wrote:
> If someone would create delayed locking for generic plans, after parameters
> are known and partition pruning occurs, I believe generic plan will be on
> pars with custom.
> So, I`m sticking with plan cache parameter for feature development, that was
> clear.

The current problem is that the locks must be obtained on the objects
mentioned in the plan so that we can check if anying has been modified
that might invalidate the prepared plan. For example, index has been
dropped, partition dropped, etc.  The partition pruning in your
prepared plan is currently done during executor startup, which is
after the locks are obtained (which is why we must lock everything in
the plan).

There is a patch around at the moment that moves the run-time
partition pruning away from executor startup to before we obtain the
locks so that we can forego the locking of partitions which have been
pruned.  If that patch makes it then the problem will be solved, at
least starting with the version the patch makes it into.

David

Re: "set role" semantics

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> david.g.johns...@gmail.com wrote:
>> 
>>> b...@yugabyte.com wrote:
>>> 
>>> Notice that I didn't grant "connect" on either of the databases, "d1" or 
>>> "d2", to any of the roles, "clstr$mgr, "d1$mgr", or "d2$mgr".
>> 
>> You didn't have to since PUBLIC gets that privilege and you didn't revoke it.
>> 
>> https://www.postgresql.org/docs/current/ddl-priv.html
> 
> Revoking PUBLIC has been explained before to you (Bryn Llewellyn).
> 
> A quick search:
> 
> https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us
> 
> https://www.postgresql.org/message-id/cakfquwayij%3daqrqxjhfuj3qejq3e-pfibjj9cohx_l_46be...@mail.gmail.com
> 
> https://www.postgresql.org/message-id/cakfquwzvq-lergmtn0e3_7mqhjwtujuzf0gsnkg32mh_qf2...@mail.gmail.com

Here's an extract from the script that I copied in my first email:

create database d1;
revoke all on database d1 from public;

create database d2;
revoke all on database d2 from public;

Didn't I do exactly what you both said that I failed to do?

*Summary*

My experiments (especially below) show that "set role" has special semantics 
that differ from starting a session from cold:

"set role" allows a role that lacks "connect" on some database to end up so 
that the "current_database()" shows that forbidden database.

My question still stands: where can I read the account of this? I'm also 
interested to know _why_ it was decided not to test for the "connect" privilege 
when "set role" is used.

*Detail*

I suppose that the script that I first showed you conflated too many separable 
notions. (My aim was to you show what my overall aim was). Here's a drastically 
cut down version. It still demonstrates the behavior that I asked about.

create role joe
  nosuperuser
  nocreaterole
  nocreatedb
  noreplication
  nobypassrls
  connection limit -1
  login password 'p';

create database d1;
revoke all on database d1 from public;

\c d1 postgres

set role joe;
select current_database()||' > '||session_user||' > '||current_user;

I'm still able to end up with "Joe" as the "current_user" and "d1" (to which 
Joe cannot connect) as the "current_database()".

I then did the sanity test that I should have shown you at the outset. (Sorry 
that I didn't do that.) I started a session from cold, running "psql" on a 
client machine where the server machine is called "u" (for Ubuntu) in my 
"/etc/hosts", thus:

psql -h u -p 5432 -d d1 -U joe

The connect attempt was rejected with the error that I expected: "User does not 
have CONNECT privilege".

I wondered if the fact that the "session_user" was "postgres" in my tests was 
significant. So I did a new test. (As ever, I started with a freshly created 
cluster to be sure that no earlier tests had left a trace.)

create role mary
  nosuperuser
  noinherit
  nocreaterole
  nocreatedb
  noreplication
  nobypassrls
  connection limit -1
  login password 'p';

create role joe
  nosuperuser
  noinherit
  nocreaterole
  nocreatedb
  noreplication
  nobypassrls
  connection limit -1
  login password 'p';

create database d1;
revoke all on database d1 from public;
grant connect on database d1 to mary;
grant joe to mary;

Then I did this on the client machine:

psql -h u -p 5432 -d d1 -U mary
set role joe;

Here, too, I ended up with "Joe" as the "current_user" and "d1" (to which Joe 
cannot connect) as the "current_database()".

Re: copy file from a client app to remote postgres isntance

[Ron]

On 11/9/22 10:17, Peter J. Holzer wrote:

On 2022-11-07 14:40:40 -0600, Ron wrote:

On 11/7/22 10:57, Вадим Самохин wrote:
 I have an application that must copy a local file in csv format to a
 postgres table on a remote host. The closest solution is this one (https://
 stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy
 meta-command in a psql command:

 psql -U %s -p %s -d %s -f - < 


 and executing it. But it's quite an unnatural way to write database code. 
Has anything changed in the last ten years? Or, is there a better way to copy 
file contents in a remote database?


I'd write a small Python script, using the csv module to read the data and
psycopg2 to load it.

If you use insert statements it will be significantly slower (which may
not matter for small files or one-off actions). If you use copy_from()
you don't have to parse it (but then why use Python at all?)


If OP does not want to embed psql in his app, then he must find a different 
solution.  Python is such an option.



--
Angular momentum makes the world go 'round.

Re: "set role" semantics

[Guillaume Lelarge]

Hi,

Le mer. 9 nov. 2022, 19:55, Bryn Llewellyn  a écrit :

> adrian.kla...@aklaver.com wrote:
>
> david.g.johns...@gmail.com wrote:
>
> b...@yugabyte.com wrote:
>
> Notice that I didn't grant "connect" on either of the databases, "d1" or
> "d2", to any of the roles, "clstr$mgr, "d1$mgr", or "d2$mgr".
>
>
> You didn't have to since PUBLIC gets that privilege and you didn't revoke
> it.
>
> https://www.postgresql.org/docs/current/ddl-priv.html
>
>
> Revoking PUBLIC has been explained before to you (Bryn Llewellyn).
>
> A quick search:
>
> https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us
>
>
> https://www.postgresql.org/message-id/cakfquwayij%3daqrqxjhfuj3qejq3e-pfibjj9cohx_l_46be...@mail.gmail.com
>
>
> https://www.postgresql.org/message-id/cakfquwzvq-lergmtn0e3_7mqhjwtujuzf0gsnkg32mh_qf2...@mail.gmail.com
>
>
> Here's an extract from the script that I copied in my first email:
>
>
>
>
>
>
> *create database d1;revoke all on database d1 from public;create database
> d2;revoke all on database d2 from public;*
>
> Didn't I do exactly what you both said that I failed to do?
>

Nope. All you did was revoking all privileges on these database objects. It
didn't revoke privileges on objects of these databases. In other words, you
revoked CREATE, TEMP, VONNECT privileges on d1 and d2, you didn't revoke
privileges on the public schema.


> **Summary**
>
> My experiments (especially below) show that "set role" has special
> semantics that differ from starting a session from cold:
>
> *"set role" allows a role that lacks "connect" on some database to end up
> so that the "current_database()" shows that forbidden database.*
>
> My question still stands: where can I read the account of this? I'm also
> interested to know _why_ it was decided not to test for the "connect"
> privilege when "set role" is used.
>

Using SET ROLE doesn't connect you as another role on the database. You can
see this by logging connections, you won't see any connection log lines
when using SET ROLE. It also doesn't check pg_hba.conf rules when using SET
ROLE.

SET ROLE only makes you impersonate another role. The only privilege you
need to do that is being a member of this role.


> **Detail**
>
> I suppose that the script that I first showed you conflated too many
> separable notions. (My aim was to you show what my overall aim was). Here's
> a drastically cut down version. It still demonstrates the behavior that I
> asked about.
>
> *create role joe*
> *  nosuperuser*
> *  nocreaterole*
> *  nocreatedb*
> *  noreplication*
> *  nobypassrls*
> *  connection limit -1*
> *  login password 'p';*
>
> *create database d1;*
> *revoke all on database d1 from public;*
>
> *\c d1 postgres*
>
> *set role joe;*
> *select current_database()||' > '||session_user||' > '||current_user*
> *;*
> I'm still able to end up with "Joe" as the "current_user" and "d1" (to
> which Joe cannot connect) as the "current_database()".
>

Because SET ROLE doesn't connect you as this role name.


> I then did the sanity test that I should have shown you at the outset.
> (Sorry that I didn't do that.) I started a session from cold, running
> "psql" on a client machine where the server machine is called "u" (for
> Ubuntu) in my "/etc/hosts", thus:
>
>
> *psql -h u -p 5432 -d d1 -U joe*
> The connect attempt was rejected with the error that I expected: "User
> does not have CONNECT privilege".
>

Because joe tried to connect on d1, and he doesn't have the privileges to
do so.


> I wondered if the fact that the "session_user" was "postgres" in my tests
> was significant. So I did a new test. (As ever, I started with a freshly
> created cluster to be sure that no earlier tests had left a trace.)
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *create role
> mary  nosuperuser  noinherit  nocreaterole  nocreatedb  noreplication  
> nobypassrls  connection
> limit -1  login password 'p';create role
> joe  nosuperuser  noinherit  nocreaterole  nocreatedb  noreplication  
> nobypassrls  connection
> limit -1  login password 'p';create database d1;revoke all on database d1
> from public;grant connect on database d1 to mary;grant joe to mary;*
> Then I did this on the client machine:
>
> *psql -h u -p 5432 -d d1 -U mary*
>
> *set role joe;*
> Here, too, I ended up with "Joe" as the "current_user" and "d1" (to which
> Joe cannot connect) as the "current_database()".
>
>

Re: "set role" semantics

[Adrian Klaver]

On 11/9/22 10:55 AM, Bryn Llewellyn wrote:

adrian.kla...@aklaver.com  wrote:




Revoking PUBLIC has been explained before to you (Bryn Llewellyn).

A quick search:

https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us 



https://www.postgresql.org/message-id/cakfquwayij%3daqrqxjhfuj3qejq3e-pfibjj9cohx_l_46be...@mail.gmail.com

https://www.postgresql.org/message-id/cakfquwzvq-lergmtn0e3_7mqhjwtujuzf0gsnkg32mh_qf2...@mail.gmail.com


Here's an extract from the script that I copied in my first email:

*create database d1;
revoke all on database d1 from public;

create database d2;
revoke all on database d2 from public > *

Didn't I do exactly what you both said that I failed to do?


You need to go here:

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

To see what revoke all on database actually does:

CREATE

For databases, allows new schemas and publications to be created 
within the database, and allows trusted extensions to be installed 
within the database. ...


CONNECT

Allows the grantee to connect to the database. This privilege is 
checked at connection startup (in addition to checking any restrictions 
imposed by pg_hba.conf).


TEMPORARY

Allows temporary tables to be created while using the database.


Also look at Table 5.2. Summary of Access Privileges


None of the above stops a role from looking up information in the 
system catalogs which is what:


select current_database()||' > '||session_user||' > '||current_user;

is doing.


More comments below.


**Summary**

My experiments (especially below) show that "set role" has special 
semantics that differ from starting a session from cold:


*"set role" allows a role that lacks "connect" on some database to end 
up so that the "current_database()" shows that forbidden database.*nn


From below, you started the session with postgres(superuser) db user 
and it can set role to whatever it wants. The system catalog information 
is basically available to all and the functions(current_database, 
session_user, current_user) also are. Revoking connect on a database is 
that just denying the connection. Once a role has connected it can 
change the current_user to any role it is allowed to that does not count 
as a connection vs:


\c - joe
connection to server at "localhost" (::1), port 5432 failed: FATAL: 
permission denied for database "d1"

DETAIL:  User does not have CONNECT privilege.



My question still stands: where can I read the account of this? I'm also 
interested to know _why_ it was decided not to test for the "connect" 
privilege when "set role" is used.


**Detail**

I suppose that the script that I first showed you conflated too many 
separable notions. (My aim was to you show what my overall aim was). 
Here's a drastically cut down version. It still demonstrates the 
behavior that I asked about.


*create role joe*
*  nosuperuser*
*  nocreaterole*
*  nocreatedb*
*  noreplication*
*  nobypassrls*
*  connection limit -1*
*  login password 'p';*
*
*
*create database d1;*
*revoke all on database d1 from public;*
*
*
*\c d1 postgres*
*
*
*set role joe;*
*select current_database()||' > '||session_user||' > '||current_user**;
*
I'm still able to end up with "Joe" as the "current_user" and "d1" (to 
which Joe cannot connect) as the "current_database()".


I then did the sanity test that I should have shown you at the outset. 
(Sorry that I didn't do that.) I started a session from cold, running 
"psql" on a client machine where the server machine is called "u" (for 
Ubuntu) in my "/etc/hosts", thus:


*psql -h u -p 5432 -d d1 -U joe
*
The connect attempt was rejected with the error that I expected: "User 
does not have CONNECT privilege".


I wondered if the fact that the "session_user" was "postgres" in my 
tests was significant. So I did a new test. (As ever, I started with a 
freshly created cluster to be sure that no earlier tests had left a trace.)


*create role mary
   nosuperuser
   noinherit
   nocreaterole
   nocreatedb
   noreplication
   nobypassrls
   connection limit -1
   login password 'p';

create role joe
   nosuperuser
   noinherit
   nocreaterole
   nocreatedb
   noreplication
   nobypassrls
   connection limit -1
   login password 'p';

create database d1;
revoke all on database d1 from public;
grant connect on database d1 to mary;
grant joe to mary;
*
Then I did this on the client machine:

*psql -h u -p 5432 -d d1 -U mary*
*set role joe;
*
Here, too, I ended up with "Joe" as the "current_user" and "d1" (to 
which Joe cannot connect) as the "current_database()".





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

Re: "set role" semantics

[David G. Johnston]

On Wed, Nov 9, 2022 at 11:55 AM Bryn Llewellyn  wrote:

>
> Here's an extract from the script that I copied in my first email:
>
>
>
>
>
>
> *create database d1;revoke all on database d1 from public;create database
> d2;revoke all on database d2 from public;*
>
> Didn't I do exactly what you both said that I failed to do?
>

I'll admit that I didn't spend enough time thoroughly reading your email
and indeed missed some salient points.

*"set role" allows a role that lacks "connect" on some database to end up
> so that the "current_database()" shows that forbidden database.*
>

Just because you cannot connect to a database using a specific role doesn't
mean you cannot connect to said database using some other role and then
assume the role that doesn't have connect privileges.  SET ROLE does not
equate to connecting (in particular, role-level settings are not applied,
in addition to not performing the connection check).


>
> My question still stands: where can I read the account of this? I'm also
> interested to know _why_ it was decided not to test for the "connect"
> privilege when "set role" is used.
>

Why should "connect privilege" be tested in a situation where one is not
connecting?


>
> I suppose that the script that I first showed you conflated too many
> separable notions.
>

Yes, I would rationalize away my mistake as being a consequence of your
tendency to do this.



> (My aim was to you show what my overall aim was). Here's a drastically cut
> down version. It still demonstrates the behavior that I asked about.
>
> *create role joe*
> *  nosuperuser*
> *  nocreaterole*
> *  nocreatedb*
> *  noreplication*
> *  nobypassrls*
> *  connection limit -1*
> *  login password 'p';*
>
> *create database d1;*
> *revoke all on database d1 from public;*
>
> *\c d1 postgres*
>

You are connect as postgres which is superuser and can always connect
(pg_hba.conf permitting)


> *set role joe;*
>

You've assumed the role of joe but have not connected as them



> *select current_database()||' > '||session_user||' > '||current_user*
> *;*
> I'm still able to end up with "Joe" as the "current_user" and "d1" (to
> which Joe cannot connect) as the "current_database()".
>

Yes, that is the meaning of "current_user", the role you are presently
assuming.  session_user exists in order to keep track of the user you
actually connected with.


>
> I then did the sanity test that I should have shown you at the outset.
> (Sorry that I didn't do that.) I started a session from cold, running
> "psql" on a client machine where the server machine is called "u" (for
> Ubuntu) in my "/etc/hosts", thus:
>
>
> *psql -h u -p 5432 -d d1 -U joe*
> The connect attempt was rejected with the error that I expected: "User
> does not have CONNECT privilege".
>

Yep
We did not document that "set role" doesn't obey "connect" privilege
because, frankly, it didn't seem like one of the many things the system
does not do that warrants documenting.  I still agree with that position.

That said, I'm kinda seeing the design choice that is contrary to your
assumptions.  There are only three privileges on a database: create,
connect, and temporary - none of which speak to whether a given role is
allowed to be assumed while already connected to a specific database.
Roles are global, not per-database, and the system does not consider it an
issue for a role to be active in any database.  You can make such a role be
incapable of doing anything useful by revoking all default privileges its
mere presence produces no harm.  If you do remove connect, then the only
people who could assume that role would be members who themselves have
connect privilege.  It is seemingly pointless to prohibit them from
assuming any of the roles they are a member of on the basis of which
database they are in.  In short, yes, the permissions model could be made
more nuanced than its present design.  But it isn't, it isn't documented to
be, and your assuming that connect implies non-assumability doesn't seem to
stem from anything the documentation actually says.

David J.

Re: "set role" semantics

[Adrian Klaver]

On 11/9/22 10:55 AM, Bryn Llewellyn wrote:

adrian.kla...@aklaver.com  wrote:




Revoking PUBLIC has been explained before to you (Bryn Llewellyn).

A quick search:

https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us 



https://www.postgresql.org/message-id/cakfquwayij%3daqrqxjhfuj3qejq3e-pfibjj9cohx_l_46be...@mail.gmail.com

https://www.postgresql.org/message-id/cakfquwzvq-lergmtn0e3_7mqhjwtujuzf0gsnkg32mh_qf2...@mail.gmail.com


Here's an extract from the script that I copied in my first email:

*create database d1;
revoke all on database d1 from public;

create database d2;
revoke all on database d2 from public;
*


Should have added to previous post I missed this on initial read of 
original post.




Didn't I do exactly what you both said that I failed to do?




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

Re: "set role" semantics

[David G. Johnston]

On Tue, Nov 8, 2022 at 5:16 PM Bryn Llewellyn  wrote:

>
> Is there anything that can be done to limit the scope of the ability to
> end up in a database like I'd thought would be possible? (A little test
> showed me that "set role" doesn't fire an event trigger.)
>
> I do see that, as far as I've taken this test, "d2$mgr" is entirely
> impotent when the "current_database()" is "d1". Is this, maybe, just as
> good as it gets. I suppose I can live with what seems to me to be very odd
> as long as no harm can come of it.
>

Yes.  In short, you can prevent a person from connecting to specific
databases by ensuring the credentials they hold only resolve successfully
on those specific databases.  Both via connect privilege and pg_hba.conf
can this be done. But that person, while connected to any databases, can
assume any roles the user name of the credentials they used are a member
of.  Whether that is actually useful or not depends upon grants.  But in no
case can you prevent them from, say, examining the database catalog tables,
or executing at least some limited SQL.

David J.

Re: "set role" semantics

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Is there anything that can be done to limit the scope of the ability to end 
>> up in a database like I'd thought would be possible? (A little test showed 
>> me that "set role" doesn't fire an event trigger.)
>> 
>> I do see that, as far as I've taken this test, "d2$mgr" is entirely impotent 
>> when the "current_database()" is "d1". Is this, maybe, just as good as it 
>> gets. I suppose I can live with what seems to me to be very odd as long as 
>> no harm can come of it.
> 
> Yes. In short, you can prevent a person from connecting to specific databases 
> by ensuring the credentials they hold only resolve successfully on those 
> specific databases.  Both via connect privilege and pg_hba.conf can this be 
> done. But that person, while connected to any databases, can assume any roles 
> the user name of the credentials they used are a member of.  Whether that is 
> actually useful or not depends upon grants.  But in no case can you prevent 
> them from, say, examining the database catalog tables, or executing at least 
> some limited SQL.

Thanks. If nobody thinks that ending up as I showed is possible brings any kind 
of risk, then I’m happy to accept that. More generally, I’m a huge fan of the 
principle of least privilege, and (as far as it concerns what I asked about in 
this thread), its following aspect:

If you consider these two alternatives:

Alt. #1: Some ability that you do not need (and therefor don’t want) is simply 
not available.

Alt. #2: That unwanted ability is available, but reasoning shows that it’s 
harmless.

then Alt. #1 is preferable.

But I do see that I can’t get Alt #1 here.

In my actual use case, every role apart from "postgres", and its non-superuser 
deputy with "create role" and "createdb", will be confined at "connect time" to 
exactly one database. And those of these that will be able to "set role" will 
be able to do this only to other roles that are also confined (at "connect" 
time) to the same database. Moreover, I cannot worry about what sessions that 
authorize as "postgres" or its deputy can do because the former is unstoppable" 
and the latter is dangerously powerful in the wrong human hands. There's always 
a need for trusted humans who, if they betray that trust, can do untold harm. 
In this case, they must understand the design of the "multitenancy by 
convention" scheme and must be committed to honoring it. So, sure enough, 
reasoning tells me that my plan is sound.

Nevertheless, it does seem to be unfortunate to take the mechanics of "connect" 
as salient rather than the resulting state of the session that either "connect" 
or "set role" can bring about. There could be (in a future PG version) a 
privilege that governed "set role" in the present scenario. But I'm sure that 
there never will be.


You mentioned access to the catalog tables. This, too, belongs to the 
discussion of the principle of least privilege. This access is not hard wired. 
Rather, it's just a manifestation of the default regime. I've prototyped a 
regime where the privileges that you need to access these tables (and other 
things too) are revoked from public and (for convenience) are granted to a 
single dedicated role. This means that it's easy to make it such that the 
role(s) that clients use to connect can't query the catalog—or, if you prefer, 
can access exactly and only those catalog items that they need to. I'm pleased 
with how it worked out. And I'll pursue this regime further.

Re: "set role" semantics

[Adrian Klaver]

On 11/9/22 12:31, Bryn Llewellyn wrote:


Thanks. If nobody thinks that ending up as I showed is possible brings any kind 
of risk, then I’m happy to accept that. More generally, I’m a huge fan of the 
principle of least privilege, and (as far as it concerns what I asked about in 
this thread), its following aspect:

If you consider these two alternatives:

Alt. #1: Some ability that you do not need (and therefor don’t want) is simply 
not available.

Alt. #2: That unwanted ability is available, but reasoning shows that it’s 
harmless.

then Alt. #1 is preferable.

But I do see that I can’t get Alt #1 here.

In my actual use case, every role apart from "postgres", and its non-superuser deputy with "create role" and "createdb", will be 
confined at "connect time" to exactly one database. And those of these that will be able to "set role" will be able to do this only to other 
roles that are also confined (at "connect" time) to the same database. Moreover, I cannot worry about what sessions that authorize as "postgres" 
or its deputy can do because the former is unstoppable" and the latter is dangerously powerful in the wrong human hands. There's always a need for trusted 
humans who, if they betray that trust, can do untold harm. In this case, they must understand the design of the "multitenancy by convention" scheme and 
must be committed to honoring it. So, sure enough, reasoning tells me that my plan is sound.

Nevertheless, it does seem to be unfortunate to take the mechanics of "connect" as salient rather than the 
resulting state of the session that either "connect" or "set role" can bring about. There could be 
(in a future PG version) a privilege that governed "set role" in the present scenario. But I'm sure that 
there never will be.


Connecting to database and the role that is in play inside a session are 
two different things. Making them the same would make things like from here:


https://www.postgresql.org/docs/current/sql-createfunction.html

[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER indicates that the function is to be executed with 
the privileges of the user that calls it. That is the default. SECURITY 
DEFINER specifies that the function is to be executed with the 
privileges of the user that owns it.


The key word EXTERNAL is allowed for SQL conformance, but it is 
optional since, unlike in SQL, this feature applies to all functions not 
only external ones.


go sideways.






You mentioned access to the catalog tables. This, too, belongs to the 
discussion of the principle of least privilege. This access is not hard wired. 
Rather, it's just a manifestation of the default regime. I've prototyped a 
regime where the privileges that you need to access these tables (and other 
things too) are revoked from public and (for convenience) are granted to a 
single dedicated role. This means that it's easy to make it such that the 
role(s) that clients use to connect can't query the catalog—or, if you prefer, 
can access exactly and only those catalog items that they need to. I'm pleased 
with how it worked out. And I'll pursue this regime further.



Have you actually done that and tried to run SQL statements?

They are called system catalogs because they are used by the system to 
get the information necessary to do things. Throwing restrictions on 
their access would be akin to pouring sand in a gearbox, lots of strange 
behavior and then nothing.



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

Re: copy file from a client app to remote postgres isntance

[Peter J. Holzer]

On 2022-11-09 12:57:23 -0600, Ron wrote:
> On 11/9/22 10:17, Peter J. Holzer wrote:
> > On 2022-11-07 14:40:40 -0600, Ron wrote:
> > > On 11/7/22 10:57, Вадим Самохин wrote:
> > >  I have an application that must copy a local file in csv format to a
> > >  postgres table on a remote host. The closest solution is this one 
> > > (https://
> > >  stackoverflow.com/a/9327519/618020). It boils down to specifying a 
> > > \copy
> > >  meta-command in a psql command:
> > > 
> > >  psql -U %s -p %s -d %s -f - < > > \nEOT\n
> > > 
> > >  and executing it. But it's quite an unnatural way to write
> > >  database code. Has anything changed in the last ten years?
> > >  Or, is there a better way to copy file contents in a remote
> > >  database?
> > > 
> > > 
> > > I'd write a small Python script, using the csv module to read the data and
> > > psycopg2 to load it.
> > If you use insert statements it will be significantly slower (which may
> > not matter for small files or one-off actions). If you use copy_from()
> > you don't have to parse it (but then why use Python at all?)
> 
> If OP does not want to embed psql in his app, then he must find a different
> solution.  Python is such an option.

Invoking a program written in Python is just as hard (or simple) as
invoking a program written in C (psql). But that Python
program is additional code in their project which has to be first
written and then maintained.

What they probably should do is write the code in the programming
language they are already using for their app. And as I wrote just using
copy (from within their app, not from psql or a python script or
whatever) is probably the simplest solution. But since the OP chose not
to tell us what programming language or library they use, it's hard to
be more specific.

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: "set role" semantics

[Bryn Llewellyn]

adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
> 
> Connecting to database and the role that is in play inside a session are two 
> different things. Making them the same would make things [security define vs 
> "security invoker"] go sideways.

I said nothing to suggest that the role with which you connect to a database 
should be identical, always, to what "current_role" returns. I speculated only 
that an enhanced privilege scheme that limited the target of "set role" to 
those that have "connect" on the current database might be nice. I can't see 
that this would interfere with the "security" notion for a subprogram. After 
all, it's already possible for role "r1" to invoke a "security definer" 
subprogram owned by role "r2" when "r1" cannot "set role" to "r2". (This is 
probably the overwhelmingly common case.)

I believe that I do understand the business of these two "security" kinds for 
user-defined functions and procedures well. (And, yes, I know that a "set role" 
attempt in a "security definer" context causes a run-time error.) But thanks 
for mentioning the topic. There's a certain family resemblance between a 
"security definer" subprogram and "set role" in that each brings the outcome 
that the value that "current_role" returns might differ from the value that 
"session_user" returns. And you can certainly arrange it so that a "security 
definer" subprogram is owned by a role that does not have "connect" on the 
database where the subprogram exists. There is, though, a difference between 
the two paradigms in that the subprogram follows a stacked behavior so that 
when the subprogram that's first called exits, the "current_role" value is back 
where it was when the call was made. In contrast "set role" makes a durable 
change that you can see at the "psql" prompt (mentioning this as an example of 
any client). And you can use "set role" to roam around, on demand, among any 
number of roles in the set that allows you do do this in any order. This feels 
different—at least to me.

Anyway, all this is moot (except in that thinking about it helps me to enrich 
my mental model) because the privilege notions here will never change.

>> 
>> You mentioned access to the catalog tables. This, too, belongs to the 
>> discussion of the principle of least privilege. This access is not hard 
>> wired. Rather, it's just a manifestation of the default regime. I've 
>> prototyped a regime where the privileges that you need to access these 
>> tables (and other things too) are revoked from public and (for convenience) 
>> are granted to a single dedicated role. This means that it's easy to make it 
>> such that the role(s) that clients use to connect can't query the 
>> catalog—or, if you prefer, can access exactly and only those catalog items 
>> that they need to. I'm pleased with how it worked out. And I'll pursue this 
>> regime further.
>> 
> 
> Have you actually done that and tried to run SQL statements? They are called 
> system catalogs because they are used by the system to get the information 
> necessary to do things. Throwing restrictions on their access would be akin 
> to pouring sand in a gearbox, lots of strange behavior and then nothing.

Yes I have actually done this. But rigorous testing remains to be done. I've 
implemented the scheme only within a disciplined bigger picture. I've mentioned 
the thinking that I'll sketch now, before, in other contexts. It's not 
original. Many real-world applications follow it. I like to refer to it as the 
"hard shell" paradigm. Here, the ownership of the various artifacts that 
implement an application's database backend is spread among as many roles as 
you please. For example, tables and their associated artifacts (like indexes, 
sequences, and so on) would have a different owner from the user-defined 
subprograms that implement the business functions that access the tables. 
Significantly, client-side access to this whole shooting match would be via one 
(or a few) dedicated "client" roles. Such a role has only "connect" on the 
database that houses the application's backend. And it owns no schema and no 
objects in other schemas. Rather, it's just the target for the "execute" 
privilege of those few of all the user-defined subprograms that jointly define 
the database's API. The point (conforming to the principle of least privilege) 
is that sessions that connect as "client" must not be allowed to do arbitrary 
SQL. Rather, they should be able to do only what has been explicitly 
"white-listed" in by the encapsulation provided by the API-defining subprograms.

The "lazy" approach for the roles that own the application's implementation and 
that rely on (some of) the artifacts that live in "pg_catalog" is simply to 
revoke "all" from "public" for every one of these catalog items and, in the 
same breath, to grant "all" (or what is needed) on each to a dedicated role 
(say, "d0$developer" in database "d0"). Then "d0"developer" is grante

Re: Q: pg_hba.conf separate database names file format

[Michael Paquier]

On Wed, Nov 09, 2022 at 04:02:43AM -0600, Ron wrote:
> Are these "include" files supposed to solve the problem of having a *lot* of
> databases (or users) that you want to allow access to?

Yes, splitting the list of users and database eases the maintenance
and readability of pg_hba.conf as each HBA entry could get quite
long depending on the connection policy you may want.  My take would
be to use one entry per line in an @ file in this case.
--
Michael


signature.asc
Description: PGP signature

List user databases

[Igor Korot]

Hi, ALL,
According to 
https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/,
there are generally 3 system DBs.

However I'm confused with the word general.
How many system databases can be made on a PG server?

Thank you.

Re: List user databases

[Ian Lawrence Barwick]

2022年11月10日(木) 13:41 Igor Korot :
>
> Hi, ALL,
> According to 
> https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/,
> there are generally 3 system DBs.
>
> However I'm confused with the word general.
> How many system databases can be made on a PG server?

template0 and template1 are the mandatory system databases which
cannot be dropped.

"postgres" is created by default for operational convenience, but is
not essential and can
be removed if you really want to.

Regards

Ian Barwick

Re: List user databases

[Adrian Klaver]

On 11/9/22 20:57, Ian Lawrence Barwick wrote:

2022年11月10日(木) 13:41 Igor Korot :


Hi, ALL,
According to 
https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/,
there are generally 3 system DBs.

However I'm confused with the word general.
How many system databases can be made on a PG server?


template0 and template1 are the mandatory system databases which
cannot be dropped.


Actually that is not strictly true:

https://www.postgresql.org/docs/current/manage-ag-templatedbs.html

"
Note

template1 and template0 do not have any special status beyond the fact 
that the name template1 is the default source database name for CREATE 
DATABASE. For example, one could drop template1 and recreate it from 
template0 without any ill effects. This course of action might be 
advisable if one has carelessly added a bunch of junk in template1. (To 
delete template1, it must have pg_database.datistemplate = false.)


The postgres database is also created when a database cluster is 
initialized. This database is meant as a default database for users and 
applications to connect to. It is simply a copy of template1 and can be 
dropped and recreated if necessary.

"



"postgres" is created by default for operational convenience, but is
not essential and can
be removed if you really want to.

Regards

Ian Barwick




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

Re: "set role" semantics

[Adrian Klaver]

On 11/9/22 15:23, Bryn Llewellyn wrote:

adrian.kla...@aklaver.com wrote:



b...@yugabyte.com wrote:


Connecting to database and the role that is in play inside a session are two different 
things. Making them the same would make things [security define vs "security 
invoker"] go sideways.


I said nothing to suggest that the role with which you connect to a database should be identical, always, to what "current_role" returns. I speculated only that an 
enhanced privilege scheme that limited the target of "set role" to those that have "connect" on the current database might be nice. I can't see that this would 
interfere with the "security" notion for a subprogram. After all, it's already possible for role "r1" to invoke a "security definer" subprogram owned 
by role "r2" when "r1" cannot "set role" to "r2". (This is probably the overwhelmingly common case.)

I believe that I do understand the business of these two "security" kinds for user-defined functions and procedures well. (And, yes, I know that a "set role" attempt in a "security 
definer" context causes a run-time error.) But thanks for mentioning the topic. There's a certain family resemblance between a "security definer" subprogram and "set role" in that each 
brings the outcome that the value that "current_role" returns might differ from the value that "session_user" returns. And you can certainly arrange it so that a "security 
definer" subprogram is owned by a role that does not have "connect" on the database where the subprogram exists. There is, though, a difference between the two paradigms in that the subprogram 
follows a stacked behavior so that when the subprogram that's first called exits, the "current_role" value is back where it was when the call was made. In contrast "set role" makes a 
durable change that you can see at the "psql" prompt (mentioning this as an example of any client). And you can use "set role" to roam around, on demand, among any number of roles in the 
set that allows you do do this in any order. This feels different—at least to me.

Anyway, all this is moot (except in that thinking about it helps me to enrich 
my mental model) because the privilege notions here will never change.


So, I want it but not really.





You mentioned access to the catalog tables. This, too, belongs to the 
discussion of the principle of least privilege. This access is not hard wired. 
Rather, it's just a manifestation of the default regime. I've prototyped a 
regime where the privileges that you need to access these tables (and other 
things too) are revoked from public and (for convenience) are granted to a 
single dedicated role. This means that it's easy to make it such that the 
role(s) that clients use to connect can't query the catalog—or, if you prefer, 
can access exactly and only those catalog items that they need to. I'm pleased 
with how it worked out. And I'll pursue this regime further.



Have you actually done that and tried to run SQL statements? They are called 
system catalogs because they are used by the system to get the information 
necessary to do things. Throwing restrictions on their access would be akin to 
pouring sand in a gearbox, lots of strange behavior and then nothing.


Yes I have actually done this. But rigorous testing remains to be done. I've implemented the scheme only within a disciplined bigger 
picture. I've mentioned the thinking that I'll sketch now, before, in other contexts. It's not original. Many real-world applications 
follow it. I like to refer to it as the "hard shell" paradigm. Here, the ownership of the various artifacts that implement an 
application's database backend is spread among as many roles as you please. For example, tables and their associated artifacts (like 
indexes, sequences, and so on) would have a different owner from the user-defined subprograms that implement the business functions that 
access the tables. Significantly, client-side access to this whole shooting match would be via one (or a few) dedicated "client" 
roles. Such a role has only "connect" on the database that houses the application's backend. And it owns no schema and no objects 
in other schemas. Rather, it's just the target for the "execute" privilege of those few of all the user-defined subprograms that 
jointly define the database's API. The point (conforming to the principle of least privilege) is that sessions that connect as 
"client" must not be allowed to do arbitrary SQL. Rather, they should be able to do only what has been explicitly 
"white-listed" in by the encapsulation provided by the API-defining subprograms.


All right that I get.



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

Re: List user databases

[Julien Rouhaud]

Hi,

On Wed, Nov 09, 2022 at 09:16:40PM -0800, Adrian Klaver wrote:
> On 11/9/22 20:57, Ian Lawrence Barwick wrote:
> >
> > template0 and template1 are the mandatory system databases which
> > cannot be dropped.
>
> Actually that is not strictly true:
>
> https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
>
> "
> Note
>
> template1 and template0 do not have any special status beyond the fact that
> the name template1 is the default source database name for CREATE DATABASE.
> For example, one could drop template1 and recreate it from template0 without
> any ill effects. This course of action might be advisable if one has
> carelessly added a bunch of junk in template1. (To delete template1, it must
> have pg_database.datistemplate = false.)
>
> The postgres database is also created when a database cluster is
> initialized. This database is meant as a default database for users and
> applications to connect to. It is simply a copy of template1 and can be
> dropped and recreated if necessary.
> "

And one important thing about template0 is that postgres will assume that it
only contains identifiers with plain ASCII characters, so that the on-disk data
is compatible with any encoding (at least any supported server encoding, stuff
like EBCDIC clearly wouldn't play well with that assumption), and can therefore
be used to create a new database with a different encoding from template1 (or
another template database).  Breaking that assumption is usually a very bad
idea.

Re: "set role" semantics

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Anyway, all this is moot (except in that thinking about it helps me to 
>> enrich my mental model) because the privilege notions here will never change.
> 
> So, I want it but not really.

I’d rather say “I’d very much prefer it if I had it. But, because I don’t, I 
will have to write a comment essay to explain what tests might show and why 
these outcomes that might seem worrisome at first sight can be seen, after an 
exercise of reasoning, to be harmless. I’m not a fan of that kind of essay 
writing. But I’ll do it if I have to.

>> Yes I have actually done this. But rigorous testing remains to be done... 
>> The point (conforming to the principle of least privilege) is that sessions 
>> that connect as "client" must not be allowed to do arbitrary SQL. Rather, 
>> they should be able to do only what has been explicitly "white-listed" in by 
>> the encapsulation provided by the API-defining subprograms.
> 
> All right that I get.

Good. I’m relieved that you haven’t (yet) spotted a flaw in my scheme.

Re: List user databases

[Ian Lawrence Barwick]

2022年11月10日(木) 14:16 Adrian Klaver :
>
> On 11/9/22 20:57, Ian Lawrence Barwick wrote:
> > 2022年11月10日(木) 13:41 Igor Korot :
> >>
> >> Hi, ALL,
> >> According to 
> >> https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/,
> >> there are generally 3 system DBs.
> >>
> >> However I'm confused with the word general.
> >> How many system databases can be made on a PG server?
> >
> > template0 and template1 are the mandatory system databases which
> > cannot be dropped.
>
> Actually that is not strictly true:
>
> https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
>
> "
> Note
>
> template1 and template0 do not have any special status beyond the fact
> that the name template1 is the default source database name for CREATE
> DATABASE. For example, one could drop template1 and recreate it from
> template0 without any ill effects. This course of action might be
> advisable if one has carelessly added a bunch of junk in template1. (To
> delete template1, it must have pg_database.datistemplate = false.)

OK, "cannot be dropped unless you manually convert them into non-template
databases" :).

Regards

Ian Barwick