Обсуждение: Fwd: 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. > > regards, tom lane
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. 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.
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. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
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/>