Re: Migration of Oracle Vault to Postgres

[Laurenz Albe]

On Fri, 2023-09-22 at 11:36 +0800, Elango Thiagarajan wrote:
> We are planning to migrate Oracle Enterprise edition to Azure Postgresql.
> 
> Our oracle database is guarded by Oracle Vault. Do we have similar security 
> on Postgres.
> 
> Any Whitepapers on how to migrate vault to Postgres.

You will have to redesign your application.
The most likely solution is to manage the encryption keys in your application
and encrypt and decrypt the data there.  That way, the DBA cannot access clear
text data.

Of course, that moves the probpem how to keep the keys safe to the application,
which may not be simple either.

Yours,
Laurenz Albe

Re: How to fork pg_dump or psql w/o leaking secrets?

[Luca Ferrari]

On Thu, Sep 21, 2023 at 7:46 PM Dominique Devienne  wrote:
>
> Hi. To administer our PostgreSQL-based system, we have custom tools
> to manage the schemas, load data, etc... Including a versatile CLI tool.
>
> But that tool is special purpose, while sometimes we want/need the general
> purpose PSQL. But because our ROLE and SCHEMA names are rather long
> and unwieldly, it's cumbersome to SET ROLE and SET SEARCH_PATH manually
> to setup the session for easy access to the data. Thus I'd like to fork PSQL 
> from
> our tool, which requires reconnecting to the server (thus possibly "secrets"),
> and I also need to run some PSQL \commands and SQL to config PSQL correctly
> for the context our tool was run with (i.e. our tool's own CLI options).
>

Isn't .psqlrc a possibile approach?

% cat .psqlrc
\echo "Loading configuration"
set search_path to 'luca';



> So how would I:
> 1) "pass secrets" somehow, so they don't leak to the PS command for example. 
> stdin?

I don't get what you mean by secrets, but I guess you have to play
with a wrapper and pass or tools like that.


> 2) configure the forked PSQL in terms of SET ROLE, SET SEARCH_PATH, and 
> possible other \commands?

I would use .psqlrc for every user.

>
> In a very similar vein, I do my own backups using COPY, similar to 
> [pg_dumpbinary][1],
> for the same reasons, but using a different "container" (not several files), 
> for several reasons,
> but I'd like to reuse `pg_dump --pre/post-data` by also forking it, with the 
> save issue #1 above,
> to take care of the DDLs for me, while I handle the DMLs myself.

Again, I don't understand the aim, seems you would obfuscate what you
are doing to others for, ehm, commercial reasons?
However, pg_dump can dump the only schema (--schema-only and friends),
as well as data only. I'm not sure pg_dumpbinary can (is it a
purpose?).
I would not mix and macth the two tools however.

Luca

Re: How to fork pg_dump or psql w/o leaking secrets?

[Luca Ferrari]

On Fri, Sep 22, 2023 at 12:13 PM Dominique Devienne  wrote:
> So my question is how I pass the password my tool already own, to the forked 
> PSQL,
> w/o that password leaking. I could pass it on the command-line, but that 
> would be leaking
> it to the `ps` command (and in various other places).
>

I think that forking a beast like psql will make you incurring into a
lot of security problems that are worst your "password leak".
One solution I could see, could be something like "a poor man captive
psql": write a per-user .pgpass file with the password for the
database (from your tool), write a .psqlrc configuration for the user
and let him to connect to the database. Once done, remove both files
and/or restore previous ones.

Besides, as far as I understand, you are approaching the problem with
a possible wrong design: schemas in PostgreSQL do not provide the
amount of isolation it seems you are trying to achieve with your
persona-to-role-to-schema wrapper.



> That's why I'm asking the community how best to the forked PSQL can connect 
> w/o password prompting.

psql and lipq can exploit .pgpass for exactly that aim: not messing
around with passwords.
Again, I would discourage you to fork psql. Would you be able to
maintain the new upcoming versions in the future?


>> However, pg_dump can dump the only schema (--schema-only and friends),
>> as well as data only. I'm not sure pg_dumpbinary can (is it a
>> purpose?). I would not mix and macth the two tools however.
>
>
>  pg_dumpbinary is a Perl script that uses pg_dump and pg_restore.


Yes, and in fact it does the pre-data and post-data sections, e.g.,
.
However, I'm not sure you will be ble to mix and match a pg_dump
manually obtined schema (in plain text) with pg_restorebinary.
And it is not clear to me, still, the aim of this approach.

Luca

Re: debugger from superuser only.... why?

[Luca Ferrari]

On Wed, Sep 20, 2023 at 9:32 AM Александр Петросян (web)  wrote:
>
> Checked few sources, can not seem to find reasoning behind this limit:
>
> > You must have superuser privileges to use the debugger.
>

It means database superuser.

> What is the reason?

I suspect the debugger will need to open a connection back to pgadmin,
and that probably is the need for privileges.

Luca

Re: debugger from superuser only.... why?

[Luca Ferrari]

On Fri, Sep 22, 2023 at 1:28 PM Alexander Petrossian
 wrote:
> SELECT * FROM pldbg_set_global_breakpoint(1, 65695, -1, NULL);
>
> ERROR: must be a superuser to create a breakpoint
>
>
> I am wondering why is this, why not allow debugging for non-privileged users?

Again, I'm suspecting that this debugger works by performing a
sandboxing and attaching your session back. In order to communicate
back and forth, you probably need such privileges.
You should ask the developers why they need such privileges, or dig
the code by yourself.



> SELECT setting
> FROM pg_settings
> WHERE name = 'shared_preload_libraries';
>

nothing to debug at all in here

> SELECT * FROM (SELECT
>t_namespace.nspname,
>t_extension.extname,
>t_extension.extversion
>FROM pg_extension t_extension
> JOIN pg_namespace t_namespace ON 
> t_extension.extnamespace = t_namespace.oid)q;
>

ditto

> SELECT * FROM (SELECT pid,
>   application_name,
>   usename,
>   client_addr
>FROM pg_stat_activity
>WHERE application_name = 'idea_debugger'
>  AND pid <> pg_backend_pid())q;

nothing to debug in here


> SELECT * FROM pldbg_create_listener();

uh oh, it mentions a listener...then there must be some "sender"
somehwere, that probably needs privileges.

Luca

Re: How to fork pg_dump or psql w/o leaking secrets?

[Dominique Devienne]

On Fri, Sep 22, 2023 at 12:45 PM Luca Ferrari  wrote:

> On Fri, Sep 22, 2023 at 12:13 PM Dominique Devienne 
> wrote:
> > So my question is how I pass the password my tool already own, to the
> forked PSQL,
> > w/o that password leaking. I could pass it on the command-line, but that
> would be leaking
> > it to the `ps` command (and in various other places).
>
> I think that forking a beast like psql will make you incurring into a
> lot of security problems that are worst your "password leak".
>

I'm sorry, but this doesn't make sense to me.
I'm talking of replacing using my tool then psql, with using my tool that
forks psql.
In both cases these are clients apps that connect to a particular DB, for a
given user.
The point if about the best way to not expose the password, if a password
is necessary.


> One solution I could see, could be something like "a poor man captive
> psql": write a per-user .pgpass file with the password for the
> database (from your tool), write a .psqlrc configuration for the user
> and let him to connect to the database. Once done, remove both files
> and/or restore previous ones.
>

I already told you my app is LIBPQ aware, including about PGPASSWORD and
PGSERVICE.
It's users who decide to use these mechanisms (which have plain-text
passwords BTW...), not my tool.
The same way PSQL prompts for a password when not using these mechanism, so
does my tool.

 Besides, as far as I understand, you are approaching the problem with

> a possible wrong design: schemas in PostgreSQL do not provide the
> amount of isolation it seems you are trying to achieve with your
> persona-to-role-to-schema wrapper.
>

I don't know where you are inferring that...
And it seems beside the point anyway, if it was true (and it isn't).


> > That's why I'm asking the community how best to the forked PSQL can
> connect w/o password prompting.
>
> psql and lipq can exploit .pgpass for exactly that aim: not messing
> around with passwords.
>

You are confusing things. I use PGSERVICE and PGPASSWORD myself.
But other users don't. The tool must work either way. And besides, I already
mentioned we have other custom (encrypted store) ways to get passwords too.
So in that case, the password must be communicated to PSQL anyways, somehow.
And the somehow is exactly the point of my questions here.


> Again, I would discourage you to fork psql. Would you be able to
> maintain the new upcoming versions in the future?
>

PSQL is not willy-nilly changing its CLI options or the way it operates.
Plus I bundled PSQL in my package, which is standalone and cross-platforms.
And again, I really don't see what's the harm is forking PSQL versus
running it directly,
albeit having to provide credentials again, exactly what I want to avoid.

 >> However, pg_dump can dump the only schema (--schema-only and friends),

> >> as well as data only. I'm not sure pg_dumpbinary can (is it a
> >> purpose?). I would not mix and macth the two tools however.
> >
> >
> >  pg_dumpbinary is a Perl script that uses pg_dump and pg_restore.
>
> Yes, and in fact it does the pre-data and post-data sections, e.g.,
> .
> However, I'm not sure you will be ble to mix and match a pg_dump
> manually obtined schema (in plain text) with pg_restorebinary.
> And it is not clear to me, still, the aim of this approach.
>

Who says I'd use pg_restorebinary? My tool does custom backup / restore
because:
1) it's a mix of a schema (a "project"), and pieces (subset) of another
schema (meta-data about the project)
2) it's full of bytea values, some quite large.
3) the backup is not opaque, it's an SQLite DB with a 1-to-1 match in terms
of tables and rows with the original schema.
  (but not in terms of columns, the COPY BINARY bytes of the row as dumped
as-is; I also use the same integer PK if any)
  (and in some cases, I even [expose virtual columns][1] in SQLite for
"interesting" (i.e. NKs) columns of the COPY BINARY bytes)

The DML parts using COPY BINARY are already working just fine, thank you.

And it yields a 1-file backup per "project" schema (+ meta-data) that you
can easily introspect in any SQLite tool.
Most of the rows are opaque, but tables, rows, integer PKs, and even some
text NKs and FKs (parent-child)
are visible from SQLite, making it possible to partially restore some
subset, or get row stats about the "project", etc...
I even have views which know about the schema structure, to project even
more insights about the backup content.
It's so convenient, I wonder why it doesn't exist already. I'm not the only
SQLite + PostgreSQL dev, after all, right.
OTOH, I do tend to think out-of-the-box...

Now I want to add capturing the DDLs for the "project" schema at backup
time,
so I don't have to pre-create the "project" schema using my own tool,
before restoring it.
I want to capture the schema exactly as it was, recreate the schema using
the captured DDLs,
and the use my tool as usual to ch

Re: How to fork pg_dump or psql w/o leaking secrets?

[Dominique Devienne]

On Fri, Sep 22, 2023 at 10:56 AM Luca Ferrari  wrote:

> On Thu, Sep 21, 2023 at 7:46 PM Dominique Devienne 
> wrote:
> > and I also need to run some PSQL \commands and SQL to config PSQL
> correctly
> > for the context our tool was run with (i.e. our tool's own CLI options).
> >
>
> Isn't .psqlrc a possibile approach?
>
> % cat .psqlrc
> \echo "Loading configuration"
> set search_path to 'luca';
>

I see. I don't want to mess with user's .pqsqlrc, but from
https://www.postgresql.org/docs/current/app-psql.html
I can use PSQLRC to override the default location.

Or I can use `-f filename` or `-f -` to provide them independently of the
.psqlrc,
with or without `--no-psqlrc`.

So that takes care of configuring PSQL, thanks.

> So how would I:
> > 1) "pass secrets" somehow, so they don't leak to the PS command for
> example. stdin?
>
> I don't get what you mean by secrets, but I guess you have to play
> with a wrapper and pass or tools like that.
>

Well, simply the password for example. The user connects to PostgreSQL
using our tool,
possibly prompting for the password, if PQconnectionNeedsPassword() returns
true.
We translate project names and "persona" into actual ROLE and SCHEMA names,
check
permissions, check schema versions, that sort of things. Then "fork" PSQL,
which must
reconnect to the same DB for the same USER, but ideally w/o having PSQL
prompting
for the password again. It should be seamless. Especially since we have
alternate ways
to get the password from, beside prompting and PGSERVICE and PGPASSWORD.

So my question is how I pass the password my tool already own, to the
forked PSQL,
w/o that password leaking. I could pass it on the command-line, but that
would be leaking
it to the `ps` command (and in various other places).

I guess I could create a temp file with the connection info, and set a
PGSERVICE env-var
for PSQL, but then the password in in-clear in a file. Again, that's
leaking the password, in a way.

Another alternative would be to read what PSQL outputs, and look for it
asking for a password,
and provide it, but that seems brittle, requires `pipe` (not
cross-platform).

That's why I'm asking the community how best to the forked PSQL can connect
w/o password prompting.


> > In a very similar vein, I do my own backups using COPY, similar to
> [pg_dumpbinary][1],
> > for the same reasons, but using a different "container" (not several
> files), for several reasons,
> > but I'd like to reuse `pg_dump --pre/post-data` by also forking it, with
> the save issue #1 above,
> > to take care of the DDLs for me, while I handle the DMLs myself.
>
> Again, I don't understand the aim, seems you would obfuscate what you
> are doing to others for, ehm, commercial reasons?
>

Not really no. It's actually to make the backup more introspectable in fact.
But that's beside the point, no?


> However, pg_dump can dump the only schema (--schema-only and friends),
> as well as data only. I'm not sure pg_dumpbinary can (is it a
> purpose?). I would not mix and macth the two tools however.
>

 pg_dumpbinary is a Perl script that uses pg_dump and pg_restore.

Re: How to fork pg_dump or psql w/o leaking secrets?

[Francisco Olarte]

On Fri, 22 Sept 2023 at 15:25, Dominique Devienne  wrote:
> On Fri, Sep 22, 2023 at 12:45 PM Luca Ferrari  wrote:
...
>> I think that forking a beast like psql will make you incurring into a
>> lot of security problems that are worst your "password leak".
> I'm sorry, but this doesn't make sense to me.
> I'm talking of replacing using my tool then psql, with using my tool that 
> forks psql.
> In both cases these are clients apps that connect to a particular DB, for a 
> given user.

It appears you use fork to mean "fork & exec", the classic way to
start a slave ( controlled? helper ? I do not know the modern PC word
for that ) process on *ix, while Luca is thinking on a source fork,
the thing which is normally done in git and friends to have a similar
but derived software built.

Francisco Olarte.

Re: debugger from superuser only.... why?

[Luca Ferrari]

On Fri, Sep 22, 2023 at 2:00 PM Alexander Petrossian (PAF)
 wrote:
> Some mail list you would suggest, Luka?
>

pgadmin mailing list, or ask EDB somehwere.

>>
>> > SELECT * FROM pldbg_create_listener();
>> uh oh, it mentions a listener...then there must be some "sender"
>> somehwere, that probably needs privileges.
>
>
> Surprisingly this passes OK under non-privileged user.

I meant: somewhere else there must be something that connects back and
requires privileges.

Also, this (not tried) does not seem to require other privileges
.

Luca

How to fork pg_dump or psql w/o leaking secrets?

[David G. Johnston]

On Friday, September 22, 2023, Luca Ferrari  wrote:

>
> > That's why I'm asking the community how best to the forked PSQL can
> connect w/o password prompting.
>
> psql and lipq can exploit .pgpass for exactly that aim: not messing
> around with passwords.
> Again, I would discourage you to fork psql. Would you be able to
> maintain the new upcoming versions in the future?
>
>
The OP seems to be used the term fork in a process sense, not forking the
source code.  Process execution from within another program.  “Launch” or
“execute” psql would be a better choice of wording here.

David J.

Re: How to fork pg_dump or psql w/o leaking secrets?

[Dominique Devienne]

On Fri, Sep 22, 2023 at 5:19 PM Luca Ferrari  wrote:

> On Fri, Sep 22, 2023 at 3:24 PM Dominique Devienne 
> wrote:
> You added information I was not aware before: you are dumping
> PostgreSQL to restore it into SQLite, while I was thinking you wanted
> to do some stuff with a PostgreSQL-to-PostgreSQL backup and restore.
>

I must be really bad at explaining things today, sorry...

Because of course I'm doing PostgreSQL-to-PostgreSQL backup/restore.
The fact I use a custom SQLite DB file as the backup's "format" instead of
a "pile-of-files" (or a TAR of that pile), is the only difference.

That, and the fact it's a backup that mixes one full schema with part of
another.
And that my SQLite backup format is much more convenient and "expressive"
IMHO,
since fully introspectable ("semi-opaque", since rows are still COPY BINARY
encoded).

Writing the backup as an SQLite DB incurs a little overhead, of course,
compared to just appending to per-table files, but not that much when
in non-transactional mode. Acceptable tradeoff compared to the fact one
can open the backup easily and see the tables and rows almost as-is,
which also opens the door to partial restores (there are separates "roots",
independent entity trees, in these schemas).

Re: How to fork pg_dump or psql w/o leaking secrets?

[Brad White]

Seems to me that your tool could set the env var that you want.
If you don't export it, I think it shouldn't "leak" but your child process
should get it as part of their environment.

On Fri, Sep 22, 2023 at 12:43 PM Dominique Devienne 
wrote:

> On Fri, Sep 22, 2023 at 5:19 PM Luca Ferrari  wrote:
>
>> On Fri, Sep 22, 2023 at 3:24 PM Dominique Devienne 
>> wrote:
>> You added information I was not aware before: you are dumping
>> PostgreSQL to restore it into SQLite, while I was thinking you wanted
>> to do some stuff with a PostgreSQL-to-PostgreSQL backup and restore.
>>
>
> I must be really bad at explaining things today, sorry...
>
> Because of course I'm doing PostgreSQL-to-PostgreSQL backup/restore.
> The fact I use a custom SQLite DB file as the backup's "format" instead of
> a "pile-of-files" (or a TAR of that pile), is the only difference.
>
> That, and the fact it's a backup that mixes one full schema with part of
> another.
> And that my SQLite backup format is much more convenient and "expressive"
> IMHO,
> since fully introspectable ("semi-opaque", since rows are still COPY
> BINARY encoded).
>
> Writing the backup as an SQLite DB incurs a little overhead, of course,
> compared to just appending to per-table files, but not that much when
> in non-transactional mode. Acceptable tradeoff compared to the fact one
> can open the backup easily and see the tables and rows almost as-is,
> which also opens the door to partial restores (there are separates "roots",
> independent entity trees, in these schemas).
>

Re: How to fork pg_dump or psql w/o leaking secrets?

[David G. Johnston]

On Friday, September 22, 2023, Dominique Devienne 
wrote:
>
>
> I already told you my app is LIBPQ aware, including about PGPASSWORD and
> PGSERVICE.
> It's users who decide to use these mechanisms (which have plain-text
> passwords BTW...), not my tool.
> The same way PSQL prompts for a password when not using these mechanism,
> so does my tool.
>

Once you have the password you should utilize the PGPASSWORD environment
variable to get it passed to psql.  It doesn’t matter in the least how you
obtained that password in the first place.

David J.

Start service

[Brad White]

I'm trying to start a v15 service on a Windows 2012 R2 server where it
hasn't been used for a while.
The service is set to run as pgUser.
pgUser owns the Postgres directory, including the data dir.

The command the service is using is
' "C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" runservice -N
"postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w'

First off, it's interesting that "runservice" isn't listed as an option for
pg_ctl.

Windows Event Viewer shows it starting up, handing off logging to pg_log,
then timing out.
 Waiting for server startup...
 2023-09-22 12:31:15.173 CDT [5000] LOG:  redirecting log output to
logging collector process
 2023-09-22 12:31:15.173 CDT [5000] HINT:  Future log output will
appear in directory "pg_log".
 Timed out waiting for server startup

It does create a log file in pg_log, but it is empty.

If I sub 'start' for 'runservice' and try to start it from CLI, I get
 C:\Users\administrator>"C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe"
start
 -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w
 waiting for server to start2023-09-22 12:48:05.438 CDT [4796]
FATAL:  could
not create lock file "postmaster.pid": Permission denied
 stopped waiting
 pg_ctl: could not start server
 Examine the log output.

And again, I get an empty log file in pg_log.
Is there somewhere else I should check?

I get the same results if I pass username and password with -U and -P

I'm running as administrator, so not sure how I get permission denied
Checking EffectiveAccess shows that admin and pgUser have full control, so
I assume this message is a red herring, standing in for the real issue.

Without an actual error message to fix, I'm not sure how to proceed.

Thanks,
Brad.

Re: How to fork pg_dump or psql w/o leaking secrets?

[Luca Ferrari]

On Fri, Sep 22, 2023 at 3:24 PM Dominique Devienne  wrote:
> I'm talking of replacing using my tool then psql, with using my tool that 
> forks psql.

I thought you were talking about forking the source code to write your
own psql, sorry about that.
It is clear to me that you mean Unix fork(2) and exec(2).


> The point if about the best way to not expose the password, if a password is 
> necessary.

As I understand the point, which is apparently really hard for me, one
way is using .pgpass (for example adding/removing lines from your
tools, that is effectively a psql wrapper in this sense).


>
> I already told you my app is LIBPQ aware, including about PGPASSWORD and 
> PGSERVICE.
> It's users who decide to use these mechanisms (which have plain-text 
> passwords BTW...), not my tool.

It cannot be your tool, it is the database that asks for a password if
needed (i.e., pg_hba.conf).
Therefore, if your tool is PGPASSWORD aware, it can handle dynamic
lines in .pgpass, which apparently is the simplest and most secure way
to avoid other users spoofing on passwords using ps(1) and friends.
Or pass the environment variable as it is: you are already risking at
the entry point of your tool.

> So in that case, the password must be communicated to PSQL anyways, somehow.
> And the somehow is exactly the point of my questions here.

The options are what you already have mentioned: environment
variables, stdin, and .pgpass.
I'm not aware of any other method, so I thjnk the decision is up to you.


> PSQL is not willy-nilly changing its CLI options or the way it operates.
> Plus I bundled PSQL in my package, which is standalone and cross-platforms.
> And again, I really don't see what's the harm is forking PSQL versus running 
> it directly,

I was referring to source code forking, not fork(2).

> And instead of re-inventing the wheel (in my tool) to capture DDLs, reusing 
> pg_dump
> makes complete sense to me.
>

You added information I was not aware before: you are dumping
PostgreSQL to restore it into SQLite, while I was thinking you wanted
to do some stuff with a PostgreSQL-to-PostgreSQL backup and restore.
That is why I was suggesting to stick with a tool and not to mix and
match.
Apparently you have already what you need, or quite frankly I'm not
getting your actual need.


> That's not the kind of answers (or questions / comments) I was expecting, 
> from this ML.

Sorry about that, hope you will find the correct answer.

Luca

Re: How to fork pg_dump or psql w/o leaking secrets?

[Tom Lane]

"David G. Johnston"  writes:
> Once you have the password you should utilize the PGPASSWORD environment
> variable to get it passed to psql.  It doesn’t matter in the least how you
> obtained that password in the first place.

Keep in mind that on many flavors of Unix, a process's environment
variables can readily be inspected by other processes.  You should
check your platform carefully before assuming that PGPASSWORD is
a safe way to pass down a secret.

regards, tom lane

Re: Start service

[Brad White]

Good guess, but no. I should have mentioned that I checked that.

There is, though, a postmaster.opts file that wasn't there earlier. It
contains
  C:/Program Files/PostgreSQL/15/bin/postgres.exe  "-D"  "C:\Program
Files\PostgreSQL\15\data"
Note the lack of quotes around the exe path.

On Fri, Sep 22, 2023 at 1:48 PM Nick Ivanov 
wrote:

> I'd check if there is already "postmaster.pid" in C:\Program
> Files\PostgreSQL\15\data, left over from a previous abend.
>
> On Fri, Sep 22, 2023 at 2:40 PM Brad White  wrote:
>
>> I'm trying to start a v15 service on a Windows 2012 R2 server where it
>> hasn't been used for a while.
>> The service is set to run as pgUser.
>> ...
>> If I sub 'start' for 'runservice' and try to start it from CLI, I get
>>  C:\Users\administrator>"C:\Program
>> Files\PostgreSQL\15\bin\pg_ctl.exe" start
>>  -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w
>>  waiting for server to start2023-09-22 12:48:05.438 CDT [4796]
>> FATAL:  could
>> not create lock file "postmaster.pid": Permission denied
>>
>
> --
> Nick Ivanov
> Solution Architect
> www.enterprisedb.com
>

Re: debugger from superuser only.... why?

[postgresql439848]

hi,
the first steps to use debugger:
1. modify config file
2. restart server
...
i would say, enough reasons to be superuser or not?

Re: Start service

[Brad White]

I created the pid file by hand and I get
  C:\Users\administrator>"C:\Program
Files\PostgreSQL\15\bin\pg_ctl.exe" start
 -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w -U
TMA\pgUse
r -P ***
   pg_ctl: another server might be running; trying to start server
anyway
   waiting for server to start..2023-09-22 14:31:17.752 CDT
[4276] FATA
L:  could not remove old lock file "postmaster.pid": Permission denied
   2023-09-22 14:31:17.752 CDT [4276] HINT:  The file seems
accidentally left over,
 but it could not be removed. Please remove the file by hand and try again.
  stopped waiting
  pg_ctl: could not start server
  Examine the log output.

Remove the file and we are back where we started.
But a) we are looking at the right dir, and b) it looks like it might
really be a permissions issue somehow.

On Fri, Sep 22, 2023 at 1:56 PM Brad White  wrote:

> Good guess, but no. I should have mentioned that I checked that.
>
> There is, though, a postmaster.opts file that wasn't there earlier. It
> contains
>   C:/Program Files/PostgreSQL/15/bin/postgres.exe  "-D"  "C:\Program
> Files\PostgreSQL\15\data"
> Note the lack of quotes around the exe path.
>
> On Fri, Sep 22, 2023 at 1:48 PM Nick Ivanov 
> wrote:
>
>> I'd check if there is already "postmaster.pid" in C:\Program
>> Files\PostgreSQL\15\data, left over from a previous abend.
>>
>> On Fri, Sep 22, 2023 at 2:40 PM Brad White  wrote:
>>
>>> I'm trying to start a v15 service on a Windows 2012 R2 server where it
>>> hasn't been used for a while.
>>> The service is set to run as pgUser.
>>> ...
>>> If I sub 'start' for 'runservice' and try to start it from CLI, I get
>>>  C:\Users\administrator>"C:\Program
>>> Files\PostgreSQL\15\bin\pg_ctl.exe" start
>>>  -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w
>>>  waiting for server to start2023-09-22 12:48:05.438 CDT [4796]
>>> FATAL:  could
>>> not create lock file "postmaster.pid": Permission denied
>>>
>>
>> --
>> Nick Ivanov
>> Solution Architect
>> www.enterprisedb.com
>>
>

Re: Start service

[postgresql439848]

Am 22.09.23 um 20:40 schrieb Brad White:

I'm trying to start a v15 service on a Windows 2012 R2 server where it
hasn't been used for a while.
The service is set to run as pgUser.
pgUser owns the Postgres directory, including the data dir.

The command the service is using is
 ' "C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" runservice -N
"postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w'

First off, it's interesting that "runservice" isn't listed as an option for
pg_ctl.

Windows Event Viewer shows it starting up, handing off logging to pg_log,
then timing out.
  Waiting for server startup...
  2023-09-22 12:31:15.173 CDT [5000] LOG:  redirecting log output to
logging collector process
  2023-09-22 12:31:15.173 CDT [5000] HINT:  Future log output will
appear in directory "pg_log".
  Timed out waiting for server startup

It does create a log file in pg_log, but it is empty.

If I sub 'start' for 'runservice' and try to start it from CLI, I get
  C:\Users\administrator>"C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe"
start
  -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w
  waiting for server to start2023-09-22 12:48:05.438 CDT [4796]
FATAL:  could
not create lock file "postmaster.pid": Permission denied
  stopped waiting
  pg_ctl: could not start server
  Examine the log output.

And again, I get an empty log file in pg_log.
Is there somewhere else I should check?

I get the same results if I pass username and password with -U and -P

I'm running as administrator, so not sure how I get permission denied
Checking EffectiveAccess shows that admin and pgUser have full control, so
I assume this message is a red herring, standing in for the real issue.

Without an actual error message to fix, I'm not sure how to proceed.

Thanks,
Brad.


if you want to run postgresql server then you could use "pg_ctl start ..."
but to run it as a windows service you first need to register it as a 
service. so look at pg_ctl register in the options.

StackBuilder unexpected restart

[russcampbell]

I downloaded and installed Postgres 15.4 and then let StackBuilder run. All
I installed was pgAgent and the four drivers. The software notified me that
I should choose "Restart Later" or "No" if I was asked about restarting.
Instead of getting that type of question along the way, I got no such
questions. Fine. Figured the software needing to be installed didn't need a
reboot (why would drivers need that, after all?). But, without any warning,
the installation rebooted my server. Just all of a sudden - bam!. No
warning. This was on a production server. Now, I did this because 1) I was
led to believe that there would be no forced restart and 2) the transaction
rate on this server is low and, frankly, a reboot like this is unlikely to
cause any problems for this server. However, there's no excuse for
unannounced reboots. It should always involve user permission except in a
silent install.

 

Why is this software allowed to do this?! In advance, let me say I hope I
don't get any "you shouldn't have done this on a server during business
hours." Well, 1) I've already explained why, and 2) developers should never
use that to shirk responsibility. Reboots should always be authorized by the
user. If a mandatory reboot is required at the exact moment of installation
for some reason, that should be disclosed very clearly at the outset.

Re: Start service

[Brad White]

So, there were two issues.
Even though admin is in the admin group and had Full Control under
effective permissions, somehow that wasn't enough.
I noticed that I can run the v14 service, but it had Admin explicitly
listed with full control on Data.
Once I added that, I started getting error messages when run from CLI and
could fix the issue in pg_hba.conf.
Now the service starts.

Thanks,
Brad.

On Fri, Sep 22, 2023 at 2:43 PM  wrote:

> Am 22.09.23 um 20:40 schrieb Brad White:
> > I'm trying to start a v15 service on a Windows 2012 R2 server where it
> > hasn't been used for a while.
> > The service is set to run as pgUser.
> > pgUser owns the Postgres directory, including the data dir.
> >
> > The command the service is using is
> >  ' "C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" runservice -N
> > "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w'
> >
> > First off, it's interesting that "runservice" isn't listed as an option
> for
> > pg_ctl.
> >
> > Windows Event Viewer shows it starting up, handing off logging to pg_log,
> > then timing out.
> >   Waiting for server startup...
> >   2023-09-22 12:31:15.173 CDT [5000] LOG:  redirecting log output to
> > logging collector process
> >   2023-09-22 12:31:15.173 CDT [5000] HINT:  Future log output will
> > appear in directory "pg_log".
> >   Timed out waiting for server startup
> >
> > It does create a log file in pg_log, but it is empty.
> >
> > If I sub 'start' for 'runservice' and try to start it from CLI, I get
> >   C:\Users\administrator>"C:\Program
> Files\PostgreSQL\15\bin\pg_ctl.exe"
> > start
> >   -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w
> >   waiting for server to start2023-09-22 12:48:05.438 CDT [4796]
> > FATAL:  could
> > not create lock file "postmaster.pid": Permission denied
> >   stopped waiting
> >   pg_ctl: could not start server
> >   Examine the log output.
> >
> > And again, I get an empty log file in pg_log.
> > Is there somewhere else I should check?
> >
> > I get the same results if I pass username and password with -U and -P
> >
> > I'm running as administrator, so not sure how I get permission denied
> > Checking EffectiveAccess shows that admin and pgUser have full control,
> so
> > I assume this message is a red herring, standing in for the real issue.
> >
> > Without an actual error message to fix, I'm not sure how to proceed.
> >
> > Thanks,
> > Brad.
> >
> if you want to run postgresql server then you could use "pg_ctl start ..."
> but to run it as a windows service you first need to register it as a
> service. so look at pg_ctl register in the options.
>
>
>

Re: Start service

[Nick Ivanov]

I'd check if there is already "postmaster.pid" in C:\Program
Files\PostgreSQL\15\data, left over from a previous abend.

On Fri, Sep 22, 2023 at 2:40 PM Brad White  wrote:

> I'm trying to start a v15 service on a Windows 2012 R2 server where it
> hasn't been used for a while.
> The service is set to run as pgUser.
> ...
> If I sub 'start' for 'runservice' and try to start it from CLI, I get
>  C:\Users\administrator>"C:\Program
> Files\PostgreSQL\15\bin\pg_ctl.exe" start
>  -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w
>  waiting for server to start2023-09-22 12:48:05.438 CDT [4796]
> FATAL:  could
> not create lock file "postmaster.pid": Permission denied
>

-- 
Nick Ivanov
Solution Architect
www.enterprisedb.com

Re: How to fork pg_dump or psql w/o leaking secrets?

[Dominique Devienne]

On Fri, Sep 22, 2023 at 8:56 PM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > Once you have the password you should utilize the PGPASSWORD environment
> > variable to get it passed to psql.  It doesn’t matter in the least how
> you
> > obtained that password in the first place.
>
> Keep in mind that on many flavors of Unix, a process's environment
> variables can readily be inspected by other processes.  You should
> check your platform carefully before assuming that PGPASSWORD is
> a safe way to pass down a secret.
>

Yep. From https://www.postgresql.org/docs/current/libpq-envars.html :
> PGPASSWORD behaves the same as the password connection parameter.
> Use of this environment variable is not recommended for security reasons,
> as some operating systems allow non-root users to see process environment
> variables via ps; instead consider using a password file (see Section
34.16).

but I'm not a fan of creating a temporary file either, with the password in
plain text...

Remember that I'm already connected in the "parent" process, to the DB.
There aught to be a way to obtain a token from the DB via a connection,
with a short duration, to supply to the exec'd PostgreSQL tools like psql
or pg_dump,
to completely bypass passwords. The server would maintain per-DB secrets,
and sign a JWT token for example, valid for a few seconds, for that user/DB
pair,
that the parent "process" could then utilize / pass to the "fork/exec"d
tool.

Much safer than plain-text passwords floating around env-vars or
temp-files. --DD

Ubuntu 18 + PHP 8.2 + PDO: can't find drivers

[Chris Kelly]

The need has mostly passed (I used another computer with Ubuntu 20) but are 
there clear, *working* instructions for how to connect to Postgress on Ubuntu 
18 via PDO? I don't see drivers that would work. This is for a Drupal site.

Re: Ubuntu 18 + PHP 8.2 + PDO: can't find drivers

[Adrian Klaver]

On 9/22/23 13:39, Chris Kelly wrote:

The need has mostly passed (I used another computer with Ubuntu 20) but are 
there clear, *working* instructions for how to connect to Postgress on Ubuntu 
18 via PDO? I don't see drivers that would work. This is for a Drupal site.


1) I'm assuming that when you refer to Ubuntu 18 and 20 you mean 18.04 
LTS and 20.04 LTS. Is that correct?


2) What Postgres version?

3) What did you try?


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

Re: How to fork pg_dump or psql w/o leaking secrets?

[David G. Johnston]

On Friday, September 22, 2023, Dominique Devienne 
wrote:

>
> Remember that I'm already connected in the "parent" process, to the DB.
> There aught to be a way to obtain a token from the DB via a connection,
> with a short duration, to supply to the exec'd PostgreSQL tools like psql
> or pg_dump,
> to completely bypass passwords. The server would maintain per-DB secrets,
> and sign a JWT token for example, valid for a few seconds, for that
> user/DB pair,
> that the parent "process" could then utilize / pass to the "fork/exec"d
> tool.
>
> Much safer than plain-text passwords floating around env-vars or
> temp-files. --DD
>

Sure, though maybe just some kind of “—password-on-stdin” option and then
the next input read from stdin is interpreted as the password, would be
more readily accomplished.  Scripts should be sent via “—file” in that
usage but that seems desirable anyway.

David J.

Re: Ubuntu 18 + PHP 8.2 + PDO: can't find drivers

[Ray O'Donnell]

On 22 September 2023 21:40:38 Chris Kelly  wrote:

The need has mostly passed (I used another computer with Ubuntu 20) but are 
there clear, *working* instructions for how to connect to Postgress on 
Ubuntu 18 via PDO? I don't see drivers that would work. This is for a 
Drupal site.


From memory (not at my laptop now) you need to install the php8.2-pgsql 
package (or maybe it's php8.2-pdo-pgsql). It should just work then... 
Drupal will notice the driver and offer Postgresql as an option on 
installation. Don't forget to restart Apache after installing that package


Ray.

Re: Ubuntu 18 + PHP 8.2 + PDO: can't find drivers

[Ray O'Donnell]

On 22 September 2023 21:40:38 Chris Kelly  wrote:

The need has mostly passed (I used another computer with Ubuntu 20) but are 
there clear, *working* instructions for how to connect to Postgress on 
Ubuntu 18 via PDO? I don't see drivers that would work. This is for a 
Drupal site.


PS - depending on your Ubuntu version, you might need to take packages from 
deb.sury.org in order to get PHP 8.2. I use them all the time, they're very 
good.


Ray

Re: connecting to new instance

[Alan Hodgson]

On Fri, 2023-09-22 at 17:08 -0500, Brad White wrote:
> I have the v15 service started and listening on 0.0.0.0:5434.
> Through TCPView, I can see it listening on 5434, I can see the
> previous version listening and connecting on 5432.
> I can connect from localhost to port 5434.
> I have ipv6 turned off in the network settings on both machines.
> From any other client or server, I get
> "Are you sure the server is running on 192.168.1.112 and accepting
> connections on 5434?
> The relevant line in pg_hba.conf looks like:
> host    all             all             192.168.1.0/24          
> password
> 
> I can't see what else I'm missing.

Probably the host firewall.

Re: connecting to new instance

[Adrian Klaver]

On 9/22/23 15:08, Brad White wrote:
I have the v15 service started and listening on 0.0.0.0:5434 
.
Through TCPView, I can see it listening on 5434, I can see the previous 
version listening and connecting on 5432.

I can connect from localhost to port 5434.
I have ipv6 turned off in the network settings on both machines.
 From any other client or server, I get


Is the above across a network?

If so is there a firewall and is it blocking 5434?

"Are you sure the server is running on 192.168.1.112 and accepting 
connections on 5434?

The relevant line in pg_hba.conf looks like:
host    all             all 192.168.1.0/24    
     password


I can't see what else I'm missing.


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

connecting to new instance

[Brad White]

I have the v15 service started and listening on 0.0.0.0:5434.
Through TCPView, I can see it listening on 5434, I can see the previous
version listening and connecting on 5432.
I can connect from localhost to port 5434.
I have ipv6 turned off in the network settings on both machines.
>From any other client or server, I get
"Are you sure the server is running on 192.168.1.112 and accepting
connections on 5434?
The relevant line in pg_hba.conf looks like:
hostall all 192.168.1.0/24   password

I can't see what else I'm missing.

Re: Changed functionality from 14.3 to 15.3

[Michael Corey]

I created a clean 14.3 server with everything default on server creation.
Ran the setup script did the test and again I was able to query the data
successfully.  I then decided to create a clean 15.3 server with everything
default.  Ran the setup script did the test and was not able to query the
data.

Interestingly enough I contacted AWS and presented the same issue to them
and they informed me that they could duplicate my exact issue, and said yes
there was a functionality change from 14 to 15, but they did not say if the
change was something they did with their RDS Postgres or was it something
changed in the underlying Postgres build.



On Wed, Sep 20, 2023 at 7:11 PM Erik Wienhold  wrote:

> On 2023-09-20 17:53 -0400, Michael Corey wrote:
> > To make matters even more strange.  I checked the permissions of
> > rds_superuser in 15 and 14
> >
> > For 14
> > GRANT pg_monitor, pg_signal_backend, rds_password, rds_replication TO
> > rds_superuser WITH ADMIN OPTION;
> >
> > For 15
> > GRANT pg_checkpoint, pg_monitor, *pg_read_all_data*, pg_signal_backend,
> > *pg_write_all_data*, rds_password, rds_replication TO rds_superuser WITH
> > ADMIN OPTION;
> >
> > AWS added these permissions, but based on what they do you would think
> this
> > would allow the SELECTs in 15.
>
> Yes it would if sten_schema would inherit from rds_superuser.  But it
> cannot inherit privileges from rds_superuser (indrect membership through
> object_creator) because object_creator was created with NOINHERIT.  And
> INHERIT applies to direct memberships only.
>
> --
> Erik
>


-- 
Michael Corey