Option B would be fine with me, unless there is good reason to normalize
it further. A query using recursive CTE would be able to find ancestors
and descendants neatly and efficiently.
I deal with some tables in the billions of rows, and with that hat on, I
would use int/bigint identity for t
On Mon, Nov 24, 2025 at 2:46 PM Tom Lane wrote:
> =?utf-8?Q?=C3=81lvaro?= Herrera writes:
> > On 2025-Nov-24, Tom Lane wrote:
> >> I don't think so. They are just shorthand for issuing a SET to the
> >> original value, so how do they break the model in a way that that
> >> doesn't?
>
> > No, be
=?utf-8?Q?=C3=81lvaro?= Herrera writes:
> On 2025-Nov-24, Tom Lane wrote:
>> I don't think so. They are just shorthand for issuing a SET to the
>> original value, so how do they break the model in a way that that
>> doesn't?
> No, because the new user doesn't have privs to become the previous on
On Fri, Nov 21, 2025 at 4:55 PM Tom Lane wrote:
> Laurenz Albe writes:
> > I dug into the git history, and it has been like that since commit
> b3506006b564
> > in 2002 (way before version 9.x). That commit fixed a bug that returned
> ten
> > time the correct reault (but still offset from the U
On Mon, Nov 24, 2025 at 11:18:20AM -0500, Tom Lane wrote:
> Laurenz Albe writes:
> > On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
> >> I really feel, once you "set role usera", you should behave like usera,
> >> you should
> >> NOT have the power say: hi, I can assume my super user power
On 2025-Nov-24, Tom Lane wrote:
> =?utf-8?Q?=C3=81lvaro?= Herrera writes:
> > For what it's worth, I think we break the SQL standard's security model
> > by providing RESET ROLE and RESET SESSION AUTHORIZATION, neither of
> > which the standard has.
>
> I don't think so. They are just shorthand
=?utf-8?Q?=C3=81lvaro?= Herrera writes:
> For what it's worth, I think we break the SQL standard's security model
> by providing RESET ROLE and RESET SESSION AUTHORIZATION, neither of
> which the standard has.
I don't think so. They are just shorthand for issuing a SET to the
original value, so
Hi everyone,
First time trying to configure a PG cluster by the book, I want to create a
role with read permissions on all current and future tables in the current
db. It looks smth like this
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES
On 2025-Nov-24, Tom Lane wrote:
> Laurenz Albe writes:
> > On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
> > I respect your feelings, but that is not how SET ROLE works.
> > The current behavior is intentional and documented in
> > https://www.postgresql.org/docs/current/sql-set-role.html
Laurenz Albe writes:
> On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
>> I really feel, once you "set role usera", you should behave like usera, you
>> should
>> NOT have the power say: hi, I can assume my super user power whenever I want.
>> As this make the "set role usera" pretty much us
> On Nov 24, 2025, at 6:18 AM, Bernice Southey
> wrote:
>
> Christoph Pieper wrote:
>> Question:
>> From a PostgreSQL point of view (database best practices, data integrity,
>> performance and long‑term maintainability at millions of rows), which
>> approach would you prefer, or is there a
On Mon, Nov 24, 2025 at 9:15 AM Calvin Guo wrote:
> I really feel, once you "set role usera", you should behave like usera, you
> should NOT have the power say:
> hi, I can assume my super user power whenever I want. As this make the "set
> role usera" pretty much useless.
SET ROLE is only abou
Christoph Pieper wrote:
> Question:
> From a PostgreSQL point of view (database best practices, data integrity,
> performance and long‑term maintainability at millions of rows), which
> approach would you prefer, or is there a better pattern for this kind of
> “account can be deleted, but genea
> On 24 Nov 2025, at 09:15, Calvin Guo wrote:
>
> I feel that set role logic is kindof misleading.
>
> I am a superuser, admin,
> I do:
> set role usera
> Now I am under the security context of usera, so I think running any sql is
> safe as long as it's allowed by usera.
>
> Which is not th
On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
> I feel that set role logic is kindof misleading.
>
> I am a superuser, admin,
> I do:
> set role usera
> Now I am under the security context of usera, so I think running any sql is
> safe
> as long as it's allowed by usera.
>
> Which is not
No, that's not the case. right now: usera can totally do: reset role, and
then behave like userb.
this behavior should not be allowed.
userb can impernate usera, once the impersonation is done, it becomes
usera, and it should NOT be able to impersonate userb. but right now, reset
role will allow it
On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
> I feel that set role logic is kindof misleading.
>
> I am a superuser, admin,
> I do:
> set role usera
> Now I am under the security context of usera, so I think running any
> sql is safe as long as it's allowed by usera.
>
> Which is not the
Hi,
I’m designing a schema for a family‑tree web app on PostgreSQL. Users
register accounts and can create one or more family trees. Each tree
consists of persons (the user themself, relatives, ancestors). Many persons
in a tree will never have an account (e.g. great‑grandparents). Because of
GDPR
I feel that set role logic is kindof misleading.
I am a superuser, admin,
I do:
set role usera
Now I am under the security context of usera, so I think running any sql is
safe as long as it's allowed by usera.
Which is not the case!
as usera can do:
set role userb; other sql,
or
reset role; orthe
19 matches
Mail list logo
