How to connect to Mountain Lion's Profile Manager DB with pgAdmin

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to connect to Mountain Lion's Profile Manager DB with pgAdmin

The Mac OS X Server Mailing List mailing list
Hi guys,
I'm managing a Mountain Lion server (OS X 10.8.5 with Server 2.2.5) and I've been requested to provide access to the Profile Manager's PostgreSQL database using a GUI database management application.
After a bit of research I chose to give a try to pgAdmin[1] but I've not been able to find a complete guide on exactly how to connect to the Profile Manager's DB with pgAdmin (I don't have much database knowledge, let alone PostgreSQL). So far, the most detailed instructions I found are Brian Dwyer's ones:
<http://bdwyertech.net/2012/08/15/apple-profile-manager-mountain-lion-migration/#comment-69>

He first suggests to use LaunchAsRoot (that's seems no more supported, by the way) to… you know… launch pgAdmin as root.
Initially, I chose pgAdmin because of the advertised "Server connection may be made using TCP/IP or Unix Domain Sockets (on *nix platforms)"[1] feature, cause I know that the default configuration doesn't let TCP/IP connections to the DB.
However, I didn't realize that I'd have to run pgAdmin as root and I'd prefer to avoid it, so I enabled local network access with
$ sudo serveradmin settings postgres_server:listen_addresses = 127.0.0.1
and restarted the postgres_server service.

Now, if I fire up pgAdmin and want to add a server, I have to fill the "Maintenance DB" field, among others (screenshot here[2]).
According to Brian's comment, I should enter the device_management db.
However, according to pgAdmin's inline help:
"The maintenance DB field is used to specify the initial database that pgAdmin connects to, and that will be expected to have the pgAgent schema and adminpack objects installed (both optional). On PostgreSQL 8.1 and above, the maintenance DB is normally called ‘postgres’, and on earlier versions ‘template1’ is often used, though it is preferrable to create a ‘postgres’ database for this purpose to avoid cluttering the template database."

These are my DBs:
$ sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/psql -h '/Library/Server/PostgreSQL For Server Services/Socket/' --list
Password:
                                   List of databases
       Name        |   Owner    | Encoding | Collate | Ctype |    Access privileges  
-------------------+------------+----------+---------+-------+-------------------------
 caldav            | caldav     | UTF8     | C       | C     |
 collab            | collab     | UTF8     | C       | C     |
 device_management | _devicemgr | UTF8     | C       | C     |
 postgres          | _postgres  | UTF8     | C       | C     |
 template0         | _postgres  | UTF8     | C       | C     | =c/_postgres           +
                   |            |          |         |       | _postgres=CTc/_postgres
 template1         | _postgres  | UTF8     | C       | C     | =c/_postgres           +
                   |            |          |         |       | _postgres=CTc/_postgres
 webauth           | webauth    | UTF8     | C       | C     |
(7 rows)

As you can see, OS X has the postgres DB, however I don't know if the expected-but-not-required pgAgent schema and adminpack objects are installed.

Now, the questions:
1) Should I connect to the postgres DB and then somehow "switch" to the Profile Manager's DB or should I directly connect to the device_management DB?
2) Is it safe to connect to either the postgres (with the _postgres user) or device_management (with the _devicemgr user) databases? I really don't want to mess them up just by connecting with pgAdmin.

Thank you in advance for any advice.


[1] <http://www.pgadmin.org/>
[2] <http://tinyurl.com/o39665h>

--
Andrea "XFox" Govoni

AIM/iChat/ICQ: [hidden email]
Yahoo! Messenger: xfox82

PGP
KeyID: 0x212E69C1
Fingerprint: FBE1 CA7D 34BE 4A53 9639  5C36 B7A0 605F 212E 69C1

 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Macos-x-server mailing list      ([hidden email])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/macos-x-server/lists%2Bs10970n2h62%40n7.nabble.com

This email sent to [hidden email]
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to connect to Mountain Lion's Profile Manager DB with pgAdmin

The Mac OS X Server Mailing List mailing list
Il giorno 27 nov 2014 alle 11:11, OS X Server Mail List <[hidden email]> ha scritto:
> Hi guys,
 
I noticed that the e-mail lost proper formatting, go here to see it how I intended it to be:
<https://devforums.apple.com/thread/255570>

--
Andrea "XFox" Govoni

AIM/iChat/ICQ: [hidden email]
Yahoo! Messenger: xfox82

PGP
KeyID: 0x212E69C1
Fingerprint: FBE1 CA7D 34BE 4A53 9639  5C36 B7A0 605F 212E 69C1

 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Macos-x-server mailing list      ([hidden email])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/macos-x-server/lists%2Bs10970n2h62%40n7.nabble.com

This email sent to [hidden email]
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to connect to Mountain Lion's Profile Manager DB with pgAdmin

The Mac OS X Server Mailing List mailing list
In reply to this post by The Mac OS X Server Mailing List mailing list

On 27 Nov 2014, at 10:09am, OS X Server Mail List <[hidden email]> wrote:

> Should I connect to the postgres DB

What are they (the people who asked for access) hoping to do once you're connected ?

It might make sense if they just want to read some data from the database, but not if they want to make changes.  You have no way of telling the server that fields in the database were updated.

They can't use an pgAdmin to back up the database because they have no way to tell the server "Don't make any changes while I'm backing up.".

Lastly, unless they are experts in OS X or iOS, they won't have any idea what the values in the database mean.

Simon

 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Macos-x-server mailing list      ([hidden email])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/macos-x-server/lists%2Bs10970n2h62%40n7.nabble.com

This email sent to [hidden email]
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to connect to Mountain Lion's Profile Manager DB with pgAdmin

The Mac OS X Server Mailing List mailing list
In reply to this post by The Mac OS X Server Mailing List mailing list
Il giorno 28 nov 2014 alle 10:17, OS X Server Mail List <[hidden email]> ha scritto:
> On 27 Nov 2014, at 10:09am, OS X Server Mail List <[hidden email]> wrote:
>
>> Should I connect to the postgres DB
>
> What are they (the people who asked for access) hoping to do once you're connected ?

They want to performs SQL queries to extract lists of devices, let's say, still on iOS 7.1.1 or with less than 10 GB of free space left.
They absolutely don't want to perform any write operation on the DBs.

> They can't use an pgAdmin to back up the database because they have no way to tell the server "Don't make any changes while I'm backing up.".

We are already backing up the Profile Manager database with this command:

$ sudo /Applications/Server.app/Contents/ServerRoot/usr/bin/pg_dump -h '/Library/Server/PostgreSQL For Server Services/Socket/' -U _postgres device_management -c -f /db_backups/device_management.sql

Since we are at it, can you confirm that it is correct?

> Lastly, unless they are experts in OS X or iOS, they won't have any idea what the values in the database mean.

I'm already accessing the Profile Manager DB on the command line with the psql tool and using the \x switch I can actually see meaningful values. Are you saying that it will not be the same with pgAdmin?

-- 
Andrea "XFox" Govoni

AIM/iChat/ICQ: [hidden email]
Yahoo! Messenger: xfox82

PGP
KeyID: 0x212E69C1
Fingerprint: FBE1 CA7D 34BE 4A53 9639  5C36 B7A0 605F 212E 69C1

 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Macos-x-server mailing list      ([hidden email])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/macos-x-server/lists%2Bs10970n2h62%40n7.nabble.com

This email sent to [hidden email]
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to connect to Mountain Lion's Profile Manager DB with pgAdmin

The Mac OS X Server Mailing List mailing list

> On Nov 28, 2014, at 6:59 AM, OS X Server Mail List <[hidden email]> wrote:
>
> We are already backing up the Profile Manager database with this command:
>
> $ sudo /Applications/Server.app/Contents/ServerRoot/usr/bin/pg_dump -h '/Library/Server/PostgreSQL For Server Services/Socket/' -U _postgres device_management -c -f /db_backups/device_management.sql
>
> Since we are at it, can you confirm that it is correct?

Hi,

I'll jump in here to say that the only way to have confidence in a backup procedure is to practice the restore procedure and validate the results. I would do this on a test system. Your pg_dump command looks correct at first glance, but there are likely other things you need to handle also, such as keychains / CA certs and service config files.

The best time to learn what it takes to restore service data to a different host is when things are not broken :)

-dre

 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Macos-x-server mailing list      ([hidden email])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/macos-x-server/lists%2Bs10970n2h62%40n7.nabble.com

This email sent to [hidden email]
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to connect to Mountain Lion's Profile Manager DB with pgAdmin

The Mac OS X Server Mailing List mailing list
In reply to this post by The Mac OS X Server Mailing List mailing list

On 28 Nov 2014, at 2:59pm, OS X Server Mail List <[hidden email]> wrote:

> I'm already accessing the Profile Manager DB on the command line with the psql tool and using the \x switch I can actually see meaningful values. Are you saying that it will not be the same with pgAdmin?

The values you see may be meaningful but they may not be correct.  While Profile Manager is running, it may keep current changes in memory.  There's no need for it to flush changes to the database until it quits.  So by querying the database you may be getting 'old' values.

When you use Time Machine to back up an OS X Server it sends a special signal to each running application saying "You're about to be backed up, flush all changes to disk.".  Unless you're doing this you cannot be sure of the 'state' stored on disk: it may be up-to-date, it may be old, it may even be inconsistent because some things have been flushed and others haven't.

I don't know the details of how this particular program works, though, and you may be okay for the purposes you need.

Simon

 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Macos-x-server mailing list      ([hidden email])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/macos-x-server/lists%2Bs10970n2h62%40n7.nabble.com

This email sent to [hidden email]
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to connect to Mountain Lion's Profile Manager DB with pgAdmin

The Mac OS X Server Mailing List mailing list
On 01 dic 2014 at 10:10, OS X Server Mail List <[hidden email]> wrote:
> When you use Time Machine to back up an OS X Server it sends a special signal to each running application saying "You're about to be backed up, flush all changes to disk.". Unless you're doing this you cannot be sure of the 'state' stored on disk: it may be up-to-date, it may be old, it may even be inconsistent because some things have been flushed and others haven't.
 
I know, thanks, but I'm not aware of a better method to create reports of enrolled devices that satisfy certain conditions on Mountain Lion's Profile Manager. Do you?
However, judjing from the tests I made so far on the command-line, it doesn't seem that Profile Manager keeps many changes in memory before writing to disk. Ideally, I could also stop and restart the service if I want to be sure to have the most up-to-date informations.

> I don't know the details of how this particular program works, though, and you may be okay for the purposes you need.

Yes, and now if only someone would answer to the two questions I made in the first post of the thread… :)

--
Andrea "XFox" Govoni

AIM/iChat/ICQ: [hidden email]
Yahoo! Messenger: xfox82

PGP
KeyID: 0x212E69C1
Fingerprint: FBE1 CA7D 34BE 4A53 9639  5C36 B7A0 605F 212E 69C1



 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Macos-x-server mailing list      ([hidden email])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/macos-x-server/lists%2Bs10970n2h62%40n7.nabble.com

This email sent to [hidden email]
Loading...