Re: Invoking user of the function with SECURITY DEFINER
Got it.. In my case i was getting session_user in declare section and trying to validate later which always resulted in the function owner. DECLARE user text := SESSION_USER; So using it within the BEGIN; ...; END; clause worked for me. Thanks. Warm Regards, Madan Kumar K <https://about.me/madankumark> *"There is no Elevator to Success. You have to take the Stairs"* On Sun, Nov 25, 2018 at 2:24 AM raf wrote: > Laurenz Albe wrote: > > > Madan Kumar wrote: > > > How to get the user who is invoking the function with SECURITY > DEFINER? > > > When we define the function to be SECURITY DEFINER, it will execute in > the > > > context of the user who created it. Let's say I've given execute > permission > > > for this function to other users and wish to know who is executing it. > > > Is there a way to find that out? > > > I tried CURRENT_USER and SESSION_USER but they return the function > owner > > > since they execute in that context. So is there any way to figure out > the > > > user who is invoking the function? > > > > It works for me: > > > > As user "postgres": > > > > CREATE OR REPLACE FUNCTION tellme() RETURNS text LANGUAGE plpgsql > >SECURITY DEFINER AS 'BEGIN RETURN session_user; END;'; > > > > As user "laurenz": > > > > SELECT tellme(); > > tellme > > - > > laurenz > > (1 row) > > > > Yours, > > Laurenz Albe > > session_user has always worked for me. > > cheers, > raf > > >
Invoking user of the function with SECURITY DEFINER
Hi, How to get the user who is invoking the function with SECURITY DEFINER? When we define the function to be SECURITY DEFINER, it will execute in the context of the user who created it. Let's say I've given execute permission for this function to other users and wish to know who is executing it. Is there a way to find that out? I tried CURRENT_USER and SESSION_USER but they return the function owner since they execute in that context. So is there any way to figure out the user who is invoking the function? Warm Regards, Madan Kumar K <https://about.me/madankumark> *"There is no Elevator to Success. You have to take the Stairs"*
Re: How to change standby node to sync from the new master without rebooting the PostgreSQL service?
It's not feasible since my deployments are in cloud. We make use of floating IP for DB clients, but usage of same for within cluster communication will be slight overhead. Warm Regards, Madan Kumar K <https://about.me/madankumark> *"There is no Elevator to Success. You have to take the Stairs"* On Tue, Oct 30, 2018 at 7:57 PM Scot Kreienkamp < scot.kreienk...@la-z-boy.com> wrote: > Why is it not feasible? How do your DB clients know to switch to the new > master? > > > > I’m using pcs clustering in my environment to manage two production nodes, > automatic failover, and two VIPs (one for master, one for slave). All my > clients point at either the master VIP or the slave VIP. When we have a > role change where the master is moved we do nothing to any clients or > replication slaves, they automatically reconnect in all cases after the pcs > cluster activates the two VIPs. Also in case the primary slave would go > down pcs will move the primary slave VIP to the master so that replication > continues. Makes maintenance easy, I can reboot the primary slave at any > time and everything just continues working. > > > > There is easy to use open source software that will do nothing but VIPs. > I used to use keepalived for that purpose. You define a script that will > let it determine which node to activate the VIP on. In the script have it > check which node is the master, and it will activate that VIP on the > master. When you transition the master to another server the VIP will > travel with the master. > > > > > > *Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate* > One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | > Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com > > *From:* Madan Kumar [mailto:madankumar1...@gmail.com] > *Sent:* Tuesday, October 30, 2018 10:02 AM > *To:* Scot Kreienkamp > *Cc:* pgsql-general@lists.postgresql.org > *Subject:* Re: How to change standby node to sync from the new master > without rebooting the PostgreSQL service? > > > > Thanks Scot. > > But moving VIP is not feasible option for me. > > At present PostgreSQL doesn't support for reloading of recovery.conf > parameters via SIGHUP. To prevent recovery.conf reload for master IP, I can > manage internal DNS to always point to the current master. However there > are some cases where old master will come up as standby before the new > master is elected. In this case it will lead to cascading replication. > > > > So to overcome such cases reboot is a required. It can be achieved by > restarting the wal receiver process too. But there is no straight forward > way of restarting wal receiver process. The only way i figured out is to > kill the wal receiver process. Postmaster will take care of restarting the > wal receiver process. But here my worry is, will there be any side effect > if i kill wal receiver process (even using TERM signal)? > > > > Warm Regards, > > Madan Kumar K <https://about.me/madankumark> > > > > *"There is no Elevator to Success. You have to take the Stairs"* > > > > > > On Tue, Oct 30, 2018 at 6:27 PM Scot Kreienkamp < > scot.kreienk...@la-z-boy.com> wrote: > > Point it at a VIP that travels with the master. > > *From:* Madan Kumar [mailto:madankumar1...@gmail.com] > *Sent:* Tuesday, October 30, 2018 7:20 AM > *To:*pgsql-general@lists.postgresql.org > *Subject:* How to change standby node to sync from the new master without > rebooting the PostgreSQL service? > > > > Hi, > > > > Whenever there is a change in master, PostgreSQL service on standby nodes > must be restarted (after changing the master IP in the recovery.conf) to > ensure it is syncing with the new master. > > Is there a way to point to new master without reboot of PostgreSQL on the > standby? > > > > Warm Regards, > > Madan Kumar K <https://about.me/madankumark> > > > > *"There is no Elevator to Success. You have to take the Stairs"* > > This message is intended only for the individual or entity to which it is > addressed. It may contain privileged, confidential information which is > exempt from disclosure under applicable laws. If you are not the intended > recipient, you are strictly prohibited from disseminating or distributing > this information (other than to the intended recipient) or copying this > information. If you have received this communication in error, please > notify us immediately by e-mail or by telephone at the above number. Thank > you. > >
Re: How to change standby node to sync from the new master without rebooting the PostgreSQL service?
Thanks Scot. But moving VIP is not feasible option for me. At present PostgreSQL doesn't support for reloading of recovery.conf parameters via SIGHUP. To prevent recovery.conf reload for master IP, I can manage internal DNS to always point to the current master. However there are some cases where old master will come up as standby before the new master is elected. In this case it will lead to cascading replication. So to overcome such cases reboot is a required. It can be achieved by restarting the wal receiver process too. But there is no straight forward way of restarting wal receiver process. The only way i figured out is to kill the wal receiver process. Postmaster will take care of restarting the wal receiver process. But here my worry is, will there be any side effect if i kill wal receiver process (even using TERM signal)? Warm Regards, Madan Kumar K <https://about.me/madankumark> *"There is no Elevator to Success. You have to take the Stairs"* On Tue, Oct 30, 2018 at 6:27 PM Scot Kreienkamp < scot.kreienk...@la-z-boy.com> wrote: > Point it at a VIP that travels with the master. > > > > *Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate* > One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | > Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com > > *From:* Madan Kumar [mailto:madankumar1...@gmail.com] > *Sent:* Tuesday, October 30, 2018 7:20 AM > *To:* pgsql-general@lists.postgresql.org > *Subject:* How to change standby node to sync from the new master without > rebooting the PostgreSQL service? > > > > Hi, > > > > Whenever there is a change in master, PostgreSQL service on standby nodes > must be restarted (after changing the master IP in the recovery.conf) to > ensure it is syncing with the new master. > > Is there a way to point to new master without reboot of PostgreSQL on the > standby? > > > > Warm Regards, > > Madan Kumar K <https://about.me/madankumark> > > > > *"There is no Elevator to Success. You have to take the Stairs"* > > This message is intended only for the individual or entity to which it is > addressed. It may contain privileged, confidential information which is > exempt from disclosure under applicable laws. If you are not the intended > recipient, you are strictly prohibited from disseminating or distributing > this information (other than to the intended recipient) or copying this > information. If you have received this communication in error, please > notify us immediately by e-mail or by telephone at the above number. > Thank you. >
How to change standby node to sync from the new master without rebooting the PostgreSQL service?
Hi, Whenever there is a change in master, PostgreSQL service on standby nodes must be restarted (after changing the master IP in the recovery.conf) to ensure it is syncing with the new master. Is there a way to point to new master without reboot of PostgreSQL on the standby? Warm Regards, Madan Kumar K <https://about.me/madankumark> *"There is no Elevator to Success. You have to take the Stairs"*
