Thread: Change owner of all database objects
Currently all my database objects are owned by superuser postgres . I need to change owner to a nonprivileged role for all objects in a database. I'm using Postgres 8.1 server and client in Windows XP, only plpSQL language installed in server. I'm thinking about the following possibilities: 1. Create a loop over all database objects and issue change owner statements 2. Update system tables. 3. Make database dump and restore 4. make text dump and run some global find/replace. I'm not sure which utility can handle it in windows Is there any sample how to do it in Windows ? Andrus.
Andrus wrote: > Currently all my database objects are owned by superuser postgres . > > I need to change owner to a nonprivileged role for all objects in a > database. > > I'm using Postgres 8.1 server and client in Windows XP, only plpSQL language > installed in server. Search the mailing list archives. I seem to remember solutions involving shell and perl (and you could install perl if you wanted) but I think also something in plpgsql. -- Richard Huxton Archonet Ltd
On Jan 18, 2006, at 4:02 AM, Richard Huxton wrote: > Andrus wrote: >> Currently all my database objects are owned by superuser postgres . >> I need to change owner to a nonprivileged role for all objects in >> a database. >> I'm using Postgres 8.1 server and client in Windows XP, only >> plpSQL language installed in server. > > Search the mailing list archives. I seem to remember solutions > involving shell and perl (and you could install perl if you wanted) > but I think also something in plpgsql. You can find some plpgsql functions here: http://pgedit.com/tip/postgresql/access_control_functions John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
i use this :
CREATE TABLE UnstructuredContent
(
imageId int8,
content OID
);
for importing image : insert into UnstructuredContent values(1, lo_import('image_path'))
for exporting it's the "lo_export" function
see : http://www.postgresql.org/docs/8.1/interactive/lo-funcs.html
-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de John DeSoi
Envoyé : mercredi 18 janvier 2006 15:43
À : Richard Huxton
Cc : Andrus; pgsql-general@postgresql.org
Objet : Re: [GENERAL] Change owner of all database objects
On Jan 18, 2006, at 4:02 AM, Richard Huxton wrote:
> Andrus wrote:
>> Currently all my database objects are owned by superuser postgres .
>> I need to change owner to a nonprivileged role for all objects in
>> a database.
>> I'm using Postgres 8.1 server and client in Windows XP, only
>> plpSQL language installed in server.
>
> Search the mailing list archives. I seem to remember solutions
> involving shell and perl (and you could install perl if you wanted)
> but I think also something in plpgsql.
You can find some plpgsql functions here:
http://pgedit.com/tip/postgresql/access_control_functions
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.
sorry, i made a mistake ... not the good post :|
-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de FERREIRA,
William (GFI INFORMATIQUE)
Envoyé : mercredi 18 janvier 2006 15:51
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] Change owner of all database objects
i use this :
CREATE TABLE UnstructuredContent
(
imageId int8,
content OID
);
for importing image : insert into UnstructuredContent values(1, lo_import('image_path'))
for exporting it's the "lo_export" function
see : http://www.postgresql.org/docs/8.1/interactive/lo-funcs.html
-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de John DeSoi
Envoyé : mercredi 18 janvier 2006 15:43
À : Richard Huxton
Cc : Andrus; pgsql-general@postgresql.org
Objet : Re: [GENERAL] Change owner of all database objects
On Jan 18, 2006, at 4:02 AM, Richard Huxton wrote:
> Andrus wrote:
>> Currently all my database objects are owned by superuser postgres .
>> I need to change owner to a nonprivileged role for all objects in
>> a database.
>> I'm using Postgres 8.1 server and client in Windows XP, only
>> plpSQL language installed in server.
>
> Search the mailing list archives. I seem to remember solutions
> involving shell and perl (and you could install perl if you wanted)
> but I think also something in plpgsql.
You can find some plpgsql functions here:
http://pgedit.com/tip/postgresql/access_control_functions
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.
> You can find some plpgsql functions here: > > http://pgedit.com/tip/postgresql/access_control_functions John, thank you. I found that acl_admin.grant_on_all() does not grant privileges on schema: it does not execute GRANT ALL ON SCHEMA myschema TO myrole; statements. So I must execute GRANT ALL ON SCHEMA commands for each schema manually, right ? Andrus.