Log file

I initially sent this to the ODBC list.'

Thank you for any suggestions.

---------- Forwarded message ---------
From: Igor Korot <ikorot01@gmail.com>
Date: Sun, Oct 28, 2018 at 11:03 PM
Subject: Log file
To: PostgreSQL ODBC list <pgsql-odbc@postgresql.org>

Hi, ALL,
I'm trying to test the functionality of logging on my older Mac with
PostgreSQL 9.1.

I see that the logile is created with the owner of postgres and the
group of wheel.

Is there a way to make it open with "<current_user><current_user>"?
Or I will have to change the owner/group manuall every time I will
access the file?

Thank you.

Igor Korot <ikorot01@gmail.com> writes:

I'm trying to test the functionality of logging on my older Mac with
PostgreSQL 9.1.
I see that the logile is created with the owner of postgres and the
group of wheel.

Well, more specifically, it's created under the OS user & group that
the server is running under.

Is there a way to make it open with "<current_user><current_user>"?

What current user? The SQL user name might not correspond to any
OS-level entity at all. Even if it did, it's quite unlikely that
the OS would permit the server process to create files owned by
some other OS user --- doing so would be a giant security risk.

Or I will have to change the owner/group manuall every time I will
access the file?

You can set up the log files as readable by the OS group of the server
(see log_file_mode), and then grant membership in that group to whichever
OS accounts you trust. You may also need to move the log directory
out from under $PGDATA to make that work, since PG doesn't like
world-readable data directories.

regards, tom lane

Hi, Tom,

On Mon, Oct 29, 2018 at 1:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Igor Korot <ikorot01@gmail.com> writes:

I'm trying to test the functionality of logging on my older Mac with
PostgreSQL 9.1.
I see that the logile is created with the owner of postgres and the
group of wheel.

Well, more specifically, it's created under the OS user & group that
the server is running under.

OK, that clarifies it a little.
I thought I could just crate a user called "igor", give him all
"postgres" permissions
and login to the server as "igor" and not "postgres" every time I test.
But since the server will probably run from the "postgres" account
during the machine
start-up that won't work.

Is there a way to make it open with "<current_user><current_user>"?

What current user? The SQL user name might not correspond to any
OS-level entity at all. Even if it did, it's quite unlikely that
the OS would permit the server process to create files owned by
some other OS user --- doing so would be a giant security risk.

The current_user = user who logged in to the machine and open the
current session.
If I log in to the machine as "igor" and try to create a file in vi/nano/notepad
I will be the owner of this file and the group will be the group to which
I belong as a user.

And I'm talking about specifically to the result of the "ls -la"
output from the *nix/OSX
POV.

Or I will have to change the owner/group manuall every time I will
access the file?

You can set up the log files as readable by the OS group of the server
(see log_file_mode), and then grant membership in that group to whichever
OS accounts you trust. You may also need to move the log directory
out from under $PGDATA to make that work, since PG doesn't like
world-readable data directories.

I'm trying to make the log file of PG readable of the user who logs in
to the current
OS session. I don't need a write permission, just read.
Because my program will not be started from the "postgres" account.

Thank you.

Igor Korot <ikorot01@gmail.com> writes:

On Mon, Oct 29, 2018 at 1:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

You can set up the log files as readable by the OS group of the server
(see log_file_mode), and then grant membership in that group to whichever
OS accounts you trust. You may also need to move the log directory
out from under $PGDATA to make that work, since PG doesn't like
world-readable data directories.

I'm trying to make the log file of PG readable of the user who logs in
to the current
OS session. I don't need a write permission, just read.
Because my program will not be started from the "postgres" account.

Well, any such setup is a serious security hole in itself, because
there is likely to be sensitive data in the postmaster log, eg
passwords. (Remember that the log file is global to the whole cluster,
it will not contain just data relevant to the current session.)
You should only grant access to people who you trust at more or less
the level of trust you'd put in the installation DBA.

It may be that these concerns are all irrelevant to you because it's
a single-user installation anyway, but they're not irrelevant to
people running multi-user installations. So that's why you can't
get Postgres to do it. In a single-user installation, maybe you
should just launch the postmaster as that user.

regards, tom lane

Now is there a command to flush the log - delete the content of it?

All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.

Hi, Tom,

On Mon, Oct 29, 2018 at 5:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Igor Korot <ikorot01@gmail.com> writes:

On Mon, Oct 29, 2018 at 1:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

You can set up the log files as readable by the OS group of the server
(see log_file_mode), and then grant membership in that group to whichever
OS accounts you trust. You may also need to move the log directory
out from under $PGDATA to make that work, since PG doesn't like
world-readable data directories.

I'm trying to make the log file of PG readable of the user who logs in
to the current
OS session. I don't need a write permission, just read.
Because my program will not be started from the "postgres" account.

Well, any such setup is a serious security hole in itself, because
there is likely to be sensitive data in the postmaster log, eg
passwords. (Remember that the log file is global to the whole cluster,
it will not contain just data relevant to the current session.)
You should only grant access to people who you trust at more or less
the level of trust you'd put in the installation DBA.

It may be that these concerns are all irrelevant to you because it's
a single-user installation anyway, but they're not irrelevant to
people running multi-user installations. So that's why you can't
get Postgres to do it. In a single-user installation, maybe you
should just launch the postmaster as that user.

regards, tom lane

OK, I understand.

Thank you.

Igor Korot wrote:

Now is there a command to flush the log - delete the content of it?

No, managing the logs is outside of PostgreSQL's responsibility.

But it shouldn't be a problem to do this outside the database.
Of course you could write a funtion in PostgreSQL that uses one
of the "untrusted" procedural languages to do it for you.

All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.

You mean DROP, right?

You can set "log_statement = 'ddl'" for that.

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

On 10/30/18 9:20 AM, Igor Korot wrote:

Now is there a command to flush the log - delete the content of it?

The only thing I know of is:

https://www.postgresql.org/docs/10/static/functions-admin.html

pg_rotate_logfile() boolean Rotate server's log file

All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.

On Wed, Oct 31, 2018 at 12:32 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi, Tom,

On Mon, Oct 29, 2018 at 5:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Igor Korot <ikorot01@gmail.com> writes:

On Mon, Oct 29, 2018 at 1:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

You can set up the log files as readable by the OS group of the server
(see log_file_mode), and then grant membership in that group to whichever
OS accounts you trust. You may also need to move the log directory
out from under $PGDATA to make that work, since PG doesn't like
world-readable data directories.

I'm trying to make the log file of PG readable of the user who logs in
to the current
OS session. I don't need a write permission, just read.
Because my program will not be started from the "postgres" account.

Well, any such setup is a serious security hole in itself, because
there is likely to be sensitive data in the postmaster log, eg
passwords. (Remember that the log file is global to the whole cluster,
it will not contain just data relevant to the current session.)
You should only grant access to people who you trust at more or less
the level of trust you'd put in the installation DBA.

It may be that these concerns are all irrelevant to you because it's
a single-user installation anyway, but they're not irrelevant to
people running multi-user installations. So that's why you can't
get Postgres to do it. In a single-user installation, maybe you
should just launch the postmaster as that user.

regards, tom lane

OK, I understand.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi, guys,
For some reason this reply was in my "Spam" folder (gmail service).
I guess moving to the new mailer service is not completely safe for "Spam".

So replying now - apologies to be late.

On Wed, Oct 31, 2018 at 2:00 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

Igor Korot wrote:

Now is there a command to flush the log - delete the content of it?

No, managing the logs is outside of PostgreSQL's responsibility.

But it shouldn't be a problem to do this outside the database.
Of course you could write a funtion in PostgreSQL that uses one
of the "untrusted" procedural languages to do it for you.

Ok.
I guess I will have to write such function.

All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.

You mean DROP, right?

Yes, sorry.

You can set "log_statement = 'ddl'" for that.

That's what I did.
But now I need to find a way to read the log file by the regular user
and not the "postgres" one.

Thank you.

On 11/06/2018 12:06 PM, Igor Korot wrote:
[snip]

Ok.
I guess I will have to write such function.

Cron and the relevant log_* config variables should solve your problems.

--
Angular momentum makes the world go 'round.

On 2018-10-29 14:56:06 -0400, Tom Lane wrote:

Igor Korot <ikorot01@gmail.com> writes:

Or I will have to change the owner/group manuall every time I will
access the file?

You can set up the log files as readable by the OS group of the server
(see log_file_mode), and then grant membership in that group to whichever
OS accounts you trust.

Another way is to use ACLs. Set a default ACL on the log directory which
enforces read permission for some users or groups on all newly created
files.

This is a bit more fine-grained and may be better suited for situations
where different people should be able to read the logs of different
servers. I also find that ACLs are more likely to survive "corrective"
actions by update or init scripts.

Another way would be to log via syslog and configure syslogd to split
the log files according to the username embedded in the log message.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;