Re: Passwordcheck configuration
Thank you for the information! This issue originated from a Department of Defense STIG (Security Technical Implementation Guides). It's a security check that applications and databases have to go through. I'll just leave this one as a "finding" since there isn't a way to really configure it to their requirements. Thanks again for your help. On Thu, Mar 19, 2020 at 7:19 PM Tom Lane wrote: > Dave Hughes writes: > > I have a requirement to set some password complexity for our database > such > > as length of password, upper case, lower case, special characters, > > expiration limit, reuse, etc. > > Usually, if you have to do something like that, we recommend setting PG to > use PAM authentication and configuring the restrictions on the PAM side. > The only native capability in that direction is that you can set a > password expiration date. > > Note that it's widely believed that this sort of thing makes you LESS > secure, not more. Quite aside from the well-established fact that forced > password changes are bad from a human-factors standpoint, you can't check > any of those other points unless the password is sent to the server as > cleartext. That creates its own set of vulnerabilities, and I don't > know of anybody who considers it good practice. > > > I saw there was a module you can use for this called passwordcheck. > Seems > > easy to install, but I don't see how you can configure it for you > specific > > needs? > > passwordcheck hasn't got any out-of-the-box configurability. It's mainly > meant as sample code that people could modify if they have a mind to. > > (I seem to recall some recent discussion about deprecating/removing > passwordcheck altogether, but I can't find it right now.) > > regards, tom lane >
Passwordcheck configuration
Hello, I have a requirement to set some password complexity for our database such as length of password, upper case, lower case, special characters, expiration limit, reuse, etc. I saw there was a module you can use for this called passwordcheck. Seems easy to install, but I don't see how you can configure it for you specific needs? The documentation ( https://www.postgresql.org/docs/11/passwordcheck.html) says it can be configured using CrackLib, but again I don't see "how" you go about setting this up. Does anyone know of any documentation that shows you "how" to set up specific requirements? Thanks in advance, Dave Hughes
Re: Help with configuring pgAudit
Oh okay! I wasn't aware of the pg_settings system view. Thanks for all the info! On Thu, Nov 21, 2019 at 1:36 PM Joe Conway wrote: > On 11/21/19 1:27 PM, Dave Hughes wrote: > > Thank you so much for all your help! I found out my issue on accident > > actually. I backed up all my user accounts into a SQL scripts and after > > reviewing it, I noticed there were some lines that said: > > ALTER ROLE postgres SET "pgauid.log" to 'Role'; > > ALTER ROLE postgres SET "pgaudit.log_level" to 'notice'; > > ALTER ROLE postgres SET "pgaudit.log_client" to 'on'; > > > > I think these commands were leftover from when I ran an integrity check > > on the pgaudit install (it crashed for other reasons) so it never > > cleaned up these settings. Once I reset those settings back, it's > > working perfectly now. > > > > Thanks again for helping me getting this thing setup and working! > > Ah, makes sense now. > > For future reference, you can inspect the pgaudit (and other) active > settings using the pg_settings system view, e.g.: > > select name, setting, source > from pg_settings where name like 'pgaudit.%'; > name| setting | source > +-+ > pgaudit.log| ddl, role, read | configuration file > pgaudit.log_catalog| on | configuration file > pgaudit.log_client | off | default > pgaudit.log_level | log | configuration file > pgaudit.log_parameter | on | configuration file > pgaudit.log_relation | off | configuration file > pgaudit.log_statement_once | off | configuration file > pgaudit.role | | default > (8 rows) > > HTH, > > Joe > > -- > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development > >
Re: Help with configuring pgAudit
Thank you so much for all your help! I found out my issue on accident actually. I backed up all my user accounts into a SQL scripts and after reviewing it, I noticed there were some lines that said: ALTER ROLE postgres SET "pgauid.log" to 'Role'; ALTER ROLE postgres SET "pgaudit.log_level" to 'notice'; ALTER ROLE postgres SET "pgaudit.log_client" to 'on'; I think these commands were leftover from when I ran an integrity check on the pgaudit install (it crashed for other reasons) so it never cleaned up these settings. Once I reset those settings back, it's working perfectly now. Thanks again for helping me getting this thing setup and working! On Thu, Nov 21, 2019 at 10:15 AM Joe Conway wrote: > On 11/20/19 5:54 PM, Dave Hughes wrote: > > Thanks for the tips Joe! After fighting with this all day, I realized > > while I was testing this, I was logging into the database as the > > "postgres" user. For some reason those actions were not being logged. > > But once I logged in as another superuser account I have, I saw all my > > ddl statements being audited in the log file. So it was working after > > all, but just not for the "postgres" user. Do you happen to know if > > that behavior is how pgaudit is supposed to work? You'd think even the > > "postgres" user activity would be logged as well? > > I'm not sure what you are doing wrong, but the "postgres" user actions > should get logged just like everything else. > > For grins I followed the aforementioned supplement pdf sections 2.2 > (pgaudit) except I used cvslog instead of stderr as a destination, and > 2.3 (logging), started up postgres, logged in as postgres, created a > table, and then tailed the postgres log: > > > tail -n 33 $PGDATA/postgresql.conf > shared_preload_libraries = 'pgaudit' > > # Enable catalog logging - default is 'on' > pgaudit.log_catalog='on' > # Specify the verbosity of log information (INFO, NOTICE, LOG, WARNING, > DEBUG) > pgaudit.log_level='log' > # Log the parameters being passed > pgaudit.log_parameter='on' > # Log each relation (TABLE, VIEW, etc.) mentioned in a SELECT or DML > statement > pgaudit.log_relation='off' > # For every statement and substatement, log the statement and parameters > every time > pgaudit.log_statement_once='off' > # Define the master role to use for object logging > # pgaudit.role='' > # Choose the statements to log: > # READ - SELECT, COPY > # WRITE - INSERT, UPDATE, DELETE, TRUNCATE, COPY > # FUNCTION - Function Calls and DO Blocks > # ROLE - GRANT, REVOKE, CREATE/ALTER/DROP ROLE > # DDL - All DDL not included in ROLE > # MISC - DISCARD, FETCH, CHECKPOINT, VACUUM > pgaudit.log='ddl, role, read' > > log_line_prefix = '%m %u %d: ' > log_destination = 'csvlog' > logging_collector = on > log_directory = 'pg_log' > log_filename = 'postgresql-%a.log' > log_file_mode = 0600 > log_truncate_on_rotation = on > log_rotation_age = 1d > log_rotation_size = 0 > > > (restart postgres) > > > psql test > psql (11.1) > Type "help" for help. > > test=# CREATE TABLE pgatest(id int); > CREATE TABLE > test=# \q > > > tail -n 1 $PGDATA/pg_log/postgresql-Thu.csv > 2019-11-21 10:07:39.320 > EST,"postgres","test",14809,"[local]",5dd6a829.39d9,1,"CREATE > TABLE",2019-11-21 10:07:21 EST,3/8,394984,LOG,0,"AUDIT: > SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE pgatest(id > int);,","psql" > > Joe > -- > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development > >
Re: Help with configuring pgAudit
Thanks for the tips Joe! After fighting with this all day, I realized while I was testing this, I was logging into the database as the "postgres" user. For some reason those actions were not being logged. But once I logged in as another superuser account I have, I saw all my ddl statements being audited in the log file. So it was working after all, but just not for the "postgres" user. Do you happen to know if that behavior is how pgaudit is supposed to work? You'd think even the "postgres" user activity would be logged as well? Thanks again. On Wed, Nov 20, 2019 at 9:46 AM Joe Conway wrote: > On 11/20/19 8:09 AM, Dave Hughes wrote: > > Hey, > > Thanks for reaching out. It looks like I have that parameter set as > > well. Here is a list of settings I have turned on in postgresql.conf > > since I installed pgAudit: > > > > shared_preload_libraries = 'pgaudit' > > log_destination = 'csvlog' > > logging_collector = on > > log_directory = '/work/PostgreSQL/10/data' > > log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' > > log_connections = on > > log_disconnections = on > > log_line_prefix = '<%m:%r:%u@%d:[%p]:>' > > pgaudit.log = 'ddl' > > > > Is there some other setting I may be missing? > > I don't see anything wrong that jumps out except perhaps the OS postgres > user does not have sufficient permissions to write to > '/work/PostgreSQL/10/data'. > > Beyond that, please see the supplemental PDF here for an example setup > instruction (section 2.2/appendix B, and possibly section 2.3 as well): > > > > https://dl.dod.cyber.mil/wp-content/uploads/stigs/zip/U_PostgreSQL_9-x_V1R6_STIG.zip > > Also read through the pgaudit README if you have not already done so: > > https://github.com/pgaudit/pgaudit > > Finally (again if not already done), see the docs section on logging: > > https://www.postgresql.org/docs/12/runtime-config-logging.html > > HTH, > > Joe > > -- > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development > >
Re: Help with configuring pgAudit
Hey, Thanks for reaching out. It looks like I have that parameter set as well. Here is a list of settings I have turned on in postgresql.conf since I installed pgAudit: shared_preload_libraries = 'pgaudit' log_destination = 'csvlog' logging_collector = on log_directory = '/work/PostgreSQL/10/data' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_connections = on log_disconnections = on log_line_prefix = '<%m:%r:%u@%d:[%p]:>' pgaudit.log = 'ddl' Is there some other setting I may be missing? Thanks! On Tue, Nov 19, 2019 at 4:27 PM Rajni Baliyan wrote: > Hello Dave, > What I can see is you missed to include pgAudit extension in > shared_preload_libraries parameter (*shared_preload_libraries='pgaudit'* > ). > > Thanks > Rajni > > On Wed, Nov 20, 2019 at 7:39 AM Dave Hughes wrote: > >> Hello, >> I'm using PostgreSQL 10.5 on Linux (RHEL). I recently installed pgAudit >> and was trying to configure it to capture DLL statements. >> >> 1) The first thing I tried was to edit the postgresql.conf file >> directly. I didn't see any commented out default entries to edit, so near >> where I have the entries: >> log_destination = 'csvlog' >> log_collector = on >> I entered my own entry of: >> pgaudit.log = 'ddl' >> Then I restarted the database. After doing this I created and dropped a >> table, but no entry of that in the log file >> postgresql-2019-11-19-141901.csv. >> >> 2) So then tried to create the entry using the ALTER command: >> ALTER SYSTEM SET pgaudit.log TO 'ddl'; >> SELECT pg_reload_conf(); >> After doing this, I noticed a second file was created >> (postgresql.auto.conf). That file had the entry pgaudit.log = 'ddl'. From >> what I read, this file get read after the main postgresql.conf file. >> However after creating and dropping a table, still no entry in the log >> file. I did notice though when I run the command: show pgaudit.log; It >> came back with 'Role' (and not ddl). >> >> 3) So the final thing I tried was to enter the command: set pgaudit.log = >> 'ddl'; Now when I run the command show pgaudit.log; it returned 'ddl'. >> This time I tried to drop a table again. After the table was dropped , my >> psql client displayed: >> NOTICE: AUDIT: SESSION, 1,1,DDL, DROP TABLE, TABLEblah,blah> logged>. This looks like a pgaudit entry but was on my screen and not in >> the log file. At this point I restarted the database again and now when I >> run the command: show pgaudit.log; it defaulted back to 'Role' (vs. ddl). >> >> Can someone please help me see what it is i'm doing incorrectly? >> >> Thanks in advance, >> Dave Hughes >> >>
Help with configuring pgAudit
Hello, I'm using PostgreSQL 10.5 on Linux (RHEL). I recently installed pgAudit and was trying to configure it to capture DLL statements. 1) The first thing I tried was to edit the postgresql.conf file directly. I didn't see any commented out default entries to edit, so near where I have the entries: log_destination = 'csvlog' log_collector = on I entered my own entry of: pgaudit.log = 'ddl' Then I restarted the database. After doing this I created and dropped a table, but no entry of that in the log file postgresql-2019-11-19-141901.csv. 2) So then tried to create the entry using the ALTER command: ALTER SYSTEM SET pgaudit.log TO 'ddl'; SELECT pg_reload_conf(); After doing this, I noticed a second file was created (postgresql.auto.conf). That file had the entry pgaudit.log = 'ddl'. From what I read, this file get read after the main postgresql.conf file. However after creating and dropping a table, still no entry in the log file. I did notice though when I run the command: show pgaudit.log; It came back with 'Role' (and not ddl). 3) So the final thing I tried was to enter the command: set pgaudit.log = 'ddl'; Now when I run the command show pgaudit.log; it returned 'ddl'. This time I tried to drop a table again. After the table was dropped , my psql client displayed: NOTICE: AUDIT: SESSION, 1,1,DDL, DROP TABLE, TABLEblah,blah. This looks like a pgaudit entry but was on my screen and not in the log file. At this point I restarted the database again and now when I run the command: show pgaudit.log; it defaulted back to 'Role' (vs. ddl). Can someone please help me see what it is i'm doing incorrectly? Thanks in advance, Dave Hughes
Re: pgaudit log directory
Thanks for the response! I realized I didn't have the default logging turned on. I needed to edit the postgresql.conf file to enable log_destination = 'csvlog' and logging_collector = on. Once I did that I can now see the audit file. On Tue, Nov 19, 2019 at 4:31 AM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 18/11/19 9:56 μ.μ., Dave Hughes wrote: > > Hello, > > I'm using PostgreSQL 10.5 on Linux (RHEL). I'm new to administering > PostgreSQL and recently installed pgaudit. I believe I have it installed > correctly and wanted to start playing with it to see > > how exactly it works. > > > > So while walking through a tutorial I found online, I saw where I can > enter a statement in PostgreSQL, such as: > > ALTER SYSTEM SET pgaudit.log TO 'read, write'; > > SELECT pg_reload_conf(); > > Then after reading or writing to a table, you can then check "pg_log" > for the audit entries. But my issue is that I can't find the log file at > all? > > > > In my main PostgreSQL directory (/work/PostgreSQL/10)I do have a file > called "logfile", but there are no entries from today. When I go into the > pgaudit sub-directory (/work/PostegreSQL/10/pgaudit) > > I don't see any log file in there either? > > pgaudit writes in the standard pgsql log. > > > > > Can someone point me in the right direction? > > > > Thanks, > > Dave Hughes > > > -- > Achilleas Mantzios > IT DEV Lead > IT DEPT > Dynacom Tankers Mgmt > > > >
pgaudit log directory
Hello, I'm using PostgreSQL 10.5 on Linux (RHEL). I'm new to administering PostgreSQL and recently installed pgaudit. I believe I have it installed correctly and wanted to start playing with it to see how exactly it works. So while walking through a tutorial I found online, I saw where I can enter a statement in PostgreSQL, such as: ALTER SYSTEM SET pgaudit.log TO 'read, write'; SELECT pg_reload_conf(); Then after reading or writing to a table, you can then check "pg_log" for the audit entries. But my issue is that I can't find the log file at all? In my main PostgreSQL directory (/work/PostgreSQL/10)I do have a file called "logfile", but there are no entries from today. When I go into the pgaudit sub-directory (/work/PostegreSQL/10/pgaudit) I don't see any log file in there either? Can someone point me in the right direction? Thanks, Dave Hughes
Re: Authentication: MD5 to SCRAM-SHA-256 error
Thanks for replying guys! You both led me down the right path. I didn't realize it, but looks like we had 2 directories where psql was installed. Once I gave the path specifically to PostgreSQL10, it worked like a charm. I'm reaching out to our server admins now to see why there are 2 instances. Thanks again for pointing me in the right direction. On Fri, Nov 15, 2019 at 11:44 AM Christoph Moench-Tegeder < c...@burggraben.net> wrote: > ## Dave Hughes (dhughe...@gmail.com): > > > However when I try to log in now, via command line, I receive the error: > > "psql: authentication method 10 not supported". > > Your client (more precisely: it's libpq) is not ready for SCRAM. > I guess you're using an older (<10) client version? Mixed up > packages? > > Regards, > Christoph > > -- > Spare Space >
Authentication: MD5 to SCRAM-SHA-256 error
Hello, We're currently using PostgreSQL version 10.5 in a Linux environment. We were wanting to change the password authentication from MD5 to SCRAM-SHA-256. I performed these steps to do so: 1) Modified the postgresql.conf and changed the password_encryption entry from "md5" to "scram-sha-256". 2) restarted the database 3) Changed all our users password to a default password using the command: alter user xxx password 'xxx'; 4) Once I did this, I could run this sql statement and verify the password was now a sha-256 password: select passwd from pg_shadow where username = 'xxx' 5) Finally, i went into the pg_hba.conf file and changed the authentication method from md5 over to scram-sha-256. 6) restarted the database again. However when I try to log in now, via command line, I receive the error: "psql: authentication method 10 not supported". I tried to search online for this error but everything I've seen implies that occurs when some client's libraries are not compatible, but i'm just using psql via the command line. What's worse...I tried to set everything back to MD5 and i'm still getting the exact same error. Has anyone else experienced this? The only thing I can think of is that even though I'm on version 10.5, maybe somehow I have old libraries it's trying to use to connect? Any help would be greatly appreciated! Thanks!
Managing permissions for multiple users to Create and Drop tables
We have one database with several schemas. We have several groups of developers that have the need to be able to collaborate including creating and dropping tables. I noticed it became difficult to manage because when one developer creates a table, he is now the owner. All the other developers need to be given permissions to select from (or drop) those tables he created. I was looking for a way to where all members of a group can select from everybody’s tables, drop them if needed, etc. Where they can all work collaboratively. I found this site that had a suggestion: https://blog.hagander.net/setting-owner-at-create-table-237/ You basically create a “common_role” as the table owner and grant all the users access to tables owned by common_role. However, for this to work automatically, you need to create an event trigger so that each time a developer creates a table, it alters the table owner after the table is created. I was wonder how other DBA’s handle this permission issue for collaborative environments? Any best practices or advise?
SuperUser permission denied when creating table.
I recently noticed that my default superuser "postgres" cannot create tables in new schemas I create, but can in some existing ones. So as the postgres user I ran: afleads=# create schema mytest; CREATE SCHEMA afleads=# create table mytest.test (col1 int); ERROR: permission denied for schema mytest LINE 1: create table mytest.test (col1 int); So I tried to give usage & connect permission to another superuser I have, but I get the same error: afleads=# GRANT USAGE, CREATE ON SCHEMA mytest TO dhughes; GRANT afleads=# set role dhughes; SET afleads=# create table mytest.test (col1 int); ERROR: permission denied for schema mytest LINE 1: create table mytest.test (col1 int); So then I gave the same usage & create permission to a non-superuser. It's a group role we have: afleads=# GRANT USAGE, CREATE ON SCHEMA mytest TO creator_role; GRANT afleads=# set role creator_role; SET afleads=> create table mytest.test (col1 int); CREATE TABLE And then it works. This has me baffled as to why the two superusers I have do not have the permissions to create a table, when non-superusers can (once granted permissions). However I still seem to be able to create tables in older schemas I have: afleads=# select current_role; current_role -- postgres (1 row) afleads=# create table timeline.test (col1 int); CREATE TABLE Has anyone ran across something like this before?
