generated column cast from timestamptz to timestamp not OK.

[alias]

CREATE TABLE test_g (
a timestamptz,
b timestamp GENERATED ALWAYS AS (a::timestamp) STORED
);
then an error occurred.

> ERROR:  42P17: generation expression is not immutable
> LOCATION:  cookDefault, heap.c:2768
>

However  the following 2 commands is ok.

CREATE TABLE test_i (
> a int,
> b bigint GENERATED ALWAYS AS (a::bigint) STORED
> );
>


> CREATE TABLE test_c (
> a varchar,
> b text GENERATED ALWAYS AS (a::text) STORED
> );
>

I didn't get it. timestamptz changes then timestamp also changes. timestamp
is part of timestamptz...
Even if column timestamptz is some value that is constantly changing (like
now() ), it changes/updates then just in the mean time captures timestamp
to column b.

Re: generated column cast from timestamptz to timestamp not OK.

[Francisco Olarte]

On Fri, 13 May 2022 at 12:47, alias  wrote:
> CREATE TABLE test_g (
> a timestamptz,
> b timestamp GENERATED ALWAYS AS (a::timestamp) STORED
> );
> then an error occurred.
>> ERROR:  42P17: generation expression is not immutable

Cast to timestamp uses current session time zone, current session time
zone is not inmutable.

Try forcing the time zone ( a at timezone $whatever ) ( or use a view
if you need it ).


> However  the following 2 commands is ok.
...
>> b bigint GENERATED ALWAYS AS (a::bigint) STORED
...
>> b text GENERATED ALWAYS AS (a::text) STORED

Because conversion from int to bigint is inmutable, just extend sign
bit, and from varchar to text too, they are the same.

> I didn't get it. timestamptz changes then timestamp also changes. timestamp 
> is part of timestamptz...

I think you are falling in the common misconception that a timestamp
is something like "MMDDhhmmss" and a timestamptz is the same plus
a time zone. They are not ( look at the docs, they both have the same
size ).

A timestamptz dessignates a point in time, is like a real number,
which is printed ( and read by default ) in the timezone of the user
session. It DOES NOT STORE A TIME ZONE. Its external ( text )
representation varies with the session parameters. Use set timezone
and play a bit to see it.

A timestamp is more or less the same, but is always printed/read as if
it where in the UTC timezone, and the time zone is not printed ( as it
is always the same ). But inside is also just a real number. It is
just a compact and convenient way of storing "MMDDhhmmss",
calculate the timepoint in that utc time and store it.

To convert between them in a inmutable way you need to specify how and
use the at tz operator.

Try it in a command line.

FOS

Re: Restricting user to see schema structure

[Neeraj M R]

Hi all,

Thanks for your suggestions, I would like to define my problem a little
more.

 I am using pgAdmin . I have a database 'db' and it has got 2 schemas
'schema1' and 'schema2', I have created some views in schema2  from tables
of schema1. I have created a new user and granted connection access to
database and granted usage on tables and views of schema2 only. But now the
problem is that the new user is able to see the table names of schema1 even
though the user cannot see the data present in them they can see the table
names.Is there any way I can completely hide schema1 from the new user.

Thanks & Regards
Neeraj

On Fri, May 13, 2022, 09:40 Bryn Llewellyn  wrote:

> *t...@sss.pgh.pa.us  wrote:*
>
> *b...@yugabyte.com  writes:*
>
> Maybe this entire discussion is moot when hackers can read the C code of
> PG's implementation…
>
>
> We have pretty much no interest in revisiting that design choice, even if
> doing so wouldn't likely break a couple decades' worth of client-side
> software development.
>
> Anyway, if you feel a need to prevent user A from seeing the
> catalog entries for user B's database objects, the only answer we have is
> to put A and B into separate databases.  If despite that you want A and B
> to be able to share some data, you can probably build the connections you
> need using foreign tables or logical replication; but there's not a lot of
> pre-fab infrastructure for that, AFAIK.
>
>
> Thanks Tom. It certainly helps to know that nothing in how PG works in the
> space that's relevant here is going to change in my lifetime. (I just wrote
> exactly the same in reply to David Johnston.)
>
> My sense is that the database is intended to be a hermetic unit of
> encapsulation and provides some of the features that multi-tenancy
> requires. But there's the caveat that users are defined, and operate,
> cluster-wide.
>
> If a cluster has two databases, "app_1" and "app_2", each populated using
> the general scheme that I sketched, then users "client_1" and "client_2"
> (designed, respectively to let them operate as intended in their
> corresponding databases) could always connect each to the other's database.
> They couldn't do much in the "wrong" database. But they could certainly
> list out all the application's objects and the source code of all the
> application's user-defined subprograms.
>
> In general, it's best to use any system in the way that it was designed to
> be used. And PG was designed to allow all users to see the metadata account
> of all of the content of every database in the cluster—but not to use any
> of the content unless this has been specifically catered for.
>

Re: Restricting user to see schema structure

[Neeraj M R]

Hi Bryn,

What I meant by 'created a new user' is that I have used the following
commands.

CREATE USER  WITH ENCRYPTED PASSWORD '';
GRANT CONNECT ON DATABASE  TO ;
GRANT USAGE ON SCHEMA  TO ;

Thanks & Regards
Neeraj

On Fri, May 13, 2022, 10:43 Bryn Llewellyn  wrote:

> *neerajmr12...@gmail.com  wrote:*
>
> I am using pgAdmin . I have a database 'db' and it has got 2 schemas
> 'schema1' and 'schema2', I have created some views in schema2  from tables
> of schema1. I have created a new user and granted connection access to
> database and granted usage on tables and views of schema2 only. But now the
> problem is that the new user is able to see the table names of schema1
> even though the user cannot see the data present in them they can see the
> table names. Is there any way I can completely hide schema1 from the new
> user.
>
>
> What exactly do you mean by "have created a new user and granted
> connection access to database"? As I understand it, there's no such thing.
> I mentioned a simple test in my earlier email that showed that any user
> (with no schema of its own and no granted privileges) can connect to any
> database—and see the full metadata account of all its content. I'm teaching
> myself to live with this.
>

Re: Restricting user to see schema structure

[David G. Johnston]

On Thu, May 12, 2022 at 11:37 PM Bryn Llewellyn  wrote:

> *neerajmr12...@gmail.com  wrote:*
>
> *b...@yugabyte.com  wrote:*
>
> What exactly do you mean by "have created a new user and granted
> connection access to database"? As I understand it, there's no such thing.
> I mentioned a simple test in my earlier email that showed that any user
> (with no schema of its own and no granted privileges) can connect to any
> database—and see the full metadata account of all its content. I'm teaching
> myself to live with this.
>
>
> What I meant by 'created a new user' is that I have used the following
> commands.
>
> CREATE USER  WITH ENCRYPTED PASSWORD '';
> GRANT CONNECT ON DATABASE  TO ;
> GRANT USAGE ON SCHEMA  TO ;
>
> In a freshly initialized cluster the newly created user will have already
inherited the necessary connect privilege making this one redundant (though
that property can be considered useful here).

Ah… there's obviously something I don't understand here. I've never used
> "grant connect on database"—and not experience an ensuing problem. I just
> tried this:
>
> \c postgres postgres
> create user joe login password 'joe';
> revoke connect on database postgres from joe;
> \c postgres joe
>
> It all ran without error. (I've turned off the password challenge in my
> MacBook PG cluster.) I don't have a mental model that accommodates this.
> And a quick skim for this variant in the "GRANT" section of the PG doc
> didn't (immediately) help me. I obviously need to do more study. I'll shut
> up until I have.
>

It's because joe hasn't been granted connect on the database directly.  It
is through their mandatory membership in the PUBLIC pseudo-role, and that
role's default grant of connect on all newly created databases, that joe
receives permission to connect.  You can only revoke what has been
explicitly granted so one must revoke the grant from PUBLIC - then
re-assign it to the subset of roles that require it.

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

David J.

Re: Restricting user to see schema structure

[Adrian Klaver]

On 5/12/22 22:03, Neeraj M R wrote:

Hi all,

Thanks for your suggestions, I would like to define my problem a little 
more.


  I am using pgAdmin . I have a database 'db' and it has got 2 schemas 
'schema1' and 'schema2', I have created some views in schema2  from 
tables of schema1. I have created a new user and granted connection 
access to database and granted usage on tables and views of schema2 
only. But now the problem is that the new user is able to see the table 
names of schema1 even though the user cannot see the data present in 
them they can see the table names.Is there any way I can completely hide 
schema1 from the new user.


AFAIK, you can't change that display in pgAdmin4. It would not help in 
any case as long as the Query Tool is available as a user can get the 
information the same way pgAdmin4 does, by querying the system catalogs.




Thanks & Regards
Neeraj




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

Re: Restricting user to see schema structure

[Adrian Klaver]

On 5/12/22 22:13, Bryn Llewellyn wrote:

/neerajmr12...@gmail.com  wrote:/



What exactly do you mean by "have created a new user and granted 
connection access to database"? As I understand it, there's no such 
thing. I mentioned a simple test in my earlier email that showed that 
any user (with no schema of its own and no granted privileges) can 
connect to any database—and see the full metadata account of all its 
content. I'm teaching myself to live with this.


Besides the REVOKE CONNECT, it is also possible to prevent connections 
to a given database by a particular user by using settings in pg_hba.conf.



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

Logon via GSSAPI from Linux fails, but works from Windows

[Niels Jespersen]

Hello all

We have our analysis users log on to Postgres without a password. Instead we 
rely on their Windows identity. 

When the Postgres server is on Windows, we use SSPI. Works great. 

When the Postgres server is on Linux, we use GSSAPI. Works great.

Now, we are introducing Linux for analysts to run their Python/R/SQL/whatever. 

They log onto Linux using their Windows identity. The Linux analysis servers 
are joined to the Windows domain using sssd. 

This gives us a headache. Logging onto Postgres without a password does not 
immediately work when the Windows user is logged onto a Linux server with their 
Windows credentials. 

Linux is Ubuntu 22.04 on the client side, Ubuntu 20.04 on the Postgres server 
side. Postgres server version is server 14.2 (Ubuntu 14.2-1.pgdg20.04+1). psql 
client is psql (PostgreSQL) 14.2 (Ubuntu 14.2-1ubuntu1).

What happens is this

yyy@srvpython8:~$ psql service=bigdata_db1
psql: error: connection to server at "srvpostgres4.xxx.local" (172.30.33.30), 
port 1609 failed: could not initiate GSSAPI security context: Unspecified GSS 
failure.  Minor code may provide more information: Server not found in Kerberos 
database
connection to server at "srvpostgres4.xxx.local" (172.30.33.30), port 1609 
failed: GSSAPI continuation error: Unspecified GSS failure.  Minor code may 
provide more information: Server not found in Kerberos database
yyy@srvpython8:~$

The Postgres server log has this. 

2022-05-13 18:14:01.140 
CEST,,,474093,"172.30.32.213:33554",627e83c9.73bed,1,"",2022-05-13 18:14:01 
CEST,,0,LOG,0,"connection received: host=172.30.32.213 
port=33554","","not initialized",,0
2022-05-13 18:14:01.159 
CEST,,,474094,"172.30.32.213:33556",627e83c9.73bee,1,"",2022-05-13 18:14:01 
CEST,,0,LOG,0,"connection received: host=172.30.32.213 
port=33556","","not initialized",,0
2022-05-13 18:14:01.176 
CEST,"yyy","db1",474094,"172.30.32.213:33556",627e83c9.73bee,2,"authentication",2022-05-13
 18:14:01 CEST,2/14544,0,FATAL,28000,"GSSAPI authentication failed for user 
""yyy""","Connection matched pg_hba.conf line 15: ""hostall all 
172.0.0.0/8 gss map=xxxlocal include_realm=0 
krb_realm=""XXX.LOCAL""","client backend",,-3382135431624836920

Are we forgetting to set something up? 

Regards Niels Jespersen