Re: PostgreSQL Community Enquire !

[Kashif Zeeshan]

Hi

On Tue, Jun 25, 2024 at 4:29 AM madhav sira  wrote:

> Dear PostgreSQL Community,
>
>
> I am new to the PostgreSQL community. I am writing to seek guidance on the
> best practices for engaging with the community, particularly regarding
> where to post questions related to SQL queries and general database issues.
>
> In other database communities, such as the Oracle SQL and PL/SQL forums,
> there are dedicated spaces where users can ask and answer questions. I am
> wondering if PostgreSQL has a similar community forum or if all questions
> should be directed to this mailing list (pgsql-general).
>
> Could you please provide some information on the following:
>
> 1. Is there a dedicated community forum or platform where PostgreSQL users
> typically ask and discuss SQL-related questions?
> 2. If the `pgsql-general` mailing list is the primary venue, are there any
> specific guidelines or best practices I should follow when posting
> questions?
>

There are as such no  guidelines  but what I recommend is that you can
check the existing posts on the mail lists and you will get a general idea.

3. Are there other resources or mailing lists that would be beneficial for
> someone looking to learn and troubleshoot PostgreSQL?
>
There are a lot of mail lists available, you need to subscribe to the
desired ones, the mostly used ones are
1.  pgsql-general
2. pgsql-hackers
3. pgsql-admin

You can go to the following link.
https://www.postgresql.org/community/

Regards
Kashif Zeeshan

>
> I want to ensure that I am using the correct channels and adhering to
> community standards to get the most out of my interactions with the
> PostgreSQL community.
>
> Thank you for your time and assistance. I look forward to your response.
>
> Best regards,
> Shiv
>

Re: schema privileges and drop role

[Tom Lane]

Matt Zagrabelny  writes:
> On Mon, Jun 24, 2024 at 7:03 PM Adrian Klaver 
> wrote:
>> "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

You need to do DROP OWNED as well to get rid of those privileges.
REASSIGN OWNED only changes the ownership of ownable objects.

> I'd still like to see how to list the "privileges for schema
> public",

"\dn+ public" in psql would do that.

regards, tom lane

Re: schema privileges and drop role

[Matt Zagrabelny]

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
>
>

Re: Issue with pgstattuple on Sequences in PostgreSQL

[Tatsuo Ishii]

> On Monday, June 24, 2024, Ayush Vatsa  wrote:
>>
>> I was recently exploring the pgstattuple code directory and found this
>> piece of code: https://github.com/postgres/postgres/blob/master/contrib/
>> pgstattuple/pgstattuple.c#L255-L259.
>>
>> It indicates that pgstattuple supports relations, toast tables,
>> materialized views, and sequences.
>> However, when I executed a query with a sequence, I encountered the
>> following error:
>>
>> postgres=> CREATE SEQUENCE serial START 101;
>> CREATE SEQUENCE
>> postgres=> SELECT * FROM pgstattuple('serial');
>> ERROR:  only heap AM is supported
>> postgres=>
>>
>> It got stuck in this if condition - https://github.com/postgres/
>> postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329
>>
>>
>> How can one use pgstattuple on sequences?
>>
> 
> As-is?  Doesn’t look like you can.

It used to work until v11.

test=# SELECT * FROM pgstattuple('serial');
-[ RECORD 1 ]--+--
table_len  | 8192
tuple_count| 1
tuple_len  | 41
tuple_percent  | 0.5
dead_tuple_count   | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 8104
free_percent   | 98.93

It stopped working by this commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4b82664156c230b59607704506f5b0a32ef490a2

because pgstat_heap() rejects other than heap AM.  I think the v12
release note should have explicitly mentioned that sequences are not
supported by pgstattuple any more.

> I agree it’s a documentation bug that
> this is the case with a brief explanation of why - sequences do not produce
> dead tuples and do not behave like real tables aside from being able to be
> selected from (i.e., no SQL update/delete command).
> 
> The code should produce an explicit error for that relkind as well.

If so, then the regression test should be fixed as well. Currently
there's no test case for sequences.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: schema privileges and drop role

[Adrian Klaver]

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."




Does anyone have any hints or advice on where to look?

Thanks for the help!

-m


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

schema privileges and drop role

[Matt Zagrabelny]

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: PostgreSQL Community Enquire !

[Adrian Klaver]

On 6/24/24 16:28, madhav sira wrote:

Dear PostgreSQL Community,


I am new to the PostgreSQL community. I am writing to seek guidance on 
the best practices for engaging with the community, particularly 
regarding where to post questions related to SQL queries and general 
database issues.


In other database communities, such as the Oracle SQL and PL/SQL forums, 
there are dedicated spaces where users can ask and answer questions. I 
am wondering if PostgreSQL has a similar community forum or if all 
questions should be directed to this mailing list (pgsql-general).


Yes.

Though if you have a more specific need within Postgres you could ask on 
the mailing lists here:


https://www.postgresql.org/list/

that deal with that need.



Best regards,
Shiv


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

PostgreSQL Community Enquire !

[madhav sira]

Dear PostgreSQL Community,


I am new to the PostgreSQL community. I am writing to seek guidance on the
best practices for engaging with the community, particularly regarding
where to post questions related to SQL queries and general database issues.

In other database communities, such as the Oracle SQL and PL/SQL forums,
there are dedicated spaces where users can ask and answer questions. I am
wondering if PostgreSQL has a similar community forum or if all questions
should be directed to this mailing list (pgsql-general).

Could you please provide some information on the following:

1. Is there a dedicated community forum or platform where PostgreSQL users
typically ask and discuss SQL-related questions?
2. If the `pgsql-general` mailing list is the primary venue, are there any
specific guidelines or best practices I should follow when posting
questions?
3. Are there other resources or mailing lists that would be beneficial for
someone looking to learn and troubleshoot PostgreSQL?

I want to ensure that I am using the correct channels and adhering to
community standards to get the most out of my interactions with the
PostgreSQL community.

Thank you for your time and assistance. I look forward to your response.

Best regards,
Shiv

Re: Execute permission to function

[Adrian Klaver]

On 6/24/24 11:10 AM, arun chirappurath wrote:


Hi,

Only see select script here in pg_admin..


I don't see any active servers in the Browser pane.

Have you done?:

https://www.pgadmin.org/docs/pgadmin4/8.8/connecting.html

If you have set up a server what connection parameters did you use?

Do those parameters work if you try to connect with psql?

If not what is the complete error message?



image.png

Thanks,
Arun

On Mon, 24 Jun 2024 at 18:25, Ron Johnson > wrote:


On Mon, Jun 24, 2024 at 6:29 AM arun chirappurath
mailto:arunsnm...@gmail.com>> wrote:

Hi all

I am using rds postgres 14. I have created few users and added
them to pg_readall_data and pg_write_alldata groups


They are able to read all data and do update in tables

However they can't execute functions 



https://www.postgresql.org/docs/14/sql-grant.html


and not able to script out objects from pg_admin


What error message(s)?



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

Re: Password complexity/history - credcheck?

[Christoph Moench-Tegeder]

## Martin Goodson (kaema...@googlemail.com):

> Crikey, that would be  quite a lot of  lot of SSL/TLS to set up. We
> have quite a few (massive understatement :( ... ) PostgreSQL database
> clusters spread over quite a lot (another understatement) of VMs.

No matter what: you'll have to touch all your instances anyways.
The good thing is that all the options (including TLS) can be
automatically deployed iff you're set up for that - and you should
be, especially when you have "many" databases.

> The last time I suggested LDAP there was a lot of enthusiasm ... until
> they went down and looked at what might have to be done, after which
> it all became very quiet ...

With "many" databases and personal accounts, you should have some
sort of central management (else even an inventory of the accounts
("who can access what") is a nightmare). Finding the best ways towards
that goal for your organization could be beyond the scope of an email
list - but I'd start with looking at what you already have. I mentioned
LDAP because all too often that's the system which you can most easily
get access to (but depending on your environment, that might mot be
the best solution).

Regards,
Christoph

-- 
Spare Space.

Re: Execute permission to function

[Adrian Klaver]

On 6/24/24 03:29, arun chirappurath wrote:

Hi all

I am using rds postgres 14. I have created few users and added them to 
pg_readall_data and pg_write_alldata groups



They are able to read all data and do update in tables

However they can't execute functions and not able to script out objects 
from pg_admin


The above are two separate issues. Provide examples of what you are 
doing and the errors you get when you do them.




Any other role to be added?


You probably should take a look at:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Roles.html#Appendix.PostgreSQL.CommonDBATasks.Access



Thanks,
Arun


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

Re: Issue with pgstattuple on Sequences in PostgreSQL

[Adrian Klaver]

On 6/24/24 04:09, Ayush Vatsa wrote:

Hi PostgreSQL Community,

I was recently exploring the pgstattuple code directory and found this 
piece of code: 
https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L255-L259 .


It indicates that pgstattuple supports relations, toast tables, 
materialized views, and sequences.
However, when I executed a query with a sequence, I encountered the 
following error:


postgres=> CREATE SEQUENCE serial START 101;
CREATE SEQUENCE
postgres=> SELECT * FROM pgstattuple('serial');
ERROR:  only heap AM is supported
postgres=>


File a bug report here:

https://www.postgresql.org/account/login/?next=/account/submitbug/



It got stuck in this if condition - 
https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329 

How can one use pgstattuple on sequences?

Regards,
Ayush Vatsa



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

Re: Issue with pgstattuple on Sequences in PostgreSQL

[David G. Johnston]

On Monday, June 24, 2024, Ayush Vatsa  wrote:
>
> I was recently exploring the pgstattuple code directory and found this
> piece of code: https://github.com/postgres/postgres/blob/master/contrib/
> pgstattuple/pgstattuple.c#L255-L259.
>
> It indicates that pgstattuple supports relations, toast tables,
> materialized views, and sequences.
> However, when I executed a query with a sequence, I encountered the
> following error:
>
> postgres=> CREATE SEQUENCE serial START 101;
> CREATE SEQUENCE
> postgres=> SELECT * FROM pgstattuple('serial');
> ERROR:  only heap AM is supported
> postgres=>
>
> It got stuck in this if condition - https://github.com/postgres/
> postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329
>
>
> How can one use pgstattuple on sequences?
>

As-is?  Doesn’t look like you can.  I agree it’s a documentation bug that
this is the case with a brief explanation of why - sequences do not produce
dead tuples and do not behave like real tables aside from being able to be
selected from (i.e., no SQL update/delete command).

The code should produce an explicit error for that relkind as well.

David J.

Re: 2FA - - - was Re: Password complexity/history - credcheck?

[Chris Travers]

On Mon, Jun 24, 2024 at 8:00 PM o1bigtenor  wrote:

>
>
> On Sun, Jun 23, 2024 at 10:10 AM Greg Sabino Mullane 
> wrote:
>
>> On Sun, Jun 23, 2024 at 5:30 AM Martin Goodson 
>> wrote:
>>
>>> I believe that our security team is getting most of this from our
>>> auditors, who seem convinced that minimal complexity, password history
>>> etc are the way to go despite the fact that, as you say, server-side
>>> password checks can't really be implemented when the database receives a
>>> hash rather than a clear text password and password minimal complexity
>>> etc is not perhaps considered the gold standard it once was.
>>>
>>> In fact, I think they see a hashed password as a disadvantage.
>>
>>
>> Wow, full stop right there. This is a hill to die on.
>>
>> Push back and get some competent auditors. This should not be a DBAs
>> problem. Your best bet is to use Kerberos, and throw the password
>> requirements out of the database realm entirely.
>>
>> Also, the discussion should be about 2FA, not password history/complexity.
>>
>>
> Hmmm - - - - 2FA - - - - what I've seen of it so far is that
> authentication is most often done
> using totally insecure tools (emailing some numbers or using SMS). Now if
> you were espousing
> the use of security dongles and such I would agree - - - - otherwise you
> are promoting the veneering
> of insecurity on insecurity with the hope that this helps.
>
> IMO having excellent passwords far trumps even 2FA - - - - 2FA is useful
> when simple or quite
> easily broken passwords are required.  Now when you add the lack of SMS
> possibilities (due to lack of signal) 2FA is an usually potent PITA because
> of course SMS 'always' works (except it doesn't()).
>
> (Can you tell that I've been bitten in the posterior repeatedly with this
> garbage?)
>

For 2FA, a simple solution is to require a password plus
clientcert=sameuser.  This allows you to authorize devices/user accounts
for specific remote database connections and provides that second factor --
i.e. something you have as well as something you know.

>
>
> Regards
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Execute permission to function

[David G. Johnston]

On Monday, June 24, 2024, arun chirappurath  wrote:

>
> However they can't execute functions
>

Community PG at least gives the public pseudo-role permission to execute
all functions it can see (i.e., schema access permitted).

But no, there is no predefined role that enables that function execution.
The per-object grant system is required.

David J.

2FA - - - was Re: Password complexity/history - credcheck?

[o1bigtenor]

On Sun, Jun 23, 2024 at 10:10 AM Greg Sabino Mullane 
wrote:

> On Sun, Jun 23, 2024 at 5:30 AM Martin Goodson 
> wrote:
>
>> I believe that our security team is getting most of this from our
>> auditors, who seem convinced that minimal complexity, password history
>> etc are the way to go despite the fact that, as you say, server-side
>> password checks can't really be implemented when the database receives a
>> hash rather than a clear text password and password minimal complexity
>> etc is not perhaps considered the gold standard it once was.
>>
>> In fact, I think they see a hashed password as a disadvantage.
>
>
> Wow, full stop right there. This is a hill to die on.
>
> Push back and get some competent auditors. This should not be a DBAs
> problem. Your best bet is to use Kerberos, and throw the password
> requirements out of the database realm entirely.
>
> Also, the discussion should be about 2FA, not password history/complexity.
>
>
Hmmm - - - - 2FA - - - - what I've seen of it so far is that
authentication is most often done
using totally insecure tools (emailing some numbers or using SMS). Now if
you were espousing
the use of security dongles and such I would agree - - - - otherwise you
are promoting the veneering
of insecurity on insecurity with the hope that this helps.

IMO having excellent passwords far trumps even 2FA - - - - 2FA is useful
when simple or quite
easily broken passwords are required.  Now when you add the lack of SMS
possibilities (due to lack of signal) 2FA is an usually potent PITA because
of course SMS 'always' works (except it doesn't()).

(Can you tell that I've been bitten in the posterior repeatedly with this
garbage?)

Regards

Re: Issue with pgstattuple on Sequences in PostgreSQL

[Ron Johnson]

On Mon, Jun 24, 2024 at 7:09 AM Ayush Vatsa 
wrote:
[snip]

> How can one use pgstattuple on sequences?
>
Out of curiosity... *why*?

Re: Execute permission to function

[Ron Johnson]

On Mon, Jun 24, 2024 at 6:29 AM arun chirappurath 
wrote:

> Hi all
>
> I am using rds postgres 14. I have created few users and added them to
> pg_readall_data and pg_write_alldata groups
>
>
> They are able to read all data and do update in tables
>
> However they can't execute functions
>

https://www.postgresql.org/docs/14/sql-grant.html


> and not able to script out objects from pg_admin
>

What error message(s)?

Re: Issue with pgstattuple on Sequences in PostgreSQL

[Kashif Zeeshan]

Hi

I dont think Sequences are support, please refer to the following
documentation.

https://www.postgresql.org/docs/current/pgstattuple.html

Regards
Kashif Zeeshan

On Mon, Jun 24, 2024 at 4:09 PM Ayush Vatsa 
wrote:

> Hi PostgreSQL Community,
>
> I was recently exploring the pgstattuple code directory and found this
> piece of code:
> https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L255-L259
> .
>
> It indicates that pgstattuple supports relations, toast tables,
> materialized views, and sequences.
> However, when I executed a query with a sequence, I encountered the
> following error:
>
> postgres=> CREATE SEQUENCE serial START 101;
> CREATE SEQUENCE
> postgres=> SELECT * FROM pgstattuple('serial');
> ERROR:  only heap AM is supported
> postgres=>
>
> It got stuck in this if condition -
> https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329
> How can one use pgstattuple on sequences?
>
> Regards,
> Ayush Vatsa
>

Issue with pgstattuple on Sequences in PostgreSQL

[Ayush Vatsa]

Hi PostgreSQL Community,

I was recently exploring the pgstattuple code directory and found this
piece of code:
https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L255-L259
.

It indicates that pgstattuple supports relations, toast tables,
materialized views, and sequences.
However, when I executed a query with a sequence, I encountered the
following error:

postgres=> CREATE SEQUENCE serial START 101;
CREATE SEQUENCE
postgres=> SELECT * FROM pgstattuple('serial');
ERROR:  only heap AM is supported
postgres=>

It got stuck in this if condition -
https://github.com/postgres/postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329
How can one use pgstattuple on sequences?

Regards,
Ayush Vatsa

Execute permission to function

[arun chirappurath]

Hi all

I am using rds postgres 14. I have created few users and added them to
pg_readall_data and pg_write_alldata groups


They are able to read all data and do update in tables

However they can't execute functions and not able to script out objects
from pg_admin

Any other role to be added?

Thanks,
Arun

Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.

[Daniel Verite]

Dmitry O Litvintsev wrote:

> Just want to make clear (sorry I am slow on uptake). I should first
> REINDEX and then ALTER DATABASE xxx REFRESH COLLATION VERSION, or
> first ALTER and then REINDEX or does the order of these action
> matter at all?

The order does not matter. The ALTER DATABASE command will simply
update the pg_database.datcollversion field with the current version
of libc. That will stop the warning being issued, but it doesn't have
any other concrete effect.

Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

Re: Upgrade PG from 12 to latest

[Laurenz Albe]

On Sun, 2024-06-23 at 10:08 +0300, Doron Tsur wrote:
> We are running PG 12 on AWS RDS. I want to run an upgrade process for the DB.
> The org I work for attempted to upgrade before but failed (12->15). The 
> upgrade
> resulted in unexplained slowness, and performance issues were only resolved 
> via
> a downgrade.

We cannot provide help for Amazon's upgrade process.

If you still have the slow database, you should analyze what exactly was slow 
and why.

Yours,
Laurenz Albe

Re: Password complexity/history - credcheck?

[Laurenz Albe]

On Sun, 2024-06-23 at 14:14 +0100, Martin Goodson wrote:
> On 23/06/2024 11:49, Christoph Moench-Tegeder wrote:
> > My advice would be to not use secrets stored in the database -
> > that is, do not use scram-sha-256 - but use an external authentication
> > system, like Kerberos (might be AD) or LDAP (might also be AD) and have
> > that managed by the security team: that way all these compliance
> 
> Crikey, that would be  quite a lot of  lot of SSL/TLS to set up. We have 
> quite a
> few (massive understatement :( ... ) PostgreSQL database clusters spread over 
> quite a lot (another understatement) of VMs.
> 
> The last time I suggested LDAP there was a lot of enthusiasm ... until they 
> went
> down and looked at what might have to be done, after which it all became very 
> quiet ...

Yes, LDAP is not perfect for that - for one, every connection to the database 
would
also hit the LDAP server.

Kerberos or certificate authentication is probably better.

For many PostgreSQL clusters and clients, that might be a lot of work.
But not all your PostgreSQL databases will contain equally sensitive data.
You could start with the important ones, try to automatize as much as possible,
and roll out the changes over time.

Yours,
Laurenz Albe