I've noticed that SO and other sites use the auto-incrementing primary key of the user table as a publicly viewable user id (at least I assume this is what they are doing). In the case of SO, the user's profile can be viewed if you know or can guess their user id.
What are some things to consider before implementing a similar style of user id generation? I am developing a non-commercial app that relies on the concept of "friends" in assigning various permissions between users, but I'd like all users' basic profiles to be viewable at a simple url such as app.com/users/userid. More detailed profile information would only be accessible to "friends" of that user who have been confirmed by that user.
I guess my question is this does the "guessability" of a user ID indicate anything about the inherent security of a system like this or, is that all in the way that individual features are actually implemented? Is there anything I might not be considering about this that would make it unwise? Anything I should absolutely avoid doing with these user ids?
A note: I have no concern for "competitors" knowing or guessing how many users I have based on the number of the most recent user or the rate of change between users.
It's not a problem at all. In fact, I'd almost say the opposite: if you're having to obscure the params in the URL for security then you're doing it wrong; the security should be handled in the code.
From your question, it looks like you're already thinking about security the right way, so you should be fine with the primary key in the URL.
Having a primary_key which stores no information (like an auto_incremented id) is also good because it will never change. If you're putting info like the username in URLs you'll either want to never implement people being able to edit their usernames, or cope with the broken links that may be left when they do (remember they may be on sites other than yours).
The only info having the auto_incremented id in your URLs could leak is that one user will know if they were a user before or after another. This is unlikely to be a concern (and might not be reliable anyway).