Re: FW: Re: FW: Re: Shouldn;t this trigger be called?

[stan]

On Thu, Sep 19, 2019 at 03:54:40PM -0600, Michael Lewis wrote:
> You can also look at citext type to avoid the casting.

Oh, that looks really useful I think I will go back and use that type quite
a bit.

Thanks for pointing it out to me.

> 
> customer_keyinteger DEFAULT
> nextval('customer_key_serial') PRIMARY KEY ,
> cust_no smallint NOT NULL UNIQUE ,
> namevarchar UNIQUE ,
> 
> Why do you have a surrogate primary key generated by a sequence when you
> have a natural key of either cust_no or name? Why not just declare the
> customer number to be the PK? 

At the moment, the customer (who is a small startup) really does not have a
customer number. It is really a place holder at the moment, with the
sequence being the "real" key. For all I know, the customer number may be
alphanumeric. in the final implementation.

> Where does customer number come from anyway?
> Using smallint seems potentially short-sighted on potential future growth,
> but changing the type later should be minimal work as long as you don't
> have this customer_number denormalized many places, or use it as the FKey
> after dropping customer_key surrogate key.

Thanks for your suggestion.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: FW: Re: FW: Re: Shouldn;t this trigger be called?

[Morris de Oryx]

I see that you've already been pointed at citext, but I don't think a CHECK
constraint has been mentioned. In case it hasn't, what about something like
this?

   ADD CONSTRAINT check_activity_status
CHECK (activity_status = 'ACTIVE' OR activity_status = 'INACTIVE');

I'm kind of allergic to ENUM...maybe that's just me. But since you're
considering it, maybe it's the perfect time to consider all of your
options. Such as a linked lookup table of defined allowed values (feels
silly with two values), a domain (not entirely fit to purpose), or the
CHECK constraint above. And, yeah, if it's only ever ACTIVE or INACTIVE,
I'd normally make a Boolean named something like active, as Adrian Klaver
mentioned. That's easy to reason about, and it makes it unambiguous that
there are two and only two possible states..

Re: FW: Re: FW: Re: Shouldn;t this trigger be called?

[stan]

On Thu, Sep 19, 2019 at 03:54:40PM -0600, Michael Lewis wrote:
> You can also look at citext type to avoid the casting.
> 
> customer_keyinteger DEFAULT
> nextval('customer_key_serial') PRIMARY KEY ,
> cust_no smallint NOT NULL UNIQUE ,
> namevarchar UNIQUE ,
> 
I am confysed. I am running version 11 which is current I beleive, but when
I try  to use this type, I get:

ERROR:  type "citext" does not exist
LINE 8: unit  citext UNIQUE NOT NULL ,

 Do I somehow need to enable this type?
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: FW: Re: FW: Re: Shouldn;t this trigger be called?

[stan]

On Fri, Sep 20, 2019 at 08:52:00PM +1000, Morris de Oryx wrote:
> I see that you've already been pointed at citext, but I don't think a CHECK
> constraint has been mentioned. In case it hasn't, what about something like
> this?
> 
>ADD CONSTRAINT check_activity_status
> CHECK (activity_status = 'ACTIVE' OR activity_status = 'INACTIVE');
> 
> I'm kind of allergic to ENUM...maybe that's just me. But since you're
> considering it, maybe it's the perfect time to consider all of your
> options. Such as a linked lookup table of defined allowed values (feels
> silly with two values), a domain (not entirely fit to purpose), or the
> CHECK constraint above. And, yeah, if it's only ever ACTIVE or INACTIVE,
> I'd normally make a Boolean named something like active, as Adrian Klaver
> mentioned. That's easy to reason about, and it makes it unambiguous that
> there are two and only two possible states..

Thanks you.

I actually have a number of these cases, and I sullied the simplest one,
which just has 2 values. I guess my "C: background is showing here.

I do have some similar situations where I did use a table of allowed
conditions. I am thinking citext may be the best solution here.

I am having an issue getting it to work, though. I don't have to do
anything special to enable this type, do I?

What I am really trying to do is "human proof" this input :-)

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Web users as database users?

[David Gallagher]

Hi - I’m working on a database that will be accessed via a web app. I’m
used to a setup where there is one account/role that the app would use to
access the database, but in my current scenario I’m interested in row level
security and auditing enforced by the database. In this scenario, would it
make sense to have a user account on the database to mirror the user
account from the web app? Is that an unusual practice?

Re: Web users as database users?

[Tim Clarke]

On 20/09/2019 12:50, David Gallagher wrote:
> Hi - I’m working on a database that will be accessed via a web app.
> I’m used to a setup where there is one account/role that the app would
> use to access the database, but in my current scenario I’m interested
> in row level security and auditing enforced by the database. In this
> scenario, would it make sense to have a user account on the database
> to mirror the user account from the web app? Is that an unusual practice?


Not at all, we're doing it


Tim Clarke



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Re: Web users as database users?

[Achilleas Mantzios]

On 20/9/19 2:50 μ.μ., David Gallagher wrote:
Hi - I’m working on a database that will be accessed via a web app. I’m used to a setup where there is one account/role that the app would use to access the database, but in my current scenario I’m 
interested in row level security and auditing enforced by the database. In this scenario, would it make sense to have a user account on the database to mirror the user account from the web app? Is 
that an unusual practice?


I never regretted it. Just place pgbouncer in front and configure it right.


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: FW: Re: FW: Re: Shouldn;t this trigger be called?

[Morris de Oryx]

citext is an extension, so you have to install it:

CREATE EXTENSION citext;

That's the simplest form. you can install it into a specific schema, test
for existence, etc. Check out the CREATE EXTENSION docs here:

https://www.postgresql.org/docs/current/sql-createextension.html

Re: Web users as database users?

[Rob Sargent]

> On Sep 20, 2019, at 6:15 AM, Tim Clarke  wrote:
> 
>> On 20/09/2019 12:50, David Gallagher wrote:
>> Hi - I’m working on a database that will be accessed via a web app.
>> I’m used to a setup where there is one account/role that the app would
>> use to access the database, but in my current scenario I’m interested
>> in row level security and auditing enforced by the database. In this
>> scenario, would it make sense to have a user account on the database
>> to mirror the user account from the web app? Is that an unusual practice?
> 
> 
> Not at all, we're doing it
> 
> 
> Tim Clarke
> 
But you likely want a many-to-one mapping of actual user to permission group

Re: Web users as database users?

[Tom Lane]

Rob Sargent  writes:
>> On Sep 20, 2019, at 6:15 AM, Tim Clarke  wrote:
> On 20/09/2019 12:50, David Gallagher wrote:
>>> ... would it make sense to have a user account on the database
>>> to mirror the user account from the web app? Is that an unusual practice?

>> Not at all, we're doing it

> But you likely want a many-to-one mapping of actual user to permission group

Yeah.  You're likely to end up with a *lot* of user accounts in this
scenario.  There is a restriction on how many distinct GRANTs you can
issue against any one object --- performance will get bad if the ACL
list gets too large.  However, you can add lots of users to any group
role.  So put the users into appropriate group(s) and issue database
permissions on the group level.

regards, tom lane

Fwd: FW: pg_auto_failover

[Sonam Sharma]

Hi,



When I am setting up keeper ,



[postgres@lablnxdb001 ~]$ pg_autoctl run --pgdata /opt/primary

08:51:03 INFO  Managing PostgreSQL installation at "/opt/primary"

08:51:03 INFO  PostgreSQL is running in "/opt/primary" on port 6433

08:51:03 ERROR default_version or installed_version for extension
"pgautofailover" is NULL

08:51:03 FATAL Failed to check version compatibility with the monitor
extension "pgautofailover", see above for details



Its failing with above error.

The version it’s showing is :



[postgres@lablnxdb001 primary]$ pg_autoctl --version

pg_autoctl version 1.0.4



I have installed rpm of version 10.7.



Can someone please help.



*Thanks & Regards,*

*Sonam*

*Infrastructure System Engineering – DB2 UDB/SYBASE*

*[image: cid:image001.png@01D2B91B.44E1A5E0]*


--
NOTICE: All information in and attached to the e-mails below may be
proprietary, confidential, privileged and otherwise protected from improper
or erroneous disclosure. If you are not the sender's intended recipient,
you are not authorized to intercept, read, print, retain, copy, forward, or
disseminate this message. If you have erroneously received this
communication, please notify the sender immediately by phone (704-758-1000)
or by e-mail and destroy all copies of this message electronic, paper, or
otherwise. By transmitting documents via this email: Users, Customers,
Suppliers and Vendors collectively acknowledge and agree the transmittal of
information via email is voluntary, is offered as a convenience, and is not
a secured method of communication; Not to transmit any payment information
E.G. credit card, debit card, checking account, wire transfer information,
passwords, or sensitive and personal information E.G. Driver's license,
DOB, social security, or any other information the user wishes to remain
confidential; To transmit only non-confidential information such as plans,
pictures and drawings and to assume all risk and liability for and
indemnify Lowe's from any claims, losses or damages that may arise from the
transmittal of documents or including non-confidential information in the
body of an email transmittal. Thank you.

pg_auto_failover

[Bhardwaj, SONAM- SONAM R]

Hi,

When I am setting up keeper ,

[postgres@lablnxdb001 ~]$ pg_autoctl run --pgdata /opt/primary
08:51:03 INFO  Managing PostgreSQL installation at "/opt/primary"
08:51:03 INFO  PostgreSQL is running in "/opt/primary" on port 6433
08:51:03 ERROR default_version or installed_version for extension 
"pgautofailover" is NULL
08:51:03 FATAL Failed to check version compatibility with the monitor extension 
"pgautofailover", see above for details

Its failing with above error.
The version it's showing is :

[postgres@lablnxdb001 primary]$ pg_autoctl --version
pg_autoctl version 1.0.4

I have installed rpm of version 10.7.

Can someone please help.

Thanks & Regards,
Sonam
Infrastructure System Engineering - DB2 UDB/SYBASE
[cid:image001.png@01D2B91B.44E1A5E0]


NOTICE: All information in and attached to the e-mails below may be 
proprietary, confidential, privileged and otherwise protected from improper or 
erroneous disclosure. If you are not the sender's intended recipient, you are 
not authorized to intercept, read, print, retain, copy, forward, or disseminate 
this message. If you have erroneously received this communication, please 
notify the sender immediately by phone (704-758-1000) or by e-mail and destroy 
all copies of this message electronic, paper, or otherwise. By transmitting 
documents via this email: Users, Customers, Suppliers and Vendors collectively 
acknowledge and agree the transmittal of information via email is voluntary, is 
offered as a convenience, and is not a secured method of communication; Not to 
transmit any payment information E.G. credit card, debit card, checking 
account, wire transfer information, passwords, or sensitive and personal 
information E.G. Driver's license, DOB, social security, or any other 
information the user wishes to remain confidential; To transmit only 
non-confidential information such as plans, pictures and drawings and to assume 
all risk and liability for and indemnify Lowe's from any claims, losses or 
damages that may arise from the transmittal of documents or including 
non-confidential information in the body of an email transmittal. Thank you.

citext, actually probably using extensions

[stan]

I was pointed to the citext type to help solve an issue for a project I am
working on. I looked at this page:

https://nandovieira.com/using-insensitive-case-columns-in-postgresql-with-citext
But I must be doing something wrong. As the PG superuser, I did this:

postgres=# CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
CREATE EXTENSION

and as the superuser it shows up in the \dx display

BUT as a normal user:

stan=> \dx
List of installed extensions
Name   | Version |   Schema   | Description
  
-+-++--
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural
   language
 (1 row)

 It does not.

 What am I doing incorrectly?



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

pgwatch2 monitoring dashboards

[Bikram MAJUMDAR]

Hi Good Morning Teams,
We are trying to configure pgwatch2 to monitor our postgress database named 
cemtore ( version 10.3).
We have installed the pgwatch2 docker container from GitHub and followed the 
steps under the section 'Steps to configure your database for monitoring'  for 
monitoring statements and CPU loads etc,  that are provided through the helper 
functions.
All the steps - 1.Creation of the pgwatch2 role 2. Creation of  all the helper 
functions as the superuser cemtore   were completed.
  3. Added our database (cemtore) in the pgwatch2 admin 
interface for monitoring using the pgwatch2 role
But, pgwatch2monitoring dashboards  do not show us the statement activities or 
CPU load etc  - and the pgwatch2 log shows errors that it can not access the 
helper functions - for e.g, [cemtore:stat_statements_calls]: pq: relation 
"pg_stat_statements" does not exist etc.
However, when we use the superuser cemtore for monitoring in the admin 
interface, instead of pgwatch2, everything shows up.
We are using postgress 10.3 - do we need to grant any extra access privileges 
,for e.g granting pg_monitor role to pgwatch2  to monitor using pgwatch2 ?
Any help will be greatly appreciated.

Thanks and regards,
Bikram Majumdar
Sr Software Developer/DBA, Aqsacom Inc.
c. 1.972.365.3737
e. bikram.majum...@aqsacom.com
s. BIKRAM MAJUMDAR

[cid:image001.png@01D56FB8.D57A10F0]

Disclaimer. This electronic mail message contains CONFIDENTIAL information 
which is (a) CUSTOMER / SUPPLIER / PARTNER PRIVILEGED COMMUNICATION, WORK 
PRODUCT, PROPRIETARY IN NATURE, OR OTHERWISE PROTECTED BY LAW FROM DISCLOSURE, 
and (b) intended only for the use of the Addressee(s) named herein. If you are 
not an Addressee, or the person responsible for delivering this to an 
Addressee, you are hereby notified that reading, copying, or distributing this 
message is prohibited. If you have received this electronic mail message in 
error, please reply to the sender and take the steps necessary to delete the 
message completely from your computer system. Statement and opinions expressed 
in this e-mail are those of the sender, and do not necessarily reflect those of 
AQSACOM. AQSACOM accepts no liability for any damage caused by any virus/worms 
transmitted by this email.

Re: citext, actually probably using extensions

[Tom Lane]

stan  writes:
> But I must be doing something wrong. As the PG superuser, I did this:
> postgres=# CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
> CREATE EXTENSION

> and as the superuser it shows up in the \dx display

> BUT as a normal user:

> stan=> \dx
> List of installed extensions
> Name   | Version |   Schema   | Description
  
> -+-++--
> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural
>  language
>  (1 row)

>  It does not.

Judging from the prompts you show, you installed the extension
in the "postgres" database, but you need it in the "stan" database.

regards, tom lane

Re: citext, actually probably using extensions

[Rob Sargent]

> On Sep 20, 2019, at 1:12 PM, Tom Lane  wrote:
> 
> stan  writes:
>> But I must be doing something wrong. As the PG superuser, I did this:
>> postgres=# CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
>> CREATE EXTENSION
> 
>> and as the superuser it shows up in the \dx display
> 
>> BUT as a normal user:
> 
>> stan=> \dx
>> List of installed extensions
>> Name   | Version |   Schema   | Description
>  
>> -+-++--
>> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural
>>   language
>> (1 row)
> 
>> It does not.
> 
> Judging from the prompts you show, you installed the extension
> in the "postgres" database, but you need it in the "stan" database.
> 
>regards, tom lane
> 
> 
And if you install the extension in the template1 db it will be included in 
‘create database’ calls. Rather than having to add it by hand each time 

Re: citext, actually probably using extensions

[Morris de Oryx]

Not sure about best practices, but what I'm going is like this:

* Create a schema named extensions.

* Install extensions in this special schema only. I don't put anything else
in there.

* Put the extensions schema early (left) in the search_path for each role.

* Grant execute access permissively on the functions in that schema.

If there's something deeply flawed about this strategy, I'd be keen to hear
about it. On the positive side, I find it simple to understand, maintain,
and explain to other people. YMMV