Re: can't get psql authentication against Active Directory working

[Tomas Pospisek]

Hi Stephen,

again thanks a lot for trying to help me!

On 21.02.23 16:29, Stephen Frost wrote:


* Tomas Pospisek (t...@sourcepole.ch) wrote:

On 20.02.23 15:17, Stephen Frost wrote:

* Tomas Pospisek (t...@sourcepole.ch) wrote:

so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via
Active Directory.


Looks like you're trying to do a bit more than that as you're using
GSSAPI (not SSPI, which would be the more typical method on Windows..)
and you're asking for an encrypted connection.  Is there a reason you're
going down this particular route..?


The reason I'm using the GSSAPI is it's the only documented way to do
authentication from psql.exe against Active Directory that was able to find.


I mean ... https://www.postgresql.org/docs/current/sspi-auth.html


Were should the settings that are discussed in that document be 
configured? In pg_hba.conf?


That document says:

"SSPI authentication only works when both server and client are running 
Windows, or, on non-Windows platforms, when GSSAPI is available."


I interpret that phrase like this:

* there's a case where both server and client are running Windows 
(doesn't apply to me)
* there's a case where both are running non-Windows (doesn't apply to me 
either - server is Linux, client is Windows)


So that's an extremely terse docu that is not clear at all to me.

I'd suggest to change that phrase into:

"SSPI authentication works when both server and client are running 
Windows. When server or client are on non-Windows platforms then those 
need to use GSSAPI."


- assuming that my interpretation of that super terse docu is correct of 
course.



Even now I am unable to find *any* documentation on how to go about doing
auth from psql.exe to AD with SSPI. Would you have any pointers to
documentation or a howto (or a blog post or a stackoverflow answer or...).


Sure, here's a blog post that I wrote about doing exactly that:

https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication


Note that that document doesn't mention SSPI a single time.

The ultra terse Postgres docu is using terms such as "SAM", "negotiate 
mode", "UPN", without a reference to their definition, which leaves to 
Windows noobs like me (and equaly Windows non-noobs) without a clue. 
Searching "kerberos SAM" or "SSPI negotiate mode" on duckduckgo doesn't 
seem to lead to useful hits. So the documentation seems to be geared to 
experts of both Kerberos and Active Directory and Windows technology 
alike which as far as I can tell is a very rare specimen.



The reason I am apparently asking for encrypted communications is that I
apparently misinterpreted what `gssencmode`: I thought that it would force
the use of GSS but apparently it's forcing a connection encryption?


gssencmode indeed requires a GSS encrypted connection.  Unfortunately,
there isn't a way to force GSS or SSPI be used for authentication today
(outside of GSS with encryption) from the client side, though that's
something that's been discussed and I think most people feel would be
good to have.  That said, if the server is configured in pg_hba for gss
(or sspi), then the client will try to authenticate that way.


As far as I can see it is *not possible* to configure the server for 
SSPI ("or sspi")? It's only possible to configure it for GSS *and* SSPI 
at the same time (?) assuming the server is running on a non-Windows 
platform?



How do I force psql.exe to use GSS and/or SSPI then (instead of
username/password authentication that it seems to be doing by default).


The server's hba needs to be configured for gss (if the server is Linux)
or sspi (if the server is on Windows) and then the client will be
informed that's what the server wants when it connects and it'll try to
authenticate that way.  It's not possible currently to force the client
to only try GSS or SSPI, what it will try is mostly driven by the server
side saying what the server wants the client to authenticate with.


Maybe the reason I'm using GSS is because what I really want to achieve is
to authenticate from QGIS with AD. QGIS (from osgeo4w) is shipping a libpq
that is linked against the kerberos library, so that's what I have. I have
no idea how libpq needs to be compiled so as to be able to do SSPI?


PostgreSQL, when built on Windows (including libpq), is always built
with SSPI support.  That said, if it's also built with GSSAPI, then SSPI
is preferred but you can set 'gsslib' to 'gssapi' to prefer gssapi be
used instead.  Now, if you're setting gssencmode to prefer or require
and the library is built with GSSAPI then we're going to try GSSAPI
first to try to set up the encrypted connection.


Also, just in case I would find out how to get my hand (or build) a
psql/libpsql that *can* do SSPI - would I be able to swap out the existing
libpq for the libpq with SSPI support and dependend executable would still
be supposed to work?


Your libpq is almost certainly also built with 

Re: PostgreSQL optimizations for CoW FS

[Peter Eisentraut]

On 22.02.23 10:41, HECTOR INGERTO wrote:
Let’s say we have to run a PostgreSQL instance on top of a copy on write 
filesystem like ZFS or BTRFS. In adittion to set full_page_writes = off, 
what other optimizations can be done on the PostgreSQL side?


Look at the settings wal_init_zero and wal_recycle.

Re: Debugging postgres on Windows - could not open directory "/lib"

[Adrian Klaver]

On 2/23/23 17:16, Cathy Xie wrote:



On Thu, Feb 23, 2023 at 1:21 AM Adrian Klaver > wrote:


On 2/21/23 17:18, Cathy Xie wrote:
 >
 >
 > On Wed, Feb 22, 2023 at 6:54 AM Adrian Klaver
mailto:adrian.kla...@aklaver.com>

 >
 > Hi Adrian,
 >
 > Thanks for your email!
 >
 >     1) How did you build it?
 >
 >
 > I installed ActivateState Perl, Bison, and Flex. Then I ran the
command
 > ```build Debug``` under directory postgres/src/tools/msvc
 >
 >     2) Open pgsql.sln in a text editor and report the results here.
 >
 > Here was the result when I opened pgsql.sln in Visual Studio
2022, and
 > ran the postgres.exe.

1) That just repeats the error message from your previous post.

2) Please use copy and paste of text instead of screenshots. Those
of us
with old eyes would appreciate it.

3) Per previous request:

         Open pgsql.sln in a text editor and report the results here.

         It is the contents of pgsql.sln that I am looking for.

Hi Adrian,

Thanks for your time and patience!  Sorry for my misunderstanding.

The following is the contents of pgsql.sln.  I look forward to hearing 
back from you.


Well I guess I should be careful what I ask for:). That looks like a 
Registry dump and is incomprehensible to me. More to the point I do not 
see anywhere that it directly mentions the /lib referenced in the error 
message: 'FATAL: could not open directory "/lib": No such file or 
directory.'. I'm guessing that is buried somewhere in all the references 
in the file.


At this point I am running out of ideas due to my limited knowledge 
building software on Windows. The only thing I can think of now is that 
in a previous post you said:


I installed ActivateState Perl, Bison, and Flex. Then I ran the command
```build Debug``` under directory postgres/src/tools/msvc

From here:

https://www.postgresql.org/docs/current/install-windows-full.html

did you do?:


18.1.4. Cleaning and Installing

Most of the time, the automatic dependency tracking in Visual Studio 
will handle changed files. But if there have been large changes, you may 
need to clean the installation. To do this, simply run the clean.bat 
command, which will automatically clean out all generated files. You can 
also run it with the dist parameter, in which case it will behave like 
make distclean and remove the flex/bison output files as well.


By default, all files are written into a subdirectory of the debug or 
release directories. To install these files using the standard layout, 
and also generate the files required to initialize and use the database, 
run the command:


install c:\destination\directory

If you want to install only the client applications and interface 
libraries, then you can use these commands:


install c:\destination\directory client






Thanks,
Cathy

```
Microsoft Visual Studio Solution File, Format Version 12.00
# Visual Studio 2022





 >
 > Screenshot_20230222_091621.png
 > I look forward to hearing back from you.
 >
 > Thanks!
 > Cathy
 >
 >
 >      >
 >      > Best regards,
 >      > Cathy
 >      >
 >
 >     --
 >     Adrian Klaver
 > adrian.kla...@aklaver.com 
>
 >

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

curiosity in default column header

[Rob Sargent]

riftehr=> select ascii(substring('sadb', 2,1));
 ascii
---
    97
(1 row)

riftehr=> select 24::bit(8);
   bit
--
 00011000
(1 row)

riftehr=> select ascii(substring('sadb', 2,1))::bit(8);
  ascii
--
 0111
(1 row)

Why is the last one headed "ascii" and not "bit"?

Re: curiosity in default column header

[David G. Johnston]

On Fri, Feb 24, 2023 at 1:47 PM Rob Sargent  wrote:

>
> Why is the last one headed "ascii" and not "bit"?
>

Because a function name is more likely to be a useful label than a data
type.

David J.

Re: can't get psql authentication against Active Directory working

[Stephen Frost]

Greetings,

* Tomas Pospisek (t...@sourcepole.ch) wrote:
> again thanks a lot for trying to help me!

Sure, would like to see it work for you.

> On 21.02.23 16:29, Stephen Frost wrote:
> > * Tomas Pospisek (t...@sourcepole.ch) wrote:
> > > On 20.02.23 15:17, Stephen Frost wrote:
> > > > * Tomas Pospisek (t...@sourcepole.ch) wrote:
> > > > > so I'm trying to authenticate psql (on Windows) -> postgres (on 
> > > > > Linux) via
> > > > > Active Directory.
> > > > 
> > > > Looks like you're trying to do a bit more than that as you're using
> > > > GSSAPI (not SSPI, which would be the more typical method on Windows..)
> > > > and you're asking for an encrypted connection.  Is there a reason you're
> > > > going down this particular route..?
> > > 
> > > The reason I'm using the GSSAPI is it's the only documented way to do
> > > authentication from psql.exe against Active Directory that was able to 
> > > find.
> > 
> > I mean ... https://www.postgresql.org/docs/current/sspi-auth.html
> 
> Were should the settings that are discussed in that document be configured?
> In pg_hba.conf?

Yes, it's in that part of the documentation.  Note that you may not need
to actually configure any of those options..

> That document says:
> 
> "SSPI authentication only works when both server and client are running
> Windows, or, on non-Windows platforms, when GSSAPI is available."
> 
> I interpret that phrase like this:
> 
> * there's a case where both server and client are running Windows (doesn't
> apply to me)
> * there's a case where both are running non-Windows (doesn't apply to me
> either - server is Linux, client is Windows)
> 
> So that's an extremely terse docu that is not clear at all to me.
> 
> I'd suggest to change that phrase into:
> 
> "SSPI authentication works when both server and client are running Windows.
> When server or client are on non-Windows platforms then those need to use
> GSSAPI."
> 
> - assuming that my interpretation of that super terse docu is correct of
> course.

No, that's not correct.  The blog post that I referenced is actually
using SSPI on the client and GSS on the server and it works because
they're compatible with each other on the wire.  Perhaps we could
improve the documentation you reference above but it's not actually
wrong as-is.  Perhaps this would be clearer:

SSPI and GSS are wireline compatible and can be mixed and matched
between clients and servers (where support for GSS is built into the
client library or the server), provided that there is an encryption
method which both will accept.

The encryption method bit is more of a historical artifact at this
point as modern systems have compatible AES-based encryption methods,
but that wasn't always the case.  It's also possible that it'll become
an issue in the future but at least for the moment most installations
have an AES-based compatible encryption method.

> > > Even now I am unable to find *any* documentation on how to go about doing
> > > auth from psql.exe to AD with SSPI. Would you have any pointers to
> > > documentation or a howto (or a blog post or a stackoverflow answer or...).
> > 
> > Sure, here's a blog post that I wrote about doing exactly that:
> > 
> > https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication
> 
> Note that that document doesn't mention SSPI a single time.

It doesn't actually need to because SSPI is the default and it all just
works..  I suppose I could have added in somewhere "SSPI is used on the
client for this", perhaps, but the blog post was more focused on "this
is what you do to make it work" and you don't really need to know that
the API that the client is using in this case happens to be called SSPI.

> The ultra terse Postgres docu is using terms such as "SAM", "negotiate
> mode", "UPN", without a reference to their definition, which leaves to
> Windows noobs like me (and equaly Windows non-noobs) without a clue.

I'm certainly all for improving the documentation.  Not sure that just
spelling those out would really be enough and I'm rather confident that
trying to explain all of how Kerberos and AD work wouldn't really be
maintainable in our documentation.  Still, specific suggestion on how we
can improve would be great.

> Searching "kerberos SAM" or "SSPI negotiate mode" on duckduckgo doesn't seem
> to lead to useful hits. So the documentation seems to be geared to experts
> of both Kerberos and Active Directory and Windows technology alike which as
> far as I can tell is a very rare specimen.

Active Directory experts certainly should be generally familiar with
Kerberos (and the terms you reference above are actually all AD ones,
really..), so I'd argue that the documentation is mainly focused on
helping those individuals.  I do generally agree that it would be good
if there were more folks out there who understood this stuff.  I'm not
sure how to go about making that happen though.

> > > The reason I am apparently asking for encrypted com