How to make PostreSQL utilities honor home directories?
Hi Everyone,
We are having a heck of a time getting PostreSQL utilities to honor
home directories. For example, when I execute this script:
sudo -H -u postgres PGPASSWORD=${password} \
psql -h "${hostname}" -U "${username}" -d "${database}" \
--command="..."
It produces failures:
could not change directory to "/home/jwalton/godojo": Permission denied
/home/jwalton is my home directory. The postgres user does not have
access to my stuff. The postgres user's home directory is
/var/lib/pgsql .
Reading through the `psql --help` options and searching on the web is
not turning up any hits.
We are also observing the errors when using pg_isready .
How to make PostreSQL utilities honor home directories?
Thanks in advance.
Jeff
Re: How to make PostreSQL utilities honor home directories?
On Thu, Sep 1, 2022 at 8:23 PM Peter J. Holzer wrote:
>
> On 2022-09-01 18:16:14 -0400, Tom Lane wrote:
> > Jeffrey Walton writes:
> > > We are having a heck of a time getting PostreSQL utilities to honor
> > > home directories. For example, when I execute this script:
> >
> > > sudo -H -u postgres PGPASSWORD=${password} \
> > > psql -h "${hostname}" -U "${username}" -d "${database}" \
> > > --command="..."
> >
> > > It produces failures:
> >
> > > could not change directory to "/home/jwalton/godojo": Permission
> > > denied
> >
> > You've left out quite a lot of information here ... like what
> > connection that directory has to do with anything. Is it your
> > current directory when you invoke this command?
>
> Probably. See below.
>
>
> > If so, a plausible explanation is that psql is trying to chase a
> > symlink to somewhere, which involves some chdir's so it can resolve
> > the symlink correctly, and afterwards it has to change back to
> > where it started --- which would fail if it can't look up that
> > directory.
> >
> > Why it's trying to resolve a symlink isn't apparent though.
> > Is the "psql" you're invoking a symlink to somewhere?
>
> It is on Debian/Ubuntu:
>
> % ls -l =psql
> lrwxrwxrwx 1 root root 37 Aug 11 11:25 /bin/psql ->
> ../share/postgresql-common/pg_wrapper*
>
> (this is the pgdg package)
>
> I do get the same message, but psql seems to start normally:
>
> % sudo -u postgres -H psql
> could not change directory to "/home/hjp/tmp/t": Permission denied
> Null display is "(∅)".
> Line style is unicode.
> Border style is 2.
> Unicode border line style is "double".
> Timing is on.
> Expanded display is used automatically.
> psql (13.8 (Ubuntu 13.8-1.pgdg20.04+1), server 11.17 (Ubuntu
> 11.17-1.pgdg20.04+1))
> Type "help" for help.
>
> postgres=#
>
> However, when I start a shell, I see that the directory has been
> changed:
>
> postgres=# \!
> postgres@trintignant:/usr/lib/postgresql/13/bin$
>
>
> However, the symlink doesn't seem to be the culprit. If I run
>
> % sudo -u postgres -H /usr/lib/postgresql/13/bin/psql
>
> (which is not a symlink)
>
> I get the same behaviour. So it seems that psql changes to its basedir
> and then can't change back again.
>
> And sure enough, strace shows:
>
> chdir("/usr/lib/postgresql/13/bin") = 0
> chdir("/home/hjp/tmp/t")= 0
> chdir("/usr/lib/postgresql/13/bin") = 0
> chdir("/home/hjp/tmp/t")= 0
>
> (this is without sudo, because I can't strace that)
Thanks Peter.
I guess there is no way to avoid the problem.
And if interested, others have trouble, too. I found this when
searching for a resolution:
https://github.com/ANXS/postgresql/issues/499 .
Jeff
Re: How to make PostreSQL utilities honor home directories?
On Thu, Sep 1, 2022 at 8:51 PM Tom Lane wrote: > > "Peter J. Holzer" writes: > > However, the symlink doesn't seem to be the culprit. If I run > > % sudo -u postgres -H /usr/lib/postgresql/13/bin/psql > > (which is not a symlink) > > I get the same behaviour. So it seems that psql changes to its basedir > > and then can't change back again. > > Ah --- looking closer at that code, it will chdir *before* checking > whether the target file is a symlink, which is probably unnecessarily > stupid. I'm wondering whether we could drop that logic altogether [1], > but that won't help you today. > > I concur with the other person asking why you want to sudo to postgres > at all, though. It's generally safest if the client side isn't running > as the same user as the server. The use case is an install of DefectDojo [2]. I _think_ they are taking advantage of the fact that as root, you don't need to authenticate because of postgresql's use of domain sockets. (Hat tip for that, by the way). The installer code will install packages, setup the database, install the DefectDojo programs, etc. Jeff > [1] https://www.postgresql.org/message-id/797232.1662075573%40sss.pgh.pa.us [2] https://github.com/DefectDojo/godojo
Re: How to make PostreSQL utilities honor home directories?
On Fri, Sep 2, 2022 at 7:34 AM Peter J. Holzer wrote:
>
> On 2022-09-01 20:49:56 -0400, Jeffrey Walton wrote:
> > On Thu, Sep 1, 2022 at 8:23 PM Peter J. Holzer wrote:
> > >
> > > On 2022-09-01 18:16:14 -0400, Tom Lane wrote:
> > > > Jeffrey Walton writes:
> > > > > We are having a heck of a time getting PostreSQL utilities to honor
> > > > > home directories. For example, when I execute this script:
> > > >
> > > > > sudo -H -u postgres PGPASSWORD=${password} \
> > > > > psql -h "${hostname}" -U "${username}" -d "${database}" \
> > > > > --command="..."
> > > >
> > > > > It produces failures:
> > > >
> > > > > could not change directory to "/home/jwalton/godojo": Permission
> > > > > denied
>
> > > I get the same behaviour. So it seems that psql changes to its basedir
> > > and then can't change back again.
> >
> > I guess there is no way to avoid the problem.
>
> Changing to /tmp (or some other directory accessible by posgres) before
> running the script would avoid it.
> As would (temporarily) changing the permissions of the diretor(y/ies).
> Or not using sudo at all (see other messages).
Ok, thanks everyone.
Determine if a user and database are available
Hi Everyone,
I have another beginner question. I am trying to use pg_isready to
determine if a database and user are present. The program seems to
always succeed, even when I delete the user or the database.
This baffles me from the man page. I guess this explains the behavior
I am seeing.
NOTES
It is not necessary to supply correct user name, password, or database
name values to obtain the server status; however, if incorrect values
are provided, the server will log a failed connection attempt.
A typical usage is shown below, where variables are parsed from a config file.
password=$(grep 'DD_DB_Rpass' dojoConfig.yml | awk '{ print $2 }')
hostname=$(grep 'DD_DB_Host' dojoConfig.yml | awk '{ print $2 }')
database=$(grep 'DD_DB_Name' dojoConfig.yml | awk '{ print $2 }')
username=$(grep 'DD_DB_Ruser' dojoConfig.yml | awk '{ print $2 }')
PGPASSWORD=${password} pg_isready \
-h "${hostname}" -U "${username}" -d "${database}"
Given the NOTES in the man page, how do we determine if a user and
database are present using the shell? Is there another utility we
should be using?
Thanks in advance,
Jeff
Changing the admin/postgres user password
Hi Everyone, I'm struggling to set the admin/postgres user password and use it on Fedora 36. This is a fresh install on a new VM. The admin's name is postgres. We created the user, and used the passwd utility to set the Linux password 'hi...HS'. The ellipses are over 28 characters, so it is a 32-character password. Next, move onto Postgres auth. I followed https://www.postgresql.org/docs/current/install-short.html . We used 'su - postgres' and logged on with Linux password. I changed the postgres password with 'alter user postgres with password hi...HS'. The change appeared to be successful. Next, restart the service: sudo systemctl restart postgresql.service And finally, try the new password: PGPASSWORD=hi...HS psql -U postgres psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres" It appears the password is not working. I screwed something up somewhere. Ugh... Does anyone know what I did wrong in this process? Or maybe better, what part of the manual discusses auth failures so I can read about them? Thanks in advance. Jeff
Re: Determine if a user and database are available
On Fri, Sep 2, 2022 at 5:43 PM Christophe Pettus wrote: > > > On Sep 2, 2022, at 14:22, Jeffrey Walton wrote: > > Given the NOTES in the man page, how do we determine if a user and > > database are present using the shell? Is there another utility we > > should be using? > > pg_isready literally only checks that the server can be reached over the > connection path (network or sockets), not that any login credentials work. > You can use psql do that, though: > > psql > ... will return an error if the connection information can't be used to > successfully log in. Now available as a direct replacement for pg_isready : https://github.com/noloader/pg_check_conn . Jeff
Re: lippq client library and openssl initialization: PQinitOpenSSL()
On Sun, Sep 11, 2022 at 6:55 AM Sebastien Flaesch wrote: > > The PostgreSQL doc says that if the application code is initializing OpenSSL, > it should tell PostgreSQL libpq client library that OpenSSL initialization is > already done: > > https://www.postgresql.org/docs/14/libpq-ssl.html#LIBPQ-SSL-INITIALIZE > > I was wondering if this is still true with OpenSSL 1.1.0+ > > The APIs to initialize OpenSSL are OPENSSL_init_ssl() or > OPENSSL_init_crypto(). > > According to the OpenSSL doc, version 1.1.0 initializes itself automatically > when calling other APIs ... > > https://www.openssl.org/docs/man1.1.1/man3/OPENSSL_init_ssl.html > > As of version 1.1.0 OpenSSL will automatically allocate all resources that it > needs so no explicit initialisation is required. Similarly it will also > automatically deinitialise as required. > > So, is a call to PQinitOpenSSL(0, 0) still needed? > > I did some test with our application, and I could establish a TLS/SSL > connection using server and client certificates. > > What can go wrong in fact? > > Can someone give me a hint, so I can prove that we really need to call > PQinitOpenSSL(0,0)? > > Note: Our application is for now single-threaded. > > OpenSSL doc also states: > > However, there may be situations when explicit initialisation is desirable or > needed, for example when some nondefault initialisation is required. > > If our application would requires nondefault initialization, I assume that > PostgreSQL openssl usage will implicitly inherit the OpenSSL seetings of our > application, right? > > Can this be an issue for PostgreSQL, or can both just share the same OpenSSL > settings/config? For the OpenSSL side of things, then see https://wiki.openssl.org/index.php/Library_Initialization . Jeff
Re: fully qualified domain names and .pgpass
On Tue, Oct 4, 2022 at 1:02 PM Ron wrote: > > Sometimes (both interactively and via script) I access a remote Pg server > via just the bare host name "foobar", and other times via the FQDN > "foobar.example.com". > > I've only been able to get this to work by having two lines in the .pgpass > file: > > foobar:5432:postgres:Allegedly.Strong.Password > foobar.example.com:5432:postgres:Allegedly.Strong.Password > > But I'd rather have only one line. Is there any way to do that? This is not a FQDN: > "foobar.example.com". A FQDN ends in dot '.' The dot denotes the top of the dns tree. So a FQDN for the host would be: foobar.example.com. Anyone who tells you any different has not read W. Richard Stevens :) Jeff
Re: Does psqlodbc_11_01_0000-x64 support special characters?
On Wed, Oct 12, 2022 at 7:16 AM gzh wrote: > > I found that the password can't contain the % character, and the other > special characters (* , $) are no problem. You need to percent-encode the password if you wish to use the % symbol in the password. There are other reserved characters that you should percent-encode. See https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING and https://www.rfc-editor.org/rfc/rfc3986#section-2.1 . Jeff > At 2022-10-12 16:28:51, "gzh" wrote: > > > PostgreSQL version: 13.5 > > Operating system: windows 10 > > Description: > > > I wrote a VBA application to connect to PostgreSQL database by psqlodbc. > > The application works fine when there are no special characters in the > password. > > When the password contains special characters (e.g. * , $ %), > > the application responds with an error below: > > > Number: -2147467259 > > Description: password authentication failed for user 'testdb' > > > I made an sample as below: > > > VBA > > - START - > > > Sub dbconnTest() > > Dim rs As ADODB.Recordset > > Dim sql As String > > Dim i As Integer > > Dim rcnt As Integer > > > > Set cnn = New ADODB.Connection > > cnn.Open "Provider=MSDASQL;Driver=PostgreSQL > Unicode;UID=postgres;port=5432;Server=localhost;Database=testdb;PWD=Gd*oB,$3Ln%pQ" > > > > Set rs = New ADODB.Recordset > > sql = "SELECT * FROM testtbl" > > > > rs.ActiveConnection = cnn > > rs.Source = sql > > rs.Open > > > > cnt = rs.Fields.Count > > rcnt = 2 > > > > Do Until rs.EOF > > For i = 0 To cnt - 1 > > Cells(rcnt, i + 1).Value = rs.Fields(i) > > Next > > > > rcnt = rcnt + 1 > > rs.MoveNext > > Loop > > > > Set rs = Nothing > > Set cnn = Nothing > > End Sub > > > - END - > > > > Thanks for any help! >
Re: Re: Does psqlodbc_11_01_0000-x64 support special characters?
On Thu, Oct 13, 2022 at 12:13 AM gzh wrote:
>
> My PostgreSQL is deployed on Amazon RDS, so the password of PostgreSQL is
> random and has various reserved characters.
>
> I don't know if the reserved characters below are complete, and there are
> some characters (e.g. * , $) I tried without problems.
>
> Could you tell me which characters require percent-encoding for PostgreSQL
> password?
>
>
> space → %20
>
> ! → %21
>
> " → %22
>
> # → %23
>
> $ → %24
>
> % → %25
>
> & → %26
>
> ' → %27
>
> ( → %28
>
> ) → %29
>
> * → %2A
>
> + → %2B
>
> , → %2C
>
> - → %2D
>
> . → %2E
>
> / → %2F
>
> : → %3A
>
> ; → %3B
>
> < → %3C
>
> = → %3D
>
> > → %3E
>
> ? → %3F
>
> @ → %40
>
> [ → %5B
>
> \ → %5C
>
> ] → %5D
>
> ^ → %5E
>
> _ → %5F
>
> ` → %60
>
> { → %7B
>
> | → %7C
>
> } → %7D
>
> ~ → %7E
https://www.rfc-editor.org/rfc/rfc3986#section-2.2
Jeff
Re: PostgreSql Service different path
On Mon, Oct 24, 2022 at 6:38 AM chris navarroza wrote: > > I install postgresql14.5 with the following commands > > sudo yum install postgresql14-server postgresql14-contrib > > > sudo su postgres > > cd /tmp > > /usr/pgsql-14/bin/initdb -D /home/dmartuser/pgsql/14/data > > and update the postgresql.conf to the new path, now when I start the service, > it has an error and when I check it says > > -- Unit postgresql-14.service has begun starting up. > Oct 24 15:16:45 datamartds postgresql-14-check-db-dir[132338]: > "/var/lib/pgsql/14/data/" is missing or empty. > Oct 24 15:16:45 datamartds postgresql-14-check-db-dir[132338]: Use > "/usr/pgsql-14/bin/postgresql-14-setup initdb" to initialize the database > cluster. > Oct 24 15:16:45 datamartds postgresql-14-check-db-dir[132338]: See > /usr/share/doc/postgresql14/README.rpm-dist for more information. > Oct 24 15:16:45 datamartds systemd[1]: postgresql-14.service: Control process > exited, code=exited status=1 > Oct 24 15:16:45 datamartds systemd[1]: postgresql-14.service: Failed with > result 'exit-code'. > > How can I point the service to read the new path ( > /home/dmartuser/pgsql/14/data )? The startup script that is calling systemd should perform a `mkdir -p /var/lib/pgsql/14/data` before calling the PostgreSQL binary. Jeff
Re: Segmentation Fault PG 14
On Mon, Nov 7, 2022 at 2:38 PM Tom Lane wrote: > > Willian Colognesi writes: > > `I take it things were okay with the version you used previously?` > > > Yes, it was working pretty well in another instance with pg version > > `12.4-1.pgdg18.04+1`, and we had to make a migration of one database that > > was running in this server to another using Logical Replication. > > 12.4 to 14.5 is kind of a big jump :-(. > > The stack trace seems to indicate that ExecProcNode transferred control > to never-never land, which says that something clobbered the function > pointer it's trying to indirect through. I don't recall having seen > any similar reports though. I'm just thinking out loud... I've seen the latest GCC do that on what it believes to be dead code. Our problem was detailed at https://github.com/weidai11/cryptopp/issues/1141 . We identified the problem by building/running our self tests with -fsanitize=unreachable . Testing with -fsanitize=unreachable should confirm or rule out GCC and Clang [incorrectly] removing code that is actually needed. If this is the problem, then -fsanitize=unreachable will also provide a usable stack trace and provide a useful debugging experience. Jeff
Re: PCI:SSF - Safe SQL Query & operators filter
On Mon, Nov 7, 2022 at 8:25 PM Jan Bilek wrote:
> ...
> select * from pg_read_file('/etc/passwd' , 0 , 100); -> it's possible to
> display content of '/etc/passwd/' file
> select version(); -> Result of DBMS version request.
Input filtering may help in the interim, until you get the roles and
privileges sorted out.
> COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab'; -> it's
> possible to create files on the filesystem on behalf of 'postgres' user.
Prepared Statement or Parameterized Query here. In this case, don't
execute data as code.
Jeff
Re: pg_isready mandatory parameters?
On Fri, Nov 11, 2022 at 12:09 PM Zwettler Markus (OIZ) wrote: > > I found this in PG14 è > > bash-4.4$ ./pg_isready --version > > pg_isready (PostgreSQL) 14.2 > > bash-4.4$ ./pg_isready > > /var/run/postgresql:5432 - no attempt > > bash-4.4$ ./pg_isready -h localhost > > localhost:5432 - no attempt > > bash-4.4$ ./pg_isready -h localhost -p 5432 > > localhost:5432 - no attempt > > bash-4.4$ ./pg_isready -h localhost -p 5432 -d postgres > > localhost:5432 - no attempt > > bash-4.4$ ./pg_isready -h localhost -p 5432 -d postgres -U postgres > > localhost:5432 - accepting connections > > I always thought all parameters are optional? No? Optional or ignored??? This may be more what you are looking for: https://github.com/noloader/pg_check_conn Jeff
Re: Best Open Source OS for Postgresql
On Tue, Jan 31, 2023 at 3:03 AM Giovanni Biscontini wrote: > We're looking for a Open Source alternative to Rhel for our VM server > dedicated to Postgresql (14->15) installations. We're testing Alma, Rocky, > and Oracle distributions as they're compatible with Rhel package systems. > Can you share your experience on a similar choice? > Let me throw my choice into the mix... Fedora Server. The benefits of Fedora Server is modern software. Every six months, you get the latest release of nearly all packages. The downside is, you lose API/ABI compatibility. Fedora Server is always moving forward with the latest packages, including past ABI/API breaks. Another downside is, you run dnf-system-upgrade ( https://docs.fedoraproject.org/en-US/quick-docs/dnf-system-upgrade/) every six months. It is like Ubuntu's do-release-upgrade. The real upside to modern software is, you get all the bug fixes, including latent bugs that should have CVE's associated with them but were misclassified. For a discussion, see Greg KH's talk at https://thenewstack.io/design-system-can-update-greg-kroah-hartman-linux-security/ . Jeff
Re: valgrind a background worker
On Fri, Feb 10, 2023 at 10:04 AM Tom Lane wrote: > > =?UTF-8?Q?Jon_Erdman?= writes: > > I've got a background worker that has a slow memory leak in it > > somewhere. How can I get it to start under valgrind to get a memcheck > > output from it? > > You have to valgrind the whole cluster AFAIK. Basically, start > the postmaster under valgrind with --trace-children=yes. > For leak tracking you probably also want > --leak-check=full --track-origins=yes --read-var-info=yes One additional comment... the program in question and PostgreSQL should also be built with -g -O1 per https://valgrind.org/docs/manual/quick-start.html . Otherwise, there's a risk the line information will not be accurate or usable. Jeff
Re: Memory leak using when using libpq PQExecParams() CRYPTO_zalloc()
On Wed, Feb 22, 2023 at 8:35 AM Michael Arnold wrote:
>
> Am looking for guidance on how to fix a memory leak when using libpq
> PQExecParams(). Memory leaks through CRYPTO_zalloc() and arises when using
> json_agg(). None-JSON based PQExecParams() calls are not leaking.
>
> Using Postgresql 13.6 (Ubuntu 13.6-0ubuntu0.21.10.1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 11.2.0-7ubuntu2) 11.2.0, 64-bit
> Accessed by a c/c++ application via libpq (libssl3 and libcrypt are also
> linked)
>
> Valgrind reports:
>
> ==4107== 2,712 bytes in 3 blocks are definitely lost in loss record 265 of 276
> ==4107==at 0x4848899: malloc (in
> /usr/libexec/valgrind/vgpreload_memcheck-amd64-linux.so)
> ==4107==by 0x4B1F41D: CRYPTO_zalloc (in
> /usr/lib/x86_64-linux-gnu/libcrypto.so.3)
> ==4107==by 0x4AD50A8: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.3)
> ==4107==by 0x4AD51EC: ERR_clear_error (in
> /usr/lib/x86_64-linux-gnu/libcrypto.so.3)
> ==4107==by 0x488D52A: ??? (in /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
> ==4107==by 0x488E89D: ??? (in /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
> ==4107==by 0x488E923: PQsendQueryParams (in
> /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
> ==4107==by 0x4893C27: PQexecParams (in
> /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
> ==4107==by 0x3E1613: getJSON(std::__cxx11::basic_string std::char_traits, std::allocator >) (dbInterface.cpp:7202)
> ==4107==by 0x47ACEC:
> tickerHandler::handleRequest(Poco::Net::HTTPServerRequest&,
> Poco::Net::HTTPServerResponse&) (externalInterface.cpp:615)
> ==4107==by 0x5096E18: Poco::Net::HTTPServerConnection::run() (in
> /usr/lib/x86_64-linux-gnu/libPocoNet.so.80)
> ==4107==by 0x50D935A: Poco::Net::TCPServerConnection::start() (in
> /usr/lib/x86_64-linux-gnu/libPocoNet.so.80)
>
> i.e. call to PQExecParams() within getJSON() is leaking via CRYTO_zalloc().
> The code for getJSON() is reproduced below.
>
> std::string getJSON(std::string sqlQuery){
> PGconn *dbConn = nullptr;
> int nParams = 0;
> int resultFormat = 1; //binary format
> size_t noRecords = 0;
> PGresult *res = nullptr;
> std::string resultJSON;
>
> std::string fullQuery = "select json_agg(t) FROM (" + sqlQuery + ") t;";
>
> if ((dbConn = getConnection(connectionPool)) != nullptr) {
> res = PQexecParams(dbConn,
> fullQuery.c_str(),
> nParams,
> NULL,
> NULL,
> NULL,
> NULL, resultFormat);
>
> //Check if there was a problem
> if (PQresultStatus(res) != PGRES_TUPLES_OK) {
> if (dbConn != nullptr){
> returnConnection(connectionPool, dbConn);
> PQclear(res);
> }
> return (resultJSON);
> } //End of result checking
>
> //If we successfully get the information then populate resultJSON
> noRecords = (int64_t) PQntuples(res);
> if (noRecords == 1)
> resultJSON = std::string((const char*) PQgetvalue(res, 0, 0));
>
> //Clean-up
> PQclear(res);
> returnConnection(connectionPool, dbConn);
> }
>
> //select json_arr may return an empty string "", but this is not valid json
> and json.parse will error
> // to avoid this return a valid json empty string
> if (resultJSON.empty())
> resultJSON = "\"\"";
>
> return (resultJSON);
> }
>
> getConnection() and returnConnection() are application specific calls to get
> and replace valid PGconn* to / from an in-app postgres connection pool.
>
> Setup is really basic:
> 1. In postgresql.conf
> 1a. Uncomment listen_addresses
> 1b. Replace localhost with *
> 1c. Uncomment password_encryption
>
> 2. In pg_hba.conf
> 2a. Add the local network:
> host all all 192.168.0.101/24 md5
> 2b. Replace ident with md5
Before you do anything with Valgrind, you should rebuild Postgres,
your program, and dependent libraries of interest with -g -O1.
Otherwise you risk wasting [a lot] time on false positives.
Also see https://valgrind.org/docs/manual/quick-start.html .
Jeff
Re: Properly handle OOM death?
On Mon, Mar 13, 2023 at 1:21 PM Israel Brewster wrote: > > I’m running a postgresql 13 database on an Ubuntu 20.04 VM that is a bit more > memory constrained than I would like, such that every week or so the various > processes running on the machine will align badly and the OOM killer will > kick in, killing off postgresql, as per the following journalctl output: > > Mar 12 04:04:23 novarupta systemd[1]: postgresql@13-main.service: A process > of this unit has been killed by the OOM killer. > Mar 12 04:04:32 novarupta systemd[1]: postgresql@13-main.service: Failed with > result 'oom-kill'. > Mar 12 04:04:32 novarupta systemd[1]: postgresql@13-main.service: Consumed 5d > 17h 48min 24.509s CPU time. > > And the service is no longer running. > > When this happens, I go in and restart the postgresql service, and everything > is happy again for the next week or two. > > Obviously this is not a good situation. Which leads to two questions: > > 1) is there some tweaking I can do in the postgresql config itself to prevent > the situation from occurring in the first place? > 2) My first thought was to simply have systemd restart postgresql whenever it > is killed like this, which is easy enough. Then I looked at the default unit > file, and found these lines: > > # prevent OOM killer from choosing the postmaster (individual backends will > # reset the score to 0) > OOMScoreAdjust=-900 > # restarting automatically will prevent "pg_ctlcluster ... stop" from working, > # so we disable it here. Also, the postmaster will restart by itself on most > # problems anyway, so it is questionable if one wants to enable external > # automatic restarts. > #Restart=on-failure > > Which seems to imply that the OOM killer should only be killing off > individual backends, not the entire cluster to begin with - which should be > fine. And also that adding the restart=on-failure option is probably not the > greatest idea. Which makes me wonder what is really going on? > Related, we (a FOSS project) used to have a Linux server with a LAMP stack on GoDaddy. The machine provided a website and wiki. It was very low-end. I think it had 512MB or 1 GB RAM and no swap file. And no way to enable a swap file (part of an upsell). We paid about $2 a month for it. MySQL was killed several times a week. It corrupted the database on a regular basis. We had to run the database repair tools daily. We eventually switched to Ionos for hosting. We got a VM with more memory and a swap file for about $5 a month. No more OOM kills. If possible, you might want to add more memory (or a swap file) to the machine. It will help sidestep the OOM problem. You can also add vm.overcommit_memory = 2 to stop Linux from oversubscribing memory. The machine will act like a Solaris box rather than a Linux box (which takes some getting used to). Also see https://serverfault.com/questions/606185/how-does-vm-overcommit-memory-work . Jeff
Re: Properly handle OOM death?
On Sat, Mar 18, 2023 at 6:02 PM Tomas Pospisek wrote: > > On 13.03.23 21:25, Joe Conway wrote: > > > Hmm, well big +1 for having swap turned on, but I recommend setting > > "vm.overcommit_memory=2" even so. > > I've snipped out the context here, since my advice is very unspecific: > do use swap only as a safety net. Once your system starts swapping > performance goes down the toilet. To use swap as a safety net, set swappiness to a low value, like 2. Two will keep most data in RAM and reduce (but not eliminate) spilling to the file system. I have a bunch of old ARM dev boards that are resource constrained. They use SDcards, which have a limited lifetime based on writes. I give the boards a 1 GB swap file to avoid OOM kills when running the compiler on C++ programs. And I configure them with a swappiness of 2 to reduce swapping. Jeff
Re: PostgreSQL vs MariaDB
On Fri, Mar 24, 2023 at 7:07 AM Inzamam Shafiq wrote: > > Can someone please list pros and cons of MariaDB vs PostgreSQL that actually > needs serious consideration while choosing the right database for large OLTP > DBs (Terabytes)? > For me, security is important. I don't want to do a lot of late night patching, and I don't want to cleanup after a data breach. When vendors pitch a product with a database, I insist on a PostgreSQL backend. MySQL makes regular appearances on BugTraq. MySQL has over 1700 CVEs going back to 1999.[1] It tells me there are problems with the engineering process. MariaDB adds additional CVEs on top of MySQL, but the count appears low. I have never separated the purely MariaDB flaws from the underlying MySQL flaws. In contrast, PostgreSQL has about 240 CVEs going back to 1999.[2] It tells me PostgreSQL has a better engineering process. Jeff [1] https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=MySQL [2] https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=PostgreSQL
Re: murmur3 hash binary data migration from Oracle to PostgreSQL
On Mon, Apr 24, 2023 at 9:22 PM Jagmohan Kaintura wrote: > > We are doing Migration from Oracle to PostgreSQL. In SOurce database we have > Binary data stored using murmur3 hashing function. In Oracle this data is > being generated from the Java code and inserted into the Oracle database. > > As part of Migration processes the reference data on which this murmur3 is > generated is also getting changed while migrating to PostgreSQL. > > In PostgreSQL do we have any mechanism for fetching this murmur3 hash > function for any UUID. > > Please let me know, what ever solution is available for the implementation. My apologies if I misparsed a couple of statements. I am having trouble determining if you are migrating away from Murmur3. If you are selecting a new digest, then SipHash would be an excellent choice. It was designed to avoid collisions and be fast. Plus it was designed by Jean-Philippe Aumasson and Daniel J. Bernstein. It doesn't get much better than those two fellows. Jeff
Re: SSL Enablement in Postgres via Client App like PG-ADMIN-4, Java.
On Fri, May 5, 2023 at 9:50 AM sujay kadam wrote: > > I have enabled SSL in postgres on a different port using pgbouncer. > > I have changed the default port to 6432 and made it SSL enabled and > configured pgbouncer to listen port 5432 which is non-ssl. > > But we are connecting directly using port, not by uploading a certificate or > setting up SSL certificate and properties in pgadmin4 and java. > > Our requirement is :- > we should connect ssl-enabled port by configuring ssl-properties and > providing certificates path in any client tool such as pgadmin4 or using java. > > > Below are some questions that needs to clarified :- > > How to enable SSL in Postgres database? > > How to establish the SSL connection from the client (Eg. Java )? Do we need > to add any certifications at client side for SSL authentication ? > > Do we have any documentation for SSL ? The architecture for a client/server with a database usually hides the database and exposes a web server. Clients connect to your web service, and not the database directly. Your web service connects to the database. Jeff
Re: "PANIC: could not open critical system index 2662" - twice
On Sat, May 6, 2023 at 6:35 AM Thomas Munro wrote: > > On Sat, May 6, 2023 at 9:58 PM Evgeny Morozov > wrote: > > Right - I should have realised that! base/1414389/2662 is indeed all > > nulls, 32KB of them. I included the file anyway in > > https://objective.realityexists.net/temp/pgstuff2.zip > > OK so it's not just page 0, you have 32KB or 4 pages of all zeroes. > That's the expected length of that relation when copied from the > initial template, and consistent with the pg_waldump output (it uses > FPIs to copy blocks 0-3). We can't see the block contents but we know > that block 2 definitely is not all zeroes at that point because there > are various modifications to it, which not only write non-zeroes but > must surely have required a sane page 0. > > So it does indeed look like something unknown has replaced 32KB of > data with 32KB of zeroes underneath us. This may be related... I seem to recall the GNUlib folks talking about a cp bug on sparse files. It looks like it may be fixed in coreutils release 9.2 (2023-03-20): https://github.com/coreutils/coreutils/blob/master/NEWS#L233 If I recall correctly, it had something to do with the way copy_file_range worked. (Or maybe, it did not work as expected). According to the GNUlib docs (https://www.gnu.org/software/gnulib/manual/html_node/copy_005ffile_005frange.html): This function has many problems on Linux kernel versions before 5.3 > Are there more non-empty > files that are all-zeroes? Something like this might find them: > > for F in base/1414389/* > do > if [ -s $F ] && ! xxd -p $F | grep -qEv '^(00)*$' > /dev/null > then > echo $F > fi > done
Re: OpenSSL version 3
On Mon, Jun 19, 2023 at 11:39 AM Daniel Gustafsson wrote: > > > On 19 Jun 2023, at 17:13, Sebastien Flaesch > > wrote: > > > OpenSSL V1 goes end of support soon (see > > https://www.openssl.org/policies/releasestrat.html) with replacement should > > be OpenSSL V3. > > > > What is the strategy with PostgreSQL (regarding sources, and binary > > packages)? > > > > On the various supported platforms? > > PostgreSQL supports OpenSSL 3 and have done so for some time, each individual > packager and platform are however free to choose which OpenSSL version they > ship. This is a recurring question. I've seen it 3 or 4 times in the last 6 months or so. The information should probably be added to the FAQ at https://wiki.postgresql.org/wiki/FAQ . Jeff
Re: When will trusted PL/Python be supported?
On Tue, Jun 27, 2023 at 12:17 PM Tom Lane wrote: > > Adrian Klaver writes: > > On 6/27/23 04:46, Bowen Shi wrote: > >> I'd like to know is supporting trusted PL/Python still in the > >> schedule? What is the reason for the current lack of support, and do > >> we have any relevant email discussion? > > https://www.postgresql.org/message-id/flat/20030525224833.GO31407%40tummy.com > > https://www.postgresql.org/message-id/flat/Pine.LNX.4.44.0306182125590.17051-10%40penguin.theopalgroup.com > > There's been a little bit of discussion of using RestrictedPython: > > https://www.postgresql.org/message-id/flat/9d1f8d830808041008v50104fd8p6181d5ddce85a6a%40mail.gmail.com > > but it doesn't seem to have gone anywhere. Off-topic, but I am amazed folks like Tom and Adrian remember those conversations and can actually find them in the archives. Jeff
Re: My 1st JDBC and PostgreSQL
On Wed, Jul 19, 2023 at 8:37 PM Amn Ojee Uw wrote:
>
> Following the example in :
> https://www.tutorialspoint.com/postgresql/postgresql_java.htm
> I wrote the bellow method :
>
> public void connectToDatabase() throws ClassNotFoundException,
> SQLException {
> try {
> this.perr(this.getDatabaseClass()); //
> org.postgresql.Driver = Class
> this.perr(this.getDatabaseUrl()); //
> jdbc:postgresql://localhost:5432/sara.pgdb = url
> this.perr(this.getUserID());// chispa = user
> this.perr(this.getUserPassword()); // 8UUKZW = password
>
> Class.forName(this.getDatabaseClass().toString());
> conn =
> DriverManager.getConnection(this.getDatabaseUrl().toString(),
> this.getUserID().toString(),
> this.getUserPassword().toString() );
> } catch (final SQLException | ClassNotFoundException e) {
> throw e;
> }
> }
>
> However, it throws an exception that reads :
> Exception in thread "main" org.postgresql.util.PSQLException: FATAL:
> database "sara.pgdb" does not exist
>
> In reference to the above web page, it reads :
> The following Java code shows how to connect to an existing database. If
> the database does not exist, then it will be created and finally a
> database object will be returned.
>
> True, the database file does not exist, but isn't JDBC supposed to
> create it?
If you are not married to tutorialspoint.com gear, then you can get
the Chinook database for testing. Chinook is the free/open source
alternative to Microsoft's Northwind database. See
https://github.com/lerocha/chinook-database .
Jeff
Re: How to solve the warning?
On Sat, Aug 5, 2023 at 10:10 AM jacktby jacktby wrote: > How to solve the warning? Use an explicit cast, if the types are correct. Jeff
