pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help - Unable to grant a role
9+ messages / 4 participants
[nested] [flat]
* Need help - Unable to grant a role
@ 2024-02-29 01:06 Siraj G <tosiraj.g@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Siraj G @ 2024-02-29 01:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello!
I have a PgSQL 15.4 database newly created. I created a user and is using
it to restore a backup of another database, but unable to do so as it is
failing with the below error:
Error: role 'hd*********' does not exist.
I even tried to grant pg_read_system_files and pg_write_system_files roles,
even then I am getting the same error.
Can someone please advise me on what I should do?
Regards
Siraj
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need help - Unable to grant a role
@ 2024-02-29 01:28 Erik Wienhold <ewie@ewie.name>
parent: Siraj G <tosiraj.g@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Erik Wienhold @ 2024-02-29 01:28 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On 2024-02-29 02:06 +0100, Siraj G wrote:
> I have a PgSQL 15.4 database newly created. I created a user and is using
> it to restore a backup of another database, but unable to do so as it is
> failing with the below error:
> Error: role 'hd*********' does not exist.
>
> I even tried to grant pg_read_system_files and pg_write_system_files roles,
> even then I am getting the same error.
>
> Can someone please advise me on what I should do?
The error is pretty clear: you need to create that role first.
It probably stems from commands that try to change owners to that role
or grant privileges to it. If you use pg_dump or pg_restore, you can
use options --no-acl and --no-owner to omit those commands if you don't
need them.
--
Erik
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need help - Unable to grant a role
@ 2024-02-29 01:35 Siraj G <tosiraj.g@gmail.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 9+ messages in thread
From: Siraj G @ 2024-02-29 01:35 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin@lists.postgresql.org
Thank you Erik!
Do we need to create hd*********' as both user and role?
Regards
Siraj
On Thu, Feb 29, 2024 at 6:58 AM Erik Wienhold <ewie@ewie.name> wrote:
> On 2024-02-29 02:06 +0100, Siraj G wrote:
> > I have a PgSQL 15.4 database newly created. I created a user and is using
> > it to restore a backup of another database, but unable to do so as it is
> > failing with the below error:
> > Error: role 'hd*********' does not exist.
> >
> > I even tried to grant pg_read_system_files and pg_write_system_files
> roles,
> > even then I am getting the same error.
> >
> > Can someone please advise me on what I should do?
>
> The error is pretty clear: you need to create that role first.
>
> It probably stems from commands that try to change owners to that role
> or grant privileges to it. If you use pg_dump or pg_restore, you can
> use options --no-acl and --no-owner to omit those commands if you don't
> need them.
>
> --
> Erik
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need help - Unable to grant a role
@ 2024-02-29 02:09 David G. Johnston <david.g.johnston@gmail.com>
parent: Siraj G <tosiraj.g@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2024-02-29 02:09 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org
On Wed, Feb 28, 2024 at 6:36 PM Siraj G <tosiraj.g@gmail.com> wrote:
>
> Do we need to create hd*********' as both user and role?
>
Since a PG role and a PG user are the same thing under the hood, the later
simply meaning the role can login, then no. Though maybe you mean a OS
user since you are dealing with operating system commands here...?
Maybe show your work so we aren't left guessing what you are trying to do.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need help - Unable to grant a role
@ 2024-02-29 02:59 Siraj G <tosiraj.g@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Siraj G @ 2024-02-29 02:59 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org
Hello David!
Sorry for not giving the clarity. It is a GCP environment, we are trying to
import using the GCP feature from a backup (export) taken from another
instance in the same infra. While the user (hd*****) is created from the
GCP console for a specific instance, I can see its presence from "select *
from pg_user".
Since I am trying to restore, I believe the user should have
pg_write_system_files role, hence trying to grant it.
Regards
Siraj
On Thu, Feb 29, 2024 at 7:40 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Wed, Feb 28, 2024 at 6:36 PM Siraj G <tosiraj.g@gmail.com> wrote:
>
>>
>> Do we need to create hd*********' as both user and role?
>>
>
> Since a PG role and a PG user are the same thing under the hood, the later
> simply meaning the role can login, then no. Though maybe you mean a OS
> user since you are dealing with operating system commands here...?
>
> Maybe show your work so we aren't left guessing what you are trying to do.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need help - Unable to grant a role
@ 2024-02-29 03:39 David G. Johnston <david.g.johnston@gmail.com>
parent: Siraj G <tosiraj.g@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2024-02-29 03:39 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Wednesday, February 28, 2024, Siraj G <tosiraj.g@gmail.com> wrote:
>
> Since I am trying to restore, I believe the user should have
> pg_write_system_files role, hence trying to grant it.
>
Restoring a backup does not require the client connection to access the
server’s filesystem - all data is passed over the client connection.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need help - Unable to grant a role
@ 2024-02-29 03:48 Erik Wienhold <ewie@ewie.name>
parent: Siraj G <tosiraj.g@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Erik Wienhold @ 2024-02-29 03:48 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org
On 2024-02-29 03:59 +0100, Siraj G wrote:
> On Thu, Feb 29, 2024 at 7:40 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
> > Since a PG role and a PG user are the same thing under the hood, the later
> > simply meaning the role can login, then no. Though maybe you mean a OS
> > user since you are dealing with operating system commands here...?
>
> Sorry for not giving the clarity. It is a GCP environment, we are trying to
> import using the GCP feature from a backup (export) taken from another
> instance in the same infra. While the user (hd*****) is created from the
> GCP console for a specific instance, I can see its presence from "select *
> from pg_user".
Do you see that user on the old or the new instance?
> Since I am trying to restore, I believe the user should have
> pg_write_system_files role, hence trying to grant it.
Do you mean pg_write_server_files or is pg_write_system_files a
GCP-specific role? pg_write_server_files isn't necessary for restoring
and you should be cautious about granting it because it's a powerful
role that allows grantees to gain superuser-level access[1].
[1] https://www.postgresql.org/docs/15/predefined-roles.html
--
Erik
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need help - Unable to grant a role
@ 2024-02-29 06:58 Siraj G <tosiraj.g@gmail.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 9+ messages in thread
From: Siraj G @ 2024-02-29 06:58 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org
Yes, the role is pg_write_server_files.
On Thu, Feb 29, 2024 at 9:18 AM Erik Wienhold <ewie@ewie.name> wrote:
> On 2024-02-29 03:59 +0100, Siraj G wrote:
> > On Thu, Feb 29, 2024 at 7:40 AM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
> > > Since a PG role and a PG user are the same thing under the hood, the
> later
> > > simply meaning the role can login, then no. Though maybe you mean a OS
> > > user since you are dealing with operating system commands here...?
> >
> > Sorry for not giving the clarity. It is a GCP environment, we are trying
> to
> > import using the GCP feature from a backup (export) taken from another
> > instance in the same infra. While the user (hd*****) is created from the
> > GCP console for a specific instance, I can see its presence from "select
> *
> > from pg_user".
>
> Do you see that user on the old or the new instance?
>
> > Since I am trying to restore, I believe the user should have
> > pg_write_system_files role, hence trying to grant it.
>
> Do you mean pg_write_server_files or is pg_write_system_files a
> GCP-specific role? pg_write_server_files isn't necessary for restoring
> and you should be cautious about granting it because it's a powerful
> role that allows grantees to gain superuser-level access[1].
>
> [1] https://www.postgresql.org/docs/15/predefined-roles.html
>
> --
> Erik
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need help - Unable to grant a role
@ 2024-02-29 08:00 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Siraj G <tosiraj.g@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2024-02-29 08:00 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; pgsql-admin@lists.postgresql.org
On Thu, 2024-02-29 at 06:36 +0530, Siraj G wrote:
> I have a PgSQL 15.4 database newly created. I created a user and is using it
> to restore a backup of another database, but unable to do so as it is failing
> with the below error:
> Error: role 'hd*********' does not exist.
>
> I even tried to grant pg_read_system_files and pg_write_system_files roles,
> even then I am getting the same error.
Users (= roles) are not part of the database dump, because they are not part
of the database.
Dump and restore the roles separately, *before* you restore the database.
You can dump the roles with "pg_dumpall -r".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-02-29 08:00 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-29 01:06 Need help - Unable to grant a role Siraj G <tosiraj.g@gmail.com>
2024-02-29 01:28 ` Erik Wienhold <ewie@ewie.name>
2024-02-29 01:35 ` Siraj G <tosiraj.g@gmail.com>
2024-02-29 02:09 ` David G. Johnston <david.g.johnston@gmail.com>
2024-02-29 02:59 ` Siraj G <tosiraj.g@gmail.com>
2024-02-29 03:39 ` David G. Johnston <david.g.johnston@gmail.com>
2024-02-29 03:48 ` Erik Wienhold <ewie@ewie.name>
2024-02-29 06:58 ` Siraj G <tosiraj.g@gmail.com>
2024-02-29 08:00 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox