[Beginner Question] Will the vxid replace xid?
Hi community, When I read the newest document, I find there seems to be a new concept "vxid", and It's 64 bit. Is there a plan to replace xid? Or just my careless? Can someone give me some advice? Thanks in advance! Yours, Wen Yi
RE: How to add function schema in search_path in option definitio
Hi, We have hit similar issues with the software that we develop. We don’t specify the schema names in stored procedures/functions we create. The way we resolved it was to define the schemas where the stored procedures/functions are located in the search path as part of the connection string we use. In our case, we connect using JDBC, so the search path is defined in the currentSchema argument. We needed to do this because one of the things our installation teams do is copy schemas used in one environment, such as a production environment, to another environment, such as a test environment. When that is done, the schemas are renamed as per our installation standards. If we hardcoded the schema names in the stored procedures/functions, the installer would have to manually adjust the schema names used. Thanks, Joel From: David G. Johnston Sent: Tuesday, July 11, 2023 4:51 PM To: Lorusso Domenico Cc: Adrian Klaver ; pgsql-general@lists.postgresql.org Subject: Re: How to add function schema in search_path in option definitio ATTENTION: This email originated from outside of Tecsys. Use caution when clicking links or opening attachments. | Ce courriel provient de l'extérieur de Tecsys. Soyez prudent lorsque vous cliquez sur des liens ou ouvrez des pièces jointes. On Sat, Jul 8, 2023 at 10:00 AM Lorusso Domenico mailto:domenico@gmail.com>> wrote: Hello Adrian, I've created a schema to handle some specific features. In the schema there are a couple of tables used by many functions (more than 20). In other words, I've created a schema as a package (as suggested in many points). I wish, in a function of this schema, to be able to call each other functions of this schema without adding the schema name in the call instruction. PostgreSQL isn't really designed for that - especially if you aren't formally creating an extension but simply putting stuff into a schema. The script code you use to install your makeshift package should handle dynamic schema naming. It's a pain, do you really need to allow the name of the schema to be unknown at installation time? You should read up on how extensions are implemented - you are basically writing your own CREATE EXTENSION implementation. https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION In any case, there really aren't any smarts here: explicitly schema qualify your function calls and forget that search_path even exists. Unless you are writing custom operators, and even then, consider search_path to be evil. David J.
Re: Requesting Information for GSSAPI
Greetings, * Lingesan Jeyapandy (lingesan.jeyapa...@gilead.com) wrote: > We have configured postgres GSSAPI setup on Linux server. We have huge > domain AD users in our org. > > But we are looking to limit access only to setup AD distributed groups. Is > there any way to control access only at AD distribution list user can only > access gssapi configured postgres environment. > > Our postgres version is 15. To be able to log into the PG server, an account in PG has to exist. If it doesn't, then the user won't be able to log in. There are some options for sync'ing specific AD groups into PG accounts, eg: https://github.com/larskanis/pg-ldap-sync Thanks, Stephen signature.asc Description: PGP signature
Reset Postgresql users password
Hello, I wish to find out if there is a way to reset all users in Postgresql password to the same password at once. Thank you Tiamoh J
Re: Reset Postgresql users password
On 2023-07-12 Johnathan Tiamoh wrote: > I wish to find out if there is a way to reset all users in Postgresql > password to the same password at once. I guess you could update the `pg_catalog.pg_authid` table, see https://www.postgresql.org/docs/15/catalog-pg-authid.html -- Dakkar - GPG public key fingerprint = A071 E618 DD2C 5901 9574 6FE2 40EA 9883 7519 3F88 key id = 0x75193F88
Re: Reset Postgresql users password
You can also just write an sql and execute it, like: select 'alter user '|| usename ||' with password ''newpassword'';' from pg_user; \gexec Cheers, Mateusz śr., 12 lip 2023 o 21:35 Gianni Ceccarelli napisał(a): > On 2023-07-12 Johnathan Tiamoh wrote: > > I wish to find out if there is a way to reset all users in Postgresql > > password to the same password at once. > > I guess you could update the `pg_catalog.pg_authid` table, see > https://www.postgresql.org/docs/15/catalog-pg-authid.html > > -- > Dakkar - > GPG public key fingerprint = A071 E618 DD2C 5901 9574 > 6FE2 40EA 9883 7519 3F88 > key id = 0x75193F88 > > > >
Re: Reset Postgresql users password
On Wed, Jul 12, 2023 at 12:42 PM Mateusz Henicz wrote:
>
> You can also just write an sql and execute it, like:
>
> select 'alter user '|| usename ||' with password ''newpassword'';' from
> pg_user;
> \gexec
Note that the above assumes you're using psql.
For tools other than psql, you'd have to use dynamic SQL something
like the DO block below. It will work on psql, just as well.
DO $$
declare
rec record;
begin
for rec in select
'alter user '|| quote_ident(usename)
||' with password '|| quote_literal('newpassword')
as c from pg_user
loop
raise notice 'Executing command: %', rec.c;
execute rec.c;
end loop;
end;
$$;
Best regards,
Gurjeet
http://Gurje.et
Re: Reset Postgresql users password
Thank you all very much.
I really appreciate !!
On Wed, Jul 12, 2023 at 4:17 PM Gurjeet Singh wrote:
> On Wed, Jul 12, 2023 at 12:42 PM Mateusz Henicz
> wrote:
> >
> > You can also just write an sql and execute it, like:
> >
> > select 'alter user '|| usename ||' with password ''newpassword'';' from
> pg_user;
> > \gexec
>
> Note that the above assumes you're using psql.
>
> For tools other than psql, you'd have to use dynamic SQL something
> like the DO block below. It will work on psql, just as well.
>
> DO $$
> declare
> rec record;
> begin
> for rec in select
> 'alter user '|| quote_ident(usename)
> ||' with password '|| quote_literal('newpassword')
> as c from pg_user
> loop
> raise notice 'Executing command: %', rec.c;
> execute rec.c;
> end loop;
> end;
> $$;
>
> Best regards,
> Gurjeet
> http://Gurje.et
>
>
>
Re: Reset Postgresql users password
On Wed, Jul 12, 2023 at 1:17 PM Gurjeet Singh wrote:
> for rec in select
> 'alter user '|| quote_ident(usename)
> ||' with password '|| quote_literal('newpassword')
>
>
Which is more clearly written using the format function:
...
for rec in select
format('alter user %I with password %L', usename, 'newpassword')
from pg_user
loop
...
David J.
Re: How to add function schema in search_path in option definitio
eh.. yes to be honest what I create is more similar to an extension rather than a package, I don't believe to be able to create extensions in cloud sql (the actual target db). However , is there a way to share this preliminar "extension" with the community? I'm interested to discuss about solution used For example: the software creates a custom type to manage bitemporality and audit_record inline for each table; but another approach is to use a primitive table inherited by all tables. I'm quite sure the composite type has one or two fields I can remove. etc :-D Il giorno mar 11 lug 2023 alle ore 22:51 David G. Johnston < david.g.johns...@gmail.com> ha scritto: > On Sat, Jul 8, 2023 at 10:00 AM Lorusso Domenico > wrote: > >> Hello Adrian, >> I've created a schema to handle some specific features. >> In the schema there are a couple of tables used by many functions (more >> than 20). >> In other words, I've created a schema as a package (as suggested in many >> points). >> >> I wish, in a function of this schema, to be able to call each other >> functions of this schema without adding the schema name in the call >> instruction. >> > > PostgreSQL isn't really designed for that - especially if you aren't > formally creating an extension but simply putting stuff into a schema. > > The script code you use to install your makeshift package should handle > dynamic schema naming. It's a pain, do you really need to allow the name > of the schema to be unknown at installation time? > > You should read up on how extensions are implemented - you are basically > writing your own CREATE EXTENSION implementation. > > > https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION > > In any case, there really aren't any smarts here: explicitly schema > qualify your function calls and forget that search_path even exists. > Unless you are writing custom operators, and even then, consider > search_path to be evil. > > David J. > > -- Domenico L. per stupire mezz'ora basta un libro di storia, io cercai di imparare la Treccani a memoria... [F.d.A.]
EDB to Postgres Migration
List, Recently I happened to have managed a few EDB instances running on the EDB-10 version . I am looking for an option for migrating all these EDB instances to Postgres Community edition. 1. What major steps / actions involved ( in bird's eye view ) for a successful migration to postgres community edition . ( From EDB 10 to Postgres 14 ) 2. What major challenges are involved? (or any hurdles ?) Please enlighten me with your experience.. Any reference links most welcome .. PS: - The EDB instances are live and in production.. I can get a down time ( 5 to 15 Minutes Maximum) Or can live porting and upgrading to postgres 14 is possible with minimal downtime ? Request your guidance, Krishane.
