Thread: Need help - Unable to grant a role
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
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
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
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.
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.
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.
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
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
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