procedure create_role to dd_owner;
GRANT
set role dd_owner;
SET
call create_role('dd_user', 'dd_admin');
CALL
\du dd*
List of roles
Role name | Attributes
---+--
dd_admin | No inheritance, Cannot login
dd_owner | Crea
s well. We basically changed things
because this model was deemed dangerous. I suppose we took little effort
to make it safer in the new regime had anyone decided to use it anyway,
instead figuring that most would separate the main DAG of application roles
from the object owners and role creators
grants. Whether that's correct is
an arguable point, but it seems very strange to me to argue that role
grants should work differently from every other type of grant in the
system, and it does have some nice properties. But that means that the
anti-circularity provisions that we apply in other
On Thu, Sep 12, 2024 at 2:40 PM Dominique Devienne wrote:
> Basically the above explain why we have that
> dd_user (INHERIT)
> `-> member-of dd_admin (NOINHERIT)
> `-> member-of dd_owner (INHERIT).
>
> In pre-v16, once again, this was fine.
> Because v16+ adds that dd_owner member-of dd_user
ossible, with no
known workaround at this point. Isn't that the very definition of a regression?
I don't mean to offend anyone. But as I don't see any way out of my
v16+ conundrum, I have to admit to being a little peeved.
> I didn't quite understand the exact purpose of t
longer DAGs leading to errors.
That's a pretty important change, which from my POV,
is kinda a regression. (there, I used to big word...).
> You do automatically get WITH ADMIN OPTION on roles
> you create yourself --- but in this example, dd_owner did
> not create dd_admin.
Because
evant v16 change is that
CREATEROLE used to imply having ADMIN on every (non-superuser) role.
Now it doesn't, and you have to actually have a WITH ADMIN OPTION
grant. You do automatically get WITH ADMIN OPTION on roles you
create yourself --- but in this example, dd_owner
e grant is not possible.
I thinkthisis a migrationissueforv16and it is not mentioned in release
notes.
Ididn'tquiteunderstandthe exactpurposeof the roles dd_owner and dd_admin.
But apossibleway is to use dd_admin to create roles. For example:
create role dd_admin login createrole;
\c - dd_admin
create
On Wed, Sep 11, 2024, 12:17 Wolfgang Walther
wrote:
> Dominique Devienne:
> > Hi David. I did as you suggested, and it fails the same way. Did I
> > misunderstand you? --DD
> >
> > [..]
> >
> > ddevienne=> grant dd_owner to dd_admin with admin option; --
>
> I think this needs to be the
Dominique Devienne:
Hi David. I did as you suggested, and it fails the same way. Did I
misunderstand you? --DD
[..]
ddevienne=> grant dd_owner to dd_admin with admin option; --
I think this needs to be the other way around:
grant dd_admin to dd_owner with admin option;
Best,
Wol
ERROR: permission denied to grant role "dd_owner"
> DETAIL: Only roles with the ADMIN option on role "dd_owner" may grant
> this role.
A role can't grant itself to someone? Hmmm...
> test=> create role dd_user;
> CREATE ROLE
> test=> grant dd_admin t
_user;
GRANT ROLE
ddevienne=> set role dd_owner;
SET
ddevienne=> create role dd_user;
CREATE ROLE
ddevienne=> grant dd_admin to dd_user;
ERROR: permission denied to grant role "dd_admin"
DETAIL: Only roles with the ADMIN option on role "dd_admin" may grant
this role.
ddevienne=>
T
ddevienne=> create role dd_user;
CREATE ROLE
ddevienne=> grant dd_admin to dd_user;
ERROR: permission denied to grant role "dd_admin"
DETAIL: Only roles with the ADMIN option on role "dd_admin" may grant
this role.
ddevienne=>
What user did you do the above as?
On my
> ddevienne=> set role dd_owner;
> ERROR: permission denied to set role "dd_owner"
> ddevienne=> grant dd_owner to current_user;
> GRANT ROLE
> ddevienne=> set role dd_owner;
> SET
> ddevienne=> create role dd_user;
> CREATE ROLE
> ddevienne=> gra
(KO).
In our system, the dd_owner and dd_admin roles are siblings, typically
created by the database owner, to bootstrap our system. Then all other
internal roles are created using dd_owner (that's why it has
CREATEROLE). We have internal roles for users, groups, etc... to
replicate the bus
On Mon, Jul 8, 2024 at 6:08 PM Tom Lane wrote:
> >> Hmm, if that check doesn't require INHERIT TRUE I'd say it's
> >> a bug.
>
> > The code doesn't support that claim.
>
> That doesn't make it not a bug. Robert, what do you think? If this
> is correct behavior, why is it correct?
Correct is deb
that blast radius is too large for v16. Arguable for v17. All
pre-v-16 roles encountered during upgrade will have SET true and those
roles are able to login today but a non-trivial number I would expect to be
unable to if we require INHERIT.
It would be nicer to do away with the default connec
"David G. Johnston" writes:
> On Mon, Jul 8, 2024 at 3:08 PM Tom Lane wrote:
>> That doesn't make it not a bug.
> Fair, the code was from a time when membership implied SET permission which
> apparently was, IMO, still is, a sufficient reason to allow a member of
> that group to login.
> By maki
On Mon, Jul 8, 2024 at 3:08 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Mon, Jul 8, 2024 at 2:16 PM Tom Lane wrote:
> >> Pavel Luzanov writes:
> > On 08.07.2024 22:22, Christophe Pettus wrote:
> This is more curiosity than anything else. In the v16 role system, is
> ther
"David G. Johnston" writes:
> On Mon, Jul 8, 2024 at 2:16 PM Tom Lane wrote:
>> Pavel Luzanov writes:
> On 08.07.2024 22:22, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is
there actually any reason to grant membership in a role to a diff
On Mon, Jul 8, 2024 at 2:16 PM Tom Lane wrote:
> Pavel Luzanov writes:
> > On 08.07.2024 22:22, Christophe Pettus wrote:
> >> This is more curiosity than anything else. In the v16 role system, is
> there actually any reason to grant membership in a role to a different
> role, but with SET FALSE
On 09.07.2024 00:16, Tom Lane wrote:
Pavel Luzanov writes:
On 08.07.2024 22:22, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is there
actually any reason to grant membership in a role to a different role, but with
SET FALSE, INHERIT FALSE, and
Pavel Luzanov writes:
> On 08.07.2024 22:22, Christophe Pettus wrote:
>> This is more curiosity than anything else. In the v16 role system, is there
>> actually any reason to grant membership in a role to a different role, but
>> with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role gr
On 08.07.2024 22:22, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is there
actually any reason to grant membership in a role to a different role, but with
SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role granted membership
gain any abili
On Monday, July 8, 2024, Christophe Pettus wrote:
>
>
> > On Jul 8, 2024, at 13:29, Christophe Pettus wrote:
> >
> >
> >
> >> On Jul 8, 2024, at 13:25, Laurenz Albe
> wrote:
> >> I didn't test it, but doesn't that allow the member rule to drop
> objects owned
> >> be the role it is a member of?
Christophe Pettus writes:
>> On Jul 8, 2024, at 13:25, Laurenz Albe wrote:
>> I didn't test it, but doesn't that allow the member rule to drop objects
>> owned
>> be the role it is a member of?
> No, apparently not.
IIUC, you need at least one of SET TRUE and INHERIT TRUE to be able to
access
> On Jul 8, 2024, at 13:29, Christophe Pettus wrote:
>
>
>
>> On Jul 8, 2024, at 13:25, Laurenz Albe wrote:
>> I didn't test it, but doesn't that allow the member rule to drop objects
>> owned
>> be the role it is a member of?
>
> No, apparently not.
Just from a quick check, it looks lik
> On Jul 8, 2024, at 13:25, Laurenz Albe wrote:
> I didn't test it, but doesn't that allow the member rule to drop objects owned
> be the role it is a member of?
No, apparently not.
On Mon, 2024-07-08 at 12:22 -0700, Christophe Pettus wrote:
> This is more curiosity than anything else. In the v16 role system, is there
> actually any
> reason to grant membership in a role to a different role, but with SET FALSE,
> INHERIT FALSE,
> and ADMIN FALSE? Does the role granted memb
> On Jul 8, 2024, at 12:58, David G. Johnston
> wrote:
> That scenario is allowed but provides no useful in-server behavior.
That was my conclusion as well. Thanks!
On Mon, Jul 8, 2024 at 12:23 PM Christophe Pettus wrote:
>
> This is more curiosity than anything else. In the v16 role system, is
> there actually any reason to grant membership in a role to a different
> role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role
> granted members
Hi,
This is more curiosity than anything else. In the v16 role system, is there
actually any reason to grant membership in a role to a different role, but with
SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role granted membership
gain any ability it didn't have before in that case?
On Fri, May 10, 2024 at 11:31 AM Lok P wrote:
> For the initial installation the extensions may need superuser privileges.
>
>>
>>
Thank you. Yes, I got it. For the initial installation for the extensions
,it will need super user privilege. But once that is done for the day to
day use , does the
For the initial installation the extensions may need superuser privileges.
On Fri, May 10, 2024 at 10:04 AM yudhi s
wrote:
> Hello All,
> We want to make sure to keep minimal privileges for the users based on
> their roles and responsibility. We have one user group who will be wor
Hello All,
We want to make sure to keep minimal privileges for the users based on
their roles and responsibility. We have one user group who will be working
on analyzing/debugging into performance issues in the databases. Basically
this group will be operating on extensions like
On Tuesday, March 26, 2024, Siraj G wrote:
>
>
> I am from Oracle background. In Oracle, we grant select_catalog_role or
> select any dictionary role to users who want to study performance data. I
> am trying to get similar information on the roles or privileges in PgSQL
> tha
> On Mar 26, 2024, at 22:30, Siraj G wrote:
> I am from Oracle background. In Oracle, we grant select_catalog_role or
> select any dictionary role to users who want to study performance data. I am
> trying to get similar information on the roles or privileges in PgSQL that we
Greetings!
I am from Oracle background. In Oracle, we grant select_catalog_role or
select any dictionary role to users who want to study performance data. I
am trying to get similar information on the roles or privileges in PgSQL
that we might want to request to investigate the performance
> On 21 Mar 2024, at 14:27, Alvaro Herrera wrote:
>
> On 2024-Mar-21, Daniel Gustafsson wrote:
>
>> On 21 Mar 2024, at 13:28, Alvaro Herrera wrote:
>>
>>> I very much doubt that they realized that comments were going to be
>>> omitted. But clearly it's just a mistake, and easily fixed.
>>
>>
On 2024-Mar-21, Daniel Gustafsson wrote:
> On 21 Mar 2024, at 13:28, Alvaro Herrera wrote:
>
> > I very much doubt that they realized that comments were going to be
> > omitted. But clearly it's just a mistake, and easily fixed.
>
> It sure looks like a search/replace kind of bug. I had just
> On 21 Mar 2024, at 13:28, Alvaro Herrera wrote:
>
> On 2024-Mar-21, Daniel Gustafsson wrote:
>
>> Comments on roles are stored against the pg_authid catalog relation which is
>> the catalog used for dumping roles, but when using --no-role-passwords we
>> instea
On 2024-Mar-21, Daniel Gustafsson wrote:
> Comments on roles are stored against the pg_authid catalog relation which is
> the catalog used for dumping roles, but when using --no-role-passwords we
> instead switch to using the pg_roles catalog relation. Since comments are
> du
On Thu, Mar 21, 2024 at 11:52 AM Dominique Devienne
wrote:
> On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson
> wrote:
>
>> > However I noticed that comments on roles are also omitted from the
>> dump, as if --no--comments flag was set - but it wasn't.
>&
On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson wrote:
> > However I noticed that comments on roles are also omitted from the dump,
> as if --no--comments flag was set - but it wasn't.
>
> Comments on roles are stored against the pg_authid catalog relation
Hi. What do
> On 20 Mar 2024, at 18:40, Bartosz Chroł wrote:
>
> Hello,
> I've tried to dump roles using the following call to pg_dumpall:
> pg_dumpall.exe --roles-only --no-role-passwords
> However I noticed that comments on roles are also omitted from the dump, as
> if --no--c
Hello,
I've tried to dump roles using the following call to pg_dumpall:
pg_dumpall.exe --roles-only --no-role-passwords
However I noticed that comments on roles are also omitted from the dump, as if
--no--comments flag was set - but it wasn't.
When I call `pg_dumpall.exe --roles-onl
On Thu, Mar 21, 2024 at 8:10 AM alex work wrote:
> We encounter slow `GRANT ROLES` only on PostgreSQL 16 instances up to 42
> seconds
> in production, the client process at PostgresSQL would use 100% of the
> CPU. [...]
>
Using ROLE `acc`, grant `d_` ROLE to a session ROLE:
>
Hello,
we run multiple versions of PostgreSQL instances on production. Some time ago
we add new physical servers and decided to go with latest GA from pgdg APT
repository, that is PostgreSQL 16.
We encounter slow `GRANT ROLES` only on PostgreSQL 16 instances up to 42 seconds
in production, the
On Tue, Jun 13, 2023 at 2:20 PM Joe Conway wrote:
> On 6/13/23 04:17, Dominique Devienne wrote:
> > To troubleshoot and validate that emulation, I'd like to introspect ROLE
> > membership to:
> >
> > 1) Output the ROLE "path(s)" between any two ROLEs.[.
On 6/13/23 04:17, Dominique Devienne wrote:
Hi. We emulated a legacy security model (enforced in C/C++ code)
into "layers" of PostgreSQL ROLEs and GRANTs, thus enforced database-side.
To troubleshoot and validate that emulation, I'd like to introspect ROLE
membership to:
1)
Hi. We emulated a legacy security model (enforced in C/C++ code)
into "layers" of PostgreSQL ROLEs and GRANTs, thus enforced database-side.
To troubleshoot and validate that emulation, I'd like to introspect ROLE
membership to:
1) Output the ROLE "path(s)" between
Thanks alot
On Wed, 15 Sep 2021, 07:36 Simon Riggs,
wrote:
> On Wed, 15 Sept 2021 at 00:11, Adrian Klaver
> wrote:
> >
> > On 9/14/21 1:13 PM, Gambhir Singh wrote:
> > > Hi,
> > >
> > > I'm having 4 Databases in the AWS RDS Cluster. Just w
Thank you, Thomas!
-Original Message-
From: Thomas Kellerer
Sent: Wednesday, September 15, 2021 10:55 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: How to restore roles into new Database server?
EXTERNAL
Sridhar Parepalli schrieb am 15.09.2021 um 15:53:
> Hello Pros,
&
Thank you, Adrian!
-Original Message-
From: Adrian Klaver
Sent: Wednesday, September 15, 2021 10:13 AM
To: Sridhar Parepalli ;
pgsql-general@lists.postgresql.org
Subject: Re: How to restore roles into new Database server?
EXTERNAL
On 9/15/21 6:53 AM, Sridhar Parepalli wrote:
> He
Thank you! Ninad
From: Ninad Shah
Sent: Wednesday, September 15, 2021 10:11 AM
To: Sridhar Parepalli
Cc: pgsql-general@lists.postgresql.org
Subject: Re: How to restore roles into new Database server?
EXTERNAL
This can be accomplished by taking a global dump. pg_dumpall utility has an
option
Sridhar Parepalli schrieb am 15.09.2021 um 15:53:
Hello Pros,
Is there a quick way to create roles from database server to another db server?
You can use pg_dumpall with the --globals-only parameter to create a SQL script
that contains all roles from the source server
On 9/15/21 6:53 AM, Sridhar Parepalli wrote:
Hello Pros,
Is there a quick way to create roles from database server to another db server?
pg_dumpall -g the_database -f globals.sql
psql -d other_database -f globals.sql
Though this will include other global objects e.g. tablespaces.
See
This can be accomplished by taking a global dump. pg_dumpall utility has an
option "-g" to accomplish that.
Regards,
Ninad Shah
On Wed, 15 Sept 2021 at 19:24, Sridhar Parepalli
wrote:
> Hello Pros,
> Is there a quick way to create roles from database server to another db
>
Hello Pros,
Is there a quick way to create roles from database server to another db server?
Regards,
Sridhar Parepalli
On Wed, 15 Sept 2021 at 00:11, Adrian Klaver wrote:
>
> On 9/14/21 1:13 PM, Gambhir Singh wrote:
> > Hi,
> >
> > I'm having 4 Databases in the AWS RDS Cluster. Just wanna to know how to
> > create roles which are specific to each database, because when I'm
Thank you for the clarification.
On Tue, 14 Sep 2021, 18:11 Adrian Klaver, wrote:
> On 9/14/21 1:13 PM, Gambhir Singh wrote:
> > Hi,
> >
> > I'm having 4 Databases in the AWS RDS Cluster. Just wanna to know how to
> > create roles which are specific to
On 9/14/21 1:13 PM, Gambhir Singh wrote:
Hi,
I'm having 4 Databases in the AWS RDS Cluster. Just wanna to know how to
create roles which are specific to each database, because when I'm
creating roles in one Database, they are visible in other Databases.
You can't, roles are
Hi,
I'm having 4 Databases in the AWS RDS Cluster. Just wanna to know how to
create roles which are specific to each database, because when I'm creating
roles in one Database, they are visible in other Databases.
Thanks & Regards
Gambhir Singh
Adrian Klaver writes:
> On 2/11/20 11:31 PM, Andrus wrote:
>> Also I dont understand why GRANTED BY clauses appear in file. This looks
>> like noice.
>> GRANT documentation
>> https://www.postgresql.org/docs/current/sql-grant.html
>> does not contain GRANTED BY clause. It looks like pg_dumpall ge
On 2/11/20 11:31 PM, Andrus wrote:
Hi!
Thank you.
>pg_dumpall creates an SQL file which is just a simple text file
>you can then edit sql removing postgres user from the file
>This can be automated in a script that searches the generated sql file
for the postgres user replacing it with a bl
Hi!
>Not a bad idea, would want to extend this to all the roles on the server not
>just postgres
>I've edited the global dump many times removing/editing table spaces,
>comment old users, etc..
Maybe it is easier to create plpgsql procedure which returns desired scrip
Hi!
Thank you.
>pg_dumpall creates an SQL file which is just a simple text file
>you can then edit sql removing postgres user from the file
>This can be automated in a script that searches the generated sql file for the
>postgres user replacing it with a blank/empty line or adds -- to the bri
HI Tom
Not a bad idea, would want to extend this to all the roles on the server
not just postgres
I've edited the global dump many times removing/editing table spaces,
comment old users, etc..
On Tue, Feb 11, 2020 at 5:46 PM Tom Lane wrote:
> "Andrus" writes:
> &
"Andrus" writes:
> How to create backup script which restores all roles and role memberships
> from other server without changing postgres user password.
[ shrug... ] Edit the command(s) you don't want out of the script.
This seems like a mighty random requirement to expe
which comments it out.
On Tue, Feb 11, 2020 at 5:27 PM Andrus wrote:
> Hi!
>
> How to create backup script which restores all roles and role memberships
> from other server without changing postgres user password.
>
> I tried shell script
>
> PGHOST=example.com
> P
Hi!
How to create backup script which restores all roles and role memberships
from other server without changing postgres user password.
I tried shell script
PGHOST=example.com
PGUSER=postgres
PGPASSWORD=mypass
export PGHOST PGPASSWORD PGUSER
pg_dumpall --roles-only --file=globals.sql
psql
Rob Sargent wrote:
> On 10/2/19 5:27 PM, raf wrote:
> > >
> > I can't help with questions about scale but I like to give roles/users
> > almost no permissions at all. i.e. They can't select, insert, update
> > or delete anything. All they have permission
On 10/2/19 5:27 PM, raf wrote:
I can't help with questions about scale but I like to give roles/users
almost no permissions at all. i.e. They can't select, insert, update
or delete anything. All they have permission to do is to execute stored
functions that were installed by a rol
Postgres via a connection pooler, what is
> > the best way to manage privileges for the connecting user? Especially
> > when their is a complex hierarchy of privileges?
> >
> > Should each signed up user have their own role which inherits from
> > whichever roles th
t
> that it also seems really inconvenient.
I agree that there are some drawbacks to it.
> For example how to map an application’s users/people table to Postgres
> roles? The pg_role name field is limited to 64 bytes, you can’t create a
> foreign key to pg_role. What’s the answer? Us
should it be
a problem to create privilege hierarchies and provide individuals with
privileges from any branch of the hierarchy?
Obviously, designing privileges should be done carefully, but granting
roles to users should be easy. I can easily imagine an organisation that
would require only a few
map an application’s users/people table to Postgres roles?
> The pg_role name field is limited to 64 bytes, you can’t create a foreign key
> to pg_role. What’s the answer? Use UUIDs as usernames or something?
>
> There’s very little out there on this topic, but surely this has been don
I have little experience in this area, but it seems like having a Postgres
role for every application user is the right way to do things. It’s just
that it also seems really inconvenient.
For example how to map an application’s users/people table to Postgres
roles? The pg_role name field is
Greetings,
* Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> A couple of pointers:
I generally agree with these comments.
> - This is a good setup if you don't have too many users. Metadata
> queries will start getting slow if you get into the tens of thousands
> of users, maybe earlier.
pooler, what is
> the best way to manage privileges for the connecting user? Especially
> when their is a complex hierarchy of privileges?
>
> Should each signed up user have their own role which inherits from
> whichever roles they are members of? This means that the connection
> po
? Especially when
their is a complex hierarchy of privileges?
Should each signed up user have their own role which inherits from
whichever roles they are members of? This means that the connection pool
role should then be granted in every user?
Does creating a Postgres role for every app user scale
On 8/17/19 4:56 PM, stan wrote:
I am creating an application that will need to have access control. There
will basically be the groups (roles ?):
* normal user (can do insert on a limited sate of tables, and select on a
slightly larger set
* project manager will have some increased insert and
I am creating an application that will need to have access control. There
will basically be the groups (roles ?):
* normal user (can do insert on a limited sate of tables, and select on a
slightly larger set
* project manager will have some increased insert and select capabilities
* sysadmin
Hello,
I want to create three (group) roles. The first one will be read-only,
the second will add INSERT, and the third will add UPDATE and DELETE.
Does the below look OK for this purpose or did I forget something?
/** role_r is read-only with SELECT and EXECUTE */
CREATE ROLE role_r
nikhil raj wrote:
> Currently my company is shifting from MS SQL to postgres
>
> check the roles of user I want to give user roles only select, insert,
> update, execute and create database to all users Permission
>
>
> The users should not have these permission
>
On 11/25/2017 12:03 PM, nikhil raj wrote:
check the roles of user I want to give user roles only select,
insert, update, execute and create database to all users Permission
those permissions can be assigned on a table by table basis, except
create database, thats a special permission
Hi
I am Nikhil currently working as a MS SQL DBA . Currently my company is
shifting from MS SQL to postgres
check the roles of user I want to give user roles only select, insert,
update, execute and create database to all users Permission
The users should not have these permission
drop
87 matches
Mail list logo
