Thread: PostgreSQL For Beginners

One of the programmers I work with is interested in migrating from
Oracle to PostgreSQL as the backend for his applications.

Is there a PostgreSQL "beginners" guide available somewhere, which
might help him understand setting up a test database on his Windows
system?

I may also be performing a 30-60 minute presentation to several of the
developers as a "introduction" to PostgreSQL.  Any suggestions on what
I should cover in this presentation?

Topics I've considered so far are the following:

Designing databases with security in mind
Creating your first PostgreSQL Database
Understanding Roles and host-based authentication
Understanding permissions
Creating a table
Writing procedures


Please keep in mind my primary focus is database security, so if I've
left out something a beginner should know that is glaringly obvious,
be kind!
I'm going to try not to include information such as configuring WAL or
the error log, as I will be configuring these settings once PostgreSQL
is installed.

Thanks a lot!

Ken

On 2/3/2011 8:40 AM, Kenneth Buckler wrote:
> One of the programmers I work with is interested in migrating from
> Oracle to PostgreSQL as the backend for his applications.
>
> Is there a PostgreSQL "beginners" guide available somewhere, which
> might help him understand setting up a test database on his Windows
> system?
>
> I may also be performing a 30-60 minute presentation to several of the
> developers as a "introduction" to PostgreSQL.  Any suggestions on what
> I should cover in this presentation?
>
> Topics I've considered so far are the following:
>
> Designing databases with security in mind
> Creating your first PostgreSQL Database
> Understanding Roles and host-based authentication
> Understanding permissions
> Creating a table
> Writing procedures
>
>
> Please keep in mind my primary focus is database security, so if I've
> left out something a beginner should know that is glaringly obvious,
> be kind!
> I'm going to try not to include information such as configuring WAL or
> the error log, as I will be configuring these settings once PostgreSQL
> is installed.
>
> Thanks a lot!
>
> Ken
>

Well, one question though, if they already use Oracle... shouldnt they
already know a bunch of that stuff?  Some syntax might be a little
different, but "Designing databases with security in mind" seems generic
enough that it would apply to any database.

"Creating your first PostgreSQL Database" sounds more like "Fisher price
-- My first database" to me.  Unless these are hard core programmers
that never touch sql, they might be insulted.

"Creating a table" is 95% the same as Oracle.

Really, I think the question should be "how much generic sql do they
need to learn", and "how is PG different from Oracle"

As for security, again, 95% is the same... sql injection works the same
on all databases.  If they know how to avoid it in Oracle, then "how is
PG different from Oracle" should teach them everything they need to
avoid it in PG.

All of this, of course, assumes your programmers have a decent amount of
Oracle knowledge.  If not, you should ignore this email.  (Actually, you
might want to just ignore it anyway :-) )

Also, (while you are ignoring this email) when you present Stored Procs,
tell them they can write in Perl.  If they don't cheer then fire them! :-)

-Andy

On 2/3/2011 9:31 AM, Kenneth Buckler wrote:
> I'm probably going to be presenting to a mixed audience, some of the
> developers will have extensive Oracle knowledge, some will have MS SQL
> Server experience.  Plus, I'm not extremely familiar with Oracle, so
> trying to compare the two would not work well for me.
>
> My main focus for this presentation will be to outline how PostgreSQL
> syntax works, regardless of what server the developer has experience
> with.
>
> With regards to the security issues, unfortunately the developers in
> question have never programmed with security in mind from the
> beginning, only as an afterthought, sometimes resulting in having to
> choose between rebuilding a system completely to comply with security
> requirements, or try to get an exception granted.  This usually
> results in something to the effect of "why won't you security people
> just leave us alone and stop making us rebuild our systems".  I'm
> trying to avoid rebuilding systems, and hope to have security as part
> of the design of the database system.
>
> I've already got a good idea what to touch on for the security
> aspect...just trying to figure out what should be included as far as
> "Here's how you do THIS in PostgreSQL"
>
> Ken
>

Ahh, ok.  Here are my thoughts:

1) sql injection
2) over the wire encryption need? PG ssl connections etc.
3) storing connection info:  .ini files? hard code em? etc.
4) HIPAA rules?  storing ssn, credit cards, etc
5) backup security (dont leave the tapes in the front seat of your car)
6) the top secret handshake. :-)

-Andy

I think an intro presentation for Oracle developers should include:
-- sqlplus to psql comparison
-- how to setup OS authenticated users in PostgreSQL
-- purpose of pg_hba.conf
-- schemas and search_path. 
-- where to find things in the catalog.  information_schema or pg_tables


Rick

On 2011-02-03, Kenneth Buckler <kenneth.buckler@gmail.com> wrote:

> One of the programmers I work with is interested in migrating from
> Oracle to PostgreSQL as the backend for his applications.
>
> Is there a PostgreSQL "beginners" guide available somewhere, which
> might help him understand setting up a test database on his Windows
> system?
>
> I may also be performing a 30-60 minute presentation to several of the
> developers as a "introduction" to PostgreSQL.  Any suggestions on what
> I should cover in this presentation?

where to find the documentation.

> Designing databases with security in mind

 he who controls pg_hba.conf owns the database.

> Creating your first PostgreSQL Database

the tools psql and pgadmin

> Understanding Roles and host-based authentication

 unserstanding postgres schemas
 how they are similar and how ther differ from oracle.

> Understanding permissions
> Creating a table

the postgresql data types,
  serial and bigserial for automatic serial numbers.
  text is as fast as any alternative
  timestamp and timestamptz  (with attention to how they differ from
  oracle)
  any other types suited to your application space.


how postgres breaks the standards
  string escaping
  char literals
  missing features?
  other things?

how oracle breaks sql standsrds (ie: things that may no longer work)


> Writing procedures

  dollar quoting.

postgres' exception based programming and transaction model

  instroduction to plpgsql

  raise/exception
  begin transaction/checkpoint/rollback/commit

  "CREATE FUNCTION ... SECURITY DEFINER"

  debugging plpgsql using raise debug..

  other languages.

where to find the documentation.

> I'm going to try not to include information such as configuring WAL or
> the error log, as I will be configuring these settings once PostgreSQL
> is installed.

there's probably enough for a couple of hours there.

--
⚂⚃ 100% natural