How to avoid dead rows in tables.

[Raghavendra Rao J S V]

Hi All,


auto vacuum is enabled in our database. But few of the tables contains the
dead tuples more than 5,000 records. Number of dead rows are keep on
increasing day by day if we didn’t perform the vacuum full. Monthly once we
are performing vacuum full by stopping application server process. Later we
are restarting the application server process.



How to avoid accumulating the dead tuples for those tables. Is there any
other approach to remove the dead tuple’s without vacuum full/down time.



Note:- We are using the postgres version 9.2


-- 
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

Re: How to avoid dead rows in tables.

[Andreas Kretschmer]

On 6 August 2018 09:04:45 CEST, Raghavendra Rao J S V 
 wrote:
>Hi All,
>
>
>auto vacuum is enabled in our database. But few of the tables contains
>the
>dead tuples more than 5,000 records. Number of dead rows are keep on
>increasing day by day if we didn’t perform the vacuum full. Monthly
>once we
>are performing vacuum full by stopping application server process.
>Later we
>are restarting the application server process.
>
>
>
>How to avoid accumulating the dead tuples for those tables. Is there
>any
>other approach to remove the dead tuple’s without vacuum full/down
>time.
>
>
>
>Note:- We are using the postgres version 9.2

You can tweak the autovacuum-settings, per default autovacuum will run if the 
table contains more than 20% + 50 dead tuples.

Regards,Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

How to revoke privileged from PostgreSQL's superuser

[bejita0409]

Hello,
 
I am a newbie DBA.
 
I have a request for revoking the access to user's data from DBA-user.
I think the request is right because users should be the only ones can access 
their data.
But DBA-user also need full access to the other data? It means that DBA-user 
also needs to be a superuser.
 
So I conclude the request that how to revoke privileged from superuser in 
postgres.
 
As my knowledge, the superuser in PostgreSQL bypasses all the permission check.
So that, there is no way to do it in PostgreSQL, is that right?
 
Is there some DBAs are faced with this before?
 
 
Thanks,
--
bejita

Re: [External] How to revoke privileged from PostgreSQL's superuser

[Vijaykumar Jain]

I am not sure superuser can be selectively restricted via queries, but I am not 
sure, have not tried.

But maybe you can try restricting the super user access to the db from all 
hosts via the pg_hba.conf.

Fore eg. I have a user
monitor| Superuser

and
in my /etc/postgresql/10/main/pg_hba.conf

host pgtesting monitor 0.0.0.0/0   reject

and then
psql -U monitor -p 5432 -d pgtesting -h 127.0.0.1
psql: FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user 
"monitor", database "pgtesting", SSL on
FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", 
database "pgtesting", SSL off

psql -U monitor -p 5432 -d pgtesting -h localhost
psql: FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user 
"monitor", database "pgtesting", SSL on
FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", 
database "pgtesting", SSL off

psql -U monitor -p 5432 -d pgtesting -h 173.16.6.3
psql: FATAL:  pg_hba.conf rejects connection for host "173.16.6.3", user 
"monitor", database "pgtesting", SSL on
FATAL:  pg_hba.conf rejects connection for host "173.16.6.3", user "monitor", 
database "pgtesting", SSL off


https://stackoverflow.com/questions/38942868/revoke-superuser-connect-a-specific-database

Thanks,
Vijay


From: "bejita0...@yahoo.co.jp" 
Reply-To: "bejita0...@yahoo.co.jp" 
Date: Monday, August 6, 2018 at 3:19 PM
To: "pgsql-ad...@lists.postgresql.org" , 
"pgsql-general@lists.postgresql.org" 
Subject: [External] How to revoke privileged from PostgreSQL's superuser

Hello,

I am a newbie DBA.

I have a request for revoking the access to user's data from DBA-user.
I think the request is right because users should be the only ones can access 
their data.
But DBA-user also need full access to the other data? It means that DBA-user 
also needs to be a superuser.

So I conclude the request that how to revoke privileged from superuser in 
postgres.

As my knowledge, the superuser in PostgreSQL bypasses all the permission check.
So that, there is no way to do it in PostgreSQL, is that right?

Is there some DBAs are faced with this before?


Thanks,
--
bejita

RE: How to revoke privileged from PostgreSQL's superuser

[Charles Clavadetscher]

Hello

 

From: bejita0...@yahoo.co.jp [mailto:bejita0...@yahoo.co.jp] 
Sent: Montag, 6. August 2018 11:49
To: pgsql-ad...@lists.postgresql.org; pgsql-general@lists.postgresql.org
Subject: How to revoke privileged from PostgreSQL's superuser

 

Hello,

 

I am a newbie DBA.

 

I have a request for revoking the access to user's data from DBA-user.

I think the request is right because users should be the only ones can access 
their data.

But DBA-user also need full access to the other data? It means that DBA-user 
also needs to be a superuser.

 

So I conclude the request that how to revoke privileged from superuser in 
postgres.

 

As my knowledge, the superuser in PostgreSQL bypasses all the permission check.

So that, there is no way to do it in PostgreSQL, is that right?

 

Yes, superuser have access to everything. The main question is why should a 
superuser be restricted in her access to an object?

I think that this is more a question of designing your user and groups in a 
clean way.

 

In theory you could restrict access using a before trigger, but this is not a 
good idea and is quite expensive.

You could also use a policy for the superuser with the clause using(false), but 
this also is not really a good idea and may become
very confusing.

 

In either way, this would not be a good solution.

 

Maybe, if you clarify better what is your purpose, there might be more useful 
answers and approaches.

 

Regards

Charles

 

Is there some DBAs are faced with this before?

 

 

Thanks,

--

bejita

Re: How to revoke privileged from PostgreSQL's superuser

[David G. Johnston]

On Monday, August 6, 2018,  wrote:

>
> I have a request for revoking the access to user's data from DBA-user.
> I think the request is right because users should be the only ones can
> access their data.
>

User then needs to encrypt data prior to storing it.  Superuser can still
access the data but would be challenged to make sense of it,

Usually DBAs are tasked with backups which requires read access to all
relevant data.

David J.

Re: How to revoke privileged from PostgreSQL's superuser

[Bear Giles]

I think you may be conflating two things.

The "superuser" (or root) in an operating system is a special user (with
uid = 0 in unix/linux systems) that bypasses many checks. Ideally sysadmins
and regular users will never log in as root - they'll either use the
existing privilege framework (e.g., groups and set-uid programs) or briefly
use privilege escalation via a program like 'sudo'.

In postgresql the equivalent user is 'postgres'. Nobody should ever be
logged in as that user once you've created the initial user(s). What
postgresql calls a 'superuser' is just a user with a few permissions set by
default. It's easy to grant the same privileges to any user, or drop them
from someone created as a superuser.

Since they're regular users it's easy to revoke privileges. E.g., I think
the command you want is 'revoke all on database x from [ role | public]'
or 'revoke all on schema x from [ role | public ]'. I don't know if you can
revoke their admin privileges on a single database. If not then it's just a
small hurdle since they could grant themselves the necessary privilege, do
their work, and then revoke them so they'll never know unless a periodic
audit of privileges catches them in the act.*

However it's important that you make sure you don't block normal
operations. E.g., you'll probably want a user who has access to your tables
in order to perform backups. (This user could be limited to SELECT
privileges.) You'll also need a user who can perform a restoration from
backups - that's normally the DBA. It's tempting to limit these privileges
to the only times they're actually required, e.g., the backup script could
start by granting SELECT privileges to the backup user and finish by
revoking those privileges, but that's arguably worse since it requires a
second user with the admin privileges required for that.

Since you're a newbie are you aware of postgresql 'roles'? You should have
a DBA role but never allow anyone to log in as a DBA user. Instead every
DBA has their own user account who is a member of that role. That role has
the extra privileges, not individual users, so it's easy to control access
by adding and removing that role.

Finally I think you may be asking the wrong question. You need a DBA and
you need to trust the people the people who have DBA rights. If you don't
trust them then they shouldn't hold that job. All of the security standards
I'm familiar accept that some people will have high levels of access and
the focus is on vetting those people for trustworthiness.

(*) If you want to be really paranoid you could put a trigger on the pg_*
tables that contain privileges and triggers. That would prevent anyone from
giving themselves extra privileges, or disabling the trigger that would
alert you if they give themselves extra privileges, without at least some
warning. Heck, the trigger could even prevent them from making these
changes. But that's pretty advanced dba-fu.


On Mon, Aug 6, 2018 at 3:48 AM,  wrote:

> Hello,
>
> I am a newbie DBA.
>
> I have a request for revoking the access to user's data from DBA-user.
> I think the request is right because users should be the only ones can
> access their data.
> But DBA-user also need full access to the other data? It means that
> DBA-user also needs to be a superuser.
>
> So I conclude the request that how to revoke privileged from superuser in
> postgres.
>
> As my knowledge, the superuser in PostgreSQL bypasses all the permission
> check.
> So that, there is no way to do it in PostgreSQL, is that right?
>
> Is there some DBAs are faced with this before?
>
>
> Thanks,
> --
> bejita
>

Re: How to revoke privileged from PostgreSQL's superuser

[Tom Lane]

Bear Giles  writes:
> In postgresql the equivalent user is 'postgres'. Nobody should ever be
> logged in as that user once you've created the initial user(s). What
> postgresql calls a 'superuser' is just a user with a few permissions set by
> default. It's easy to grant the same privileges to any user, or drop them
> from someone created as a superuser.

Well, more to the point, a superuser is somebody with the rolsuper bit
set in their pg_authid entry.  You can revoke the bootstrap superuser's
superuserness if you have a mind to -- see ALTER USER.  However, as
everyone has pointed out already, this is a bad idea and you will end
up undoing it.  (Figuring out how to do that without a reinstall is left
as penance for insisting on a bad idea.  It is possible, and I think
even documented.)

However: a whole lot of what the bootstrap superuser can do is inherent
in being the owner of all the built-in database objects, and that you
cannot get rid of.  Objects have to be owned by somebody.

regards, tom lane

Re: How to revoke privileged from PostgreSQL's superuser

[Bear Giles]

Encrypting data within the database(*) severely limits its usability - you
can't use it in queries, etc. In some cases it's not a problem since you'll
never want to use it in a query anyway, or you can use a proxy(**). But as
a general rule I think if you're encrypting much of your data then a
traditional database isn't the right solution to your problem.

(*) the underlying filesystem and should still be encrypted. The backups
should also be encrypted - a lot of people forget to do that.

(**) for instance you might consider the person's email address to be
sensitive information that should be encrypted but you still want to be
able index the field so you can perform a rapid lookup. In that case you
can add a salted hash of the email and index that. Your app knows how to
perform the same hash so it can quickly find the record but it's totally
opaque to an intruder.

It's important to use a salted hash since an unsalted hash is no longer
secure since a knowledgeable intruder probably already has a list of emails
from other attacks and can easily compute the values to check. At the
minimum a salted hash is something like sha1(email + "my secret") (NOT
sha1("my secret" + email)) but you should really use one of the standard
algorithms to convert a passphrase and salt into an encryption key.
(PBE2K?) For performance reasons you might not want to perform all 1000+
iterations required for an encryption key but it's important to use a
standard algorithm since it's really easy to create hashes that aren't
nearly as strong as you think. E.g., there's a huge difference between
hash(value + salt) and hash(salt + value).

In this case the salt has to systemwide, or at least easily computed given
the email address but not derived from it (e.g., you can use the last few
digits of the hash of the email address as the index into a lookup table
but don't use the hash itself.) In most cases it's best to add a 'salt'
column to the record, perhaps in a shadow table that's not obvious to an
intruder, but you can't do that with anything used in a lookup since you
have no idea what value to use.

On Mon, Aug 6, 2018 at 7:19 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, August 6, 2018,  wrote:
>
>>
>> I have a request for revoking the access to user's data from DBA-user.
>> I think the request is right because users should be the only ones can
>> access their data.
>>
>
> User then needs to encrypt data prior to storing it.  Superuser can still
> access the data but would be challenged to make sense of it,
>
> Usually DBAs are tasked with backups which requires read access to all
> relevant data.
>
> David J.
>
>

Re: How to revoke privileged from PostgreSQL's superuser

[Bear Giles]

Thanks, I didn't realize that this was handled by a flag instead of just
the standard permissions.

On Mon, Aug 6, 2018 at 7:43 AM, Tom Lane  wrote:

> Bear Giles  writes:
> > In postgresql the equivalent user is 'postgres'. Nobody should ever be
> > logged in as that user once you've created the initial user(s). What
> > postgresql calls a 'superuser' is just a user with a few permissions set
> by
> > default. It's easy to grant the same privileges to any user, or drop them
> > from someone created as a superuser.
>
> Well, more to the point, a superuser is somebody with the rolsuper bit
> set in their pg_authid entry.  You can revoke the bootstrap superuser's
> superuserness if you have a mind to -- see ALTER USER.  However, as
> everyone has pointed out already, this is a bad idea and you will end
> up undoing it.  (Figuring out how to do that without a reinstall is left
> as penance for insisting on a bad idea.  It is possible, and I think
> even documented.)
>
> However: a whole lot of what the bootstrap superuser can do is inherent
> in being the owner of all the built-in database objects, and that you
> cannot get rid of.  Objects have to be owned by somebody.
>
> regards, tom lane
>

Re: How to revoke privileged from PostgreSQL's superuser

[Bear Giles]

I should clarify something I said above - I know that some data must be
encrypted within the database. My point was that during the design phase
you should take the schema, cross out all columns that will be encrypted,
and ask what's left. If it's nothing but the primary key and things you'll
never sort on (or require uniqueness) then it's reasonable to ask if
storing the information in a relational database is the best approach. An
alternative would be storing each encrypted record as an S3 file or Hadoop
HBase record. In both cases the filename/key would be based on something
like a hashed email address. In the case of S3 it's important to remember
that there is a limit on the number of entries although I don't remember
what it is. Some people have hit it when using it like this.

It's a little more work but it's arguably a lot more secure if you make
full use of AWS and Hadoop security. On the other hand keeping everything
in a single place is a lot more convenient and it only requires one
security audit, not two.

By 'shadow' table I mean that there's no reason why everyone needs access
to everything. A classic example is the user table. Nearly everyone keeps
the password (hopefully hashed!) in the user table. There's no reason to do
this - it's just as easy to keep the passwords in a separate table with the
same primary key. That will only affect the bits of your app that handle
authentication - verifying a password or changing it. If anyone manages to
do a SQL injection attack to list the contents of the user table they'll
learn the user accounts but probably won't get the user's passwords, esp.
if you give that table some innoculous name instead of 'password'.

But since such a limited amount of code that requires access to that table
you can make a design decision that you'll use a separate database user
when authenticating a user. That user will have access to the password
table but the regular user won't have any access to it. If you want to be
really secure you could put the password table into a different schema. The
table is then entirely hidden from someone who has full access to your main
schema. Hence "shadow" table. (Plus the fact that user passwords are kept
in the /etc/shadow file on unix/linux systems.)

This is a pretty powerful technique. E.g., some people add a column that
keeps a salted hash of the data in a row. An intruder won't know to change
the hash value so you can dramatically improve security by checking that
hash value when retrieving a value from the database. If it doesn't match
you throw an alert and refuse to use the value. However this leaves a trace
on the table to the intruder knows that their change will be detected.
That's often a Good Thing - it's a deterrent - but you might prefer to let
intruders make changes so you can determine they're in your system. You
could use a shadow table to hold the hashes and view so that your app
always sees the hash but an intruder doesn't.

You could even go a step further and have a trigger that updates a shadow
table with the 'before' and 'after' values on an insert/update/delete. (Or
pgaudit if you're on postgresql specifically - the general approach works
with any database that supports triggers.) Nobody intruder knows about
this, no application developer knows about this, only the DBA and security
team. That lets you track down every change - what changed, when, who did
it (including IP address if you capture connection info), etc. At most an
intruder might see there's a trigger but if it works via a stored procedure
with the appropriate permissions they won't know what you're doing with it.

The one thing to be careful about is that you can't use a trigger to update
the hash value mentioned above. That would also be fired by an intruder so
it won't tell you anything. It has to be done programmatically, but (at
least with java) it could be implemented by a security team that uses AOP
so the developers know there's a few extra columns but they don't do
anything with them... or if you're paranoid the security team has its own
persistence mechanism so the developers are completely unaware that this is
happening since the hash is written to a different schema & table.

Hmm, I should probably write a blog entry for this...

Bear


On Mon, Aug 6, 2018 at 8:13 AM, Evan Bauer  wrote:

> Bejita,
>
> I suggest you step back and think about the problem from the point of view
> of the desired security outcome — that of protecting data from improper use
> by administrators.  Some of the elements that (to my mind) ought to be part
> of achieving that outcome are:
>
> 1. Determine and document your organizations data access policies.  They
> could be very simple, but it is important to document and share them.
> 2. Make use of a privileged access management scheme so that no one has
> unfettered access to superuser (postgres, root, et al) passwords, but has
> to check them out from an audited system for a specific task and time
> period, wi

Re: How to revoke privileged from PostgreSQL's superuser

[Evan Bauer]

Bejita,

I suggest you step back and think about the problem from the point of view of 
the desired security outcome — that of protecting data from improper use by 
administrators.  Some of the elements that (to my mind) ought to be part of 
achieving that outcome are:

1. Determine and document your organizations data access policies.  They could 
be very simple, but it is important to document and share them.
2. Make use of a privileged access management scheme so that no one has 
unfettered access to superuser (postgres, root, et al) passwords, but has to 
check them out from an audited system for a specific task and time period, with 
appropriate approval processes if needed.
3. Use pgaudit to maintain an independent record of all sensitive access. The 
doc is at: https://github.com/pgaudit/pgaudit/blob/master/README.md
4. Create a set of administrative roles privileged to only the needs of the 
tasks required.  Under normal circumstances, no one should use the ‘postgres’ 
account for production access.  This also provides a means of enforcing 
compliance to your policies.  Tom Vondra wrote a good introduction here: 
https://blog.2ndquadrant.com/auditing-users-and-roles-in-postgresql/
5. Setup automated (I tend to use ELK or Splunk) examination of the audit logs 
for violations and anomalies.  Human review at regular intervals will also make 
your regulators or security auditors happier (they are never really happy.)
6. Make use of row-level access control and encryptions as appropriate to 
protect your data.  This blog post by Jonathan Katz is a good introduction: 
https://info.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies
 

There is a lot of thought and work that goes into executing the steps above, 
but administering systems and databases that handle sensitive data is a serious 
responsibility and requires requirements definition, planning, architecture, 
execution, and then continuous monitoring and improvement.  As someone new to 
the DBA role, you should talk to your architecture colleagues as you have some 
good and serious work ahead of you.

Cheers,

- Evan



> On Aug 6, 2018, at 09:43, Tom Lane  wrote:
> 
> Bear Giles  writes:
>> In postgresql the equivalent user is 'postgres'. Nobody should ever be
>> logged in as that user once you've created the initial user(s). What
>> postgresql calls a 'superuser' is just a user with a few permissions set by
>> default. It's easy to grant the same privileges to any user, or drop them
>> from someone created as a superuser.
> 
> Well, more to the point, a superuser is somebody with the rolsuper bit
> set in their pg_authid entry.  You can revoke the bootstrap superuser's
> superuserness if you have a mind to -- see ALTER USER.  However, as
> everyone has pointed out already, this is a bad idea and you will end
> up undoing it.  (Figuring out how to do that without a reinstall is left
> as penance for insisting on a bad idea.  It is possible, and I think
> even documented.)
> 
> However: a whole lot of what the bootstrap superuser can do is inherent
> in being the owner of all the built-in database objects, and that you
> cannot get rid of.  Objects have to be owned by somebody.
> 
>   regards, tom lane
> 

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Alexandru Lazarev]

Thanks all for responses.

Let me ask other dummy question:
plv8 RPMs were built by PostgreSQL Community for different OSes, or by
those OSes vendors/community (e.f. RedHat/Debian, etc)?
And the same question about postgresql-server install packages themselves
(RPMs, debs, etc)

Thanks in advance

On Mon, Aug 6, 2018 at 12:02 AM, Devrim Gündüz  wrote:

>
> Hi,
>
> On Thu, 2018-08-02 at 08:53 +0300, Alexandru Lazarev wrote:
> > I saw on URL there are PG 9.6.8 and 9.6.9 - Are there maintained only
> > latest 2 build releases?
>
> Yes, older releases are deleted automagically.
>
> Regards,
> --
> Devrim Gündüz
> EnterpriseDB: https://www.enterprisedb.com
> PostgreSQL Consultant, Red Hat Certified Engineer
> Twitter: @DevrimGunduz , @DevrimGunduzTR
>

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Alexandru Lazarev]

 Thanks all for responses.

Let me ask other dummy question:
plv8 RPMs were built by PostgreSQL Community for different OSes, or by
those OSes vendors/community (e.f. RedHat/Debian, etc)?
And the same question about postgresql-server install packages themselves
(RPMs, debs, etc)

Thanks in advance


On Fri, Aug 3, 2018 at 11:30 AM, Pavel Raiskup  wrote:

> On Friday, August 3, 2018 8:08:55 AM CEST Devrim Gündüz wrote:
> > On Thu, 2018-08-02 at 16:26 +0300, Alexandru Lazarev wrote:
> > > What about 3rd party libraries like plv8 - Who and How (based on which
> > > criteria, which versions) build RPM and upload them there?
> >
> > Latest versions of PL/v8 does not build on RHEL/Fedora anymore, at least
> from
> > the package build point of view.
>
> Yes, packaging of plv8 is pretty complicated.  If one decided to ship RPM
> package with plv8, it would mean maintenance of whole v8 language - which
> is incredibly complicated (incompatible changes all the time, backporting
> security fixes, etc.).
>
> That's the reason why plv8 (and even v8 runtime) becomes dropped from Linux
> distributions.
>
> [1] https://github.com/plv8/plv8/issues/281
>
> Pavel
>
>
>
>

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Dimitri Maziuk]

On 08/06/2018 03:49 PM, Alexandru Lazarev wrote:

> And the same question about postgresql-server install packages themselves
> (RPMs, debs, etc)

For this one, packages you get from "PGDG"@
https://www.postgresql.org/download/ are built by postgres. RedHat, for
example, also has postgres built by them and included in the
distribution -- in RHEL7 it's v.9.2 but RedHat is known to patch code
without changing version numbers.

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Devrim Gündüz]

Hi,

On Mon, 2018-08-06 at 23:49 +0300, Alexandru Lazarev wrote:

> Let me ask other dummy question:
> plv8 RPMs were built by PostgreSQL Community for different OSes, or by
> those OSes vendors/community (e.f. RedHat/Debian, etc)?
> And the same question about postgresql-server install packages themselves
> (RPMs, debs, etc)

There are community RPMs, and then the RPMs supplied by Red Hat / CentOS  via 
OS or SCL.

Community packages has wider range of supported PostgreSQL versions, and wider 
range of packages.

Regards,
-- 
Devrim Gündüz
EnterpriseDB: https://www.enterprisedb.com
PostgreSQL Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: PANIC: could not open critical system index 2662

[C GG]

On Sat, Aug 4, 2018 at 11:13 AM, Tom Lane  wrote:

> C GG  writes:
> > When trying to restart the PostgreSQL 9.2 database for our JIRA
> > installation, we're getting
> > "PANIC:  could not open critical system index 2662" ... I've tried
> various
> > things like turning on zero_damaged_pages but postmaster still crashes on
> > connect when attempting to pg_dump.
>
> 2662 is pg_class_oid_index :-(
>
> Try starting a single-user backend with the ignore-system-indexes flag,
> and doing "REINDEX INDEX pg_class_oid_index".  If that works, I'd try
> reindexing the rest of the system just to see how bad things are.
>
>
I appreciate the reality check, Tom. I turned it every which way, but no
matter what, before I can issue a statement it will crash. Looks pretty
bleak.

If that doesn't work, it's time to hire professional help --- there are
> several companies that specialize in PG data recovery.
>
>
@Community: Does anyone know of a data recovery company that will work on a
per-incident basis?




> regards, tom lane
>

Re: PANIC: could not open critical system index 2662

[Ravi Krishna]

Just curious, why can't you restore the db from the backup ?

Re: How to revoke privileged from PostgreSQL's superuser

[Tim Cross]

bejita0...@yahoo.co.jp writes:

> Hello,
>
> I am a newbie DBA.
>
> I have a request for revoking the access to user's data from DBA-user.
> I think the request is right because users should be the only ones can access 
> their data.
> But DBA-user also need full access to the other data? It means that DBA-user 
> also needs to be a superuser.
>
> So I conclude the request that how to revoke privileged from superuser in 
> postgres.
>
> As my knowledge, the superuser in PostgreSQL bypasses all the permission 
> check.
> So that, there is no way to do it in PostgreSQL, is that right?
>
> Is there some DBAs are faced with this before?
>

There are certainly DBAs who have had very similar requests. Often, they
are generated by non-technical people who don't really understand how
the technology works and have concerns over who has access to the data
(a common one is people who are concerned about who has access to their
email - we had a similar challenge from our Chief legal officer who was
paranoid sys admins were reading his highly sensitive email, this is
despite the fact 1 week previously, I was on a flight sitting in the
seat behind him while he read his email on his iPad, which I could (but
didn't) easily read over his shoulder!).

The key to handling this sort of request is to dig deeper to understand
what the real risk is that they want addressed and work out how you can
do that within the constraints of the technology and what makes sense
within your context. I'm sure someone will respond to this thread with
all sorts of highly restrictive and powerful controls that will restrict
access to the data, but if they are not appropriate for your business
context, will likely cripple the very process you are trying to
protect. All controls/restrictions cause some level of inconvenience -
the challenge is in getting the balance right so that the identified
risk is mitigated with the least level of inconvenience to normal
business operations.

The reality is that at various times, humans will need the ability to
access the data in ways which will limit, if not completely prevent,
your ability to restrict access. This is particularly relevant for
system and database administrators. It is pretty much 100% impossible to
guarantee that a sys admin or DBA cannot access data. However, what you
can do is approach the problem slightly differently and look at ways to
make this access harder and more importantly, make sure that all access
is logged appropriately and can be audited, ensuring the
logging/auditing system is also protected from deletion or modification.

Other posts in the thread include some good pointers on what you can do
to help with this. The principals are pretty straight forward. Possibly
the most important thing to do is ensure there is no 'anonymous' access
e.g. you cannot login to the database as 'postgres' or some other
generic account which multiple people have access to. Instead, ensure
that everyone with any level of administrator privilege has to login
using an account which is specific to them and not shared. The second
thing to do is ensure the logging level is appropriate and that all
logging is also stored/recorded on a system which the administrator does
not have access to and ensure the level of privileges every
individual has is at the minimum they require to get the job done. It is
also important that logs and audit trails are regularly reviewed to
ensure nobody is abusing the system and all controls are still
appropriate (things change, new systems come on line, old ones a
retired, business processes change etc).

If necessary, consider controls which restrict access to accounts with
extended privileges to certain hosts e.g. DBA 'Phil' can only log into
the database from server xxx.xxx.xxx.xxx and he can only log into that
server between 9am and 5pm Mon - Fri etc. Maybe he has to use a hardware
token etc. 

In most cases, provided you can give strong guarantee that unauthorised
data access can be identified, you will satisfy the security
requirements and this is often far more feasible than outright blocking
of access. 

Finally, it is also important that all staff are aware of the
organisations policies, procedures and controls regarding data
access. They need to know what is expected of them and what is
unacceptable.  


--
Tim Cross

Re: Re: How to revoke privileged from PostgreSQL's superuser

[bejita0409]

Hi all,

Thanks for giving a lot of points of view.  
I know superuser can not be revoked apart of privileges, 
because it does not like nosuperusers who's privileges can be made from GRANT 
statement. 

As you all mentioned, I will re-check more about our system designation.
I am inclined to encrypt sensitive data or using some monitoring softs like 
pgaudit to monitor accesses. 


Thanks,
--
bejita

--- evanba...@mac.com wrote --- :
> Bejita,
> 
> I suggest you step back and think about the problem from the point of view of 
> the desired security outcome ― that of protecting data from improper use by 
> administrators.  Some of the elements that (to my mind) ought to be part of 
> achieving that outcome are:
> 
> 1. Determine and document your organizations data access policies.  They 
> could be very simple, but it is important to document and share them.
> 2. Make use of a privileged access management scheme so that no one has 
> unfettered access to superuser (postgres, root, et al) passwords, but has to 
> check them out from an audited system for a specific task and time period, 
> with appropriate approval processes if needed.
> 3. Use pgaudit to maintain an independent record of all sensitive access. The 
> doc is at: https://github.com/pgaudit/pgaudit/blob/master/README.md
> 4. Create a set of administrative roles privileged to only the needs of the 
> tasks required.  Under normal circumstances, no one should use the ‘postgres’ 
> account for production access.  This also provides a means of enforcing 
> compliance to your policies.  Tom Vondra wrote a good introduction here: 
> https://blog.2ndquadrant.com/auditing-users-and-roles-in-postgresql/
> 5. Setup automated (I tend to use ELK or Splunk) examination of the audit 
> logs for violations and anomalies.  Human review at regular intervals will 
> also make your regulators or security auditors happier (they are never really 
> happy.)
> 6. Make use of row-level access control and encryptions as appropriate to 
> protect your data.  This blog post by Jonathan Katz is a good introduction: 
> https://info.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies
>  
> 
> There is a lot of thought and work that goes into executing the steps above, 
> but administering systems and databases that handle sensitive data is a 
> serious responsibility and requires requirements definition, planning, 
> architecture, execution, and then continuous monitoring and improvement.  As 
> someone new to the DBA role, you should talk to your architecture colleagues 
> as you have some good and serious work ahead of you.
> 
> Cheers,
> 
> - Evan
> 
> 
> 
> > On Aug 6, 2018, at 09:43, Tom Lane  wrote:
> > 
> > Bear Giles  writes:
> >> In postgresql the equivalent user is 'postgres'. Nobody should ever be
> >> logged in as that user once you've created the initial user(s). What
> >> postgresql calls a 'superuser' is just a user with a few permissions set by
> >> default. It's easy to grant the same privileges to any user, or drop them
> >> from someone created as a superuser.
> > 
> > Well, more to the point, a superuser is somebody with the rolsuper bit
> > set in their pg_authid entry.  You can revoke the bootstrap superuser's
> > superuserness if you have a mind to -- see ALTER USER.  However, as
> > everyone has pointed out already, this is a bad idea and you will end
> > up undoing it.  (Figuring out how to do that without a reinstall is left
> > as penance for insisting on a bad idea.  It is possible, and I think
> > even documented.)
> > 
> > However: a whole lot of what the bootstrap superuser can do is inherent
> > in being the owner of all the built-in database objects, and that you
> > cannot get rid of.  Objects have to be owned by somebody.
> > 
> > regards, tom lane
> > 
> 
> 
> 

Immutable function WAY slower than Stable function?

[Ken Tanzer]

Hi.  I was recently troubleshooting a function, and realized it had
incorrectly been declared as Immutable, when it should have been declared
Stable.  When I changed it to Stable, the query I was running ran
dramatically faster. Digging into this a little more, this is what I found:

I've got a function (staff_inspector) that takes two arguments, an integer
and a date.

I've got a convenience function that takes just an integer, and fills in
the date.  (With a Stable function target_date()).

There is no performance difference between the two functions if both
arguments are supplied.

If I use the convenience function however, the difference is dramatic.  The
Stable version clocks in around 1.3 seconds, and the immutable version at
around 23 seconds.

So I'm wondering if this is expected behavior, and if someone could explain
it to me.  Also, if it is expected, I'm wondering whether it's worth noting
in Section 36.6 ("Function Volatility Categories"), which led me to believe
I might be risking a stale value by marking something as Immutable, but
offers no hint about a ginormous performance penalty.

Here's some more detail and information.

I created two versions of the underlying function.
(staff_inspector_stable,staff_inspector_imm).  There is no noticeable
performance difference between them.  I created two versions of the
convenience function, si_stable and si_imm.  They are identical, except for
being declared Stable or Immutable.

The first time I run any of these queries, they take about two seconds.
All subsequent calls take about 1.3 seconds.  Except the Immutable version,
which clocks in at 20+ seconds.  This is true whether it calls the Stable
or Immutable version of the underlying function:

spc=> EXPLAIN ANALYZE SELECT
client_id,staff_inspector_stable(client_id,target_date()) FROM
tbl_residence_own;
  QUERY PLAN
---
 Seq Scan on tbl_residence_own  (cost=0.00..3734.27 rows=6977 width=8)
(actual time=8.311..1990.601 rows=6983 loops=1)
 Planning time: 1.976 ms
 Execution time: 2001.247 ms
(3 rows)

spc=> EXPLAIN ANALYZE SELECT
client_id,staff_inspector_stable(client_id,target_date()) FROM
tbl_residence_own;
  QUERY PLAN
---
 Seq Scan on tbl_residence_own  (cost=0.00..3734.27 rows=6977 width=8)
(actual time=3.472..1298.537 rows=6983 loops=1)
 Planning time: 0.279 ms
 Execution time: 1310.831 ms
(3 rows)

spc=> EXPLAIN ANALYZE SELECT
client_id,staff_inspector_imm(client_id,target_date()) FROM
tbl_residence_own;
  QUERY PLAN
---
 Seq Scan on tbl_residence_own  (cost=0.00..3734.27 rows=6977 width=8)
(actual time=3.780..1299.082 rows=6983 loops=1)
 Planning time: 0.308 ms
 Execution time: 1311.379 ms
(3 rows)

spc=> EXPLAIN ANALYZE SELECT client_id,si_stable(client_id) FROM
tbl_residence_own;
  QUERY PLAN
---
 Seq Scan on tbl_residence_own  (cost=0.00..3734.27 rows=6977 width=8)
(actual time=3.145..1300.551 rows=6983 loops=1)
 Planning time: 0.281 ms
 Execution time: 1312.762 ms
(3 rows)
*spc=> EXPLAIN ANALYZE SELECT client_id,si_imm(client_id) FROM
tbl_residence_own;
*   QUERY PLAN

 Seq Scan on tbl_residence_own  (cost=0.00..1990.02 rows=6977 width=8)
(actual time=3.537..22892.481 rows=6983 loops=1)
 Planning time: 0.079 ms* Execution time: 22903.504 ms
*(3 rows)

spc=> EXPLAIN ANALYZE SELECT client_id,si_stable_calls_imm(client_id)
FROM tbl_residence_own;
  QUERY PLAN
---
 Seq Scan on tbl_residence_own  (cost=0.00..3734.27 rows=6977 width=8)
(actual time=2.907..1291.235 rows=6983 loops=1)
 Planning time: 0.223 ms
 Execution time: 1303.488 ms
(3 rows)
*spc=> EXPLAIN ANALYZE SELECT client_id,si_imm_calls_imm(client_id)
FROM tbl_residence_own;
*   QUERY PLAN

 Seq Scan on tbl_residence_own  (cost=0.00..1990.02 rows=6977 width=8)
(actual time=3.664..22868.734 rows=6983 loops=1)
 Planning time: 0.134 ms* Execution time: 22879.761 ms
*(3 rows)

Re: Immutable function WAY slower than Stable function?

[Tom Lane]

Ken Tanzer  writes:
> Hi.  I was recently troubleshooting a function, and realized it had
> incorrectly been declared as Immutable, when it should have been declared
> Stable.  When I changed it to Stable, the query I was running ran
> dramatically faster. Digging into this a little more, this is what I found:

EXPLAIN VERBOSE might shed a bit more light.  I suspect you're falling
foul of the rather arcane rules about whether a SQL function can be
inlined.  (Although it seems like only the wrapper function would be
inline-able anyway, so I'm not sure why the penalty is that high.)

regards, tom lane

Re: Immutable function WAY slower than Stable function?

[Ken Tanzer]

On Mon, Aug 6, 2018 at 4:11 PM Tom Lane  wrote:

> Ken Tanzer  writes:
> > Hi.  I was recently troubleshooting a function, and realized it had
> > incorrectly been declared as Immutable, when it should have been declared
> > Stable.  When I changed it to Stable, the query I was running ran
> > dramatically faster. Digging into this a little more, this is what I
> found:
>
> EXPLAIN VERBOSE might shed a bit more light.  I suspect you're falling
> foul of the rather arcane rules about whether a SQL function can be
> inlined.  (Although it seems like only the wrapper function would be
> inline-able anyway, so I'm not sure why the penalty is that high.)
>
> regards, tom lane
>

Thanks Tom.  This was as talkative as I could get it.  I do see the
difference on the Output line, but I don't know how to interperet it.

spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT
client_id,si_imm(client_id) FROM tbl_residence_own;
  QUERY PLAN
---
 Seq Scan on public.tbl_residence_own  (cost=0.00..1990.02 rows=6977
width=8) (actual time=3.771..22665.604 rows=6983 loops=1)
   Output: client_id, si_imm(client_id)
   Buffers: shared hit=199814
 Planning time: 0.156 ms
 Execution time: 22677.333 ms
(5 rows)

spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT
client_id,si_stable(client_id) FROM tbl_residence_own;
  QUERY PLAN
--
 Seq Scan on public.tbl_residence_own  (cost=0.00..3734.27 rows=6977
width=8) (actual time=3.100..1302.888 rows=6983 loops=1)
   Output: client_id, staff_inspector_stable(client_id, target_date())
   Buffers: shared hit=60174
 Planning time: 0.354 ms
 Execution time: 1315.746 ms
(5 rows)


Cheers,
Ken

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Immutable function WAY slower than Stable function?

[Adrian Klaver]

On 08/06/2018 03:49 PM, Ken Tanzer wrote:
Hi.  I was recently troubleshooting a function, and realized it had 
incorrectly been declared as Immutable, when it should have been 
declared Stable.  When I changed it to Stable, the query I was running 
ran dramatically faster. Digging into this a little more, this is what I 
found:






Happy to provide any additional relevant info, or for someone to point 
out what obvious thing I'm overlooking.  Thanks in advance!


What is the definition for target_date()?



Ken



--



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

Re: Immutable function WAY slower than Stable function?

[Ken Tanzer]

On Mon, Aug 6, 2018 at 4:36 PM Adrian Klaver 
wrote:

>
> What is the definition for target_date()?
>

Hi Adrian.  Happy to provide this info.  Though on a side note, I don't
understand why it should matter, if functions are black box optimization
fences.  But here are the definitions:

CREATE OR REPLACE FUNCTION target_date() RETURNS date AS $$
SELECT target_date FROM target_date_current;
$$ LANGUAGE sql STABLE;

The target_date table and views:

CREATE TABLE tbl_target_date (
target_date_id  SERIAL PRIMARY KEY,
target_date DATE NOT NULL,
effective_atTIMESTAMP NOT NULL DEFAULT current_timestamp,
comment TEXT,
--system fields
added_byINTEGER NOT NULL REFERENCES tbl_staff (staff_id),
added_atTIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
changed_by  INTEGER NOT NULL  REFERENCES tbl_staff (staff_id),
changed_at  TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_deleted  BOOLEAN NOT NULL DEFAULT FALSE,
deleted_at  TIMESTAMP(0),
deleted_by  INTEGER REFERENCES tbl_staff(staff_id),
deleted_comment TEXT,
sys_log TEXT
);

CREATE VIEW target_date AS SELECT * FROM tbl_target_date WHERE NOT is_deleted;

CREATE VIEW target_date_current AS
SELECT * FROM target_date ORDER BY effective_at DESC LIMIT 1;

CREATE OR REPLACE FUNCTION target_date_no_edit_or_delete() RETURNS TRIGGER AS $$

BEGIN
IF (TG_OP <> 'INSERT')
THEN RAISE EXCEPTION 'Target records cannot be changed or deleted.
(Attempted operation: %)',TG_OP;
END IF;
IF (NEW.target_date <> date_trunc('month',NEW.target_date))
THEN RAISE EXCEPTION 'Target date must be the first of a month';
END IF;
IF (NEW.target_date <= target_date())
THEN RAISE EXCEPTION 'Target date can only be moved forward';
END IF;
RETURN NEW;
END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER protect_target_date BEFORE INSERT OR UPDATE OR DELETE
ON tbl_target_date FOR EACH ROW EXECUTE PROCEDURE
target_date_no_edit_or_delete();
CREATE TRIGGER target_date_no_trunacte BEFORE TRUNCATE ON
tbl_target_date FOR STATEMENT EXECUTE PROCEDURE
target_date_no_edit_or_delete();

And the tbl_residence_own, which was referenced in my sample queries:

spc=> \d tbl_residence_own
   Table
"public.tbl_residence_own"
Column|  Type  |
   Modifiers
--++--
 residence_own_id | integer| not
null default nextval('tbl_residence_own_residence_own_id_seq'::regclass)
 client_id| integer| not null
 housing_project_code | character varying(10)  | not null
 housing_unit_code| character varying(10)  | not null
 residence_date   | date   | not null
 residence_date_end   | date   |
 unit_rent_manual | numeric(7,2)   |
 utility_allowance_manual | numeric(7,2)   |
 is_active_manual | boolean| not
null default true
 was_received_hap | boolean|
 was_received_compliance  | boolean|
 moved_from_code  | character varying(10)  |
 chronic_homeless_status_code | character varying(10)  |
 lease_on_file| boolean|
 moved_to_code| character varying(10)  |
 departure_type_code  | character varying(10)  |
 departure_reason_code| character varying(10)  |
 move_out_was_code| character varying(10)  |
 returned_homeless| boolean|
 was_deposit_returned | boolean|
 comment_damage   | text   |
 comment_deposit  | text   |
 comment  | text   |
 old_access_id| character varying  |
 old_utility_allowance| numeric(9,2)   |
 added_by | integer| not null
 added_at | timestamp(0) without time zone | not
null default now()
 changed_by   | integer| not null
 changed_at   | timestamp(0) without time zone | not
null default now()
 is_deleted   | boolean| default false
 deleted_at   | timestamp(0) without time zone |
 deleted_by   | integer|
 deleted_comment  | tex

Re: Immutable function WAY slower than Stable function?

[Adrian Klaver]

On 08/06/2018 04:44 PM, Ken Tanzer wrote:
On Mon, Aug 6, 2018 at 4:36 PM Adrian Klaver > wrote:



What is the definition for target_date()?


Hi Adrian.  Happy to provide this info.  Though on a side note, I don't 
understand why it should matter, if functions are black box optimization 


Black boxes the bane of my existence, different field, control 
circuits:) My interest was because you are chaining functions and a 
chain is only as strong as it's weakest link. From the surface I am not 
seeing anything, will need to mull it over a bit.



fences.  But here are the definitions:



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

Re: Re: How to revoke privileged from PostgreSQL's superuser

[Bear Giles]

Very true - when you've been hitting credit card and health care nails you
tend to forget that not every problem requires the same level of hammer!
Ask me what's required for anything in the Hadoop ecosystem. shudder.

However it's also true that there's no such thing as a site or database too
small to attack now. The bad guys have figured out that people are lazy and
getting into a database, any database, can reveal information that can be
used in the next attack. I think worrying about the dba and sysadmin is
misplaced - if you can't trust them you have bigger problems so you need to
vet them carefully and then trust them - but in many places all uniquely
identifiable information (e.g., SSN) or even personally identifiable
information (email address, phone number, street address etc.) have be
encrypted now. Everywhere.

Is it possible that your boss knows about the appropriate requirements in
your industry but doesn't fully understand them or isn't communicating them
well? I could easily see a requirement that the data be encrypted (so
nobody can read it) being confused with a requirement that no user have the
ability have the ability to SELECT from a table. Very different things.


> I'm sure someone will respond to this thread with
all sorts of highly restrictive and powerful controls that will restrict
access to the data

On Mon, Aug 6, 2018 at 4:46 PM,  wrote:

> Hi all,
>
> Thanks for giving a lot of points of view.
> I know superuser can not be revoked apart of privileges,
> because it does not like nosuperusers who's privileges can be made from
> GRANT statement.
>
> As you all mentioned, I will re-check more about our system designation.
> I am inclined to encrypt sensitive data or using some monitoring softs
> like pgaudit to monitor accesses.
>
>
> Thanks,
> --
> bejita
>
> --- evanba...@mac.com wrote --- :
> > Bejita,
> >
> > I suggest you step back and think about the problem from the point of
> view of the desired security outcome ― that of protecting data from
> improper use by administrators.  Some of the elements that (to my mind)
> ought to be part of achieving that outcome are:
> >
> > 1. Determine and document your organizations data access policies.  They
> could be very simple, but it is important to document and share them.
> > 2. Make use of a privileged access management scheme so that no one has
> unfettered access to superuser (postgres, root, et al) passwords, but has
> to check them out from an audited system for a specific task and time
> period, with appropriate approval processes if needed.
> > 3. Use pgaudit to maintain an independent record of all sensitive
> access. The doc is at: https://github.com/pgaudit/
> pgaudit/blob/master/README.md
> > 4. Create a set of administrative roles privileged to only the needs of
> the tasks required.  Under normal circumstances, no one should use the
> ‘postgres’ account for production access.  This also provides a means of
> enforcing compliance to your policies.  Tom Vondra wrote a good
> introduction here: https://blog.2ndquadrant.com/
> auditing-users-and-roles-in-postgresql/
> > 5. Setup automated (I tend to use ELK or Splunk) examination of the
> audit logs for violations and anomalies.  Human review at regular intervals
> will also make your regulators or security auditors happier (they are never
> really happy.)
> > 6. Make use of row-level access control and encryptions as appropriate
> to protect your data.  This blog post by Jonathan Katz is a good
> introduction: https://info.crunchydata.com/blog/a-postgresql-row-level-
> security-primer-creating-large-policies
> >
> > There is a lot of thought and work that goes into executing the steps
> above, but administering systems and databases that handle sensitive data
> is a serious responsibility and requires requirements definition, planning,
> architecture, execution, and then continuous monitoring and improvement.
> As someone new to the DBA role, you should talk to your architecture
> colleagues as you have some good and serious work ahead of you.
> >
> > Cheers,
> >
> > - Evan
> >
> >
> >
> > > On Aug 6, 2018, at 09:43, Tom Lane  wrote:
> > >
> > > Bear Giles  writes:
> > >> In postgresql the equivalent user is 'postgres'. Nobody should ever be
> > >> logged in as that user once you've created the initial user(s). What
> > >> postgresql calls a 'superuser' is just a user with a few permissions
> set by
> > >> default. It's easy to grant the same privileges to any user, or drop
> them
> > >> from someone created as a superuser.
> > >
> > > Well, more to the point, a superuser is somebody with the rolsuper bit
> > > set in their pg_authid entry.  You can revoke the bootstrap superuser's
> > > superuserness if you have a mind to -- see ALTER USER.  However, as
> > > everyone has pointed out already, this is a bad idea and you will end
> > > up undoing it.  (Figuring out how to do that without a reinstall is
> left
> > > as penance for insisting on a bad idea.  It is possible, 

Re: Immutable function WAY slower than Stable function?

[Tom Lane]

Ken Tanzer  writes:
> Hi Adrian.  Happy to provide this info.  Though on a side note, I don't
> understand why it should matter, if functions are black box optimization
> fences.

They aren't, at least not when they are SQL-language functions that
meet the conditions for inlining.  The reason that EXPLAIN VERBOSE
is helpful here is that you can see whether the function got inlined
or not: do you see a call to the function, or a representation of
its body?

regards, tom lane

Re: Immutable function WAY slower than Stable function?

[Ken Tanzer]

On Mon, Aug 6, 2018 at 7:42 PM Tom Lane  wrote:

> Ken Tanzer  writes:
> > Hi Adrian.  Happy to provide this info.  Though on a side note, I don't
> > understand why it should matter, if functions are black box optimization
> > fences.
>
> They aren't, at least not when they are SQL-language functions that
> meet the conditions for inlining.


Yeah, I kinda realized after I sent this that I wasn't really making much
sense.  :)  I appreciate your explanation though.

The reason that EXPLAIN VERBOSE
> is helpful here is that you can see whether the function got inlined
> or not: do you see a call to the function, or a representation of
> its body?
>
> I wasn't able to get anything more VERBOSE than what I sent before.  I
re-copied it below.  (If there's another command or option I should be
using, please advise.)

There is the difference in the two output lines, which I guess suggests
that the stable one got inlined and the immutable one didn't?

*   Output: client_id, si_imm(client_id)
*

*   Output: client_id, staff_inspector_stable(client_id, target_date())
*


spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_imm(client_id)
FROM tbl_residence_own;

  QUERY PLAN
---
 Seq Scan on public.tbl_residence_own  (cost=0.00..1990.02 rows=6977
width=8) (actual time=3.771..22665.604 rows=6983 loops=1)*   Output:
client_id, si_imm(client_id)
*   Buffers: shared hit=199814
 Planning time: 0.156 ms
 Execution time: 22677.333 ms
(5 rows)

spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT
client_id,si_stable(client_id) FROM tbl_residence_own;
  QUERY PLAN
--
 Seq Scan on public.tbl_residence_own  (cost=0.00..3734.27 rows=6977
width=8) (actual time=3.100..1302.888 rows=6983 loops=1)*   Output:
client_id, staff_inspector_stable(client_id, target_date())
*   Buffers: shared hit=60174
 Planning time: 0.354 ms
 Execution time: 1315.746 ms
(5 rows)

Cheers,
Ken




-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Immutable function WAY slower than Stable function?

[Laurenz Albe]

Ken Tanzer wrote:
> On Mon, Aug 6, 2018 at 4:11 PM Tom Lane  wrote:
> > Ken Tanzer  writes:
> > > Hi.  I was recently troubleshooting a function, and realized it had
> > > incorrectly been declared as Immutable, when it should have been declared
> > > Stable.  When I changed it to Stable, the query I was running ran
> > > dramatically faster. Digging into this a little more, this is what I 
> > > found:
>
> spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_imm(client_id) 
> FROM tbl_residence_own;
>   QUERY PLAN  
>  
> ---
>  Seq Scan on public.tbl_residence_own  (cost=0.00..1990.02 rows=6977 width=8) 
> (actual time=3.771..22665.604 rows=6983 loops=1)
>Output: client_id, si_imm(client_id)
>Buffers: shared hit=199814
>  Planning time: 0.156 ms
>  Execution time: 22677.333 ms
> (5 rows)
> 
> spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_stable(client_id) 
> FROM tbl_residence_own;
>   QUERY PLAN  
> 
> --
>  Seq Scan on public.tbl_residence_own  (cost=0.00..3734.27 rows=6977 width=8) 
> (actual time=3.100..1302.888 rows=6983 loops=1)
>Output: client_id, staff_inspector_stable(client_id, target_date())
>Buffers: shared hit=60174
>  Planning time: 0.354 ms
>  Execution time: 1315.746 ms
> (5 rows)

That seems to say that the _stable function is running much faster.
Buth functions don't get inlined.

I'd dig into the functions and find out how long the queries in
them take.  auto_explain is a good helper for that.

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