PostgreSQL 9.6 – Log integrity

postgresql

I have a PG 9.6 DB and I need to add some auditing and after googling, I have found pgaudit. Before trying to install and playing a bit with it, I'd like to make sure it complies with all my requirements.

The first concern I have regards data integrity: from my understanding, the extension stores the information in a simple log file without any data integrity check, so If I open the log and I manually delete something nobody will never notice it. Am I correct?

Does PG come with any log integrity mechanism?

Thanks

Best Answer

For your concern, i would suggest, you make the generated log files read only so that nobody other than the owner can change its contents. Anyway if contents of any file is changed, you can check its last modification time by using command stat filename.

Till now postgresql doesn't have any reliable auditing tools, and by looking at the pgaudit link, it just gives you some extra information from what is present in database logs, and will create a logfile possibly bigger than the database log. It could result in space constraints on your server.

Related Solutions

PostgreSQL Group Roles – Understanding and Managing

It sounds like what you probably want is to:

Create a role to own all the common tables and schema, or just use your own if you really will always be the only one with full control of the main tables.
Create another role you intend to give only read-only access to the shared tables and schemas. GRANT that role rights using GRANT SELECT ON ALL TABLES IN SCHEMA [x] for each shared schema. You may also want to ALTER DEFAULT PRIVILEGES to make sure this role has read rights on any new tables created in these schemas too.
Now GRANT each user membership of the read-only access role with INHERIT.
For the private schemas, create a schema the same as the user's username with CREATE SCHEMA [username] AUTHORIZATION [username] or the older style where you create the schema then ALTER SCHEMA ... OWNER TO.

See the postgresql manual for the detailed syntax of all of the above commands. Start with user management, part of the broader database administration topic that includes grant management etc. The PostgreSQL manual is detailed, comprehensive and readable: reading it is strongly recommended.

PostgreSQL – Extension Created for Public Schema Not Available Within Database

Short answer

You are actually using (at least) two databases. You are installing CITEXT in the wrong one.

Longer answer

In the first database (whose name is not shown), you install CITEXT. This database is probably the postgres database, which is created by default when you install PostgreSQL.

Then you use the \c command and you switch to another database (sensordata). You need to create your extension in this database.

That is, you should do:

\c sensordata 
CREATE EXTENSION citext;
\dx

If you need to use the citext module in more databases, you need to install it in each one. The extensions on the other databases don't matter. Databases do not interfere with each other.

Every PostgreSQL cluster (=database installation) has (by default) one database named postgres. If you use psql, it is the one to which you connect "by default". You don't actually need to have a postgres database. I guess the postgresdatabase is where you installed the CITEXT extension.

Within every PostgreSQL database, there is, by default, one public schema.

PostgreSQL does not work like (for instance) MySQL, where you can acces several databases at once through a single connection (provided you have a user with the right permissions).

In PostgreSQL you access one database through a single connection. Within a database, you can access as many schemas as needed. The role that schemas play in PostgreSQL is nearly the same as the one played by a database in MySQL.

Best Answer

Related Solutions

PostgreSQL Group Roles – Understanding and Managing

PostgreSQL – Extension Created for Public Schema Not Available Within Database

Related Question