Re: Enhancement to psql command, feedback.
On Wed, May 9, 2018 at 3:05 AM, Pavel Stehule wrote: > > > 2018-05-09 9:59 GMT+02:00 John McKown : > >> I just wanted to throw this out to the users before I made a complete >> fool of myself by formally requesting it. But I would like what I hope >> would be a minor change (enhancement) to the psql command. If you look on >> this page, https://wiki.postgresql.org/wiki/Shared_Database_Hosting , >> you will see a number of example which look like: >> >> psql -U postgres template1 -f - << EOT >> >> REVOKE ALL ON DATABASE template1 FROM public; >> REVOKE ALL ON SCHEMA public FROM public; >> GRANT ALL ON SCHEMA public TO postgres; >> CREATE LANGUAGE plpgsql; >> >> EOT >> >> >> To me this looks similar to a UNIX shell script. Now, going sideways for >> a second, if someone wanted to create a "self contained" awk script. It >> would look something like: >> >> #!/bin/awk -f >> ... awk code ... >> >> When a user executes the above from the command line, the UNIX system >> runs the program in the first "magic" line as if the user had entered >> "/bin/awk -f ..." where the ... is replaced by the name of the file >> executed followed by the rest of the command line parameters. >> >> I think it would be nice if psql would do the same, mainly for >> "consistency" with other UNIX scripting languages, such as python, perl, & >> gawk. >> > > These languages has defined # as line comment. It is not true for SQL. > For fun, not because I've put considerable thought into it: #!/usr/bin/psql --enable-hash-comment -f ... -m
Re: Question on postgresql.conf
On Tue, Jul 31, 2018 at 9:21 AM, Tom Lane wrote: > "Lu, Dan" writes: > > Question: > > Can be a variable like `hostname` derived from Unix shell or > I have to hardcode the name of the host like " include > /nfs/global/postgres-host123.cnf > > Nope, you'd have to hard-code it. > > Of course, you could have the startup script that you intended to set the > env variable just overwrite $PGDATA/postgresql.conf with the right thing. Or have puppet/chef/ansible/etc. write the correct config file based on your dynamic data. -m
continuation character for meta commands in psql
Greetings, I've searched the internet and also the pgsql-general list for an answer to this question, but couldn't find one. Is there a continuation character for meta commands in psql? I've seen mention that a caret (^) could be used, but it didn't work correctly and the psql man page makes no mention of it. I'd like to do something like: \set PROMPT1 'long prompt code' \ 'other long prompt configurations' \ 'and more still' in my .psqlrc file. What do you folks think? Thanks for any help or guidance! -m
Re: POLL: Adding transaction status to default psql prompt
+1 On Wed, Feb 5, 2020 at 9:15 PM Ahmed, Nawaz (Fuji Xerox Australia) wrote: > > +1 > > -Original Message- > From: Vik Fearing > Sent: Thursday, 6 February 2020 1:55 PM > To: pgsql-general@lists.postgresql.org > Subject: POLL: Adding transaction status to default psql prompt > > Hello, > > I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql. > > The effect of this is: > > - nothing at all when not in a transaction, > - adding a '*' when in a transaction or a '!' when in an aborted > transaction. > > Before making a change to a long-time default, a poll in this group was > requested. > > Please answer +1 if you want or don't mind seeing transaction status by > default in psql or -1 if you would prefer to keep the current default. > > Thanks! > > +1 from me. > -- > Vik Fearing > > > > IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments, is > private and confidential and may contain legally privileged information. It > is for the addressee's attention only. If you are not the intended recipient > and have received this transmission, you must not use, edit, print, copy or > disclose its contents to any person or disseminate the information contained > herein or hereto attached, and you must notify sender immediately by return > email and delete this transmission from your system. Any confidentiality, > privilege or copyright is not waived or lost because this e-mail has been > sent to you in error. We have used reasonable efforts to protect this > transmission from computer viruses and other malicious software, but no > warranty is made and the sender takes no responsibility for any loss or > damage incurred from using this email or the information contained in this > email.
Re: Can we get SQL Server-like cross database queries
On Thu, Jun 4, 2020 at 12:56 AM Laurenz Albe wrote: > On Thu, 2020-06-04 at 16:41 +1200, Thomas Munro wrote: > > There's no doubt it's useful, and it's also part of the SQL spec, > > which says you can do catalog.schema.table. I would guess that we > > might get that as a byproduct of any project to make PostgreSQL > > multithreaded. That mountain moving operation will require us to get > > rid of all the global state that currently ties a whole process to one > > session and one database, and replace it with heap objects with names > > like Session and Database that can be passed around between worker > > threads. > > I am -1 on cross-database queries. > > I think it is a desirable feature to have databases isolated from > each other, so you don't have to worry about a permission you forgot > that allows somebody to access a different database. > Perhaps make it a new right that can be granted and it is disabled by default. Superusers could have it by default. ALTER USER foo WITH CROSSDB | NOCROSSDB Obviously there is much more to flesh out than this. -m
initdb --data-checksums
Hello, I see the --data-checksums option for initdb. Is it possible to use --data-checksums after the cluster has been initialized? I'm guessing "not", but thought I'd ask. I'm running v12 on Debian. Thanks for any help! -m
count(*) vs count(id)
Greetings,
Is there a preferred method of counting rows?
count(*) vs count(field_name)
I have used count(*) for a long time and am hitting an inconsistency. At
least it is an inconsistency to me (I'm sure there is an explanation)...
INCORRECT OUTPUT (not desired results)
$ select calendar.entry, count(*) from generate_series('2020-01-01'::date,
'2021-01-10'::date, '1 day'::interval) as calendar(entry) left join
call_records on calendar.entry = call_records.timestamp::date group by
calendar.entry;
[...]
2020-08-30 00:00:00-05 │ 1
2020-08-31 00:00:00-05 │ 1
[...]
CORRECT OUTPUT (desired results)
$ select calendar.entry, count(id) from
generate_series('2020-01-01'::date, '2021-01-10'::date, '1 day'::interval)
as calendar(entry) left join call_records on calendar.entry =
call_records.timestamp::date group by calendar.entry;
[...]
2020-08-30 00:00:00-05 │ 0
2020-08-31 00:00:00-05 │ 0
[...]
What am I missing between count(*) and count(id)?
Thanks for any help!
-m
Re: count(*) vs count(id)
On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent wrote: [...] > > You got one null from count(*) likely. > What is count(*) counting then? I thought it was rows. -m
Re: count(*) vs count(id)
On Mon, Feb 1, 2021 at 6:35 PM Tom Lane wrote: > Matt Zagrabelny writes: > > On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent > wrote: > >> You got one null from count(*) likely. > > > What is count(*) counting then? I thought it was rows. > > Yeah, but count(id) only counts rows where id isn't null. > I guess I'm still not understanding it... I don't have any rows where id is null: $ select count(*) from call_records where id is null; count ═══ 0 (1 row) Time: 0.834 ms $ select count(id) from call_records where id is null; count ═══ 0 (1 row) Time: 0.673 ms Which field is count(*) counting if it is counting nulls? -m
Re: count(*) vs count(id)
On Mon, Feb 1, 2021 at 7:19 PM Guillaume Lelarge wrote: > > > You're doing a left join, so I guess there's no row where > call_records.timestamp::date = 2020-08-30. That would result with a NULL id. > > Thanks for the excellent analysis everyone. I appreciate it! Here is the documentation (for anyone reading the mailing list in the future...) count(*) bigint number of input rows count(expression) any bigint number of input rows for which the value of expression is not null Have a great night (or equivalent for your TZ). -m
quoted-printable to jsonb
Greetings PG-users, Scenario: I am using FreeRADIUS to insert data to a PG database. One of the pieces of data is an array of key value pairs. The array is encoded with quoted-printable [0]. I'd like to ideally have the key-value pair put into a JSONB field. Here is an example of the data encoded: mdm-tlv=3Ddevice-platform=3Dlinux-64=2Caudit-session-id=3Dc0a87311021f3000601dda71=2Cmdm-tlv=3Dac-user-agent=3DOpenConnect VPN Agent =28NetworkManager=29 v8.10-1 The =3D is an encoded "=", the =2C is an encoded ",", so forth and so on. The encoded comma (=2C) separates the key-value pairs. Are there any functions within PG to properly decode something like what I have into a JSONB field? Thanks for any help! -m [0] https://en.wikipedia.org/wiki/Quoted-printable
Re: A Small psql Suggestion
At the risk of starting a +1 snowball or a divergent argument... On Tue, Jan 31, 2023 at 10:16 AM Raymond Brinzer wrote: > Greetings, > > There is (for me) a small speed bump in psql. I think it's worth > mentioning, minor though it is, because psql is such a polished tool > generally, and because it's something which affects me many, many times a > day. > > As it is, \d is a shortcut for \dtmvs. What I actually want to see, on a > regular basis, are my relations: \dtmv. Most of the time, the sequences > are clutter. If my habits are like most people's in this (and I suspect > they are), excluding sequences from \d would optimize for the common case. > +1 Of course, I don't generally have that many relations that the few sequences that show up are cause for issue, but it would be nice to not have to parse them visually. -m
psql profiles?
Greetings, I'm setting up my workstation to use "gss" for auth to a variety of Pg systems on different hosts. I'd rather not have to specify the "-h" for a connection: psql -h db-host-1.example.com foo I'd rather do: psql foo and have it know that I connect to foo on host db-host-1.example.com. Is this possible with psql or do I hack together some wrapper script? Thanks for any input! -m
Re: psql profiles?
On Thu, Dec 6, 2018 at 4:24 PM Alvaro Herrera wrote: > On 2018-Dec-06, Matt Zagrabelny wrote: > > > I'd rather do: > > > > psql foo > > > > and have it know that I connect to foo on host db-host-1.example.com. > > > > Is this possible with psql or do I hack together some wrapper script? > > Sure, just define a pg_service.conf file. > https://www.postgresql.org/docs/11/libpq-pgservice.html Thanks Alvaro! Is there any shorter version than: psql "service=foo" ? If not, I can make a shell alias that puts the "service=$@" into the command. Thanks again! -m
Re: psql profiles?
On Fri, Dec 7, 2018 at 7:42 AM Arthur Zakirov
wrote:
> On 07.12.2018 01:34, Matt Zagrabelny wrote:
> >
> >
> > On Thu, Dec 6, 2018 at 4:24 PM Alvaro Herrera > <mailto:alvhe...@2ndquadrant.com>> wrote:
> >
> > Sure, just define a pg_service.conf file.
> > https://www.postgresql.org/docs/11/libpq-pgservice.html
> >
> >
> > Thanks Alvaro!
> >
> > Is there any shorter version than:
> >
> > psql "service=foo"
> >
> > ?
> >
> > If not, I can make a shell alias that puts the "service=$@" into the
> > command.
>
>
>
Thanks for the hints and discussion about this.
Here's my final implementation for the curious and to close the loop:
# a zsh function to avoid having to type "service="
$ which pssql
pssql () {
psql "service=$@"
}
# and a zsh completion function:
$ cat ~/.fpath/_pssql
#compdef pssql
PG_SERVICES_CONF=~/.pg_service.conf
if [[ -r ${PG_SERVICES_CONF} ]]; then
compadd $(sed -nE 's/^ *\[(.*)\] *$/\1/p' ${PG_SERVICES_CONF})
fi
It works like a charm!
Thanks for all the help!
-m
Re: Subquery to select max(date) value
Hey Rich, On Tue, Feb 12, 2019 at 4:24 PM Rich Shepard wrote: > The query is to return the latest next_contact date for each person. Using > the max() aggregate function and modeling the example of lo_temp on page 13 > of the rel. 10 manual I wrote this statement: > I use DISTINCT ON and ORDER BY to get the single latest value from a table with multiple date entries: https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by HTH, -m
cast bytea to macaddr
Greetings Pg folks, I have a database schema (not mine) that uses bytea to represent a MAC address. I'd like to create a view that casts that field into a MACADDR type. # \d lease4 Table "public.lease4" Column │ Type │ Collation │ Nullable │ Default ╪══╪═══╪══╪═ hwaddr │ bytea│ │ │ # select hwaddr from lease4; hwaddr \x9cebe803e3b9 It looks like I can cast the bytea to text and then manipulate that and finally cast it to macaddr. Is there a better, or more canonical, way to convert/cast this field? Thanks for any help! -m
Re: cast bytea to macaddr
Hi Boris,
[bringing this email back to the mailing list]
A couple of questions:
1. What do the double parenthesis '(( Values ))' mean?
2. What is the 'x(field)' in the column alias?
Thanks for educating me!
-m
On Thu, Sep 28, 2023 at 10:34 AM Boris Zentner wrote:
> Hi matt,
>
> This is my take. I guess this is also your solution ( encode(fteld,
> 'hex'): :macaddr )
>
> SELECT encode(fteld, 'hex'): :macaddr FROM ((VALUES
> (decode('aabbccddeeff', 'hex'
>
> AS x(field)
> --
> Boris
>
>
> Am 28.09.2023 um 17:01 schrieb Matt Zagrabelny :
>
>
> Greetings Pg folks,
>
> I have a database schema (not mine) that uses bytea to represent a MAC
> address. I'd like to create a view that casts that field into a MACADDR
> type.
>
> # \d lease4
>Table "public.lease4"
> Column │ Type │ Collation │ Nullable │
> Default
>
> ╪══╪═══╪══╪═
> hwaddr │ bytea│ │ │
>
> # select hwaddr from lease4;
> hwaddr
>
> \x9cebe803e3b9
>
> It looks like I can cast the bytea to text and then manipulate that and
> finally cast it to macaddr.
>
> Is there a better, or more canonical, way to convert/cast this field?
>
> Thanks for any help!
>
> -m
>
>
schema privileges and drop role
Greetings, I have a database that I recently changed the ownership for and now I am attempting to drop the previous owner of the database: test_db=# drop ROLE legacy_owner; ERROR: role "legacy_owner" cannot be dropped because some objects depend on it DETAIL: privileges for schema public I don't know where to look to find out what I need to alter to be able to remove the legacy role and internet searches came up fruitless. Does anyone have any hints or advice on where to look? Thanks for the help! -m
Re: schema privileges and drop role
Hi Adrian, Thanks for the reply! On Mon, Jun 24, 2024 at 7:03 PM Adrian Klaver wrote: > On 6/24/24 17:00, Matt Zagrabelny wrote: > > Greetings, > > > > I have a database that I recently changed the ownership for and now I am > > attempting to drop the previous owner of the database: > > > > test_db=# drop ROLE legacy_owner; > > ERROR: role "legacy_owner" cannot be dropped because some objects > > depend on it > > DETAIL: privileges for schema public > > > > I don't know where to look to find out what I need to alter to be able > > to remove the legacy role and internet searches came up fruitless. > > Generally best to start with the docs: > > https://www.postgresql.org/docs/current/sql-droprole.html > > "A role cannot be removed if it is still referenced in any database of > the cluster; an error will be raised if so. Before dropping the role, > you must drop all the objects it owns (or reassign their ownership) and > revoke any privileges the role has been granted on other objects. The > REASSIGN OWNED and DROP OWNED commands can be useful for this purpose; > see Section 22.4 for more discussion." > Cool. I gave it a try, but came up with the same error: test_db=# REASSIGN OWNED by legacy_owner TO new_owner; REASSIGN OWNED Time: 0.212 ms test_db=# drop role legacy_owner; ERROR: role "legacy_owner" cannot be dropped because some objects depend on it DETAIL: privileges for schema public test_db=# I'd still like to see how to list the "privileges for schema public", but I do appreciate the REASSIGN OWNED command. Thanks! -m > > > > > Does anyone have any hints or advice on where to look? > > > > Thanks for the help! > > > > -m > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
echo of \C foo
Greetings, I am using the \C command in psql to set a title for my table. psql is echoing 'Title is "foo"'. $ \C foo Title is "foo". How do I suppress the echo of "Title is..." from psql? Thanks for any help! Cheers, -m
Re: echo of \C foo
On Tue, Aug 31, 2021 at 11:10 AM Tom Lane wrote: > > Matt Zagrabelny writes: > > $ \C foo > > Title is "foo". > > > How do I suppress the echo of "Title is..." from psql? > > The -q switch would do it ... Thank you, Tom! -m
Re: problems with postgresql 10.1 hba_conf on fedora 27
On Wed, Dec 20, 2017 at 9:30 PM, support-tiger wrote: > > No, the docs for understanding hba_conf are not good (yes we can read and > are fairly smart) - we made suggestions the last time for several case > examples and were ignored - okay, simplicity of pouchdb/couchdb is > getting our attention > > Can you point to a link where the hba docs are suboptimal and suggest to the mailing list an improvement? -m
Re: grant connect to all databases
On Sat, Oct 5, 2024 at 11:26 AM Adrian Klaver
wrote:
> On 10/5/24 09:04, Matt Zagrabelny wrote:
> >
> >
> > On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 10/5/24 07:13, Matt Zagrabelny wrote:
> > > Hi David (and others),
> > >
> > > Thanks for the info about Public.
> > >
> > > I should expound on my original email.
> > >
> > > In our dev and test environments our admins (alice, bob, eve) are
> > > superusers. In production environments we'd like the admins to be
> > read-only.
> >
> > What are the REVOKE and GRANT commands you use to achieve that?
> >
> >
> > GRANT alice TO pg_read_all_data;
>
> Does alice have existing GRANTs?
>
Nope. I create the role (via puppet) and then add the GRANT
pg_read_all_data TO (via puppet).
>
> I would try:
>
> GRANT pg_read_all_data TO alice;
>
> As example:
>
> psql -d test -U postgres
>
> List of role grants
> Role name | Member of | Options| Grantor
> +--+--+--
> aklaver| app_admin| INHERIT, SET | postgres
> aklaver| production | INHERIT, SET | postgres
> dd_admin | dd_owner | ADMIN, SET | postgres
> dd_user| dd_admin | INHERIT, SET | postgres
> pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
> pg_monitor | pg_read_all_stats| INHERIT, SET | postgres
> pg_monitor | pg_stat_scan_tables | INHERIT, SET | postgres
> postgres | dd_owner | INHERIT, SET | postgres
>
>
> grant pg_read_all_data to adrian;
> GRANT ROLE
>
> test=# \drgS
>
What is \drgS? I don't believe I have that.
> List of role grants
> Role name | Member of | Options| Grantor
> +--+--+--
> adrian | pg_read_all_data | INHERIT, SET | postgres
> aklaver| app_admin| INHERIT, SET | postgres
> aklaver| production | INHERIT, SET | postgres
> dd_admin | dd_owner | ADMIN, SET | postgres
> dd_user| dd_admin | INHERIT, SET | postgres
> pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
> pg_monitor | pg_read_all_stats| INHERIT, SET | postgres
> pg_monitor | pg_stat_scan_tables | INHERIT, SET | postgres
> postgres | dd_owner | INHERIT, SET | postgres
>
> \dt csv_test
>List of relations
> Schema | Name | Type | Owner
> +--+---+--
> public | csv_test | table | postgres
>
> test=# \q
>
> psql -d test -U adrian
>
> test=> select * from csv_test ;
> id | val
> +--
>1 | test
>2 | dog
>3 | cat
>4 | test
>5 | fish
>
>
That looks good.
Here is the output of puppet's create role:
drop role alice;
The next puppet run and I get:
'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN
NOCREATEROLE NOCREATEDB NOSUPERUSER CONNECTION LIMIT -1'
GRANT pg_read_all_data TO alice;
test=# \du
List of roles
Role name | Attributes
| Member of
--++
alice|
| {pg_read_all_data}
postgres | Superuser, Create role, Create DB, Replication,
Bypass RLS | {}
...but I still cannot connect:
$ psql -d test -U alice
psql: error: connection to server on socket
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: permission denied for
database "test"
DETAIL: User does not have CONNECT privilege.
Thanks for the help!
-m
grant connect to all databases
Hello, I'd like to have a read-only user for all databases. I found the pg_read_all_data role predefined role, which I granted to my RO user: GRANT pg_read_all_data TO ro_user; ...but I cannot connect to my database(s). I'd like to not have to iterate over all the databases and "GRANT CONNECT...". Is there a way to do this with just one GRANT or equivalent command? Thanks for any help! -m
Re: grant connect to all databases
Hi David (and others), Thanks for the info about Public. I should expound on my original email. In our dev and test environments our admins (alice, bob, eve) are superusers. In production environments we'd like the admins to be read-only. Is the Public role something I can leverage to achieve this desire? Thanks for the help! -m On Sat, Oct 5, 2024 at 9:02 AM David G. Johnston wrote: > On Saturday, October 5, 2024, Matt Zagrabelny wrote: > >> Hello, >> >> I'd like to have a read-only user for all databases. >> >> I found the pg_read_all_data role predefined role, which I granted to my >> RO user: >> >> GRANT pg_read_all_data TO ro_user; >> >> ...but I cannot connect to my database(s). >> >> I'd like to not have to iterate over all the databases and "GRANT >> CONNECT...". >> >> Is there a way to do this with just one GRANT or equivalent command? >> > > > The pseudo-role Public exists for just this kind of thing. In fact, in a > default installation it already is given connect privileges on all > databases created by the bootstrap superuser. > > David J. > >
Re: grant connect to all databases
On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver wrote: > On 10/5/24 07:13, Matt Zagrabelny wrote: > > Hi David (and others), > > > > Thanks for the info about Public. > > > > I should expound on my original email. > > > > In our dev and test environments our admins (alice, bob, eve) are > > superusers. In production environments we'd like the admins to be > read-only. > > What are the REVOKE and GRANT commands you use to achieve that? > GRANT alice TO pg_read_all_data; ...and then I could do something like this: -- for $database in $databases; GRANT CONNECT ON database $database TO alice; ...but I'd like to achieve it without the `for` loop. > > > > > Is the Public role something I can leverage to achieve this desire? > > You should read: > > https://www.postgresql.org/docs/current/ddl-priv.html Will do. > > > > From your original post: > > "but I cannot connect to my database" > > Was that due to a GRANT issue or a pg_hba.conf issue? > It was due to the missing GRANT CONNECT from above. pg_hba looks OK. > What was the actual complete error? > alice$ psql foo psql: error: connection to server at "db.example.com" (fe80:100), port 5432 failed: FATAL: permission denied for database "foo" ...after I GRANT CONNECT, I can connect. However, I don't want to have to iterate over all the databases to achieve the GRANT CONNECT. I guess I was hoping that the pg_read_all_data would also allow connecting. Or if it didn't, there could/would be a pg_connect_all_databases role. Cheers, -m
Re: grant connect to all databases
On Sat, Oct 5, 2024 at 3:12 PM Adrian Klaver wrote: > On 10/5/24 11:24, Matt Zagrabelny wrote: > > > > > > > Nope. I create the role (via puppet) and then add the GRANT > > pg_read_all_data TO (via puppet). > > > > > What is \drgS? I don't believe I have that. > > That is available in Postgres 16+, you must running be in an instance of > Postgres before that. > Ah. Yup! > > > > 'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN > > NOCREATEROLE NOCREATEDB NOSUPERUSER CONNECTION LIMIT -1' > > GRANT pg_read_all_data TO alice; > > > > > ...but I still cannot connect: > > > > $ psql -d test -U alice > > psql: error: connection to server on socket > > "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: permission denied > > for database "test" > > DETAIL: User does not have CONNECT privilege. > > Something is going on in the background. > Agreed. > > What version of Postgres? > psql (15.8 (Debian 15.8-0+deb12u1)) > Where did you install it from or where are you running it? > Installed from Debian repos via apt via puppet. Still digging... -m
Re: grant connect to all databases
On Sat, Oct 5, 2024 at 5:43 PM Adrian Klaver wrote: > On 10/5/24 15:04, Matt Zagrabelny wrote: > > > psql (15.8 (Debian 15.8-0+deb12u1)) > > > > > > Where did you install it from or where are you running it? > > > > > > Installed from Debian repos via apt via puppet. > > > > Still digging... > > To me it looks like something is doing: > > REVOKE CONNECT > ON DATABASE > FROM PUBLIC; > > I think we have a winner. When puppet creates the database, it revokes connect: https://github.com/puppetlabs/puppetlabs-postgresql/blob/main/manifests/server/database.pp#L55-L80 I'll have to ponder a solution. :( Thanks for all the help (David, Adrian, and Tom)! I very much appreciate you taking time to give me some guidance and pointers. Cheers! -m
Re: adsrc
On Tue, Nov 5, 2024 at 4:44 PM Ron Johnson wrote: > On Tue, Nov 5, 2024 at 4:45 PM Matt Zagrabelny wrote: > >> Greetings Pg folk, >> >> I've got a legacy app connecting to a legacy Pg database (9.4). >> >> I'd like to upgrade the database to Pg 15, but when I connect and perform >> some tasks in the app, I get: >> >> ERROR: column d.adsrc does not exist at character 331 >> >> Some searching online yields that adsrc was removed in Pg 12. >> > > 1. What is the application? > Netdot - an IPAM from the 2000's. > > 2. An upgraded version of the application (or at least the db driver) > probably won't reference it. > Yes. Looks like the DBD::Pg module is what needs the upgrade. -m
adsrc
Greetings Pg folk, I've got a legacy app connecting to a legacy Pg database (9.4). I'd like to upgrade the database to Pg 15, but when I connect and perform some tasks in the app, I get: ERROR: column d.adsrc does not exist at character 331 Some searching online yields that adsrc was removed in Pg 12. Is there a way to recreate it? Thanks for any help or answers. Cheers, -m
